Data lineage for agency reporting: tracing a number to its source

Reference article · 5 min read

When a figure in a public report is questioned, the agency usually has hours, not weeks, to explain where it came from. The analyst who built the report may have moved on, the query may live in a personal folder, the source system may have been patched, and the spreadsheet may contain a filter nobody remembers applying. Data lineage is the written record that closes that gap: a description of the path a value took from the field where it was first recorded to the cell where it was published.

Lineage is often described as a feature of a data catalog or a pipeline tool, and when such a tool is in place it does real work. Most government reporting, though, still moves through extracts, spreadsheets, and hand written queries, where no tool observes the steps. In those environments lineage is something staff record deliberately. The good news is that the record does not need to be elaborate. It needs to be specific, stored with the report, and written at the time the work is done.

What a lineage record has to answer

A usable record answers six questions about every published figure. If a colleague can answer all six without calling anyone, the lineage is adequate.

• Origin. Which system, which table or object, and which field held the raw value, including the system of record when several systems carry the same data.
• Population. Which records were counted and which were excluded, stated as rules rather than as a description. "Excludes cases with a status of duplicate or test" is a rule. "Excludes bad records" is not.
• Transformation. Every calculation, recoding, unit conversion, rounding, and date bucketing applied between the source value and the published one.
• Combination. Which datasets were joined, on which keys, and what happened to records that did not match.
• Timing. When the extract ran, what date range it covered, and what the cutoff was for records still arriving.
• Accountability. Who produced the figure, who reviewed it, and who owns the source system.

Where numbers change shape

Most disputed figures break at one of a small number of points, and knowing the points tells you what to document.

• Capture. A field is optional, coded differently by different offices, or backfilled during a conversion. Document known data entry practices, not just the schema.
• Extraction. A report pulls from a replica that lags, or from a view whose definition was changed by someone else. Record the exact connection, object, and query text, not a summary of it.
• Transformation. Status codes are grouped into categories, dates are shifted to fiscal periods, or records are deduplicated. These are policy decisions as much as technical ones, so record the reason alongside the logic.
• Joins. Matching people, parcels, or vehicles across systems drops or multiplies rows. Record the key, the matching method, and what was done with unmatched and duplicate matches.
• Presentation. Rounding, suppression of small counts for privacy, and chart scaling all alter what the reader sees. Note them where the number appears.

Metadata to capture when no tool produces it

For each recurring report, keep a short lineage page covering: source system name and environment; object and field names as they appear in the system, not friendly labels; the extraction method and the literal query or export settings; the run timestamp and the data cutoff; filters and exclusions with their stated reason; join keys and unmatched record handling; definitions of every derived field; rounding and suppression rules; the file path or repository location of the query; and the names of the preparer, reviewer, and data owner.

Two habits make this far more useful. Version the page whenever the logic changes, with a dated note saying what changed and why. And retain the extract itself, or a hash of it, so a published figure can be tied to a specific set of rows rather than to a query that would now return something different.

Reproducing a figure months later

Live queries rarely reproduce past numbers, because underlying records get corrected, reopened, and reclassified. Decide in advance whether each recurring report is a snapshot or a current view, and say so in the report itself. For snapshot reporting, store a frozen copy of the underlying data. For current views, publish a restatement rule explaining that prior period figures may change as records are updated. Either approach defends well in public. What does not defend well is silence about which one is in use.

What to ask vendors and put in contracts

Lineage obligations are easier to obtain during procurement than afterward.

• Ask how the system documents field level definitions, whether report and query definitions can be exported in readable form, and whether change history for report logic is retained and visible to agency staff.
• Ask whether the agency can reach the underlying data directly for audit purposes, including read access to a replica, rather than only through vendor built reports.
• Require a data dictionary as a deliverable, kept current through the term, covering fields, codes, and calculated values.
• Require that the agency owns its data and its report definitions, and that both are exportable in a non proprietary format at any time, including at contract end.
• Require notice before changes to standard report logic or to field definitions that feed published figures.

How to start

Pick the three figures most likely to be questioned in public. For each, sit with the person who produces it and write the lineage page while they walk through the steps. Save it beside the report, not in a personal drive. Then require a lineage page for any new recurring report before its first publication. Agencies that do this find the effort is concentrated in the first pass and small thereafter.

HQT works with state and local agencies on data analytics and reporting systems, including documenting how reported figures are derived.

Frequently asked questions