# Data Reality — civic-data > **Generated** by `civic_data/datadict/generator.py` from the live database + ingest code. Do not hand-edit — re-run the generator. Authored prose lives in `civic_data/datadict/config.py` (what it is) and `civic_data/datadict/lineage_config.py` (what we did). - **As of:** 2026-07-12 · **Data version:** `2026-07-12+27720116` · **Generated at:** 2026-07-13T01:46:56.006224+00:00 - **City:** Louisville / Jefferson County, KY · **Geography:** ZIP code (41-ZIP Louisville boundary set) - **Maturity posture:** v1 — generated dictionary + transformation lineage over crime, 311, the Economic-Distress theme (land bank sales, code-enforcement liens, foreclosure filings), the Investment & Growth theme (food service, ABC licenses, restaurant inspections, construction permits), Housing & Affordability (short-term rental registrations), and the geography/reference spine (district/neighborhood/HUD boundaries, Census ACS population, ZIP↔geo crosswalks). - **Source-count audit folded in:** no (run with --with-source-audit) Cleaned, normalized, and documented Louisville public open data. This document is the as-built data reality — what we actually hold, what it honestly measures, how much to trust it, and (in the lineage half) exactly what we did to it and why. It is regenerated from the live database + ingest code; the numbers cannot drift from it. **Trust tiers** — `trendable`: Clean, full multi-year coverage — safe for time series and trend claims. `viewable_caveated`: Real data with gaps/cliffs/discontinuities — viewable within a stated window, must not be presented as an uncaveated trend. `snapshot_only`: No honest time series — current-state snapshot only. `unsupported`: Claims the data cannot back — named so nobody proposes vaporware on top.
Source-pull provenance — 48 pull(s), 3,914,200 loaded vs 3,913,628 at source | Source dataset | Pulled | Source rows | Loaded | Status | |---|---|--:|--:|---| | `311-service-requests-2010` | 2026-07-09 | 106,380 | 106,380 | ok | | `311-service-requests-2011` | 2026-07-09 | 106,296 | 106,296 | ok | | `311-service-requests-2012` | 2026-07-09 | 105,503 | 105,503 | ok | | `311-service-requests-2013` | 2026-07-09 | 96,335 | 96,335 | ok | | `311-service-requests-2014` | 2026-07-09 | 94,605 | 94,605 | ok | | `311-service-requests-2015` | 2026-07-09 | 104,145 | 104,145 | ok | | `311-service-requests-2016` | 2026-07-09 | 102,039 | 102,039 | ok | | `311-service-requests-2017` | 2026-07-09 | 102,135 | 102,135 | ok | | `311-service-requests-2018` | 2026-07-09 | 124,741 | 124,741 | ok | | `311-service-requests-2019a` | 2026-07-09 | 56,917 | 56,917 | ok | | `311-service-requests-2019b` | 2026-07-09 | 95,786 | 95,786 | ok | | `311-service-requests-2020` | 2026-07-09 | 229,875 | 229,875 | ok | | `311-service-requests-2021` | 2026-07-09 | 216,399 | 216,399 | ok | | `311-service-requests-2022` | 2026-07-09 | 175,152 | 175,152 | ok | | `311-service-requests-2023` | 2026-07-09 | 173,246 | 173,246 | ok | | `311-service-requests-2024` | 2026-07-09 | 169,601 | 169,601 | ok | | `311-service-requests-2025` | 2026-07-09 | 182,490 | 182,490 | ok | | `311-service-requests-2026` | 2026-07-09 | 102,179 | 102,179 | ok | | `abc-local-licenses` | 2026-07-09 | 1,681 | 1,681 | ok | | `abc-state-licenses` | 2026-07-09 | 5,483 | 5,483 | ok | | `active-construction-permits` | 2026-07-09 | 23,332 | 23,332 | ok | | `crime-data-2007` | 2026-07-09 | 90,767 | 90,767 | ok | | `crime-data-2008` | 2026-07-09 | 86,481 | 86,481 | ok | | `crime-data-2009` | 2026-07-09 | 78,380 | 78,380 | ok | | `crime-data-2010` | 2026-07-09 | 82,871 | 82,871 | ok | | `crime-data-2011` | 2026-07-09 | 86,013 | 86,013 | ok | | `crime-data-2012` | 2026-07-09 | 81,406 | 81,406 | ok | | `crime-data-2013` | 2026-07-09 | 77,127 | 77,127 | ok | | `crime-data-2014` | 2026-07-09 | 77,466 | 77,466 | ok | | `crime-data-2015` | 2026-07-09 | 79,839 | 79,839 | ok | | `crime-data-2016` | 2026-07-09 | 84,535 | 84,535 | ok | | `crime-data-2017` | 2026-07-09 | 82,113 | 82,113 | ok | | `crime-data-2018` | 2026-07-09 | 80,000 | 80,000 | ok | | `crime-data-2019` | 2026-07-09 | 73,420 | 73,420 | ok | | `crime-data-2020` | 2026-07-09 | 70,233 | 70,233 | ok | | `crime-data-2021` | 2026-07-09 | 72,127 | 72,127 | ok | | `crime-data-2022` | 2026-07-09 | 71,506 | 71,506 | ok | | `crime-data-2023` | 2026-07-09 | 71,502 | 71,502 | ok | | `crime-data-2024` | 2026-07-09 | 70,914 | 70,914 | ok | | `crime-data-2025` | 2026-07-09 | 70,279 | 70,279 | ok | | `crime-data-2026` | 2026-07-09 | 32,670 | 32,670 | ok | | `food-service-establishments` | 2026-07-09 | 3,366 | 3,366 | ok | | `landbank-historical` | 2026-07-09 | 418 | 418 | ok | | `lien-final-orders` | 2026-07-09 | 434 | 434 | ok | | `property-foreclosures` | 2026-07-09 | 2,876 | 3,448 | ok | | `restaurant-inspection-scores` | 2026-07-09 | 11,273 | 11,273 | ok | | `str-registration` | 2026-07-09 | 1,252 | 1,252 | ok | | `zip_population` | 2026-07-11 | 40 | 40 | ok |
--- ## 1. Loss boundary — what we dropped What we DROPPED between source and silver. If bronze raw is not persisted, the silver.quarantine table is the ONLY visible drop — losses before the ZIP-validation step (fetch gaps, parse failures, dedup) are invisible from the DB. The source-count audit (datadict/audit.py, folded in below) bounds the dedup-vs-loss question from the source side. **Quarantine (the visible drop): 20,823 records.** silver.quarantine is the visible drop. Losses before the ZIP-validation step are not captured here unless bronze raw is persisted. | Source table | Quarantined | |---|--:| | `silver.crime_incidents` | 20,484 | | `silver.service_requests` | 178 | | `silver.lien_orders` | 136 | | `silver.abc_licenses` | 15 | | `silver.construction_permits` | 8 | | `silver.property_foreclosures` | 2 | Reasons: ZIP not in Louisville boundary set (20,823) ### Headline findings **Crime citywide undercount — location-less records quarantined.** The single largest real drop. LMPD stamps location-less offenses (fraud, identity theft, cyber — crimes with no physical address) with placeholder ZIP 40056 (Pewee Valley, outside the 41-ZIP set), so validation quarantines them. Per-ZIP crime is unaffected (they can't be placed anyway), but CITYWIDE crime totals undercount and the gap is biased toward non-geographic offense types. - _Live:_ 19,734 crimes quarantined at ZIP 40056 → **~1.6% citywide crime undercount** (silver 1,242,777 + quarantined 20,484). **311-vs-crime NULL-zip inconsistency.** 311 KEEPS its missing-ZIP records (NULL zip, recoverable via lat/lng); crime QUARANTINES its location-less records entirely. Same situation, opposite treatment. A candidate normalization is to distinguish 'real Louisville record, no location' (→ NULL zip, keep) from 'another jurisdiction' (→ quarantine). Documented, not yet changed. **Bronze persistence determines auditability.** If raw bronze is not retained, the quarantine step is the only place a drop becomes visible; earlier-stage losses (fetch/parse/dedup) would not show up at all. The from-source reproduction recipe + the source-count audit together bound this. - _Live:_ bronze raw tables are **partially populated** — `business_raw`=10530, `crime_raw`=1524649, `inspections_raw`=11273, `property_raw`=28884, `service_requests_raw`=2348824. > **Audit verdict.** Frozen per-year crime/311 services barely grow, so a source-vs-silver gap on those years is a clean loss check. Large apparent 'gaps' on crime are legitimate offense-row deduplication (incident counts sound), not lost data; the one genuine drop is the location-less crime at 40056. --- ## 2. Concept layer — the product narratives ### Safety & Enforcement — `trendable` _Can speak to REPORTED crime per ZIP with the policing-bias caveat. Cannot speak to 'how safe' a place is, nor to under-reported offense types._ - **crime** (offense_type, incident_date, zip_code) — primary — reported-incident density + violent/property split - _Future sources:_ traffic fatalities, uniform citations, fire incidents ### Civic Responsiveness & Livability — `viewable_caveated` _Can speak to 311 demand within 2010–2018 (the trendable window), with the reporting-propensity bias caveat. resolution_days unusable pre-2022._ - **service_requests** (service_name, requested_datetime, status) — primary — 311 demand + resolution - _Future sources:_ noise citations, property-maintenance cases ### Economic Distress & Property Disinvestment — `viewable_caveated` _Can speak to foreclosure-FILING density per ZIP from ~2013 on (not completed loss, no pre-2013 wave), cross-referenced with a current lien snapshot and historical land-bank dispositions. Cannot present liens as a time series (rolling-window source) nor claim the pre-2013 foreclosure history._ - **property_foreclosures** (action_filed, zip_code, neighborhood) — primary — foreclosure FILING density (dense from ~2013, no Great-Recession era) - **lien_orders** (notification_date, zip_code, final_order_state) — supporting — code-enforcement lien SNAPSHOT (rolling window, not a trend) - **landbank_sales** (sale_date, zip_code, sale_program) — supporting — public disposition of blighted parcels (historical, small N) - _Future sources:_ property tax delinquency, vacant/abandoned property registry, eviction filings ### Investment & Growth — `viewable_caveated` _Can speak to CURRENT investment/commercial activity per ZIP — active construction permits, alcohol licenses, permitted food service, recent inspection scores. All four are current-state or rolling-window sources: viewable now, but not a clean multi-year trend from a single pull, and permit years 2020–2022 carry a source capture discontinuity._ - **construction_permits** (issue_date, project_costs, zip_code) — primary — active-permit density + declared project cost (active set, 2020–22 caveat) - **abc_licenses** (license_type, status, zip_code) — supporting — current alcohol-license footprint (nightlife/dining) - **food_service_establishments** (facility_type_description, zip_code) — supporting — current permitted food-service roster - **restaurant_inspections** (score, grade, inspection_date) — supporting — recent inspection scores (rolling ~12-month window) - _Future sources:_ right-of-way permits, business licenses, certificates of occupancy ### Housing & Affordability — `snapshot_only` _Can speak to REGISTERED short-term-rental density per ZIP as a supply-side housing- pressure signal — a current snapshot, understating the unlicensed STR footprint. Not yet a full housing- affordability picture (rents, evictions, and code violations are future sources)._ - **str_licenses** (date_opened, zip_code, council_district) — primary — registered short-term-rental density (supply-side housing pressure) - _Future sources:_ evictions, rental registrations, housing code violations, HUD Fair Market Rent --- ## 3. Master / reference data (MDM) The canonical geography — the governed set every transactional dataset validates against. Distinct from per-dataset transactional lineage. ### Canonical Louisville geography (ZIP boundary set) — `silver.zip_codes` _One governed canonical geography that every transactional dataset validates against. This is master data, not a transaction stream: it is the authority that decides whether a crime or 311 record has a valid Louisville ZIP._ **Governance:** - Integrity check: EXPECTED_ZIP_COUNT = 41 distinct ZIPs; ingest refuses to serve a whitelist below this count (adapters.validation.load_valid_zips). - Authority: adapters.validation.validate_zip resolves every event record's ZIP against this set (valid → keep, missing → NULL, out-of-area → quarantine). - Versioning: boundaries are a versioned reference — re-pulling REPLACES the set (DELETE + reload). Boundary changes are a governed event, not an append. **Transformation & lineage** (master-reference) — _✓ verified against live schema_ - _Source system:_ Louisville / LOJIC ArcGIS Hub — 'Jefferson County KY ZIP Codes' Feature Service. - _Why this transform exists:_ One governed canonical geography that every transactional dataset validates against. This is master data, not a transaction stream: it is the authority that decides whether a crime or 311 record has a valid Louisville ZIP. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | ArcGIS GeoJSON query (42 ZIP polygons with geometry). | Authoritative boundaries from the city's own portal, not derived. | None. | | 2 | `normalize_fields` | ZIPCODE padded to 5 digits. | Consistent 5-digit keys for joins/validation. | None. | | 3 | `dedup` | ON CONFLICT(zip) load → 41 distinct ZIPs (source reports 42; one is a duplicate geometry). | One row per canonical ZIP. | The duplicate geometry collapses to one row (intended). | _Reproduce (source → silver):_ - `civic-data ingest-zips` ### Metro Council district boundaries (versioned) — `silver.council_districts` _The district dimension every event table rolls up to. Master geography, version-scoped: a redistricting is a governed event, not an append — the current boundaries are the 2022-redistricting version. council_member is an ELECTED official (public record), kept by policy._ **Governance:** - Integrity invariant: exactly 26 districts per version, or the load rolls back. - At most one ACTIVE version (effective_to IS NULL) per district_number; setting effective_to on a prior version is a separate, deliberate redistricting step. - Version-scoped: re-ingest REPLACES only the named version's rows (+ its crosswalk); prior versions are preserved. **Transformation & lineage** (master-reference) — _✓ verified against live schema_ - _Source system:_ LOJIC MapServer — OpenDataPolitical/8 (Council Districts, GeoJSON). - _Why this transform exists:_ The district dimension every event table rolls up to. Master geography, version-scoped: a redistricting is a governed event, not an append — the current boundaries are the 2022-redistricting version. council_member is an ELECTED official (public record), kept by policy. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | GeoJSON query (COUNDIST, COUN_NAME) from the LOJIC MapServer. | Authoritative boundaries from the city's GIS, not derived. | None. | | 2 | `build_geom` | ST_Multi(ST_MakeValid(ST_GeomFromGeoJSON)) → MultiPolygon 4326. | One valid MultiPolygon type for spatial joins. | None — geometry repair only. | | 3 | `normalize_fields` | COUNDIST → district_number (int), COUN_NAME → council_member; stamped version_label=2022-redistricting, effective_from=2022-01-01, effective_to=NULL. | Typed, versioned district rows. | None. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP set first` - `civic-data ingest-geo # boundaries + crosswalks (also builds zip_to_district)` - ⚠ _Reproducibility gap:_ Reproduces civic-graph exactly (26 = 26). ### Neighborhood boundaries — `silver.neighborhoods` _The neighborhood dimension for sub-ZIP rollups. Master geography; a full-refresh reference set (DELETE + reload), not an append stream._ **Governance:** - Full-refresh: re-ingest REPLACES the set (DELETE + reload, id sequence reset) — a boundary change is a governed replacement. - UNIQUE(name): one row per named neighborhood (source's duplicate-name features collapse). **Transformation & lineage** (master-reference) — _✓ verified against live schema_ - _Source system:_ LOJIC ArcGIS FeatureServer — LOUISVILLE_NEIGHBORHOODS/44 (GeoJSON). - _Why this transform exists:_ The neighborhood dimension for sub-ZIP rollups. Master geography; a full-refresh reference set (DELETE + reload), not an append stream. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | GeoJSON query (NH_NAME, NH_TYPE) from the LOJIC FeatureServer. | Authoritative boundaries from the city's GIS. | Features with an empty name are skipped. | | 2 | `build_geom` | ST_Multi(ST_MakeValid(ST_GeomFromGeoJSON)) → MultiPolygon 4326. | One valid MultiPolygon type. | None — geometry repair only. | | 3 | `dedup` | ON CONFLICT(name) upsert; 151 source features → 150 rows. | One row per neighborhood name. | A duplicate-name feature collapses to one (intended). | _Reproduce (source → silver):_ - `civic-data ingest-geo # boundaries + crosswalks (also builds zip_to_neighborhood)` - ⚠ _Reproducibility gap:_ Reproduces civic-graph exactly (150 = 150). ### HUD 1979 grant-zone boundaries — `silver.hud_zones` _A legacy grant-zone dimension (urban-core coverage). Master geography; multi-part zones are unioned into one MultiPolygon per code._ **Governance:** - Full-refresh: re-ingest REPLACES the set (DELETE + reload). - One row per nh_code: disjoint source polygons sharing a code are ST_Union'd together (e.g. code 99 'REMAINDER OF CITY' has many parts). **Transformation & lineage** (master-reference) — _✓ verified against live schema_ - _Source system:_ LOJIC MapServer — OpenDataSociety/15 (HUD 1979 Grant Zones, GeoJSON). - _Why this transform exists:_ A legacy grant-zone dimension (urban-core coverage). Master geography; multi-part zones are unioned into one MultiPolygon per code. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | GeoJSON query (NH_CODE, NH_NAME) from the LOJIC MapServer (92 parts). | Authoritative legacy grant zones from the city's GIS. | None. | | 2 | `build_geom` | ST_Multi(ST_MakeValid(ST_GeomFromGeoJSON)); parts sharing a code are ST_Union'd on conflict. | One valid MultiPolygon per zone code. | None — the 92 source parts union to 71 zones (intended). | _Reproduce (source → silver):_ - `civic-data ingest-geo # boundaries + crosswalks (also builds zip_to_hud_zone)` - ⚠ _Reproducibility gap:_ Reproduces civic-graph exactly (71 zones from 92 parts). HUD zones cover the urban core only (~20 of 41 ZIPs), by design. ### Census ACS population by ZIP (per-capita denominator) — `silver.zip_population` _The per-capita denominator for every rate. Population + median age + median household income per (ZIP, ACS vintage), so a count can be normalized with a year-appropriate population instead of one static number._ **Governance:** - Scoped to the canonical 41-ZIP set (silver.zip_codes) — out-of-area ZCTAs are never queried. - ZIP ≈ ZCTA (not exact) — documented, accepted for per-capita use. - Pre-2020 vintages use 2010 ZCTAs (require in=state:21); 2020+ use 2020 ZCTAs. - Suppressed/negative Census sentinels → NULL (never a guessed value). **Transformation & lineage** (master-reference) — _✓ verified against live schema_ - _Source system:_ U.S. Census Bureau — ACS 5-Year Estimates at ZCTA level (api.census.gov), vintages 2011–2023. - _Why this transform exists:_ The per-capita denominator for every rate. Population + median age + median household income per (ZIP, ACS vintage), so a count can be normalized with a year-appropriate population instead of one static number. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | ACS 5-year API per vintage (B01001_001E population, B01002_001E median age, B19013_001E median household income), batched by 50 ZCTAs. | Year-appropriate population for historical per-capita rates. | 40225 has no ZCTA population (non-ZCTA ZIP) → absent; 40 of 41 ZIPs return per vintage. | | 2 | `normalize_fields` | Suppressed/None/negative → NULL; typed to int/numeric. | Zero-vs-NULL discipline: NULL means no usable estimate, never 0. | Suppressed cells become NULL (honest unknown). | | 3 | `dedup` | ON CONFLICT(zip_code, vintage) upsert. | One row per ZIP per ACS vintage. | None. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP set first` - `civic-data ingest-population # ACS 2011–2023 (CENSUS_API_KEY optional at this volume)` - ⚠ _Reproducibility gap:_ 520 rows (40 ZCTAs × 13 vintages) vs civic-graph's 600 — CLEANER, not different: the 520 shared (ZIP, vintage) cells match civic-graph's values exactly; civic-graph's extra 80 rows are ~20 non-Louisville ZCTAs (LA/Chicago/Indianapolis/Frankfort…) pulled from a broader gold ZIP list. Scoping to the canonical 41-ZIP set correctly excludes them. 2009–2010 unavailable at ZCTA level; 40225 has no Census population. --- ## 4. Domains — what it is + what we did | Domain | Rows | Trust tier | PII tier | |---|--:|---|---| | [Crime incidents (LMPD / NIBRS)](#domain-crime) | 1,242,777 | `trendable` | sensitive-aggregate | | [311 service requests (Open311)](#domain-service_requests) | 2,209,988 | `viewable_caveated` | public-aggregate-safe | | [Land Bank Authority sales (historical dispositions)](#domain-landbank_sales) | 399 | `viewable_caveated` | public-aggregate-safe | | [Code-enforcement lien final orders (blight signal)](#domain-lien_orders) | 298 | `snapshot_only` | public-aggregate-safe | | [Property foreclosure filings](#domain-property_foreclosures) | 1,734 | `viewable_caveated` | public-aggregate-safe | | [Permitted food service establishments](#domain-food_service_establishments) | 3,366 | `snapshot_only` | public-aggregate-safe | | [ABC (alcohol) licenses — local + state](#domain-abc_licenses) | 7,149 | `snapshot_only` | public-aggregate-safe | | [Restaurant inspection scores](#domain-restaurant_inspections) | 11,273 | `viewable_caveated` | public-aggregate-safe | | [Active construction permits](#domain-construction_permits) | 23,308 | `viewable_caveated` | public-aggregate-safe | | [Short-Term Rental registrations](#domain-str_licenses) | 1,248 | `snapshot_only` | person-naming | ### Crime incidents (LMPD / NIBRS) - **Table:** `silver.crime_incidents` · **Rows:** 1,242,777 · **Trust tier:** `trendable` - **Grain:** One row per reported incident. UNIQUE(source_id, source_dataset); source carries ~1.18 offense-rows per incident, deduped to one row via ON CONFLICT. - **Primary key:** id · **Ingest:** `civic-data ingest crime` - **Per-year rows:** 1970:4 · 1971:5 · 1972:5 · 1973:3 · 1974:4 · 1975:6 · 1976:5 · 1977:9 · 1978:16 · 1979:31 · 1980:49 · 1981:58 · 1982:29 · 1983:27 · 1984:27 · 1985:28 · 1986:21 · 1987:40 · 1988:24 · 1989:34 · 1990:34 · 1991:33 · 1992:30 · 1993:35 · 1994:42 · 1995:39 · 1996:55 · 1997:59 · 1998:64 · 1999:87 · 2000:177 · 2001:155 · 2002:130 · 2003:190 · 2004:285 · 2005:463 · 2006:1,344 · 2007:66,280 · 2008:67,818 · 2009:66,273 · 2010:70,144 · 2011:72,870 · 2012:67,273 · 2013:63,118 · 2014:64,247 · 2015:66,779 · 2016:69,726 · 2017:66,608 · 2018:64,375 · 2019:59,576 · 2020:58,035 · 2021:58,596 · 2022:57,885 · 2023:59,466 · 2024:57,757 · 2025:56,278 · 2026:26,010 · 2030:1 · 2055:1 · 2057:1 · 2060:1 · 2062:1 · 2063:1 · 2064:1 · 2065:3 · 2066:1 · 2067:3 · 2068:2 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures crimes REPORTED to and recorded by LMPD — not crime. Under-reported offense types (much property/sexual crime) are structurally absent; enforcement intensity shapes the counts. - _Trend window:_ 2007–present for incident counts (NIBRS-era services). Occurrence dates BEFORE 2007 appear on later-reported incidents (cold cases/delayed reports) and future-dated typos exist — filter by incident_date range before trending. - _Representativeness / bias:_ HIGHEST-STAKES field. Over-policed areas read as 'high crime' when they may be high-enforcement. Presenting reported-crime density as neutral 'safety' risks amplifying policing bias. Frame as reported incidents, never 'how dangerous a place is'. - _Sensitivity / PII tier:_ **sensitive-aggregate** - _Definitional drift:_ NIBRS coverage shifts 2007–2011: unmapped codes 6–7.5%/yr then 1–3% from 2012. Violent/property split slightly understated in 2007–2011. Offense-dedup keeps one offense per incident (last-write-wins), so the offense MIX can be marginally off; incident COUNTS are sound. - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Per-year services 2007–2026 (frozen historical layers). · Source updates continuously; per-year services re-pulled on demand. - _Provenance:_ ArcGIS Feature Services, one service per year (CRIME_SERVICES registry). **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — per-year 'Crime Data' Feature Services (CRIME_SERVICES registry, one frozen service per year 2007–2026). - _Why this transform exists:_ Turn 17 years of drifting per-year LMPD exports into one typed, NIBRS-categorized, geography-validated incident table — while being explicit that it measures REPORTED crime (enforcement-shaped), not crime, and that location-less offenses are dropped. - _Quarantined (live):_ 20,484 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Paginated ArcGIS query per year (resultOffset/exceededTransferLimit). | One frozen service per year is the reproducibility anchor — a fixed source to re-pull. | Fetch-stage gaps are invisible unless bronze raw is persisted; the source-count audit bounds this from the source side. | | 2 | `normalize_fields` | Three field-naming eras unified to canonical lowercase names: 2007–2019 UPPERCASE + string dates, 2020–2023 UPPERCASE + ISO dates, 2024–2026 lowercase + epoch-ms dates. | One silver schema across 17 years of source schema drift. | None — pure renaming/aliasing. | | 3 | `parse` | date_occurred parsed from 5 string formats or epoch-ms → DATE + TIME. | A typed incident_date is the temporal grain for every downstream count. | Records whose date cannot be parsed (or is absent) are SKIPPED — no incident_date, no row. | | 4 | `map_enum` | NIBRS code → offense_type via NIBRS_CATEGORIES; unmapped-but-present codes → 'unknown_unmapped'; absent code → 'unknown_true'. | Human-readable offense categories (violent/property/other) instead of raw NIBRS codes. | Offense granularity collapses to category; 2007–2011 carries more unmapped codes (6–7.5%/yr), so the violent/property split is marginally understated in that era. | | 5 | `validate_zip` | ZIP validated against the canonical silver.zip_codes MDM set (three-state: valid → keep normalized; missing → NULL; non-Louisville → quarantine). | One canonical geography; no record silently lands on an out-of-area or invented ZIP. | Missing ZIP kept as NULL (unknown, not dropped). | | 6 | `quarantine` | Location-less offenses (fraud, identity theft, cyber) stamped by LMPD with placeholder ZIP 40056 fall outside the set → quarantined to silver.quarantine. | They have no physical location and cannot be placed; keeping them would invent geography. | CITYWIDE crime undercounts by the quarantined share, biased toward non-geographic offense types. Per-ZIP crime is unaffected. This asymmetry vs. 311 is a known open question. | | | | _Live:_ 19,734 quarantined at 40056 → **~1.6% citywide undercount** | | | | 7 | `dedup` | ON CONFLICT(source_id, source_dataset) last-write-wins; source carries ~1.18 offense-rows per incident, collapsed to one incident row. | Incident grain — one row per reported incident, not per offense charge. | The offense MIX can be marginally off (last-write-wins picks one offense); incident COUNTS are sound. | | 8 | `geocode` | Block/intersection addresses matched to the LOJIC reference tables (silver.address_points ~450K / silver.street_intersections ~21K, re-ingested from source by `ingest-refs`) → silver.crime_geocode, applied back to crime.geom (block-centroid / intersection point). ~97% of addressed crime placed. | Crime carries NO coordinates at source; a point enables sub-ZIP placement and district joins. | ~2.8% of addressed crime matches neither form (highway '@...', 'COMMUNITY AT LARGE', odd formats) and stays ZIP-level only (geom NULL). The reference tables are now reproduced from source, so this is a residual match-rate limit, not a reproduction gap. | | 9 | `derive` | Where the source ZIP is absent, the geocoded point is placed into the canonical silver.zip_codes boundary (point-in-polygon) to fill zip_code, flagged zip_derived=TRUE. | Recovers ZIP for ~1,060 otherwise-unplaced records without trusting the source's own ZIP field. | Fills only where a geocoded point exists AND falls inside a boundary; edge/unmatched points stay NULL. zip_derived marks the derivation so consumers can separate it from source ZIP. | | 10 | `strip_pii` | No person-name fields are written; crime quarantine JSONB is de-identified. | No-PII-in-DB policy — private individuals' names never persist. | None relevant to analysis (names are not an analytical field); addresses retained. | _Reproduce (source → silver):_ - `civic-data ingest-zips # load the canonical ZIP MDM first` - `civic-data ingest-refs # load LOJIC geocoding refs (address points + intersections)` - `civic-data ingest crime --all --promote # source → bronze → silver, all years` - `civic-data geocode-crime --write --apply # place crime: geom + point-in-polygon derived ZIP` - ⚠ _Reproducibility gap:_ ~2.8% of addressed crime matches neither the block nor intersection form (highway '@...', 'COMMUNITY AT LARGE', non-standard strings) and remains ZIP-level only (geom NULL) — a residual match-rate limit. The LOJIC reference tables ARE now reproduced from source (ingest-refs), so the former local-only reproduction gap is closed.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `source_id` | text | yes | 0.0% | | `incident_date` | date | no | 0.0% | | `incident_time` | time without time zone | yes | 0.0% | | `offense_type` | text | no | 0.0% | | `offense_description` | text | yes | 0.0% | | `location_address` | text | yes | 0.4% | | `latitude` | double precision | yes | 3.2% | | `longitude` | double precision | yes | 3.2% | | `geom` | USER-DEFINED | yes | — | | `census_tract_geoid` | text | yes | 100.0% | | `neighborhood_id` | integer | yes | 100.0% | | `zip_code` | text | yes | 0.3% | | `premise_type` | text | yes | 0.4% | | `weapon_used` | text | yes | 100.0% | | `status` | text | yes | 0.0% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% | | `zip_derived` | boolean | no | 0.0% |
Values: offense_type (60 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | other | 136,128 | 11.0% | | other_theft | 130,296 | 10.5% | | property_destruction | 103,265 | 8.3% | | burglary | 101,675 | 8.2% | | simple_assault | 93,532 | 7.5% | | theft_from_vehicle | 84,616 | 6.8% | | drug_manufacturing | 82,870 | 6.7% | | shoplifting | 75,238 | 6.1% | | motor_vehicle_theft | 65,599 | 5.3% | | unknown_unmapped | 46,973 | 3.8% | | intimidation | 44,714 | 3.6% | | drug_possession | 37,066 | 3.0% | | aggravated_assault | 36,930 | 3.0% | | theft_from_building | 28,812 | 2.3% | | robbery | 23,917 | 1.9% | | theft_of_vehicle_parts | 22,959 | 1.8% | | family_offense | 22,840 | 1.8% | | credit_card_fraud | 21,775 | 1.8% | | identity_theft | 13,718 | 1.1% | | weapon_violation | 12,008 | 1.0% | | stolen_property | 11,245 | 0.9% | | counterfeiting | 10,318 | 0.8% | | false_pretenses | 5,576 | 0.4% | | trespass | 4,464 | 0.4% | | fondling | 3,851 | 0.3% | | _…22,392 more in tail_ | | |
Values: status (2 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | completed | 1,176,433 | 94.7% | | attempted | 66,344 | 5.3% |
### 311 service requests (Open311) - **Table:** `silver.service_requests` · **Rows:** 2,209,988 · **Trust tier:** `viewable_caveated` - **Grain:** One row per service request. UNIQUE(service_request_id, source_dataset). - **Primary key:** id · **Ingest:** `civic-data ingest 311` - **Per-year rows:** 2010:85,593 · 2011:86,921 · 2012:85,340 · 2013:79,816 · 2014:84,929 · 2015:92,838 · 2016:93,659 · 2017:94,113 · 2018:110,907 · 2019:146,953 · 2020:229,875 · 2021:216,399 · 2022:175,150 · 2023:173,244 · 2024:169,588 · 2025:182,486 · 2026:102,177 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures who CALLS 311 (and which agency logs it) — not where problems are. A non-report is invisible; absence ≠ no problem. - _Trend window:_ 2010–2018 is the honest trend window (canonical home of this claim; location quality degrades after 2018 — see definitional_drift). - _Representativeness / bias:_ Civically-engaged / higher-trust areas over-report; under-served areas under-report the same conditions. Reading request density as 'problem density' inverts the bias. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ 'other' category bucket ~66% (2010) → ~32% (2025) as service names shifted. LOCATION QUALITY DEGRADED AFTER 2018: from 2019 on the source stamps a growing share of requests with one placeholder coordinate (downtown) carrying no usable ZIP — hitting REAL categories (Streets, Traffic Signals), not just admin. 2020–2021 have no location at all. resolution_days NULL for all pre-2022 data. 2007–2009 excluded. - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Per-year services 2010–2026 (2019 split into two half-year services). · Continuous at source; re-pulled per service on demand. - _Provenance:_ ArcGIS Feature Services (SR_SERVICES registry). A large share of records carry NULL ZIP — kept as NULL (recoverable via lat/lng), never quarantined. **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — per-year '311 Service Requests' Feature Services (SR_SERVICES registry; 2019 split into two half-year services; 2007–2009 excluded). - _Why this transform exists:_ Unify Open311 exports across schema eras into one typed request table with computed resolution time and a point geometry — keeping missing-location records (unlike crime) and being explicit that it measures reporting propensity, not problem density. - _Quarantined (live):_ 178 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Paginated ArcGIS query per year/half-year. | Frozen per-year services are the reproducibility anchor. | Fetch-stage gaps invisible without persisted bronze; source-count audit bounds it. | | 2 | `normalize_fields` | Era differences reconciled: status↔status_description, closed_datetime↔closed_date, lat/long↔latitude/longitude, and the distinct 2020–2021 'Record__/Opened_Date' schema mapped to canonical names. | One silver schema across Open311 source drift. | None — renaming/aliasing. | | 3 | `derive` | When zip_code is absent, extract a trailing 5-digit ZIP from the address string (e.g. '… LOUISVILLE, KY, 40229'). | Best-effort ZIP recovery for older eras that embedded ZIP in the address. | None added; a recovery, not a drop. Unrecoverable → left for validate_zip to NULL. | | 4 | `parse` | requested/closed/updated parsed from epoch-ms → TIMESTAMPTZ. | Typed timestamps are the temporal grain and the input to resolution time. | Records with no parseable requested_datetime are SKIPPED. | | 5 | `derive` | resolution_days = max(0, closed − requested) in days. | A normalized resolution measure independent of source formatting. | NULL whenever closed is absent — all pre-2022 data has NULL resolution_days. | | 6 | `build_geom` | ST_MakePoint(lng, lat) → geom when both coordinates present. | Point geometry enables spatial joins and sub-ZIP placement. | NULL geom when coordinates are absent — a growing share post-2018 (placeholder coordinate). | | 7 | `validate_zip` | ZIP validated against the canonical silver.zip_codes MDM set (three-state). | One canonical geography. | Missing ZIP KEPT as NULL (recoverable via lat/lng); only non-Louisville ZIPs quarantined. This differs from crime, which quarantines its location-less records — a documented asymmetry. | | 8 | `derive` | Require a non-empty service_name; drop the record otherwise. | service_name is NOT NULL in silver — a nameless request has no analytical category. | Records with empty service_name are SKIPPED. | | 9 | `dedup` | ON CONFLICT(service_request_id, source_dataset) upsert. | One row per service request per source dataset. | None beyond de-duplication of re-pulled records. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest 311 --all --promote # source → bronze → silver, all years` - ⚠ _Reproducibility gap:_ Post-2018 location degradation is a SOURCE condition, faithfully preserved (NULL geom / NULL ZIP kept), not something the recipe can fix. Trend use is honest only within 2010–2018.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `service_request_id` | text | yes | 0.0% | | `requested_datetime` | timestamp with time zone | yes | 0.0% | | `updated_datetime` | timestamp with time zone | yes | 75.5% | | `closed_datetime` | timestamp with time zone | yes | 77.7% | | `status` | text | yes | 0.0% | | `service_name` | text | no | 0.0% | | `service_code` | text | yes | 100.0% | | `description` | text | yes | 28.9% | | `agency_responsible` | text | yes | 56.0% | | `address` | text | yes | 26.4% | | `latitude` | double precision | yes | 23.7% | | `longitude` | double precision | yes | 23.7% | | `geom` | USER-DEFINED | yes | — | | `census_tract_geoid` | text | yes | 100.0% | | `neighborhood_id` | integer | yes | 100.0% | | `zip_code` | text | yes | 38.2% | | `resolution_days` | integer | yes | 77.7% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: service_name (370 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | NSR Metro Agencies | 296,006 | 13.4% | | Streets | 97,481 | 4.4% | | Large Item Appointment | 81,394 | 3.7% | | Solid Waste Container Request | 76,464 | 3.5% | | Solid Waste Missed Services | 73,693 | 3.3% | | NSR Social Services | 65,553 | 3.0% | | HIGH GRASS WEEDS | 65,276 | 3.0% | | Exterior | 63,325 | 2.9% | | CRM | 60,093 | 2.7% | | STREET POTHOLE | 48,980 | 2.2% | | Parking Concern | 48,556 | 2.2% | | EXTERIOR VIOL | 44,630 | 2.0% | | NSR Miscellaneous | 43,058 | 1.9% | | Animal Issue | 41,157 | 1.9% | | High Weeds/Grass | 40,293 | 1.8% | | TRASH PVT PROP | 38,634 | 1.7% | | DAMAGE GARB CART | 34,663 | 1.6% | | NSR Government | 34,454 | 1.6% | | GARBAGE MISSED | 32,609 | 1.5% | | RECYCLE BINS | 28,470 | 1.3% | | MISCELLANEOUS | 27,560 | 1.2% | | Metro Agencies | 27,381 | 1.2% | | ABANDON VEHICLE | 25,486 | 1.2% | | DEAD ANIMAL | 25,187 | 1.1% | | Trash | 24,551 | 1.1% | | _…765,034 more in tail_ | | |
Values: status (2 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | closed | 1,420,000 | 64.3% | | open | 789,988 | 35.7% |
Values: agency_responsible (238 distinct, 56.0% NULL) | Value | Count | % | |---|--:|--:| | IPL-PROPERTY MAINTENANCE DIV. | 238,342 | 10.8% | | SWMS - ADMINISTRATION | 117,511 | 5.3% | | LPM | 94,771 | 4.3% | | SWMS - WASTE COLLECTION | 92,084 | 4.2% | | SWMS - ENFORCEMENT UNIT | 45,486 | 2.1% | | METRO POLICE DEPARTMENT | 41,057 | 1.9% | | LMPD | 30,499 | 1.4% | | Public Works Engineering Div. | 20,952 | 0.9% | | PUBLIC WORKS ADMINISTRATION | 18,318 | 0.8% | | PUBLIC WORKS OPEN SPACES DIV | 17,931 | 0.8% | | Louisville Animal Services | 16,588 | 0.8% | | SWMS - BULK WASTE | 15,412 | 0.7% | | C&R Property Maintenance Enforcement | 15,128 | 0.7% | | PUBLIC WORKS - SIGNS MARKINGS | 13,470 | 0.6% | | PUBLIC WORKS INSPECTION GROUP | 13,261 | 0.6% | | HEALTH DEPT - ENVIR. HEALTH | 12,318 | 0.6% | | ANIMAL SERVICES | 11,799 | 0.5% | | PUBLIC WORKS - TRAFFIC ENG | 11,073 | 0.5% | | PUBLIC WORKS - ELEC MAINT | 10,939 | 0.5% | | METRO PARKS | 10,206 | 0.5% | | Parking Authority River City | 8,419 | 0.4% | | KY DEPT OF TRANSPORTATION | 8,307 | 0.4% | | Public Works Electrical Maint | 7,629 | 0.3% | | PUBLIC WORKS ELECTRICAL MAINT. | 7,002 | 0.3% | | N-PICKUP DAYS | 6,954 | 0.3% | | _…86,297 more in tail_ | | |
### Land Bank Authority sales (historical dispositions) - **Table:** `silver.landbank_sales` · **Rows:** 399 · **Trust tier:** `viewable_caveated` - **Grain:** One row per disposed property. UNIQUE(parcel_id, source_dataset). - **Primary key:** id · **Ingest:** `civic-data ingest landbank` - **Per-year rows:** 2015:21 · 2016:27 · 2017:82 · 2018:99 · 2019:97 · 2020:73 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures completed Land Bank Authority DISPOSITIONS — vacant/abandoned parcels the city's land bank actually SOLD — not the distressed-property inventory nor demand. A row is a successful sale; absence means not-yet-sold or never-in-inventory. - _Representativeness / bias:_ Concentrated in historically disinvested neighborhoods where the land bank operates. Not a citywide property-sales measure; reading disposition counts as 'investment' conflates public disposal of blight with private market activity. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ The 'Historical Data' service — a completed-sales log, not a live inventory. Small N (hundreds). sale_program categories shift over the program's life; sale_amount is a raw source string (not coerced to numeric). - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single pull (historical service). · Source updates as dispositions complete; re-pulled on demand. - _Provenance:_ ArcGIS Feature Service — Landbank_Sales_Historical_Data (single pull). Owner/applicant names stripped before any DB write (no-PII policy). **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'Landbank_Sales_Historical_Data' Feature Service (single pull; a completed-dispositions log). - _Why this transform exists:_ Turn the Land Bank Authority's historical disposition export into one typed, ZIP-validated sales log — being explicit that it measures completed PUBLIC dispositions of blighted parcels (small N), not distressed inventory or market activity, and that owner/applicant names never persist. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Single ArcGIS query (historical service, hundreds of rows). | The historical service is a fixed reproducibility anchor — one pull, fully re-runnable. | Fetch-stage gaps are invisible unless bronze raw is persisted (bounded by bronze). | | 2 | `strip_pii` | OWNER + APPLICANT dropped before any DB write. | No-PII-in-DB policy — private individuals' names never persist. | None relevant to analysis; the property address is retained. | | 3 | `normalize_fields` | address assembled from ST_/DIR/STREET_NAME/ST_TYPE/Post_Dir; council_district coerced from COUNCIL_DIST → int; has_improvements from IMPROV → bool. | One typed silver schema from the source's split address + string fields. | None — assembling/typing. sale_amount kept as the raw source string (not coerced numeric). | | 4 | `validate_zip` | ZIP validated against the canonical silver.zip_codes MDM set (three-state: valid → keep; missing → NULL; non-Louisville → quarantine). | One canonical geography; no record lands on an out-of-area or invented ZIP. | Missing ZIP kept as NULL (unknown, not dropped). | | 5 | `dedup` | ON CONFLICT(parcel_id, source_dataset) upsert. | One row per disposed parcel. | None beyond de-duplication of re-pulled records. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest landbank --promote # source → bronze → silver (single pull)` - ⚠ _Reproducibility gap:_ Reproduces civic-graph EXACTLY — content hash identical (399 = 399 rows). Historical service only; there is no live-inventory feed, so this is a completed-sales log, not current holdings.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `parcel_id` | text | no | 0.0% | | `address` | text | yes | 0.0% | | `zip_code` | text | yes | 2.3% | | `neighborhood` | text | yes | 0.0% | | `council_district` | integer | yes | 0.0% | | `zoning` | text | yes | 0.0% | | `has_improvements` | boolean | yes | 0.0% | | `sale_program` | text | yes | 0.0% | | `sale_date` | date | yes | 0.0% | | `sale_amount` | text | yes | 0.3% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: sale_program (10 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | Cut It Keep It | 75 | 18.8% | | Save the Structure | 74 | 18.5% | | Side Yard | 67 | 16.8% | | RFP | 64 | 16.0% | | Budget Rate | 43 | 10.8% | | Demo for Deed | 21 | 5.3% | | Minimum Pricing Policy | 21 | 5.3% | | Flex Rate | 20 | 5.0% | | SOI | 9 | 2.3% | | Metro Redevelopment | 5 | 1.3% |
Values: zoning (19 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | R6 | 155 | 38.8% | | R5 | 71 | 17.8% | | UN | 49 | 12.3% | | R7 | 37 | 9.3% | | EZ1 | 22 | 5.5% | | C1 | 21 | 5.3% | | OR2 | 15 | 3.8% | | C2 | 11 | 2.8% | | CR | 4 | 1.0% | | TNZD | 3 | 0.8% | | M2 | 2 | 0.5% | | R4 | 2 | 0.5% | | R8A | 1 | 0.3% | | M3 | 1 | 0.3% | | R8 | 1 | 0.3% | | EZ1 | 1 | 0.3% | | CM | 1 | 0.3% | | C3 | 1 | 0.3% | | RSA | 1 | 0.3% |
### Code-enforcement lien final orders (blight signal) - **Table:** `silver.lien_orders` · **Rows:** 298 · **Trust tier:** `snapshot_only` - **Grain:** One row per case. UNIQUE(case_id, source_dataset). Geocoded at source (lat/lng → geom). - **Primary key:** id · **Ingest:** `civic-data ingest liens` - **Per-year rows:** 2025:2 · 2026:251 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures code-enforcement lien FINAL ORDERS currently active at the source (an enforcement/blight signal), not total historical liens nor total blight. A non-finalized or resolved case is invisible. - _Representativeness / bias:_ Enforcement-driven: reflects where the city has inspected AND finalized liens, shaped by inspection intensity — not raw property condition. High-enforcement areas read as high-lien. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ SOURCE VOLATILITY (the defining property): the feed is a ROLLING ~7–8-month window of active final orders — resolved cases drop off as new ones appear. A single from-source pull is a point-in-time SNAPSHOT (298 records, notification dates 2025-11→2026-06); civic-graph's 798 is an ACCUMULATION across many re-pulls over months (same case-id format, near-zero overlap with today's window). The two are not comparable as a time series and the store cannot be reproduced from one pull — snapshot only. - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single snapshot pull (2026-07). · Source churns continuously; resolved cases leave the window. - _Provenance:_ ArcGIS Feature Service — lien_notification_final_orders (single pull, geocoded at source). Inspector/owner names stripped before any DB write. **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'lien_notification_final_orders' Feature Service (single pull, geocoded at source). - _Why this transform exists:_ Turn the code-enforcement lien final-orders feed into a typed, geocoded, ZIP-validated case table — while documenting that the source is a ROLLING WINDOW of active orders, so a single from-source pull is a point-in-time SNAPSHOT, not a reproducible historical store. - _Quarantined (live):_ 136 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Single ArcGIS query of the live final-orders feed. | One pull captures the current active-order window. | SOURCE VOLATILITY, not fetch loss: orders RESOLVED before the pull have already left the feed. The feed is a rolling ~7–8-month window (our pull: notification dates 2025-11→2026-06). | | 2 | `strip_pii` | inspector + fullname dropped before any DB write. | No-PII-in-DB policy — inspector and property-owner names never persist. | None relevant to analysis; the property address is retained. | | 3 | `normalize_fields` | address from stno/predir/stname/suffix/stsub; citation_amount from final_citation_amount → float; notification/inspection/hearing dates epoch-ms → DATE. | One typed silver schema from the source's split address + epoch fields. | None — assembling/typing. | | 4 | `build_geom` | ST_MakePoint(lng, lat) → geom from the source's own coordinates. | Liens are geocoded AT SOURCE — a point enables sub-ZIP placement and district joins directly. | NULL geom only where the source omits coordinates. | | 5 | `validate_zip` | ZIP validated against the canonical silver.zip_codes MDM set (three-state). | One canonical geography. | Missing ZIP kept as NULL; non-Louisville ZIPs quarantined (the lienholder MAILING ZIP can differ from the property ZIP — those are dropped, not placed on the wrong ZIP). | | 6 | `dedup` | ON CONFLICT(case_id, source_dataset) upsert. | One row per case. | None beyond de-duplication of re-pulled records. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest liens --promote # source → bronze → silver (single snapshot)` - ⚠ _Reproducibility gap:_ ROLLING-WINDOW SOURCE — the defining reproducibility limit. A from-source pull yields the CURRENT snapshot (~298 records) with a NULL geom of 0% and dates in a ~7-month window; it does NOT reproduce civic-graph's silver (798). The 798 is an ACCUMULATION across many re-pulls over months — same case-id format, near-zero row overlap with today's window (shifted, ~1-month overlap). To build a longitudinal lien record you must ACCUMULATE snapshots over time; no single pull can reconstruct the accumulated store. Trust tier is snapshot_only for exactly this reason.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `case_id` | text | no | 0.0% | | `property_address` | text | yes | 0.0% | | `zip_code` | text | yes | 0.0% | | `latitude` | double precision | yes | 0.3% | | `longitude` | double precision | yes | 0.3% | | `geom` | USER-DEFINED | yes | — | | `notification_date` | date | yes | 15.1% | | `inspection_date` | date | yes | 15.1% | | `inspection_result` | text | yes | 0.0% | | `hearing_scheduled` | date | yes | 94.0% | | `hearing_completed` | date | yes | 96.0% | | `hearing_result` | text | yes | 96.0% | | `final_order_state` | text | yes | 0.0% | | `citation_amount` | double precision | yes | 0.0% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: final_order_state (3 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | Final By Citation | 281 | 94.3% | | Final By Hearing | 12 | 4.0% | | Rescinded | 5 | 1.7% |
Values: inspection_result (7 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | Citation | 168 | 56.4% | | Citation Referral | 63 | 21.1% | | Cancelled | 45 | 15.1% | | Emergency Referral | 16 | 5.4% | | Corrective Order Citation | 4 | 1.3% | | Hold | 1 | 0.3% | | Violation Notice | 1 | 0.3% |
### Property foreclosure filings - **Table:** `silver.property_foreclosures` · **Rows:** 1,734 · **Trust tier:** `viewable_caveated` - **Grain:** One row per case (case_number = Case_). UNIQUE(case_number, source_dataset); source carries ~1.98 rows/case, deduped to one via ON CONFLICT. - **Primary key:** id · **Ingest:** `civic-data ingest foreclosures` - **Per-year rows:** 2008:1 · 2012:10 · 2013:88 · 2014:122 · 2015:102 · 2016:73 · 2017:123 · 2018:162 · 2019:143 · 2020:60 · 2021:72 · 2022:162 · 2023:223 · 2024:155 · 2025:118 · 2026:58 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures foreclosure FILINGS (action_filed — a case initiated), not completed foreclosures nor actual loss of home. A filing may be dismissed, settled, or withdrawn; a filing is not an outcome. - _Representativeness / bias:_ Tracks judicial legal action, not neighborhood distress directly — concentrated where lenders pursue judicial foreclosure. Reading filing density as 'distress' conflates the legal process with the underlying condition. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ NO Great-Recession era — the source is dense only from ~2013, so the 2008–2012 foreclosure wave is absent (do not read the series as the full history). Source carries ~1.98 rows/case, deduped to one. Uniquely carries neighborhood names + census tract IDs. A small set of old-numbered cases (12-CI-…, 13-CI-…) carry NULL action_filed. The source GROWS (new filings accrue) and CHURNS (old cases occasionally drop or re-appear), so totals drift from an earlier pull while shared historical years match row-for-row. The source DECLARES a parcel-ID field (Full_Parcel_ID) but ships it EMPTY, so parcel_id is all-NULL — parcel-level joins are impossible; join on address/geography instead. - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single pull. · New filings accrue continuously; re-pulled on demand. - _Provenance:_ ArcGIS Feature Service — Louisville_Metro_KY_Property_Foreclosures (single pull). Purchaser/case-style names stripped before any DB write; only a derived is_metro_purchase boolean kept. **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'Louisville_Metro_KY_Property_Foreclosures' Feature Service (single pull). - _Why this transform exists:_ Turn the county foreclosure-filings export into one typed, deduped, ZIP-validated case table carrying neighborhood + census tract — measuring FILINGS (a case initiated), not completed loss of home, dense only from ~2013, with purchaser names replaced by a derived metro-buyer boolean. - _Quarantined (live):_ 2 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Single ArcGIS query. | One pull is the reproducibility anchor. | Fetch-stage gaps bounded by bronze; the source itself grows (new filings) and churns (old cases occasionally drop/re-appear), so a later pull differs from an earlier one. | | 2 | `derive` | _is_metro_purchase = 'METRO' in Purchaser, computed BEFORE PII stripping. | Keep the analytical signal (was the buyer Metro/Landbank?) without keeping the name. | The specific purchaser identity is intentionally not retained — only the boolean survives. | | 3 | `strip_pii` | Purchaser + Case_Style dropped before any DB write. | No-PII-in-DB policy — private buyers' names never persist. | None relevant to analysis; the property address is retained. | | 4 | `normalize_fields` | address from House_Nr/Dir/Street_Name/St_Type/Post_Dir; council_district from CD → int; census_tract from Census_Tract; action_filed/sale_date epoch-ms → DATE. | One typed silver schema from the source's split address + epoch fields. | None — assembling/typing. sale_price kept as the raw source string (not coerced numeric). | | 5 | `validate_zip` | ZIP validated against the canonical silver.zip_codes MDM set (three-state). | One canonical geography. | Missing ZIP kept as NULL (unknown, not dropped). | | 6 | `dedup` | ON CONFLICT(case_number, source_dataset) last-write-wins; source carries ~1.98 rows/case, collapsed to one case row. | Case grain — one row per foreclosure case, not per source line. | Multi-line cases collapse to one (last-write-wins on the sale fields); case COUNTS are sound. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest foreclosures --promote # source → bronze → silver (single pull)` - ⚠ _Reproducibility gap:_ NO GREAT-RECESSION ERA — the source is dense only from ~2013; the 2008–2012 foreclosure wave is absent and not recoverable from this source. Trend use is honest only from ~2013 on. - ⚠ _Reproducibility gap:_ SOURCE GROWTH + CHURN — our pull (1,734 cases) SHARES 1,394 case numbers with civic-graph (1,493); +340 are newer/re-added and −99 have churned off since civic-graph's pull, while shared historical years match row-for-row (±1–2). 62 old-numbered cases (12-CI-…, 13-CI-…) carry NULL action_filed (57 new-to-source, 5 also in civic-graph) — a source condition, KEPT (not skipped), matching civic-graph's own treatment.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `address` | text | yes | 0.0% | | `zip_code` | text | yes | 2.5% | | `neighborhood` | text | yes | 2.1% | | `census_tract` | text | yes | 0.1% | | `council_district` | integer | yes | 0.5% | | `parcel_id` | text | yes | 100.0% | | `action_filed` | date | yes | 3.6% | | `sale_date` | date | yes | 13.4% | | `sale_price` | text | yes | 100.0% | | `is_metro_purchase` | boolean | yes | 0.0% | | `case_number` | text | yes | 0.0% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: is_metro_purchase (2 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | true | 972 | 56.1% | | false | 762 | 43.9% |
### Permitted food service establishments - **Table:** `silver.food_service_establishments` · **Rows:** 3,366 · **Trust tier:** `snapshot_only` - **Grain:** One row per permit. UNIQUE(permit_number, source_dataset). - **Primary key:** id · **Ingest:** `civic-data ingest food-service` - **Per-year rows:** _no honest temporal column_ **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures the CURRENT roster of permitted food-service establishments — who holds a permit now — not restaurant openings/closings over time. Carries opening_date but NO closure data, so it is an ambiguous time signal; treat it as a current-state roster. - _Representativeness / bias:_ Permitted establishments only; unpermitted/exempt food operations are invisible. Density tracks commercial food activity, weighted toward areas with formal businesses. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ A living roster: establishments enter (new permits) and leave (closures) between pulls, so a fresh pull differs modestly from an earlier one (~96% overlap with civic-graph). opening_date is kept as the raw source string. - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single pull (current roster). · Source updated as permits are issued/closed; re-pulled on demand. - _Provenance:_ ArcGIS Feature Service — Permitted_Food_Service_Establishments (single pull). Premise names are business identifiers (kept). **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'Permitted_Food_Service_Establishments' Feature Service (single pull; a current permit roster). - _Why this transform exists:_ Turn the permitted food-service roster into one typed, ZIP-validated table — being explicit that it is a CURRENT roster of permit holders (opening_date but no closures), not a time series of openings/closings. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Single ArcGIS query (~3.4K rows). | One pull captures the current roster. | Fetch-stage gaps bounded by bronze; the roster itself churns (permits open/close). | | 2 | `normalize_fields` | premise_name/address/city, facility_type(+description), subtype(+description) mapped to canonical columns; opening_date kept as raw text. | One typed silver schema. | None — renaming/typing. opening_date not coerced to DATE (raw source string). | | 3 | `validate_zip` | premise_zip validated against silver.zip_codes MDM (three-state). | One canonical geography. | Missing ZIP kept NULL (0% here); non-Louisville quarantined. | | 4 | `dedup` | ON CONFLICT(permit_number, source_dataset) upsert. | One row per permit. | None beyond de-duplication of re-pulled records. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest food-service --promote # source → bronze → silver (single pull)` - ⚠ _Reproducibility gap:_ CURRENT-ROSTER SOURCE — a fresh pull is who-holds-a-permit-now (3,366), ~96% overlapping civic-graph's earlier 3,391; the small difference is establishments entering/leaving between pulls. No closure data means no honest openings/closings trend from this source.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `permit_number` | integer | no | 0.0% | | `premise_name` | text | yes | 0.0% | | `premise_address` | text | yes | 6.6% | | `city` | text | yes | 0.0% | | `zip_code` | text | yes | 0.0% | | `facility_type` | integer | yes | 0.0% | | `facility_type_description` | text | yes | 0.0% | | `subtype` | integer | yes | 0.0% | | `subtype_description` | text | yes | 0.0% | | `opening_date` | text | yes | 0.4% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: facility_type_description (4 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | FOOD SERVICE ESTABLISHMENT | 2,613 | 77.6% | | RETAIL/FOOD ESTABLISHMENT | 357 | 10.6% | | RETAIL FOOD STORES | 335 | 10.0% | | RESTRICTED FOOD CONCESSIONS | 61 | 1.8% |
Values: subtype_description (16 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | FOOD SERVICE | 1,980 | 58.8% | | RETAIL-FOOD 10,000 SQ FT OR LESS | 298 | 8.9% | | PRE-PACKAGED RETAIL | 234 | 7.0% | | FOOD SERVICE (DAY CARE / CHILD CARE CENTER | 181 | 5.4% | | SELF-CONTAINED MOBILE FOOD UNITS | 167 | 5.0% | | SCHOOL CAFETERIA OR FOOD SERVICE | 148 | 4.4% | | FOOD SERVICE (HOSPITAL / NURSING HOME | 71 | 2.1% | | RETAIL-FOOD 10,001 SQ FT OR OVER | 59 | 1.8% | | CATERERS | 40 | 1.2% | | STATIONARY RESTRICTED CONCESSON | 36 | 1.1% | | MOBILE RETAIL UNIT | 32 | 1.0% | | SUPERMARKET WITH PROCESSING | 30 | 0.9% | | RETAIL BAKERY | 29 | 0.9% | | FOOD SERVICE COMMISSARY | 26 | 0.8% | | MOBILE RESTRICTED CONCESSION | 25 | 0.7% | | RETAIL WITH LIMITED SERVICE | 10 | 0.3% |
### ABC (alcohol) licenses — local + state - **Table:** `silver.abc_licenses` · **Rows:** 7,149 · **Trust tier:** `snapshot_only` - **Grain:** One row per license. UNIQUE(license_number, source_dataset). TWO sources: abc-local-licenses (Metro) + abc-state-licenses. - **Primary key:** id · **Ingest:** `civic-data ingest abc` - **Per-year rows:** _no honest temporal column_ **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures CURRENTLY-active alcohol licenses (a commercial nightlife/dining signal) from two authorities — Metro (local) + state — not a time series of licensing activity. A lapsed/expired license leaves the active set. - _Representativeness / bias:_ Two overlapping authorities with different fields; the local subset carries endorsement detail, the state subset does not. Reading license density as 'nightlife intensity' conflates permit count with actual activity. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ Active-license lists CHURN (renewals/expiries) + grow modestly between pulls. ENRICHMENT: the endorsements dataset fills license_category (only when NULL — ingest wins) and a semicolon-joined endorsement list on the LOCAL subset only; state licenses have no endorsement source and keep pattern-based classification (this is a CLASSIFICATION-coverage lift, not a universe lift). ZIP is parsed from a free address string (local) or PremisesCityState (state). - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single pull each source (current active licenses). · Continuously updated at source; re-pulled on demand. - _Provenance:_ ArcGIS Feature Services — abc_license_ait4135 (local) + ABC_State_ActiveLicenses (state) + abc_license_endorsements (enrichment). Business/DBA names are business identifiers (kept). **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'abc_license_ait4135' (local) + 'ABC_State_ActiveLicenses' (state) Feature Services, enriched from 'abc_license_endorsements'. Single pull each. - _Why this transform exists:_ Merge two active-license authorities (Metro + state) into one typed, geocoded, ZIP-validated table and enrich the local subset with endorsement classification — being explicit that it is a CURRENT active-license footprint, not a licensing time series. - _Quarantined (live):_ 15 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | One ArcGIS query per source (local ~1.7K, state ~5.5K). | One pull per authority captures the current active set. | Active-license lists churn (renewals/expiries); a lapsed license leaves the set. | | 2 | `normalize_fields` | Two different source schemas mapped to one: local (LicenseNO/BusinessName/LicenseCat/LicStatus/GPSX-Y/EXPDTTM/CouncilDist) and state (LicenseNumber/DBA/Status/PremisesStreet/City/Latitude-Longitude/IssueDate/ExpiryDate). | One silver schema across two authorities. | Fields absent in one source are NULL there (local has no city/issue_date; state has no license_category/council_district). | | 3 | `derive` | ZIP parsed as the last 5-digit token of the free address string (local) or PremisesCityState (state). | Neither source ships a clean ZIP field. | Unparseable → left for validate_zip to NULL. | | 4 | `build_geom` | ST_MakePoint(lng, lat) → geom from the source coordinates. | Both sources are geocoded — a point enables sub-ZIP placement. | NULL geom where coordinates absent. | | 5 | `validate_zip` | ZIP validated against silver.zip_codes MDM (three-state). | One canonical geography. | Missing kept NULL (~0.1%); non-Louisville quarantined (mailing vs premises ZIP). | | 6 | `dedup` | ON CONFLICT(license_number, source_dataset) upsert. | One row per license per authority. | None beyond de-duplication of re-pulled records. | | 7 | `derive` | ENRICH (post-promote): the endorsements dataset fills license_category (only when NULL — ingest wins) and a semicolon-joined endorsement list on the LOCAL subset. | Improves restaurant-vs-retail CLASSIFICATION accuracy on the local subset. | Classification-coverage lift only — NOT a universe lift; state licenses have no endorsement source and keep pattern-based classification. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest abc --promote # local + state → bronze → silver, then endorsement enrich` - ⚠ _Reproducibility gap:_ ACTIVE-LICENSE SOURCE — a fresh pull is the current active set (7,149: local 1,681 / state 5,468), high-overlap with civic-graph's earlier 7,038 (local 1,635 / state 5,403), differing by renewal/expiry churn + modest growth. Endorsement enrichment reproduced (1,593 local licenses enriched vs civic-graph's 1,556). Not a licensing time series.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `license_number` | text | no | 0.0% | | `business_name` | text | yes | 0.1% | | `license_category` | text | yes | 76.5% | | `license_type` | text | yes | 0.0% | | `status` | text | yes | 0.0% | | `address` | text | yes | 0.0% | | `city` | text | yes | 23.5% | | `zip_code` | text | yes | 0.1% | | `council_district` | text | yes | 76.5% | | `latitude` | double precision | yes | 0.1% | | `longitude` | double precision | yes | 0.1% | | `geom` | USER-DEFINED | yes | — | | `issue_date` | date | yes | 23.5% | | `expiry_date` | date | yes | 0.0% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% | | `endorsements` | text | yes | 77.7% |
Values: license_type (50 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | Alcoholic Beverage Annual License | 1,681 | 23.5% | | Special Sunday Retail Drink License | 907 | 12.7% | | NQ Retail Malt Beverage Package License | 849 | 11.9% | | Tobacco, Nicotine, or Vapor Product License | 816 | 11.4% | | NQ2 Retail Drink License | 585 | 8.2% | | NQ4 Retail Malt Beverage Drink License | 517 | 7.2% | | Quota Retail Package License | 423 | 5.9% | | Quota Retail Drink License | 345 | 4.8% | | Sampling License | 259 | 3.6% | | Supplemental Bar License | 155 | 2.2% | | Caterer's License | 75 | 1.0% | | Transporter's License | 60 | 0.8% | | NQ3 Retail Drink License | 59 | 0.8% | | Limited In-State Distilled Spirits Supplier's License | 37 | 0.5% | | Distilled Spirits and Wine Storage License | 36 | 0.5% | | NQ1 Retail Drink License | 27 | 0.4% | | Microbrewery License | 26 | 0.4% | | Vintage Distilled Spirits License | 23 | 0.3% | | Direct Shipper License | 22 | 0.3% | | Distiller's License - Class B | 20 | 0.3% | | Bottling House or Bottling House Storage License | 19 | 0.3% | | Extended Hours Supplemental License | 18 | 0.3% | | Special Temporary Alcoholic Beverage Auction License | 17 | 0.2% | | Limited Non Quota Package License | 17 | 0.2% | | Wholesaler's License | 15 | 0.2% | | _…141 more in tail_ | | |
Values: status (4 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | Active | 7,051 | 98.6% | | Out of Business | 96 | 1.3% | | Closed | 1 | 0.0% | | Revoked | 1 | 0.0% |
Values: license_category (22 distinct, 76.5% NULL) | Value | Count | % | |---|--:|--:| | Restaurant / Cafe | 551 | 7.7% | | Grocery / Food Mart | 352 | 4.9% | | Liquor Store | 248 | 3.5% | | Bar | 162 | 2.3% | | Retail | 55 | 0.8% | | Hotels | 54 | 0.8% | | Manufacturer | 50 | 0.7% | | Private Club | 42 | 0.6% | | Church / School | 35 | 0.5% | | Drug Store | 26 | 0.4% | | Catering | 17 | 0.2% | | Amusement | 16 | 0.2% | | Golf Course | 14 | 0.2% | | Theatre / Museum | 12 | 0.2% | | Distributor / Wholesaler | 10 | 0.1% | | Country Club | 10 | 0.1% | | Convention Center Complex | 9 | 0.1% | | Airport | 6 | 0.1% | | River Boat | 4 | 0.1% | | Sports / Fitness | 4 | 0.1% | | Bowling Alley | 3 | 0.0% | | Horse Race Track | 1 | 0.0% |
### Restaurant inspection scores - **Table:** `silver.restaurant_inspections` · **Rows:** 11,273 · **Trust tier:** `viewable_caveated` - **Grain:** One row per inspection. UNIQUE(inspection_id, source_dataset); an establishment recurs across inspections. - **Primary key:** id · **Ingest:** `civic-data ingest inspections` - **Per-year rows:** 2025:6,004 · 2026:5,269 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures health-inspection events + scores within the source's window — not overall food safety nor a full inspection history. A score is one inspection at one time. - _Representativeness / bias:_ Inspection frequency is regulator-driven; more-inspected categories appear more. Score distributions reflect enforcement cadence, not just establishment quality. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ ROLLING ~12-MONTH WINDOW: the source holds only about the last year of inspections (our pull 2025-07→2026-07; civic-graph's 2025-03→2026-03). A single pull is a recent window, NOT a multi-year archive — do not build a long time series from one pull. The window shift between pulls explains partial overlap with civic-graph (both hold ~11K, sharing ~7.9K). - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single pull (rolling ~12-month window). · Continuously updated; oldest inspections roll off the window. - _Provenance:_ ArcGIS Feature Service — FoodServiceData (single pull). Establishment names are business identifiers (kept). **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'FoodServiceData' Feature Service (single pull; a rolling ~12-month window). - _Why this transform exists:_ Turn the inspection-scores feed into one typed, ZIP-validated table — being explicit that the source holds only a ROLLING ~12-month window, so a single pull is a recent window, not a multi-year inspection history. - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Single ArcGIS query (~11K rows). | One pull captures the current ~12-month window. | ROLLING WINDOW, not fetch loss: inspections older than ~12 months have already rolled off. | | 2 | `normalize_fields` | EstablishmentID/InspectionID, Ins_TypeDesc, EstablishmentName, TypeDescription, score, Grade mapped to canonical columns. | One typed silver schema. | Records with an empty establishment name are SKIPPED. | | 3 | `parse` | InspectionDate '2025-06-04 00:00:00' truncated to the leading DATE. | A typed inspection_date is the temporal grain. | None — deterministic truncation. | | 4 | `validate_zip` | Zip zero-padded to 5, validated against silver.zip_codes MDM (three-state). | One canonical geography. | Missing kept NULL (~0%); non-Louisville quarantined. | | 5 | `dedup` | ON CONFLICT(inspection_id, source_dataset) upsert. | One row per inspection. | None beyond de-duplication of re-pulled records. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest inspections --promote # source → bronze → silver (rolling window)` - ⚠ _Reproducibility gap:_ ROLLING ~12-MONTH WINDOW — the source holds only the last ~year of inspections (our pull 2025-07→2026-07; civic-graph's 2025-03→2026-03). A single pull cannot reproduce a multi-year archive; the ~4-month window shift explains the partial overlap (both ~11K, sharing ~7.9K). A longitudinal inspection record requires accumulating windows over time.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `establishment_id` | integer | yes | 0.0% | | `inspection_id` | integer | no | 0.0% | | `inspection_type` | text | yes | 0.0% | | `establishment_name` | text | yes | 0.0% | | `address` | text | yes | 17.4% | | `city` | text | yes | 0.0% | | `zip_code` | text | yes | 0.0% | | `establishment_type` | text | yes | 0.0% | | `inspection_date` | date | yes | 0.0% | | `score` | integer | yes | 5.2% | | `grade` | text | yes | 36.3% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: grade (3 distinct, 36.3% NULL) | Value | Count | % | |---|--:|--:| | A | 6,790 | 60.2% | | C | 351 | 3.1% | | B | 37 | 0.3% |
Values: establishment_type (19 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | FOOD SERVICE | 5,452 | 48.4% | | RETAIL-FOOD 10,000 SQ FT OR LESS | 1,596 | 14.2% | | TEMPORARY FOODS | 1,406 | 12.5% | | FOOD SERVICE (DAY CARE / CHILD CARE CENTER | 459 | 4.1% | | SELF-CONTAINED MOBILE FOOD UNITS | 453 | 4.0% | | PRE-PACKAGED RETAIL | 444 | 3.9% | | SCHOOL CAFETERIA OR FOOD SERVICE | 421 | 3.7% | | RETAIL-FOOD 10,001 SQ FT OR OVER | 367 | 3.3% | | FOOD SERVICE (HOSPITAL / NURSING HOME | 168 | 1.5% | | CATERERS | 93 | 0.8% | | FOOD SERVICE COMMISSARY | 88 | 0.8% | | SUPERMARKET WITH PROCESSING | 78 | 0.7% | | STATIONARY RESTRICTED CONCESSON | 77 | 0.7% | | MOBILE RETAIL UNIT | 66 | 0.6% | | MOBILE RESTRICTED CONCESSION | 41 | 0.4% | | RETAIL BAKERY | 34 | 0.3% | | RETAIL WITH LIMITED SERVICE | 15 | 0.1% | | FARMERS MARKET TEMPORARY | 14 | 0.1% | | VENDING MACHINE COMPANY | 1 | 0.0% |
### Active construction permits - **Table:** `silver.construction_permits` · **Rows:** 23,308 · **Trust tier:** `viewable_caveated` - **Grain:** One row per permit. UNIQUE(permit_number, source_dataset). Geocoded at source. - **Primary key:** id · **Ingest:** `civic-data ingest permits` - **Per-year rows:** 2014:1,099 · 2015:1,032 · 2016:1,297 · 2017:1,318 · 2018:1,658 · 2019:2,599 · 2020:232 · 2021:54 · 2022:157 · 2023:280 · 2024:3,145 · 2025:4,711 · 2026:5,672 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures ACTIVE construction permits (an investment/development signal), not all permits ever issued nor completed construction. A permit that closes leaves the active set. - _Representativeness / bias:_ Permit activity concentrates where formal development happens; informal or unpermitted work is invisible. Project_costs are self-declared at application. - _Sensitivity / PII tier:_ **public-aggregate-safe** - _Definitional drift:_ ACTIVE-permit rolling set: historical issue-years are stable (2019 matches civic-graph within ~5), but recent composition shifts as permits close and leave while new ones enter (between civic-graph's pull and ours, 2025 dropped ~3,063 and 2026 gained ~2,751). PLUS a known 2020–2022 record-capture discontinuity (the source under-publishes those years) — do NOT read 2019→2020 permit movement as a real economic signal. contractor names stripped at ingest. - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single pull (active set). · Continuously updated; closed permits leave the active set. - _Provenance:_ ArcGIS Feature Service — active_construction_permits (single pull). Contractor (possible sole-proprietor name) stripped before any DB write. **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'active_construction_permits' Feature Service (single pull; a live ACTIVE-permit set). - _Why this transform exists:_ Turn the active-construction-permits feed into one typed, geocoded, ZIP-validated table with project cost + neighborhood — being explicit that it is the ACTIVE set (closed permits leave) and that 2020–2022 has a source capture discontinuity. - _Quarantined (live):_ 8 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Single ArcGIS query (~23K rows). | One pull captures the current active-permit set. | ACTIVE SET, not fetch loss: permits that have closed have already left the source. | | 2 | `strip_pii` | CONTRACTOR (possible sole-proprietor name) dropped before any DB write. | No-PII-in-DB policy. | None relevant to analysis; the site address is retained. | | 3 | `normalize_fields` | PERMIT_TYPE/STATUS, CATEGORY_NAME, WORK_TYPE, ZONING, SQFT, PERMIT_FEE, PROJECT_COSTS, DISTRICT, NEIGHBORHOOD mapped to canonical columns. | One typed silver schema. | None — renaming/typing. project_costs are self-declared at application. | | 4 | `parse` | ISSUE_DATE epoch-ms → DATE. | A typed issue_date is the temporal grain. | None. | | 5 | `build_geom` | ST_MakePoint(lng, lat) → geom from the source coordinates. | Permits are geocoded at source — a point enables sub-ZIP placement and district joins. | NULL geom where coordinates absent. | | 6 | `validate_zip` | ZIPCODE validated against silver.zip_codes MDM (three-state). | One canonical geography. | Missing kept NULL (~0.2%); non-Louisville quarantined. | | 7 | `dedup` | ON CONFLICT(permit_number, source_dataset) upsert. | One row per permit. | None beyond de-duplication of re-pulled records. | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first` - `civic-data ingest permits --promote # source → bronze → silver (active set)` - ⚠ _Reproducibility gap:_ ACTIVE-PERMIT ROLLING SET — historical issue-years are stable (2019 matches civic-graph within ~5), but recent composition shifts as permits close and leave while new ones enter (between civic-graph's pull and ours, 2025 dropped ~3,063 and 2026 gained ~2,751); totals stay near ~24K. Overlap with civic-graph is ~19.2K of ~23–24K each. - ⚠ _Reproducibility gap:_ 2020–2022 CAPTURE DISCONTINUITY — the source under-publishes those years (a records artifact, not an economic signal). Do NOT read 2019→2020 permit movement as real.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `permit_number` | text | no | 0.0% | | `permit_type` | text | yes | 0.0% | | `permit_status` | text | yes | 0.0% | | `category` | text | yes | 60.1% | | `work_type` | text | yes | 53.0% | | `zoning` | text | yes | 0.6% | | `sqft` | double precision | yes | 63.2% | | `permit_fee` | double precision | yes | 0.3% | | `project_costs` | double precision | yes | 12.7% | | `address` | text | yes | 0.0% | | `zip_code` | text | yes | 0.2% | | `latitude` | double precision | yes | 0.3% | | `longitude` | double precision | yes | 0.3% | | `geom` | USER-DEFINED | yes | — | | `council_district` | text | yes | 0.5% | | `neighborhood` | text | yes | 87.2% | | `issue_date` | date | yes | 0.2% | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: permit_type (29 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | HVAC Residential | 5,638 | 24.2% | | Electrical Residential | 4,259 | 18.3% | | Residential Alteration | 2,435 | 10.4% | | Residential New | 1,727 | 7.4% | | Residential Addition | 1,718 | 7.4% | | Electrical Commercial | 1,594 | 6.8% | | HVAC Commercial | 1,255 | 5.4% | | Commercial Alteration | 1,130 | 4.8% | | PoolSpa Residential | 488 | 2.1% | | Fire Suppression System | 411 | 1.8% | | Commercial New | 402 | 1.7% | | Change of Use | 347 | 1.5% | | Commercial Addition | 288 | 1.2% | | Wrecking Permit | 277 | 1.2% | | Fire Detection | 252 | 1.1% | | Fireplace | 215 | 0.9% | | Tent | 211 | 0.9% | | Residential Foundation | 141 | 0.6% | | Commercial Foundation | 127 | 0.5% | | Parking Lot | 122 | 0.5% | | Cell Tower | 85 | 0.4% | | Mechanical Refrigeration | 81 | 0.3% | | Range Hood | 27 | 0.1% | | Gas Restoration Pemit | 27 | 0.1% | | PoolSpa Commercial | 17 | 0.1% | | _…34 more in tail_ | | |
Values: permit_status (1 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | Issued | 23,308 | 100.0% |
Values: category (6 distinct, 60.1% NULL) | Value | Count | % | |---|--:|--:| | Single Family | 7,734 | 33.2% | | Commercial | 888 | 3.8% | | Multi Family | 301 | 1.3% | | Multifamily | 250 | 1.1% | | Condo/Patio | 109 | 0.5% | | Multi-Family | 14 | 0.1% |
### Short-Term Rental registrations - **Table:** `silver.str_licenses` · **Rows:** 1,248 · **Trust tier:** `snapshot_only` - **Grain:** One row per registered STR license. UNIQUE(license_number, source_dataset). - **Primary key:** id · **Ingest:** `civic-data ingest str` - **Per-year rows:** 2025:540 · 2026:708 **Honesty overlay (what it is)** — - _Measures vs assumes:_ Measures CURRENTLY-registered short-term rentals (a supply-side housing- pressure signal — residential addresses pulled toward the STR market), not the full STR footprint. Unregistered/unpermitted STRs are invisible, so this understates true STR pressure. - _Representativeness / bias:_ Registration compliance varies; areas with more enforcement/compliance over-report. Reading registered-STR density as total STR pressure understates the unlicensed gap. - _Sensitivity / PII tier:_ **person-naming** - _Definitional drift:_ SOURCE CARRIES PII — host name/email/phone + owner name — ALL stripped at ingest (never persist in bronze, silver, or quarantine). A living registration roster: licenses enter (new registrations) and leave (closures) between pulls, so a fresh pull is a current-state snapshot. Address is block-level (ADDR_FULL_BLOCK, no unit). ZIP is derived spatially from the point (point-in-polygon), not the source. This is a DIFFERENT, better source than civic-graph's token-gated conditional-use-permit dataset (which it supersedes). - _License / terms:_ Louisville Metro Open Data — published under the Open Data Commons Public Domain Dedication and License (PDDL; verified 2026-07-11 at data.louisvilleky.gov/pages/terms-of-use-and-license, backed by Open Data Ordinance O-243-22). Share/create/adapt permitted, no attribution required; when republishing modified data, identify the source, version, and modifications made - which this project's generated lineage layer does per dataset. We license only our added value (normalization + documentation), never the city's underlying data - see LICENSE-data (CC BY 4.0). - _As-of / cadence:_ Single public pull (current registrations; source updated continuously). · Continuously updated at source; re-pulled on demand (public, no token). - _Provenance:_ ArcGIS Feature Service — short_term_rental_registration (single pull, PUBLIC). HOSTNAME/HOSTEMAIL/HOSTPHONE/OWNERNAME stripped before any DB write; block address + license kept. **Transformation & lineage** (transactional) — _✓ verified against live schema_ - _Source system:_ Louisville Metro / LOJIC ArcGIS Hub — 'short_term_rental_registration' Feature Service (single PUBLIC pull; supersedes civic-graph's token-gated conditional-use dataset). - _Why this transform exists:_ Turn the registered-STR feed into one typed, spatially-ZIP'd table as a supply-side housing-pressure signal — while STRIPPING all four private-individual fields the source carries (host name/email/phone + owner name) so no PII ever persists, and being explicit that it is a current registration roster (not the full unlicensed STR footprint). - _Quarantined (live):_ 0 records | # | Step | What it does | Why | What's lost / changed | |--:|---|---|---|---| | 1 | `fetch` | Single ArcGIS query of the PUBLIC registration service (no token; ~1.25K). | Public + live means genuine from-source reproduction — a real upgrade over civic-graph's token-gated conditional-use archive, which this dataset supersedes. | Registration roster churns (licenses open/close); a fresh pull is a current snapshot. | | 2 | `strip_pii` | HOSTNAME, HOSTEMAIL, HOSTPHONE, OWNERNAME dropped BEFORE any DB write (validation.strip_pii_keys) — never reach bronze, silver, or quarantine. | Hard no-PII-in-DB policy: private hosts' names, emails, and phone numbers never persist, even though the city publishes them openly. | Four private fields intentionally not retained; the block address + license number are kept (a permit is a public act at a public address). | | 3 | `normalize_fields` | LICENSE_NUMBER (key), ADDR_FULL_BLOCK → one-line block address, COUNCILDISTRICT → int, POLICEDISTRICT, ZONINGCODE, DATEOPENED epoch-ms → DATE. | One typed silver schema fit to this registration source. | None — assembling/typing. ADDR_FULL_BLOCK is block-level (no unit). | | 4 | `geocode` | ZIP DERIVED spatially: derive_zip_from_point(lng, lat) point-in-polygon against silver.zip_codes (the source ZIP is not trusted). | A geographic ZIP that came FROM the canonical set, not the source's own field. | A point outside the 41-ZIP set → NULL zip (kept, not quarantined) — ~1 record. | | 5 | `build_geom` | ST_MakePoint(lng, lat) → geom from the source coordinates. | Point geometry enables sub-ZIP placement and district joins. | NULL geom only where coordinates absent. | | 6 | `dedup` | ON CONFLICT(license_number, source_dataset) upsert. | One row per registered license. | Duplicate license rows in the pull collapse to one (~4). | _Reproduce (source → silver):_ - `civic-data ingest-zips # canonical ZIP MDM first (spatial ZIP derivation needs it)` - `civic-data ingest str --promote # PUBLIC source → bronze (PII-stripped) → silver` - ⚠ _Reproducibility gap:_ DIFFERENT + BETTER SOURCE than civic-graph. civic-graph used the token-gated conditional-use-permit dataset via a captured 2026-05 GeoJSON (1,089 rows, PII-free). civic-data pulls the PUBLIC, live registration service (1,252 rows, PII-laden → stripped). The two are not row-comparable — different datasets. This one is fresher and genuinely re-pullable from source. - ⚠ _Reproducibility gap:_ CURRENT-ROSTER SOURCE — registered STRs only; the large unlicensed/unregistered footprint is invisible, so density understates true STR pressure. A single pull is a current snapshot, not a time series.
Fields + NULL rates | Field | Type | Nullable | NULL % | |---|---|---|--:| | `id` | bigint | no | 0.0% | | `license_number` | text | no | 0.0% | | `address` | text | yes | 0.0% | | `zip_code` | text | yes | 0.0% | | `council_district` | integer | yes | 0.0% | | `police_district` | text | yes | 0.0% | | `zoning_code` | text | yes | 0.0% | | `date_opened` | date | yes | 0.0% | | `latitude` | double precision | yes | 0.1% | | `longitude` | double precision | yes | 0.1% | | `geom` | USER-DEFINED | yes | — | | `source_dataset` | text | no | 0.0% | | `bronze_id` | bigint | yes | 0.0% |
Values: zoning_code (28 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | R5 | 307 | 24.6% | | R6 | 134 | 10.7% | | R4 | 101 | 8.1% | | OR2 | 95 | 7.6% | | C1 | 92 | 7.4% | | (C3) | 83 | 6.7% | | (C2) | 72 | 5.8% | | TNZD | 57 | 4.6% | | C2 | 53 | 4.2% | | EZ1 | 48 | 3.8% | | R5B | 41 | 3.3% | | R5A | 36 | 2.9% | | R7 | 27 | 2.2% | | UN | 22 | 1.8% | | OR3 | 19 | 1.5% | | PVD | 18 | 1.4% | | CM | 7 | 0.6% | | C3 | 6 | 0.5% | | (OR1) | 6 | 0.5% | | (R4) | 5 | 0.4% | | R3 | 5 | 0.4% | | CR | 4 | 0.3% | | CN | 3 | 0.2% | | R2 | 2 | 0.2% | | OR1 | 2 | 0.2% | | _…3 more in tail_ | | |
Values: police_district (12 distinct, 0.0% NULL) | Value | Count | % | |---|--:|--:| | 5 | 392 | 31.4% | | 4 | 348 | 27.9% | | 1 | 260 | 20.8% | | 6 | 100 | 8.0% | | 8 | 62 | 5.0% | | 7 | 34 | 2.7% | | 3 | 30 | 2.4% | | ST MATTHEWS | 8 | 0.6% | | 2 | 6 | 0.5% | | AUDUBON PARK | 4 | 0.3% | | MIDDLETOWN | 3 | 0.2% | | LYNDON | 1 | 0.1% |
--- ## 5. Reference / dimension tables | Table | Rows | Note | |---|--:|---| | `silver.zip_codes` | 41 | Canonical 41-ZIP Louisville boundary set (ArcGIS portal). MASTER/REFERENCE data (MDM): every event table validates its ZIP against this set. See the lineage layer's MDM record. | | `silver.address_points` | 450,308 | LOJIC Jefferson County address points (~450K). Geocoding BUILD-AID (MDM-lite): resolves LMPD crime block-addresses (no coordinates at source) to a point via street_key + hundred-block range. Full-refresh from the LOJIC MapServer; not a consumer domain. | | `silver.street_intersections` | 21,048 | LOJIC Jefferson County street intersections (~21K). Geocoding BUILD-AID: resolves crime intersection-addresses ('A ST / B AVE') to the intersection point via a sorted normalized street-key pair. Full-refresh from the LOJIC MapServer. | | `silver.zip_to_district` | 133 | DERIVED crosswalk: ZIP ↔ Council district area overlap (bidirectional coverage fractions, ::geography, sliver-filtered >0.001). Built by `ingest-geo` from the boundary sets; version-scoped. Lets ZIP-grained events roll up to districts. Matches civic-graph exactly (133). | | `silver.zip_to_neighborhood` | 318 | DERIVED crosswalk: ZIP ↔ neighborhood area overlap. Built by `ingest-geo` (PostGIS overlay). Matches civic-graph exactly (318). | | `silver.zip_to_hud_zone` | 159 | DERIVED crosswalk: ZIP ↔ HUD 1979 grant-zone area overlap. Built by `ingest-geo` (PostGIS overlay); urban-core coverage only. Matches civic-graph exactly (159). | --- _This document is generated. Diffing successive `data-reality.json` runs is anomaly detection: a row count that silently halves between refreshes is a source problem, and a lineage drift error means a transform record went stale._