SQL queries and custom extracts | DLH.io Documentation

Ship the results of your own SQL alongside table syncs: aggregations, joins and reports written as CSV or Parquet to dated run folders or to fixed locations that overwrite each run.

sql_queries runs .sql files you control against a configured database and delivers the result set through the same upload pipeline as tables and views. It fills the gap when the shape you need does not exist as a view and you cannot (or would rather not) create one on the source.

When to use it

Aggregations, joins or filters that the source team will not publish as views.
Reports consumed by a spreadsheet or BI tool as a plain CSV, refreshed every run.
Slices of very large tables (for example, last 90 days) where a full table sync would be wasteful.
A stable "latest" file at a fixed path for downstream jobs, plus a dated archive of every run.

Queries run in the context of their database, so table names do not need the database prefix. They are always executed in full every run; Change Tracking and CDC do not apply to query results.

Files on disk

Alongside the agent:

C:\dlh\agent\
  dlh_agent_sql_server.exe
  dlh_agent_config.yaml
  sql_queries\
    DailyRevenueByLocation.sql
    OpenOrdersSnapshot.sql
    CustomerContactExport.sql

DailyRevenueByLocation.sql:

SELECT
  l.LocationCode,
  CAST(o.OrderDate AS date)          AS OrderDay,
  COUNT(*)                            AS OrderCount,
  SUM(o.TotalAmount)                  AS Revenue
FROM dbo.CustomerOrder AS o
JOIN dbo.Location      AS l ON l.LocationId = o.LocationId
WHERE o.OrderDate >= DATEADD(day, -90, CAST(GETDATE() AS date))
GROUP BY l.LocationCode, CAST(o.OrderDate AS date);

OpenOrdersSnapshot.sql:

SELECT o.OrderId, o.CustomerId, o.Status, o.OrderDate, o.PromisedDate, o.TotalAmount
FROM dbo.CustomerOrder AS o
WHERE o.Status IN ('Open', 'Picking', 'Shipped');

Each file must be a single statement (or a batch whose last statement returns a result set) that runs with the agent login's permissions. SET NOCOUNT ON at the top is harmless and avoids row-count messages in the log.

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
  clean_up_output_folder: true
  cloud_upload_strategy: end_of_run
  parquet_compression: zstd
  databases:
    - name: SalesDb
      sync_mode: ct
      schema_refresh_mode: 1D
      tables:
        - name: dbo.*
          all_columns: true
          no_pk_strategy: full
      views: []
      sql_queries:
        # Fixed location, overwritten each run: downstream jobs read one stable path
        - file: ./sql_queries/DailyRevenueByLocation.sql
          output_format: parquet
          output_folder_prefix: reports/daily_revenue
        # Fixed location, CSV for a spreadsheet consumer
        - file: ./sql_queries/OpenOrdersSnapshot.sql
          output_format: csv
          output_folder_prefix: reports/open_orders
        # No prefix: one dated copy per run, kept as an archive
        - file: ./sql_queries/CustomerContactExport.sql
          output_format: parquet

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

Setting	Why
`output_format: delta` at the top, `parquet` and `csv` per query	The global format governs tables and views. Each query chooses its own `csv` or `parquet` (default `parquet`) independently.
`output_folder_prefix: reports/daily_revenue`	Result lands at a fixed path under `sql_queries/` and is overwritten every run, so a consumer can always read the same URL.
No `output_folder_prefix` on the export	Result lands in the dated `YYYY/MM/DD/<epoch>/` folder for that run, so every run's copy is retained.
`cloud_upload_strategy: end_of_run`	Query results (and CSV/Parquet tables) are uploaded after all extraction finishes. `per_table` uploads each result as soon as it is produced if you want them earlier. `direct_cloud_write` does not affect query results; they always go through the upload step.
`clean_up_output_folder: true`	Local copies are removed after a successful upload.

Where the results land

Under the connection's storage prefix (<org>/<prj>/Site003/):

data/delta_iceberg_tables/
  delta/SalesDb.dbo.CustomerOrder/                       table syncs
  sql_queries/
    reports/daily_revenue/SalesDb.sql_query.DailyRevenueByLocation.parquet
    reports/open_orders/SalesDb.sql_query.OpenOrdersSnapshot.csv
2026/09/04/1788202802/
  SalesDb.sql_query.CustomerContactExport.parquet          dated archive copy

The result file name is always <database>.sql_query.<sql file name without extension>.<format>, so keep query file names unique within a database. Keep the .sql files themselves under source control alongside the configuration; the agent does not archive them to storage.

Run behaviour

Queries appear in the per-run summary next to tables and views with their own kind:

  SalesDb   dbo.CustomerOrder                          table   ct          412  OK
  SalesDb   sql_query.DailyRevenueByLocation           query   full       3120  OK
  SalesDb   sql_query.OpenOrdersSnapshot               query   full        857  OK
  SalesDb   sql_query.CustomerContactExport            query   full      48211  OK

A query that fails (syntax error, missing permission, timeout) is reported in the ERRORS block and raises the same notifications as a failed table, but other queries and tables still complete. Queries run after the tables and views of their database.

Keep queries bounded

A query runs against the live source every run, on the same schedule as table syncs. Aggregate or filter in the query (as the 90-day window above does) rather than exporting whole tables; use a normal table entry for that. In Community mode query results are capped at 10,000 rows like tables and views.

Reading a fixed-path result

Snowflake external table over the stable Parquet path, refreshed after each run:

CREATE OR REPLACE EXTERNAL TABLE RAW.SITE003.DAILY_REVENUE_BY_LOCATION
  WITH LOCATION = @dlh_site003_stage/data/delta_iceberg_tables/sql_queries/reports/daily_revenue/
  FILE_FORMAT = (TYPE = PARQUET)
  AUTO_REFRESH = FALSE;

ALTER EXTERNAL TABLE RAW.SITE003.DAILY_REVENUE_BY_LOCATION REFRESH;

Because the file is overwritten in place, downstream readers see the latest run without any path changes. For the dated archive, glob the YYYY/MM/DD/*/SalesDb.sql_query.CustomerContactExport.parquet pattern.

Variations

Several databases: each databases entry has its own sql_queries list, and each query runs in its own database's context.
Deprecated top-level sql_files: older configurations listed SQL files at the top of data_retrieval. Still accepted (each file is injected into every database without its own sql_queries), but move them under the database entry when you next edit the file.
Trigger transformations after the export: pair with a DBDeux job or CI/CD dispatch in CI/CD and DBDeux triggers so the model that reads reports/daily_revenue runs as soon as the file is refreshed.

Page Contents