Synthetic data: every record, name and site is invented

Every record that goes missing between the database and the report, found by its ID.

A program database export moves through four stages before it reaches the reporting system. This run follows 1,200 invented service records through each stage, counts them, and lists every record that was lost, duplicated, cast to a blank or left unmapped, with a likely cause and an owner.

Stage by stage counts

Each stage is compared with the one before it. When a count changes, the Record ID#s that explain the change are listed under it.

    Discrepancy log

    One row per problem. Owners and statuses are example roles. The plain-language view hides the technical columns so program staff can read it as is.

    Cast failures, by record

    The stand-in pipeline turns a value it cannot read into a blank without any error. The check compares the text in staging with the typed column, so a silent blank becomes a named record.

    Source to target key join

    Record ID#s in the export compared with the fact table in the reporting model.

    Category mapping check

    Every category label in the typed data must have a code in the mapping table. A new label is a decision for the program team, not a pipeline fix.

    How the run proves itself

    Planted faults, then a blind check

    A generator writes the export and, separately, a list of the faults it planted. The checker never reads that list. It reads only the file, staging, typed and target tables. A scorer then compares the two.

    A clean control run

    How a cause is assigned

    A bad value that is already in the export is data entry, or an extract problem when the file itself is wrong. A record that is fine in one stage and wrong in the next is an ETL problem. A label with no code is a mapping decision.

    Shaped like a Quickbase export

    Field labels in the header row, a Record ID# column, the built-in Date Created, Date Modified, Record Owner and Last Modified By fields, and records ended by a carriage return. Service Date is a text field in this invented app, so it is not date-checked at entry.

    The checks, in SQL

    What this run does not show