Write and validate a transformation query

Every transformation has one query: the SELECT statement that reads your source system. Write it on the Query step of the transformation editor, validate it, then save. The step sits between General and Dependencies in the wizard; the whole flow is on Build, test and finalize a transformation.

Write the query

1. Open the transformation and go to Transformation > Query.
2. Write the SELECT that reads your source system. It should return the columns you will map to the table's columns on the Mapping step.
3. Where a value should change between runs, write a placeholder such as {current_date:DateTime} instead of a fixed value. Each placeholder appears as a parameter under the editor.
4. To draft or change the query with AI, click AI in the editor header. See Generate and fix SQL with AI.
5. To work in the full Editor, click Open in Editor. The query opens in a new console named after the transformation, and the popup minimises; see Consoles, saving and history.

Validate the query

• Click Validate in the editor header, or press Ctrl+Enter. Its tooltip reads "Validate query (Ctrl+Enter)".
• Validate runs the query with the values in the parameter chips and shows what it returns in a Query Data Preview window, so you can check the rows before you map columns and test a run.
• While the query runs, the editor is read-only and the button reads Stop; click it to cancel the check. Nothing changes when you cancel.
• A successful check turns the badge in the header to Validated. A check that returns no rows still succeeds: the window reads "No data available" and "This query executed successfully but did not return any rows."
• A failed check opens the Query Validation Error window. Read Error Details:, fix the query yourself, or click Fix with AI to have the AI correct it; the corrected query opens for review in Query Difference Checker, described on Generate and fix SQL with AI.
• Validating does not save the query. Click Apply to keep it.

Work with parameters

• A parameter is written in the SQL as {name:Type}. Type one into the query and it is listed as a chip in the Parameters strip under the editor, with its own value.
• Three parameters come ready for transformations: current_date (the date the run works on), source_system (the name of the source system this transformation reads) and, for periodic patterns, synchronize_days (how many days of data a run loads). See Transformation patterns and load settings.
• The platform parameters stay in the strip even when your query no longer uses them. A parameter you wrote yourself disappears when its placeholder leaves the query.
• To change a value, click the chip. A small window opens with a Value box; change it, then press Enter or click Save. Cancel closes the window without a change, and the chip reads empty while it has no value.
• The values are saved with the query when you save the step.
• The info icon by the Parameters heading explains: "These are optional parameters. If included in the transformation query, they make the transformation dynamic so it can run dynamically."

Copy the query

Copy in the editor header opens two actions:

Copy is unavailable while the query is empty.

Restore an earlier version

1. Click History in the editor header. It appears once the transformation has been saved; a new transformation has no versions yet, and the list reads "No query history yet".
2. In the Versions list, pick a revision. The list holds one entry per saved revision, newest first, so the newest is the highest version number, shown as v1, v2 and so on.
3. Look at the version under Query, or open Compare to see it next to your current text.
4. Click Replace. The version replaces the query in the editor and the query is marked Not validated, so validate and save again.

Save and move on

• Apply saves the query and keeps you on the step. Success shows "Query saved successfully".
• Next moves on to Dependencies. If the step has unsaved changes, the Unsaved Changes dialog asks first: Save and continue saves and moves on, Confirm drops the changes.
• Both actions are refused while the query is empty or not validated. The messages are described below.
• If the save fails, the message explains why; when nothing comes back, it reads "Failed to update Transformation".

Questions, answered

Why can't I move on from the Query step?

The step passes only when the query is filled and validated. An empty query is refused with "* Query must be filled to proceed"; a query that changed since its last check is refused with "Query not validated". The same messages appear when you click Apply or switch to another tab. Click Validate, wait for Validated, then click Next or Apply.

What does Validate do?

It runs your query and shows what it returns in a Query Data Preview window, so you can confirm the rows before mapping columns and testing. A successful run turns the header badge to Validated. The check does not save the query; click Apply to keep it.

Why did my query go back to Not validated?

The query stores the result of its last check. Any change to the query text, applying an AI suggestion or restoring an older version marks it Not validated again, and the step and its save are refused with "Query not validated" until you click Validate once more.

What are the {name:type} parameters?

A placeholder written as {name:Type}, such as {current_date:DateTime}, becomes a parameter: the query runs with the value you give it. Each one appears as a chip under the editor. Transformations come with current_date (the date the run works on), source_system (the name of the source system this transformation reads) and, for periodic patterns, synchronize_days; see Transformation patterns and load settings.

Why does a parameter stay after I remove it from my query?

current_date, source_system and synchronize_days are kept in the Parameters strip even when the query no longer uses them. A parameter you wrote yourself disappears again when you remove its placeholder from the text.

How do I change a parameter's value?

Click the chip under the editor. A small window opens with a Value box; change the value, then press Enter or click Save. The chip reads empty while it has no value, and the values are saved with the query when you save the step.

Why is the editor read-only while the query validates?

While a check runs, the editor is locked so the query cannot change under it. Wait for the result, or click Stop to cancel; nothing changes when you cancel, and the editor unlocks when the check finishes.

How do I restore an earlier version of my query?

Click History in the editor header, pick a revision in the Versions list, check it under Query or Compare, then click Replace. The version replaces the query and is marked Not validated, so validate and save again. History appears once the transformation has been saved.

What is the difference between Copy as is and Copy parameters replaced?

Copy as is copies the query exactly as written. Copy parameters replaced swaps every {name:Type} for its current value, quoting text, date and UUID values so the copied text stays valid SQL. Both live under Copy, which is unavailable while the query is empty.