Medallion Architecture · Layer 03
I aggregated all Silver tables into 7 business-ready Gold tables — one per dataset group — and joined them into a single master state_environmental_risk_profile with 51 states and 41 columns feeding Tableau and the ML models.
Layer Purpose
The Gold layer is the business-ready aggregation layer. I aggregate Silver tables from their native grains (state × year, county × year) up to state-level summary tables — one per dataset group. These tables are optimized for analytics, reporting, and machine learning consumption.
The culmination of the Gold layer is the state_environmental_risk_profile — a master join table that combines all 6 summary tables into a single 51-row, 41-column wide table. This is the single source of truth for all downstream work: every analytics notebook, both ML models, and all 3 Tableau dashboards read from this table.
Key design decisions: time-varying tables (AQI, water violations) are averaged across all years before joining since they don't share the same year grain as cancer incidence. Small dimension tables (dim_state, dim_year) are broadcast joined for performance. The is_high_cancer_incidence flag (rate ≥ 500 per 100k) is defined here and used as the binary target for the ML classifier.
7 Notebooks · Click to Explore
Full outer joins incidence (1999–2022) and mortality (2018–2023) on state_abbr + year. Computes mortality-to-incidence ratio revealing that WV (35%) and KY (33.7%) lose far more cancer patients per diagnosis than NY (26.5%) and NJ (26.9%).
Aggregates county-level AQI to state-year level, excluding low-coverage counties. Reveals the PM2.5 paradox — national median AQI improved slightly 2000–2022, but PM2.5 days nearly doubled from 83 to 151 annually.
Joins violations to water systems to get state codes, normalizes FIPS codes to 2-letter abbreviations, and aggregates by state and year. TX leads with 153K violations; enforcement rates improved from 70% in 1990 to 99%+ by 2009.
Aggregates CAFO facilities to state level with livestock type breakdown (cattle, dairy, poultry, turkey, aquaculture). PA leads in CAFOs near impaired waters (64.5%) vs KS which has the most total CAFOs but only 9.2% near impaired waterways.
Pivots the long-format chronic disease Silver table into a wide-format state-year summary with one column per indicator: smoking, obesity, diabetes, COPD, asthma, and physical inactivity prevalence rates.
Aggregates 14 key food environment indicators from county to state level. Removes negative values from zero-population county denominators before averaging. ND leads in low food access (45%) while DC has lowest at 4.6%.
The master join notebook — joins all 6 Gold summary tables into a single 51-row, 41-column state-level analytical table. This is the primary input for all analytics notebooks, both ML models, and all 3 Tableau dashboards. Adds the is_high_cancer_incidence binary flag used as the ML classifier target.