End-to-End Data Platform

Built and maintain an end-to-end incremental batch production Azure data platform as sole data engineer - ADF ingestion, Databricks medallion pipeline, and Power BI dashboards for live auction analytics.. all governed by Unity Catalog.

1. How It Works

Data Pipeline: Azure Databricks Medallion Architecture

• Ingestion: Raw files (CSV/JSON/Parquet) land in ADLS Gen2 (dated folders); a scheduled Databricks Control Job reads a batch control table and picks up the oldest unprocessed batch automatically
• Bronze → Silver → Gold:
• Bronze: raw ingestion with batch ID + timestamp (full traceability)
• Silver: cleaning, deduplication, schema conformance
• Gold: business-ready aggregates & dimensional models
• Reliability: Batch lifecycle tracked as PENDING → IN_PROGRESS → COMPLETED/FAILED — pipeline is idempotent and safely retriable on failure
• Serving: Curated Gold data exposed to Power BI via Databricks SQL endpoint, with Unity Catalog RBAC governing access

2. Unity Catalog Hierarchy & Databricks Connectivity

A managed identity-backed Access Connector binds to a Unity Catalog storage credential, which authorizes an external location mapped to the ADLS Gen2 abfss:// URI. The workspace is Unity Catalog-enabled, exposing five schemas under a single catalog: landing (external volume over /landing/files/), bronze, silver, gold (managed Delta tables), and batch_control (control and audit tables). All access is governed via three-part naming and ACL-based RBAC.

3. Medallion Architecture — Step-by-Step Breakdown

Bronze ingests raw CSV/JSON/Parquet from the landing external volume, enforces schema via StructType, adds batch_id and ingestion_ts, and appends to managed Delta tables. Silver reads Bronze filtered by batch_id, applies deduplication using row_number() window functions, casts types, joins reference data, and upserts via idempotent MERGE keyed on (entity_key, batch_id). Gold reads Silver to build dimensional models, fact tables, and KPI aggregates using window aggregations and overwrite/merge writes.

4.Control / Batch Table Orchestration

The orchestrator lists folders from /Volumes/landing/files/ via the external volume API, then queries batch_control.batches to diff against already-COMPLETED batch_ids. The oldest unprocessed folder is selected, set as a TaskValue (batch_id, batch_exists), and the workflow conditionally continues only if batch_exists == true. The batch is UPSERTed to IN_PROGRESS before the processing job runs. On success or exception, status transitions to COMPLETED or FAILED, and an event row is INSERTed into batch_control.batch_audit with event_type ∈ {STARTED, COMPLETED, FAILED}

5.Processing Pipeline -Technical Breakdown

Step 1 resolves the batch_id TaskValue to a volume path /Volumes/landing/files/{batch_id}/*.csv and passes file references to the Bronze notebook via spark.read. Step 2 enforces StructType schema, rejects malformed rows, adds batch_id and source_file audit columns, and appends to Bronze Delta tables — eight tables ingested in parallel. Step 3 reads Bronze filtered by batch_id, deduplicates with row_number() OVER (PARTITION BY natural_key), casts and conforms types, joins reference tables, and MERGEs into Silver using idempotent upsert logic. Step 4 builds dimensions with surrogate keys and SCD attributes, assembles facts at the correct grain, computes KPI aggregates with window and groupBy, then writes to Gold with Z-ORDER and OPTIMIZE. Step 5 updates batch_control.batches to COMPLETED, INSERTs the audit event, and exposes Gold to Power BI, ML notebooks, and APIs via Databricks SQL endpoint.

6.Power BI Consumption - End-to-End Architecture

Gold Delta tables (fact_*, dim_*, kpi_*) are optimized with Z-ORDER on filter columns and exposed via a serverless Databricks SQL Warehouse endpoint. Power BI connects via the native Databricks connector, authenticates through Entra ID OAuth/SSO, and routes traffic over a private endpoint. The semantic model is built in DirectQuery or Import mode over the SQL Warehouse, with DAX measures defined on top of the Gold schema.

7.Azure Data Factory Ingestion (Future Implementation)

An ADF schedule or event-based trigger fires the pipeline. A Lookup activity queries Azure SQL Database or a REST API endpoint to retrieve the file manifest. A ForEach activity iterates over the file list, and a parameterized Copy Activity writes each file to landing/{source}/{batchId}/{fileName} in ADLS Gen2 via the Integration Runtime. Credentials are resolved from Azure Key Vault at runtime. Files written to the landing zone are immediately discoverable by the Databricks batch control job via the Unity Catalog external volume.

8.Databricks Jobs - Batch Control & Processing Workflow

001_Identify_next_batch queries the control table to find the next unprocessed batch and sets s_batch as a TaskValue. The conditional task is_there_a_batch_to_process evaluates {{...s_batch}} == "true" and short-circuits the workflow if false. On true, 002_Create_New_Batch registers the batch as IN_PROGRESS, the auction_results_job is triggered as a run-job task passing batch_id, and 003_Complete_Batch updates the control table to COMPLETED on success.