Build, test and finalize a transformation

A transformation is the query that loads one Dimension or Fact table from one source system. Build it in the transformation editor: five steps take it from a table and a source system to a tested, finalized load that workflows can run.

Before you start

• Deploy the target table before you test the transformation. Until it is deployed, the Validation step stays on Target table not deployed; deploy the table first and come back to the test. See Deploy, run, truncate and monitor tables.
• The source data lives in the Lake or the Warehouse. See Lake.

The transformation editor

• The editor opens on the Transformation tab and has an Execution tab beside it for running the finalized transformation.
• Transformation is a five-step wizard: General → Query → Dependencies → Mapping → Validation. The step chips mark which step you are on, which steps are complete or still pending, and which ones carry errors or unsaved changes.
• Up to three strips can appear under the tabs, in this order: an error strip for the current step, with View details (on General, step errors come as toasts instead); a warning strip, hidden on General, whose View details opens Warning Details; and a status strip that describes the state, with a button such as Navigate to Column Mapping that opens the step it names.
• The footer carries Previous, Revert, Apply and Next; on Validation the last button becomes Finish, which closes the editor. Apply saves the step and keeps you on it; Revert drops unsaved changes; Next passes the step's checks and moves on.
• Moving between steps or switching tabs with unsaved changes opens the Unsaved Changes dialog: "You have unsaved changes. How would you like to proceed?" Choose to save your changes and continue, or to drop them and continue. Leaving Query is refused unless the query is filled and validated, the same gate as Next.
• A transformation moves through these states: Testing while a test run is in progress, Pending when it waits to be tested, Invalid when a step needs attention, In Progress while steps remain before the test, and Finished once it is finalized. The status strip tells you where it stands while there is something to tell.

Create the transformation

1. In the Warehouse tree, right-click a Dimension or Fact table and choose Add Transformation; you can also click Add Transformation on the table's Transformations tab.
2. On General, choose the Table, the Source System and the Pattern, and add a Suffix only if you need a second transformation for the same table and source system. Schedule starts off; switch it on if workflows should run the transformation. Detail: Transformation general settings, Transformation patterns and load settings.
3. On Query, write the SELECT that reads the source system, and click Validate, or press Ctrl+Enter, until the badge reads Validated. AI drafts or changes the query. Detail: Write and validate a transformation query, Generate and fix SQL with AI.
4. On Dependencies, click Manage Dependencies, tick the tables your query reads, choose their source systems, and save. Detail: Transformation dependencies.
5. On Mapping, attach a source column to every target column, then save. Detail: Map transformation columns.
6. On Validation, click Test Now. In the test dialog, check the date (it starts on yesterday) and the run parameters. When the test finishes, click Show Results, review Source Table and then Next: Target Output, and click Finalize Transformation, confirming with Finalize. Detail: Run and monitor transformation tests, Review test results and finalize a transformation.
7. Open the Execution tab: Execute Now is available for a finalized transformation. Run it, or let its schedule do it, and follow the result in the Run Log, which lists production runs only; test runs stay on Validation. What a run writes is described on How Dimension and Fact runs write data.

What locks after the first save

• After the first save, Table, Source System, Pattern and Suffix are fixed, and the Name is generated from them and cannot be edited.
• Description, Schedule and the periodic and query execution settings stay editable.
• To load the same table from another source system or pattern, open a copy: Duplicate transformation > Open copy, set a new Suffix in the copy and save. A copy starts with Schedule off. Detail: Transformation general settings.
• When the Warehouse Builder creates a transformation from chat, the table, source system, pattern and name stay fixed too; its schedule arrives off until you turn it on, and its test runs write only the scratch copies, never the table. See The Warehouse Builder in chat.

What blocks a test or a finalize

• Target table not deployed. "You can't run this transformation until its target table is deployed. Deploy the table first, then run a test." The Validation step shows Target table not deployed until you deploy the table.
• A test already running. You cannot start a second test: the step reads "A test run is currently in progress…", Test Now stays disabled until the run ends, and a finalize is refused until then too.
• An unfinished test. Finalize needs a finished test: if the latest test was aborted or failed, the refusal ends "only a Finished test can be finalized." Run the test again and let it finish.
• A split group. Every transformation that writes the same table for the same source system must belong to one workflow: "All transformations writing the same table for the same source system must belong to the same workflow." Finalizing or moving part of the group is refused; move the whole group together or unfinalize the rest first.
• A run in progress on the workflow. While the transformation's workflow has a run in progress, finalize is refused and asks you to wait until it finishes.

Saving dependencies and the cycle check

• Each save on Dependencies replaces the whole list: what you mark becomes the transformation's dependency list.
• Before the step completes, the dependency graph is checked for loops. A loop refuses the save: when no detail comes back, the step reads Circular dependency detected, and the error strip's View details shows the loop it found.
• The same check refuses a finalize and switching Schedule on; switching it off is always allowed. Fix the loop on the step by removing the dependency that closes it. Detail: Transformation dependencies.

Who can do what

If your role lacks a permission, the action is refused with a message that starts "Access denied. Required permission(s):". See Warehouse permissions and row-level security.

Questions, answered

Why is the Name field locked?

The name is generated from the table, source system, pattern and suffix, so it always describes what the transformation does. To get a different name, change the suffix in a copy, or create a new transformation.

What is the difference between Apply and Next?

Apply saves the step and keeps you on it. Next passes the step's checks and moves to the next step; if the step has unsaved changes, the Unsaved Changes dialog asks whether to save them first. On Validation, Next becomes Finish, which closes the editor.

Can I edit a transformation while a test or run is in progress?

No. While a test or run is in progress, saving changes is refused with a message that starts "Test is running for transformation", finalizing waits, and deleting is refused with "Cannot delete transformation(s) [names]: a run is in progress. Abort it first." Wait for the run to finish, or abort it, then continue.

Why did my finalized transformation become a draft again?

Any edit returns a finalized transformation to a draft: saving a step, changing the query or updating the column mapping clears the finalized state, and you test and finalize it again.

What do the messages above the steps mean?

They tell you where the transformation stands. An error strip carries the current step's error, with View details; a warning strip carries warnings; the status strip describes the state and offers Navigate to ... to open the step that needs you. On General, errors come as toasts instead of the strip.

Why can't transformations of the same table and source system be split across workflows?

They fill the same table from the same source system, so they must stay in one workflow: creating, moving or finalizing while the group is split is refused, and the message asks you to move every finalized transformation of the group together, or unfinalize the rest first. Different source systems of one table may run in different workflows.