> 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-shape-knobs.md).

# Shape knobs

Settings that control the physical layout of the destination dataset: partitioning, clustering, and nested-field flattening depth.

These knobs decide *how* the destination dataset is laid out. They affect downstream query performance, not pipeline runtime.

## `partition_by` (sink)

| Goes under     | Default | Type                       |
| -------------- | ------- | -------------------------- |
| `sink.options` | unset   | array of partition objects |

Physical partitioning for query pruning. Most useful on lakehouse-style destinations (Iceberg, DataOS Lakehouse) where consumers filter by date/region/customer.

Each partition object accepts:

| Field          | Required    | Description                                                                    |
| -------------- | ----------- | ------------------------------------------------------------------------------ |
| `column`       | Yes         | Source column to derive the partition from.                                    |
| `type`         | Yes         | One of `year`, `month`, `day`, `hour`, `bucket`, `identity`.                   |
| `name`         | Yes         | Display name for the partition (used in the destination's metadata catalogue). |
| `index`        | Yes         | Integer position (1-based) for ordering when multiple partitions are defined.  |
| `bucket_count` | Conditional | Required when `type: bucket`; ignored otherwise.                               |

### Example: single date rule

```yaml
spec:
  sink:
    address: dataos://lakehouse?purpose=rw
    options:
      dest_table: analytics.transactions_raw
      incremental_strategy: append
      partition_by:
        - column: transaction_date
          type: day
          name: transaction_day
          index: 1
```

### Example: date + bucket rule

```yaml
spec:
  sink:
    address: dataos://lakehouse?purpose=rw
    options:
      dest_table: analytics.orders_raw
      incremental_strategy: append
      partition_by:
        - column: order_date
          type: year
          name: order_year
          index: 1
        - column: country
          type: bucket
          name: country_bucket
          bucket_count: 16
          index: 2
```

### Anti-patterns

* **Partition explosion.** `type: identity` on a high-cardinality column (e.g. `user_id` on a 10M-user table) creates 10M partitions and breaks the destination's metadata catalog. Use `type: bucket` with a sensible `bucket_count` instead.
* **Mismatched cardinality between partition and `loader_file_size`.** A `type: hour` partition on a low-volume table produces thousands of tiny files (one per hour, per loader-file split). Pick a coarser partition (`day` or `month`) or accept that small files are the norm for low-volume sources.
* **Adding partitions before confirming the access pattern.** Add a partition only when consumers repeatedly filter by that column. Otherwise, the partition adds write-side cost with no read-side benefit.

## `cluster_by` (sink)

| Goes under     | Default | Type             |
| -------------- | ------- | ---------------- |
| `sink.options` | unset   | array of strings |

Secondary physical organization. Different destinations interpret this differently:

* **Snowflake**: maps to `CLUSTER BY (...)` on the destination table. Snowflake auto-clusters in the background. No DDL action is required. The auto-clustering service uses warehouse credits.
* **BigQuery**: maps to clustered-table DDL when the table is created. BigQuery does **not** re-cluster existing tables in place. Re-clustering after the fact requires a `CREATE TABLE AS SELECT` rebuild.
* **Databricks**: maps to `CLUSTER BY (...)` when supported by the table format (Delta v3+ liquid clustering).
* **Other warehouses (Redshift / Synapse / MS SQL Server)**: typically no-op or destination-specific behavior. Check the destination connector page.

Start with one frequently-filtered or join column. Use a second column only if the destination and workload clearly justify it.

```yaml
spec:
  sink:
    address: snowflake://user:pass@account/db?warehouse=COMPUTE_WH&role=ANALYST
    options:
      dest_table: raw.page_views
      incremental_strategy: append
      cluster_by:
        - account_id
```

## `max_table_nesting` (source)

| Goes under       | Default | Type                             |
| ---------------- | ------- | -------------------------------- |
| `source.options` | `"0"`   | **string** (`"0"`, `"1"`, `"2"`) |

Controls how deeply nested objects are flattened into parent columns (MongoDB documents, BSON, JSON-typed SaaS payloads). SQL tables are already flat, so the default is a no-op for them. Raising the value **increases** flattening. Also accepted as environment variable `MAX_TABLE_NESTING`.

| Value | Meaning                                                                              |
| ----- | ------------------------------------------------------------------------------------ |
| `"0"` | Nested objects land as one `json` column on the parent. No `__` flattening.          |
| `"1"` | Flatten one level into parent columns (`addr__city`). Remaining objects stay `json`. |
| `"2"` | Flatten two levels (`addr__city`, `addr__geo__lat`).                                 |

{% hint style="warning" %}
**Raising nesting can turn arrays into child tables.**

At `"0"`, nested objects and arrays stay on the parent as `json`. When you raise `max_table_nesting`, nested **objects** flatten into `__` columns, and a nested **array** at that depth can become a **child table** with one row per element instead of a JSON column.

MongoDB **batch** is stricter about arrays. If an array exists on the **first document** read, Nilus keeps it as `json` even when nesting is `"1"` or higher. If that field is missing from the first document, it can still land as `<collection>__<field>`.

Leave `"0"` unless you have inspected the payload for arrays.
{% endhint %}

{% hint style="warning" %}
**Type matters**

The stack schema declares `max_table_nesting` as a string. Always quote the value (`"0"`, not `0`). An integer literal will be rejected by the JSON-schema validator.
{% endhint %}

#### Example: Keep nested MongoDB objects as JSON

```yaml
spec:
  source:
    address: mongodb://user:pass@host:27017
    options:
      source_table: sample.users
      max_table_nesting: "0"
  sink:
    address: postgresql://user:pass@host:5432/warehouse
    options:
      dest_table: raw.users
      incremental_strategy: append
```

#### Example: Flatten one level of nested objects

```yaml
spec:
  source:
    address: mongodb://user:pass@host:27017
    options:
      source_table: sample.users
      max_table_nesting: "1"
  sink:
    address: postgresql://user:pass@host:5432/warehouse
    options:
      dest_table: raw.users
      incremental_strategy: append
```


---

# 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-shape-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.
