A pattern decides how each run writes rows into a Dimension or Fact table. Use this page to pick the right pattern, set its periodic options and tune the query execution settings of a transformation.
Choose the Table first. The pattern list follows the table type, and Diff Delete and Diff Update also depend on the Source System.
| Table | Pattern | What it does |
|---|---|---|
| Dimension | Full Reload | Inserts new records, updates existing changed ones and deletes records that no longer exist. |
| Dimension | Diff Upsert | Inserts new and updates existing records based on keys. |
| Dimension | Diff Insert | Only inserts new records that don't already exist. |
| Dimension | Diff Delete | Deletes records that no longer exist in the source. |
| Dimension | Diff Update | Updates existing records where changes are detected. |
| Fact | Truncate Insert (Full) | Completely clears and reloads the entire dataset. |
| Fact | Truncate Insert (Periodic) | Clears the table periodically and inserts new data. |
| Fact | Diff Insert (Periodic) | Inserts only new records on a scheduled basis. |
| Fact | Truncate Insert (Periodic Loop) | Clears the table periodically and inserts new data within a looped daily schedule. |
Diff Delete and Diff Update appear only when the Dimension already has a transformation for the same source system.
Each pattern decides which of a run's marked rows reach the table. How Dimension and Fact runs write data maps every pattern to the rows it writes.
A Dimension table never loses rows physically. Full Reload and Diff Delete set the Delete Flag column to true instead. The pattern's own note reads: "Delete means Updating "Delete Flag" column value to true, physical Delete never happens in Dimension tables."
The pattern cannot be changed once the transformation is created. The Pattern field stays locked, and the server refuses a change.
For the Lake load patterns, which work differently, see Choose a sync type.
The three periodic patterns are Truncate Insert (Periodic), Diff Insert (Periodic) and Truncate Insert (Periodic Loop). When you pick one, their fields appear under Source System and Pattern in Configuration, and Synchronize Days is set to 1.
| Setting | What it does | Default |
|---|---|---|
| Synchronize Days | How many days of data each run synchronizes. Required for periodic patterns. Takes a whole number from 0 to 9999. | 1 |
| Full Reload When Partition Empty | When on, a source system whose target partition has no data gets a full reload on its run, instead of an incremental sync. The switch reads Full reload on empty partition when on and Incremental sync only when off. | Off |
| Loop Increment Value | The step size of the back-fill loop. Required for Truncate Insert (Periodic Loop). For the other two periodic patterns, it is required when Full Reload When Partition Empty is on, and it is used only for source systems whose partition is empty. Must be at least 1. | 1 |
| Full Reload Start Date | The earliest date a full reload goes back to. Shown for a periodic pattern that accepts it, once Full Reload When Partition Empty is on. Format YYYY-MM-DD, between 1970-01-01 and 2106-02-07. | Empty |
Leave Full Reload Start Date empty and the system finds the earliest date itself. The field then shows an auto badge. A heavy script can fail during that search, so set the date yourself in that case.
Turning off Full Reload When Partition Empty hides Full Reload Start Date, but keeps its value. It also resets Loop Increment Value to 1 for the two periodic patterns that do not use it on their own.
To set the options for one transformation:
To change these values for many transformations at once, use Batch edit.
When you execute or test a periodic transformation, the Run Parameters panel shows Synchronize Days, and Loop Increment Value when the pattern uses it.
The panel appears for periodic patterns only; for the rest it is hidden. In the panel, Synchronize Days takes a whole number of at least 1, and Loop Increment Value at least 1. Only the values you change apply to that run; the saved values on the transformation do not change. If the loop value is larger than the synchronize days for that run, the run is refused with a message that names both values, for example "Loop increment value (10) cannot be greater than synchronize days (5)."
To watch the run after it starts, see Deploy, run, truncate and monitor tables.
| Preset | What it does |
|---|---|
| Balanced | Default - balanced speed and memory. |
| Turbo | Faster - uses more RAM. |
| Eco | Saves RAM - slower. |
Balanced is the default. When the preset is not Balanced, a reset icon appears next to the presets, with the tooltip Reset to default.
The two toggles:
The History button in the dialog lists earlier versions of the settings. Restoring one shows Restored version with the version number and the reminder Save Settings to apply it; nothing is saved until you click Save Settings.
The settings text cannot be empty, and it is checked when you save. If it is empty or invalid, Save Settings shows an Invalid query settings message with the reason. The dialog stays open, and nothing is saved.
Without the permission, the action is refused with an Access denied. Required permission(s): message that names the missing permission. See Warehouse permissions and row-level security for the full list.
They appear only when the Dimension already has a transformation for the same source system. Choose the Source System first, then check that a transformation exists for it.
No. It sets the Delete Flag column to true. Physical deletes never happen in Dimension tables. How Dimension and Fact runs write data lists what each pattern writes.
Truncate Insert (Full), Truncate Insert (Periodic), Diff Insert (Periodic) and Truncate Insert (Periodic Loop).
How many days of data each run synchronizes. It defaults to 1 and is required for the three periodic patterns. In the transformation's settings it takes a whole number from 0 to 9999.
Turn on Full Reload When Partition Empty. A source system whose partition is empty then gets a full reload on its run. To limit how far back it goes, set Full Reload Start Date. Leave it empty to let the system find the earliest date.
It is required for Truncate Insert (Periodic Loop), and for the other two periodic patterns when Full Reload When Partition Empty is on. It must be at least 1, and it defaults to 1.
No. The Pattern field locks once the transformation exists, and the server refuses a change. To load the same table with another pattern, open a copy of a finalized transformation, choose the new pattern and set a new Suffix. See Build, test and finalize a transformation.
Choose the Eco preset in Query execution settings. It saves RAM at the cost of speed. Avoid Turbo, which uses more RAM.
Turn off Empty TARGET_OUTPUT tables after successful ETL run in the query settings dialog, then save and apply. Do this before the run you want to inspect, because a successful run with the toggle on empties the staging copies.
Yes. Change Synchronize Days under Run Parameters in the Execute Transformation or Test Transformation dialog. The override must be at least 1, and the saved value stays the same.
The loop value you set for that run is larger than its synchronize days. The message names both values, for example "Loop increment value (10) cannot be greater than synchronize days (5)." Lower the loop value or raise the synchronize days, then start the run again.