Change Tracking to Delta Lake on Azure and Snowflake

This is the deployment most DLH.io customers run: one agent on a Windows host next to the SQL Server, Change Tracking (CT) for incremental changes, Delta Lake tables written straight into an Azure storage container, and Snowflake reading those tables in place. Nothing is staged on the local disk.

When to use it

• You own the SQL Server and can enable Change Tracking on the databases and tables you need (it is a lightweight, built-in feature that does not require SQL Server Agent jobs or Enterprise edition).
• Most tables have a primary key. Tables without one still work; they fall back to a full extract each run.
• You want a queryable table per source table in storage, kept up to date every few minutes, rather than files of changes that a downstream loader has to apply.
• Snowflake (or Databricks, Synapse, Fabric) is the analytics engine and Azure is the storage.

Prerequisites

1. A DLH.io account with an Agent connection: you need org_guid, prj_guid, connection_guid and an API key. The platform issues the Azure storage credentials to the agent at runtime, so no storage key has to live on the host. See Storage and bootstrap.
2. The agent installed on a Windows host with the ODBC Driver 18 for SQL Server, following Getting started.
3. Change Tracking enabled on the database and on each table you want to sync incrementally:

ALTER DATABASE SalesDb
  SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE dbo.CustomerOrder ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
ALTER TABLE dbo.Location      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);
-- repeat for each table, or script it from sys.tables

4. A SQL login for the agent with SELECT and VIEW CHANGE TRACKING on the tracked tables:

GRANT SELECT, VIEW CHANGE TRACKING 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: [SalesDb]

data_retrieval:
  historical_load: false
  output_format: delta
  direct_cloud_write: true
  storage_base_path: /data/delta_iceberg_tables
  output_path: C:/dlh/output
  table_name_casing: default
  table_name_structure: default
  parquet_compression: zstd
  create_delta_table_crc: true
  delta_log_retention_duration: 'interval 7 days'
  delta_vacuum_retention_hours: 168
  on_schema_drift_action: migrate_target
  on_schema_drift_action_handling: fail_once
  produce_audit_trail: false
  agent_log_retention_days: 30
  fetch_batch_size: 100000
  max_memory_percent: 85
  databases:
    - name: SalesDb
      sync_mode: ct
      schema_refresh_mode: 1D
      tables:
        - name: dbo.*
          all_columns: true
          sync_mode: ct
          no_pk_strategy: full
        - name: dbo.AuditLog
          all_columns: true
          write_strategy: append
      views: []

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>'

# No credentials here: the platform issues them at runtime. The section is kept
# only to select the URI style Snowflake expects inside Delta metadata.
dlh_storage:
  storage_type: azure
  azure_storage_uri_scheme: azure

dlh_notifications:
  enabled: true
  platform_run_report: true
  error_severity: error
  webhooks:
    - url_env_var: DLH_OPS_WEBHOOK_URL
      format: teams

What each block does:

First run

Run dlh_agent_sql_server.exe --config-check, then dlh_agent_sql_server.exe --diagnose and confirm every line is PASS (a WARN on free disk is acceptable for a direct-cloud-write host). Then start the agent once interactively.

The very first run is always a historical load: every table is extracted in full, the Delta tables are created, and the CT version of each table is recorded in the state database. Expect this to take from minutes to hours depending on volume; the log prints one line per table as it completes and the tier banner near the top confirms CUSTOMER or TRIAL with Cloud uploads : ENABLED.

Successful creation looks like this in the log:

Delta table written to abfss://<container>@<account>.dfs.core.windows.net/<org>/<prj>/Site003/data/delta_iceberg_tables/delta/SalesDb.dbo.CustomerOrder (version 0, 1046246 rows, mode=overwrite)

And the run summary lists every table with full as the mode and OK as the status. Later incremental runs print Merging N ct changes into Delta table at ... followed by Delta merge complete at ... (version N, total rows: N) for each table that changed.

Steady state

Schedule the executable every five minutes as described in Operations. Each run:

1. Asks SQL Server for the changes since the recorded CT version of each table.
2. Merges inserts and updates into the Delta table by primary key and applies deletes (ct_cdc_delete_mode: physical by default).
3. Advances the CT version only for tables whose write succeeded.

A typical steady-state summary:

  SalesDb   dbo.CustomerOrder      table   ct                 328  OK
  SalesDb   dbo.Location           table   ct                  16  OK
  SalesDb   dbo.SecurityMatrix     table   ct                   0  OK        No CT/CDC changes - skipped write
  SalesDb   dbo.AuditLog           table   ct                  91  OK

No CT/CDC changes - skipped write is normal: most tables do not change every five minutes and no Delta version is produced for them. Delta versions therefore increase only for tables that received changes; comparing versions day over day is the quickest health check on the storage side.

If one table fails, the others still complete and are committed. The failing table is listed in the ERRORS block, notifications fire, and its CT pointer is held so the next successful run picks the changes up.

Snowflake

Snowflake reads the Delta tables in place through an external volume and a catalog integration for object-store Delta tables. Create them once per agent installation:

CREATE OR REPLACE EXTERNAL VOLUME dlh_site003_vol
  STORAGE_LOCATIONS = ((
    NAME = 'site003-azure'
    STORAGE_PROVIDER = 'AZURE'
    STORAGE_BASE_URL = 'azure://<account>.blob.core.windows.net/<container>/<org>/<prj>/Site003/data/delta_iceberg_tables/delta/'
    AZURE_TENANT_ID = '<tenant-id>'
  ));

CREATE OR REPLACE CATALOG INTEGRATION dlh_delta_catalog
  CATALOG_SOURCE = OBJECT_STORE
  TABLE_FORMAT = DELTA
  ENABLED = TRUE;

Then one Iceberg table per Delta table, pointing at its folder:

CREATE OR REPLACE ICEBERG TABLE RAW.SITE003.CUSTOMER_ORDER
  EXTERNAL_VOLUME = 'dlh_site003_vol'
  CATALOG = 'dlh_delta_catalog'
  BASE_LOCATION = 'SalesDb.dbo.CustomerOrder/';

ALTER ICEBERG TABLE RAW.SITE003.CUSTOMER_ORDER REFRESH;

Run ALTER ICEBERG TABLE ... REFRESH on a schedule (a Snowflake task) or enable auto refresh so Snowflake picks up new Delta versions. Follow Snowflake's documentation for granting the external volume's service principal read access to the container; the exact SQL above may need adjusting to your Snowflake edition and region.

Operational notes

• Adding a table: enable Change Tracking on it in SQL Server. The dbo.* wildcard picks it up on the next run and performs its initial full load automatically, while other tables continue incrementally.
• Excluding a table: replace the wildcard with explicit entries, or keep the wildcard and add the table's specific entry with a different sync_mode; specific entries win over wildcards. There is no exclude list.
• Re-baselining one table: set sync_mode: full on that table's entry for one run, then set it back to ct. Do not set historical_load: true unless you want every table reloaded.
• Moving hosts: copy the state database with the config so CT pointers survive; see State store.
• Monitoring: the platform run report plus a silence alert on your side covers "the agent stopped running" and "a table keeps failing". See Monitoring and logs.

Variations

• Databricks or Synapse instead of Snowflake: set azure_storage_uri_scheme: abfss and register the Delta folders as external tables.
• Amazon S3 instead of Azure: remove the dlh_storage block entirely (the platform issues S3 credentials) and read the Delta tables with Snowflake's S3 external volume, Athena or Databricks; nothing else changes.
• Static Azure credentials instead of platform-issued ones: add azure_account, azure_sas_token and azure_container_name under dlh_storage. Complete static credentials always take precedence.
• Row history for selected tables: see SCD Type 2 history tables.