Ch 1: SQL Database Job Roles

• Introduction to Data
• Database Intro, Types
• OLTP, DWH, OLAP
• DBMS Concepts
• Database Job Roles
• Data Engineer Job Roles

Ch 2: Database Intro & Installations

• SQL Server Installations
• Instance Concepts
• Authentication Types
• Authentication Modes
• SSMS Tool Installation
• Connections, Authentications

Ch 3: SQL Basics V1 (Commands)

• Creating Databases (GUI)
• Creating Tables, Columns (GUI)
• SQL Basics (DDL, DML, etc..)
• Creating Databases, Tables
• Data Inserts (GUI, SQL)
• Basic SELECT Queries

Ch 4: SQL Basics V2 (Commands, Operators)

• DDL: Create, Alter, Drop, Add
• DML: Insert, Update, Delete
• DQL: Select, Fetch
• SQL Operators
• pecial Operators

Ch 5: Excel Data Imports

• Data Imports with Excel
• Order By: Asc, Desc
• Order By with WHERE
• TOP & OFFSET
• UNION, UNION ALL

Ch 6: Schemas & Batches

• Schemas: Creation, Usage
• Schemas & Table Grouping
• Real-world Banking Database
• 2 Part, 3 Part & 4 Part Naming
• Batch Concept & “Go” Command

Ch 7: Constraints, Keys & RDBMS

• Null, Not Null Constraints
• Unique Key Constraint
• Primary Key Constraint
• Foreign Key & References
• Default Constraint & Usage
• DB Diagrams & ER Models

Ch 8: Normal Forms & ERD

• Normal Forms: 1 NF, 2 NF
• 3 NF, BCNF and 4 NF
• Self Referencing Keys
• Cascading Keys
• Database Diagrams

Ch 9: Joins Queries – Level 1

• Joins: Table Comparisons
• Inner Join & Outer Joins
• Cross Join & Cross Apply
• Table Combination
• Table & Column Aliases

Ch 10: Joins Queries – Level 2

• Group By & Aggregations
• Joins with Group By
• 3 Table, 4 Table Joins
• Join Queries with Aliases
• WHERE & HAVING
• Query Execution Order

Ch 11: Sub Queries

• Distinct & Union, Union All
• Sub Queries Concept
• Sub Queries & Aggregations
• Joins with Sub Queries
• Correlated Queries

Ch 12: Views & Data Analytics

• Views: Realtime Usage
• Storing SELECT in Views
• DML, SELECT with Views
• RLS: Row Level Security
• Data Analytics with Excel
• Important System Views

Ch 13: Stored Procedures – Level 1

• Stored Procedures: Realtime Use
• Procedures with SELECT
• System Stored Procedures
• Metadata Access with SPs
• Stored Procedures, Tuning

Ch 14: Stored Procedures – Level 2

• Merge Statement
• Upsert Operations with Merge
• Merge with OLTP & DWH
• Matched and Not Matched
• Merge Statement inside SPs

Ch 15: Functions – Level 1

• Using Defined Functions (UDF)
• Scalar Functions in Real-world
• Table Valued Functions
• Parameterized Queries
• Returns and Return
• SP Versus Functions

Ch 16: Functions – Level 2

• Aggregated Functions
• Date & Time Functions
• String Functions
• Window Functions
• Rank, Row_Number
• DenseRank, Partition By

Ch 17: Triggers & Automations

• Need for Triggers in Real-world
• DDL & DML Triggers
• For / After Triggers
• Instead Of Triggers
• Memory Tables with Triggers
• Disabling DMLs & Triggers

Ch 18: Transactions & ACID

• Transaction Concepts in OLTP
• Auto Commit Transaction
• Explicit Transactions
• COMMIT, ROLLBACK
• Lock Hints & Query Blocking
• READPAST, LOCKHINT

Ch 19: Indexes Basics, Tuning

• Indexes & Tuning
• Clustered Index, Primary Key
• Non Clustered Index & Unique
• Creating Indexes Manually
• Composite Keys, Query Optimizer
• Composite Indexes & Usage

Ch 20: CTEs & Tuning

• Common Table Expression
• Creating and Using CTEs
• CTEs, In-Memory Processing
• IIF(), CASE Statement
• Cube( ) and Rollup( )
• Sub Totals & Grand Totals
• Grouping( ) & Usage

Ch 21: Data Types & Variables

• Integer Data Types
• Character, MAX Data Types
• Decimal & Money Data Types
• Boolean & Binary Data Types
• Date and Time Data Types
• SQL_Variant Type
• Variables in SQL
• Cursor Variable & Fetch

Ch 22: Temp Tables

• Local Temp Tables
• Global Temp Tables
• Testing Temp Tables
• .INTO Statement
• Bulk Copy Operations

Ch 23: SQL Server Architecture

• Network Protocols
• Query Execution Engine
• Parser, Compiler, Checkpoint
• SQL Manager, DB Manager
• Storage Engine, Locks
• SQL OS Components

Ch 24: Real-Time SQL Server Case Studies (2)

✅ Healthcare Management System

• Patient Records Management
• Doctor Appointment Scheduling
• Billing & Insurance Processing
• Medical Reports Analysis

✅ E-Commerce Database

• Customer & Product Management
• Order Processing
• Inventory Tracking
• Sales Reporting

Part 1: Fundamentals, ADF & Synapse

Ch 1: Azure Fundamentals

• Cloud Introduction
• Azure Concepts
• Cloud Implementations: IaaS, PaaS, SaaS
• Azure Account, Subscription
• Azure Resources & Resource Groups
• Azure ETL & DWH Resources
• Azure Storage, IoT Resources

Ch 2: Azure Deployments, Azure SQL 

• Azure SQL Server, SQL DB
• Azure SQL Database (OLTP)
• Azure SQL Pool (DWH)
• Connections from SSMS Tool
• Source Data Configurations

Ch 3: Azure Synapse (DWH)

• Synapse Pool Architecture
• Control Node, Compute Node
• DMS (Data Movement Service)
• Connection Strings
• Pause / Resume SQL Pool
• Scale Up / Scale Down

Ch 4: Azure SQL Pool Operations (DWH)

• Creating Tables with TSQL
• Partitioned Tables
• Distributions
• DOP Concept
• Big Data Loads with TSQL

Ch 5: Azure Data Factory (ADF)

• Need for ADF & Pipelines
• Data Orchestration with IR
• Integration Runtime Engine
• Linked Services, Datasets
• Pipelines: Copy Data Activity
• Data Flow Activity with IR

Ch 6: Azure SQL DB Loads 

• ADF: Author, Azure SQL DB Reads
• Azure SQL Pool Writes
• Synapse Analytics with IR
• Pipeline Design, Validation
• Pipeline Runs, Monitoring

Ch 7: Pipeline Settings

• ADF Pipeline Settings
• Staging: Advantages
• Reliable Logging
• Best Effort Logging
• DIU & DOCP with IR
• Compressions, Health Check

Ch 8: File Incremental Loads

• File Incremental Loads
• Storage Account, Data Lake
• Binary Copy, Schema Drift
• Staging Concept in ADF
• Initial, Incremental Loads
• Schema & Data Changes

Ch 9: Table Incremental Loads

• Implement SCD with ADF
• Self Hosted IR: Realtime Use
• On-premise Data: Incr Loads
• Copy Method: Upsert, Keys
• Staging & ADF Optimizations
• Pipeline Runs, Activity IDs

Ch 10: ADF: Data Flow – 1

• Data Flow Concepts
• Data Flow Protypes
• Data Flow Workflow
• Data Flow Transformations
• Spark Clusters
• Optimized Clusters, Preview
• ADF Debug Options

Ch 11: ADF Data Flow – 2

• Creating Data Flow Items
• Using Multiple Sinks
• Conditional Split Transformation
• SELECT Transformation
• Sort, Union Transformations
• Pipelines with Data Flow

Ch 12: ADF Data Flow – 3

• Working with Multiple Tables
• Join Transform, Broadcast
• Row Filters, Column Filters
• Surrogate Keys, Derived Cols
• ETL Loads Dates, Sink Options
• Aggregated Data Loads

Ch 13: ADF Data Flow – 4

• Pivot Transformation
• Group By & Pivot Keys
• Column Pattern, Deduplicate
• Lookup, Cached Lookup
• Tuning Transformations
• Tuning Data Flow, Spark

Ch 14: ADF Data Flow – 5

• Lookup Transformation
• Cache Lookup
• Inline Datasets
• Data Validations
• Lookup Versus Joins

Ch 15: ADF Metrics, Alerts

• Azure Insights
• Azure Metrics for ADF
• Azure Metrics for Synapse
• CPU, Memory Metrics
• Alerts and Notifications
• Action Groups, Tuning Options

Ch 16: ADF with Azure Functions

• Azure Functions
• Function Activity in ADF
• Linked Services
• Pipeline Debug
• ADF Activity Controls

Ch 17: ADF Optimizations

• Synapse SQL Pool Partitions
• ADF Partitions
• Broadcast Options
• Staging, Logging
• DIU, DOCP
• Spar Cluster Optimizations

Ch 18: ADF Parameters, Security

• Linked Service Parameters
• Creating Logins
• Users and ETL Permissions
• Parameterize Logins
• Parameterize Users
• Dynamic Linked Services

Ch 19: SCD & ETL with Control Tables – 1

• ADF Templates in Realtime
• Implementing ADF SCD
• Table Incremental Loads
• Creating Control Tables
• Creating Watermark Columns
• Creating ETL Stored Procedures

Ch 20: SCD & ETL with Control Tables – 2

• ADF Lookup Activity
• Delta Data Expressions
• SP Activity & Parameters
• Control Tables, Watermarks
• Pipeline Parameters, SPs
• Dynamic Data Sets, SCD

Ch 21: Synapse Analytics

• Azure Synapse Analytics
• Synapse Deployments
• Synapse Configurations
• ADLS Containers
• Workspace Server Setup
• Synapse Studio (GUI)

Ch 22: Synapse: Dedicated SQL Pools

• Dedicated SQL Pools
• BLOB Data Imports
• Data Source Creations
• TSQL Queries
• Big Data Analytics

Ch 23: Synapse: Serverless Pools

• Serverless Pools
• Serverless Architecture
• Serverless Vs Dedicated Pools
• BLOB Data Imports
• OPENROWSET Operations
• Big Data Analytics

Ch 24: Synapse: Apache Spark Pools

• Apache Spark Pools
• Spark Cluster Concepts
• Nodes and Executors
• PySpark Notebooks
• Notebook Operations
• BLOB Data Imports
• Big Data Analytics
• Pipeline Integrations

Ch 25: CDC in ADF

• CDC: Change Data Capture
• Using CDC in ADF
• CDC Source Configurations
• Incremental Loads with CDC
• New Rows, Net Changes
• CDC Advantages & Performance

Part 2: Databricks

Ch 1: Databricks Introduction

• Cloud ETL, DWH 
• Cloud Computing 
• Databricks Concepts 
• Big Data in Cloud 
• LakeHouse & Spark Compute 

Ch 2: Databricks Architecture

• Unity Catalog, Volume
• Spark Clusters
• Apache Spark and Databricks
• Apache Spark Ecosystem
• LakeHouse Architecture
• Hadoop, MapReduce, Apache Spark

Ch 3: Unity Catalog

• Unity Catalog Concepts
• Workspace Objects
• Databricks Notebooks
• Databricks Workspace UI
• Organizing Workspace Objects
• Creating Volumes
• Spark Table Creations
• Spark UI: Limitations

Ch 4: Spark SQL: Basics

• Spark SQL Notebooks
• Creating Catalog
• Creating Schemas
• Creating Tables
• Spark Data Types
• PySpark API: SQL Queries
• Dropping Objects
• Notebooks: Exports, Clone

Ch 5: Spark SQL: Table Types

• Delta Tables
• Managed Tables
• External Tables
• Data Partitioning
• Union, Views in Spark
• External Volumes

Ch 6: Spark SQL: Functions

• Math, Sort Functions
• String, DateTime Functions
• Conditional Statements
• SQL Expressions with expr()
• Volume for our Data Assets
• File Formats, Schema Inference
• Spark SQL Aggregations

Ch 7: Spark SQL: Time Travel

• Time Travel Concepts
• Spark DB: Logical Architecture
• Spark DB: Physical Store
• Data File & Log File Store
• Time Travel
• DESCRIBE, EXTENDED
• HISTORY, Version Numbers

Ch 8: Python: Introduction, Print

• Python Introduction
• Python Versions
• Python Implementations
• Python in Spark (PySpark)
• Python Print()
• Single, Multiline Statements

Ch 9: Python: Variables

• Python Variables
• Variable Declarations
• Variable Values
• Value Types
• Multi Variable Values
• Common Variable Values
• Realtime use of Variables

Ch 10: Python: Operators

• Need for Operators
• Arithmetic Operators
• Assignment Operators
• Comparison Operators
• Operator Precedence
• Operands in Python

Ch 11: Python: Control Statements

•  Python Control Structures
• If … Else Statement
• Short Hand If
• ELIF & ELSE IF Statements
•  OR, AND Concepts
• Python Loops

Ch 12: Python: Data Types

• Python Data Types
• Integer / Int Data Types
• Float, String Data Types
• List Data Type
• Dictionary Data Type
• Tuple Data Type
• List Items, Indexes
• Tables Versus Dictionaries

Ch 13: Python: Modules & Dataframes

•  Python Modules
• Pandas
• NumPy
•  Dataframe Concepts
• Handling Nulls
• Data Cleansing Concepts
• Pandas Series, arrays
• Indexes, Indexed Lists

Ch 14: PySpark Concepts

• Constructing Dataframes
• Single List Dataframes
• Multi List Dataframes
• Pandas Dataframes
• Contact & Union
• Merge
• Join Options with Dataframes

Ch 15: Medallion Architecture – 1

• Medallion Architecture
• Aggregated Data Loads
• Bronze, Silver and Gold
• Temp Views
• Spark Tables (Parquet)
• Work with File Sources

Ch 16: Medallion Architecture – 2 

• Medallion Architecture
• Azure SQL DB Connections
• Joining Source Tables
• Dataframes, Temp Views
• Aggregated Data Loads
• Gold Data Consumption

Ch 17: Delta Lake 

• Databricks Delta Lake
• Schema Evolution
• Azure SQL DB Connections
• Delta Table API
• Deleting Records
• Updating Records
• Merging Records
• Old History Retention
• Delta Transaction Log

Ch 18: PySpark: Widgets 

• PySpark Parameters
• Text Widgets
• User Parameters
• Manual Executions
• Automations
• UI & JSON For Widgets

Ch 19: Lake Flow Jobs 

• Worksflows & CRON
• Job Compute, Running Tasks
• Python Script Tasks
• Parameters into Notebook Tasks
• Parameters into Python Script Tasks
• Concurrent Executions, Dependencies
• Branching Control with the If-Else Task

Ch 20: Pyspark: Auto Loader – 1 

• Auto Loader Concept
• Cloud files Architecture
• Checkpoint Configurations
• Creating Directories
• Reading Databricks Cloud Sources
• Initial Loads

Ch 21: PySpark: Auto Loader – 2 

• Reading Streams with Auto Loader
• Reading a Data Stream
• Manually Cancel your Data Streams
• Writing to a Data Stream
• Schema Evaluation Modes
• Adding New Columns
• Workspace Modules

Ch 22: Lake Flow Declarative Pipelines 

• SDP: Spark Declarative Pipelines
• Delta LIVE Tables
• Streaming Data Loads
• Bronze, Silver, Gold Data
• Materialized Views
• Pipeline Clusters
• Databricks CLI
• Data Quality Checks

Ch 23: Databricks Optimizations 

• Lazy Evaluation
• Explain Plan
• Caching
• Data Shuffling
• Broadcast Joins
• Partitions
• Data Skipping
• Z Ordering
• Liquid Clustering
• VACUUM
• OPTIMIZE

Ch 24: Security Concepts 

• Overview of ACLs
• Adding a New User to Workspace
• Workspace Access Control
• Cluster Access Control
• Groups & LakeBridge
• Access Keys (Tokens)

Ch 25: AI Assisted Cloud Data Engineering 

• GitHub Copilot
• Databricks Genie
• Genie Assistant (AI)
• AI Assisted ETL Development

Ch 26: Azure Databricks 

• Azure Cloud Concepts 
• Azure Databricks 
• Azure Regions, Pricing Tiers 
• Azure Databricks Workspace 
• Classic Deployment 
• Driver Nodes, Worker Nodes 
• DBR Versions, RDD & DAG 
• Open Source Databricks Vs Azure Databricks

Ch 27: Databricks Data Engineer Associate Exam 

• Databricks Data Engineer Associate Exam
• AVRO Formats
• Exam Pattern & Guidance
• Exam Q & A, Scenarios

Ch 1: Azure Databricks with Data Factory

• Connecting ADF with Databricks
• ADF: Notebook Activity
• Comparing ADF with Databricks
• When to use ADF?
• When to use Databricks?
• How to use Databricks and ADF together?

Ch 2: GitHub Concepts

• Creating Github Account
• GIT Project Concept
• GIT Project Creation
• GIT: Main, Branches
• Connecting with ADF
• Connecting with Databricks

Ch 3: Azure DevOps For Data Engineers

• Azure DevOps Repos
• Azure Boards
• Azure Pipelines
• Release Pipelines
• CI/CD for ADF
• CI/CD for Databricks
• Environment Promotion (Dev, QA, UAT, Prod)

Project Objective:

Build an end-to-end Azure Data Engineering solution to process, transform, and analyze e-commerce business data from multiple sources.

Technologies Used:

• Azure Data Factory (ADF)
• Azure Data Lake Storage (ADLS Gen2)
• Azure Databricks (Apache Spark)
• Azure SQL Database
• Azure Blob Storage
• Azure Monitor
• Azure Purview
• Azure Monitor Logs

Skills Gained:

• Data Ingestion & ETL Development
• Azure Data Factory Pipelines
• Databricks & PySpark Transformations
• Data Lake Architecture
• Medallion Architecture (Bronze/Silver/Gold)
• Real-Time Industry Experience

Components For Project (From Resume Perspective):

• Source Systems
• Bronze
• Silver
• Gold
• ADF Pipelines
• Synapse Analytics
• PySpark
• Power BI Reporting
• Monitoring
• Alerting
• CI/CD
• Deployment
• End to End Integrations

1. Azure Data Fundamentals (DP 900)

• Exam Pattern
• Exam Q & A, Scenarios

2. Azure Databricks (DP 750)

• Exam Pattern
• Exam Q & A, Scenarios

3. Databricks Data Engineer Associate Exam

• Exam Pattern
• Exam Q & A, Scenarios

Microsoft Fabric Concepts

• Fabric Architecture
• Fabric ETL Components
• Fabric One Lake Components
• Fabric Analytics Components

 Microsoft Fabric Implementation

• Fabric Workspace
• Fabric Warehouse Creation
• Fabric Lakehouse Creation

Microsoft Fabric Migrations

• Azure SQL Pool to Fabric Migrations
• Azure Data Factory with Fabric Pipelines
• Azure Versus Fabric Implementations