> 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/concepts/schema-evolution.md).

# Schema evolution

Learn how Nilus handles schema changes over time across batch and CDC pipelines.

Schema evolution is how a pipeline handles columns, types, nested fields, and source-side Data Definition Language (DDL) as the source system changes over time.

## How schema changes appear

| Change                                       | Batch pipelines                                                                                                                                                                                                                                                                              | CDC pipelines                                                                    |
| -------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------- |
| New nullable column on the **source object** | Usually appears on the next extraction when the connector reflects the source table or collection. Later pages in the same run can also add nullable columns on schema-flexible sources, such as MongoDB. Columns invented in a `query:` `SELECT` are not this case. See the callouts below. | Captured through the source change stream and stored with schema history.        |
| Removed column                               | May disappear from new loads, but destination cleanup depends on destination behavior and write strategy.                                                                                                                                                                                    | Treat as a compatibility event. Consumers may still see older rows or events.    |
| Type change                                  | May require `type_hints` or a destination table migration.                                                                                                                                                                                                                                   | Requires extra care because earlier and later events can carry different shapes. |
| Nested field growth                          | Controlled by `max_table_nesting`. Nested **objects** expand up to that depth. A nested **array** at that level becomes a **child table**, rather than a JSON column. See Shape Knobs → `max_table_nesting`.                                                                                 | Depends on connector payload shape and chosen CDC strategy.                      |

{% hint style="warning" %}
**Dynamic / on-the-fly columns are not schema evolution.**

Schema evolution applies to columns on the source object, such as a table, collection, or CDC payload. A column created in a `query:` `SELECT` is not eligible for additive evolution on an existing destination table. This includes computed expressions and extra projections that were not on the original table load.

Changing `source_table: schema.table` to `source_table: "query:SELECT … extra_col …"` with the same `dest_table` is not a clean evolution. The query path uses a different extract and schema contract. A destination schema ALTER can fail before rows are replaced. For example, it can report `cannot add required column: _nilus_load_id`. The extra SQL column does not land, and the destination keeps its previous shape.

Applying an unchanged `type: nilus` resource does not re-execute it. Delete and recreate the resource, or apply it under a new name, so the changed specification runs.

**Workaround:** use a new `dest_table` in the same destination schema. Alternatively, `DROP` the existing destination table and reload. Do not overlay a `query:` shape onto a table created by a table-name load.
{% endhint %}

{% hint style="warning" %}
**Table reads and `query:` write different Iceberg decimal types.**

On a DataOS Lakehouse (Iceberg) destination, table reads and `query:` extracts are not interchangeable. This applies to the shared SQL batch extract path, including PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery, Redshift, Databricks, ClickHouse, SQLite, and SAP HANA.

* A **table read** (`source_table: schema.table`) preserves source decimal precision and scale. A `DECIMAL(20,4)` or `NUMERIC(20,4)` source value lands as Iceberg `decimal(20,4)`.
* A **`query:`** extract stores decimal columns as Iceberg `decimal(38,9)`.

Switching extract styles on the same `dest_table` is not supported. Iceberg does not convert incoming `decimal(20,4)` values into an existing `decimal(38,9)` field. The load can fail at Parquet or Iceberg commit with:

`ValueError: Cannot serialize value, scale of value does not match type decimal(38, 9): 4`

The reverse conversion also fails. `type_hints: { col: decimal }` pins the logical type only. It does not set precision or scale.

**Workaround:** keep one extract style per destination table. Use a new `dest_table`, or drop the existing table and reload.
{% endhint %}

{% hint style="info" %}
**Later pages can add nullable columns in the same run.**

Batch extraction reads in pages. If later pages introduce fields that earlier pages lack, Nilus unions those columns onto the destination schema as nullable. This is common with MongoDB and other document sources. Documents without the field land as `NULL`. An existing destination table missing the column is widened on the next run.

This does not apply to columns invented by a SQL `query:`. Do not switch a `query:` onto a table-read `dest_table`, or the reverse.

A column that is all-null on every early page can be dropped from inferred schema before a later page has a value. Pin that column with `type_hints` so it remains in the schema. See Correctness Knobs.

Incompatible types still fail. The union supports new nullable columns, not type changes.
{% endhint %}

## Stable authoring patterns

* Use `type_hints` when the destination type must be pinned, or when a sparse column may be all-null on early pages.
* Keep `primary_key` stable for merge-style loads.
* Keep CDC source identity stable so offsets and schema history continue to line up.
* Use a new destination table, or `DROP` the existing table and reload, when rebuilding is easier than migration.
* For nested records, tune `max_table_nesting` only after inspecting the source payload. Arrays at that level become child tables, not JSON columns.

## CDC schema history

Nilus persists CDC schema history through the observability layer so operators can inspect the schema context tied to a pipeline. Use the schema-history endpoints when a CDC pipeline fails after source DDL, a restart, or a connector configuration change.

## Destination considerations

| Destination style         | Watch for                                                                                                                                                                                                                                                     |
| ------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Lakehouse tables          | Partition and clustering changes should be planned. Existing data files do not automatically become physically reorganized. Table reads and `query:` also write different decimal precision and scale. Do not switch extract styles on the same `dest_table`. |
| Warehouses                | Type widening is usually safer than type narrowing. Confirm destination permissions for table changes.                                                                                                                                                        |
| Document or search stores | Mapping conflicts can block writes if a field changes type. Predefine mappings for high-value indexes.                                                                                                                                                        |
| File destinations         | Appended files may contain different schemas across time unless the reader handles evolution.                                                                                                                                                                 |

## Related docs

* [Batch pipeline config](https://v2.dataos.info/references/resources/nilus/batch/pipeline-config.md)
* [Custom Query (SQL Sources)](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md)
* [CDC pipeline config](https://v2.dataos.info/references/resources/nilus/cdc/pipeline-config.md)
* [Custom Query (SQL Sources)](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md)
* [Observability API endpoints](https://v2.dataos.info/references/resources/nilus/observability/api-endpoints.md)
* [Common errors](https://v2.dataos.info/references/resources/nilus/troubleshooting/common-errors.md)
* [MongoDB](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/mongodb.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/concepts/schema-evolution.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.
