Azure Data Engineering

Azure Data Engineering and Data bricks with power bi

Chapter 1: Cloud Basics, Azure SQL

Cloud Introduction and Azure Basics:

1. Azure Implementation: IaaS, PaaS, SaaS
2. ADE Job Roles; Azure Storage Components
3. Azure ETL & Streaming Components; Need for Azure Data Factory (ADF)
4. Need for Azure Synapse Analytics; Azure Resources and Resource Types
5. Azure Account, Subscription (Free); Azure SQL Server [Logical Server]; Firewall Rules
6. Azure SQL Database & SQL Pool Deployment; DTU Versus DWU; SSMS Connections

Chapter 2: Synapse SQL Pools (DWH)

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

Chapter 3: Azure Data Factory, Pipelines

ADF Concepts; ADF Pipelines

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

Chapter 4: OnPremise Data Loads, Upsert

Copy Data Tool

1. Incremental Loads; On-Premise Data Sources with Azure; Self Hosted Integration Runtime (IR); Access Keys, Remote Linked Service
2. Synapse SQL Pool (DW), on Premise; ETL Staging with Storage Account
3. Copy Method: Polybase – Tuning; Polybase : Big Data Loads
4. ETL Pipelines for Incremental Loads; Business Keys For Table Upsert; Pipeline Schedules with ADF; ETL Logging with Storage Account
5. 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

1. Tumbling Window; Execution Retry and Delay Options; Binary Copy,
2. Structural Data Loads; Incremental Loads Verification Tests
3. Incompatible Rows & Fault Tolerance; Pipeline Compression & Tuning; Pipeline Publish, Monitor Options
4. 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

1. Data Flow Task, Data Flow Activity; Transformations with Data Flow
2. Spark Cluster for Debugging; Cluster Node Configurations; Spark Cluster Types & Sizing; Transaction Optimized – Capacity
3. 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

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

Chapter 8: Azure Synapse Analytics

1. Azure Synapse Analytics Resource; Azure Synapse Analytics Workspace; Managed
   Resource Group, SQL Account; Synapse Workspace & Synapse Studio; Operations with
   Synapse Workspace; DLS Gen 2 Storage Account, Container;
2. 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

1. Synapse Pipelines: Performance Advantage; Pivot Transformation For Normalization;
   Generate Pivot Column, Aggregations; Pivot Transformation & Pivot Setting; Pivot Key
   Selection, Value and Nulls
2. Pivoted Columns & Column Pattern; Column Prefix, Help Graphic, Metadata;
   Denormalized Data and Aggregations; Apache Spark Pool in Azure Synapse
3. Spark Cluster Nodes: Vcores,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

1. 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
2. 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)

1. 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 Open Datasets in Synapse;
2. OPENROWSET and BULK Data Loads; Working with Parquet Files in Synapse; Python
   Notebooks (Pyspark) in Synapse

AZURE DATABRICKS

1. Azure Cloud : SaaS, PaaS, PaaS & IaaS; Azure Cloud : Storage, ETL Resources; Azure
   Databricks; Compute Resources; Need for Azure Databricks (ADB)
2. 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
3. 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

1. 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
2. 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

1. 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;
   dataframe.write.format(); Spark Parquet Tables

Chapter 4: PySpark with ADLS

1. 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; spark.config (), Session Context; DataFrames with Temp
   Tables; Escape Sequence ; HIVE & Spark DB

Chatper 5: PySpark Widgets & Spark

1. Widgets : Notebook Parameters; dbutils.widget module : Text, Combo; Dropdown, Multi
   Select Parameters; dbutils help(), get() & remove()
2. 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
3. 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

1. 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;
2. 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

1. 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
2. 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

1. Data Imports with Azure SQL DB; Using Scala for Big Data Loads; Spark SQL Queries
   @ Temp Views; Variables, display(), spark.read(); Scala Transformations, display();
   JSON, AVRO and DBFS Mounts; fs.azure.sas.container @ ADLS; dataframe.write.jdbc()
   & JVM; JDBC Connection, DataframeWriter; Data Extraction, SQLContext
2. Spark Context and Spark Session; SQLServerDriver with Scala; ADLS with Scala
   Notebooks; ;Parameters (Widgets) with Scala

Chapter 9: DeltaLake Incr Loads, DWH

1. 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
2. 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

POWERBI

Ch 1: POWER BI INTRODUCTION

1. Power BI : Introduction to Analytics
2. Power BI Tools Suite, Advantages
3. Power BI : Career Options, Plan
4. Power BI Developer Job Role
5. Microsoft Data Analyst Job Role
6. Big Data Analyst Job Role
7. Power BI Data Analyst (PL 300)
8. Data Engineer*, Power BI (DP 500 *)
9. Artificial Intelligence (AI) Visuals
10. AI Enabled Power BI Features
11. Course – Lab Plan with Design Tools
12. Need for Power Query & DAX
13. Power BI Licensing Types
14. Power BI Cloud – Advantages
15. Power BI Report Server Advantages

Ch 2: Basic Report Design

1. Power BI Eco System: Architecture
2. Data Sources & Types in Real-world
3. Report Types: Interactive, Paginated
4. Analytical Reports & Mobile Reports
5. Data Sources : File, Database, Web
6. Visualizations : Report Shapes
7. Power BI Design Tools, Requirements
8. Power BI Desktop Tool : Installation
9. Desktop Interface: Overview, Canvas
10. Get Data, Data View, Report View
11. In-Memory Xvelocity Database
12. Basic Visuals: Table, Tree Map
13. Data Labels, Legend, Category
14. Local Store: PBIX & PBIT Files
15. Data Points and Tooltips

Ch 3: Visual Interaction, Visual Sync

1. Visual Interaction with Data Points, Disabling / Enabling Interactions
2. Edit Interactions: Format Options, Spotlight and Focus Mode
3. Report Export to CSV, PDF, Tooltip Options and Usage
4. Working with Pages in PBI, Rename, Duplicate, Hide Pages
5. Slicer Visual : Real-time Usage, Orientation, Selection Properties
6. Slicer Settings : Tiles & Slider, Single & Multi Select, Header
7. Number, Text, Show Summary,Date Slicer and Value Selections,
8. Slicer List, Dropdowns & Clear

Ch 4: Grouping & Hierarchies

1. Grouping : Visuals with Pdf Sources, List Grouping and Binning Options
2. Grouping Static / Fixed Data Values, Grouping Dynamic / Changing Data
3. Bin Size and Bin Limits (Max, Min),Bin Count and Grouping Options
4. Group with Bins & Clustering, Group, Layer with Selection Pane
5. Creating Hierarchies in Power BI, Independent, Dependant Drill-Down
6. Drill-Down with Interactive Reports,Conditional Drilldowns, Data Points
7. Drill Up Buttons and Operations,Expand & Show Next Level
8. Dynamic Data Drills Limitations

Ch 5: Filters & Bookmarks

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

Ch 6: Big Data Access, Visuals

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

Ch 7: POWER QUERY LEVEL 1

1. Power Query M Language Purpose
2. Power Query Architecture and ETL
3. Data Types, Literals and Values
4. Power Query Transformation Types
5. Table & Column Transformations
6. Text & Number Transformations
7. Date, Time and Structured Data
8. let, source, in statements @ M Lang
9. Get Data, Table Creations and Edit
10. ETL Operations with Power Query
11. Merge Transformations in Power BI
12. Join Kinds: Inner, Outer & Apply
13. Union All Transformation & Appends
14. Power Query Editor, Step Edits
15. Close & Apply Options. Report Design

Ch 8: POWER QUERY LEVEL 2

1. Query Duplicate, Query Reference
2. Group By and Advanced Options
3. Aggregations with Power Query
4. Transpose, Header Promotion
5. Reverse Rows and Row Count
6. Data Type Changes & Detection
7. Replace Columns: Text, NonText
8. Advanced Query Edit Options
9. Replace Nulls: Fill Up, Fill Down
10. Pivot, Unpivot Transformations
11. Move Column and Split Column
12. Date & Time Transformations
13. Derive Year, Quarter, Month, Day
14. Add Column : Query Expressions
15. Query Step Inserts and Step Edits

Ch 9: POWER QUERY LEVEL 3

1. Big Data Loads : Parameter Queries
2. Creating Parameters in Power Query
3. Parameter Data Types, Default Lists
4. Static & Dynamic Lists: List Queries
5. Convert Tables to Lists, Use Cases
6. Linking Parameters to Queries
7. Testing Parameters with Canvas
8. Multi-Valued Parameter Lists
9. Creating Lists in Power Query
10. Converting Lists to Table Data
11. Invoke Function, Type Conversions
12. Function Query & Parameter List
13. Columns From Examples, Indexes
14. Conditional Columns, Expressions
15. Disable / Enable Data Loads

Ch 10: POWER BI CLOUD – 1

1. Power BI Cloud Components
2. App Workspaces, Report Publish
3. Reports & Related Datasets Cloud
4. Creating New Reports in Cloud
5. Report Publish, Report Uploads
6. Report Edits and New Reports
7. Report Actions: Downloads
8. Dataset Usage Options in Cloud
9. Dashboards Creation and Usage
10. Pining Visuals and Report Pages
11. Visual Pin Actions in Dashboards
12. Dashboard & LIVE Interactions
13. Media Tiles: Images, Custom Links
14. Q & A Option with Dashboards
15. Pin with Q & A; Standard Visuals

Ch 11: POWER BI CLOUD – 2

1. Report Actions : Share, Subscribe
2. Report Actions : Lineage, Embed
3. Report Actions : Export Options
4. Report Actions : Public User Access
5. Dashboard Actions : Share, Subscribe
6. Dashboard Actions : Themes, Lineage
7. Dashboard Actions : Share, Subscribe
8. Favorite, Insights, Embed Code
9. Gateways Configuration, PBI Service
10. Gateway Types, Cloud Connections
11. Gateway Cluster, Add Data Sources
12. Data Refresh : Manual, Scheduled
13. Power Query Parameters, Gateways
14. DataFlows, Power Query in Cloud
15. Lineage, Share, Subscribe, Insights
16. Performance Inspector& Gateways

Ch 12: POWER BI CLOUD – 3

1. Workbooks : Excel Online & Pins
2. Power BI Apps: Creation & Usage
3. Power BI Segments, Content
4. Navigation Screens, Audience
5. App Publish, Verification & Edits
6. Export, Share & Subscribe
7. List View & Lineage View Options
8. Power BI Scorecards: Realtime Use
9. Paginated Reports – Design & Usage
10. Power BI Report Builder Tool
11. Microsoft Report Builder Tool
12. Report Builder : Datasets, Charts
13. Report Builder : Bar Charts, Fields
14. Report Builder : Creating RDL Files
15. Paginated Reports : Deployments

Ch 13: DAX Functions – Level 1

1. DAX : Importance in Real-time
2. DAX Data Types, Syntax Rules
3. DAX Measures and Columns
4. ROW Context and Filter Context
5. Operators, Special Characters
6. DAX Functions, Vertipaq Engine
7. DAX Cheat Sheet : Expressions
8. Data Analytics with DAX
9. DAX Measures : Expressions
10. ISBLANK, IF, IN, SUM
11. SUMX, AVG, AVERAGEX
12. Data Models: Fact, Dimensions
13. Detecting Relations for DAX
14. Star & Snowflake Schemas
15. Data Modeling Options in DAX

Ch 14: DAX Functions – Level 2

1. Quick Measures in Power BI
2. Average and Filtered Average
3. Running Totals, EARLIER( )
4. RELATED, COUNTROWS
5. CALCULATE Function Conditions
6. ALL Members Scope & IN
7. Account and Time Calculations
8. Star Rating, DAX Expressions
9. Data Modeling Options in DAX
10. 1:1, 1:M and M:1 Relations
11. Working with Facts & Measures
12. Modeling : Missing Relations
13. Relationships & Importance
14. Modeling : Relation Management
15. Modeling with Multiple Keys

Ch 15: DAX Functions – Level 3

1. DAX : Variables and Expressions
2. Dynamic Expressions, RETURN
3. Current Value, Previous Value
4. SELECTED VALUE, Joins
5. FORMAT Function with DAX
6. RELATED, Joins in DAX
7. DAX Expressions with SQL DB
8. Time Intelligence Functions
9. Date Dimension : Generation
10. CALENDAR(), DATESYTD()
11. TOTALYTD, TOTALQTD
12. TODAY, DATE, DAY with DAX
13. SELECTEDVALUE, FORMAT
14. Date, Time and Text Functions

Ch 16: DAX Functions – Level 4

1. RLS: Row Level Security
2. Data Models in Power BI Desktop
3. DAX Roles Creation and Testing
4. DAX Expressions & Operators
5. PBIX Uploads: Power BI Cloud
6. Dataset Security with DAX Roles
7. Entity Sets and Slicing in DAX
8. Dataflows with Power BI
9. Analytical Reports – DAX Usage
10. Creating Data Models with DAX
11. Datasets in Excel and Dashboards
12. Using Excel Analyzer in Power BI
13. Power BI Data Source in Excel
14. Connection Strings and Refresh
15. Analytical Reports – Limitations

Ch 17: Power BI Report Server

1. Power BI Report Server Config
2. SQL Server Instance Verifications
3. Report Server DB, Temp Database
4. WebService & WebPortal URL
5. Uploading Interactive Reports
6. End User Report Share (pdf)
7. Power BI Desktop RS Tool
8. Interactive Reports: Report Server
9. Mobile Reports : Design Options
10. Mobile Reports : Grids, Elements
11. Mobile Reports : Uploads, Edits
12. Paginated Reports : Deployments
13. Paginated Vs Interactive Reports
14. Paginated Vs Analytical Reports
15. Paginated Vs Mobile Reports
16. Power BI Report Server Vs Cloud

Ch 18: Power BI Admin & AI

1. Power BI Cloud Management, Power BI Admin : Alerts
2. Workspace Management, Users
3. Security: Report, Dataset Levels, Security: Dataset, App Levels
4. Security: Workspace Options
5. PBI Performance Inspector
6. Power BI & Artificial Intelligence, Power BI & CoPilot Add-Ins
7. AI Visuals & Big Data Analytics
8. Smart Narrative and Q & A
9. Infographics, Icons and Labels
10. Key Influencer Visual in Power BI, Metrics Visual, Performance
11. Paginated Reports Visual