Output formats

CSV, Parquet, Delta Lake and Apache Iceberg output: paths, direct cloud writes, table naming, compression and format-specific options.

data_retrieval.output_format chooses how extracted rows are written. Pick one format per agent installation.

Format	Best for	Incremental (CT/CDC) behaviour	Where it lands
`csv`	Quick inspection, legacy loaders	Each run writes the changed rows as a new file set	`output_path/YYYY/MM/DD/<run epoch>/` then uploaded
`parquet`	Columnar landing zone, Snowflake `COPY INTO`, DuckDB	Same as CSV, columnar and compressed	Same dated layout
`delta`	Databricks, Snowflake Delta Direct, Fabric, Trino	Rows are merged into one Delta table per source table with a transaction log	`storage_base_path/delta/<table>/`
`iceberg`	Snowflake Iceberg tables, StarRocks, Trino, Dremio	Rows are merged into one Iceberg table per source table with snapshots	`storage_base_path/iceberg/<table>/`

Delta Lake and Apache Iceberg are the formats designed for continuous, in-place synchronization and are what the write strategies, schema drift handling and SCD2 apply to. CSV and Parquet produce a new dated batch on every run and leave merging to the downstream system.

Common settings

data_retrieval:
  output_format: delta
  output_path: ./output
  direct_cloud_write: true
  storage_base_path: /data/delta_iceberg_tables
  table_name_casing: default
  table_name_structure: default
  parquet_compression: zstd
  always_gzip_output_files: true
  clean_up_output_folder: true
  records_per_file_threshold: 100000

Key	Default	Description
`output_path`	`./output`	Local directory for extracted files, the `audit/` trail and, when `direct_cloud_write` is false, the local copy of Delta / Iceberg tables.
`direct_cloud_write`	`false`	Write Delta and Iceberg tables straight to object storage with no local copy. Required for cloud query engines (Snowflake, Databricks, StarRocks) so that metadata contains `s3://` or `azure://` URIs rather than `file://`. Recommended for every production deployment.
`storage_base_path`	none	Fixed base path for Delta / Iceberg tables: `<storage_base_path>/<format>/<db.schema.table>`. Without it tables are written under the dated layout and every run creates a new table, so always set it for `delta` and `iceberg`. Ignored for `csv` and `parquet`.
`table_name_casing`	`default`	`default` keeps SQL Server casing, `uppercase` or `lowercase` normalize table and file names.
`table_name_structure`	`default`	Folder / namespace layout for Delta and Iceberg, see below.
`parquet_compression`	`snappy`	`snappy`, `zstd`, `gzip` or `uncompressed` for Parquet data files in Delta and Iceberg tables. `zstd` gives noticeably smaller files; all four are readable by Snowflake, Databricks, DuckDB and Trino.
`parquet_compression_level`	codec default	Optional level for `gzip` (1 to 9) and `zstd` (1 to 22).
`always_gzip_output_files`	`true`	Gzip CSV output files.
`clean_up_output_folder`	`true`	Remove previous local output before a run. Delta and Iceberg directories are preserved.
`records_per_file_threshold`	`100000`	Rows per CSV / Parquet file before the agent starts a new file.
`null_fallback_data_types`	`geography`, `geometry`, `hierarchyid`, `sql_variant`	SQL Server types the ODBC driver cannot deserialize. Columns of these types are kept but their values are written as NULL. Add `xml` if you hit ODBC errors on XML columns.

Table naming

table_name_structure controls how the database, schema and table names become a path (Delta) or a namespace and table (Iceberg):

Value	Delta path	Iceberg namespace / table
`default` or `flat`	`delta/SalesDb.dbo.Customer/`	`default` / `SalesDb__dbo__Customer`
`nested`	`delta/SalesDb/dbo/Customer/`	`default.SalesDb.dbo` / `Customer`
`db_combined`	`delta/SalesDb__dbo/Customer/`	`default.SalesDb__dbo` / `Customer`
`db_separated`	`delta/SalesDb/dbo.Customer/`	`default.SalesDb` / `dbo__Customer`

In object storage everything is rooted under your organization and project, then dlh_ref.target_schema_prefix, which is what keeps several agents (for example one per site or store) from colliding in the same container:

<org_guid>/<prj_guid>/
  logs/                                    shipped run logs
  <target_schema_prefix>/
    <storage_base_path>/delta/<table>/     Delta tables
    <storage_base_path>/iceberg/           Iceberg warehouse
    <storage_base_path>/sql_queries/<output_folder_prefix>/
    YYYY/MM/DD/<run epoch>/                CSV / Parquet batches and sql_files

Do not change naming after the first load

Changing table_name_casing, table_name_structure, storage_base_path or target_schema_prefix after tables exist makes the agent write to new locations and leaves the old tables orphaned. If you must change them, plan a historical load and clean up the old paths.

Delta Lake

data_retrieval:
  output_format: delta
  direct_cloud_write: true
  storage_base_path: /data/delta_iceberg_tables
  create_delta_table_crc: true
  delta_log_retention_duration: 'interval 7 days'
  delta_vacuum_retention_hours: 168

Key	Default	Description
`create_delta_table_crc`	`true`	Write a `.crc` checksum file per commit in `_delta_log/`, matching Spark's behaviour and speeding up discovery by Databricks, Trino and Snowflake.
`delta_log_retention_duration`	`interval 7 days`	How long to keep JSON commit files before the latest checkpoint. Controls `_delta_log/` growth.
`delta_vacuum_retention_hours`	`168`	Age at which data files no longer referenced by the current version are removed. Keep at 7 days or more when an external engine reads the table; shorter values can cause "Unable to download parquet file" errors during incremental refreshes. The agent warns at startup when this is set very low.

Each source table becomes one Delta table. Every run that changes rows produces a new version; the run log prints the resulting version and total row count per table.

On Azure, azure_storage_uri_scheme (under dlh_storage) selects the URI style written into Delta metadata: az (default), abfss for Databricks, Synapse and Data Factory, wasbs for legacy Hadoop tools, or azure for Snowflake external volumes.

Apache Iceberg

data_retrieval:
  output_format: iceberg
  direct_cloud_write: true
  storage_base_path: /data/delta_iceberg_tables
  iceberg_expire_snapshots: true
  iceberg_version_hint: true
  iceberg_metadata_previous_versions_max: 100
  iceberg_delete_after_commit: true
  iceberg_apply_deletes: true
  iceberg_upload_catalog: true
  iceberg_path_style: absolute

Key	Default	Description
`iceberg_expire_snapshots`	`true`	Expire old snapshots after each write so only current data files are uploaded and retained. Set to `false` to keep full snapshot history for time travel.
`iceberg_version_hint`	`true`	Write `metadata/version-hint.text` so DuckDB, Trino and similar engines find the latest metadata automatically. Snowflake does not read it; use `ALTER ICEBERG TABLE ... REFRESH`.
`iceberg_metadata_previous_versions_max`	`100`	Number of previous metadata JSON files to keep (`write.metadata.previous-versions-max`). `-1` keeps all.
`iceberg_delete_after_commit`	`true`	Remove superseded metadata files immediately after a commit.
`iceberg_apply_deletes`	`true`	Apply CT/CDC deletes to the Iceberg table (copy-on-write). `false` logs deletes without applying them.
`iceberg_upload_catalog`	`true`	Upload the agent's local catalog file to the warehouse root as a backup. Downstream engines read the metadata JSON, not this file.
`iceberg_path_style`	`absolute`	`absolute` stores full cloud URIs in metadata (supported everywhere). `relative` stores table-relative paths for zero-rewrite relocation; check that your engine supports it first.

Iceberg with cloud engines

Set direct_cloud_write: true when Snowflake, Databricks or StarRocks will read the tables. Iceberg metadata and manifests embed file locations; with local writes they would contain file:// paths that the engine cannot resolve. On Azure the agent always writes azure://account.blob.core.windows.net/container/... paths in Iceberg metadata so they match a Snowflake external volume STORAGE_BASE_URL.

CSV and Parquet

Both formats write to output_path/YYYY/MM/DD/<run epoch>/<db>.<schema>.<table>.<ext> (gzipped CSV by default) and upload the same layout to storage. Large tables are split into files of records_per_file_threshold rows. Incremental runs contain only changed rows plus the CT/CDC operation metadata, so a downstream loader has to apply them; if you want the agent to maintain a queryable table, use Delta Lake or Iceberg instead.

Column typing

Delta and Iceberg columns are typed from SQL Server metadata when a table is first created: decimal, numeric, money and smallmoney columns take the declared precision and scale (for example decimal(18,4)) rather than a width inferred from the first batch of rows, so later larger values fit. Date, time, boolean, integer, floating point, string and binary types map to their natural Arrow types. Subsequent type changes in the source are handled by Schema drift.