IF Commons GitHub

sql-loads

0054Active

0054-sql-loads · owner if · domain data

Requirements

FR-001checkpassing

A source MUST give rows of a type in batches, and MUST be made from rows in memory, from CSV text (RFC 4180: quoted cells, doubled quotes, line breaks inside quotes, a byte order mark) with a typed parser for each column of the row (text, integer, decimal, boolean, date, timestamp, a closed list of codes, optional), from lines of JSON read by a function that checks a row, and from a typed select of any database.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: sources give rows in batches: CSV with quoted cells, doubled quotes, line breaks inside quotes, a byte order mark and typed cells; a line that does not read is rejected with its line and why; lines of JSON; rows; and a select

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/mod.ts, data/sql/loads/source.ts

Tests: passed: suite/loads.test.ts sources give rows in batches: CSV with quoted cells, doubled quotes, line breaks inside qu

FR-002checkpassing

A line that cannot be read (a cell that does not parse, the wrong number of cells, a header that lacks a column, a line that is not JSON or not a row) MUST be rejected with its line, why and what it was, and MUST NOT stop the lines that can be read.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: sources give rows in batches: CSV with quoted cells, doubled quotes, line breaks inside quotes, a byte order mark and typed cells; a line that does not read is rejected with its line and why; lines of JSON; rows; and a select

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/source.ts

Tests: passed: suite/loads.test.ts sources give rows in batches: CSV with quoted cells, doubled quotes, line breaks inside qu

FR-003checkpassing

An extract MUST write the rows of a typed select as CSV (a header, quoting where needed, null as an empty cell) or as lines of JSON, and return the text; a CSV that is extracted MUST be read again by the source to the same rows.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: an extract is CSV with a header and quoted cells where they need it, or lines of JSON, and a CSV that is extracted is read again to the same rows

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/extract.ts

Tests: passed: suite/loads.test.ts an extract is CSV with a header and quoted cells where they need it, or lines of JSON, and

FR-004checkpassing

A load MUST put a source into a typed table in batches in one transaction, in a mode: append (a key that is there fails the load), skip-existing, upsert or replace; a load that the database refuses MUST leave the table as it was and be a report with status failed, not a throw; each run MUST be a row of the ledger of loads (its name, status, counts, start and end), which the load makes if it is not there.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: a load in each mode: append fails on a key that is there and leaves the table as it was, skip-existing keeps the rows that are there, upsert changes them, replace empties the table first; batches, one transaction, and a row of the ledger for each run

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/load.ts, data/sql/loads/mod.ts

Tests: passed: suite/loads.test.ts a load in each mode: append fails on a key that is there and leaves the table as it was, s

Open: issue data 00010: Data loads, extracts and ETL/ELT: connectors and native bulk paths are left

FR-005checkpassing

A load with a gate MUST go through a staging table that has the target's columns and none of its constraints; the SQL-DQ tests of the gate MUST run on the staging table; the rows MUST be merged into the target only where no test failed or erred; a load that does not pass MUST leave the target as it was, report the outcomes of the tests with status gated, and leave no staging table unless asked to keep it.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: ELT: the rows go to a staging table, SQL-DQ tests run on it, and only a load that passes is merged; one that does not leaves the target as it was, reports the failures, and drops the staging table unless it is asked to keep it

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/load.ts

Tests: passed: suite/loads.test.ts ELT: the rows go to a staging table, SQL-DQ tests run on it, and only a load that passes i

FR-006checkpassing

A load MUST report the lines it rejected, MUST be refused with status rejected and take nothing where more than the share that is allowed was rejected, and otherwise MUST take the rows that were read.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: the lines that are rejected are reported by line, a load is refused and takes nothing where more than the share that is allowed was rejected, and a load with some rejected lines takes the rest

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/load.ts

Tests: passed: suite/loads.test.ts the lines that are rejected are reported by line, a load is refused and takes nothing wher

FR-007checkpassing

A load MUST keep the greatest value of a named column in the rows it loaded as its high-water mark in the ledger, watermarkOf MUST give the mark of the last run that loaded, and a run that did not load MUST NOT move it.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: a load keeps the greatest value of a column as its high-water mark, the next run reads from it, and a load that did not load does not move it

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/load.ts

Tests: passed: suite/loads.test.ts a load keeps the greatest value of a column as its high-water mark, the next run reads fro

FR-008checkpassing

A select of one database MUST be loadable into a table of another, through a gate, on every pair of SQLite, DuckDB and PostgreSQL, with the rows, the counts and the ledger of the database loaded into.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: a select of one database is loaded into a table of another, through a gate, on every pair of engines: rows are copied, counted and recorded in the target's ledger

Proved by: data/suite/loads.test.ts

Written in: data/sql/loads/load.ts

Tests: passed: suite/loads.test.ts a select of one database is loaded into a table of another, through a gate, on every pair

FR-009checkpassing

A load MUST be able to give the run's id to every row it writes (loadIdColumn), so that what a load wrote is found by its id; and with divertConflicts, in the mode append and without a gate, a row whose key is already there MUST NOT fail the load: where its content is the same it MUST be absorbed and counted, where it differs it MUST be written to the table <table>__conflict (the run, when, the key and the existing and incoming rows as JSON) and the original MUST be left as it is; the report MUST say how many rows were absorbed and diverted.

Enforced (check): .github: cd commons/data && bun run verify — suite/loads.test.ts: a load can give the run's id to every row it writes, so that what a load wrote is found by it, and with divertConflicts a key that is there is not a failure: the same content is absorbed and counted, other content goes to the conflict table and the original is left as it was

Proved by: data/suite/loads.test.ts

Tests: passed: suite/loads.test.ts a load can give the run's id to every row it writes, so that what a load wrote is found by

Out of scope

  • Connectors that sit on Source and Load and are later parts: dialect-native bulk load and extract (PostgreSQL COPY, DuckDB read_csv, read_parquet and COPY ... TO, SQLite .import), Parquet and Arrow, HTTP and API sources with paging, object stores, queues, change data capture and LISTEN/NOTIFY.
  • Orchestration: schedules, dependencies between loads, retries and alerts; a load is a function that a scheduler calls, and the ledger is what it reads.
  • dbt-style seeds, snapshots and source freshness, which are built on these (issue 11).
  • Reading and writing files: the text is given and returned; the caller owns the file, the bucket and the response.

Edge cases

  • A table with no primary key cannot be loaded in skip-existing or upsert unless the keys are given, per FR-004.
  • A staging table is made with CREATE TABLE ... AS SELECT ... WHERE 1 = 0, so it has none of the target's constraints, defaults or indexes; the gate's tests are what hold the rows to them, per FR-005.
  • A mode of replace through a gate empties the target only after the gate has passed, per FR-005.
  • A batch that fails after other batches were written takes none of them, per FR-004.

Assumptions

  • The driver takes text and every value is a literal (0053-sql-typed), so a load writes batches of INSERT statements; the native bulk paths are the connectors above.
  • The ledger is in the database that is loaded into, in the schema the connection is set to.

Open questions

OQ-1open question

Whether the ledger of loads becomes a model of its own (with the issues model's pattern), and whether it records the SQL-DQ outcomes of a gate.

OQ-2open question

Whether a connector is a contribution point of the engine (sql.loads.sources) or a plain value, as it is here.

Key entities

  • Source - rows of a type in batches, and the lines that were rejected on the way.
  • Load - a source into a typed table: a mode, a gate and a high-water mark.
  • Report - what a run did: status, counts, the rejected lines and the outcomes of the gate.
  • Ledger - the table load_run, one row for each run.

Success criteria

SC-001success criterion

A CSV file is loaded into a table with a gate of data tests, on each engine, and what fails the gate is reported and not loaded; the same table is extracted to CSV that reads back to the same rows.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/loads/extract, sql/loads/load, sql/loads/source

Titles, headings, exports, tables, columns and requirements. Esc closes.