> 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/optimize-correctness-knobs.md).

# Correctness knobs

Settings that control what data lands in the destination: incremental strategy, primary key, type hints, interval bounds, and column masking.

These settings (knobs) control what reaches the destination. Incorrect settings can result in incorrect data.

### `incremental_strategy` (sink)

| Goes under     | Default              | Valid values                 |
| -------------- | -------------------- | ---------------------------- |
| `sink.options` | required, no default | `replace`, `append`, `merge` |

How repeated runs interact with the destination table.

| Strategy  | Use it for                                                          | Behaviour                                                              |
| --------- | ------------------------------------------------------------------- | ---------------------------------------------------------------------- |
| `replace` | Small lookup tables, controlled snapshots, pre-production backfills | Drops and re-creates the destination table on every run.               |
| `append`  | Immutable / event-style data, logs, events, history, telemetry      | Adds new rows; never updates or deletes.                               |
| `merge`   | Mutable entity tables, customers, subscriptions, orders, accounts   | Upserts on `primary_key`. Requires a stable primary key in the source. |

{% hint style="warning" %}
`merge` with no usable `primary_key` does **not** fail. Nilus logs a warning and falls back to **append**, so every run adds rows again and the destination fills with duplicates. Table reads can pick up a table primary key. `query:` never does, so set `source.options.primary_key` on every custom-query merge. See [Custom query](https://v2.dataos.info/references/resources/nilus/batch/custom-sources/custom-query-sql-sources.md).
{% endhint %}

Some destinations restrict the supported set. See [Destinations](https://v2.dataos.info/references/resources/nilus/destinations.md) for per-destination strategy support. The most common surprises:

* **MongoDB destination**: supports `replace` and `merge` only, `append` is explicitly rejected.
* **Elasticsearch destination**: supports `replace` only, drop-and-recreate-index semantics.
* **AWS S3 file-system destination**: supports `replace` and `append`. `replace` requires `s3:DeleteObject` IAM permission.

#### Example: `append`

```yaml
spec:
  type: batch
  source:
    address: postgresql://user:pass@host:5432/app
    options:
      source_table: public.events
      incremental_key: event_time
  sink:
    address: dataos://lakehouse
    options:
      dest_table: raw.events
      incremental_strategy: append
```

#### Example: `merge`

```yaml
spec:
  type: batch
  source:
    address: postgresql://user:pass@host:5432/app
    options:
      source_table: public.customers
      incremental_key: updated_at
      primary_key: id
  sink:
    address: postgresql://user:pass@host:5432/warehouse
    options:
      dest_table: raw.customers
      incremental_strategy: merge
```

#### Example: `replace`

```yaml
spec:
  type: batch
  source:
    address: postgresql://user:pass@host:5432/app
    options:
      source_table: public.country_codes
  sink:
    address: dataos://lakehouse
    options:
      dest_table: raw.country_codes
      incremental_strategy: replace
```

### `incremental_key` (source)

| Goes under       | Default | Type                 |
| ---------------- | ------- | -------------------- |
| `source.options` | unset   | string (column name) |

Tells Nilus which column represents "newness" so each run can extract only the rows that have appeared or changed since the last successful run.

* Prefer `updated_at` for mutable tables.
* Prefer `created_at` or event time for append-only datasets.
* The column must be **monotonically increasing** in the source. Rows mutated without their `updated_at` being touched will be missed.
* The column should be indexed in the source database. Un-indexed scans are the most common cause of slow incremental runs.
* Recommended type: `timestamp`, `datetime`, `date`, or a sequence-backed integer.
* Write the **source** column name (`LastModifiedDate`), not the snake\_cased destination name.
* Some SaaS sources **reject** `incremental_key` and manage incrementality themselves. Stripe uses `:sync:incremental` on `created` only. Status, refund, and dispute updates on older charges are not picked up. See [Stripe](https://v2.dataos.info/references/resources/nilus/batch/batch-sources/stripe.md).

### `primary_key` (source)

| Goes under       | Default                      | Type                 |
| ---------------- | ---------------------------- | -------------------- |
| `source.options` | unset (required for `merge`) | string (column name) |

Identifies the logical row. Used by `incremental_strategy: merge` to decide whether an inbound row is an insert or an update.

* Start with one stable business key, typically `id`. Write the **source** column name (`OrderId`), not snake\_case.
* Avoid composite keys unless the source model genuinely requires it.
* Table reads can pick up a table primary key during schema reflection. `query:` cannot, so set `primary_key` explicitly.
* If the source has no reliable row identifier, do not use `merge`. Omitting the key does not fail the run. It falls back to append.

### `type_hints` (source)

| Goes under       | Default  | Type                               |
| ---------------- | -------- | ---------------------------------- |
| `source.options` | inferred | object map (`column_name: <type>`) |

Overrides Nilus' automatic type inference when the source's declared type is ambiguous or operationally important. Each entry is `column_name: <type>`. Supported types:

`text`, `bigint`, `bool`, `timestamp`, `date`, `decimal`, `double`, `binary`, `json`, `time`.

```yaml
spec:
  source:
    address: postgresql://user:pass@host:5432/app
    options:
      source_table: public.users
      incremental_key: updated_at
      type_hints:
        created_at: timestamp
        updated_at: timestamp
        signup_date: date
        amount: decimal
```

Hint **ambiguous** columns and **sparse** columns that can be all-null on early extraction pages. Hinting columns the source already declares correctly, and that always have values, adds no value and increases the chance of a future schema-drift mismatch.

A sparse column, such as a `deleted_utc` timestamp that is null on every early page, can be dropped from the inferred schema before a later page has a value. Pin it:

```yaml
      type_hints:
        deleted_utc: timestamp
```

That keeps the column in the destination even when this run never sees a non-null. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md).

`decimal` pins the logical type only. It does not set Iceberg precision or scale. A `query:` extract from any SQL batch source into a DataOS Lakehouse still lands decimals as `decimal(38,9)`. Use a table read if you need the source precision and scale. Do not switch extract styles on the same `dest_table`. See [Schema evolution](https://v2.dataos.info/references/resources/nilus/concepts/schema-evolution.md).

### `interval_start` / `interval_end` (source)

| Goes under       | Default | Type            |
| ---------------- | ------- | --------------- |
| `source.options` | unset   | ISO-8601 string |

Bounded extraction window. Both bounds are optional and independent. Set one or both. Useful for one-off backfills, replays, or windowed catch-up runs.

```yaml
spec:
  source:
    address: postgresql://user:pass@host:5432/app
    options:
      source_table: public.orders
      incremental_key: updated_at
      interval_start: "2024-01-01T00:00:00Z"
      interval_end:   "2024-04-01T00:00:00Z"
```

When set in addition to `incremental_key`, the incremental cursor is constrained to `[interval_start, interval_end]`. Without `incremental_key`, these bounds have no effect.

### `mask` (source)

| Goes under       | Default | Type                                          |
| ---------------- | ------- | --------------------------------------------- |
| `source.options` | unset   | object map (`column_name: algorithm[:param]`) |

Column-level data masking applied during extraction. Use it to hash or redact PII before the data hits the sink.

```yaml
spec:
  source:
    address: postgresql://user:pass@host:5432/app
    options:
      source_table: public.users
      mask:
        email: hash
        phone: "partial:3"
        ssn: redact
```

Supported algorithms include `hash`, `partial:<n>`, and `redact`. The full list is enumerated in the per-connector pages where masking has connector-specific implications.

### `SCHEMA_NAMING` (env)

| Goes under                                              | Default   | Valid values        |
| ------------------------------------------------------- | --------- | ------------------- |
| Runtime env (`spec.use.projection.projections.envVars`) | `default` | `default`, `direct` |

Controls whether Nilus **normalizes** destination table and column identifiers, or **preserves** the source names. Nilus reads this from the `SCHEMA_NAMING` environment variable only. It is **not** a `source.options` key. The domain schema rejects unknown source options, so `schema_naming:` under `source.options` fails apply.

| Value     | Behaviour                                                         | Example                                              |
| --------- | ----------------------------------------------------------------- | ---------------------------------------------------- |
| `default` | Apply Nilus naming conventions (snake\_case, identifier cleanup). | `pkey` → `p_key`, `CustomerID` → `customer_id`       |
| `direct`  | Keep source table and column names as they appear in the source.  | `pkey` stays `pkey`, `CustomerID` stays `CustomerID` |

Use `direct` when downstream queries, reports, or applications depend on the original names. Leave the default when you want a consistent, SQL-friendly identifier style.

```yaml
name: orders-preserve-names
version: v1alpha
type: nilus
spec:
  type: batch
  compute: runnable-default
  use:
    projection:
      projections:
        envVars:
          - key: SCHEMA_NAMING
            template: "direct"
  source:
    address: dataos://mssqlsource?purpose=rw
    options:
      source_table: dbo.Orders
  sink:
    address: dataos://lakehouse?purpose=rw
    options:
      dest_table: raw.Orders
      incremental_strategy: replace
```

{% hint style="info" %}
`direct` does not rewrite identifiers that the destination itself rejects (reserved words, illegal characters). Those still fail at load time. Fix the source name or keep `default` so Nilus can sanitise them.
{% endhint %}


---

# 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/optimize-correctness-knobs.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.
