Modelling patterns¶
Patterns from dimensional modelling and from dbt's own guidance that Murakumo writes for you:
- unknown members;
- accumulating snapshots;
- conformed dimensions and the bus matrix;
- access levels;
- exposures;
- masking policies;
- slowly changing dimensions of types 3, 4 and 6;
- the Data Vault pack: hubs, links and satellites;
- the semantic layer: metrics defined once.
Each one is set in the model, and is in the generated dbt project, the plain SQL and the replication guide.
Unknown members¶
A fact row whose dimension key is missing would join to nothing, and drop out of every report
that joins them. Kimball's answer is an unknown member: a row in each dimension, with key
-1 and "Unknown" for its text, that such facts point at instead.
# a dimension that should not have one
name: dim_calendar
kind: dimension
unknown_member: false # true or false; left out, it follows modelling.unknown_members
- Each dimension with a key gets its unknown row, added to the rows it builds:
- key columns are
-1(or'-1'for text keys); - text columns are
'Unknown'; - columns with accepted values are null, so the accepted-values test still passes;
- other columns are null, or a neutral value (
0,false,1900-01-01) when they cannot be null. - An SCD2 dimension's unknown row is always current, valid from 1900-01-01.
- Every fact with a relationship to such a dimension writes
coalesce(<key>, -1), so a missing key points at the unknown row. The relationship test keeps reporting keys that are present but not yet in the dimension. - In the canvas, a dimension's inspector has Unknown row: as the project, yes or no.
Accumulating snapshots¶
Some business processes pass through milestones: an order is placed, shipped, then delivered. An accumulating snapshot has one row per process, with a date column per milestone. Each run updates the row as the process moves on, and computes the time between milestones.
name: fct_order_fulfilment
layer: marts
kind: accumulating
grain: [order_id, line_number] # the key of each row
accumulating:
milestones: [ordered_on, shipped_on, received_on] # its date or timestamp columns, in order
unit: day # day | hour | minute
attributes: [...]
mappings: [...] # as any other table
- The table is incremental, and merges by its grain. A milestone once reached keeps its date, even when a later run does not see it again.
- It adds a column for the time between each pair of consecutive milestones
(
ordered_on_to_shipped_on_days), null until both are reached. - It adds
current_milestone: the latest milestone reached. - MK314: it needs
accumulating:and a grain. - MK315: each milestone must be a date or timestamp column.
- In the canvas, a table of kind accumulating gets Per, Milestones and Time in in the inspector.
Conformed dimensions and the bus matrix¶
A conformed dimension is one table that every domain's facts use, so the numbers they report agree. The bus matrix shows which business processes (facts) use which dimensions.
- MK316: a fact uses another domain's dimension that is not marked conformed. Mark it, if every domain shares it.
- MK317: a dimension has the same key (from the same source column) as a conformed one. It is a copy that will drift: use the conformed one.
- The bus matrix is drawn from the relationships between facts (and accumulating snapshots and events) and dimensions. It is in the replication guide's reference chapter, and in the canvas's Bus matrix panel, where each name selects its table.
Access levels¶
dbt's model access says who may build on a model:
private: only models in its group (its domain);protected: models in this project (dbt's default);public: other projects too.
The model's YAML gets access:. MK706: a private table needs a domain, or no group may use
it.
Exposures¶
The dashboards, notebooks and apps that read the marts. dbt lists them with what they read, so lineage and the docs show what a change would break.
# model/exposures.yaml
exposures:
- name: sales_dashboard
type: dashboard # dashboard | notebook | analysis | ml | application
description: Weekly revenue and late shipments, by customer segment.
owner: Sales analytics
email: sales-analytics@example.com
url: https://bi.example.com/dashboards/sales
maturity: high # high | medium | low
depends_on: [fct_order_line, fct_order_fulfilment, dim_customer]
- They are written to
generated/dbt/models/_exposures.yml, drawn in the guide's lineage and architecture, and listed in its reference chapter. - MK209: an exposure reads a table that does not exist. MK109: two exposures have the same name.
- In the canvas: Bus matrix → Exposures adds and edits them.
Masking policies¶
Columns marked pii: true, or with a classification, can be masked on Snowflake for every role
that should not see them.
# murakumo.yaml
governance:
masking: # pii, or a classification, to a rule
pii: redact
restricted: hash
unmasked_roles: [analyst_pii] # these roles see the real values
| Rule | Shows |
|---|---|
redact |
*** |
partial |
all but the last 4 characters as * |
hash |
a SHA-2 hash: the same value gives the same hash, so it can still be joined and counted |
null |
null |
- Each rule is a masking policy,
<database>.governance.murakumo_mask_<rule>. - The marts' text columns that match are set to use it.
- Re-creating a table drops its policies, so dbt sets them again after each build: a post-hook
calls the
murakumo_maskingmacro. The macro creates or updates the policies, then sets them on the table's columns. generated/ddl/snowflake/masking.sql(and the guide'ssql/masking.sql) is the same as SQL to run by hand.murakumo stack deployhas a masking step that creates the policies in an environment.- The role that builds needs CREATE MASKING POLICY on the governance schema (its owner has it). To set a policy, it needs to own both the table and the policy, or APPLY MASKING POLICY.
- MK704: a rule that does not exist. MK705: a column to mask that is not text (masking policies here mask text).
- Other warehouses have no masking policies here, so nothing is generated for them.
- In the canvas: Settings → Personal data.
Row-access policies¶
Which rows of a mart each role may see (Snowflake):
# model/entities/marts/fct_orders.yaml
row_access:
column: region # the column that decides; a text column
allow:
SALES_EMEA: [EMEA, UK]
SALES_US: [US]
- A role sees the rows whose column has one of its values. Roles in
governance.unmasked_rolessee every row. A role that is not named sees none. - Each table gets a policy,
governance.murakumo_rows_<table>, set after each build by a dbt post-hook (murakumo_row_access): re-creating a table drops its policies. generated/ddl/snowflake/row_access.sql(and the guide'ssql/row_access.sql) is the same as SQL to run by hand, andmurakumo stack deployhas a row_access step.- MK707: the column does not exist, is not text, or the table is not a mart. MK708: no role is allowed, so nobody would see a row. MK709: the warehouse is not Snowflake, so nothing is generated.
- In the canvas: the inspector's Rows by and Roles.
Change feeds (CDC)¶
A raw table that receives changes, not rows as they now are (Debezium, a database's log, AWS DMS):
load: cdc
cdc:
operation: op # the column that says what happened to the row
deletes: [d, delete, deleted] # its values that mean deleted (the default)
order: changed_at # which change is latest; default the watermark, then load time
- The raw table keeps every change as it arrived: nothing is replaced.
- Staging is the table as it is now: each key's latest change, without the keys whose latest change is a delete. Everything built on staging follows.
- Without
cdc.operation, staging is still the latest change of each key, and no row counts as deleted. - MK321: no primary key. MK322:
operationororderis not a column. MK325: a table built incrementally from a feed that marks deletes keeps the deleted rows until a full rebuild. - In the canvas: the inspector's New rows →
cdc, then Operation and Latest by.
Slowly changing dimensions: types 3, 4 and 6¶
Type 1 overwrites and type 2 keeps every version. Three more are built:
name: dim_part
kind: dimension
history: scd3 # scd1 | scd2 | scd3 | scd4 | scd6
tracked: [brand, retail_price] # scd3 and scd6; left out: every column that is not a key
| History | What is built |
|---|---|
scd3 |
One row per key, with previous_<column> beside each tracked column: its value before its latest change. The table is incremental, so each run compares what arrives with the row it replaces |
scd4 |
The dimension holds only the current version of each key (valid_from says since when), and <name>_history every version, with valid_from, valid_to and is_current |
scd6 |
Type 2, with current_<column> for each tracked column on every version: the value as it is now, so old facts can be reported by today's values |
- MK318: a tracked column does not exist. MK319:
trackedis set but the history is notscd3orscd6. - In the canvas, a dimension's inspector has History, and Tracked for types 3 and 6.
The Data Vault pack¶
Hubs, links and satellites, insert-only, so nothing loaded is ever lost.
name: hub_customer
layer: intermediate
kind: hub # hub | link | satellite
attributes:
- {name: customer_number, type: bigint, nullable: false, key: natural} # the business key
mappings:
- {target: customer_number, sources: [customer.c_custkey]}
| Kind | What it holds |
|---|---|
hub |
Each business key once: <hub>_hk (its hash), the key's columns, load_date (when it was first loaded), record_source |
link |
Each combination of business keys once: <link>_hk, a <hub>_hk for each hub it connects, the keys' columns, load_date, record_source |
satellite |
The descriptive columns of a hub or link, a row each time they change: <parent>_hk, hashdiff, load_date, record_source |
- A link connects its hubs through relationships to them; a satellite has one relationship, to its hub or link. The relationships map the business key's columns.
- Hash keys are md5 of the trimmed, upper-cased key values joined by
||, soabcandABCare one key, and a hub's key hashes the same wherever it is computed. - Each run adds what was loaded since the last one (by
load_date). Within a run, a hub or link keeps a key's first row, a satellite its latest. <satellite>_currentis a view of each key's latest row: other tables that read a satellite read it.- MK320: a hub, link or satellite has no business key. MK509: a link with fewer than two hubs, a satellite without its parent, or a vault table that does not read a raw table.
- The commerce example has
hub_customer,hub_order,link_order_customerandsat_customer.
The semantic layer¶
Metrics are defined once, in model/metrics.yaml, and written as dbt semantic models and
metrics (MetricFlow), so every tool asks for "revenue" and gets the same number.
metrics:
- name: revenue
label: Revenue
entity: fct_order_line # the table it is counted on
expression: sum(net_amount) # one or more aggregates
time: ordered_on # left out: the table's first date column
grain: day
dimensions: [order_status, dim_customer.market_segment]
- name: average_order_value
entity: fct_order_line
expression: sum(net_amount) / count(distinct order_id)
- name: open_order_lines
entity: fct_order_line
expression: count(*)
filters: ["order_status = 'O'"] # only rows that meet them count
generated/dbt/models/semantic/_semantic.ymlhas a semantic model per table a metric is counted on: its key as the primary entity, its relationships to dimensions as foreign entities, its time column, the columns metrics break it down by, and a measure per aggregate.- Each dimension those metrics use gets a semantic model too. An SCD2 dimension joins by its
validity (
valid_from,valid_to), so each fact row finds the version current at its time. - One aggregate is a simple metric; several (
sum(a) / count(*)) are a derived metric over a hidden simple metric per aggregate (<metric>_part_1, ...). metricflow_time_spineis the table of days MetricFlow needs.- Query it with MetricFlow:
pip install "dbt-metricflow[dbt-duckdb]", thenmf query --metrics revenue --group-by metric_time__month. - MK210: a metric's table has no date column. MK211: its expression has no aggregate. MK212: a dimension it names does not exist.
- In the canvas: Bus matrix → Metrics adds and edits them.