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
- The data is synthetic. Real exports will hold faults this run does not plant.
- There is no live Quickbase connection. The run starts from a CSV file.
- The reporting model is an invented star schema in SQLite, standing in for a production database.
- The faulty pipeline is a stand-in, written with four known bugs so the checks have something to find.
- Causes and owners follow a stated rule. On real data, each one is confirmed with the program team before a fix.