Medallion Architecture · Layer 01
I ingested raw CSV files from 5 federal agencies directly into Delta Lake tables with minimal transformation.
Every row is stamped with ingested_at and source_file for full lineage traceability.
Layer Purpose
The Bronze layer is the raw ingestion layer of the Medallion Architecture. I read each source CSV file exactly as received from the federal agency and write it to a Delta Lake table with zero business logic applied — only structural fixes like column name sanitization to comply with Delta's naming rules.
Every Bronze table includes two metadata columns: ingested_at (timestamp of when the row was ingested) and source_file (the exact filename it came from). This ensures full data lineage — I can always trace any row in the warehouse back to its exact source file.
The Bronze layer handles the project's most complex ingestion challenges: a wildcard read of 23 annual AQI files into one unified table, a 15.3 million row water violations file, a tab-delimited USDA livestock file misidentified as CSV, and 26 NPDES/SDWIS files ingested via a loop pattern into separate tables.
8 Notebooks · Click to Explore
Ingests CDC WONDER cancer mortality data (2018–2023) into the Bronze layer. Discovers and fixes the Delta column naming issue (spaces not allowed) — a fix carried forward into all subsequent notebooks.
Ingests CDC WONDER cancer incidence data (1999–2022) — the primary outcome variable for the entire project. Uses a generic sanitizer function that lowercases and snake_cases all column names.
Reads all 23 annual AQI county-level files (2000–2022) in a single wildcard Spark read, unions them automatically, and uses Unity Catalog's _metadata.file_path to tag each row with its source file.
Ingests the CDC Chronic Disease Indicators dataset — a long-format file with 34 columns covering 19 chronic disease topics including Cancer, Tobacco, Diabetes, COPD, and Cardiovascular Disease.
Ingests all 11 EPA SDWIS files using a loop pattern — including the 15.3 million row violations file (the largest in the project). Uses try/catch per file with a summary report at the end.
Ingests all 15 EPA ECHO NPDES files covering CAFO permits, inspections, violations, and enforcement actions. Uses the same loop pattern as SDWIS — 15/15 success in one run.
Ingests the USDA Food Environment Atlas — already in long format (one row per county × variable_code), contrary to the wide format expected from the handoff doc. Also ingests the variable list dictionary.
Ingests the 2.24 GB USDA Census of Agriculture QuickStats file — a tab-delimited .txt file misidentified as CSV. Uses delimiter detection to discover the tab separator before reading the full 6M row file.