History tables
A history table keeps every version of each row a table has had, next to the table itself. The table holds the current state, and its history table lets you see what a record looked like at any point in time: for audits, debugging, or reporting on how values changed, such as a deal's stage or a customer's plan.
This is the warehouse pattern known as a slowly changing dimension (SCD).
Turn on a history table
- Click Data Sources in the sidebar, open the data source and click Configure. For a new data source, this is the Data to sync step.
- Scroll to Tables and click the cogwheel next to the table (2).
- Turn on History table (3). For a table with subtables, the option is History tables and covers the subtables too.
- Click Update Sync.
To turn it on for every table at once, click Enable all history tables above the table list (1).
The history table shows in the list under the original (4) and lands in your warehouse with __history added to its name, for example customers__history.
If the table was already syncing, Weld fills the history table from the current table straight away, so your existing data shows in history at once instead of only after each row changes again. Every change after that adds a new row.
Example
customers
| id | name | phone_number | updated_at | _weld_synced |
|---|---|---|---|---|
| 1 | Anna | 123-456-8888 | 2024-02-01 10:15:00 | 2024-02-01 11:15:00 |
The table always holds the latest version of each record.
customers__history
| id | name | phone_number | updated_at | _weld_synced |
|---|---|---|---|---|
| 1 | Anna | 123-456-7890 | 2024-01-01 09:00:00 | 2024-01-01 10:00:00 |
| 1 | Anna | 123-456-8888 | 2024-02-01 10:15:00 | 2024-02-01 11:15:00 |
The history table holds every version. The newest _weld_synced value for a record is its current version.
Good to know
- The history table has the same columns as the original table.
- Rows in history tables count toward your active rows, at 50% of a regular active row from January 1, 2027. See Monitor and optimize usage.
Query a point in time
To see each record as it was at a given moment, take the latest version synced before it:
select *
from customers__history
where _weld_synced <= timestamp '2024-01-15 00:00:00'
qualify row_number() over (partition by id order by _weld_synced desc) = 1
qualify works in BigQuery, Snowflake and Databricks. In other warehouses, wrap the row_number() in a subquery and filter on it.
Related
- Data sources (ELT): table options and sync modes.
- Capture deletes: keep rows the source deleted, rather than previous versions of them.