Data Lineage

Dependencies are how DBML expresses data lineage. A Dep records which table or column is built from which (docs). Ref means related by foreign key, while Dep means derived from.

Basic concepts

A dependency is a direction plus two endpoints. The arrow runs from upstream (the source) to downstream (the thing built from it), so A -> B and B <- A mean the same thing.

An endpoint is either a table or a column, which gives three levels:

• Table to table. Dep: raw_orders -> stg_orders
• Column to column. Dep: raw_orders.amount -> stg_orders.revenue
• Mixed. Dep: stg_orders -> fct_orders.revenue

Ways to write one:

• Short form. One dependency per line. E.g: Dep: raw_orders -> stg_orders. See design a pipeline DAG.
• Block form. Maps many columns at once. Holds the transform logic. See trace a column end to end and document a transform.
• Inline. Sits on the table or column it belongs to.
  • On a table header: Table fct_orders [dep: <- stg_orders]
  • On a column: order_id integer [dep: <- raw_stripe.id]

The example below uses the short and inline forms.

Dep: stg_orders -> fct_orders [note: 'One row per order. Maps status to state.']
Dep: fct_orders -> mart_revenue [note: 'Aggregates paid orders into monthly revenue.']

// or use inline dependencies
Table stg_orders {
  order_id integer [pk, dep: <- raw_stripe.id]
  amount decimal [dep: <- raw_stripe.amount]
  ...
}

tip

Add a note to any dependency to record what the transform does. The description stays on the dependency instead of in a separate doc. See document a transform for SQL and custom keys.

To lay a pipeline out by layer, wrap each stage in a table group.

Use cases

Four jobs Dep is built for, from a first sketch to documenting what is already running.

Design a pipeline DAG

One short-form line per hop. This is the fastest way to draft a pipeline at a high level.

• It is plain text, so you can write the whole DAG before any SQL exists.
• Share the link and get review on the shape of the pipeline while it is still cheap to change.
• Add columns and transform logic later, in the same file.

Table raw_stripe {
  id integer [pk]
  amount decimal
  status varchar
}

Table stg_orders {
  order_id integer [pk]
  amount decimal
  status varchar
}

Table mart_revenue {
  month date
  revenue decimal
}

Dep: raw_stripe -> stg_orders [note: 'Cleans raw charges. Drops voided rows.']
Dep: stg_orders -> mart_revenue [note: 'Aggregates paid orders into monthly revenue.']

Trace a column end to end

Map dependencies column by column and you can follow one field across every hop that produced it. Click a field in the diagram to focus its end-to-end lineage. That is how you answer "where does this number come from" on a pipeline too large to read at once.

Two forms do this, and they mix freely:

• Block form maps many columns at once.
• Inline dep maps one column at a time, on the column itself.

Every edge in one block must land on the same downstream table, so a chain of hops needs one block per hop. Below, fct_orders and mart_revenue each get their own.

Table raw_orders {
  id integer [pk]
  amount decimal
  status varchar
}

Table stg_orders {
  order_id integer [pk, dep: <- raw_orders.id]
  amount decimal [dep: <- raw_orders.amount]
  status varchar [dep: <- raw_orders.status]
}

Table fct_orders {
  order_id integer [pk]
  amount decimal
  state varchar
}

Table mart_revenue {
  month date
  revenue decimal
}

Dep {
  stg_orders.order_id -> fct_orders.order_id
  stg_orders.amount -> fct_orders.amount
  stg_orders.status -> fct_orders.state

  note: 'Renames status to state and keeps paid orders.'
}

Dep {
  fct_orders.amount -> mart_revenue.revenue

  note: 'Sums order amounts by month.'
}

Document a transform

Keep the query next to the dependency it produced.

• note holds the query. Use ''' for a multi-line note.
• Notes render as Markdown. Wrap the query in a fenced code block for syntax highlighting.
• Name the block to label it, and add any key you like (materialized, owner). Custom keys are kept with the dependency.
• color sets the color of the block's lineage lines, as #rgb or #rrggbb.

Below, the three edges match the three columns in the SELECT. The diagram and the query stay in step.

Table raw_orders {
  id integer [pk]
  amount decimal
  status varchar
}

Table raw_payments {
  order_id integer
  method varchar
}

Table stg_orders {
  order_id integer [pk]
  revenue decimal
  payment_method varchar
}

Dep order_staging {
  raw_orders.id -> stg_orders.order_id
  raw_orders.amount -> stg_orders.revenue
  raw_payments.method -> stg_orders.payment_method

  note: '''
    ```sql
    SELECT o.id     AS order_id,
           o.amount AS revenue,
           p.method AS payment_method
    FROM raw_orders o
    JOIN raw_payments p ON p.order_id = o.id
    WHERE o.status != 'void'
    ```
  '''
  materialized: table
  owner: 'data-team'
}

Document a database view

A view is already a dependency. It is defined by a query over other tables.

• Write the view as a Table.
• Point each base column at the view column it produces.
• Put the CREATE VIEW statement in the note.
• Set materialized: view to record what it is in the warehouse.

Table customers {
  id integer [pk]
  email varchar
  status varchar
}

Table orders {
  id integer [pk]
  customer_id integer
  total decimal
}

Table active_customers {
  customer_id integer [pk]
  email varchar
  lifetime_value decimal
}

Ref: orders.customer_id > customers.id

Dep active_customers_view {
  customers.id -> active_customers.customer_id
  customers.email -> active_customers.email
  orders.total -> active_customers.lifetime_value

  note: '''
    ```sql
    CREATE VIEW active_customers AS
    SELECT c.id         AS customer_id,
           c.email      AS email,
           SUM(o.total) AS lifetime_value
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE c.status = 'active'
    GROUP BY c.id, c.email
    ```
  '''
  materialized: view
}