Video summary

Spotify End-To-End Azure Data Engineering Project (From Beginner To Pro)

Main summary

Key takeaways

Educational

Main ideas / lessons conveyed

  • Goal of the project: Build a complete, industry-style end-to-end Azure data engineering portfolio project (inspired by a “Spotify” dataset) to become competitive for Azure data engineering roles.
  • Core technologies/tools covered (end-to-end):
    • Azure SQL Database
    • Azure Data Factory (ADF) for ingestion and pipeline orchestration
    • Azure Data Bricks for transformations (Spark, Structured Streaming, Autoloader)
    • Unity Catalog for governance/metadata management
    • Delta Live Tables / Lakeflow-style declarative pipelines for SCD Type 2 and data quality patterns
    • CI/CD + versioning: Git branches, publishing, and deployment practices
    • Logic Apps for alerting/monitoring (email on pipeline failure)

Data modeling approach

Use Medallion architecture:

  • Bronze (raw) → ingest into Data Lake using ADF
  • Silver (enriched) → transform/standardize in Databricks (dimensions + fact structure)
  • Gold (curated) → star schema + SCD Type 2 with Delta Live Tables

Key engineering concepts emphasized

  • Incremental ingestion (don’t reload all 1B+ rows)
  • Backfilling / backdated refresh (rerun for a specific historical interval)
  • Watermarking using stored state in JSON files inside the data lake
  • Parameterization / metadata-driven-ish pipelines (reuse logic across multiple tables)
  • Optimization to avoid creating unnecessary empty files
  • SCD Type 2 handling (history with start/end dates and “current/expired” records)
  • Autoloader “exactly once” / checkpointing for streaming ingestion into silver
  • Expectations (data quality checks) in Delta Live Tables

Methodology / step-by-step workflow (as presented)

1) Portfolio project pitch + prerequisites

Build a single project that covers many Azure/Databricks competencies:

  • ADF ingestion with dynamic/incremental/backfill pipelines
  • Databricks transformations with Autoloader + Spark Structured Streaming
  • Star schema + SCD Type 2
  • Git branch workflow + publishing/deployment
  • Unity Catalog configuration
  • Alerting via Logic Apps
  • Databricks asset bundles for deployment

2) Define the architecture (data flow)

  • Source: Azure SQL Database tables
  • ADF step: load data into Bronze layer in ADLS Gen2
  • Databricks step: read Bronze and write Silver layer transformations (dimensions/fact)
  • Gold step: create curated outputs with Delta Live Tables (SCD Type 2 + curated results)

3) Project requirements (what the pipelines must support)

Implement:

  • Incremental processing (process only new records)
  • Backfilling/backdated refresh (rerun from a chosen start date)
  • Stream processing pattern (avoid batch-only assumptions)
  • Custom utilities / reusable code (Python utilities/functions)
  • Metadata-driven / parameterized reusability
  • Git CI/CD standards:
    • main branch protected
    • develop in feature branches
    • merge via PR
    • publish from main

4) Azure setup & resource provisioning (hands-on)

Create:

  • Resource group
  • ADLS Gen2 storage account
    • enable hierarchical namespace
  • Containers in the data lake:
    • bronze, silver, gold
  • Azure Data Factory
  • Azure SQL Database
    • create server + database + sample schema/data

Organize Bronze/Silver/Gold containers for pipeline outputs.

5) Create source SQL objects (data generation for learning)

Use SQL scripts from the GitHub repo to:

  • create dimension-like tables and a fact-like table (pseudo dataset)
  • populate initial data for development/testing

6) ADF Bronze pipeline design (incremental + backfill + watermark)

6.1 Link services + datasets

In ADF:

  • create GitHub integration (repository + publish settings)
  • create link services:
    • SQL Database connection (SQL auth)
    • ADLS Gen2 storage connection (account key in same RG)
  • create datasets:
    • SQL dataset using a query (not table name, to inject incremental logic)
    • ADLS datasets for:
      • JSON watermark files (CDC)
      • PARQUET outputs

6.2 Incremental/backfill logic structure in ADF

ADF uses a CDC watermark JSON file in ADLS:

  • initial state: bronze/cdc/empty.json
  • then overwritten with updated max CDC (e.g., cdc/cdc.json)

Query pattern:

  • Initial run: read all rows where updated_at > very_old_date
  • Incremental run: read only rows where updated_at > last_saved_cdc

Then update the watermark by:

  • computing MAX(updated_at) from the source
  • writing that back into cdc/cdc.json (overwriting it)

6.3 Dynamic reuse for multiple tables

Introduce pipeline parameters (conceptually):

  • schema, table, cdc_column

Use dynamic expressions to build SQL queries, and use parameterized datasets so you don’t recreate datasets for each table.

6.4 Optimization: avoid empty output files

  • Use an If Condition:
    • only update watermark and write/overwrite outputs when dataRead > 0
    • if dataRead == 0:
      • delete the empty output parquet file

6.5 Backdated refresh / backfill

  • Add pipeline parameter: fromDate
  • Modify query logic:
    • if fromDate is provided → filter from fromDate
    • if fromDate is empty → use watermark logic (last_saved_cdc)

Validate by running:

  • without fromDate (incremental only)
  • with fromDate (backfill behavior)

6.6 Scaling to all tables via looping

Build an “incremental loop” pipeline using For Each over an array of dictionaries, e.g.:

  • {schema, table, cdc_column, fromDate}

This reduces manual per-table pipeline runs and enables scaling by updating the input array.

7) ADF monitoring & alerting via Logic Apps

  • When pipeline fails, send an email alert:
    • create a Logic App with:
      • HTTP Request trigger
      • Send email action
    • In ADF:
      • call the Logic App endpoint via a Web activity on failure (or test paths)
    • Include payload fields like:
      • pipeline name
      • pipeline run id

Databricks Silver layer methodology (Autoloader + transformations)

1) Unity Catalog setup and governance

Create/enable:

  • Unity Catalog metastore
  • Catalog (e.g., Spotify)
  • Schemas (e.g., silver)

Create:

  • Access connector + role assignment for ADLS access
  • External locations for Bronze/Silver/Gold containers:
    • based on ABFSS paths + credentials

2) Read Bronze with Spark + Autoloader (streaming pattern)

Use Autoloader:

  • read files from Bronze per-table folder
  • configure:
    • cloudFiles format = parquet
    • checkpoint location
    • schema location
    • schema evolution mode (default add new columns; rescue option noted)

Highlighted benefit:

  • Exactly-once / item potency via checkpoints
  • improved schema evolution handling compared to manual streaming code

3) Transformations into Silver dimensions and fact

Examples shown:

  • dim_user: normalize uppercase username; drop rescue_data
  • dim_artist: drop rescue_data, drop duplicates, write to delta tables
  • dim_track: derive duration_flag; sanitize track name (replace hyphens); drop rescue_data
  • dim_date: minimal handling; drop rescue_data
  • fact_stream: drop unused columns like rescue_data (optional branching noted)

4) Reusable utilities in Databricks

Create a Python utility class (e.g., in utils/transformations.py) to:

  • drop columns
  • optionally implement deduplication patterns

Mechanism:

  • add project root to sys.path so Databricks can import utility modules

5) Write Silver outputs

  • Write transformed streaming data into Delta
  • use trigger once=True for one-time processing examples
  • specify checkpoint location for streaming correctness
  • optionally create native delta tables in Silver via create table ... using delta location ...

6) Metadata-driven SQL generation (Ginga templating)

Implement a dynamic query generator concept:

  • define a parameters array describing:
    • base table, columns, aliases
    • join tables and join conditions
    • optional filters/where clauses
  • use Ginga (templating library) to render dynamic SQL

Outcome:

  • avoid rewriting join SQL from scratch—only update parameter dictionaries.

Gold layer methodology (SCD Type 2 with Delta Live Tables / Lakeflow)

1) Slowly changing dimensions Type 2 overview

Focus on Type 2:

  • keep history using:
    • start_at, end_at
  • expire old records and insert new versions on changes
  • “latest/current” records have end_at = NULL

2) Implement SCD2 declaratively with Lakeflow / Delta Live Tables

  • enable Lakeflow pipeline editor in Databricks settings
  • define a DLT pipeline:
    • create a staging table for each dimension:
      • dt.table (e.g., dim_user_staging) reading from Silver delta table
    • create an Auto CDC flow:
      • streaming/target table (e.g., dim_user)
      • configure:
        • key columns (e.g., user_id)
        • sequence by column (e.g., updated_at)
        • settings to expire rows and insert updated versions

3) Data quality with Expectations

  • use Delta Live Tables expectations:
    • define rules in a dictionary
    • set behavior on failure:
      • warn/drop/fail pipeline (example behavior described as fail for sensitive data vs drop for non-critical data)

4) End-to-end testing strategy shown

  • load incremental data into Bronze via ADF
  • run Silver notebooks to update delta dimensions/fact
  • run Gold DLT pipelines to:
    • apply SCD2 logic
    • verify results via SQL queries

Validation points:

  • counts increase appropriately for changed/new rows
  • only new/changed rows appear in updated history
  • dedup/merge logic prevents duplicating records

Databricks deployment methodology (Asset Bundles)

1) What asset bundles are used for

  • Package notebooks/pipelines/jobs/config into a deployable bundle
  • support deployments across environments:
    • dev → QA → prod
  • tied to CI/CD:
    • developers create bundles
    • DevOps deploys them

2) Bundle structure & deployment flow

  • create a Databricks bundle project using YAML:
    • specify:
      • targets (dev/prod)
      • host per environment
      • permissions/variables
  • deploy using CLI:
    • validate bundle
    • deploy to a target (e.g., -target dev, then -target prod)

Speakers / sources featured

  • Speaker: The video host/instructor (unnamed in subtitles; referred to as “Anlamba / An Lamba” / “Anal Lamba”).
  • Platform/source: YouTube video titled “Spotify End-To-End Azure Data Engineering Project (From Beginner To Pro)” (the video itself is the only explicit source referenced; no external interviewees are clearly identified).

Rate this summary

Your feedback will help improve summaries.

Improve this summary

Reprocess with a stronger model when the summary feels incomplete or inaccurate.

Pro

Translate summary in another language

Pro

Ask questions to this video

Chat for follow-up questions, clarifications, and source-backed answers.

Coming soon

Share this summary

Share on X/Twitter Share on LinkedIn Share on Reddit

Original video