Data lineage (v0.7)
File: 18-data-lineage.xdbml · Target: Snowflake + Stripe API
Declared data lineage with Dep (spec §27). Raw data lands in Snowflake from the shop’s database and from the Stripe API, which a Container declares so that dependencies can start from it; dbt models clean it in a staging layer and join it into marts. The example uses the three forms: the short form for an array unnested into a table (field to entity), blocks gathering the columns one model fills, with the query in the note, and inline dep settings on the columns of stg_orders. In the diagram, each dependency draws as an amber curve with an arrowhead at its downstream end, the first and last names of a customer meet at a junction before full_name, the stg_payments Dep draws in its own color, and the Refs between staging tables keep their crow’s feet. A Dep from an entity to a field records a count (daily_revenue.orders) and an existence flag (fct_orders.paid).
Source
xdbml
xdbml: 0.7
// ---------------------------------------------------------------------------
// Data lineage: which data is computed from which (spec 27).
//
// The analytics warehouse of an online shop. Raw data lands from the shop's
// database and from the Stripe API; dbt models clean it in a staging layer
// and join it into marts. Each Dep records which element feeds which, from
// upstream to downstream. The transformations themselves stay in the notes,
// as text.
//
// - `Dep: a -> b` states one dependency, between entities, Views or
// fields, or from an entity to a field and the reverse.
// - A block gathers the dependencies into one downstream element, such as
// the columns one dbt model fills. Its note holds the query.
// - `[dep: <- source]` states a dependency in the brackets of the element
// it concerns.
// - In the diagram, a dependency draws as an amber curve with an arrowhead,
// and two fields combined into one meet at a junction.
//
// A Ref still records the relationships between rows: the dependencies say
// how the data is produced, the relationships how its rows connect.
// ---------------------------------------------------------------------------
Project shop_analytics {
targets: [Snowflake, 'Stripe API']
Note: 'Lineage of the analytics warehouse, from the sources to the marts (spec §27).'
}
// An external source, declared so that dependencies can start from it.
Container stripe [type: database, target: 'Stripe API'] {
Entity charges {
id varchar [pk]
amount int [note: 'In cents']
customer varchar
created int [note: 'Unix time']
}
}
Container raw [type: schema, target: Snowflake] {
Table orders {
id int [pk]
customer_id int
status varchar
ordered_at timestamp
line_items array [object {
sku varchar
quantity int
unit_price decimal(10, 2)
}]
}
Table customers {
id int [pk]
first_name varchar
last_name varchar
email varchar
}
}
Container staging [type: schema, target: Snowflake] {
// The inline form: each column names the column it is copied from.
Table stg_orders [dep: <- raw.orders] {
order_id int [pk, dep: <- raw.orders.id]
customer_id int [dep: <- raw.orders.customer_id]
status varchar [dep: <- raw.orders.status]
ordered_at timestamp [dep: <- raw.orders.ordered_at]
}
Table stg_order_lines {
order_id int
sku varchar
quantity int
unit_price decimal(10, 2)
}
Table stg_customers {
customer_id int [pk]
full_name varchar
email varchar
}
Table stg_payments {
payment_id varchar [pk]
customer_id varchar
amount_cents int
paid_at timestamp
}
}
Container marts [type: schema, target: Snowflake] {
Table fct_orders {
order_id int [pk]
ordered_at timestamp
customer_name varchar
order_total decimal(12, 2)
paid boolean
}
View daily_revenue [materialized: true] {
day date
orders int
revenue decimal(14, 2)
}
}
Ref: staging.stg_order_lines.order_id > staging.stg_orders.order_id
Ref: staging.stg_orders.customer_id > staging.stg_customers.customer_id
// Field to entity: each element of the array becomes a row.
Dep unnest_lines: raw.orders.line_items -> staging.stg_order_lines [note: 'One row per element of line_items']
Dep stg_order_lines {
raw.orders.id -> staging.stg_order_lines.order_id
raw.orders.line_items.[*].sku -> staging.stg_order_lines.sku
raw.orders.line_items.[*].quantity -> staging.stg_order_lines.quantity
raw.orders.line_items.[*].unit_price -> staging.stg_order_lines.unit_price
}
// Two fields combined into one meet at a junction.
Dep stg_customers [owner: 'analytics'] {
raw.customers.id -> staging.stg_customers.customer_id
raw.customers.first_name -> staging.stg_customers.full_name
raw.customers.last_name -> staging.stg_customers.full_name
raw.customers.email -> staging.stg_customers.email [note: 'Lowercased']
Note: '''
~~~sql
SELECT id AS customer_id,
first_name || ' ' || last_name AS full_name,
LOWER(email) AS email
FROM raw.customers
~~~
'''
}
Dep stg_payments [color: #7c3aed] {
stripe.charges.id -> staging.stg_payments.payment_id
stripe.charges.customer -> staging.stg_payments.customer_id
stripe.charges.amount -> staging.stg_payments.amount_cents
stripe.charges.created -> staging.stg_payments.paid_at [note: 'TO_TIMESTAMP(created)']
}
// Entity to field: `paid` flags an order that has a payment.
Dep fct_orders [materialized: 'table'] {
staging.stg_orders.order_id -> marts.fct_orders.order_id
staging.stg_orders.ordered_at -> marts.fct_orders.ordered_at
staging.stg_customers.full_name -> marts.fct_orders.customer_name
staging.stg_order_lines.quantity -> marts.fct_orders.order_total
staging.stg_order_lines.unit_price -> marts.fct_orders.order_total
staging.stg_payments -> marts.fct_orders.paid
Note: 'Joins each order to its customer, its lines and its payment; order_total = SUM(quantity * unit_price).'
}
// Entity to field: `orders` counts the rows of fct_orders.
Dep daily_revenue {
marts.fct_orders.ordered_at -> marts.daily_revenue.day
marts.fct_orders -> marts.daily_revenue.orders
marts.fct_orders.order_total -> marts.daily_revenue.revenue
}