> 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/optimizing-for-time.md).

# Optimizing for time

Diagnostic guide for slow pipelines: bottleneck identification, tuning order, and the Iceberg file-size and worker-concurrency knobs that affect runtime.

Use this guide when a pipeline is correct but does not finish inside the expected window.

## Find the bottleneck

| Signal                                      | Likely bottleneck                                     | Next check                                                                       |
| ------------------------------------------- | ----------------------------------------------------- | -------------------------------------------------------------------------------- |
| High duration and low records/sec           | Source extraction or destination loading.             | Compare stage timing and destination-side load logs.                             |
| Extract stage dominates                     | Source pagination, source throttling, or query shape. | Try a bounded table, narrower object, or connector-specific filters.             |
| Load stage dominates                        | Destination write throughput or file layout.          | Review `DATA_WRITER__FILE_MAX_BYTES`, destination permissions, and staging path. |
| CPU is high with steady throughput          | Compute-bound processing.                             | Increase compute only if the workload scales with it.                            |
| CPU and memory are low but duration is high | Waiting on source, network, or destination.           | Check source API/warehouse limits and destination load queues.                   |

## Tuning order

1. Confirm the row volume and source object are expected.
2. Use `incremental_key` and bounded intervals where supported.
3. Increase `page_size` only if memory remains healthy.
4. Increase `extract_parallelism` only for connectors where parallel extraction is supported.
5. Tune `DATA_WRITER__FILE_MAX_BYTES` to avoid excessive tiny files or very large loader batches.
6. Increase load or normalization workers only when destination and compute capacity can use the extra concurrency.
7. Recheck destination query behavior after changing partitions or clustering.

## Lakehouse / Iceberg loads: size files by table shape

For Lakehouse (Iceberg) destinations the dominant time factor on large loads is **file size**, governed by `DATA_WRITER__FILE_MAX_BYTES` (a per-file byte cap).

| Table shape             | Will it be queried?      | File cap                 | Why                                                         |
| ----------------------- | ------------------------ | ------------------------ | ----------------------------------------------------------- |
| Wide (100+ columns)     | Yes                      | Default 128 MB or 512 MB | Keeps Iceberg per-file stats for pruning.                   |
| Wide (100+ columns)     | No (archive / full-scan) | 2 GB                     | Fewer commits, fastest write; costs more memory.            |
| Narrow (high row count) | Either                   | 512 MB                   | Larger files give no time benefit and can be slower on AWS. |

Files at or below **512 MB** preserve Iceberg file-level statistics so engines can skip irrelevant files. Going larger trades query pruning and peak memory for fewer catalog commits. For the validated wide/narrow configurations and benchmark numbers, see [Tuning large Lakehouse (Iceberg) loads](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-lakehouse-iceberg-loads.md).

## Knob reference

The following settings control time-optimized pipeline runs.

### File sizing (runtime env)

#### `DATA_WRITER__FILE_MAX_BYTES`

Runtime environment variable, default `134217728` (128 MB). The per-file **byte cap** is the primary file-size lever for Lakehouse / Iceberg loads. Larger files mean fewer catalog commits and faster writes, at the cost of higher peak memory and (past \~512 MB) weaker query pruning. **Raise** it for large wide-table loads. For example, use `536870912` (512 MB) for a good time-to-memory balance, or `2147483648` (2 GB) for write-only archives. Set the sink option `loader_file_size` high enough that the byte cap governs rotation rather than the row count. See [Tuning large Lakehouse (Iceberg) loads](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-lakehouse-iceberg-loads.md) for validated values.

### Staging (`sink.options`)

#### `staging_bucket`

Default: unset (must be `gs://`- or `s3://`-prefixed). External staging bucket for warehouses that support external-stage loads (BigQuery, Snowflake, Redshift). Without it the loader streams rows directly into the warehouse, which is bandwidth-bound and slow for large extracts. The bucket must be writable by the destination's service principal; for BigQuery it must live in the same region as the dataset.

### Concurrency (`source.options` + runtime env)

#### `extract_parallelism`

`source.options`, default `5`. Concurrent extraction workers. **Raise** toward available vCPU when CPU is low and the run is long; **lower** it if the source throttles or memory spikes. Each worker holds its own page buffer. See [Optimizing for resource](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md). Typical range 2–8.

#### `LOAD__WORKERS`

Runtime environment variable, default \~5. Loader concurrency: parallel workers that stage, write, and commit files. This is the **single biggest throughput knob** for write-heavy pipelines once file size and format are sensible. Raise until CPU or object-store bandwidth saturates, then back off one notch.

#### `NORMALIZE__WORKERS`

Runtime environment variable, default \~1. Concurrency for normalization between extract and load. Most **batch** pipelines stay at 1–2; raise only if profiling shows normalization is the bottleneck, such as heavy `type_hints` or nested flattening. On **CDC** pipelines, leave this at the default. Raising it, including `NORMALIZE__WORKERS=20`, caused clean-exit restarts in load tests. See [Optimizing CDC](broken://spaces/MGGvF90i2ylfQsaDXNwy/pages/0vlze05bKKCIZAwUs1OK).

#### `LOAD__PARALLELISM_STRATEGY`

Runtime environment variable, values `parallel` / `sequential`. How the loader schedules load units across workers. Use `parallel` for Lakehouse and warehouses that handle concurrency well (DataOS Lakehouse, BigQuery, Snowflake, Databricks); `sequential` for fragile or strictly-locked destinations (some MS SQL Server / Synapse setups, conservative Elasticsearch).

### Sampling for faster validation (`source.options`)

Use these to iterate quickly on a slice before committing to a full run. Do not use them on steady-state schedules.

#### **`sql_limit`**

Default: unset. Caps total rows extracted (a `LIMIT` clause on SQL sources). Use for validation runs and quick sanity checks before scaling to the full dataset; common range 1k–100k.

#### **`yield_limit`**

Default: unset. Caps the number of pages yielded. It is the page-level analogue of `sql_limit` and works for non-SQL sources too. With `page_size: 50000` and `yield_limit: 4`, the source yields at most 200,000 rows.

### Passing options and env vars

Source and sink knobs go under `source.options` and `sink.options`. Runtime environment variables (`DATA_WRITER__FILE_MAX_BYTES`, `LOAD__WORKERS`, `EXTRACT__WORKERS`, and the rest) are projected onto the pipeline through `spec.use.projection.projections.envVars`. The example below mirrors the validated wide-table Lakehouse throughput profile:

```yaml
spec:
  type: batch
  compute: <compute-name>
  use:
    projection:
      projections:
        envVars:                              # runtime environment variables
          - key: DATA_WRITER__FILE_MAX_BYTES
            template: "536870912"             # 512 MB per-file byte cap
          - key: LOAD__WORKERS
            template: "16"
          - key: EXTRACT__WORKERS
            template: "8"
          - key: EXTRACT__MAX_PARALLEL_ITEMS
            template: "16"
  source:
    address: dataos://<source-depot>?purpose=rw
    options:
      source_table: "dbo.events"
      page_size: 100000
  sink:
    address: dataos://<lakehouse-depot>?purpose=rw
    options:
      dest_table: "analytics.events"
      incremental_strategy: append
```

For warehouse sinks that support external-stage loads, add `staging_bucket` under `sink.options`. Omit it for DataOS Lakehouse.

## Avoid these shortcuts

* Do not use `sql_limit` as a production speed fix. It is a sampling and testing knob.
* Do not raise every concurrency knob at once.
* Do not optimize a failed run before reading the error.
* Do not switch from `merge` to `append` just to reduce runtime unless downstream consumers explicitly want history instead of current state.
* Do not run a full-table `merge` on a very large Lakehouse table. PyIceberg row-level upserts make it extremely slow. Use an incremental `merge` that loads only changed rows per run.

## Related docs

* [Pipeline optimization](https://v2.dataos.info/references/resources/nilus/pipeline-optimization.md)
* [Optimizing for resource](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimizing-for-resource.md)
* [Optimizing CDC](broken://spaces/MGGvF90i2ylfQsaDXNwy/pages/0vlze05bKKCIZAwUs1OK)
* [Optimize sink datasets](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets.md)
* [Tuning large Lakehouse (Iceberg) loads](https://v2.dataos.info/references/resources/nilus/pipeline-optimization/optimize-sink-datasets/optimize-lakehouse-iceberg-loads.md)
* [Grafana dashboards](https://v2.dataos.info/references/resources/nilus/observability/grafana-dashboard.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/pipeline-optimization/optimizing-for-time.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.
