---
title: "Query-based transforms"
description: "Create Metabase questions and SQL queries to transform your data and write the results back into your data warehouse."
url: "https://www.metabase.com/docs/latest/data-modeling/transforms/query"
version: "65"
---

> For the complete documentation index, see [llms.txt](https://www.metabase.com/docs/llms.txt).

# Query-based transforms

> On Metabase Cloud, you need the [**Basic transforms** add-on](https://www.metabase.com/docs/latest/data-modeling/transforms/addons.md) to run query-based transforms.

With query-based transforms, you can write a query in SQL or Metabase's query builder, and then write the results of the query back into the database on schedule.

![Query transform](https://www.metabase.com/docs/latest/data-modeling/images/sql-transform.png)

For general information about Metabase transforms, see [Transforms](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md).

## How query-based transforms work

- In Metabase, you create a `SELECT` query either using SQL or Metabase's [graphical query builder](https://www.metabase.com/docs/latest/questions/query-builder/editor.md).
- When the transform first runs, your _database_ executes the transform's query.
- Your database writes the results of the query to a new table.
- The new table is synced to Metabase.
- On subsequent transform runs, your database will overwrite that table with the updated results unless you [configure your transform to be incremental](#incremental-query-transforms).

## Create a query-based transform

On Metabase Cloud, you need the [**Basic transforms** add-on](https://www.metabase.com/docs/latest/data-modeling/transforms/addons.md) to create query-based transforms.

1. [Enable transforms](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#enable-transforms).
2. Go to **Data Studio > Data transformation**.

3. Click **+ New** and pick **Query builder**, **SQL query**, or **Copy of a saved question**.

   Currently, you can't convert between different transform types (like converting a query builder transform to a SQL-based transform, or a SQL transform into a Python transform). If you want to change your transform built with the query builder into a SQL transform, you'll need to create a new transform with the same target and tags, and delete the old transform.

4. Write your transform query as you would normally write a query in Metabase. See [Query builder](https://www.metabase.com/docs/latest/questions/query-builder/editor.md) and [SQL editor](https://www.metabase.com/docs/latest/questions/native-editor/writing-sql.md) documentation for more information.

   Not all databases support transforms, see [Databases that support transforms](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#databases-that-support-transforms).

5. To test your transform, press the **Run** button at the bottom of the editor.

   Previewing a query transform in the editor will _not_ write the result of the transform back to the database.

6. Click **Save** in the top right corner and fill out the transform information:

   - **Name** (required): The name of the transform.
   - **Schema** (required): Target schema for your transform. This schema can be different from the schema of the source table(s). You create a new schema by typing its name in this field. You can only transform data _within_ a database; you can't write from one database to another.
   - **Table name** (required): Name of the target table. Metabase will write the results of the transform into this table, and then sync the table in Metabase.
   - **Folder** (optional): The folder where the transform should live. Click on the field to pick a different folder or create a new one.
   - **Incremental transformation** (optional): see [Incremental query transforms](#incremental-query-transforms)

7. Optionally, assign tags to your transforms. Tags are used by [jobs](https://www.metabase.com/docs/latest/data-modeling/transforms/jobs-and-runs.md) to run transforms on schedule.

## Variables in SQL transforms

SQL transforms support [variables](https://www.metabase.com/docs/latest/questions/native-editor/sql-parameters.md) (`{{my_variable}} `), which are only useful when combined with [snippets](https://www.metabase.com/docs/latest/questions/native-editor/snippets.md).

For (a really simple) example, let's say you want to count rows per week across several tables. You could create a snippet called "rows per week" containing the full query with a `{{table}}` variable:

```sql

SELECT
  date_trunc('week', created_at) AS week,
  count(*) AS row_count
FROM {{table}}
GROUP BY week
ORDER BY week

```

That way you can use the snippet to create multiple transforms, each sourced from different tables. Each transform's entire SQL is just:

```sql
{{snippet: rows per week}}
```

In each transform's variable panel, set the default value of `table` to the target table (e.g., `orders`, `returns`, `subscriptions`). See [table variables](https://www.metabase.com/docs/latest/questions/native-editor/table-variables.md).

If you ever need to change the query (say, to switch from weekly to daily granularity), you update the snippet once and every transform picks up the change on its next run.

### Transform variable must be optional, or have a default value

Parameters in transforms must either:

- Wrap the variable in optional blocks (`[[ ]]`).
- Supply a default value.

The reason transform variables must have a default value (or be optional) is that transforms run on a schedule, so there's no way to pass a value to the variable when the job runs the transform.

## Run a query transform

See [Run a transform](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#run-a-transform). You'll see logs for a transform run on the transform's page.

## Incremental query transforms

By default, on every transform run after the first one, Metabase will process all the data in all input tables, then drop the existing target table, and create a new table with the processed data. You can tell Metabase to only process **new** data by marking your transform as incremental.

### Prerequisites for incremental transforms

Your data has to have certain structure for incremental transforms to work. See [Prerequisites for incremental transforms](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#prerequisites-for-incremental-transforms).

### How incremental query transforms work

For a transform to run incrementally, you'll need to pick a column ("checkpoint") that Metabase needs to check for new values. Then, behind the scenes, Metabase will add a filter around your transform query that will filter the results of the query for values greater than the last written checkpoint value.

If you set a [merge key](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#add-merge-keys-to-upsert-rows), Metabase updates the target rows that match the key instead of adding new ones.

### Make a query transform incremental

#### Use table variables for incremental SQL transforms

If you built your transform in the graphical query builder, you can skip right to [marking the transform as incremental](#mark-transform-as-incremental).

If you are writing your incremental transform in raw SQL, you'll need to add a [table variable](https://www.metabase.com/docs/latest/questions/native-editor/table-variables.md) into your SQL code, with the table variable replacing the table with the checkpoint column.

For example, let's say you have a transform that retrieves order id, total and product title:

```sql
SELECT
  orders.id,
  orders.total,
  products.title
FROM
   orders JOIN products on orders.product_id = products.id
```

To make this transform incrementally load the data based on new values of `orders.id` column, you need to:

1. Add a table variable, for example `{{orders_var}}` replacing `orders` in the `FROM` statement;
2. In the table variable settings, connect the table variable to the `orders` table;
3. Replace other references to the table in your query with either:
   - The name of the table variable (if you have "Emit table alias" toggled on in variable's setting).
   - Your own handcrafted alias for the variable.

So your query will look like this:

```sql

SELECT
  orders_var.id,
  orders_var.total,
  products.title
FROM
   {{orders_var}} JOIN products on orders_var.product_id = products.id

```

In this query,`orders_var` is connected to the `orders` table in variable settings, and "Emit table aliases" toggled on in the variables sidebar.

Once your query has table aliases, you can mark the transform as incremental using the `orders.id` column in the transform's settings.

#### Mark transform as incremental

To make a query transform incremental:

1. Go to the transform's page in **Data Studio > Data transformation**.
2. Switch to **Settings** tab.
3. In **Field to check for new values**, select the field in one of the source tables that Metabase should scan to determine which records are new or changed. Only some fields are eligible. See [prerequisites for incremental transforms](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#prerequisites-for-incremental-transforms).
4. (Optional) To catch late-arriving rows, [set a lookback window](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#catch-late-arriving-rows-with-a-lookback-window).
5. (Optional) To update matching rows instead of appending new ones, [add a merge key](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#add-merge-keys-to-upsert-rows).

## Convert models to transforms

> Converting models to transforms is only available on [Pro](https://www.metabase.com/product/pro) and [Enterprise](https://www.metabase.com/product/enterprise) plans (both self-hosted and on Metabase Cloud).

If you have models you'd like to migrate to transforms, Metabase can convert them for you. When you convert a model, Metabase:

1. Creates a new transform based on the model's query.
2. Runs the transform to create the output table in your database.
3. Replaces every question, dashboard, and other item that used the model with the transform's output table.
4. Converts the original model to a question.

You must be an admin to convert models, and the model's database must [support transforms](https://www.metabase.com/docs/latest/data-modeling/transforms/transforms-overview.md#databases-that-support-transforms).

To convert a model into a transform:

1. Review [Replacing data sources](https://www.metabase.com/docs/latest/data-modeling/tools/replace-data-sources.md) docs for overview and limitations of the process.
2. Go to **Data Studio > Data transformation**.
3. Click **Tools > Migrate models**.
4. Find the model you want to convert and click it to open its details panel. The panel shows the model's name, database, and collection, and a list of items that depend on it.
5. Click **Convert to a transform**.
6. Fill out the transform settings. See [Create a transform](#create-a-query-based-transform) for the overview of settings.
7. Click **Convert to a transform**.

Metabase runs the conversion in the background. A status indicator at the bottom of the screen shows progress.

If the transform run fails, Metabase stops and leaves the model unchanged—nothing is replaced.

If the transform runs successfully, but the source swap fails afterward, the transform and its output table are kept. The model is left unchanged. You can complete the migration manually using [Replace data sources](https://www.metabase.com/docs/latest/data-modeling/tools/replace-data-sources.md) to point the remaining content from the model to the transform's output table.

Once conversion completes, all content that previously queried the model now queries the transform's output table. The original model just becomes a question.

Newly created tables will be created with default permissions and will _not_ inherit the model's permissions. This includes [row and column security](https://www.metabase.com/docs/latest/permissions/row-and-column-security.md): if the model's tables have row and column security policies, you'll need to set up new policies on the transform's output table. As an alternative, consider manually creating and running the transform first, setting up the permissions, then using [Replace data sources](https://www.metabase.com/docs/latest/data-modeling/tools/replace-data-sources.md) to swap the model for the transform's output table once you configured permissions.
