Open-data must publish a flat CSV citation extract for the portal. What transform is appropriate?

Open-data citation portals that offer CSV downloads need a denormalized flat extract. Code tables that are normalized in silver or warehouse models should be joined into readable columns—violation descriptions, disposition labels, agency names—so residents receive one spreadsheet-friendly file. Denormalization for publish does not erase normalized systems of record; it creates a consumer-shaped product for the civic portal. Concatenating code lookups into a delimited column in a citation extract keeps descriptions packed in a single parse-it-yourself string instead of flat readable columns—residents cannot easily filter, sort, or read violation descriptions, disposition labels, and agency names without splitting values, which defeats the spreadsheet-friendly flat CSV the stem asks for. Joining citations to code tables with inner joins matching code values drops any citation with a missing, null, or unmatched code—an open-data extract must preserve every citation row, so denormalization for publish should keep all records and bring in descriptions where available rather than filtering to only matched rows. Pivoting citations to a wide flat format with violation codes as columns creates a sparse, unstable spreadsheet with ever-changing columns for each code instead of one row per citation with readable description columns—new codes would force schema changes and residents could not easily scan dates, locations, and dispositions as the flat citation extract requires. Exam caveat: Normalization is right for curated storage and integrity; denormalized publish extracts are right for flat open-data CSV. Exam items often test whether you pick the consumer shape for portal delivery without abandoning upstream governance. Operational check: Generate a flat citation CSV from joined code tables, open it in a spreadsheet, and confirm description columns are populated without surrogate-only codes. Compare row counts to the governed citation fact table. Verify sensitive fields remain excluded per open-data policy while the file stays fully readable.