Hi Databricks Community,
I wanted to share a project I've been building over the past few months using Databricks on Azure , a production Lakehouse pipeline for retail pricing analytics.
What it does
Ingests ~21,000 products daily across 16 categories from Plaza Vea Peru's public API
Processes 120,000+ historical records using PySpark, Delta Lake, and Medallion Architecture (Bronze / Silver / Gold)
Orchestrated with Lakeflow Jobs and governed with Unity Catalog
Analytics via Databricks AI/BI Dashboard and Power BI Direct Lake
Architecture
1. Ingesta โ Azure Functions + ADLS Gen2 Azure Function (Python) scrapes the public API concurrently by category and lands raw JSON into ADLS Gen2, partitioned by batch_id=YYYY-MM-DD.
2. Orchestration โ Lakeflow Jobs A 9-task Lakeflow Job orchestrates the full pipeline, passing p_batch_id via taskValues across tasks: trigger Azure Function โ identify batch โ ingest Bronze โ transform Silver โ build Gold โ validate โ complete batch.
3. Medallion Architecture
Layer What happens
| Bronze | Raw JSON ingestion with replaceWhere partitioning by batch_id. Historical data intact. |
| Silver | MERGE upserts with 36 columns, deduplication keyed on product_id + ingest_date. 8 real data quality issues documented and resolved. |
| Gold | Star Schema with 6 dimensions + fact_precio_snapshot. 34,835 facts loaded with 100% referential integrity via INNER JOINs. Lost records audited in gold_auditoria_perdidos. |
Key technical decisions
replaceWhere vs full overwrite โ only overwrites the day's batch_id partition, keeping history safe
Composite MERGE key (product_id + ingest_date) โ each day is an independent snapshot
Pre-populated dim_etiquetado JUNK dimension โ 32 combinations under Peruvian Law 30021, eliminating NULLs in the fact table
INNER JOINs in the fact table โ 100% referential integrity guaranteed
