Medallion Architecture · Layer 02
I cleaned, typed, validated, and standardized every Bronze table into production-ready Silver tables. This is where CDC suppression flags get decoded, column names align across datasets, and state abbreviations are standardized for joins.
Layer Purpose
The Silver layer is the cleaning and standardization layer. I apply explicit type casting, handle suppressed/null values from CDC and USDA data, rename columns for consistency across datasets, and add derived columns that simplify downstream analytics.
Key transformations include: filtering out CDC WONDER Total rollup rows (keeping only year-level data), adding state abbreviation lookups via broadcast joins so all datasets share a common join key, flagging low-coverage AQI counties with fewer than 50 monitoring days, and normalizing SDWIS state codes from mixed FIPS/abbreviation format to clean 2-letter codes.
Every Silver notebook ends with a data quality summary — row count, column count, and null counts per column — to validate that the cleaning was successful before promoting data to Gold.
8 Notebooks · Click to Explore
Filters CDC WONDER Total rollup rows, casts all columns to correct types, renames state columns for consistency, adds state abbreviations via broadcast join. Zero nulls across all 8 columns — the cleanest Silver table in the project.
Same pattern as mortality but handles the plural "States"/"States Code" column names from the CDC incidence export. Includes a cross-check join between incidence and mortality Silver tables revealing the mortality-to-incidence ratio by state.
Fixes the column starting with a number (90th_percentile_aqi → percentile_90_aqi), removes non-US entries (Canada, Mexico, territories), fixes DC casing mismatch, flags low-coverage counties, and adds EPA AQI category labels.
Filters 398K rows to 7 relevant topics, keeps only Overall stratification rows, uses a window function to prefer Age-Adjusted Prevalence over Crude Prevalence, and flags suppressed values (DataValueFootnoteSymbol = '*').
Filters 15.3M violations to 1.98M health-based violations only, normalizes mixed FIPS/abbreviation state codes, filters to active water systems, and adds human-readable water source and system type descriptions.
Identifies CAFO facilities via NAICS 112xxx codes, converts the '303(D) Listed' impaired waters flag to a boolean, joins permit status information, and adds livestock type descriptions from NAICS code mappings.
Joins the variable list dictionary to add human-readable descriptions to each indicator code, pads FIPS codes to 5 characters for county-level joins, and removes negative values caused by zero-population county denominators.
Re-reads the raw file directly with the correct tab delimiter (bypassing the broken Bronze table), filters to Animals & Products sector, handles USDA suppression codes (D), (Z), (H) as nulls, and cleans comma-formatted numeric values.