Generating and verifying¶
murakumo generate # write generated/: a dbt project and DDL
murakumo generate --warehouse snowflake # for another warehouse than the manifest's
murakumo generate --check # CI: fail if generated/ is out of date
murakumo migrate # write the next migration for tables that keep data
murakumo migrate --check # CI: fail if the model changed without a migration
murakumo verify # build it on DuckDB and run every test, twice
murakumo build # build it in the project's warehouse, and keep the tables
murakumo build --schedule nightly # only what one schedule refreshes
murakumo export sql # the project as plain SQL scripts, without dbt
murakumo docs # open the pipeline docs
murakumo runs # the latest runs, loads and failed tests, from the audit tables
murakumo backfill TABLE --from D --to D # build a cumulative table day by day
murakumo run --engine polars # the same pipeline as Polars (or PySpark) jobs
generate refuses to run while murakumo validate reports errors. Everything it writes goes under
generated/; it never touches model/, custom/ or anything else. It keeps a list of the files
it wrote in generated/.murakumo-files.json, so when an entity is removed, its files are removed
too, while files you added yourself are left alone. If you edited a generated file by hand, the
next run overwrites it and tells you; put hand-written code in custom/ instead.
Output is deterministic: the same model always gives byte-identical files, so --check in CI and
code review of generated/ both work.
What is generated¶
generated/
dbt/
dbt_project.yml, profiles.yml profiles read credentials from environment variables
models/staging/<source>/ one view per raw table, plus sources.yml and tests
models/intermediate/<domain>/ the versions behind SCD2 dimensions
models/marts/<domain>/ dimensions and facts, with contracts and tests
snapshots/_snapshots.yml SCD2 history
macros/murakumo/ the record hash, schema names, extra tests, the run log and time travel
selectors.yml one selector per schedule
ddl/<warehouse>/
raw.sql landing schemas and tables for ingestion to write into
marts.sql reference DDL for the modelled tables, with keys
orchestration/<orchestrator>/ a pipeline per schedule (fetch, load, build), and how to deploy them
ingestion/ for sources with a connection: the extract code, and the Lambda,
Glue, Snowpipe or Auto Loader setup the stack chose
docs/
index.html, <table>.md pipeline docs: tables, columns, lineage, schedules, sources
stack.md the platform: its tools, sources, how to set it up by hand, and why
sql/<warehouse>/ the project as plain SQL, with `plain_sql: true`
python/<engine>/ the pipeline as PySpark or Polars jobs, with `python_engine`
contracts/<table>.odcs.yaml each table's data contract (ODCS v3.0.2)
quality/great_expectations/ an expectation suite per table, and run.py (quality.framework)
quality/soda/ SodaCL checks per table, the warehouse, and run.py (quality.framework)
Schedules, pipelines, plain SQL and CI are described in orchestration.md; the stack, connections and ingestion in stack.md.
Staging¶
Every raw entity becomes stg_<source>__<entity> (from conventions.naming.staging), a view
that:
- renames every column to snake_case:
PULocationIDbecomespu_location_id; - casts every column to the physical type of its logical type on the warehouse (with
modelling.raw_columns: text, a cast that gives null instead of failing: see below); - passes through the landing columns
_ingested_atand_batch_id, which ingestion writes; - adds
_source_systemand_record_hash(a hash of every source column, to detect changes).
Tests come from the model: unique and not_null for a single-column primary key, one
murakumo_unique_combination for a composite key, not_null for non-nullable columns,
accepted_values, relationships for foreign keys, and every rule in tests:.
A raw table with rules whose action is quarantine gets a second model beside its staging
model, <staging model>__quarantine (a table). Both read the raw table and check those rules with
the same CASE; staging keeps the rows that pass, the quarantine table the rows that fail, with
_failed_rule. Everything built from staging only sees rows that passed. With
quality.reconciliation, the quarantine table (or staging, without one) has a test that the two
add up to the raw table. See operating.md.
Modelled layers¶
An entity in intermediate, marts or semantic is built from its mappings:
- the first entity a mapping reads drives the query; other entities are left-joined through the model's relationships, which must connect them directly and unambiguously (MK501 otherwise);
entity.columnsources resolve to the staged column names, and expressions are rewritten to match. Expressions are written in DuckDB SQL and translated to each warehouse's dialect, somd5(concat_ws('|', a, b))becomes the null-safe Snowflake form,TO_HEX(MD5(...))on BigQuery, and so on;- every column is cast to its physical type, and
_updated_atis added, with_loaded_at: when the row's source row was loaded (the raw row's_ingested_at, carried through); load: incrementalbecomes an incremental model keyed on the grain (or primary key) that reads what was loaded since its last run (_ingested_atpast its latest_loaded_at). Rows that arrive late, with old dates, are picked up; rows read again replace themselves by key.lookback_hours: 6looks that far back each time, for stragglers.incremental_by: watermarkreads rows newer by thewatermarkcolumn instead, as before 0.7;history: scd2becomes a view of the current rows, a dbt snapshot that keeps every version, and the dimension on top withvalid_from,valid_toandis_current;- tables in
martsget an enforced dbt contract (contracts.enforce_onin the manifest), so a wrong type fails the build instead of reaching consumers; partition_byandcluster_bybecome warehouse config where the warehouse supports them.
Columns with no mapping are generated as typed nulls, with warning MK503.
Every column of a modelled entity carries its lineage in the dbt YAML, as meta.lineage: the
entity.columns it is made from, so tools that read dbt's manifest (catalogues, docs sites) can
show it too.
Domains¶
Each domain in project.domains that has models becomes a dbt group in models/_groups.yml,
owned by the domain's owner, and each model of an entity in a domain gets group: and
meta.domain in its config. Models keep the default protected access, so models in other
domains can still use them; groups say who owns what. Files stay in their layer folders, so
setting a domain moves nothing.
Warehouses¶
DuckDB, Snowflake, Databricks, BigQuery, PostgreSQL, Redshift, Microsoft Fabric and Synapse each
have their own physical types, dialect and profiles.yml. The logical type decides the physical
one: money is NUMBER(38,4) on Snowflake and DECIMAL(38,4) on Databricks; timestamp is
TIMESTAMP_NTZ on Snowflake and DATETIME on BigQuery. Override a single column with
physical_types in the model.
Verify¶
murakumo verify proves the generated project works, without touching generated/. It runs
on DuckDB, locally and with no warehouse, or on Snowflake with --warehouse snowflake (see
snowflake.md):
- it generates the project for that warehouse in a temporary folder;
- creates the raw tables from the generated DDL;
- loads them with synthetic rows that follow the model: unique keys (composite too), foreign keys that point at real parent rows, accepted values, NOT NULL, and range rules;
- runs
dbt build: every model, snapshot and test; - runs it again, so incremental models and snapshots take their update paths too.
murakumo verify --rows 1000
murakumo verify --data yellow_tripdata=local-data/tlc/yellow_tripdata_2024-01.parquet \
--data taxi_zone_lookup=local-data/tlc/taxi_zone_lookup.csv
murakumo verify --keep build/ # keep the dbt project and verify.duckdb to look around
With --data, a raw table is loaded from a real Parquet, CSV or JSON file (local or https://)
instead, matching columns by name, or on Snowflake from a table (snowflake:DB.SCHEMA.TABLE);
--limit caps the rows. Real data is where tests start to find real problems.
Verify needs dbt and DuckDB: pip install "murakumo[verify]", plus dbt-snowflake for Snowflake.
On the other warehouses, murakumo verify --warehouse postgres (or redshift, databricks,
bigquery, fabric, synapse) does the same in schemas with a unique prefix, dropped afterwards.
Install the warehouse's extra (pip install "murakumo[postgres]") and set the variables its dbt
profile reads: DB_HOST, DB_USER, DB_PASSWORD (and DB_PORT, DB_NAME) for PostgreSQL and
Redshift; DATABRICKS_HOST, DATABRICKS_HTTP_PATH, DATABRICKS_TOKEN; BIGQUERY_PROJECT;
FABRIC_SERVER or SYNAPSE_SERVER with AZURE_TENANT_ID, AZURE_CLIENT_ID, AZURE_CLIENT_SECRET.
PostgreSQL is run in CI. The others are written to the same interface and not yet run against a
real account.
Build¶
verify leaves nothing behind. murakumo build builds the project where it belongs, in the
warehouse from murakumo.yaml (a DuckDB file in the project folder, or a Snowflake database), and
keeps every table:
- creates any raw landing table that is missing, from the generated DDL (existing tables and their rows are left as they are);
- loads raw tables only when asked:
--data TABLE=PATHfrom a file, folder, glob or table, or--sample Nmade-up rows into tables that are still empty; - runs
dbt buildfor the environment (--env; by default your own, in your schema: see working as a team).
murakumo build --sample 200 # try it out with made-up rows
murakumo build --data yellow_tripdata=data/yellow_tripdata_2024-01.parquet
murakumo build --data yellow_tripdata=landing/trips/ # every new file in a folder
murakumo build --data 'yellow_tripdata=landing/trips_*.csv' # or matching a pattern
murakumo build --env prod # the prod environment: its database and schemas
murakumo build # rebuild the models on whatever has landed
Loading only new data¶
A folder or pattern is read file by file, and each file goes in once: every file loaded is recorded
in audit.loaded_files with its size and a SHA-256 of its content, and a file whose content was
loaded before is skipped, even under another name. Run the same build --data on a schedule
(murakumo scheduler --data ...) and each run takes what has landed since.
How the new rows meet the old is the raw table's load in the model (New rows in the canvas's
inspector):
load |
What a load does |
|---|---|
append |
adds the new files' rows |
incremental |
the new rows replace rows with the same primary key; within a load, the latest of each key (by the watermark column, else load time) is kept |
full |
replaces the table with every file given, once any of them changed |
Raw columns as text¶
With text, raw tables keep every column as text, exactly as it arrived, so a value that does not
fit its type ("N/A" in a number column) never stops a load. Staging converts each column with the
warehouse's safe cast (TRY_CAST, SAFE_CAST): a value that does not convert becomes null, and a
warning test on the raw column (murakumo_converts_to) counts it, so the run log says how many
values of which column did not convert. Settings → Project in the canvas has the same switch.
Changing it changes the landing tables' types, so it needs a migration.
It builds the model as it is now, and says so if generated/ is out of date or a change to a
table that keeps data has no migration yet: apply migrations to the warehouse first, since build
never alters an existing table. It runs on DuckDB and Snowflake; for other warehouses, run
dbt build in generated/dbt. In production an orchestrator does the same on a schedule.
Audit tables¶
Every run records itself in an audit schema beside the tables it builds (under a personal or
pull-request environment's prefix, in that environment's own audit schema):
| Table | One row per | Records |
|---|---|---|
audit.run_log |
run | when, which engine, environment and schedule, who, status, models and tests passed and failed |
audit.model_runs |
model or snapshot | status, seconds, rows in the table afterwards (views are not counted) |
audit.dq_results |
test | passed, warned or failed, and how many rows failed it |
audit.row_counts |
raw table loaded | rows ingested, rows rejected and why |
audit.daily_summary |
day (a view) | runs, rows ingested and rejected, models and tests that failed |
The dbt project writes the first three at the end of every run (on-run-end), wherever it runs:
murakumo build, an orchestrator, dbt by hand. murakumo build writes row_counts as it loads:
rows of a CSV file that do not fit the model's column types are set aside and counted as rejected,
with the reason (Could not convert string "nope" to INTEGER), instead of failing the load.
murakumo runs shows the last days, runs, loads and failed tests. Set quality.run_log: false to
turn the run log off.
Python pipelines¶
With python_engine: pyspark or polars under generation, generate also writes the pipeline as
Python to generated/python/<engine>/: a module per model, in the order they run, a runtime, and
run.py. Each model's SQL is its dbt SQL rendered once more with the runtime's own source(),
ref() and this, in Spark SQL for PySpark and DuckDB SQL for Polars, so the jobs build the same
tables as dbt:
- PySpark runs everything as Spark SQL over temporary views and writes Parquet tables, or Delta
with
--format delta; - Polars holds the data as Polars data frames and runs each model's SQL with DuckDB over them, writing Parquet. The SQL is not rewritten into Polars expressions by hand.
Both keep incremental, cumulative and SCD2 snapshot tables up to date between runs (merging new
rows by key, closing changed versions), run every test, and record each run in <output>/audit/
as the dbt project does in the warehouse.
murakumo run --engine polars --sample 200 # on made-up rows
murakumo run --engine pyspark # raw tables from the project's DuckDB file
murakumo run --engine pyspark --data orders=orders.parquet --schedule nightly --run-date 2024-01-31
murakumo run writes the pipeline to a temporary folder and runs it in its own environment (from
its requirements.txt, with uv), so Murakumo itself does not need Spark. PySpark 3.5 needs Java 17
or 21: the requirements include jdk4py, which brings one. On the same data, both engines build the
same tables as dbt, row for row, including SCD2 history after a change: Murakumo's tests check
Polars against dbt on every change, and PySpark was checked the same way by hand.
Migrations¶
dbt rebuilds views and tables on every run, so a model change reaches them by itself. Three kinds of table keep data between runs, so their schema has to change in place:
- raw landing tables, which ingestion writes into;
- incremental models, which dbt only adds rows to;
- the snapshots behind SCD2 dimensions, which hold every version.
murakumo migrate compares those tables in the model with the state recorded by the last
migration, and writes the difference as the next numbered SQL file:
migrations/duckdb/
schema.json the tables as of the last migration, by id
0001_initial.sql the first run: schemas and landing tables
0002_rename_lineitem_l_discount_and_3_more.sql
-- lineitem: rename l_discount to discount
ALTER TABLE raw_tpch_erp.lineitem RENAME COLUMN l_discount TO discount;
-- lineitem: drop l_comment
-- l_comment was removed from the model. Uncomment to drop it and its data:
-- ALTER TABLE raw_tpch_erp.lineitem DROP COLUMN l_comment;
- Tables and columns are matched by id, so a rename is
RENAME, which keeps the data, not a dropped column and an empty new one. This is why renames should go throughmurakumo renameor the canvas, which keep ids, rather than editing a name and its id by hand. - New tables, new columns, renames and type changes are written as statements. Anything that loses data (a dropped column or table) is written commented out for a person to decide.
- Incremental models and snapshots are built by dbt, so a new environment does not have them
until dbt's first run, which creates them in their new shape. Their statements use
ALTER TABLE IF EXISTS(DuckDB, Snowflake, Postgres, BigQuery), so migrations can always run before dbt; on other warehouses a comment says to skip them where dbt has not built the table. - For incremental models and snapshots, dbt adds new columns itself
(
on_schema_change: append_new_columns), so those additions are left as comments too. - Statements are written for the manifest's warehouse (
--warehousefor another):RENAME COLUMN,SET DATA TYPEorTYPE,sp_renameon Fabric and Synapse, and so on. --namenames the file;--dry-runprints it instead of writing.
Apply migrations in order before running dbt, with your usual tool for the warehouse, and
commit each one with the model change that caused it. Files are never rewritten; to undo a
change, change the model back and write another migration. murakumo migrate --check in CI
catches a model change that needs a migration and has none. A project with no migrations/
folder has nothing to check, so migrations are opt-in: the first murakumo migrate starts them.
Rule codes¶
| Code | Level | Meaning |
|---|---|---|
| MK501 | error | An entity reads two entities with no single relationship between them |
| MK502 | error | A mapping combines several columns without an expression |
| MK503 | warning | A column has no mapping; it is generated as null |
| MK504 | warning | A modelled entity has no mappings, so nothing is generated |
| MK505 | error | An SCD2 entity has no primary or natural key |
| MK506 | error | No mapping names a source column, so there is no table to select from |
| MK507 | error | A mapping expression is not valid SQL |
| MK508 | error | A cumulative table's grain column has no column or mapping |
| MK509 | error | A Data Vault table cannot be built: a link with fewer than two hubs, a satellite without its hub or link, a relationship that does not map the business key, or no raw table to read |
| MK701 | error | Two choices in the stack do not go together (Snowpipe on DuckDB, Lambda landing in a folder) |
| MK702 | warning | A source's connection fills a table that is not a raw table in the model |
| MK703 | warning | A transient layer asks for more than 1 day of time travel, which Snowflake refuses |
| MK704 | error | governance.masking names a rule that does not exist |
| MK705 | warning | A column to mask is not text: masking policies here mask text columns |
| MK706 | warning | A table with access: private has no domain, so no group may use it |
| MK707 | error | row_access.column is not a column of the table, or is not text (a warning when the table is not a mart) |
| MK708 | warning | row_access allows no role, so nobody would see a row |
| MK709 | warning | row_access on a warehouse other than Snowflake: nothing is generated |
An entity with an error is skipped; the rest of the project is still generated.