Load a file and prove the load

Real data arrives as files, and a file has no rules. Nothing in a CSV says a rate must be a number or a gear type must exist. The rules live in your CREATE TABLE statements, and the work of a load is getting the file's rows past those rules honestly: the good rows in, the bad rows out, and a sentence for every row you left out.

The pattern this course uses has four steps. Stage: copy the file into a table exactly as it is, every column as text. Check: test the text against the data dictionary before converting anything. Load: INSERT ... SELECT the rows that pass, with the cleaning rules written into the SELECT. Prove: count what came in and what stayed out, and keep the scripts so the database can be rebuilt from nothing.

Trail Gear's rehearsal keeps all four steps on view. stg_items is the staged file, items is the real table, gear_types is its parent, and load_audit holds one row per source line with the decision and the reason.

SELECT source_row, serial, decision, reason
FROM load_audit
ORDER BY source_row;

In the sqlite3 shell, two lines stage the file: .mode csv, then .import items.csv stg_items. Neither is SQL. They start with a dot, take no semicolon, and work only in the sqlite3 tool or in a script it reads; the browser playground runs SQL and nothing else. When stg_items does not exist yet, .import creates it from the file's header row: four columns named serial, type_id, daily_rate and note, every one of them TEXT. The gear type is the text '1'. The rate is the text '8', or the text 'unknown'.

A blank cell arrives as an empty string, not as NULL. TG-106's note is '' and TG-102's note is three spaces, and typeof(note) says text for all six rows. So note IS NULL finds nothing, and a NOT NULL constraint would accept both. An empty string is a value that looks like a missing one.

SELECT serial, note, typeof(note) AS storage
FROM stg_items
ORDER BY rowid;

The fix is a rule, written as SQL on the way out of staging: NULLIF(TRIM(note), ''). TRIM removes the spaces at either end. NULLIF compares what is left with the empty string and returns NULL when they match, the text otherwise. The order matters. NULLIF(note, '') catches TG-106 and leaves TG-102's three spaces in place, a blank that is still a word of spaces.

SELECT serial, NULLIF(TRIM(note), '') AS clean_note
FROM stg_items
ORDER BY rowid;

Use the rule only where the data dictionary says blank means unknown. A blank note means there is no note, so NULL is honest. A blank serial is not an unknown serial; it is a row that cannot be loaded, and turning it into NULL hides the problem in the one column that identifies the item. Required columns get a check and, when they fail it, an exclusion with a reason.

stg_items is evidence, not part of the application. It is the file, row for row, with nothing cleaned and nothing typed, and it stays in the database until every decision about it is written down. If a teammate asks why TG-105 is missing from items, the answer starts with the row as it arrived.

It has no primary key on purpose. A key is a rule, and the staging table's job is to accept whatever the file holds, duplicates and blanks included, so the checks can find them. Each staged row is traced by its rowid, which follows the file's order: TG-101 is row 1, TG-106 is row 6, and load_audit's source_row points at the same numbers.

Your client's application tables are the opposite. Every one of them gets the keys and constraints the ERD and the dictionary give it. Some load scripts drop the staging tables at the end; the kit keeps them so a reviewer can see every decision. Either is defensible when the report says which.

TG-105's rate arrived as the word unknown. CAST(daily_rate AS INTEGER) turns it into 0, with no error and no warning. CAST reads digits from the start of the text and stops at the first character that does not fit; when there are no digits at all, the answer is zero.

SELECT serial, daily_rate, CAST(daily_rate AS INTEGER) AS converted_rate
FROM stg_items
ORDER BY rowid;

items has a CHECK on daily_rate: an integer, zero or more. That CHECK sees the converted 0 and accepts it, because a CHECK can only see the value it is handed, never the text in the file. An item that rents for nothing a day enters the database, and every report built on it treats the gear as free. TG-104's -3 is refused, but only because this rule happens to forbid negatives.

So test the text before you convert it. The rehearsal's rule: after TRIM, the rate is not empty and every character is a digit from 0 to 9. TRIM(daily_rate) = '' OR TRIM(daily_rate) GLOB '*[^0-9]*' finds the rows that break it. GLOB matches a pattern: * stands for any run of characters, and [^0-9] for one character that is not a digit. TG-104 breaks the rule with its minus sign, TG-105 with every letter.

SELECT serial, daily_rate
FROM stg_items
WHERE TRIM(daily_rate) = ''
   OR TRIM(daily_rate) GLOB '*[^0-9]*'
ORDER BY rowid;

The rule is this rehearsal's, not a universal one. It refuses decimals, which suits whole-dollar daily rates and would be wrong for a wage with cents. Your client's dictionary says what a valid value looks like, and your rule follows it. Whatever the rule, keep the rejected row and its reason: load_audit gives both TG-104 and TG-105 the reason 'Rate is not an unsigned whole number'.

TG-103 says its gear type is 99. gear_types has two rows, 1 for Trail gear and 2 for Water gear. A row whose foreign key points at nothing is an orphan, and an orphan in a file is easy to lose before anyone sees it.

Look for it with an INNER JOIN and it disappears. The join keeps only the staged rows that found a parent, so WHERE g.id IS NULL is asked of five rows that each have one. The query returns nothing, and nothing looks exactly like no orphans.

A LEFT JOIN from staging to the parent keeps all six staged rows. TG-103 comes back with NULL in every gear_types column, and the test is on the parent's key: WHERE g.id IS NULL. The ON clause converts the staged text, g.id = CAST(s.type_id AS INTEGER), and that CAST is safe only because every type_id in this file was checked first and is all digits.

SELECT s.serial, s.type_id, g.name AS gear_type
FROM stg_items s
LEFT JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER)
ORDER BY s.rowid;

Finding TG-103 is SQL. Deciding what happens to it is not. There are three honest answers: exclude the row and write down why; correct it, when the client confirms what the type should have been and sends a corrected file; or let it in with no type, if the business allows an item without one and the column is declared to allow NULL. Each needs a reason the client would sign.

The dishonest fourth answer is to invent a gear type 99, call it Unknown, and watch the error go away. The foreign key is satisfied, and every report now has a category nobody defined. The rehearsal excludes TG-103 with the reason 'Unknown gear type', and the reconciliation lists it by name.

The audit says three rows were excluded. A teammate checks it the quick way: six source rows minus three loaded is three. Same answer. Now suppose the load had silently lost TG-106. Loaded drops to two, source minus loaded says four were excluded, and the arithmetic still balances, because it was built to. A count worked out from the other two can never disagree with them.

So count each population from its own table. Source rows come from stg_items, loaded rows from items, excluded rows from the decisions in load_audit. When source equals loaded plus excluded with three independent counts, the equation means something. When it fails, the gap is a row you can go and find.

SELECT (SELECT COUNT(*) FROM stg_items) AS source_rows,
       (SELECT COUNT(*) FROM items) AS loaded_rows,
       (SELECT COUNT(*) FROM load_audit WHERE decision = 'Excluded') AS excluded_rows;

Watch the excluded count itself. COUNT(CASE WHEN decision = 'Excluded' THEN 1 ELSE 0 END) says six, because COUNT counts every value that is not NULL, and 0 is a value. Count the rows with a WHERE, or let the CASE fall through to NULL.

The equation fits this kind of load: a first load into an empty table, one source row to one target row. An append to a table that already has rows, a file with duplicates you merge, a file that becomes two tables: each needs its own mapping, written down before the counts mean anything.

Northshore Coffee copies its 204 orders into a new table, and the copy reports 204 rows. One order came in twice and another never arrived. The count is perfect, the sales total is twelve dollars short, and the count will never tell you.

A reconciliation needs evidence about the rows as well as the totals. COUNT(DISTINCT id) beside COUNT(*) shows a duplicate. A SUM of the amount shows money that moved. And then the identities themselves: the three serials load_audit marks Loaded should be exactly the three serials in items, carrying the cleaned values you meant.

SELECT a.source_row, a.serial, i.id AS loaded_id
FROM load_audit a
LEFT JOIN items i ON i.serial = a.serial
WHERE a.decision = 'Loaded'
ORDER BY a.source_row;

Write a baseline down before the load and compare after it: the row count, the count of distinct keys, a total, the first and last key. Two numbers that match prove that two numbers match. Rows that match prove the load.

The desktop kit holds five files: schema.sql, load.sql, verify.sql, items.csv and a README. In the extracted folder, open sqlite3 with a database file that does not exist yet, then run .read schema.sql, .read load.sql and .read verify.sql, and .quit. Then do it again into a second new file, rehearsal-again.db, without deleting the first.

-- In a terminal, inside the extracted kit folder
sqlite3 rehearsal.db
.read schema.sql
.read load.sql
.read verify.sql
.quit

Two databases built from the same scripts and the same file should agree row for row. That is what reproducible means on the project checklist: anyone who runs your scripts gets your database. Anything you typed only into the shell is missing from the second build, which is how the rebuild catches it.

.bail on, near the top of each script, stops the script at the first error. It is not a rollback: whatever ran before the error stays done, and the file is half built. Start again in a new file rather than rerunning into it. The scripts expect an empty database on purpose; run load.sql into one that is already loaded and its first insert is refused on the duplicate gear type, so nothing is appended twice.

verify.sql ends with a sanity join: each gear type with its count of items and the sum of their daily rates. Trail gear has two items and 18, Water gear one item and 12. When both builds print 18 and 12, that is reassuring, and not much more.

SELECT g.name AS gear_type, COUNT(i.id) AS items, SUM(i.daily_rate) AS rate_sum
FROM gear_types g
LEFT JOIN items i ON i.type_id = g.id
GROUP BY g.id, g.name
ORDER BY g.id;

A teammate's version of the join pairs the tables ON 1 = 1. Every item meets every type, each type reports 30, and the total across both types doubles from 30 to 60. Run it in both builds and they agree perfectly on the wrong numbers. A sanity query that agrees with itself proves nothing; the ON must pair items.type_id with gear_types.id, the key and not a coincidence.

And 18 is a sum of listed daily rates, not revenue. There are no rentals in this database, so no query on it can say what Trail Gear earned. Say what a number is before you compare it.

Compare the strongest evidence first: the ordered rows of items, column by column; the count in every table; the notes that are NULL (two) against the notes that are empty strings (none); PRAGMA foreign_key_check, which should return no rows, and PRAGMA integrity_check, which should say ok; and only then the totals. Then, on a disposable copy, try the inserts that must fail, a duplicate serial, a negative rate and a type with no parent, and paste the exact errors.