SQL models and materialization

Start with the output table

Write down what the finished model should represent before you write the query. A model with one clear business concept is easier to test, change, and reuse than a table that combines several incompatible grains.

For example, an order-level model and a daily revenue model can both be correct. They are different datasets and should stay separate because their rows mean different things.

Glossary

• SQL model is an asset whose query produces a dataset. The asset definition can also describe the destination, materialization, columns, and checks.
• Staging model standardizes raw source data for later use. It usually handles column names, types, timestamps, and basic cleanup close to the source.
• Fact model records events or measurable activity, such as orders, payments, or page views. Its grain is usually an event, entity, or entity over time.
• Dimension model describes an entity used to group or filter facts, such as customers, products, or accounts. It should have one clear key and a documented history rule.
• Grain is the meaning of a row. It tells you which GROUP BY columns are required and what a duplicate row looks like.
• Primary key identifies a row at that grain. A merge materialization uses the declared primary key to decide which records match.
• Materialization controls how query results reach the destination. It should match the size of the table and how the source records change.

Example: customer revenue by day

This asset has one row per customer per calendar day. Its primary key is the pair of customer_id and order_date; revenue updates when a matching row changes.

/* @bruin
name: analytics.daily_customer_revenue
type: duckdb.sql
materialization:
  type: table
  strategy: merge
columns:
  - name: customer_id
    primary_key: true
  - name: order_date
    primary_key: true
  - name: revenue
    update_on_merge: true
@bruin */

SELECT
  customer_id,
  order_date,
  SUM(amount) AS revenue
FROM staging.orders
GROUP BY 1, 2

The query and materialization need to agree. If the source can correct an order from a previous day, the run must include that day and the model must update the existing customer-day row.

Review the model before running it

• Read the SELECT and confirm that every join preserves the intended grain.
• Check that the primary key has the columns needed to identify a row.
• Check whether the source can insert, update, or delete historical records.
• Choose a materialization that handles those changes.
• Add a check for the failure that would cause the most harm, such as duplicate keys or null amounts.

Official links

• Bruin SQL assets
• Bruin materialization reference
• Bruin quality checks