Import everything as text first 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;Six text values. Two of them look empty, and neither is NULL. Open in the playground 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;TG-102 and TG-106 become NULL, and TG-101 loses its outside spaces. Open in the playground 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.
Rule of thumb. Stage every column as text, and turn a blank into NULL only where the dictionary says blank means unknown.
Going deeper (optional, not on the exam) Every command that starts with a dot belongs to the sqlite3 shell, not to SQL: .mode, .import, .headers, .read, .bail, and .timer, which prints how long each statement took and earns its place when a client file has fifty thousand rows. None of them runs in the browser.
When the target table already exists, .import treats the header row as one more row of data. Recent versions of sqlite3 take .import --skip 1 to step over it. Staging into a table that does not exist yet avoids the question, which is one reason the kit does it.
Why the staging table has no key 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.
Rule of thumb. The staging copy is the evidence. Clean on the way out of it, and keep it until the decisions are written down.
Going deeper (optional, not on the exam) The browser copy of Trail Gear is the finished rehearsal, with original fictional data. It cannot take your client's file: .import is a desktop step, and the playground opens a fresh copy of the rehearsal each time.