LEARN. PRACTICE. GROW.Define the population before the query
A correct query can answer the wrong question. Before writing SQL, define the service, period, event type and criterion to assess. In this exercise, the population is six fictional deployments, D1 through D6, recorded within one UTC window. The initial objective is to relate every deployment to its change record without losing events lacking a match. The dataset was created for learning and was not extracted from production. Execution confirms query behavior on these records; it does not prove completeness of a real inventory or effectiveness of a banking process.
What disappears in a join
INNER JOIN returns only deployments with a matching change. Because C6 is absent, the result contains five deployments and D6 disappears. Calling that result the “complete population” turns a gap into a silent exclusion. Use a join that preserves the defined population and identify missing matches separately. The missing record may reflect incomplete extraction, an incorrect identifier or an unrecorded change; the query does not choose the cause. Seek corroboration and retain the item in the workpaper during investigation. Do not immediately conclude that someone changed production without authorization.
Duplication and unit of analysis
C1 has two approvals. Joining deployments to approvals produces seven rows although there are still six deployments. COUNT(DISTINCT d.id) restores the identifier count in this query, but does not automatically repair every analysis: summing costs or durations after the join may still duplicate values. Define the unit of analysis and aggregate each relationship at the appropriate level before calculating totals. Retain the detail needed for the decision. Arbitrarily selecting the first approval may hide a late approval or an approver who lacked authority.
Reproduce and challenge the result
Record origin, extraction date, filters, query version, transformation, input counts and output. Retain an authorized copy of the dataset used and restrict access to its contents. A reviewer should be able to repeat the calculation and understand the link between criterion and conclusion. Also test cases that challenge the logic: unmatched identifiers, duplicate approvals, null fields and emergency classification. If a known test yields an incorrect conclusion, correct the query and identify affected earlier results. Success on one dataset does not establish correct interpretation of every future format.
Exercise and bounded conclusion
Run this lesson’s code in an empty disposable SQLite database. The first count should be six; the inner join yields five; joining approvals yields seven rows. Identify D6 as unmatched and explain why none of these counts alone represents a noncompliance rate. Write a conclusion containing population, procedure, result and limitation. For example: “Among six synthetic deployments, one lacks a matching change in the supplied dataset; corroboration is needed before classifying the cause.” This wording communicates the gap without claiming fraud, universal noncompliance or statistical validity for a sample.
-- Original synthetic audit evidence. Times are integer UTC minutes within one day.
CREATE TABLE deployments(id TEXT PRIMARY KEY, change_id TEXT, digest TEXT NOT NULL, deployed_minute INTEGER NOT NULL, operator TEXT NOT NULL);
CREATE TABLE changes(id TEXT PRIMARY KEY, class TEXT NOT NULL, approved_digest TEXT);
CREATE TABLE approvals(id TEXT PRIMARY KEY, change_id TEXT NOT NULL, approved_minute INTEGER NOT NULL, approver TEXT NOT NULL);
INSERT INTO deployments VALUES('D1','C1','digest-A',600,'ops-a'),('D2','C2','digest-B',610,'ops-b'),('D3','C3','digest-C',620,'ops-c'),('D4','C4','digest-D',630,'ops-d'),('D5','C5','digest-E',640,'ops-e'),('D6','C6','digest-F',650,'ops-f');
INSERT INTO changes VALUES('C1','standard','digest-A'),('C2','standard','digest-old'),('C3','standard','digest-C'),('C4','emergency',NULL),('C5','standard','digest-E');
INSERT INTO approvals VALUES('A1','C1',590,'review-a'),('A2','C1',595,'review-b'),('A3','C2',600,'review-c'),('A4','C3',625,'review-d'),('A5','C5',630,'ops-e');
CREATE TABLE recovery(id TEXT PRIMARY KEY, elapsed INTEGER, rto INTEGER, data_gap INTEGER, rpo INTEGER, key_available INTEGER, partner_validated INTEGER, business_accepted INTEGER);
INSERT INTO recovery VALUES('R1',40,45,3,5,1,1,1),('R2',20,45,1,5,0,0,0),('R3',44,45,7,5,1,1,1),('R4',35,45,3,5,1,0,0),('R5',30,45,NULL,5,1,1,1);
SELECT COUNT(*) FROM deployments;
SELECT COUNT(*) FROM deployments d JOIN changes c ON c.id=d.change_id;
SELECT d.id FROM deployments d LEFT JOIN changes c ON c.id=d.change_id WHERE c.id IS NULL;
SELECT COUNT(*),COUNT(DISTINCT d.id) FROM deployments d LEFT JOIN approvals a ON a.change_id=d.change_idD6 disappears in the INNER JOIN and C1 duplicates rows when joined to approvals. The auditor reconciles identifiers before calculating metrics.
Common pitfalls
Treating joined rows as deployments; filtering away exceptions; inferring cause from absence; treating DISTINCT as a universal repair.
Related topics: Evidence and sampling · SQL and null data
The conclusion depends on a preserved population and demonstrated transformation. A query does not replace corroboration or justify unsupported extrapolation.
Reference: Assessing Security and Privacy Controls in Information Systems and Organizations · CISA outline effective August 1, 2024