Ch 1: SQL Database Job Roles

• Database Intro
• OLTP, DWH, OLAP
• DBMS Basics
• Data Engineer Job Roles

Ch 2: Database Intro & Installations

• SQL Server Installations
• Instance & Collations
• SSMS Tool Installation
• Connections, Authentications

Ch 3: SQL Basics V1 (Commands)

• 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
• DML: Insert, Update, Delete
• DQL: Select, Fetch
• Add, Truncate Statements
• SQL Operators

Ch 5: Data Types & Variables

• Integer Data Types
• Character, MAX Data Types
• Decimal & Boolean Data Types
• Date and Time Data Types
• SQL_Variant Type

Ch 6: Data Imports From Files

• Data Imports with Excel
• Data Imports with CSV
• Auto Detection of DataTypes
• OLE-DB Connections
• Order By, TOP, OFFSET

Ch 7: Sub Queries

• Basic Sub Queries
• Aggregations
• Combining Queries
• UNION, UNION ALL

Ch 8: Schemas & Batches

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

Ch 9: Constraints, Keys & RDBMS

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

Ch 10: Normal Forms & RDBMS

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

Ch 11: Joins & Queries

• Joins: Table Comparisons
• Inner Joins & Matching Data
• Outer Joins: LEFT, RIGHT
• Full Outer Joins & Aliases
• Self Joins & Aliases

Ch 12: Views & RLS

• Views: Realtime Usage
• DML, SELECT with Views
• Excel Analytics with Views
• Important System Views

Ch 13: Stored Procedures – 1

• Stored Procedures: Realtime Use
• Parameters Concept with SPs
• Procedures with SELECT
• System Stored Procedures
• Stored Procedures, Tuning

Ch 14: Stored Procedures – 2

• Merge Statement (Upsert)
• Merge with OLTP & DWH
• Matched and Not Matched
• Merge Statement inside SPs
• SP Recompilations

Ch 15: User Defined Functions – 1

• Scalar Functions in Real-world
• Inline & Multiline Functions
• Parameterized Queries
• Variables & Parameters
• Function Executions

Ch 16: User Defined Functions – 2

• Date & Time Functions
• String Functions & Queries
• Aggregated Functions & Usage
• Window Functions (Rank)
• Row_Number, DenseRank
• Partition By & Order 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: Group By Queries

• Group By, Distinct
• GROUP BY, HAVING
• Cube( ) and Rollup( )
• Sub Totals & Grand Totals
• Grouping( ) & Usage

Ch 19: Joins with Group By

• 3 Table, 4 Table Joins
• Join Queries & WHERE
• Join Queries & Group By
• IIF(), CASE Statement
• Query Execution Order

Ch 20: Transactions & ACID

• Auto Commit Transaction
• Explicit Transactions
• COMMIT, ROLLBACK
• Checkpoint & Query Blocking
• READPAST, LOCKHINT

Ch 21: Indexes Basics, Tuning

• Clustered Index, Primary Key
• Non Clustered Index
• Query Optimizer
• Tuning Join Queries
• Tuning Group By Queries

Ch 22: CTEs & Tuning

• Common Table Expression
•  CTEs for Data Retrieval
• CTEs for DML Operations
• CTEs for Data Cleansing
• Using CTEs with Row Number

Ch 23: Cursors & Temp Tables

• Cursors & Fetch
• Scroll, Absolute Types
• Local Temp Tables
• Global Temp Tables
• SELECT..INTO Statement

Ch 24: SQL Server Architecture

• Network Protocols
• Storage Engine
• Query Processing Engine
• Query Execution Order
• SQL OS Components

Ch 25: Capstone Project (HealthCare Management System)

• ECommerce Database
• Data Validations
• Query Writing
• Query Tuning
• Excel Analytics

Part 1: Azure Data Factory & Synapse
Ch 1: Azure Fundamentals

• Cloud Introduction, Azure
• Cloud Implementations
• Azure Account, Subscription
• Azure ETL & DWH Resources
• Azure Storage, IoT Resources

Ch 2: Azure Storage & ADLS

• Azure Storage Account
• Azure Data Lake Storage
• Azure BLOB Containers
• Blob File Uploads
• Azure Tables

Ch 3: Azure Deployments, Azure SQL

• Azure SQL Server, SQL DB
• Azure SQL Database (OLTP)
• Azure Firewall Rules
• Connections from SSMS Tool

Ch 4: Azure SQL DB Migrations

• On-Premise SQL DB, bacpac
• Azure SQL Deployment
• Azure Storage from SSMS
• Azure SQL DB Migration
• Migration Verifications

Ch 5: Azure Synapse (DWH)

• Synapse Pool Architecture
• Control Node, Compute Node
• DMS (Data Movement Service)
• Azure DWH Tables: Partitions
• Distributions & MAXDOP

Ch 6: 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 7: 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 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: Pipeline Settings

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

Ch 10: 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 11: ADF Data Flow – 1

• Creating Data Flow Items
• Using Multiple Sinks
• Conditional Split Transformation
• Select, Sort, Union, Loops
• ADF Debug Options

Ch 12: ADF Data Flow – 2

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

Ch 13: ADF Data Flow – 3

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

Ch 14: ADF Data Flow – 4

• Get Metadata
• IF & Lookup Transformation
• Cache Lookup
• 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
• Schemas, ETL Permissions
• Logins Parameters in ADF
• Dynamic Linked Services

Ch 19: SCD & ETL with Control Tables

• ADF Templates in Realtime
• Incremental Loads (SCD)
• Control Tables, Watermarks
• Lookup Activity, Delta Queries
• SP Activity & Parameters
• Pipeline Parameters, SPs

Ch 20: Azure Key Vaults

• Azure Key Vaults
• Access Policies
• Secret Management
• Managed Identity
• Key Vault Integrations

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

• Creating Dedicated SQL Pools
• BLOB Data Imports
• TSQL Queries, Data Imports
• Big Data Analytics

Ch 23: Synapse: Serverless Pools

• Serverless Pools, TSQL
• Serverless Architecture
• OPENROWSET Operations
• Big Data Analytics

Ch 24: Synapse: Apache Spark Pools

• Apache Spark Pools
• Nodes and Executors
• Big Data Analytics
• Pipeline Integrations

Part 2: Databricks (Spark, PySpark, Big Data, Genie AI)
Ch 1: Databricks Introduction

• Cloud ETL, DWH
• Cloud Computing
• Databricks Concepts
• Databricks Account
• Big Data in Cloud

Ch 2: Databricks Architecture

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

Ch 3: Unity Catalog

• Unity Catalog Concepts
• Databricks Workspace UI
• Organizing Workspace Objects
• Volumes, File Uploads
• Managed & External Tables
• Spark UI: Limitations

Ch 4: Spark SQL: Basics

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

Ch 5: Spark SQL: Functions

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

Ch 6: Spark SQL: Time Travel

• Time Travel Concepts
• Spark DB: Logical Architecture
• Spark DB: Physical Store
• Time Travel, History
• DESCRIBE, EXTENDED
• Version Numbers

Ch 7: Python Introduction, Print

• Python Introduction
• Python Versions
• print() & display()
• Single, Multiline Statements

Ch 8: Python Variables

• Python Variables
• Declarations, Values
• Multi Variable Values
• Common Variable Values
• Realtime use of Variables

Ch 9: Python Operators

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

Ch 10: Python Control Statements

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

Ch 11: Python Data Types

• Python Data Types
• Integer / Int Data Types
• Float, String Data Types
• List Data Type
• Dictionary Data Type
• Tuple Data Type

Ch 12: Python Modules & Dataframes

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

Ch 13: PySpark Concepts

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

Ch 14: Medallion Architecture – 1

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

Ch 15: Medallion Architecture – 2

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

Ch 16: Delta Lake

• Databricks DeltaLake
• Schema Evolution
• Dataframes, Temp Views
• Delta Table API
• Update, Delete Records
• Merging Records
• Old History Retention
• Delta Transaction Log

Ch 17: PySpark: Widgets

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

Ch 18: Lake Flow Jobs

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

Ch 19: Pyspark: Auto Loader – 1

• AutoLoader Concept
• Cloudfiles Architecture
• Checkpoint Configurations
• Creating Directories
• Reading Databricks Cloud Sources
• Initial Loads

Ch 20: 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 21: Lake Flow Declarative Pipelines

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

Ch 22: Databricks Optimizations

• Lazy Evaluation
• Data Shuffling
• Broadcast Joins
• Data Skipping
• Z Ordering
• Liquid Clustering
• VACUUM
• OPTIMIZE

Ch 23: Databricks Security, AI

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

Ch 24: Azure Databricks

• Databricks Deployment Modes
• Classic Deployments
• Azure Databricks Workspace
• Databricks Compute
• Scaling & Tuning & AI
• Open Source Databricks Vs Azure Databricks

Ch 25: GitHub Concepts

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

Part 3: Data Engineer Project 1 (For Resume)
Project 1: ECommerce Platform

Project Objective:
Build an end-to-end Azure Data Engineering solution to process, transform, and analyze ecommerce business data from multiple sources.
Skills Gained:

• Data Ingestion & ETL Development
• Azure Data Factory Pipelines
• Data Orchestration (End to End)
• Data Lake Architecture, RBAC
• Real-Time Industry Experience
• Azure IoT, Stream Analytics
• Azure Databricks with Data Factory

Components For Project (From Resume Perspective):

• ADF Pipelines
• Databricks Notebooks
• Synapse Analytics
• Apache Spark Schemas
• Power BI Reporting
• CI/CD Deployment
• End to End Integrations
• IAM & Managed Identity

Project Title
Retail Sales Analytics Platform using Databricks (Medallion Architecture)

Project Overview
Students will build a production-style data platform using Lakehouse Architecture
(Bronze, Silver, and Gold), implementing modern ETL practices, data quality validation,
incremental processing, orchestration, optimization, and reporting.

Business Scenario
A multinational retail company receives daily data from multiple operational systems. The
company wants to build a centralized analytics platform to:

• Consolidate data from different business domains
• Improve data quality and consistency
• Track sales performance across regions
• Analyse customer purchasing behaviour
• Monitor inventory levels
• Generate business KPIs for decision-makers
• Deliver Power BI dashboards with near real-time insights