> For the complete documentation index, see [llms.txt](https://docs.coupler.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.coupler.io/functionality/data-set/combining-data/sql-based-transformations.md).

# SQL based transformations

**SQL BASED TRANSFORMATIONS** in Coupler.io terms mean writing your own SQL query to transform and combine your data sets, instead of using other transformations and source data. This is the most flexible transformation available: a single query can filter, join, aggregate, reshape, and calculate in one step.

Queries accept [**DuckDB SQL syntax**](https://duckdb.org/docs/stable/sql/introduction), so you can use standard SQL plus DuckDB's extended syntax.

{% hint style="warning" %}
**SQL based transformations are currently in early access** and not available to everyone yet. If you don't see the **SQL** option in the Transformations step, it hasn't been enabled for your account. Contact our support team if you'd like access.
{% endhint %}

### Concept

Every data set you add becomes its own table in the query, identified by its data set **ID**. You don't need to look up or type these IDs: start typing a data set's name and the editor's autocomplete lets you pick it by name. The editor then shows each data set by its name, while the query stores the underlying ID. You then write any `SELECT` statement against those tables.

**Goal**: Combine an "Orders" data set and a "Customers" data set, keep only paid orders, and return total revenue per customer country - something that would otherwise take a Join plus a Filter plus an Aggregate transformation, done here in one query.

### How to use SQL based transformations?

{% stepper %}
{% step %}
After adding the needed sources, go to the "Transformations" step and either select **SQL** from the data preview toolbar, or hit **+ Add transformation >> Custom SQL** from the left sidebar.
{% endstep %}

{% step %}
The **Custom SQL** editor opens. Click **Write SQL** and type your query. Start typing a data set's name and the editor autocompletes it to that data set's ID (used as the table name), and suggests its columns as you type.
{% endstep %}

{% step %}
Reference each data set by its ID in your `FROM` / `JOIN` clauses. For example:

```sql
SELECT
  c.country,
  SUM(o.amount) AS total_revenue
FROM s6z3w o          -- s6z3w is the ID of the Orders data set
JOIN k9m2p c          -- k9m2p is the ID of the Customers data set
  ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY total_revenue DESC
```

In the editor, each data set ID is shown as its name (the `s6z3w` above appears as **Orders**). You insert it by typing the data set's name and picking it from autocomplete - the saved query still stores the ID.
{% endstep %}

{% step %}
Click the apply button to run the query. The resulting table is previewed by Coupler.io. If the query is empty or invalid, you'll see an error - fix the query and re-apply.
{% endstep %}

{% step %}
After the SQL transformation, you can still apply other transformations (hide and reorder columns, filter, sort, add a formula column, etc.) on top of the result if needed.
{% endstep %}

{% step %}
If no further transformations are needed, proceed to the **Destinations** setup. Don't forget to select your SQL transformation result as the data to share.
{% endstep %}

{% step %}
Add the schedule if needed, then **Save and Run** the importer to check the results.
{% endstep %}
{% endstepper %}

### Syntax it accepts

SQL based transformations accept [**DuckDB SQL syntax**](https://duckdb.org/docs/stable/sql/introduction) in a standard `SELECT`-based query. In practice this means you can use:

* **Joins of every type** - `INNER`, `LEFT`, `RIGHT`, `FULL OUTER`, and `CROSS JOIN`.
* **Set operations** - `UNION`, `UNION ALL`, `INTERSECT`, `EXCEPT`.
* **Aggregation and grouping** - `GROUP BY`, `HAVING`, and grouping extensions like `ROLLUP`, `CUBE`, `GROUPING SETS`.
* **Window functions** - `ROW_NUMBER()`, `RANK()`, `SUM() OVER (...)`, running totals, and more.
* **Common Table Expressions (CTEs)** and subqueries - `WITH ... AS (...)`.
* **Conditional logic** - `CASE WHEN ... THEN ... END`.
* **Pivot** - reshape rows into columns using DuckDB's **SQL standard `PIVOT` syntax**. The simplified `PIVOT ... ON ... USING` syntax is **not** supported.
* **String, date, and math functions**, `DISTINCT`, `LIMIT`, `ORDER BY`, and casting.

Pivot must use the SQL standard syntax, for example:

```sql
SELECT *
FROM x4lzu               -- x4lzu is a data set ID
PIVOT (
    sum(population)
    FOR
        year IN (2000, 2010, 2020)
    GROUP BY country
)
```

**Referencing your data:**

* Each data set is exposed as a separate table named by its **ID**. Start typing the data set's name and the editor autocompletes it to the correct ID.
* Column names are auto-completed from each data set's schema.

{% hint style="info" %}
When you apply a query, Coupler.io runs it to detect the resulting columns, so most mistakes - an unknown table or column, an invalid function, an unsupported statement - are caught right away and the query won't be saved until you fix them. Write a `SELECT` query: SQL based transformations are meant to read and reshape your data sets, not to modify them.
{% endhint %}

### Benefits over the other transformations

The point-and-click transformations (Append, Join, Aggregate) each do one thing. SQL based transformations give you the full expressive power of SQL in a single step:

* **All join types, not just LEFT JOIN.** Coupler.io's [Join](/functionality/data-set/combining-data/join-data.md) transformation always performs a LEFT JOIN (all rows from the left set, matches from the right). With SQL you can also use `INNER`, `RIGHT`, `FULL OUTER`, and `CROSS` joins, join a table to itself, and join on complex conditions - not just equal columns.
* **Pivot.** Turn row values into columns using SQL standard `PIVOT` syntax - reshaping the standard transformations can't do.
* **Combine several transformations in one step.** Filter, join, aggregate, and calculate columns in a single query, instead of chaining multiple separate transformations.
* **Flexible combining of different structures.** [Append](/functionality/data-set/combining-data/append-data.md) requires the sources to share column names. With `UNION`/`UNION ALL` (and `SELECT` aliases) you control exactly how columns line up, and set operations like `INTERSECT` and `EXCEPT` let you compare data sets.
* **Advanced aggregation.** Beyond sum/average/count/min/max, you get `HAVING` filters on aggregates, multiple grouping levels (`ROLLUP`, `CUBE`, `GROUPING SETS`), and window functions for running totals and rankings.
* **Conditional and derived columns.** `CASE` expressions, string/date/regex functions, and math give you precise control over derived values.
* **Deduplication and ranking.** `DISTINCT`, or `ROW_NUMBER()` in a CTE, let you keep the latest or top-N rows per group.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.coupler.io/functionality/data-set/combining-data/sql-based-transformations.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.