contract

sqlload contract diff     [SELECTOR ...] [OPTIONS]
sqlload contract generate [SELECTOR ...] [OPTIONS] [--write [--overwrite]]

Both read the warehouse’s catalog, and list sources to find their tables; CSV and JSON files are sampled for their columns, as plan does. They load no rows. See Contracts for what declared columns do.

contract diff

Compares each selected table’s declared columns with its live table, in SQLLoad’s portable types, and changes nothing.

$ sqlload contract diff
Contract comparison
  OK    table:app__customers
  DIFF  table:app__orders
        type_mismatch: column 'total' is declared decimal(18,2) but physical type is decimal(12,2)

1 contract difference(s)

A type agrees when the warehouse stores it the same way, so varchar(50) matches DuckDB’s VARCHAR. A wider live type is a difference. SQLLoad’s own _sqlload_* columns never count.

Finding	Meaning
type_mismatch	A declared column’s live type differs.
missing_physical_column	A declared column the table lacks.
relation_missing	The table doesn’t exist yet.
missing_declaration	On contract = "enforced" tables: a live column nothing declares.
files_type_mismatch	With --all-files: files holding a column as another type.
files_missing_column	With --all-files: files without a column.
files_extra_column	With --all-files: a column only older files hold.
source_new_column	With --source: a column the source has that the table lacks.
source_type_mismatch	With --source: a column the source would load as a type the live column doesn’t hold as it is.
source_missing_column	With --source: a live column the source no longer has.
inspection_unavailable	The table couldn’t be read, such as a file or store error.

It exits 0 when every table agrees, and 1 otherwise, including when a source can’t be listed or a selector matches nothing.

Comparing with the source

--source also compares what each table’s source would load with the live table, for the columns no declaration names. It reads the catalog or file footers plan lists, and no rows. In --json, its findings carry a source_type field. Tables under contract = "enforced" add none, since their declaration decides what loads. A Python table’s columns are known only when it runs, so it’s inspection_unavailable under --source, and the command exits 1: declare its columns, leave it out with --exclude, or compare after a load.

contract generate

Proposes the declarations that adopt each live table: every live column nothing declares, with its live type.

$ sqlload contract generate app__orders
Contract comparison
  OK    table:app__orders
        adopt: columns of [sources.app.tables."public.orders"]
          + order_id = "int"
          + customer_id = "int"
          + status = "text"
          + total = "decimal(12,2)"
          + updated_at = "timestamp_tz"

Run contract generate --write to update sqlload.toml.

0 contract difference(s)

• --write writes them into the table’s columns in sqlload.toml, creating its [sources.app.tables."public.orders"] entry when needed, and keeping the rest of the file, its comments and layout as they were. The edited file must still parse, or nothing is written. It’s replaced atomically, with its permissions, and only if nobody changed it while the command ran.
• --overwrite, with --write, also retypes declared columns to their live type and removes declared columns the table lacks.
• Python tables are never edited: the report prints the whole columns={...} to paste into the decorator.
• A table found by an include pattern with no description isn’t written, since its new entry would need one.
• A database or files table declares only columns its source has, spelled as the source spells them. Masked columns stay under mask.
• Adopted columns become pinned types, so a CSV or JSON source converts them while reading.
• With --from, a table whose own target is another kind of warehouse isn’t adopted, since its types could be ones its own warehouse can’t take.

The exit code is that of diff, after the write.

Options

Option	Meaning
SELECTOR ..., -s, --select, --exclude	Tables to compare, as for plan.
--from <TARGET>	Read every table as if its source loaded into this target, in its database and schema. Declared types are read for that warehouse.
--json	Print the report as one JSON document.
--source	Also report where what each table’s source would load differs from the live table, for columns no declaration names. See Comparing with the source.
--all-files	On files targets, also read every file’s footer under the table’s folder, and report files whose columns differ from the newest load.
--write	generate only: write the declarations into sqlload.toml.
--overwrite	generate only, with --write: also retype and remove declared columns.

JSON

$ sqlload contract diff --json
{
  "differences": 1,
  "from_target": null,
  "resources": [
    {
      "findings": [],
      "relation": "raw.app__customers",
      "resource_name": "app__customers",
      "resource_type": "table"
    },
    {
      "findings": [
        {
          "column": "total",
          "declared_type": "decimal(18,2)",
          "kind": "type_mismatch",
          "message": "column 'total' is declared decimal(18,2) but physical type is decimal(12,2)",
          "physical_type": "decimal(12,2)"
        }
      ],
      "relation": "raw.app__orders",
      "resource_name": "app__orders",
      "resource_type": "table"
    }
  ],
  "problems": [],
  "written_paths": []
}

The field names are the same as SQLBuild’s sqb contract diff --json.

Files targets

On a files target, a table’s live columns are the footers of the newest load in its newest partition, as plan uses. --all-files reads every footer under the table’s folder, ignoring max_footer_reads, and names the files behind each finding. It changes nothing.