LEARN. PRACTICE. GROW.The instant belongs in the population definition
An auditor requests the count and total of positions in a fictional funds application. Two separate queries seem sufficient, but the application continues receiving transactions. If the count is collected before an insert and the total afterward, the pair may never have existed as a coherent state. Record the system, purpose, filters, currency or unit, reference date and extraction mechanism. The audit question may require a day-end state or a particular instant; that is not necessarily when the file was downloaded. A recent file may represent older data, and a consistent database may be incomplete for the business scope.
Reproduce the inconsistency
The lab begins with two rows, A=100 and B=200, in integer cents. A reader queries the count and obtains two. Another connection inserts C=300 and commits the write. The reader then queries the total and obtains 600. The individual answers are correct for their respective instants; presenting them as one snapshot of two rows totaling 600 would be misleading. Compare the pair with actual states: initially 2/300 and then 3/600. Before alleging manipulation, reproduce the collection process and identify transaction boundaries. The anomaly can arise from extract design without malicious data modification.
An observed read transaction
In the second experiment, SQLite uses WAL and separate connections. The reader begins a transaction and performs its first query, establishing its view. The writer transfers 50 from A to B, adds C and commits. Within its transaction, the reader still sees two rows, total 300 and A=100. After ending that transaction, a new read sees three rows, total 600 and A=50. This does not mean the commit failed: writer and reader observe different moments. The behavior was executed in this SQLite configuration. For another technology or isolation level, confirm semantics and demonstrate behavior rather than transferring the conclusion automatically.
Freeze a copy with known scope
The backup API creates an exercise copy containing three rows totaling 600. The source later receives D=400 and reaches four rows totaling 1000; the copy retains the earlier state. For a workpaper, identify the copy, source, covered period and acquisition method. Repeating analysis on a fixed copy helps compare results but does not establish that every relevant transaction was present in the source. An omitted branch or delayed interface can remain absent from a technically consistent copy. If the question requires later data, obtain another identified extract; do not silently update evidence already used in a conclusion.
Propose an executable procedure
When working with APS, agree extraction with the database owner: minimum access, expected impact, timing, criteria and how to stop an overly expensive operation. Requesting consistency does not authorize blocking production without assessment. The lab opens the analysis connection read-only and verifies that a write is rejected; this protects against that mutation through the connection without establishing every system permission. Deliver the file with a parameter manifest and reconciliation controls, and ask another reviewer to repeat the calculation on the same copy. Record discrepancies and limitations. The conclusion should state what the extract supports and what requires additional evidence.
python3 content/labs/cisa-data-lineage/run.py --output /tmp/cisa-lineage-evidence.json
# Separate connections, synthetic SQLite WAL, no real application data.Count 2 and total 600 came from different instants. A read transaction retains 2/300 while another user commits 3/600.
Common pitfalls
Correct queries as a coherent report; invisible commit as failed commit; consistent backup as complete population; locking as a universal solution.
Related topics: Populations and workpapers · Databases and operations · Integrity and provenance
A reproducible conclusion needs coherent data, recorded parameters and justified business scope.
Reference: Isolation in SQLite · CISA outline effective August 1, 2024