> 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/pipeline-optimization/optimize-sink-datasets.md).

# Optimize sink datasets

Full knob taxonomy for sink tuning (correctness, shape, throughput, sampling), recommended config profiles, a sizing playbook, and common anti-patterns.

This guide is the customer-facing reference for shaping the **destination dataset** so it lands cleanly and stays cheap to query later. It covers the two knob families that decide what the destination looks like: **Correctness** (which rows land, and how they are reconciled) and **Shape** (how the dataset is physically laid out).

For knobs that govern how *fast and how cheaply the run completes*, including file sizing, workers, parallelism, and sampling, see [Pipeline optimization](https://v2.dataos.info/references/resources/nilus/pipeline-optimization.md). [Optimizing for time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md) covers throughput and validation-sampling knobs. [Optimizing for resource](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md) covers memory/CPU and width-sampling knobs. The hub holds the recommended tuning profiles.

This guide does **not** cover destination-native maintenance. This includes Iceberg compaction, Snowflake auto-clustering jobs, BigQuery materialized view refreshes, and Redshift `VACUUM`. Those are scheduled by the destination owner, not by the Nilus pipeline.

### When NOT to optimize

For most pipelines, the defaults are correct. **Tune only when you see a concrete symptom**, not pre-emptively:

* Repeated runs duplicate rows or fail to refresh → look at `incremental_strategy`, `incremental_key`, `primary_key` ([Correctness knobs](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-correctness-knobs.md)).
* Destination table or column names do not match the source (`pkey` became `p_key`) → look at `SCHEMA_NAMING` (Correctness Knobs).
* Destination is hard to query / scans too much → look at `partition_by`, `cluster_by`, `max_table_nesting` ([Shape knobs](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-shape-knobs.md)).
* Run is slow, runs out of memory, or produces too many small files → that is a throughput/resource concern; see [Pipeline optimization](https://v2.dataos.info/references/resources/nilus/pipeline-optimization.md).

If the destination is queryable, the data is correct, and your bills are reasonable, leave the configuration alone.

### The knob taxonomy

Nilus exposes 20-plus options across `source.options`, `sink.options`, and pipeline runtime environment variables. They group into four jobs:

| Category        | Job                                                                   | Knobs                                                                                                                                                               | Documented in                                                                                                                                                                                                                                         |
| --------------- | --------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **Correctness** | Get the right rows into the destination                               | `incremental_strategy`, `incremental_key`, `primary_key`, `type_hints`, `interval_start`, `interval_end`, `mask`, `SCHEMA_NAMING`                                   | This guide                                                                                                                                                                                                                                            |
| **Shape**       | Decide how the destination dataset is physically laid out             | `partition_by`, `cluster_by`, `max_table_nesting`                                                                                                                   | This guide                                                                                                                                                                                                                                            |
| **Throughput**  | Decide how fast and how cheaply the run completes                     | `loader_file_format`, `loader_file_size`, `page_size`, `extract_parallelism`, `staging_bucket`, `LOAD__WORKERS`, `NORMALIZE__WORKERS`, `LOAD__PARALLELISM_STRATEGY` | [Optimizing for time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md) / [Optimizing for resource](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md) |
| **Sampling**    | Limit extraction volume for testing, validation, or partial backfills | `sql_limit`, `yield_limit`, `sql_exclude_columns`, `sql_reflection_level`                                                                                           | [Optimizing for time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md) / [Optimizing for resource](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md) |

This guide documents the **Correctness** and **Shape** knobs in detail. **Throughput** and **Sampling** are covered by the [Optimizing for time](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-time.md) and [Optimizing for resource](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md) guides. Every knob is documented with its placement (`source.options` / `sink.options` / env var), default, valid values, and the symptom it solves.

{% hint style="warning" %}
**Stack-schema strictness**

`sink.options` is **`additionalProperties: false`** in the Nilus stack schema. Any key that is not in the documented sink-options list is rejected at YAML validation. Do not pass arbitrary low-level loader options through `sink.options`. If you need a new knob, request it.

`source.options` is also **`additionalProperties: false`**. Options placed under the wrong section, such as `incremental_strategy` under `source.options`, fail YAML validation. Extra keys such as `schema_naming` also fail validation. Project runtime-only flags as environment variables, such as `SCHEMA_NAMING`.
{% endhint %}

### Detailed knob reference

Each knob is documented with its placement (`source.options` / `sink.options` / env var), default, valid values, and the symptom it solves.

* [Correctness knobs](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-correctness-knobs.md): get the right rows into the destination: `incremental_strategy`, `incremental_key`, `primary_key`, `type_hints`, `interval_start` / `interval_end`, `mask`, `SCHEMA_NAMING`.
* [Shape knobs](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-shape-knobs.md): lay out the destination dataset: `partition_by`, `cluster_by`, `max_table_nesting`.
* [Tuning large Lakehouse (Iceberg) loads](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-lakehouse-iceberg-loads.md): wide/narrow file sizing and benchmark-backed configs for Iceberg destinations.

{% hint style="info" %}
For the **Throughput** knobs (`loader_file_size`, `page_size`, `extract_parallelism`, `LOAD__WORKERS`, …) and **Sampling** knobs, plus the recommended tuning profiles and the step-by-step sizing playbook, see [Pipeline optimization](https://v2.dataos.info/references/resources/nilus/pipeline-optimization.md).
{% endhint %}

### Anti-patterns

* **Using `merge` without a usable `primary_key`**: the run does not fail. Nilus warns and falls back to append, which duplicates rows. `query:` never infers a table primary key.
* **Putting optimization options under the wrong section**: for example, `incremental_strategy` under `source.options`. The stack schema rejects unknown keys in both `sink.options` and `source.options` because `additionalProperties` is `false`.
* **Adding multiple `partition_by` rules pre-emptively**: partitions cost write-side performance for no read-side benefit until consumers actually filter on them.
* **Raising `max_table_nesting` on nested payloads without inspecting arrays**: the default `"0"` keeps nested objects as `json`. Values `"1"` and `"2"` flatten objects and can split arrays into extra child tables. See Shape knobs.
* **Switching a table read to `query:` (or the reverse) on the same Lakehouse `dest_table`**: the two paths write different decimal precision and scale values, plus different `_nilus_*` columns. Iceberg commits can fail with a scale serialization error or `cannot add required column: _nilus_load_id`. Use a new `dest_table`. See Schema evolution.

### Related Docs

* [Pipeline optimization](https://v2.dataos.info/references/resources/nilus/pipeline-optimization.md): run speed/cost, tuning profiles, and the sizing playbook.
* [Tuning large Lakehouse (Iceberg) loads](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-lakehouse-iceberg-loads.md): wide/narrow file sizing and benchmark-backed configs for Iceberg destinations.
* [Batch pipeline config](https://v2.dataos.info/references/resources/nilus/batch/pipeline-config.md), [CDC pipeline config](https://v2.dataos.info/references/resources/nilus/cdc/pipeline-config.md), and [Stream pipeline config](https://v2.dataos.info/references/resources/nilus/stream/pipeline-config.md): full pipeline references.
* [Batch sample configs](https://v2.dataos.info/references/resources/nilus/batch/sample-configs.md), [CDC sample configs](https://v2.dataos.info/references/resources/nilus/cdc/sample-configs.md), and [Stream sample configs](https://v2.dataos.info/references/resources/nilus/stream/sample-configs.md): copy-pasteable starting points.
* [PostgreSQL partitioned tables for CDC](https://v2.dataos.info/references/resources/nilus/troubleshooting/postgresql-cdc-partitioned-tables.md): setup for PostgreSQL partitioned CDC sources.
* [Sources overview](https://v2.dataos.info/references/resources/nilus/sources.md) and [Destinations](https://v2.dataos.info/references/resources/nilus/destinations.md): per-connector pages with troubleshooting and incremental-strategy support tables.


---

# 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/pipeline-optimization/optimize-sink-datasets.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.
