Performance and retention

Batch sizes, memory throttling, upload strategy, file thresholds and the retention settings that keep disk usage under control on a long-running agent host.

The agent is designed to run every few minutes for months on a modest Windows VM. These settings control how much memory a run may use, how work is split into files and uploads, and how aggressively local and cloud artifacts are cleaned up.

Streaming fetch and memory throttling

data_retrieval:
  fetch_batch_size: 100000
  max_memory_percent: 85

Key	Default	Description
`fetch_batch_size`	`100000`	Rows fetched from SQL Server per batch. The agent streams with `fetchmany` so a 50 million row table never has to fit in memory. Lower values reduce peak memory, higher values reduce round trips.
`max_memory_percent`	`85`	System memory percentage (50 to 95) at which the agent pauses between batches. Fetching resumes when usage drops below the threshold, or after a five-minute patience window.

Guidance:

8 GB host, other software present: fetch_batch_size: 50000, max_memory_percent: 75.
16 GB dedicated host: defaults.
Very wide tables (hundreds of columns, large nvarchar(max) or binary columns): halve the batch size first; memory per batch scales with row width, not only row count.
Delta and Iceberg merges hold the batch plus target metadata in memory; on the initial full load of a large table expect the peak to occur during the write, not the fetch.

Every run prints a STATE STORE (DuckDB) telemetry block with the process RSS and free disk, and --diagnose reports total and available RAM against max_memory_percent, so you can size these values from real numbers rather than guesses.

Files and uploads

data_retrieval:
  records_per_file_threshold: 100000
  cloud_upload_strategy: end_of_run
  cloud_upload_batch_size: 5
  list_cloud_objects_after_upload: false
  direct_cloud_write: false

Key	Default	Description
`records_per_file_threshold`	`100000`	Rows per output file for CSV and Parquet before a new file is started.
`cloud_upload_strategy`	`end_of_run`	`end_of_run` uploads all local output once extraction finishes; `per_table` uploads each table as soon as it is written; `batch` uploads every `cloud_upload_batch_size` tables. `per_table` and `batch` bound the local disk footprint and get data downstream sooner on long runs.
`cloud_upload_batch_size`	`5`	Tables per upload group when the strategy is `batch`.
`list_cloud_objects_after_upload`	`false`	Log the uploaded object keys after each upload. Useful during setup, noisy in production.
`direct_cloud_write`	`false`	Write Delta and Iceberg tables directly to object storage instead of locally then uploading. Recommended for large Delta and Iceberg estates and required for cloud engines that read the Iceberg metadata in place. See Output formats.

Five-minute schedules

With a five-minute cadence keep runs short and predictable: incremental sync modes (ct, cdc, auto), direct_cloud_write: true or cloud_upload_strategy: per_table, and produce_audit_trail: false once the deployment is stable. A run that regularly approaches the interval should be split into two agent installs (two connections) rather than run in parallel from one directory.

Data type fallbacks

data_retrieval:
  null_fallback_data_types:
    - geography
    - geometry
    - hierarchyid
    - sql_variant
    # - xml

SQL Server types the ODBC driver cannot deserialize are still included as columns but their values are written as NULL, so one exotic column never blocks a table. Add xml if you hit ODBC errors on XML columns, or remove a type once you have confirmed your driver handles it.

Retention

Key	Default	Applies to	Description
`agent_log_retention_days`	`30`	local `logs\`	Days of `dlh_agent_*.log` files to keep; older files are deleted at the start of each run. `0` keeps everything.
`produce_audit_trail`	`true`	local `audit\`	Writes per-run, per-table diagnostics (the CT/CDC query, raw and cleansed samples, merge operations). Invaluable during rollout, disable in steady state to save disk and I/O.
`audit_trail_retention_days`	`7`	local `audit\`	Days of audit run folders to keep. `0` keeps everything.
`clean_up_output_folder`	`true`	local `output\`	Clear the local output folder before each run writes new data (Delta and Iceberg directories are preserved).
`delta_log_retention_duration`	`interval 7 days`	Delta `_delta_log/`	Transaction log JSON files older than this and before the latest checkpoint are removed after each write. Controls metadata growth on tables written every five minutes.
`delta_vacuum_retention_hours`	`168`	Delta data files	Data files no longer referenced by the current version are deleted once older than this. Keep at seven days or more when Snowflake, Databricks or another engine reads the table incrementally; `0` breaks external readers that are still replaying recent versions.
`iceberg_expire_snapshots`	`true`	Iceberg snapshots	Expire old snapshots before upload so only current data files are transferred and stored. Set `false` to keep full snapshot history for time travel.

Time travel and audit history

Vacuum and snapshot expiry are permanent. If you rely on Delta time travel or Iceberg snapshot history for audit, set delta_vacuum_retention_hours and delta_log_retention_duration to your audit horizon or set iceberg_expire_snapshots: false, and budget the extra storage. Use the SCD2 write strategy if you need queryable row history rather than table-version history.

Disk sizing

A useful rule of thumb for the agent directory on the Windows host:

output\: with direct_cloud_write: true for Delta and Iceberg, near zero. Otherwise the compressed size of your largest table's initial load, or of one interval's changes in steady state, times the number of tables uploaded together.
logs\: a few MB per run at default verbosity, times runs per day, times agent_log_retention_days.
audit\: can be large on full loads because it includes data samples; the main reason to turn it off after rollout.
dlh_agent_state.duckdb: tens of MB for thousands of tables; grows slowly with run_history. See State store.

--diagnose reports free space on the volumes holding the output, state and temp paths and warns when they run low.

Streaming fetch and memory throttling

Files and uploads

Data type fallbacks

Retention

Disk sizing

Page Contents