Azure Lakehouse
Data Pipeline

Tech stack

Azure

Databricks

Delta Lake

Synapse

Superset

Grafana

Prometheus

Docker

Python

The problem

FPT Play produces raw OTT search logs — each event a user_id × keyword × platform × network × subscription record. The keywords are messy: typos, missing Vietnamese diacritics, and noise ("doremon", "nguoi nhen", "one pice"), making it impossible to answer "which titles are actually being searched for?" without cleaning them first.

The goal: port an AWS lakehouse design to Azure, build an incremental Bronze → Silver → Gold pipeline, normalize search keywords to canonical movie titles with ML, and serve analytics through a warehouse and BI dashboards — with pipeline observability on top.

System architecture — Azure cloud pipeline plus the local Docker analytics & monitoring stack.

Detailed medallion flow, ending in the Gold star schema.

Data model

81K search events land as Parquet in ADLS Gen2 and flow through three Delta Lake layers — Bronze, Silver, Gold — governed by Unity Catalog. Gold is exported to a plain ADLS path and served by Synapse Serverless SQL for BI.

The lakehouse_pipeline Databricks Job — four chained notebook tasks, fired on file arrival, all green.

Plus six dimensions — dim_date, dim_platform, dim_category, dim_network, dim_user (SCD Type 1), and dim_subscription — completing the star schema.

What the data actually looks like at each step

Raw → Bronze Ingested as-is; only datetime typed (two locale formats reconciled)

Bronze → Silver Keyword normalized to canonical title with a match method & confidence

Silver → Gold Facts join surrogate keys; dimensions carry descriptive attributes

Data-quality challenges encountered

ML keyword normalization

The core challenge: map messy search keywords to canonical movie titles. A local LLM classifies high-frequency keywords, then fuzzy matching handles the long tail — all applied to Silver via an idempotent MERGE.

• Pareto scoping — keywords with freq ≥ 2 (7,646 unique) cover ~72% of traffic; the 21K freq=1 long-tail is handled with cheap fuzzy matching instead of the LLM
• Local LLM — Qwen2.5-14B via LM Studio (JSON-schema output, 4 worker threads) labels each keyword: movie or not, canonical title, confidence
• Confidence calibration — a hand-reviewed sample showed the model is not well-calibrated (weighted fit R²=0.405); accuracy at conf 0.85 was 17.4% vs 65.3% at ≥0.95, so only ≥0.95 mappings are trusted
• Fuzzy clustering — rapidfuzz (threshold 92, sequel-protection rules) merges near-duplicate titles: 3,173 → 3,006

Raw LLM accuracy was only 40.4% — the honest number is what drove the strict 0.95 threshold instead of blindly trusting model confidence.

Approach

Five notebooks move data one layer at a time, wired into a Databricks Job that fires on file arrival. Transforms run in PySpark; Unity Catalog governs access; Auto Loader handles incremental ingestion.

Step 1 — Incremental ingest (Auto Loader)

Bronze uses cloudFiles with trigger(availableNow=True). A checkpoint tracks which files were processed, so only new files are read and re-running the notebook never duplicates rows — batch economics with streaming plumbing.

• Exactly-once — the checkpoint guarantees each file is ingested once, no manual offset tracking
• No small-files problem — Spark controls output file sizes rather than hand-chunking rows into tiny Parquet files
• Event-driven — a Databricks Job with a file-arrival trigger runs the whole chain when a new batch lands

Step 2 — Transform, normalize & model

Silver fixes calendars/locales, lowercases fields, and applies the ML mapping via MERGE. Gold builds a star schema: a central fact plus six dimensions and a bridge table for the user↔subscription many-to-many.

Where Unity Catalog & Synapse fit in

Unity Catalog is not on the data path. Delta files live on ADLS Gen2; Unity Catalog is the governance layer that tracks each table's location, schema, and access via a Managed Identity. Synapse Serverless holds no data either — it queries the exported Delta files in place through OPENROWSET, billed per TB scanned, keeping BI queries off the pipeline's compute.

📚 ADLS is the bookshelf holding the actual files; Unity Catalog is the card catalog; Synapse is a reading desk that opens the books in place without copying them.

Key techniques used

Dashboards & monitoring

A Superset dashboard for analytics, connected to Synapse, plus a Grafana dashboard for pipeline observability fed from Databricks.

Screenshots

Superset analytics (top) and Grafana pipeline monitoring (bottom).

Results

What I'd do differently

Process the full long-tail — the demo ran rapidfuzz on 4K of the 27K remaining raw keywords; a complete pass would push normalization coverage well past 72%.

Add a dim_movie dimension instead of keeping the normalized title as a degenerate attribute on the fact — cleaner for title-level slicing and future enrichment (genre, release year).

Replace the ngrok tunnel with a Pushgateway on Azure Container Instances inside the same VNet — the honest production answer to "how does a cloud job reach a home PC behind CGNAT."

• SCD Type 2 on dim_user — once multi-week history exists, track profile changes over time instead of overwriting
• Data-quality gates — Delta constraints between layers with Grafana alerting when row counts drift unexpectedly
• CI/CD for notebooks — Databricks Asset Bundles; Terraform IaC is demonstrated in my separate CI/CD project