Skip to content

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: PULocationID becomes pu_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_at and _batch_id, which ingestion writes;
  • adds _source_system and _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.column sources 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, so md5(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_at is added, with _loaded_at: when the row's source row was loaded (the raw row's _ingested_at, carried through);
  • load: incremental becomes an incremental model keyed on the grain (or primary key) that reads what was loaded since its last run (_ingested_at past its latest _loaded_at). Rows that arrive late, with old dates, are picked up; rows read again replace themselves by key. lookback_hours: 6 looks that far back each time, for stragglers. incremental_by: watermark reads rows newer by the watermark column instead, as before 0.7;
  • history: scd2 becomes a view of the current rows, a dbt snapshot that keeps every version, and the dimension on top with valid_from, valid_to and is_current;
  • tables in marts get an enforced dbt contract (contracts.enforce_on in the manifest), so a wrong type fails the build instead of reaching consumers;
  • partition_by and cluster_by become 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):

  1. it generates the project for that warehouse in a temporary folder;
  2. creates the raw tables from the generated DDL;
  3. 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;
  4. runs dbt build: every model, snapshot and test;
  5. 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:

  1. creates any raw landing table that is missing, from the generated DDL (existing tables and their rows are left as they are);
  2. loads raw tables only when asked: --data TABLE=PATH from a file, folder, glob or table, or --sample N made-up rows into tables that are still empty;
  3. runs dbt build for 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

modelling:
  raw_columns: text      # typed (the default) | 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 through murakumo rename or 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 (--warehouse for another): RENAME COLUMN, SET DATA TYPE or TYPE, sp_rename on Fabric and Synapse, and so on.
  • --name names the file; --dry-run prints 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.