Food Inspection Analytics

Food-safety inspection data is fragmented across jurisdictions — every city publishes it in a different schema and violation format, so you can't compare compliance or spot high-risk facilities across cities. This platform unifies Chicago and Dallas inspection data into one analytics-ready lakehouse.

A Medallion pipeline from raw government files to dashboards, orchestrated by Azure Data Factory:

• Bronze: raw Chicago & Dallas inspection files landed unmodified in Azure Data Lake Gen2 for auditability
• Silver: Databricks (PySpark) cleansing to Parquet — unpivoting Dallas's wide format, parsing Chicago's pipe-separated violation strings, regex-extracting lat/long, and standardizing risk levels
• Integration: sequential notebooks build stg_chicago and stg_dallas, then merge into a unified table with source, job_id, and load_dt lineage fields
• Gold: Snowflake Dynamic Tables build a star schema — fact_inspections plus dim_establishment, dim_location, dim_violation, and dim_date
• Tableau dashboards for inspection outcomes, risk hotspots, and cross-city violation trends

The trade-offs — and why:

• Adopted the Medallion architecture (Bronze → Silver → Gold) so raw data stays auditable while analytics reads a clean, modeled layer
• Built a city-agnostic, modular pipeline so a new jurisdiction can be added without rewriting core logic
• Tagged every record with source, job_id, and load_dt for full lineage tracking
• Used Snowflake Dynamic Tables for dependency-aware incremental refresh instead of full reloads

The hard parts:

• Heterogeneous schemas: Chicago is long-format with pipe-separated violations; Dallas is wide-format with up to 25 violation blocks per row — reconciling them into one model took the most design work
• Profiling showed Dallas violation columns beyond #5 were >99% null, so I processed them selectively instead of wasting compute
• City-specific parsing: regex to pull embedded coordinates and standardizing inconsistent risk categories across both cities

A unified, lineage-tracked food-safety warehouse that lets public-health teams compare compliance across cities, rank risk hotspots, and drill into facility-level history — served through interactive Tableau dashboards.

Adding more cities via the modular ingestion pattern, layering predictive risk scoring on inspection history, and automating dashboard refresh on new data loads.