Ch 1: SQL Database Job Roles

• Database Intro
• OLTP, DWH, OLAP
• DBMS Basics
• Data Stack 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: 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 Projects (For Resume)
Domain: 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 IAM, Azure KeyVaults
• Azure Databricks with Data Factory

Ch 1: Introduction to Snowflake

• Database, DWH Introduction
• Cloud Data Warehouse
• Cloud DWH Implementations
• Snowflake Cloud Intro
• Snowflake: SaaS Platform

Ch 2: Snowflake Concepts

• Snowflake Account (Cloud)
• Snowflake Components
• Snowflake Editions, Credits
• Snowflake Editions
• Virtual Private Edition (VPS)
• Snowflake Pricing

Ch 3: Architecture, Warehouse

• Compute Architecture
• Shared Disk Architecture
• CPU & Memory in Clusters
• Database Query & Data Cycle
• ColumnStore, Virtual Warehouse
• Classic UI with Snowflake
• Massively Parallel Processing

Ch 4: SQL Concepts – 1

• SQL Concepts & Basics
• Snowflake Databases
• Snowflake Tables
• DDL Operations
• DML Operations
• Select Operations

Ch 5: SQL Concepts – 2

• Snowflake Databases
• Snowflake Tables
• Data Imports
• SQL Operators
• Important Functions

Ch 6: SQL Concepts – 3

• Snowflake Data Types
• Snowflake Date Vaues
• Snowflake Text Data
• Snowflake Numeric Data
• Snowflake Type Conversions

Ch 7: Snowflake Databases & Tables

• Snowflake Database Types
• Snowflake Table Types
• Retention Time, Connections
• Permanent, Transient Types
• CREATE TABLE AS SELECT (CTAS)

Ch 8: Time Travel, Recovery

• Time Travel in Snowflake
• Invoking Time Travel Feature
• Timestamp, Offset, Query ID
• Data Recovery, TIMESTAMP
• Fail Safe and UNDROP, OFFSET
• Transient Tables, Real-time

Ch 9: Schemas and Session Context

• Schema Creation Usage
• Permanent, Transient Schemas
• Managed Schemas in Snowflake
• Invoking Schemas & Cloning
• Session Context & Schema
• Data Loading with GUI

Ch 10: Snowflake Cloning

• Cloning with Snowflake
• Zero Copy, Schema Cloning
• Snapshot, Metadata
• Storage & Metadata Layer
• Real-time Considerations
• Transactions & Injection

Ch 11: Procedures & Views

• Procedures and Functions
• SQL and JavaScript & CALL
• SQL Text:command
• Cursoring Data and Operations
• Dynamic DML with SPs
• RETURN, RETURNS Statements

Ch 12: Security Management

• Security with Snowflake
• Users & Roles in Snowflake
• Privileges and Groups
• Organization, Account, Users
• Creating, Using Roles, Users
• System Defined Roles Usage
• Role Hierarchy in Realtime
•  Views For Security
• RBAC & DAC in Real-time

Ch 13: Snowflake Transactions

• Transaction ACID Properties
• Implicit, Explicit and Auto
• Durability and Data Storage
• current transaction() Usage
• to_timestamp_ltz and Usage
• Failed Transactions with SPs
• Transactions and SPs
• Scoped & INNER Transactions

Ch 14: Snowflake Streams & Audits

• Snowflake Streams & Usage
• Streams and DML Auditing
• Snapshot Creation, Offset
• METADATA Options & Streams
• Auditing DML Operations
• Data Flow & Snowflake Streams
• Streams on Transient Tables
• Time Travel with Stream Tables

Ch 15: Snowflake Tasks

• Tasks, Serverless Compute
• Tasks Tree: Root and DAG
• Tasks Schedules and RESUME
• User & Snowflake Managed
• CRON Syntax with Tasks
• Virtual Warehouse Concepts
• Multi Cluster Warehouse
• Auto Scale Options, Billing

Ch 16: SnowSQL and Variables

• SnowSQL Configurations
• DDL, DML & SELECT
• SnowSQL Command Line
• Variables and Batch Process
• DECLARE, LET, BEGIN & END
• EXECUTE IMMEDIATE, FOR
• Creating Virtual Warehouse
• Writing Output to Files

Ch 17: Snowflake Partitions, Stages

• Snowflake Partitions, Views
• Micro Partition with DML, CDC
• Cluster Key, Depth and Overlap
• Internal Partition Types & Usage
• List, Range and Hash Partitions
• Snowflake Stages, Types
• Internal and External Stages
• COPY Command, Bulk Loads

Ch 18: Azure / AWS External Stages

• Azure Storage Account, BLOB
• SAS: Shared Access Signature
• Using SAS Key and FILE PATH
• Azure Storage with BLOB
• COPY INTO Command Usage
• Snowflake Patterns & RegEx
• File Formats: Creation, Usage

Ch 19: Snow Pipes & Incr Loads

• SnowPipe Incremental Loads
• Azure Active Directory
• External Stage, Enterprise AD
• Snow Pipes and Data Loads
• Incremental Data Loads
• File Format with Reg Expr

Ch 20: Power BI with Snowflake

• Power BI: Big Data Analytics
• Snowflake Data Access, Views
• Datawarehouse Access, Views
• Server URL & View Access
• Data Analytics with Views

Snowflake Data Engineer Projects (For Resume)
Domain: HealthCare / Ecommerce Domain
Skills Gained:

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