database-load-check

Date: 2026-09-16. Status: implemented.

Decision. Two more generated scripts ship beside the lookups, both produced by python -m simd_ingest.sql_check from the output schemas and the export contract. docs/sql/import_csv.sql stages each shared CSV as text and restores the declared types, putting an empty cell back as a blank or a null according to the contract's rules. docs/sql/check_loaded_digest.sql then verifies the declared columns, types, nullability and order, the natural key, the absence of the characters the digest frames with, the row count and the row digest the build recorded. Expected values come from the manifest, never from the loaded table. The scripts read; they never repair, replace or promote anything, and the CLI never connects to a database.

Reason. A file hash proves the CSV arrived intact, not that the load preserved it, and the two failures that matter are silent: a reader gives every empty field as NULL whatever it meant, and an empty string becomes 1900-01-01 as a datetime. The digest is computed from the accepted table rather than from a re-read of the file, so an import mistake cannot appear on both sides of the comparison. It is compact and probabilistic: each SHA-256 is truncated to eight bytes and the signed prefixes are summed, so collisions and cancelling changes are possible. It detects accidental corruption and is neither an exact cell comparison nor a signature. Structure and key checks sit beside it because a digest is order independent and blind to types.

Evidence. Verified on SQL Server 2022 Developer Edition 16.0.4252.3 in the project's container. Both tables imported by the shipped recipe, 230,103 and 247,773 rows, and all ten checks passed with the digests equal to the manifest's. Eight deliberate corruptions were each caught and undone: a lost leading zero, a blank turned into a null and the reverse, an empty deletion date turned into 1900-01-01, truncated text, a changed band away from any documented example, a deleted row, and a column altered to accept null. The five lookup queries ran there unchanged and gave their documented answers, which retires the "SQL Server syntax intended but not verified" caveat for that version only. Findings recorded in the recipe: CODEPAGE is rejected on Linux, so the encoding is declared as a UTF-8 collation on the staging columns; HASHBYTES over an nvarchar expression hashes UTF-16, so the text is converted to varchar with a binary UTF-8 collation first; and CONCAT_WS drops a NULL argument, so every column is wrapped before framing.