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, so you can use standard SQL plus DuckDB's extended syntax.

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.

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?

1

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.

2

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.

3

Reference each data set by its ID in your FROM / JOIN clauses. For example:

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.

4

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.

5

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.

6

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.

7

Add the schedule if needed, then Save and Run the importer to check the results.

Syntax it accepts

SQL based transformations accept DuckDB SQL syntax 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:

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.

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.

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 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 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.