Data retrieval | DLH.io Documentation

Selecting databases, tables, views and SQL queries; sync modes (full, Change Tracking, CDC, auto); schema refresh; columns, partitions, masking and historical loads.

Everything the agent extracts is declared under data_retrieval.databases. Each database entry lists tables, views and optional SQL query files, and sets defaults that its tables inherit.

data_retrieval:
  historical_load: false
  databases:
    - name: SalesDb
      sync_mode: auto
      schema_refresh_mode: 1D
      tables:
        - name: dbo.*
          all_columns: true
          no_pk_strategy: full
        - name: dbo.Customer
          all_columns: false
          columns: [CustomerID, Name, Email, ModifiedDate]
          sync_mode: ct
          masks:
            - column: Email
              algorithm: partial
              param: 3
      views:
        - name: dbo.vSalesSummary
          all_columns: true
      sql_queries:
        - file: ./sql_queries/DailyRevenue.sql
          output_format: parquet
          output_folder_prefix: reports/daily_revenue

Database entries

Key	Default	Description
`name`	required	Database name. Must also appear in `connection_information.database_names`.
`sync_mode`	`full`	Default sync mode for every table in this database. Tables can override it.
`schema_refresh_mode`	`always`	When to refresh the schema catalog (columns, types, CT/CDC status) from SQL Server into the state database.
`tables`	`[]`	Table entries, wildcard or specific.
`views`	`[]`	View entries, wildcard or specific. Views are always extracted in full.
`sql_queries`	`[]`	Custom SQL files executed in the context of this database.

Sync modes

Mode	What happens each run	Requirements
`full`	`SELECT` the whole table and rewrite the target.	None.
`ct`	Read only rows inserted, updated or deleted since the last stored Change Tracking version, then merge them into the target.	CT enabled on the database and table; primary key.
`cdc`	Read the CDC capture instance for the same window and apply inserts, updates and deletes in commit order.	CDC enabled on the database and table; SQL Server Agent service running; primary key.
`auto`	Per table, pick CT if enabled, otherwise CDC if enabled, otherwise `full`. Re-evaluated whenever the schema catalog refreshes.	None.

The first run of any table (no pointer in the state database) is always a full extract, after which ct and cdc continue incrementally. If the source retention window has been exceeded (the stored version is older than CHANGE_RETENTION), the agent detects the invalid version and performs a full re-extract of that table automatically.

Change Tracking is the usual choice

CT has the lowest overhead on SQL Server, needs no SQL Server Agent jobs and gives the agent exactly the net changes it needs. Use CDC when you need the intermediate values of every change (for example for SCD2 history of every update within the window) or when CT cannot be enabled by policy.

Schema refresh mode

The schema catalog is what lets auto choose a sync mode, what --diagnose reports, and what schema drift remediation uses to look up declared column types.

Value	Behaviour
`always`	Refresh on every run. Simplest, but on databases with tens of thousands of objects it adds metadata queries to each run.
`never`	Collect once on the first run and never again. New tables matching a wildcard are not picked up.
`table_changed`	Refresh only when SQL Server reports a table modification since the last refresh (`sys.tables.modify_date`).
`1H`, `12H`, `1D`, `2W`, `1M`	Refresh when at least that much time has passed since the last refresh. `1D` is a good default for large databases on a 5 minute schedule.

Table entries

Key	Default	Description
`name`	required	`schema.table`, or a wildcard `schema.` / `schema.Prefix`. The wildcard must come after the schema.
`all_columns`	`true`	Extract every column. Wildcard entries always behave as `all_columns: true`.
`columns`	none	Explicit column list when `all_columns: false`. Ignored for wildcards.
`sync_mode`	database default	`full`, `ct`, `cdc` or `auto`.
`primary_key_columns`	auto-discovered	Override the primary key used for merges, for example on a table whose PK is not declared.
`no_pk_strategy`	`full`	What to do when `ct`/`cdc` is requested but the table has no primary key: `full` (full extract each run), `truncate_reload` (truncate the target then reload), `drop_recreate` (drop and recreate the target then reload).
`write_strategy`	inherits `data_retrieval.write_strategy`, else `merge`	How rows are written to the target. See Write strategies.
`ct_cdc_delete_mode`	`physical`	`physical` removes deleted rows from the target; `logical` keeps them and sets `_cdc_deleted = true`.
`incremental_key`	primary key	Column that bounds the window for `delete+insert`.
`partition_columns`	none	Delta / Iceberg partition columns, for example `[Year, Month]`.
`masks`	none	Column-level masking rules, see below.
`on_schema_drift_action`, `on_schema_drift_action_handling`	inherit `data_retrieval`	Per-table schema drift overrides. See Schema drift.

Wildcards and specific entries

dbo.* matches every table in dbo; Sales.Order* matches tables in Sales whose name starts with Order. All attributes on the wildcard entry apply to every matched table. If a table is matched by a wildcard and also has its own entry, the specific entry wins, which is how you give one table a different write strategy, a column list or masks while the rest of the schema uses the wildcard defaults.

New tables that appear in the source and match a wildcard are picked up the next time the schema catalog refreshes.

Column masking

Masks are applied in memory after CT/CDC cleanup and before the write, so masked values are the only values that reach storage.

Algorithm	`param`	Result
`hash`	optional truncation length	Deterministic SHA-256 hex digest, so equal source values stay joinable.
`partial`	characters to keep at each end (default 3)	`joh********com` style masking; the middle is replaced with ``.
`redact`	replacement string (default `[REDACTED]`)	Constant replacement.
`round`	rounding interval	Numeric bucketing, for example salaries rounded to the nearest 1000.

Each mask also accepts if_exists: false to fail the table when the column is missing (default is to skip the mask silently).

Views

View entries use the same name, all_columns and columns keys as tables (wildcards included) and are always extracted in full, because Change Tracking and CDC do not apply to views. In Community mode views are subject to the same per-object row cap as tables.

SQL queries

sql_queries runs .sql files against the database and writes the result set as CSV or Parquet through the normal upload pipeline. Use it for aggregations or joins that do not exist as views, or when you cannot create views on the source.

Key	Default	Description
`file`	required	Path to the `.sql` file, relative to the agent directory or absolute.
`output_format`	`parquet`	`csv` or `parquet`.
`output_folder_prefix`	none	When set, the result is written to a fixed location under `sql_queries/<prefix>/` that is overwritten each run. When omitted, the result lands in the dated `YYYY/MM/DD/<epoch>/` layout.

Query failures are logged, listed in the run summary and raise notifications like any table failure; they do not stop other work.

Historical load

data_retrieval:
  historical_load: true

Set historical_load: true to force a complete re-extract of every table and view on the next run, ignoring CT/CDC pointers. The agent resets the flag to false after the run so that the following run is incremental again. Runs are tagged is_historical_load in the state database's run history so you can tell them apart later.

Use it to re-baseline storage after a manual change, after a lost state database, or when a table's CT retention has lapsed for many tables at once. To rebuild a single table, set that table's sync_mode: full for one run instead.

Cost of a historical load

A historical load rewrites every table. On large databases plan it outside business hours and make sure the output volume and the SQL Server host have headroom. In Community mode it is still subject to the 10,000 row cap per object.

Page Contents