Custom SQL Queries

Custom SQL queries let you define free-form SELECT statements that produce virtual tables in the target. Unlike structured data filters (which modify how existing tables are read), queries create entirely new result sets from arbitrary SQL — including JOINs, CTEs, aggregations, and expressions.

Available in Load mode only. For cross-source SQL, use Federated Queries.

How queries work

Each query entry in the queries array defines:

• name — the virtual table name created in the target
• query — a full SELECT statement executed against the source

The backend executes queries via DuckDB, writing the result set to the target under the given name.

Example

{
  "name": "users-transform",
  "mode": "load",
  "source": {
    "connections": [
      {
        "connectionId": "conn_SOURCE_ID",
        "database": "app_db",
        "queries": [
          {
            "name": "active_customers",
            "query": "SELECT id, UPPER(full_name) AS full_name, LOWER(email) AS email FROM customers WHERE active = true"
          }
        ]
      }
    ]
  },
  "target": {
    "id": "conn_TARGET_ID",
    "spec": {
      "db": { "database": "warehouse" }
    }
  }
}

This creates a active_customers table in the target warehouse database with the filtered and transformed result.

Practical rules

• Use SELECT statements only.
• The name field becomes the target table name — keep it stable across runs.
• Return only the columns the target needs.
• If the target depends on primary keys, include them in the result.
• Keep column aliases stable so the resulting schema is predictable.
• Validate the query in the SQL Console before running a full stream.

Mixing tables and queries in one stream

A single database connection can include both tables and queries in the same stream. The backend runs this as a mixed stream:

1. Tables are streamed in parallel using native database readers.
2. Custom queries are executed sequentially via DuckDB.
3. Results from both phases are aggregated into the target.

This lets you combine straightforward table copies with transformed query extracts in a single Load run.

{
  "mode": "load",
  "source": {
    "connections": [
      {
        "connectionId": "conn_PG_ID",
        "database": "app_db",
        "tables": [
          { "name": "orders" },
          { "name": "products" }
        ],
        "queries": [
          {
            "name": "active_customers",
            "query": "SELECT id, email, plan FROM customers WHERE active = true"
          }
        ]
      }
    ]
  }
}

In multi-source streams, each connection defines its own tables and queries independently. You can also mix connection types — for example, a PostgreSQL connection with tables + queries alongside an S3 connection with file selections.

Queries vs data filters

For simple column selection and row filtering without writing SQL, use Data Filters.

Validation checklist

1. Verify the source connection in Data Explorer.
2. Run the query in the SQL Console.
3. Confirm the column names and types match the target expectation.
4. Start with a limited run (limits.numberOfEvents) before applying broadly.
5. After the first run, verify the stream reached finished and the target table has the expected rows.

Related docs

• Data Filters
• Schema Selection Guide
• Federated Queries
• Stream Configuration Guide