IF Commons GitHub

Transforms

0023Active

0023-sql-transforms · owner if · domain data

The project

FR-001checkpassing

transformer MUST make a project from a logical schema, a dialect, a naming and models (each a name, a function from references to its SQL, a materialization, a key and tests) and MUST give errors as values for a name its SQL asks for that is neither a table of the schema nor a model, a cycle among models, two models with one name, an incremental model with no key, and an ephemeral model with a test.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: a project is made in the order of what each model reads, with lineage, and a name that is not there, a cycle, a duplicate, a missing key and a test of an ephemeral model are errors as values

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

Written in: data/sql/extensions/transforms/mod.ts, data/sql/extensions/transforms/model.ts

Tests: passed: suite/transforms.test.ts a project is made in the order of what each model reads, with lineage, and a name that is

Open: issue data 00011: Capabilities of dbt that the library does not have yet

FR-002checkpassing

The order of a project MUST be that of what each model asks for, a model after every table or model its SQL refers to, found by running its SQL function with references that note what was asked; and lineage MUST be the edge from each thing a model reads to the model, in that order.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: a project is made in the order of what each model reads, with lineage, and a name that is not there, a cycle, a duplicate, a missing key and a test of an ephemeral model are errors as values

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

Written in: data/sql/extensions/transforms/model.ts

Tests: passed: suite/transforms.test.ts a project is made in the order of what each model reads, with lineage, and a name that is

FR-003checkpassing

Models MUST be written where the setting says (the schema of the tables where it does not say) and each model's name MUST be written by the naming layer, with the schema's words added where the dialect has no schemas.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: a project is made in the order of what each model reads, with lineage, and a name that is not there, a cycle, a duplicate, a missing key and a test of an ephemeral model are errors as values

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

Written in: data/sql/extensions/transforms/model.ts

Tests: passed: suite/transforms.test.ts a project is made in the order of what each model reads, with lineage, and a name that is

Materializations

FR-004checkpassing

A view MUST be dropped and made again, a table MUST be dropped and made again as the result of its query, an incremental model MUST be made as a table the first time and after that MUST add the rows whose key it does not hold and never change a row it holds, and an ephemeral model MUST be written into the SQL of the models that read it and be made as nothing; a table made again under views that read it MUST be dropped with CASCADE where the dialect has one, and a build of some models MUST also build every model that reads them.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: on three engines a view, a table, an ephemeral and an incremental model are made as they say, a table is made again under the views that read it, and each build is in the ledger

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

Written in: data/sql/extensions/transforms/build.ts

Tests: passed: suite/transforms.test.ts on three engines a view, a table, an ephemeral and an incremental model are made as they s

Tests

FR-005checkpassing

A test MUST be SQL that gives the rows that are wrong: unique (columns that have the same values in more than one row), not-null, accepted-values, relationship (a value with no row in another relation) and custom; its finding MUST give the model, the test, its severity, the number of rows that are wrong and a sample of them.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: a model whose test of severity error finds a row is rolled back, what was published stays, what reads it is blocked, and a warning is reported and does not stop the build; suite/transforms.test.ts: a relationship, a not-null and a test of one's own find the rows that are wrong, by the names of the schema

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

Written in: data/sql/extensions/transforms/model.ts

Tests: passed: suite/transforms.test.ts a model whose test of severity error finds a row is rolled back, what was published stays,; passed: suite/transforms.test.ts a relationship, a not-null and a test of one's own find the rows that are wrong, by the na

FR-006checkpassing

A model MUST be built and its tests run in one transaction; where a test of severity error finds a row, the transaction MUST be rolled back, so that the model is as it was, and the models that read it MUST be reported as blocked and not be built; a finding of severity warn MUST be reported and MUST NOT stop the build.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: a model whose test of severity error finds a row is rolled back, what was published stays, what reads it is blocked, and a warning is reported and does not stop the build

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

Written in: data/sql/extensions/transforms/build.ts

Tests: passed: suite/transforms.test.ts a model whose test of severity error finds a row is rolled back, what was published stays,

The record

FR-007checkpassing

A built model MUST add a row to a ledger table (its name, its sequence, the hash of its definition, its materialization, its row count and the number of warnings), made IF NOT EXISTS, in the transaction of the build; and a report MUST say, for each model, whether it was built, was ephemeral, failed (with the error or the findings) or was blocked, and whether the whole build succeeded.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: on three engines a view, a table, an ephemeral and an incremental model are made as they say, a table is made again under the views that read it, and each build is in the ledger

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

Written in: data/sql/extensions/transforms/build.ts

Tests: passed: suite/transforms.test.ts on three engines a view, a table, an ephemeral and an incremental model are made as they s

Settings

FR-008checkpassing

The schema models are written in, the ledger's table and the number of rows in a sample MUST each have a default and be changeable.

Enforced (check): .github: cd commons/data && bun run verify — suite/transforms.test.ts: models can be written in a schema of their own, and in prefixed names where the dialect has no schemas

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

Written in: data/sql/extensions/transforms/settings.ts

Tests: passed: suite/transforms.test.ts models can be written in a schema of their own, and in prefixed names where the dialect ha

Cited by: Data, sql/extensions/transforms/settings

Out of scope

  • Snapshots (slowly changing history) and seeds: integrations load data.
  • Documentation sites.
  • Macros: a model's SQL function is a function.

Edge cases

  • A model that reads a table the schema does not have: an error value naming it, per FR-001.
  • A table model under a view model: the view is dropped by CASCADE and made again in the same build, per FR-004.
  • Two builds of the same project: the table models are made again, the incremental ones add nothing, per FR-004.

Assumptions

  • Models are best written in a schema of their own (FR-003), so that a reader of the catalog that reads the tables of the schema (convergence) does not find them as drift.
  • A model's columns are named by its SQL; its tests name them as the SQL does.
  • The dialect's DDL is transactional (PostgreSQL, SQLite and DuckDB are).

Open questions

OQ-1open question

Whether an incremental model can also update rows that changed, by a second key of change.

Key entities

  • Model - a name, a query and how its result is kept.
  • Project - the models in the order they build, with their lineage.
  • Finding - the result of a test that found rows that are wrong.

Success criteria

SC-001success criterion

A model that is wrong is not published, and what was published before is still there.

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/extensions/transforms/build, sql/extensions/transforms/model, sql/extensions/transforms/settings, sql/physical/dialect

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