> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md).

# Custom Query (SQL Sources)

Configuration reference for Custom Query batch sources: writing a query:-prefixed SQL source, incremental-column requirements, and common pitfalls.

Custom Query sources let a batch pipeline read from the result of a SQL query instead of reading one table directly. The `query:` form works across supported SQL batch sources, not only SQL Server. Use it when the required dataset needs filtering, joins, computed columns, or a governance-friendly query surface that cannot be modeled as a simple `source_table`.

Custom queries are an advanced batch-source path. Prefer a first-party table connector or a database view when that gives the same result with simpler operational behavior.

### Requirements

Connectivity and credentials must both be in place before the pipeline can run.

#### Connectivity

* The Nilus runtime must reach the SQL source through a supported SQL connector or DataOS depot.
* The source role/user must be allowed to run the query and read every referenced table or view.
* The query should be tested directly against the source database before it is added to a Nilus manifest.

#### Query requirements

| Requirement           | Description                                                                                                          |
| --------------------- | -------------------------------------------------------------------------------------------------------------------- |
| `query:` prefix       | `source.options.source_table` must start with `query:` so Nilus treats the value as SQL rather than a table name.    |
| Stable column names   | The query result should return stable, explicitly named columns. Avoid ambiguous duplicate names from joins.         |
| Incremental column    | For incremental loads, the query must return `incremental_key` with that exact output alias.                         |
| Merge key             | A merge load requires `source.options.primary_key`. The query must return that key with the exact output alias.      |
| Source-side filtering | Nilus sends the SQL as authored. It does not add, rewrite, or optimize filters. Put joins and predicates in the SQL. |

### Source options

| Option            | Required    | Description                                                                                                                                      |
| ----------------- | ----------- | ------------------------------------------------------------------------------------------------------------------------------------------------ |
| `source_table`    | Yes         | SQL string prefixed with `query:`.                                                                                                               |
| `primary_key`     | Conditional | Column returned by the query and used as the merge key. Required for `incremental_strategy: merge`. It is not inferred from an underlying table. |
| `incremental_key` | No          | Exact output-column alias. It must be a native timestamp or numeric value. Cast timestamp strings in the SQL.                                    |
| `interval_start`  | No          | Optional lower bound passed into the extraction window.                                                                                          |
| `interval_end`    | No          | Optional upper bound passed into the extraction window.                                                                                          |
| `sql_limit`       | Unsupported | **Not supported** on `query:`. The run fails with `sql_limit is not supported for custom queries`. Put `LIMIT` or `TOP` in the SQL instead.      |
| `type_hints`      | No          | Object map of `column_name: <type>` to override inferred types when query inference is ambiguous.                                                |

### Sample Nilus configs

Each example below is self-contained and uses the current Nilus pipeline shape.

#### Append from a join query

```yaml
name: custom-query-orders
version: v1alpha
type: nilus
spec:
  type: batch
  compute: runnable-default
  source:
    address: dataos://postgressource?acl=rw
    options:
      source_table: >
        query:select
          oi.id,
          oi.order_id,
          oi.sku,
          oi.quantity,
          o.updated_at
        from order_items oi
        join orders o
          on oi.order_id = o.id
  sink:
    address: dataos://analyticslakehouse?acl=rw
    options:
      dest_table: sales.order_items_enriched  
      incremental_strategy: append
```

#### Incremental custom query

```yaml
name: custom-query-orders-incremental
version: v1alpha
type: nilus
spec:
  type: batch
  compute: runnable-default
  source:
    address: dataos://postgressource?acl=rw
    options:
      source_table: >
        query:select
          oi.id,
          oi.order_id,
          oi.sku,
          oi.quantity,
          o.updated_at
        from order_items oi
        join orders o
          on oi.order_id = o.id
        where o.updated_at > :interval_start
      incremental_key: updated_at
      primary_key: id
  sink:
    address: dataos://analyticslakehouse?acl=rw
    options:
      dest_table: sales.order_items_enriched     
      incremental_strategy: merge
```

### Keys, incrementality, and output columns

`query:` reads do **not** inherit a table's primary key.

* Set `source.options.primary_key` to a column the query returns whenever `sink.options.incremental_strategy` is `merge`.
* `incremental_key` and `primary_key` must match the query's column names exactly. They are not snake\_cased.
* `sql_limit` is rejected with `sql_limit is not supported for custom queries`. Bound the result in the SQL (`LIMIT`, `TOP`, or a `WHERE` on `:interval_start`).
* `:interval_start` and `:interval_end` are bound parameters. Write them bare in the SQL (`where o.updated_at > :interval_start`), never in quotes. This is the opposite of MongoDB, which uses the quoted string `":interval_start"`.
* With `incremental_key` set, `:interval_start` takes the progress saved by the last run. On the first run it uses `interval_start`, or `0001-01-01` if that isn't set. `:interval_end` is only filled when `interval_end` is set, so don't reference it in the query without setting `interval_end`.
* Without `incremental_key`, the placeholders take `interval_start` and `interval_end` from the config, falling back to the earliest and latest possible dates when unset.

{% hint style="warning" %}
`incremental_strategy: merge` with no `primary_key` does **not** fail. Nilus logs a warning and falls back to **append**, so every run adds rows again. Set `primary_key` on every `query:` merge.
{% endhint %}

`query:` always adds the load-metadata columns `_nilus_load_id` and `_nilus_id` to the destination tables it creates. Default table reads (pyarrow) do not. Because of that, switching an existing table-read destination to `query:` on the same `dest_table` can fail at the destination with `cannot add required column: _nilus_load_id`. Point `dest_table` at a new table, or recreate the existing one. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md) and [MS SQL Server → Keys, incrementality, and output columns](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/ms-sql-server.md).

On a DataOS Lakehouse destination this is the same for every SQL batch source. `query:` stores decimal columns as Iceberg `decimal(38,9)` instead of the source precision and scale. A table read of `DECIMAL(20,4)` or `NUMERIC(20,4)` writes `decimal(20,4)`. Mixing those styles on the same `dest_table` fails with `Cannot serialize value, scale of value does not match type decimal(38, 9)`. Keep one extract style per table. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md).

### Behavior and capabilities

* **Pipeline type** — custom queries run as `spec.type: batch` pipelines.
* **Query execution** — Nilus sends the SQL to the source as authored. Database-specific SQL syntax applies.
* **Schema inference** — Nilus infers the output schema from the query result. That inferred schema does **not** evolve onto a destination table created by a previous table-name load. Computed / on-the-fly columns (aliases, literals, expressions) are not entitled to schema evolution — point `dest_table` at a new table, or drop the existing dest and reload. See [Schema Evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md).
* **Incrementality** — incremental state only works when the query returns `incremental_key` and filters with the current interval.
* **Lakehouse decimals:** `query:` stores decimals as Iceberg `decimal(38,9)` for every SQL batch source. Table reads preserve source precision and scale. Do not switch those styles on the same `dest_table`. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md).
* **CDC support** — custom queries are not supported for CDC sources.

### Troubleshooting

| Symptom                                                                                                                                                                                       | Likely cause                                                                                                                                                                                         | Resolution                                                                                                                                                                             |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Query fails before extraction                                                                                                                                                                 | SQL syntax is invalid for the source database, or referenced objects are not visible to the source role.                                                                                             | Test the query directly in the database using the same role/user.                                                                                                                      |
| Incremental run reprocesses old rows                                                                                                                                                          | The query does not filter with `:interval_start`, or the returned `incremental_key` is not stable.                                                                                                   | Return the incremental column and add an explicit interval predicate to the query.                                                                                                     |
| Merge behaves like append                                                                                                                                                                     | `primary_key` is missing, is under `sink.options`, or does not match a query output alias.                                                                                                           | Set `source.options.primary_key` to the exact selected key alias.                                                                                                                      |
| Query validation rejects the incremental key                                                                                                                                                  | The selected alias differs from `incremental_key`, or the result is neither numeric nor a native timestamp.                                                                                          | Alias the output exactly and cast timestamp strings in the SQL.                                                                                                                        |
| `sql_limit is not supported for custom queries`                                                                                                                                               | `sql_limit` was set on a `query:` source.                                                                                                                                                            | Remove `sql_limit` and put `LIMIT` or source-specific `TOP` in the SQL query.                                                                                                          |
| Schema changes unexpectedly                                                                                                                                                                   | The query uses `SELECT *`, ambiguous aliases, or source tables changed shape.                                                                                                                        | Select explicit columns and alias computed expressions.                                                                                                                                |
| Load fails with `cannot add required column: _nilus_load_id` (or `column "_nilus_load_id" … contains null values`) after switching a table-name source to `query:` on the **same** dest table | Overlaying a `query:` extract onto a dest table that was created by a table read is not additive schema evolution. Nilus-managed columns such as `_nilus_load_id` are not reconciled as user schema. | Point `dest_table` at a new table, or drop the existing dest table and reload. See [Schema Evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md). |
| Lakehouse load fails on decimal precision or scale                                                                                                                                            | The query's inferred decimal type differs from the existing destination column.                                                                                                                      | Keep one extract style per `dest_table`. Point `dest_table` at a new table, or drop the existing table and reload.                                                                     |
| Extraction is slow                                                                                                                                                                            | Query plan is expensive or lacks indexes on join/filter columns.                                                                                                                                     | Review the source database query plan and index the incremental/filter columns.                                                                                                        |
| CDC manifest rejects the query                                                                                                                                                                | Custom queries are batch-only.                                                                                                                                                                       | Use a batch pipeline for custom queries, or use a CDC source page for log-based change capture.                                                                                        |

### Best practices

* Prefer database views when the query is stable and can be governed upstream.
* Keep query logic deterministic; avoid non-deterministic functions such as `NOW()` or `RANDOM()` in incremental predicates.
* Explicitly alias computed columns.
* Validate row counts and schema in a non-production sink before scheduling production runs.
* Keep queries narrow and bounded. Large cross-source joins should be modeled upstream or in a transformation layer.

### Related Docs

* [Batch sources](https://v2.dataos.info/references/resources/nilus/batch/batch-sources.md)
* [Custom sources](https://v2.dataos.info/references/resources/nilus/batch/custom-sources.md)
* [Batch pipeline config](https://v2.dataos.info/references/resources/nilus/batch/pipeline-config.md)
* [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md)
* [Batch sample configs](https://v2.dataos.info/references/resources/nilus/batch/sample-configs.md)


---

# 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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.
