Model files (IR v1)¶
A Murakumo project is a folder with a manifest and a model. The model is the canonical metadata model (the IR) that every generator compiles from. It is plain YAML, so you can edit it by hand, review it in pull requests, and the canvas reads and writes the same files.
my_platform/
murakumo.yaml the manifest: targets, modelling choices, conventions
model/
sources/nyc_tlc.yaml one source system per file
entities/ one entity per file, in any folder structure you like
nyc_tlc/yellow_tripdata.yaml
marts/fct_trip.yaml
relationships.yaml every relationship, in one list
metrics.yaml every metric, in one list
layout.yaml where the canvas draws each entity (optional)
decisions.yaml why the platform is built as it is: see docs/stack.md (optional)
custom/ hand-written code, never touched by Murakumo
generated/ written by `murakumo generate`; see docs/generating.md
New entities go in entities/<source>/ for raw tables and entities/<layer>/ otherwise. If you
move a file, Murakumo keeps it where you put it.
Ids¶
Every source, entity, attribute, relationship and metric has an id (a UUID). Names are what
people read and what references use; ids are how Murakumo knows that a renamed column is the
same column, so a rename becomes ALTER ... RENAME rather than a drop and an add.
murakumo init, murakumo add and the importers (docs/importing.md) write ids for you. If you write YAML by hand, leave id out
and run murakumo validate --fix to fill them in. Never copy an id to a new object.
Entity¶
id: 0e3b910a-721e-4b22-9dd0-50bf65e8b966
name: fct_trip
description: One row per yellow taxi trip.
layer: marts # raw | staging | intermediate | marts | semantic
kind: fact # table | fact | dimension | bridge | hub | link | satellite | event | cumulative | accumulating | reference
domain: mobility # must be listed under project.domains
source: nyc_tlc # for raw tables: the source system
grain: [trip_id] # the columns that make a row unique
history: none # none | scd1 | scd2 | scd3 | scd4 | scd6 | snapshot
tracked: [] # scd3 and scd6: the columns whose changes it keeps (default: all but keys)
load: incremental # full | append | incremental | cdc | streaming
watermark: pickup_at # the column that orders a key's versions (and, by choice, says what is new)
cdc: # load: cdc only. A change feed: Debezium, a database's log, DMS
operation: op # the column that says what happened to the row; without it no row counts as deleted
deletes: [d, delete, deleted] # its values that mean the row was deleted (the default)
order: changed_at # which change is the latest; default the watermark, then load time
incremental_by: load_time # load_time (the default: what was loaded since the last run) | watermark
lookback_hours: 0 # an incremental table also re-reads this many hours, for stragglers
partition_by: [day(pickup_at)] # a column, or a transform: day(), month(), year(), hour(), bucket(n, col)
cluster_by: []
freshness: { warn_after_hours: 24, error_after_hours: 48 }
access: public # private | protected | public: who may build on it (dbt model access)
row_access: # which rows each role sees (Snowflake); see modelling-patterns.md
column: region
allow: { SALES_EMEA: [EMEA, UK] }
tags: []
attributes:
- name: trip_id
type: string
nullable: false
key: surrogate # primary | natural | surrogate
tests: [unique, not_null]
- name: total_amount
type: money
classification: confidential # public | internal | confidential | restricted
pii: false
unit: USD
mappings:
- target: total_amount
sources: [yellow_tripdata.total_amount]
- target: trip_id
sources: [yellow_tripdata.VendorID, yellow_tripdata.tpep_pickup_datetime]
expression: md5(concat_ws('|', VendorID, tpep_pickup_datetime)) # DuckDB SQL, translated per warehouse
tests: # table-level rules
- type: unique_combination
columns: [pickup_at, pickup_zone_id]
severity: warn
properties: {} # your own metadata, available to custom templates
Columns are generated in the order they are listed. murakumo move column fct_trip total_amount
--after trip_id reorders them (or drag them on the canvas); tables that already exist keep their
order, since no warehouse can reorder columns in place.
A calculated column is a column whose mapping has an expression. The expression is DuckDB SQL
over the tables the entity is built from, written entity.column (or just column, when a
mapping source names its table), and is translated for each warehouse. The canvas checks and
previews expressions as you write them; see the canvas.
Domains group entities by business area. List them in murakumo.yaml, with an owner if you
like, and set domain: on each entity; an entity's domain must be listed (MK202):
project:
name: shop
domains:
- name: sales
owner: Sales analytics
- finance # just a name is fine too
murakumo add domain, rename domain (its entities follow) and remove domain change the list
without touching the file's comments, as does the canvas's Domains panel. Each domain with models
becomes a dbt group.
Lineage comes from the mappings: a column is made from the columns its mapping's sources and
expression name. murakumo lineage fct_trip.trip_id shows where a column comes from, as a tree,
down to the raw columns; --down shows what uses it instead; --format json gives the same as
data. Give a table instead of a column for all of its columns. Generated dbt columns carry it as
meta.lineage.
Only name is required. Attributes also accept default, physical_types (an override per
target, such as {snowflake: VARCHAR(16)}), source_type, accepted_values (a list of codes, or
a mapping of code to label), example, business_term and description.
Logical types¶
string, text, boolean, smallint, integer, bigint, float, double,
decimal(p,s), money, date, time, timestamp, timestamp_tz, uuid, json, binary,
array<type>, struct. string(n) sets a maximum length. Generators map each one to the right
physical type per warehouse.
Quality rules¶
Write tests: [unique, not_null] for simple rules, or a full rule. Rules on a column sit under the
column; rules on the whole table sit under the table's tests:.
| Type | Checks | Needs | Judges each row |
|---|---|---|---|
not_null |
every row has a value | nothing | yes |
unique |
no two rows share a value | nothing | no |
accepted_values |
values come from a list | values (or the column's accepted_values) |
yes |
range |
numbers or dates between bounds | min, max or both |
yes |
regex |
text matches a pattern | pattern |
yes |
length |
text length between bounds | min, max or both |
yes |
custom_sql |
an expression every row satisfies | sql (staging's column names) |
yes |
null_share |
at most a share of values is empty | max, from 0 to 1 |
no |
distinct_count |
how many distinct values | min, max or both |
no |
recent |
the newest date or time is recent | max_age_hours |
no |
row_count |
how many rows (table level) | min, max or both |
no |
unique_combination |
no two rows share these columns together (table level) | two or more columns |
no |
freshness |
the table loads often enough | max_age_hours |
no |
What a failing rule does is its action:
action |
When a row or the table fails |
|---|---|
fail (default) |
the run stops: an error test |
warn |
it is recorded and the run carries on: a warning test |
quarantine |
the rows that fail are set aside in <staging model>__quarantine, with the first rule each failed (_failed_rule); the rest carry on. Only for rules that judge each row, on raw tables |
attributes:
- name: Borough
type: string
tests:
- type: accepted_values
values: [Manhattan, Queens, Bronx, Brooklyn, Staten Island, EWR]
action: quarantine
- name: service_zone
type: string
example: Boro Zone # made-up data for `verify` uses it for the regex rule
tests:
- { type: regex, pattern: '^[A-Za-z ]+$', action: warn }
- { type: null_share, max: 0.05 }
tests:
- { type: row_count, min: 1 }
- { type: custom_sql, sql: location_id > 0, action: quarantine, description: ids are positive }
severity: error | warn still works; action sets it (fail is error, warn is warn). Every
rule becomes a dbt test (or, for quarantine, the CASE that splits staging), a check in the PySpark
and Polars jobs, an expectation for Great Expectations and a SodaCL check when
quality.framework names them, and a quality entry in the table's data contract. In the canvas:
the inspector's Quality rules.
Data contract¶
contract:
version: 1.2.0
status: active # proposed | draft | active | deprecated | retired
enforce: block # block: a load that breaks it stops the build; warn: it is reported
Every table has a contract in generated/contracts/<table>.odcs.yaml (ODCS v3.0.2); these
settings are its version and status. See operating.md.
Cumulative tables¶
A cumulative table keeps, for each key, its whole history in one row per day: the days it was active, and a value per active day for each measure, most recent first. It is the pattern in the project brief: each day takes yesterday's rows and puts today's activity in front of their arrays, so a year of history never has to be scanned again.
name: customer_order_activity
layer: marts
kind: cumulative
grain: [customer_id] # the key it accumulates per
cumulative:
date: fct_order_line.ordered_on # the source column whose day counts as activity
measures: # optional: one value per active day
order_lines: count(*)
spend: sum(fct_order_line.net_amount)
attributes:
- { name: customer_id, type: bigint, nullable: false }
mappings:
- { target: customer_id, sources: [fct_order_line.customer_id] }
The generated table adds dates_active (an array of dates), an array per measure, days_active,
last_active and snapshot_date, unique on the grain and snapshot_date. Each run adds one day:
run_date (murakumo build --run-date 2024-01-31, or what the orchestrator passes), or the
latest day with activity. murakumo backfill customer_order_activity --from 2024-01-01 --to
2024-01-31 builds a range of days, one after another. Rows whose key is empty are left out:
they have no history to join. On the canvas, a table of kind cumulative gets the same settings in
the inspector.
Modelling patterns¶
Unknown members, accumulating snapshots, conformed dimensions, access levels, exposures
(model/exposures.yaml) and masking policies have a page of their own:
modelling patterns.
Relationship¶
relationships:
- name: fct_trip_pickup_zone
from: fct_trip # usually the many side, holding the foreign key
to: dim_taxi_zone
cardinality: many_to_one # one_to_one | one_to_many | many_to_one | many_to_many
optional: true # a trip may have no zone
role: pickup # tells apart two links between the same tables
keys:
- { from: pickup_zone_id, to: zone_id }
Source system¶
name: nyc_tlc
type: file # database | file | api | stream | manual
connection: https://d37ci6vzurychx.cloudfront.net/trip-data/
load_tool: dlt
schedule: monthly
Use env:NAME in connection for anything secret; never put credentials in model files.
Metric¶
metrics:
- name: total_revenue
label: Total revenue
entity: fct_trip # the table it is counted on
expression: sum(total_amount) # one or more aggregates
time: pickup_at # left out: the table's first date or timestamp column
grain: day # hour | day | week | month | quarter | year
dimensions: [dim_taxi_zone.borough]
filters: ["payment_type = 1"] # only rows that meet them count
Metrics become dbt's semantic layer: see modelling patterns.
Validation rules¶
murakumo validate reports errors (the model cannot be generated) and warnings (it can, but
something looks wrong). It exits with 1 on errors, or on warnings too with --strict.
| Code | Level | Meaning |
|---|---|---|
| MK000 | error | No murakumo.yaml in this folder or any folder above it |
| MK001 | error | A file is not valid YAML, or cannot be read |
| MK002 | error | A field is wrong: unknown key, bad type, missing value, value not allowed |
| MK003 | error | A list file has the wrong shape |
| MK004 | warning | A file under model/ is ignored (wrong extension or unknown name) |
| MK005 | warning | Objects have no id; run murakumo validate --fix |
| MK101 | error | Two entities have the same name |
| MK102 | error | An entity has two columns with the same name |
| MK103 | error | Two objects share an id |
| MK104–106 | error | Two sources, metrics or relationships have the same name |
| MK107 | error | A table has the name of the staging model generated for a raw table (stg_<source>__<table>) |
| MK109 | error | Two exposures have the same name |
| MK108 | warning | Two tables of different layers (or a table and an audit table) share a name, so they would collide in someone's own schema on a shared warehouse |
| MK201 | error | An entity's source is not declared |
| MK202 | error | An entity's domain is not in project.domains |
| MK203 | error | grain, watermark, partition, cluster or test columns do not exist |
| MK204 | error | A relationship points to an entity that does not exist |
| MK205 | error | A relationship's join column does not exist |
| MK206 | error | A mapping targets a column the entity does not have |
| MK207 | error | A mapping reads from an entity or column that does not exist |
| MK208 | error | A metric's entity does not exist |
| MK209 | error | An exposure reads an entity that does not exist |
| MK210 | error | A metric's table has no date or timestamp column to count it by |
| MK211 | error | A metric's expression cannot be read, or has no aggregate |
| MK212 | warning | A metric's dimension does not exist |
| MK301 | warning | A join compares different types, so it will need a cast |
| MK302 | warning | The one side of a relationship is not joined on its primary or natural key |
| MK303 | warning | A fact table has no grain |
| MK304 | warning | A dimension has no primary key |
| MK305 | warning | An entity has no columns |
| MK306 | warning | A PII column is not classified confidential or restricted |
| MK307 | warning | A name is not snake_case (raw tables are exempt: they mirror the source) |
| MK308 | warning | An incremental raw table has no watermark |
| MK309 | warning | SCD2 or SCD6 history without a primary or natural key |
| MK310 | error | A cumulative table has no cumulative: setting |
| MK311 | warning | A primary key column is nullable |
| MK312 | error | A cumulative table has no grain |
| MK313 | warning | A table is built incrementally by its watermark (incremental_by: watermark) but has no watermark column, so it is rebuilt in full |
| MK314 | error | An accumulating snapshot has no accumulating: setting, or no grain |
| MK315 | error | An accumulating snapshot's milestone is not a date or timestamp column |
| MK316 | warning | A fact uses another domain's dimension that is not marked conformed: true |
| MK317 | warning | A dimension has the same key as a conformed one: a copy of it |
| MK318 | error | A tracked column (tracked:) is not a column of the table |
| MK319 | warning | tracked is set, but only scd3 and scd6 history use it |
| MK320 | error | A hub, link or satellite has no business key (key: natural) |
| MK321 | error | A change feed (load: cdc) has no primary key |
| MK322 | error | cdc.operation or cdc.order is not a column of the table |
| MK323 | warning | cdc: settings on a table that is not load: cdc |
| MK324 | warning | load: streaming on a table no Kafka connection fills: it is loaded as append |
| MK325 | warning | A table built incrementally reads a change feed that marks deleted rows (cdc.operation): they stay until a full rebuild |
| MK326 | error | action: quarantine on a rule that judges the whole table (unique, row_count...), not each row |
| MK327 | error | action: quarantine on a table that is not raw: rows are set aside as they are loaded |
| MK328 | warning | A regex rule on Synapse, which has no regular expressions |
| MK329 | error | A regex rule's pattern is not a regular expression |
| MK330 | warning | A regex rule's column has no example that matches: made-up data for verify cannot match a pattern |
| MK331 | warning | quality.framework names a tool Murakumo does not write rules for (it writes dbt_tests, great_expectations, soda) |
| MK401 | error | An import would overwrite a table that belongs to another source or layer |
| MK402 | error | A dictionary row has no table or column name, or the file has no rows |
| MK403 | warning | A dictionary value could not be used (type, classification, foreign key) |
| MK404 | warning | An import found an unknown type, no tables, or too small a sample |
| MK501–508 | mixed | Generation problems; see docs/generating.md |
| MK601–604 | mixed | Schedules: a selection names nothing, a name is used twice, two schedules refresh "the rest", a table no schedule refreshes; see orchestration.md |
Editor support¶
murakumo schema entity prints the JSON Schema for entity files (also manifest, model,
source and relationship). Point your editor's YAML extension at it for autocomplete and
inline errors.