Loading the documentation…
Transformations load Dimension and Fact tables. This page follows what one run writes: where its result appears, what each pattern changes, and what the loaded rows carry. To build and test a transformation, see Build, test and finalize a transformation; to run one and watch it, see Deploy, run, truncate and monitor tables.
When a test run finishes, click Show Results (see Build, test and finalize a transformation for the full test and finalize flow). The results open on Source Table, the rows your query read. Click Next: Target Output to review Data Changes, what the run would write to the table.
A production run reports on the transformation's Execution tab. The Run Log lists the run and its result, and when a run stops, the message it failed with is what you read there.
A run does not write straight into your table. It works in stages:
A test run stops after the staging copies. It writes nothing to the Dimension or Fact table, and no PII tokenisation runs.
For Full Reload, Diff Delete and the Truncate Insert patterns, the run checks its data safety guards before the final write. A run that fails a guard writes nothing.
The run marks each row as it compares the result with the table. The DML Type column carries the mark and stays empty until the pattern fills it in:
| DML Type | What it means |
|---|---|
| Insert | The row is in the result and not yet in the table for this source system. |
| Update | The row is already in the table and one of its mapped values changed. |
| Delete | The row is in the table and no longer in the result. |
| Unchanged | The row did not change. It is left alone and not written. |
For a Dimension, the Pattern decides which rows reach the table:
| Pattern | What the run writes |
|---|---|
| Full Reload | Every changed row: inserts, updates and deletes. |
| Diff Upsert | Inserts and updates. |
| Diff Insert | Inserts only. |
| Diff Update | Updates only. |
| Diff Delete | Deletes only. |
A Dimension never loses rows physically. A delete sets the Delete Flag column to true on the row instead of removing it, as Transformation patterns and load settings notes.
Facts write in snapshot-day windows. A periodic Fact run replaces the data of the days it covers, and Diff Insert (Periodic) adds only rows that are not loaded for its source system yet.
A production Fact run never writes into the table while it loads. It fills a temporary table of its own, checks it, and only then publishes it, one whole day at a time:
| Pattern | What the publish does |
|---|---|
| Truncate Insert (Periodic) and Truncate Insert (Periodic Loop) | Replaces each day of the run window for its source system with the day the run loaded. A window day the source no longer has is cleared at the end of the publish. |
| Diff Insert (Periodic) | Adds the days the run loaded. It refuses to publish when a loaded row already exists in the window with the same Source ID, so rows are never doubled. |
| Truncate Insert (Full) | Swaps the whole Fact table for the new copy at once. Such a run holds the whole table for as long as it runs, so loads of other source systems of the table, and comment or permission changes, wait for it (When a table is busy). |
What this means for queries and reports:
Production Fact runs no longer create or leave the transformation's _SOURCE_OUTPUT and _TARGET_OUTPUT work tables; test runs still use them. Work tables left by earlier runs stay until you empty them on the ClickHouse maintenance page (ClickHouse storage and maintenance) or delete the transformation. While a Fact run loads, the table's statistics show the size of its temporary table as Temp (Dimension and fact settings).
Before publishing, the run also checks that its rows belong where it would put them. Each check refuses to publish, the run ends Failed and the table keeps its data:
The messages are listed on Warehouse troubleshooting.
Before writing, a run checks its staging copy for duplicate keys. When the check fails, the run stops before writing to your table.
Fix the problem at its source: change the query or the mapping so each key comes back once.
Target rows are keyed by Source ID plus Source System ID. A Fact also keys its rows by snapshot day. Because the key includes the source system, the same Source ID loaded by two source systems stays two separate rows.
The row also carries the columns the platform maintains while loading:
A foreign-key column points at another table's row, and its value stays stable across runs and source systems.
When a periodic Fact run loads full history into an empty partition and no Full Reload Start Date is set, it searches the source for the earliest date to start from. When the search cannot find what it needs, the run stops and the message names the missing piece:
Set Full Reload Start Date so the first run does not depend on the search (Transformation patterns and load settings), or make sure the transformation's Mapping step maps the Fact's snapshot column (Build, test and finalize a transformation).
A Dimension with Store History has an extra history step in its workflow: each history run writes a new version for records that changed since the last run. See Dimension history for the versions and their windows.
Nothing in your table. A test run fills the staging copies and stops: it writes no rows to the Dimension or Fact table and runs no PII tokenisation. Use it to review Source Table and Target Output before you finalize.
On the transformation's Execution tab, where the Run Log follows the run and its result. The table's Settings tab lists the run under Transformation Runs. Test runs are not listed there.
The staging copy holds more than one row for the same Source ID, so the run cannot tell which row is the right one and stops before writing. Change the query or the mapping so each key comes back once.
Yes, per snapshot day and Source ID. Its message is Output Contains Duplicates in Source ID Per Partition. Find the duplicated key inside the day it names and fix the query or the mapping.
It marks a row whose record no longer exists in the source. A Dimension run that deletes sets it to true and keeps the row, so nothing is removed physically.
They name the runs that wrote the row. Insert Process ID names the run that first inserted it, and Update Process IDs lists every run that has updated it, growing with each update.
Source ID plus Source System ID. A Fact also keys by its snapshot day. The same Source ID loaded by two source systems stays two separate rows.
Yes. A foreign-key column builds the key from the source value, so the reference stays stable across runs and source systems.
The search did not find what it needed. The message names the missing piece: No Snapshot column found for transformation ... or No mapping found for Snapshot column in transformation .... Set Full Reload Start Date so the first run does not depend on the search, or map the Fact's snapshot column on the Mapping step.
No. Each day is published as a whole, so a day holds either all of its old rows or all of the new ones and is never missing. While a run publishes several days, a query can briefly see some days already new and others still old. See How a Fact run publishes.
The run loaded rows for days outside its window: a month or year snapshot dates its rows across the whole period, while the run publishes only the days of its Synchronize Days window. Publishing would have replaced whole days outside the window, so nothing was published. Give the transformation a window that covers the whole period.
Not by itself. A Dimension with Store History gets its history from a separate history step in its workflow, which writes new versions for records that changed. See Dimension history.