CDC to Apache Iceberg on Amazon S3

Use this scenario when your DBAs already run Change Data Capture (CDC), or when you need the before and after images and operation metadata that CDC records, and when Apache Iceberg is the table format your analytics platform standardises on. The agent writes Iceberg data files, metadata and its catalog straight to S3.

When to use it

• CDC is enabled on the source (it requires the SQL Server Agent service to run the capture and cleanup jobs; Change Tracking does not). If you have a free choice, Change Tracking is simpler to operate; see Data retrieval.
• Downstream engines read Iceberg natively: Snowflake, Athena, Trino, Spark, DuckDB.
• Deletes at the source must disappear from the target (iceberg_apply_deletes: true), or be kept as flagged rows (ct_cdc_delete_mode: logical).

Prerequisites

1. A DLH.io account and API key, with the connection's storage provider set to S3 so the platform issues S3 credentials at runtime. Static keys are also supported; see the variation at the end.
2. CDC enabled on the database and each table:

USE OrdersDb;
EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo', @source_name = N'Orders',
  @role_name = NULL, @supports_net_changes = 1;

@supports_net_changes = 1 requires a primary key or unique index and lets the agent read one net row per key per interval.

3. The SQL Server Agent service running on the instance (CDC capture and cleanup are SQL Agent jobs) and SELECT on the cdc schema for the agent login:

GRANT SELECT ON SCHEMA::cdc TO svc_dlh_agent;
GRANT SELECT ON SCHEMA::dbo TO svc_dlh_agent;

Configuration

agent_version: 1.9.4
state_db_path: C:/dlh/state/dlh_agent_state.duckdb

connection_information:
  connection_type: sql_server
  server_name_or_ip: sql01.corp.local
  server_port: 1433
  database_names: [OrdersDb]

data_retrieval:
  historical_load: false
  output_format: iceberg
  direct_cloud_write: true
  storage_base_path: /data/delta_iceberg_tables
  output_path: C:/dlh/output
  table_name_structure: nested
  table_name_casing: lowercase
  parquet_compression: zstd
  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
  on_schema_drift_action: migrate_target
  on_schema_drift_action_handling: fail_once
  databases:
    - name: OrdersDb
      sync_mode: cdc
      schema_refresh_mode: table_changed
      tables:
        - name: dbo.Orders
          all_columns: true
          partition_columns: [OrderYear]
        - name: dbo.OrderLines
          all_columns: true
        - name: dbo.Customers
          all_columns: true
          ct_cdc_delete_mode: logical
          masks:
            - column: Email
              algorithm: partial
              param: 3
        - name: ref.*
          all_columns: true
          sync_mode: full
          write_strategy: replace

dlh_ref:
  org_guid: '<YOUR_ORG_GUID>'
  prj_guid: '<YOUR_PROJECT_GUID>'
  connection_guid: '<YOUR_CONNECTION_GUID>'
  target_schema_prefix: Site003
  api_key: '<YOUR_DLH_API_KEY>'

dlh_notifications:
  enabled: true
  platform_run_report: true
  webhooks:
    - url: 'https://hooks.slack.com/services/T000/B000/XXXXXXXX'
      format: slack

What lands in S3

With the platform-issued bucket and the settings above:

s3://<bucket>/<org>/<prj>/Site003/data/delta_iceberg_tables/iceberg/
  dlh_iceberg.db                              catalog backup
  default.ordersdb.dbo.db/orders/
    metadata/                                 vN.metadata.json, manifests, version-hint.text
    data/OrderYear=2026/*.zstd.parquet
  default.ordersdb.dbo.db/orderlines/
  default.ordersdb.dbo.db/customers/
  default.ordersdb.ref.db/country/

Folder names under the warehouse follow the Iceberg catalog convention of <namespace>.db/<table>/; the Creating new Iceberg table: and Iceberg upsert complete: log lines print the identifier of each table, and the metadata JSON records the exact location.

First run and steady state

--config-check, then --diagnose, then one interactive run. The first run creates every table (Creating new Iceberg table: default.ordersdb.dbo.orders then Iceberg table written: ... (N rows, mode=overwrite, snapshots=1)) and records the CDC LSN per table.

Subsequent runs read the CDC change table since the recorded LSN and print Upserting N cdc changes into Iceberg table ... followed by Iceberg upsert complete: ... (total rows: N, snapshots: N). Tables with no changes are skipped without a new snapshot. After every write the agent updates metadata/version-hint.text so DuckDB and Trino find the latest metadata without a catalog.

Reading the tables

Snowflake: create an S3 external volume and an object-store catalog integration for Iceberg, then one table per folder pointing at the current metadata file. Refresh with ALTER ICEBERG TABLE ... REFRESH '<metadata file>' on a schedule; Snowflake does not read version-hint.text.

CREATE OR REPLACE CATALOG INTEGRATION dlh_iceberg_catalog
  CATALOG_SOURCE = OBJECT_STORE
  TABLE_FORMAT = ICEBERG
  ENABLED = TRUE;

CREATE OR REPLACE ICEBERG TABLE RAW.SITE003.ORDERS
  EXTERNAL_VOLUME = 'dlh_site003_s3_vol'
  CATALOG = 'dlh_iceberg_catalog'
  METADATA_FILE_PATH = 'default.ordersdb.dbo.db/orders/metadata/v12.metadata.json';

Athena and Glue: register each table with the Glue catalog from its metadata location (register_table via Spark or the Glue console) and query it as an Iceberg table. DuckDB can attach the warehouse directly with the Iceberg extension and reads version-hint.text automatically.

Variations

• Static S3 credentials: add a dlh_storage block with storage_type: s3, access_key, secret_key, bucket_name and region. It is encrypted in place on the first run.
• Azure instead of S3: nothing in data_retrieval changes. Iceberg metadata on Azure always uses azure://account.blob.core.windows.net/container/... paths so a Snowflake external volume STORAGE_BASE_URL matches.
• Change Tracking instead of CDC: set sync_mode: ct; everything on the Iceberg side is identical.
• Row history: give a table write_strategy: scd2; see SCD Type 2 history tables. Iceberg and Delta support the same SCD2 semantics.