Complete Practical; Real-time Job Oriented Training

Azure Data Engineer

PLAN A PLAN B PLAN C
Applicable For (Resume Plan) Azure Data Engineer SQL & T-SQL Queries SQL & T-SQL Queries
Azure Data Engineer Azure Data Engineer
Power BI
ADF : Azure Data Factory   
ADF : Data Imports, ETL   
ADF : Data Flows, Wrangling   
ADF : Transformations, ETL   
Synapse: Configuration, Loads   
Synapse: ETL with ADF, DWH   
Synapse: MPP, DWH, Tuning   
Storage: ADLS Gen 2, BLOB   
Storage: Azure Tables, ACL, IAM   
Azure Stream Analytics & Jobs   
IoT Hubs and Event Hubs, ETL   
ADB : Azure Data Bricks, Spark   
ADB : Spark Database, Data Loads   
ADB : SparkSQL, Jobs, Parameters   
ADB : Delta Tables, PySpark ETL   
DP 203 Exam Guidance   
TSQL: Database Basics, T-SQL X  
TSQL : Constraints, Joins, Queries X  
TSQL: Views, Group By, Self Joins X  
Power BI: Report Design, Visuals X X 
Power BI: M Lang, DAX for ETL X X 
Power BI: Cloud, Apps, Tenant X X 
Power BI: Report Server, Project X X 
DP 500, DA 100 Exams Guidance X X 
Total Duration 7 Weeks 8 Weeks 12 Weeks

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
Trainer : Mr. Sai Phanindra T [18+ Yrs of Real-time Exp]. Profile @ [Link]/in/saiphanindra

Training Module Duration Plan A Plan B Plan C

Module 1 Azure Data Engineer (DP 203)
[Azure Funda, Azure Migrations, ADF,
Synapse DWH, ADLS Storage, Spark SQL, 7W   
Databricks, ASA, IoT, Real-time Project]
Module 2 SQL Basics, T-SQL Queries 3W X  
Power BI & Big Data Analytics (DA 100)
[Power BI Cloud Service, Report Server,
Module 3
REST API, Dashboards, Power Query,
4W X X 
DAX, Real-time Project, Resume Guide]
Total Duration 7W 10 W 14 W

Module 1: Azure Data Engineer

Part 1: Azure Data Factory [ADF], Synapse Analytics
Chapter 1: Cloud Basics, Azure SQL
Cloud Introduction and Azure Basics; Azure Implementation: IaaS, PaaS, SaaS; Azure Data
Engineer: Job Roles; Azure Storage Components; Azure ETL & Streaming Components; Need for
Azure Data Factory (ADF); Need for Azure Synapse Analytics; Azure Resources and Resource
Types; Azure Account, Subscription (Free); Azure SQL Server [Logical Server]; Firewall Rules; Azure
SQL Database & Azure SQL Pool Deployment; DTU Versus DWU; SSMS Connections;

Chapter 2: Synapse SQL Pools (DWH)

Dedicated SQL Pools in Azure; Data Warehouse with Synapse; Massively Parallel Processing
(MPP); Control Nodes and Compute Nodes; DMS: Data Movement Service; Start/Resume/Pause
& Scaling; SQL Pool Config @ TSQL Scripts; Start/Resume/Pause, Scaling Options; Table Creations
@ TSQL Scripts; Table Partitions: Left & Right; Distributions: Round Robin, Hash; Distributions:
Replicate and Usage; Auto Indexing & Column Store; Planning for Big Data Loads; Need for ADF:
Azure Data Factory;

Chapter 3: Azure Data Factory, Pipelines

Azure Data Factory (ADF) Concepts; ADF Pipelines : Architecture; Integration Runtime (IR) & Use;
Linked Services and Datasets; Pipeline Activities: Copy Data Tool; DIU : Data Integration Units;
DTU Vs DWUs Vs DIU; ADF Pipeline with Copy Data Tool;Azure SQL DB to Synapse Data Loads;
Multi Tables Data Loads with ADF; Bulk Insert, Data Copy Methods; ETL Staging: Storage Account;
Staging Container Connections; DIU Allocations; ETL Pipeline Monitoring, Runs;

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Chapter 4: OnPremise Data Loads, Upsert
Copy Data Tool : Incremental Loads; On-Premise Data Sources with Azure; Self Hosted Integration
Runtime (IR); Access Keys, Remote Linked Service; Synapse SQL Pool (DW), OnPremise; ETL
Staging with Storage Account; Copy Method: Polybase – Tuning; Polybase : Big Data Loads; ETL
Pipelines for Incremental Loads; Business Keys For Table Upsert; Pipeline Schedules with ADF; ETL
Logging with Storage Account; Copy Method: UPSERT; DIU, DOCP & Publish; Manual Pipeline
Executions in ADF;

Chapter 5: File Incremental Loads in ADF

Incremental Loads with Files (BLOB); ETL Schedules: Tumbling Window; Execution Retry and Delay
Options; Binary Copy, Structural Data Loads; Incremental Loads Verification Tests; Incompatible
Rows & Fault Tolerance; Pipeline Compression & Tuning; Pipeline Publish, Monitor Options; Azure
Monitor Resource : Metrics; ADF Metrics; Pipeline Monitoring; Synapse: Storage Monitoring,
Alerts; Conditions, Signal Rules and Metrics; Alerts & Action Groups: Emails;

Chapter 6: ADF Data Flow - 1

Data Flow Task, Data Flow Activity; Transformations with Data Flow; Spark Cluster for Debugging;
Cluster Node Configurations; Spark Cluster Types & Sizing; Transaction Optimized – Capacity;
Memory Optimized – Capacity; Data Cleansing with ADF; Data Orchestration with Data Flow;
SELECT Transformation; Conditional Split Transformation; UNION, SELECT Transformation; Spark
Cluster For Pipeline Executions; Pipeline Monitoring & Run IDs; Adding Data Flow;

Chapter 7: ADF Data Flow - 2

ADF Pipelines For ETL Operations; Data Flow Tasks, Activities in Synapse; JOIN & EXISTS
Transformations; Aggregate & Group By Transformations; Window Functions, Rank in Data Flow;
Rank / DenseRank / Row Number; Derived Column Transformation; Lookup, Surrogate Key, Parse;
Type Convert, Cast Transformations; Reusing Data Flow Tasks in Synapse; Pipeline Validations &
Executions; Inline Datasets, Schema Drift; Data De-duplication with ADF; DFT Optimization
Techniques; Data Flow Task - Staging, Logging;

Chapter 8: Azure Synapse Analytics

Azure Synapse Analytics Resource; Azure Synapse Analytics Workspace; Managed Resource
Group, SQL Account; Synapse Workspace & Synapse Studio; Operations with Synapse Workspace;
ADLS Gen 2 Storage Account, Container; Synapse Studio: Scripts & Pipelines; Dedicated SQL Pools
: Creation, Use; Synapse Tables, Data Loads with TSQL; COPY INTO Statements with T-SQL; Row
Terminator and Compression; T-SQL Queries and Aggregations; Aggregation Data Loads; Synapse
Pipelines with TSQL; Stored Procedure Activity & Triggers;

Chapter 9: Synapse Analytics with Spark

Synapse Pipelines: Performance Advantage; Pivot Transformation For Normalization; Generate
Pivot Column, Aggregations; Pivot Transformation & Pivot Setting; Pivot Key Selection, Value and
Nulls; Pivoted Columns & Column Pattern; Column Prefix, Help Graphic, Metadata; Denormalized
Data and Aggregations; Apache Spark Pool in Azure Synapse; Spark Cluster Nodes: Vcores,

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Memory; Notebooks : Purpose, Usage Options; Python Notebooks; Databases in Apache Spark
Pool; Data Loads from Dedicated SQL Pools; PySpark Code for Data Operations, Writes;

Chapter 10: Synapse Security & Parameters

Azure Active Directory (AAD) Users, Groups; IAM: Identity & Access Management; Synapse
Workspace Security with RBAC; ADF Security: RBAC, Owner, Contributor; Azure Synapse SQL Pool
Security: Logins; Creating SQL Logins & Users : master; SQL Users in Azure SQL DB and SQL Pool;
Grant, Control, Revoke: Security Roles; Parameters - Creation and Use in Pipelines; Dynamic
Connections with Credentials; User Name and Password Connectivity; Dynamic Dataset
Configurations; Pipeline Expressions with Parameters; Resource Classes;

Chapter 11: Change Data Capture (CDC)

Change Data Capture (CDC) Data Loads; Incremental Loads with CDC Types; SQL Server CDC : ETL
Load Dates; Pipeline Expression, Data Window; JSON Parameters, Pipeline Scheduling; ETL
Optimization Techniques; Serverless Pool in Azure Synapse; Connections, Use with Serverless
Pool; Using Azure OpenDatasets in Synapse; OPENROWSET and BULK Data Loads; Working with
Parquet Files in Synapse; Python Notebooks (Pyspark) in Synapse;

Part 2 : Azure Data Lake Storage & Stream Analytics

Chapter 1: Azure Fundamentals - Storage
Azure Resources: Storage Components; Storage Resources and Properties; Resource Groups &
Subscriptions; Azure Storage : Files, Tables and ETL; Azure Storage Account & Use; Data Lake
Storage Account (ADLS); Advanced Options: HNS Property; Resource Location, Resource Group;
Azure Portal: Deployment Verification; Azure Portal: Deployment Verification; Storage Account :
Basic Properties; Overview Page: Status, HNS State; Azure Storage : Access Options; Azure Storage
Explorer Tool; Explorer Tool : Configuration; Azure Subscription : Filter Options;

Chapter 2: Azure Storage Operations

BLOB: Binary Large Objects; Storage Browser and Service Pages; Storage Browser: Container
Creation;
Storage Browser: Folder, File Uploads; Service Page: Container Creation; Service Page: Folder, File
Uploads; Container, Folder, File Properties; Limitations with Storage Portal; Azure Data Explorer
Tool : Usage; Contrainer: Creation, Properties; File Uploads, Edits and Access URLs; Azure Storage
Explorer Tool Usage; Azure Account Options in Explorer; Directory Creation, File Operations;
Limitations with Explorer Tool;

Chapter 3: Azure Storage Security, ACLs

Azure Data Lake Storage Security Options; Shared Access Keys: Primary, Secondary; SAS Key
Generation: Container, Tables; SAS Key Permissions, Validation Options; Access Keys: Account
Level Permissions; Azure Active Directory: Users, Groups; Azure AD Security: RBAC, IAM, ACLs;
Owner Role, Contributor, Reader Role; Azure Data Lake Storage Security; ACL : Access Control Lists
& Security; Azure BLOB Storage Containers & ACLs; Folder Level and File Level Security;

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 ACL Permissions: Read, Write, Execute; Access Policy: Creation, Realtime Use; rwacdl; Azure
Principals, CORS;

Chapter 4: SQL Database Migrations

OnPremise SQL Server to Azure Migration; SSMS Tool, SQL Database Installation; SourceDatabase
Scripts & Validations; BACPAC File Generation: SSMS Tool; Table Selection & Advanced Options;
Azure Data Lake Storage, SSMS Access; Azure Storage Container, BACPAC Files; IAM and Account
Key Authentication; Azure SQL Server Creation From Portal; Azure SQL Database Deployment;
DTU : Data Transaction Units, Pricing; Azure Firewall Configuration, Security; Azure SQL Database
Imports (bacpac); Azure SQL Server with ADLS Containers;
Azure SQL DB Migrations, Verification;

Chapter 5: Azure Tables & Replication

Azure Tables - SchemaLess Design; Azure Tables: Creation, Data Inserts; Tables, Entities,
Properties Concepts; Structured, Relational Data Storage; Azure Tables: GUI, Data Types; Azure
Tables: Big Data Imports; Data Edits, Queries, Delete Operations; Odata Options (REST API), End
Points; Azure Storage: Replications, DR Options; LRS: Locally Redundant Storage; GRS: Globally
Redundant Storage; ZRS: Zone Redundant Storage; Replication Options and Advantages;
Replication Verification, Modifications; Storage Endpoints, Failover Partner;

Chapter 6: Azure Stream Analytics, IoT

Azure Stream Analytics; Real-time Data Processing, Events; Ingest, Deliver & Analysis Operations;
Azure Stream Analytics Jobs Concept; Understanding Input, Output Options; SAQL Queries:
Stream Analytics Jobs; IoT: Internet of Things, Real-time Data; Need for IoT Hubs and Event Hubs;
Conditional Split Transformation; IoT Device for Data Inputs; Creating Azure Stream Analytics Job;
Stream Analytics for Historical Data; Azure SQL Database for ASA Jobs; SAQL: Query Formatting;
Historical Data Upload, ASA Jobs; Stream Analytics Job Monitoring;

Chapter 7: Azure Event Hubs

Azure Stream Analytics For API Data; ;IoT Hubs, IoT Devices, Connection Strings; Rasberry APP
Connections with IoT Hub; Azure Storage Account and Container; Creating Azure Stream Analytics
Job; Configuring Input Aliases with IoT Hub; Output Aliases with ADLS Gen 2; SAQL Query, Job
Executions; Monitoring; Azure Event Hubs and Event Instances; Event Hub Namespaces, Partition
Counts; Access Policies, Permissions & Defaults; RootManageSharedAccessKey & Options;
Connection Strings & Event Service Bus; Telco App : Executions & LIVE Data; On-Premise App
Integration, ASA Jobs;

Chapter 8: Storage Architecture, Queues

Azure Storage Account : Architecture; Etag: Replication & Encryption Use; BLOB Types: Block,
Append & Page; Access Tiers: Hot, Cool, Cold Types; Archive Access Tier & Retention; Legal Hold
& Time Bound Access; Pricing : HNS, Security, Encryption; EndPoint URL & Read-Only Use; Azure
File Share Service (Files); Mounting Files From On-Premise; SMB File Share : Hot, Optimized; Azure

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Queue Service & Messages; Message Queues : Operations; Storage Explorer Tool with Shares;
Azure Storage Services: ETL Needs;

Chapter 9: Monitoring & Key Vaults

Azure Monitor, Metrics & Activity Logs; Monitoring Azure Storage Namespaces; Add KQL Metrics;
Account, Blob and File; Total Ingress and Egress Metrics: Charts; Average Latency, Transaction
Count; Request Breakdowns, Signal Logic; Azure Alerts & Conditions, Notifications; Signal Logic
Conditions and Emails; Key Vaults Types: Standard & Premium; Secret Page, Key Backups, Key
Restores; Azure Key Vaults - Name and Vault URI; Inbuilt Managed Key and Azure Key Vault; Key
Vaults Types: Standard & Premium; Secret Page, Key Backups, Key Restores; Managed Identity
with ETL Process;

Part 3: Azure Databricks & Spark, Python

Chapter 1: Azure Intro, Azure Databricks
Azure Cloud : SaaS, PaaS, PaaS & IaaS; Azure Cloud : Storage, ETL Resources; Azure Databricks
: Compute Resources; Need for Azure Databricks (ADB); Azure Databricks : Purpose & Config;
;Azure Databricks Service Creation; Azure Databricks Component; Azure Databricks
Workspace, Usage; Spark Cluster Configurations, Capacity; Driver Nodes, Worker Nodes in
Spark; Cluster Types : Personal, Unrestricted; CPU, Memory & IO Resources; Virtual Machines
(VM) for Clusters; Databricks : Runtime & DBFS Storage; DBFS : Files, Tables with Spark DB;

Chapter 2: SparkDatabase, SQL Notebooks

DBFS : File Uploads from ON-Premise; Creating Spark Tables; Spark DB; Data Explorer: HIVE
Metastore; Data Explorer: Spark Database, Tables; Notebooks: SQL, Python and Scala; Creating
SQL Notebooks in Databricks; Creating User Defined Spark Databases; Connecting / Using Spark
Databases; Spark SQL : Big Data Loads; Spark SQL : Database & Table List; Spark SQL : Data
Aggregations, Jobs; Spark SQL : Data Analytics, Reports; Analytics: X, Y Axis, Group By;
Notebooks : Export, Import, Clone; Notebooks : Storage & Versions;

Chapter 3: Python Intro, Data Loads

Python : Introduction, Real-time Use; Python For ETL and DWH; Python For Azure: Data
Engineer; Python Data Frames & Purpose; Python Dataframes – Pandas; Python with Spark
Integrations; PySpark for DDL and ETL; PySpark Versus SQL Notebooks; Reading DBFS Data into
Spark; Creating Dataframes for ETL; Temporary Views & Dataframes; Spark Temp Views:
Aggregations; Spark Table Loads, HIVE Data; [Link](); Spark Parquet Tables;

Chapter 4: PySpark with ADLS

Azure Storage Account : Creation; Azure Data Lake Storage : HNS; Creating Containers in ADLS;
BLOB File Uploads / Generation; Account Key : Access Key / SAS Key; BLOB Access URL for
Databricks; WASBS URL for PySpark Notebook; Generating PySpark Script; PySpark Connection
Variables; Databricks : Data Import Scripts; Config Options with ADLS, Spark; [Link] (),
Session Context; DataFrames with Temp Tables; Escape Sequence ; HIVE & Spark DB;

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Chatper 5: PySpark Widgets & Spark
Widgets : Notebook Parameters; [Link] module : Text, Combo; Dropdown, Multi Select
Parameters; dbutils help(), get() & remove(); Dataframes, Spark SQL @ Variables; Python Data
Frames, Spark SQL; Reading Parameters Values; Parameters Versus Variables; Using
Parameters For Temp Tables; Using Parameters for Spark Tables; Data Storage and HIVE
Metastore; Reading Parameterized Data; Format Strings with PySpark; Dynamic Queries with
Spark SQL; Aggregations and f Strings;

Chapter 6: Architecture, Workflows

Driver Nodes, Worker Nodes, DBUs; RDD : Resilent Data Distribution; DAG : Directed Acyclic
Graph; Hadoop HDES and Spot Instance; Cluster Manager, Master Node; RDDS, Worker,
Excecutor & Slave; Hadoop HDES & Databricks Runtime; Databricks Optimization Techniques;
Spot Instance, Photon Acceleration; All Purpose Cluster, Job Cluster; Databricks Jobs: Creation
& Tasks; Jobs with Parameters, Executions; Task Dependency & Notifications; Continuous &
Manual Schedules; Active Jobs, Recent Run Jobs, Monitor;

Chapter 7: Databricks Security, Scala

Azure Databricks Security Operations; Azure Active Directory (Azure AD); AD Users and RBAC
with IAM;
Owner, Contributor & Reader Roles; Workspace Admin Permissions; Notebook Permissions &
Share;
Workflow Security, HTTP Path; User Tokens & ServerName; Scala : Differences with PySpark;
Scala : Variables Declaration, Usage; SparkSQL with Scala Notebooks; Temp Views with Scala
Notebooks;
Aggregations with Scala Notebooks; Visual Data Analytics with Scala; PySpark to Scala
Conversions;

Chapter 8: Scala with ADLS, Azure SQL

Data Imports with Azure SQL DB; Using Scala for Big Data Loads; Spark SQL Queries @ Temp
Views; Variables, display(), [Link](); Scala Transformations, display(); JSON, AVRO and
DBFS Mounts; [Link] @ ADLS; [Link]() & JVM; JDBC Connection,
DataframeWriter; Data Extraction, SQLContext; Spark Context and Spark Session;
SQLServerDriver with Scala; ADLS with Scala Notebooks; ;Parameters (Widgets) with Scala;

Chapter 9: DeltaLake Incr Loads, DWH

Azure DeltaLake Implementation; ACID Properties, Upsert Advantages; Delta Engine
Optimizations & Uses; Pipeline Creation: JSON Files in DBFS; Delta Tables Creation, Data Loads;
Spark Cluster Settings: Auto Optimize; Auto Compact, Delta Table Optimize; JSON Files, Delta
Streaming Location; Joins and Merge with Delta Tables; Incremental Loads, Delta Tables;
Create & Use DWH with Databricks; Upsert (Merge) with Spark Tables; Big Data & Jupyter
Notebooks; Databricks with Data Factory (ADF); End to End Implementations;
Real-time Project (End to End)

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Online Retail / Travel Database Data Source; Azure Migrations and ETL Concepts; Azure SQL Pool
(Synapse DWH) Tables; Apache Spark Pool: Databases, Tables; Azure Data Lake Storage (ADLS Gen
2); Handling Unstructured Data in ADF; End to End Workflows, Automations; Azure Logic Apps:
Automated Workflows; Visual Designer & Prebuild Templates; Server Less Integrations in Azure;
Workflow, Triggers and Actions; Managed Connectors, Integrations; ARM Template: Deployments;
ARM Templates: ADF, ADLS; ADLS with Spark Databases; Aggregations with Big Data Loads;
Parameterized ETL Sources; Parameterization & Workflows; Python Notebooks to Scala; Azure SQL
DB Connections; ARM Templates & JSON; Project Requirement; Project Solution, FAQs; Concept wise
FAQs; Resume Guidance; Mock Interviews (1 to 1); DP 203 Certification Guidance; DP 203 Sample
Papers (Latest);

Azure Data Engineering with Power BI (For Power BI Registrations)

Power BI with Synapse SQL Pool; Power BI with Synapse Analytics; Get Data: Storage Modes; Direct
Query, Performance Inspector; Aggregated Data Analytics; Data Gateways: Auto Refresh; Power BI
with ADLS: Record Query; Power BI with ADLS: BLOB Data; Power BI with Spark DB: JDBC; Power BI
with Spark DB : User Tken; Power BI with Spark DB : LIVE Data; Power BI with Spark DB: Refresh;

Azure Purview: Data Governance; Unified SaaS for Multi Cloud; Data Mapping and Resilence;
Automated Data Discovery; Sensitive Data Labels : SQL Server; Interactive Data Lineage; Trusted
DataDiscovery in Azure; Confidential Data & Trust; DataCatalog, Data Estate Insights; Azure Key
Vaults, ADLS Security; Azure Passwords, Keys, Certificates; Azure Key Vaults - Name, Vault URI;
Managed Key & ETL Connections;

Module 2: Database Basics, SQL, T-SQL Queries

Applicable for Azure Data Engineer Plans B, C

Chapter 1: DATABASE INTRODUCTION

Databases Introduction & Purpose; Database Types : OLTP, DWH, OLAP; Microsoft SQL Server
Advantages, Use; SQL Server Components and Usage; Microsoft SQL Server - Career Options;
Developer, DBA, Data Engineer; Data Analyst, Data Scientist Careers; SQL : Purpose, Real-time
Usage Options;SQL Versus Microsoft T-SQL [MSSQL]; Course Plan, Real-time Project, Resume;
24 x 7 Online Lab for Remote DB Access; Versions and Editions of SQL Server; SQL Server Pre-
requisites : S/W, H/W; System Configuration Checker Tool;

Chapter 2: SQL SERVER INSTALLATIONS

SQL Server & SSMS Installation Plan; SQL Server Pre-requisites : S/W, H/W; SQL Server 2022 &
2019 Installation; Database Engine Feature, OLTP; Instances : Types and Properties; Default
Instance, Named Instances; Service and Service Account Use; Authentication Modes and Logins;
Windows Logins and SQL Logins; SQL Server Management Studio; Server Connections with SSMS
Tool; Local and Remote Connections; System Databases: Master and Model; MSDB, TempDB,
Resource Databases;

Chapter 3: SSMS Tool, SQL BASICS - 1

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Creating Databases: Files [MDF, LDF]; Creating Tables in User Interface; Data Insertion & Report
in User Interface; SQL : Purpose and Real-time Usage; SQL Versus T-SQL : Basic Differences;
DDL, DML, SELECT, DCL and TCL; Creating SSMS Sessions : SPID; Create, Connect Databases using
SQL; Creating Tables with INT, CHAR; Data Storage, Inserts - Basic Level; Table Data Verifications
with Select; SELECT Statement for Table Retrieval; Identify Databases and Tables; Identify
Sessions and Session ID;

Chapter 4: SQL BASICS - 2

Creating Tables: VARCHAR, FLOAT; Single Row Inserts, Multi Row Inserts; Rules for Data Insertion
Statements; SELECT with WHERE Conditions; AND and OR Operators Usage; IN Operator and NOT
IN Operator; Between, Not Between Operators; LIKE and NOT LIKE Operators; ORDER BY, TOP &
OFFSET; Basic Sub Queries with SELECT; UPDATE Statement & Conditions; DELETE & TRUNCATE
Statements; ALTER, ADD COLUMN Statements; DROP Statements: Table, Database;

Chapter 5: SQL BASICS - 3, T-SQL Introduction

Database Objects : Tables and Schemas; Schemas : Group Tables in Database; Schemas : Security
Management Object; Creating Schemas & Batch Concept; Using Schemas for Table Creation;
Data Storage in Tables with Schemas; Data Retrieval & Usage with Schemas; Table Migrations
across Schemas; Import and Export Wizard in SSMS; Data Imports with Excel File Data;
Performing Bulk Operations in SSMS; Temporary Tables : Real-time Use; Local and Global
Temporary Tables; # and ## Prefix, Scope of Usage;
Chapter 6: CONSTRAINTS & INDEXES BASICS
Constraints and Keys - Data Integrity; NULL, NOT NULL Property on Tables; UNIQUE KEY
Constraints: Importance; PRIMARY KEY Constraint: Importance; FOREIGN KEY Constraint:
Importance; REFERENCES, CHECK & DEFAULT; Candidate Keys and Identity Property; Database
Diagrams and ER Models; Relationships Verification and Links; Indexes : Basic Types and Creation;
Index Sorting and Search Advantages; Clustered and NonClustered Indexes; Primary Key and
Unique Key Indexes; Need for Indexes - working with Keys;
Case Study 1: Database Design with Tables, Constraints, Keys & Relations

Chapter 7: JOINS and TSQL Queries

JOINS - Table Comparisons Queries; INNER JOINS For Matching Data; OUTER JOINS For (non)
Match Data; Join Queries with "ON" Conditions; Left Outer Joins - Example Queries; Right Outer
Joins - Example Queries; FULL Outer Joins: Realtime Scenarios; CROSS JOIN and CROSS APPLY;
One-way, Two way Data Comparisons; Using Table Aliases & Column Aliases; Optimizing Join
Queries with Indexes; Choosing Correct Comparison Columns; Joining Unrelated Tables in TSQL;
Self References, Self Joins in TSQL;

Chapter 8: GROUP BY in T-SQL, Views Basics

GROUP BY: Importance, Realtime Use; GROUP BY Queries and Aggregations; Group By Queries
with Having Clause; Group By Queries with Where Clause; Using WHERE and HAVING in T-SQL;

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Group By with Joins in TSQL; Query Execution Order & Aliases; Joins with Sub Queries,
Formatting; Database Objects: Overview & Usage; Views: Types, Usage in Real-time; Creating,
Executing & Verifying Views; Storing Queries in Database Views; Excel Analytics - Joins & Views;
Excel Office Data Connection Reports;

Chapter 9: Functions, Procedures Basics

Functions with SQL Server, TSQL; Scalar, Inline, Table Functions; Variables: Declare, Real-time
Use; Creating, Executing Functions; Functions for Computations; Functions for Parameterized
Joins; Procedures: Usage in Real-time; Using Parameters in SQL Server; Parameterized Joins in
TSQL; Compilation with Stored Procedures; sp_help, sp_helptext, sp_helpindex; sp_helpdb,
sp_rename, sp_recompile; System Views & Metadata Audits;

Chapter 10: TRIGGERS & TRANSACTIONS

Triggers - Purpose, Real-world Usage; FOR/AFTER Triggers - Real time Use; INSTEAD OF Triggers
- Real time Use; INSERTED, DELETED Memory Tables; Using Triggers for Data Replication; Enable
Triggers and Disable Triggers; Database Level, Server Level Triggers; Transactions : Types, ACID
Properties;Transaction Types and AutoCommit; EXPLICIT & IMPLICIT Transactions; COMMIT and
ROLLBACK Statements; Batch Concept and Go Statement; Open Transactions in Real-time; Using
Conditional Commits, Rollbacks;
Chapter 11: Normal Forms, Cursors
First Normal Form and Atomicity; Third Normal Form and MVD Property; Boycee-Codd Normal
Form : BNCF; Fourth Normal Form : Advantages; Self Reference Keys and 4 NF Usage; 1:1, 1:M,
M:1, M:M Relationship Types; Linked Servers Configurations, RPC; Linked Servers, Remote Joins
in TSQL; 2 Part, 3 Part, 4 Part Naming Styles; Remote Joins Queries and Aliases; Cursors - Basics,
Data Operations; Cursors - Life Cycle & Declaration; Cursors Types, FETCH Operations; Cursors -
Deallocate, Real-world Use;
Chapter 12: TSQL Queries, SQL Analytics
IIF() Function with SELECT Query; CASE. WHEN..THEN..ELSE; WHEN MATCHED, NOT MATCHED;
Incremental Loads, Upsert Statement; Stored Procedures: Merge Statement; UNION and UNION
ALL Operator; Window Functions: Rank, Dense Rank; Row_Number, PartitionBy in TSQL;
Duplicate Row Identification, Deletion; Grouping, Cube, Rollup, Lag, Lead; Data Types: Numerical,
Date, Time; Data Types: Characters, Real, Float; Date & Time Functions, DateAdd; String
Functions, Concat, SubString;
Case Study 2: Joins with Group By, Sub Queries, Views, Excel Analytics

Module 3: Power BI (Reports, Cloud, Server, Analytics)

Applicable for Azure Data Engineer Plan C

Chapter 1 : POWER BI INTRODUCTION

Power BI : Introduction to Analytics; Power BI Tools Suite, Advantages; Power BI : Career
Options, Plan; Power BI Developer Job Role; Microsoft Data Analyst Job Role; Big Data Analyst

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Job Role; Power BI Data Analyst (PL 300); ;Data Engineer*, Power BI (DP 500 *); Artificial
Intelligence (AI) Visuals; AI Enabled Power BI Features; Course - Lab Plan with Design Tools;
Need for Power Query & DAX; Power BI Licensing Types; Power BI – Advantages;
Chapter 2 : BASIC REPORT DESIGN
Power BI Eco System: Architecture; Data Sources & Types in Real-world; Report Types:
Interactive, Paginated; Analytical Reports & Mobile Reports; Data Sources : File, Database,
Web; Visualizations : Report Shapes; Power BI Design Tools, Requirements; Power BI Desktop
Tool : Installation; Desktop Interface: Canvas; Data View, Report View; In-Memory Xvelocity
Database; Labels, Legend, Category; Local Store: PBIX & PBIT Files; Data Points and Tooltips;
Chapter 3 : Visual Interaction, Visual Sync
Visual Interaction with Data Points; Disabling / Enabling Interactions; Edit Interactions: Format
Options; Spotlight and Focus Mode; Report Export to CSV, PDF; Tooltip Options and Usage;
Working with Pages in PBI; Rename, Duplicate, Hide Pages; Slicer Visual : Real-time Usage;
Orientation, Selection Properties; Slicer Settings : Tiles & Slider; Single & Multi Select, Header;
Number, Text, Show Summary; Date Slicer and Value Selections; Slicer List, Dropdowns & Clear;
Visual Sync Limitations with Slicer;
Chapter 4 : Grouping & Hierarchies
Grouping : Visuals with Pdf Sources; List Grouping and Binning Options; Grouping Static / Fixed
Data Values; Grouping Dynamic / Changing Data; Bin Size and Bin Limits (Max, Min); Bin Count
and Grouping Options; Group with Bins & Clustering; Group, Layer with Selection Pane;
Creating Hierarchies in Power BI; Independent, Dependant Drill-Down; Drill-Down with
Interactive Reports; Conditional Drilldowns, Data Points; Drill Up Buttons and Operations;
Expand & Show Next Level; Dynamic Data Drills Limitations;

Chapter 5 : Filters & Bookmarks

Filters : Types and Usage in Real-time; Visual Filter, Page Filter, Report Filter; Basic, Advanced
and TOP N Filters; Category and Summary Level Filters; Data / Drill Options, DrillThru Filters;
Keep All Filters" Options in DrillThru; CrossReport Filters, Include, Exclude; Drill-thru Filters,
Page Navigations; Bookmarks : Report Navigations; Buttons, Images with Actions; Selection
Pane, Actions, Text URLs; Show Data and See Records; Custom Tooltips, Table Visual; Table Vs
Matrix : Drill-downs; Styles, Cell Properties, Databars; Conditional Formatting, Divergent;

Chapter 6 : Big Data Access, Visuals

OLTP Databases, Big Data Sources; Azure Database Access, Reports; Import, Direct Query &
Dual Mode; Data Modeling: Do Not Summarize; Data Modeling: Currency, Relations; Power BI
Archtiecture, Eco System; Power BI Interface for Reports; Stacked Chart, Clustered Chart; Line
Chart, Area Chart, Bar Chart; 100% Stacked Bar & Column Chart; Map Visuals: Tree, Filled,
Bubble; Small Multiples, Legends, Axis; Cards, Funnel, Table, Matrix; Scatter Chart : Play Axis,
Labels; Waterfall Chart, Multi Row Cards;

Chapter 7 : POWER QUERY LEVEL 1

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Power Query M Language Purpose; Power Query Architecture and ETL; Data Types, Literals
and Values; Power Query Transformation Types; Table & Column Transformations; Text &
Number Transformations; Date, Time and Structured Data; let, source, in statements @ M
Lang; Get Data, Table Creations and Edit; ETL Operations with Power Query; Merge
Transformations in Power BI; Join Kinds: Inner, Outer & Apply; Union All Transformation &
Appends; Power Query Editor, Step Edits; Close & Apply Options. Report Design;

Chapter 8 : POWER QUERY LEVEL 2

Query Duplicate, Query Reference; Group By and Advanced Options; Aggregations with Power
Query; Transpose, Header Promotion; Reverse Rows and Row Count; Data Type Changes &
Detection; Replace Columns: Text, NonText; Advanced Query Edit Options; Replace Nulls: Fill
Up, Fill Down; Pivot, Unpivot Transformations; Move Column and Split Column; Extract, Format
and Numbers; Date & Time Transformations; Derive Year, Quarter, Month, Day; Add Column :
Query Expressions; Query Step Inserts and Step Edits;

Chapter 9 : POWER QUERY LEVEL 3

Big Data Loads : Parameter Queries; Creating Parameters in Power Query; Parameter Data
Types, Default Lists; Static & Dynamic Lists: List Queries; Convert Tables to Lists, Use Cases;
Linking Parameters to Queries; Testing Parameters with Canvas; Multi-Valued Parameter Lists;
Creating Lists in Power Query; Converting Lists to Table Data; Invoke Function, Type
Conversions; Function Query & Parameter List; Columns From Examples, Indexes; Conditional
Columns, Expressions; Disable / Enable Data Loads;

Chapter 10: POWER BI CLOUD - 1

Power BI Cloud Components; App Workspaces, Report Publish; Reports & Related Datasets
Cloud; Creating New Reports in Cloud; Report Publish, Report Uploads; Report Edits and New
Reports; Report Actions: Downloads; Dataset Usage Options in Cloud; Dashboards Creation
and Usage; Pining Visuals and Report Pages; Visual Pin Actions in Dashboards; Dashboard &
LIVE Interactions; Media Tiles: Images, Custom Links; Q & A; Pin with Q & A; Standard Visuals;

Chapter 11 : POWER BI CLOUD - 2

Report Actions : Share, Subscribe; Report Actions : Lineage, Embed; Report Actions : Export
Options; Report Actions : Public User Access; Dashboard Actions : Share, Subscribe; Dashboard
Actions : Themes, Lineage; Dashboard Actions : Share, Subscribe;Favorite, Insights, Embed
Code; Gateways Configuration, PBI Service; Gateway Types, Cloud Connections; Gateway
Cluster, Add Data Sources; Data Refresh : Manual, Scheduled; Power Query Parameters,
Gateways; DataFlows; Lineage, Share, Subscribe, Insights; Performance Inspector& Gateways;

Chapter 12 : POWER BI CLOUD - 3

Workbooks : Excel Online & Pins; Power BI Apps: Creation & Usage; Power BI Segments,
Content; Navigation Screens, Audience; App Publish, Verification & Edits; Export, Share &
Subscribe; List & Lineage; Power BI Scorecards; Paginated Reports - Design & Usage; Power BI

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Report Builder Tool; Microsoft Report Builder Tool; Report Builder : Datasets, Charts; Report
Builder : Bar Charts, Fields; Report Builder : RDL Files; Paginated Reports : Deployments;

Chapter 13 : DAX Functions - Level 1

DAX : Importance in Real-time; DAX Data Types, Syntax Rules; DAX Measures and Columns;
ROW Context and Filter Context; Operators, Special Characters; DAX Functions, Vertipaq
Engine; DAX Cheat Sheet : Expressions; Data Analytics with DAX; DAX Measures : Expressions;
ISBLANK, IF, IN, SUM; SUMX, AVG, AVERAGEX; Data Models: Fact, Dimensions; Detecting
Relations for DAX; Star & Snowflake Schemas; Data Modeling Options in DAX;

Chapter 14 : DAX Functions - Level 2

Quick Measures in Power BI; Average and Filtered Average; Running Totals, EARLIER( );
RELATED, COUNTROWS CALCULATE Function Conditions; ALL Members Scope & IN; Account
and Time Calculations; Star Rating, DAX Expressions; Data Modeling Options in DAX; 1:1, 1:M
and M:1 Relations; Working with Facts & Measures; Modeling : Missing Relations;
Relationships & Importance; Modeling : Relation Management; Modeling with Multiple Keys;

Chapter 15 : DAX Functions - Level 3

DAX : Variables and Expressions; Dynamic Expressions, RETURN; Current Value, Previous Value;
SELECTED VALUE, Joins; FORMAT Function with DAX; RELATED, Joins in DAX; DAX Expressions
with SQL DB; Time Intelligence Functions; Date Dimension : Generation; CALENDAR(),
DATESYTD(); TOTALYTD, TOTALQTD; TODAY, DATE, DAY with DAX; SELECTEDVALUE, FORMAT;
Date, Time and Text Functions;

Chapter 16: DAX Functions - Level 4

RLS: Row Level Security; Data Models in Power BI Desktop; DAX Roles Creation and Testing;
DAX Expressions & Operators; PBIX Uploads: Power BI Cloud; Dataset Security with DAX Roles;
Entity Sets and Slicing in DAX; Dataflows with Power BI; Analytical Reports - DAX Usage;
Creating Data Models with DAX; Datasets in Excel and Dashboards; Using Excel Analyzer in
Power BI; Power BI Data Source in Excel; Connection Strings and Refresh; Analytical Reports;

Chapter 17: Power BI Report Server

Power BI Report Server Config; SQL Server Instance Verifications; Report Server DB, Temp
Database; WebService & WebPortal URL; Uploading Interactive Reports; End User Report
Share (pdf); Power BI Desktop RS Tool; Interactive Reports: Report Server; Mobile Reports :
Design Options; Mobile Reports : Grids, Elements; Mobile Reports : Uploads, Edits; Paginated
Reports : Deployments; Paginated Vs Interactive Reports; Paginated Vs Analytical Reports;
Paginated Vs Mobile Reports; Power BI Report Server Vs Cloud;

Chapter 18 : Power BI Admin & AI

Power BI Cloud Management;Power BI Admin : Alerts; Workspace Management, Users;
Security: Report, Dataset Levels; Security: Dataset, App Levels; Security: Workspace Options;
PBI Performance Inspector; Power BI & Artificial Intelligence; Power BI & CoPilot Add-Ins; AI

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)
 Visuals & Big Data Analytics; Smart Narrative and Q & A; Infographics, Icons and Labels; Key
Influencer Visual in Power BI; Metrics Visual, Performance; Paginated Reports Visual;

Chapter 18: Real-time Project [Sales & Customers]

Phase 1 : Basic Report Design
Project Requirement Analysis; Requirement Gathering, FSA; Report Design with Excel; Basic Data
Modelling; Infographics, Histograms; Analytics and Formating;

Phase 2: SME Level

Report Design with SQL DB; SQL Database: Joins, Views; ;Dual Storage Mode, SQL Queries; Data
Modeling, Power Query; Dynamic Connections, Azure DB; Parameters and M Lang Scripts;

Phase 3: Deployments (Cloud, Server)

DAX Requriements, Analysis; Cloud and Report Server; Custom Visualizations; 3party Visuals & REST
API *; Project FAQs and Solutions; One - One Resume, Mock Interview;

Resume, Project Oriented FAQs and Solutions

Email : contact@[Link] Call Us (India) : 24 x 7

Skype: SQL School Training Institute +91 9666 44 0801
Website: [Link]
Trainer Contact: +91 9030040801
saiphanindrait@[Link]

[Link] For Free Demo: Reach us on +91 9666 44 0801 or +1 956.825.0401 (24x7)