Materialization

Materialization is the idea of taking a simple SELECT query, and applying the necessary logic to materialize the results into a table or view.

Bruin supports various materialization strategies catered to different use cases.

Here's a sample asset with materialization:

/* @bruin

name: dashboard.hello_bq
type: bq.sql

materialization:
    type: table

@bruin */

select 1 as one
union all
select 2 as one

Definition Schema

The top level materialization key determines how the asset will be materialized.

Here's an example materialization definition:

materialization:
  type: table
  strategy: delete+insert
  incremental_key: dt
  partition_by: dt
  cluster_by:
    - dt
    - user_id

materialization > type

The type of the materialization, can be one of the following:

• table
• view

Default: none

materialization > strategy

The strategy used for the materialization, can be one of the following:

• create+replace: overwrite the existing table with the new version.
• delete+insert: incrementally update the table by only refreshing a certain partition.
• truncate+insert: truncate the entire table and insert new data (full refresh without DROP/CREATE).
• append: only append the new data to the table, never overwrite.
• merge: merge the existing records with the new records, requires a primary key to be set.
• time_interval: incrementally load time-based data within specific time windows.
• ddl: create a new table using a DDL (Data Definition Language) statement.
• datavault_hub: incrementally load unique business entities into a Data Vault hub.
• datavault_link: incrementally load unique relationships into a Data Vault link.
• datavault_satellite: incrementally load descriptive history into a Data Vault satellite.
• scd2_by_column: implement SCD2 logic that tracks changes based on column value differences.
• scd2_by_time: implement SCD2 logic that tracks changes based on time-based incremental key.

materialization > partition_by

Define the column that will be used for the partitioning of the resulting table. This is used to instruct the data warehouse to set the column for the partition key.

• Type: String
• Default: none

materialization > cluster_by

Define the columns that will be used for the clustering of the resulting table. This is used to instruct the data warehouse to set the columns for the clustering.

• Type: String[]
• Default: []

materialization > incremental_key

This is the column of the table that will be used for incremental updates of the table.

• Type: String
• Default: ""

materialization > incremental_predicate

An additional boolean SQL expression added to the match condition of a merge materialization. It limits the destination rows considered for a match and can reduce the number of destination partitions scanned by warehouses such as BigQuery.

The generated merge exposes two aliases:

Alias	Refers to
source	The rows returned by the asset query for the current run.
target	The existing rows in the destination table named by the asset.

The predicate may reference either alias. Provide only the expression, without a leading WHERE or AND and without a trailing semicolon.

The following BigQuery asset reads seven days from its source and restricts matching to the same seven days in the partitioned destination table:

/* @bruin

name: analytics.events
type: bq.sql

materialization:
  type: table
  strategy: merge
  partition_by: event_date
  incremental_predicate: target.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)

columns:
  - name: event_id
    type: integer
    primary_key: true
  - name: event_date
    type: date
  - name: payload
    type: string
    update_on_merge: true

@bruin */

SELECT event_id, event_date, payload
FROM raw.events
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) 

Bruin adds the predicate to the primary-key comparison in the generated merge. The relevant part is equivalent to:

MERGE analytics.events AS target
USING (
  SELECT event_id, event_date, payload
  FROM raw.events
  WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
) AS source 
ON (
  (source.event_id = target.event_id OR (source.event_id IS NULL AND target.event_id IS NULL))
  AND (target.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) 
)
WHEN MATCHED THEN
  UPDATE SET target.payload = source.payload
WHEN NOT MATCHED THEN
  INSERT (event_id, event_date, payload)
  VALUES (source.event_id, source.event_date, source.payload);

incremental_predicate does not filter the asset query. Filter the asset query separately when source processing should use the same or a narrower window.

This pattern can significantly reduce cost and execution work when the destination is large and partitioned. In one BigQuery benchmark, merging a 5,000-row source into a 1.8-million-row destination processed 1.92 GB without the predicate and 10.5 MB with the seven-day destination predicate shown above. That was a 99.45% reduction in bytes processed (a factor-of-183 reduction), with 98.91% fewer billed bytes and 93.88% less slot usage. Actual savings depend on the destination size, partitioning, and the selected window.

• Type: String
• Default: ""
• Supported platforms: BigQuery, Athena, Databricks, Doris, DuckDB, MSSQL, MySQL, Oracle, PostgreSQL, Snowflake, Synapse, and Vertica. It is not supported for Redshift (whose MERGE only accepts equality predicates in its match condition), Ingestr assets, or Python assets.

The predicate is database-specific SQL and is inserted without validation. It must include every destination row that could match the source data. If a primary key already exists outside the predicate, that row cannot match and the merge may insert a duplicate. Account for late-arriving data and the full period in which existing rows can change when choosing the destination window.

Strategies

Bruin supports various materialization strategies that take your code and convert it to another structure behind the scenes to materialize the execution results of your assets.

Default: no materialization

By default, Bruin does not apply any materialization to the assets. This means that the query will be executed every time the asset is run, and you are responsible for storing the results in a table via a CREATE TABLE or a similar statement in your SQL asset.

create+replace

This materialization strategy is useful when you want to create a table if it does not exist, and replace the contents of the table with the results of the query. This is useful when you want to ensure that the table is always up-to-date with the query results.

create+replace strategy does not do any incremental logic, which means it's a full refresh every time the asset is run. This can be expensive for large tables.

Here's an example of an asset with create+replace materialization:

/* @bruin

name: dashboard.hello_bq
type: bq.sql

materialization:
    type: table

@bruin */

select 1 as one
union all
select 2 as one

The result will be a table dashboard.hello_bq with the result of the query.

Full Refresh and full_refresh_restricted

When running assets with the --full-refresh flag, Bruin will drop and recreate tables to ensure a clean state. However, there are cases where you may want to protect certain tables from being dropped during a full refresh, such as:

• Tables with external dependencies
• Tables that take a long time to rebuild
• Critical production tables that should never be accidentally dropped

You can use the full_refresh_restricted flag to prevent an asset from being dropped during a full refresh:

/* @bruin

name: dashboard.critical_table
type: bq.sql

materialization:
    type: table

full_refresh_restricted: true

@bruin */

select * from important_data

Behavior:

• full_refresh_restricted: true - Table will NOT be dropped during full refresh. The asset will use its normal materialization strategy instead.
• full_refresh_restricted: false or not set - Table will be dropped and recreated during full refresh (default behavior).

The older asset-level refresh_restricted field is still supported as an alias.

You can also apply the same protection to every asset in an environment from .bruin.yml:

environments:
  production:
    config:
      full_refresh_restricted: true
    connections:
      # ...

This is useful when you want to run bruin run --full-refresh on your entire pipeline but protect specific critical tables from being dropped.

delete+insert

delete+insert strategy is useful for incremental updates. It deletes the rows that are no longer present in the query results and inserts the new rows. This is useful when you have a large table and you want to minimize the amount of data that needs to be written.

This strategy requires an incremental_key to be specified. This key is used to determine which rows to delete and which rows to insert.

Bruin implements delete+insert strategy in the following way:

• run the asset query, put the results in a temp table
• run a SELECT DISTINCT query on the temp table to get the unique values of the incremental_key
• run a DELETE query on the target table to delete all the rows that match the incremental_key values determined above
• run an INSERT query to insert the new rows from the temp table

Here's an example of an asset with delete+insert materialization:

/* @bruin

name: dashboard.hello_bq
type: bq.sql

materialization:
    type: table
    strategy: delete+insert
    incremental_key: UserId

@bruin */

select 1 as UserId, 'Alice' as Name
union all
select 2 as UserId, 'Bob' as Name

truncate+insert

truncate+insert strategy is useful for full table replacement when you want to clear all existing data and insert fresh data. Unlike create+replace, this strategy maintains the existing table structure (schema, permissions, indices, etc.) and only removes the data.

This strategy is more efficient than delete+insert for full table refreshes because:

• TRUNCATE is generally faster than DELETE for removing all rows
• It doesn't require an incremental_key
• It maintains table metadata and permissions

Here's an example of an asset with truncate+insert materialization:

/* @bruin

name: dashboard.daily_snapshot
type: bq.sql

materialization:
    type: table
    strategy: truncate+insert

@bruin */

select 
    current_date as snapshot_date,
    count(*) as total_users,
    sum(revenue) as total_revenue
from users

NOTE

On platforms where TRUNCATE participates in transactions (such as BigQuery), Bruin wraps the truncate and insert in a single transaction. A failed insert rolls back the truncate, and readers never observe the table in its intermediate empty state. On engines where TRUNCATE implicitly commits, the truncate and insert run as separate statements, so a failure between them can leave the table empty.

append

append strategy is useful when you want to add new rows to the table without overwriting the existing rows. This is useful when you have a table that is constantly being updated and you want to keep the history of the data.

Bruin will simply run the query, and insert the results into the destination table.

/* @bruin

name: dashboard.hello_bq
type: bq.sql

materialization:
    type: table
    strategy: append

@bruin */

select 1 as one
union all
select 2 as one

merge

merge strategy is useful when you want to merge the existing rows with the new rows. This is useful when you have a table with a primary key and you want to update the existing rows and insert the new rows, helping you avoid duplication while keeping the most up-to-date version of the data in the table incrementally.

Merge strategy requires columns to be defined and marked with primary_key and optionally update_on_merge or merge_sql:

• primary_key determines which rows to update vs insert.
• update_on_merge marks columns to update with source.col when a row matches.
• merge_sql lets you specify a custom expression per column for matches, e.g. GREATEST(target.col, source.col). When present, merge_sql takes precedence over update_on_merge.

Supported platforms for merge_sql:

• BigQuery, Snowflake, Postgres, mssql, MySQL, Spark: supported
• Athena (Iceberg tables): supported
• Databricks, ClickHouse, Trino, DuckDB, Dremio, Sail: not supported

Spark merge requires a catalog and table format that implement row-level MERGE INTO, such as Iceberg with the Spark SQL extensions enabled.

INFO

An important difference between merge and delete+insert is that merge will update the existing rows, while delete+insert will delete the existing rows and insert the new rows. This means if your source has deleted rows, merge will not delete them from the destination, whereas delete+insert will if their incremental_key matches.

Here's a sample asset with merge materialization:

/* @bruin

name: dashboard.hello_bq
type: bq.sql

materialization:
    type: table
    strategy: merge

columns:
  - name: UserId
    type: integer
    primary_key: true
  - name: UserName
    type: string
    update_on_merge: true
  - name: Score
    type: integer
    merge_sql: GREATEST(target.Score, source.Score)

@bruin */

select 1 as UserId, 'Alice' as UserName
union all
select 2 as UserId, 'Bob' as UserName

time_interval

NOTE

The time_interval strategy is only supported for SQL assets. Python assets do not support this strategy.

The time_interval strategy is designed for incrementally loading time-based data. It's useful when you want to process data within specific time windows, ensuring efficient updates of historical data while maintaining data consistency.

This strategy requires the following configuration:

• incremental_key: The column used for time-based filtering
• time_granularity: Must be either 'date' or 'timestamp'
  • Use 'date' when your incremental_key is a DATE column (e.g., '2024-03-20')
  • Use 'timestamp' when your incremental_key is a TIMESTAMP column (e.g., '2024-03-20 15:30:00')

When running assets with time_interval strategy, you can specify the time window using the start and end date flags:

bruin run --start-date "2024-03-01" --end-date "2024-03-31" path/to/your/asset

By default:

• start-date: Beginning of yesterday (00:00:00.000000)
• end-date: End of yesterday (23:59:59.999999)

Here's a sample asset with time_interval materialization:

/* @bruin
name: dashboard.hello_bq
type: bq.sql

materialization:
  type: table
  strategy: time_interval
  time_granularity: date
  incremental_key: dt

columns:
  - name: product_id
    type: INTEGER
    description: "Unique identifier for the product"
    primary_key: true
  - name: product_name
    type: VARCHAR
    description: "Name of the product"
  - name: price
    type: FLOAT
    description: "Price of the product in USD"
  - name: stock
    type: INTEGER
    description: "Number of units in stock"
  - name: dt
    type: DATE
    description: "Date when the product was last updated"
@bruin */


SELECT
    1 AS product_id,
    'Laptop' AS product_name,
    999.99 AS price,
    10 AS stock,
    DATE '2025-03-15' AS dt
UNION ALL
SELECT
    2 AS product_id,
    'Smartphone' AS product_name,
    699.99 AS price,
    50 AS stock,
    DATE '2024-03-16' AS dt;

The strategy will:

1. Begin a transaction
2. Delete existing records within the specified time interval
3. Insert new records from the query given in the asset

DDL

The DDL (Data Definition Language) strategy is used to create a new table using the information provided in the embedded YAML section of the asset. This is useful when you want to create a new table with a specific schema and structure and ensure that this table is only created once.

The DDL strategy defines the table structure via column definitions in the columns field of the asset. For this reason, you should not include any query after the embedded YAML section.

Here's an example of an asset with DDL materialization:

/* @bruin
name: dashboard.products
type: bq.sql

materialization:
  type: table
  strategy: ddl
  partition_by: product_category

columns:
  - name: product_id
    type: INTEGER
    description: "Unique identifier for the product"
    primary_key: true
  - name: product_category
    type: VARCHAR
    description: "Category of the product"
  - name: product_name
    type: VARCHAR
    description: "Name of the product"
  - name: price
    type: FLOAT
    description: "Price of the product in USD"
  - name: stock
    type: INTEGER
    description: "Number of units in stock"
@bruin */

This strategy will:

• Create a new empty table with the name dashboard.products
• Use the provided schema to define the column names, column types as well as optional primary key constraints and descriptions.

The strategy also supports partitioning and clustering for data warehouses that support these features. You can specify in the materialization definition with the following keys:

• partition_by
• cluster_by

Data Vault strategies

Bruin provides three strategies for loading a Raw Data Vault: datavault_hub, datavault_link, and datavault_satellite. They are supported for PostgreSQL and DuckDB SQL assets with type: table materialization. On any other platform, including Redshift, both incremental and --full-refresh runs fail with an unsupported strategy error, which bruin validate does not flag.

The asset query must calculate the hash keys and hashdiff and return every column declared in columns. Bruin uses those values to apply the loading rules; it does not calculate hashes. Every declared column must have both a name and a database-compatible type.

Data Vault column roles

Set a column's role with meta.datavault_role. Explicit roles are recommended, especially when an asset contains multiple hash key columns.

Column purpose	Accepted datavault_role values	Naming or configuration fallback
Hub hash key	hash_key, hub_hash_key	First column with primary_key: true, or the only column ending in _hk
Hub business key	business_key	Columns ending in _bk, in addition to the explicitly roled columns
Link hash key	link_hash_key, hash_key	First column with primary_key: true, or the only column ending in _hk
Related hash keys in a link	hub_hash_key, parent_hash_key, foreign_hash_key	Other columns ending in _hk
Satellite parent hash key	parent_hash_key, hub_hash_key, hash_key	First column with primary_key: true, or the only column ending in _hk
Satellite hashdiff	hashdiff, hash_diff	A column named hashdiff or hash_diff
Load datetime	load_datetime, load_dts	A column named load_dts, load_datetime, or loaded_at
Record source	record_source	A column named record_source

Role values and naming fallbacks are case-insensitive. For a link with several _hk columns, identify the link hash key explicitly with datavault_role: link_hash_key or primary_key: true; Bruin treats the remaining _hk columns as related hash keys.

In a hub, every column ending in _bk is added to the business keys, and in a link every column ending in _hk other than the link hash key is added to the related hash keys. Both happen in addition to the explicitly roled columns rather than instead of them, so a descriptive column carrying one of those suffixes becomes a required column. Rename such columns if you do not want them to take part in the key.

All three strategies:

• Create the schema, when the asset name has the form schema.table, and create the target table if it does not exist.
• Define every role-bearing column as NOT NULL, meaning hash keys, business keys, the satellite hashdiff, the load datetime, and the record source, and skip source rows that contain NULL in any of them. A column you declare with nullable: false also becomes NOT NULL in the table definition, but it takes no part in the skip filter, so a NULL there fails the run instead of dropping the row.
• Run the table creation and incremental load in one transaction.
• Drop and recreate the target table when the asset runs with --full-refresh, then apply the same Data Vault loading rules to the refreshed data. An asset with full_refresh_restricted: true keeps its incremental behavior and is not dropped.

On incremental runs, CREATE TABLE IF NOT EXISTS does not alter an existing target. If you change the declared columns or their types, migrate the table separately or run a full refresh.

datavault_hub

The datavault_hub strategy loads one row per hub hash key. Within the current asset query, Bruin keeps the row with the earliest load datetime for each hash key. It inserts that row only when the hash key does not already exist in the target, making repeated loads idempotent.

A hub requires:

• One hub hash key
• One or more business keys
• One load datetime
• One record source

/* @bruin
name: rdv.hub_customer
type: duckdb.sql

materialization:
  type: table
  strategy: datavault_hub

columns:
  - name: customer_hk
    type: VARCHAR
    meta:
      datavault_role: hash_key
  - name: customer_id
    type: VARCHAR
    meta:
      datavault_role: business_key
  - name: load_dts
    type: TIMESTAMP
    meta:
      datavault_role: load_datetime
  - name: record_source
    type: VARCHAR
    meta:
      datavault_role: record_source
@bruin */

SELECT customer_hk, customer_id, load_dts, record_source
FROM stg.customer_hashed

Bruin creates customer_hk as the target table's primary key.

datavault_link

The datavault_link strategy loads one row per link hash key. As with hubs, Bruin keeps the earliest row per link hash key from the current query and inserts it only when that key does not already exist in the target.

A link requires:

• One link hash key
• One or more related hub, parent, or foreign hash keys
• One load datetime
• One record source

/* @bruin
name: rdv.link_customer_order
type: duckdb.sql

materialization:
  type: table
  strategy: datavault_link

columns:
  - name: customer_order_hk
    type: VARCHAR
    meta:
      datavault_role: link_hash_key
  - name: customer_hk
    type: VARCHAR
    meta:
      datavault_role: hub_hash_key
  - name: order_hk
    type: VARCHAR
    meta:
      datavault_role: hub_hash_key
  - name: load_dts
    type: TIMESTAMP
    meta:
      datavault_role: load_datetime
  - name: record_source
    type: VARCHAR
    meta:
      datavault_role: record_source
@bruin */

SELECT customer_order_hk, customer_hk, order_hk, load_dts, record_source
FROM stg.customer_orders_hashed

Bruin creates customer_order_hk as the target table's primary key.

datavault_satellite

The datavault_satellite strategy preserves descriptive history for a parent hash key. Bruin orders incoming rows for each parent by load datetime, compares their hashdiff values with the latest target row and with the preceding row in the current batch, and inserts only new changes. Consecutive rows with an unchanged hashdiff are not inserted.

Bruin loads at most one row per target primary key, which is the parent hash key and load datetime by default. Rows that collide on that key within a batch are collapsed into one, and rows whose key already exists in the target are skipped rather than updated. Give each version of a parent a distinct load datetime so that no version is dropped.

A satellite requires:

• One parent hash key
• One hashdiff
• One load datetime
• One record source
• Any number of descriptive columns

/* @bruin
name: rdv.sat_customer_details
type: duckdb.sql

materialization:
  type: table
  strategy: datavault_satellite

columns:
  - name: customer_hk
    type: VARCHAR
    meta:
      datavault_role: parent_hash_key
  - name: hashdiff
    type: VARCHAR
    meta:
      datavault_role: hashdiff
  - name: load_dts
    type: TIMESTAMP
    meta:
      datavault_role: load_datetime
  - name: record_source
    type: VARCHAR
    meta:
      datavault_role: record_source
  - name: customer_name
    type: VARCHAR
  - name: email
    type: VARCHAR
@bruin */

SELECT customer_hk, hashdiff, load_dts, record_source, customer_name, email
FROM stg.customer_hashed

By default, Bruin creates a composite primary key from the parent hash key and load datetime. You can define a different primary key by marking its columns with primary_key: true; marking only the parent hash key keeps the default parent-hash-key and load-datetime primary key. When you define a custom primary key, set datavault_role: parent_hash_key on the parent hash key column as well, because Bruin otherwise falls back to the first primary_key: true column and can pick the wrong one.

scd2_by_column

The scd2_by_column strategy implements Slowly Changing Dimension Type 2 logic, which maintains a full history of data changes over time. This strategy is useful when you want to track changes to records and preserve the historical state of your data.

This strategy automatically detects changes in non-primary key columns and creates new versions of records when changes occur, while marking previous versions as historical.

Requirements:

• At least one column must be marked as primary_key: true
• The column names _valid_from, _valid_until, and _is_current are reserved and cannot be used in your column definitions

How it works: When changes are detected in non-primary key columns:

1. The existing record is marked as historical (_is_current: false) and gets an end timestamp in _valid_until
2. A new record is inserted with the updated values (_is_current: true) and _valid_until set to '9999-12-31'
3. Records that no longer exist in the source are marked as historical

Automatically added columns:

• _valid_from: TIMESTAMP when the record version became active (defaults to CURRENT_TIMESTAMP(), or uses incremental_key value if specified)
• _valid_until: TIMESTAMP when the record version became inactive (set to TIMESTAMP('9999-12-31') for current records, or uses incremental_key value when a record is expired due to changes)
• _is_current: BOOLEAN indicating if this is the current version of the record

_valid_from and _valid_until are created as timezone-aware timestamps so that both columns represent an unambiguous absolute instant (values are stored in UTC). The exact type depends on the platform: TIMESTAMP WITH TIME ZONE / TIMESTAMPTZ on DuckDB, MotherDuck, Postgres, Redshift, Athena and Oracle; TIMESTAMP_TZ on Snowflake; and BigQuery's / Databricks' TIMESTAMP (which is already an absolute-instant type). When the incremental_key is a timezone-naive column, its values are interpreted as UTC (not the database session timezone), so _valid_from/_valid_until are deterministic regardless of where the pipeline runs.

MySQL is the one exception: both columns are DATETIME (timezone-naive, stored in UTC), because MySQL's timezone-aware TIMESTAMP type cannot represent the far-future 9999-12-31 sentinel used for current records.

Migrating existing tables

SCD2 tables created before the timezone-aware columns were introduced are upgraded automatically on the next incremental run: the existing _valid_from/_valid_until values are converted to UTC, with no --full-refresh and no loss of history. This is a temporary migration (added July 2026) that will be removed once existing tables have been upgraded.

Optional: Using incremental_key for timestamps:

By default, _valid_from and _valid_until are set using CURRENT_TIMESTAMP(). However, if your source data has a column that indicates when changes actually occurred (e.g., an updated_at timestamp), you can specify it using the incremental_key option:

materialization:
  type: table
  strategy: scd2_by_column
  incremental_key: updated_at

When incremental_key is specified:

• _valid_from for new/updated records will be set to the value of the incremental_key column
• _valid_until for records being expired (due to changes) will be set to the value of the incremental_key column from the new record
• Records expiring because they're no longer in the source data will still use CURRENT_TIMESTAMP() for _valid_until

This is useful when you want the SCD2 timeline to reflect the actual business timestamps from your source data rather than the processing time.

NOTE:

• Unless otherwise specified by partition_by, the SCD2 table will be partitioned by _valid_from for platforms which support partitioning (BigQuery, Athena, Snowflake, Spark).
• Unless otherwise specified by cluster_by, the SCD2 table will be clustered using _is_current AND primary key(s) for platforms which support clustering (BigQuery, Snowflake, Spark).

Here's an example of an asset with scd2_by_column materialization:

/* @bruin
name: test.product_catalog
type: bq.sql

materialization:
  type: table
  strategy: scd2_by_column

columns:
  - name: ID
    type: INTEGER
    description: "Unique identifier for Product"
    primary_key: true
  - name: Name
    type: VARCHAR
    description: "Name of the Product"
  - name: Price
    type: FLOAT
    description: "Price of the Product"
@bruin */

SELECT 1 AS ID, 'Wireless Mouse' AS Name, 29.99 AS Price
UNION ALL
SELECT 2 AS ID, 'USB Cable' AS Name, 12.99 AS Price
UNION ALL
SELECT 3 AS ID, 'Keyboard' AS Name, 89.99 AS Price

Example with incremental_key:

When you want _valid_from and _valid_until to reflect actual business timestamps instead of processing time:

/* @bruin
name: test.product_catalog
type: bq.sql

materialization:
  type: table
  strategy: scd2_by_column
  incremental_key: updated_at

columns:
  - name: ID
    type: INTEGER
    description: "Unique identifier for Product"
    primary_key: true
  - name: Name
    type: VARCHAR
    description: "Name of the Product"
  - name: Price
    type: FLOAT
    description: "Price of the Product"
  - name: updated_at
    type: TIMESTAMP
    description: "When the product was last modified in the source system"
@bruin */

SELECT 1 AS ID, 'Wireless Mouse' AS Name, 29.99 AS Price, TIMESTAMP '2024-01-15 10:30:00' AS updated_at
UNION ALL
SELECT 2 AS ID, 'USB Cable' AS Name, 12.99 AS Price, TIMESTAMP '2024-01-14 14:00:00' AS updated_at

In this case, _valid_from will be set to the updated_at value from each record, preserving the actual business timeline of when changes occurred.

Example behavior:

Let's say you want to create a new table to track product catalog with SCD2. If the table doesn't exist yet, you'll need an initial run with the --full-refresh flag:

bruin run --full-refresh path/to/your/product_catalog.sql

This initial run creates:

ID | Name          | Price | _is_current | _valid_from         | _valid_until
1  | Wireless Mouse| 29.99 | true        | 2024-01-01 10:00:00| 9999-12-31 23:59:59
2  | USB Cable     | 12.99 | true        | 2024-01-01 10:00:00| 9999-12-31 23:59:59
3  | Keyboard      | 89.99 | true        | 2024-01-01 10:00:00| 9999-12-31 23:59:59

Now lets say you have new incoming data that updates Wireless Mouse price to 39.99, removes Keyboard from the catalog, and adds a new item Monitor. When you run the asset again:

bruin run path/to/your/product_catalog.sql

The table becomes:

ID | Name          | Price | _is_current | _valid_from         | _valid_until
1  | Wireless Mouse| 29.99 | false       | 2024-01-01 10:00:00| 2024-01-02 14:30:00
1  | Wireless Mouse| 39.99 | true        | 2024-01-02 14:30:00| 9999-12-31 23:59:59
2  | USB Cable     | 12.99 | true        | 2024-01-01 10:00:00| 9999-12-31 23:59:59
3  | Keyboard      | 89.99 | false       | 2024-01-01 10:00:00| 2024-01-02 14:30:00
4  | Monitor       | 199.99| true        | 2024-01-02 14:30:00| 9999-12-31 23:59:59

Notice how:

• Wireless Mouse (ID=1) now has two records: the old price (marked as historical) and the new price (current)
• USB Cable (ID=2) remains unchanged with its original record still current
• Keyboard (ID=3) is marked as historical since it's no longer in the source data
• Monitor (ID=4) is added as a new current record

scd2_by_time

The scd2_by_time strategy implements Slowly Changing Dimension Type 2 logic based on a time-based incremental key. This strategy is ideal when your source data includes timestamps or dates that indicate when records were last modified, and you want to maintain historical versions based on these time changes.

Requirements:

• At least one column must be marked as primary_key: true
• An incremental_key must be specified that references a column of type TIMESTAMP or DATE
• The column names _valid_from, _valid_until, and _is_current are reserved and cannot be used in your column definitions

How it works: The strategy tracks changes based on the time values in the incremental_key column:

1. When a record has a newer timestamp than existing records, it creates a new version
2. Previous versions are marked as historical (_is_current: false) with their _valid_until updated
3. Records no longer present in the source are marked as historical

Automatically added columns:

• _valid_from: TIMESTAMP when the record version became active (derived from the incremental_key)
• _valid_until: TIMESTAMP when the record version became inactive (set to TIMESTAMP('9999-12-31') for current records)
• _is_current: BOOLEAN indicating if this is the current version of the record

_valid_from and _valid_until are created as timezone-aware timestamps so that both columns represent an unambiguous absolute instant (values are stored in UTC). The exact type depends on the platform: TIMESTAMP WITH TIME ZONE / TIMESTAMPTZ on DuckDB, MotherDuck, Postgres, Redshift, Athena and Oracle; TIMESTAMP_TZ on Snowflake; and BigQuery's / Databricks' TIMESTAMP (which is already an absolute-instant type). When the incremental_key is a timezone-naive column, its values are interpreted as UTC (not the database session timezone), so _valid_from/_valid_until are deterministic regardless of where the pipeline runs.

MySQL is the one exception: both columns are DATETIME (timezone-naive, stored in UTC), because MySQL's timezone-aware TIMESTAMP type cannot represent the far-future 9999-12-31 sentinel used for current records.

Migrating existing tables

SCD2 tables created before the timezone-aware columns were introduced are upgraded automatically on the next incremental run — the existing _valid_from/_valid_until values are converted to UTC, with no --full-refresh and no loss of history. This is a temporary migration (added July 2026) that will be removed once existing tables have been upgraded.

Here's an example of an asset with scd2_by_time materialization:

/* @bruin
name: test.products
type: bq.sql

materialization:
  type: table
  strategy: scd2_by_time
  incremental_key: dt

columns:
  - name: product_id
    type: INTEGER
    description: "Unique identifier for the product"
    primary_key: true
  - name: product_name
    type: VARCHAR
    description: "Name of the product"
  - name: stock
    type: INTEGER
    description: "Number of units in stock"
  - name: dt
    type: DATE
    description: "Date when the product was last updated"
@bruin */

SELECT
    1 AS product_id,
    'Laptop' AS product_name,
    100 AS stock,
    DATE '2025-04-02' AS dt
UNION ALL
SELECT
    2 AS product_id,
    'Smartphone' AS product_name,
    150 AS stock,
    DATE '2025-04-02' AS dt

Example behavior:

Let's say you want to create a new table to track product inventory with SCD2 based on time. If the table doesn't exist yet, you'll need an initial run with the --full-refresh flag:

bruin run --full-refresh path/to/your/products.sql

This initial run creates:

product_id | product_name | stock | _is_current | _valid_from         | _valid_until
1          | Laptop       | 100   | true        | 2025-04-02 00:00:00| 9999-12-31 23:59:59
2          | Smartphone   | 150   | true        | 2025-04-02 00:00:00| 9999-12-31 23:59:59
3          | Headphones   | 175   | true        | 2025-04-02 00:00:00| 9999-12-31 23:59:59
4          | Monitor      | 25    | true        | 2025-04-02 00:00:00| 9999-12-31 23:59:59

Now lets say you have new incoming data with updates: Headphones stock changed from 175 to 900 with a new date (2025-06-02), Monitor is no longer available, and a new product PS5 is added. When you run the asset again:

bruin run path/to/your/products.sql

The table becomes:

product_id | product_name | stock | _is_current | _valid_from         | _valid_until
1          | Laptop       | 100   | true        | 2025-04-02 00:00:00| 9999-12-31 23:59:59
2          | Smartphone   | 150   | true        | 2025-04-02 00:00:00| 9999-12-31 23:59:59
3          | Headphones   | 175   | false       | 2025-04-02 00:00:00| 2025-06-02 00:00:00
3          | Headphones   | 900   | true        | 2025-06-02 00:00:00| 9999-12-31 23:59:59
4          | Monitor      | 25    | false       | 2025-04-02 00:00:00| 2025-06-02 00:00:00
5          | PS5          | 25    | true        | 2025-06-02 00:00:00| 9999-12-31 23:59:59

Notice how:

• Laptop (ID=1) and Smartphone (ID=2) remain unchanged with their original records still current
• Headphones (ID=3) now has two records: the old stock level (marked as historical) and the new stock level (current) based on the newer date
• Monitor (ID=4) is marked as historical since it's no longer in the source data
• PS5 (ID=5) is added as a new current record with the latest date

Key differences between scd2_by_column and scd2_by_time:

Aspect	scd2_by_column	scd2_by_time
Change Detection	Automatically detects changes in any non-primary key column	Based on time values in the incremental_key column
_valid_from Value	Set to CURRENT_TIMESTAMP() by default, or uses incremental_key value if specified	Always derived from the incremental_key column value
Use Case	When you want to track any column changes; optionally use incremental_key for business timestamps	When your source data has reliable timestamps indicating when changes happened
Configuration	Only requires primary_key columns; incremental_key is optional	Requires both primary_key columns and incremental_key

WARNING

SCD2 materializations are currently only supported for BigQuery, Snowflake, Postgres, Amazon Redshift, MySQL, DuckDB, Databricks, and Spark.