Authoring suites

The YAML anatomy of a suite, choosing key columns, and validating before you spend a query

A suite is a YAML file. It declares the connections available and the reconciliations to run, and it is the same file whether it runs on your laptop or on a schedule in production.

YAML is the primary surface. You can write every setting in the file and keep the file in a repository. The app has a form for every setting too, so you can author a suite without writing YAML. Authoring in the app says where each setting is.

Anatomy of a suite

suite.yaml

# yaml-language-server: $schema=https://schemas.synq.io/synq-recon/v1/config.schema.json

name: orders-consistency               # short machine identifier
title: "Orders consistency"            # what people see
description: "Payment gateway vs warehouse"

connections: {}                        # named databases (or, in a workspace, integration names)
reconciliations: {}                    # the reconciliations: one comparison each
variables: {}                          # values interpolated into queries, resolved once per run
annotations: {}                        # labels that follow the suite into the platform
setup: {}                              # SQL to run before, and after, the reconciliations
teardown: {}
strict_time_references: false          # turn NOW()/CURRENT_DATE warnings into errors
execution: {}                          # how long a run may take, and how many reconciliations run at once
comparison: {}                         # the precision of decimal and floating-point columns

Keep that yaml-language-server line at the top of every suite. It points your editor at the published JSON Schema, which gives you field completion, type checks and inline documentation as you type. The loader of synq-recon validates against the same schema.

The full list of fields, with each type, default and constraint, is generated from the code and published:

Configuration reference

Every field, rendered and browsable.

config.schema.json

The machine-readable schema, for editors and validators.

This page explains the shape and the decisions. It deliberately does not restate the field list.

The smallest useful suite

A connection pair and one reconciliation:

suite.yaml

name: orders-consistency
title: "Orders consistency"

connections:
  gateway:
    postgres:
      host: db.internal
      port: 5432
      database: payments
      username: ${PG_USER}
      password: ${PG_PASSWORD}
  warehouse:
    snowflake:
      account: myorg.us-east-1
      warehouse: COMPUTE_WH
      role: ANALYST
      username: ${SF_USER}
      password: ${SF_PASSWORD}
      databases: [ANALYTICS]

reconciliations:
  orders-daily:
    title: "Daily orders"
    source:
      connection: gateway
      table: public.orders
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.ORDERS
    key_columns: [order_id]
    mode: row_checksum

Fourteen databases are supported, each with its own connection block: PostgreSQL, MySQL, MSSQL, Oracle, Snowflake, BigQuery, Redshift, Databricks, ClickHouse, Trino, Athena, Microsoft Fabric, IBM Db2 and DuckDB (local files and MotherDuck). The source and the target can be on different platforms. That is the purpose of the tool.

Choosing the dataset

Each side of a reconciliation is a dataset: a connection plus what to read.

table (preferred)

source:
  connection: warehouse
  table: ANALYTICS.PUBLIC.ORDERS
  columns: [order_id, customer_id, order_date, total_amount]
  where: "region = 'EU'"

With table:, the tool finds the column list for you. Thus it can compare columns that you did not list, warn when the two sides have different columns, and narrow the comparison without SQL from you:

Field	What it does
columns	Compare only these columns.
exclude_columns	Compare everything except these. Resolved at run time, so a new column is picked up automatically.
where	Filter, applied as WHERE (condition). Supports {{ variable }} interpolation.

columns and exclude_columns are mutually exclusive. The table reference accepts a dotted string (db.schema.table) or a structured object with database, schema and name.

query

source:
  connection: gateway
  query: |
    SELECT order_id, customer_id, order_date, total_amount
    FROM public.orders
    WHERE created_at >= '{{ start_date }}'

Use query: when the comparison needs SQL: a join, a union, a calculated column or a function of one platform. The tool then does not find the columns for you, and columns, exclude_columns and where are not accepted with it. Put the filter in the query.

Never put NOW(), CURRENT_DATE or GETDATE() in a query. The two sides evaluate them at slightly different instants, which manufactures differences that are not there. Use a template variable instead. check-config warns about these functions, and strict_time_references: true makes the warning an error.

Key columns decide everything

key_columns: (or key_column:, both accept a string or a list) names the columns that identify a row. It is the most important choice in a suite, because the drill-down sorts and filters on these columns.

Two rules:

1. It must be unique. A non-unique key makes segment boundaries ambiguous and the drill-down unreliable.
2. It should be indexed. Each drill-down level range-filters on the key. With a matching primary key or index, the database prunes; without one, every segment is a full scan. check-config --db tells you which you have.

A key column can be an integer, text, a date or a timestamp, alone or in a composite key. Its type is read from each side, never configured. A timestamp key is compared at the precision both sides hold; when that is not what the two sides agree on, or a side holds local wall clocks, set it with timestamp_precision and the time zone settings.

Declare a composite key; never synthesise one. For a multi-column natural key, write the columns out:

key_columns: [workspace, path]     # ✓ orders and filters on the column tuple

not

key_column: "workspace || '|' || path"   # ✗ forces a full scan per segment

The composite form expands to a lexicographic, dialect-portable range filter over the real columns, so an index on (workspace, path) still prunes. A concatenated expression cannot use that index, and a drill that would have taken seconds takes minutes.

A single key column is written back as the scalar key_column; several as a key_columns list. This is only a format detail of the synq-recon suite yaml output. Write the form that you prefer.

Matching columns across the two sides

Column names that differ only in case match automatically, so user_id on Postgres lines up with USER_ID on Snowflake with no configuration. For genuinely different names, map them:

column_mapping:
  invoice_amount: total_payment
  order_id: transaction_number

An explicit mapping overrides the automatic case matching. Set case_insensitive: false to turn the automatic behaviour off entirely.

Keep credentials out of the suite

Move the whole connections: block into a separate file, git-ignored, keyed by the same connection names:

.connections.yaml

connections:
  gateway:
    postgres:
      host: db.internal
      port: 5432
      database: payments
      username: ${PG_USER}
      password: ${PG_PASSWORD}

The CLI finds .connections.yaml or connections.yaml in the working directory or in a parent directory. --connections <path> names a file explicitly. Commit a .connections.yaml.example with ${ENV_VAR} placeholders so the next person knows which names to fill in.

This is also important for backend runs. You can safely upload-config a suite that has no credentials, and the same file then runs on the backend, where the connection names bind to workspace integrations. To make a matching skeleton from the integrations that you have, run:

synq-recon connections remote bootstrap --recon-deployable > .connections.yaml

The values are ${ENV} placeholders. The command does not read or write secrets.

Anywhere in a suite, ${VAR} reads an environment variable and ${VAR:-default} supplies a fallback.

Setup and teardown

SQL to run before and after the reconciliations, for each connection. Use it to stage a snapshot, to materialise a view, or to build fixtures in a test suite:

setup:
  gateway:
    - CREATE TEMP TABLE staging AS SELECT * FROM raw_data
teardown:
  gateway:
    - DROP TABLE IF EXISTS staging
teardown_on_failure: true     # run teardown also when a reconciliation fails
ignore_setup_errors: false    # log setup errors as warnings and keep going

setup_file and teardown_file take a path per connection instead of inline statements. Both blocks also exist per reconciliation, where teardown_on_failure and ignore_setup_errors inherit the suite’s value unless you set them.

A file path is correct only on the computer that runs synq-recon. upload-config reads the files and copies their SQL into the suite, so the suite also runs in the workspace. The app cannot read your files. Thus Upload config in the app refuses a suite that has setup_file or teardown_file. In the app, write the SQL under setup and teardown. If only your local runs need the files, put them in a local: block.

ignore_setup_errors: true is correct for bootstrap SQL that you can run again safely (CREATE TABLE IF NOT EXISTS). It is not correct when the setup makes the data that you compare: a failed setup then gives two empty tables, and they match.

Settings for local runs only

Some settings are correct only when synq-recon runs on your computer: setup that builds test fixtures, and reporting.level: detailed, which Coalesce Quality refuses. Put them in a local: block, at the suite level or in a reconciliation. Then one suite file works for your local tests and for the workspace:

local:
  setup_file:
    test-db: fixtures/orders.sql
  teardown:
    test-db: DROP TABLE IF EXISTS orders
reconciliations:
  orders:
    reporting:
      level: with_keys
    local:
      reporting:
        level: detailed
        consent_acknowledged: true

A local: block can hold setup, setup_file, teardown, teardown_file and reporting. Any other setting in it is an error.

• run, run-check, run-drill, check-config and plan merge the block into the suite.
• A connection under local.setup or local.teardown replaces the setup or teardown of that connection outside the block. The other connections keep their queries.
• A field under local.reporting replaces the same field outside the block. The fields that it does not set keep their values.
• The suite’s local.reporting applies to every reconciliation. The local.reporting of a reconciliation takes precedence.
• upload-config does not send the block and does not read its files. Upload config in the app drops the block. Thus the workspace never runs your fixtures against its warehouse, and suite yaml does not show the block.

check-config and the app check the block. They report a problem in it, such as an unknown connection or detailed without consent_acknowledged, at its line in the file.

Annotations

Name/value labels on a suite or a single reconciliation. Reconciliation-level annotations merge with the suite’s, and they follow a promoted suite into the platform, where they annotate the resulting assets and checks.

annotations:
  team: data-platform
  domain: [revenue, billing]
  critical:

The map form above and the list form are equivalent. The loader changes both to a sorted list, so a change of order only never shows in the version history. Names and values cap at 50 characters, with at most 20 values per name.

Run timeout and parallelism

How a run of the suite executes. These settings are stored with the suite, so a local run, an ad-hoc backend run and a Production deployment read the same values:

execution:
  timeout: 30m                 # wall-clock budget for the whole run
  concurrency: 4               # reconciliations running at the same time
  reconciliation_timeout: 5m   # wall-clock budget for each reconciliation

All three are optional. Without timeout, a local run stops after 5 minutes and a backend run after 15 minutes. Without concurrency the reconciliations run one after another, so one slow table holds up the rest.

A run that hits its timeout is cancelled. Reconciliations that finished keep their results, the ones still running are reported as failed.

Without reconciliation_timeout, a single slow query, like a cutoff watermark over an unindexed column, can use up the run’s whole timeout, and every reconciliation after it then fails without running. With it, a reconciliation that runs past its budget (setup, check and any drill together) is reported as failed with a timeout, and the run moves on to the next one. Set timeout on a reconciliation to give that one a different budget:

orders_sync:
  timeout: 20m   # this one scans a large table; overrides reconciliation_timeout

A reconciliation’s budget never extends the run: whichever runs out first applies. It counts the time the reconciliation itself runs, so with auto-drill a drill that waits for the other reconciliations’ checks to finish still gets what its own check left over.

To bound each individual query rather than the whole reconciliation, use error_handling.query_timeout. It covers the reconciliation’s setup and teardown statements too. A query that hits it fails with a timeout and is not retried, because a query that took that long usually takes that long again.

If the query that times out is a cutoff watermark, point cutoff.query at something cheaper to read than the full table, like a load-metadata table or the most recent partition, or give the reconciliation a window: its watermark is then read from inside the window only.

Teardown still runs after the run’s timeout expires, as long as teardown_on_failure asks for it.

Each reconciliation that runs in parallel sends queries to both of its databases at the same time. Thus a higher concurrency puts more load on them. In a local run, parallelism: on a connection sets the maximum number of queries that run on it at the same time. The default is 8. With disabled: true on a connection, a local run does not run the reconciliations that use that connection in a dataset, in setup or in teardown, and does not send the suite’s setup and teardown to it. If --include names one of these reconciliations, the command fails. These two settings apply only to local runs. A suite in the workspace uses integrations for its connections, and these settings do not apply to it.

A more specific setting wins over the suite’s:

Where	Timeout	Concurrency
Local run	--timeout	--concurrency
Backend run	The value in the Run dialog, or --execution-timeout on run-remote and trigger	—
Production deployment	The deployment’s own execution timeout, set on promote or deployment update	—

A backend run limits the timeout to a supported range, and can use less concurrency than the suite sets. reconciliation_timeout has no per-run override: every run reads it from the suite. In the app, all three settings are on the suite’s Execution tab, and a reconciliation’s own timeout is on its Overview tab.

Validate before you spend a query

Three steps, cheapest first. Do not skip ahead; each one catches a class of problem the next would surface as a confusing mid-run error.

1. Parse and check the suite at no cost

   synq-recon check-config suite.yaml
   

   Parses the YAML, checks required fields, and warns about time-dependent SQL. It shows all the errors that it finds. Never connects to a database. Move on when it prints Configuration check passed!.

2. Check the wiring

   synq-recon check-config suite.yaml --connections .connections.yaml
   

   Makes sure that each connection that a reconciliation uses is defined. If the connection: values of your suite are workspace integration ids and not local definitions, the check without a connections file reports no connections defined. This step tells you why.

3. Check against the databases

   synq-recon check-config suite.yaml --db
   

   Connects to every connection and sends each query to the query planner of the database with LIMIT 0. It scans nothing and reads no rows. It reports the resolved columns, table size, primary and partition keys, a suggested key column, and a warning when the key column you chose is not indexed. For a reconciliation with comparison settings, it also reports each column that numeric_scale: exact cannot compare.

   Move on when every connection is OK and every query validates. A failure here is a wrong table name, a missing grant, or a column that does not exist.

--db does not run the setup: SQL of the suite. If the setup makes the tables, every query fails here. This is expected. Start a run instead.

On BigQuery, the table analysis in --db enumerates dataset metadata rather than jumping to the tables you named, so its cost scales with the size of the warehouse rather than the size of your suite. For this reason, the analysis runs only in this interactive command, and not before every run.

Authoring in the app

In the app, Health → Reconciliations → Development has a New suite wizard with four steps: Data sources, Comparison, Suite info and Review. It also has an Upload config button, which opens a dialog that accepts a suite as pasted or uploaded YAML.

The wizard is quicker for a first suite and better at discovery: it searches your catalog, so you can pick a table you already have rather than typing warehouse coordinates, and it shows which columns are keys or indexed while you choose. On the Review step, Create and add another saves the suite and starts the next reconciliation in it.

After you save a suite, you can change every setting in a form. Open the suite for the settings of the whole suite, and open a reconciliation for its own settings:

Setting	Where in the app
Source and target, by catalog entity, typed table or SQL query, and where	Wizard, and the reconciliation’s Data sources tab
columns or exclude_columns, and as_of time travel	Wizard, and Data sources
Key columns, mode, compared columns and column_mapping	Wizard, and the reconciliation’s Comparison tab
case_insensitive, and hash_algorithm in row_checksum mode	Wizard, and Comparison under Matching
Bisection: enabled (false stops the drill-down in every run except run-drill and drill-deeper), factor, threshold, strategy, time column and granularity	Wizard, and Comparison
Aggregate measures (including COUNT(*)), group_columns, thresholds and their per_column / per_measure overrides	Wizard, and Comparison
row_count.thresholds	Wizard, and Comparison
comparison: numeric precision, timestamp precision and time zones, also per column	In row_checksum mode, the wizard and Comparison under Precision. For the whole suite, the suite’s Settings tab
cutoff and window, every setting	Wizard, and Comparison
reporting: level and sample limit	The reconciliation’s Advanced tab
error_handling	Advanced
setup, teardown, teardown_on_failure, ignore_setup_errors	Advanced for a reconciliation, Settings for the suite
execution, strict_time_references	The suite’s Settings tab
variables	The suite’s Variables tab, which also shows where each variable is used
Annotations, title and description	Overview of the suite and of a reconciliation

Some settings are for local runs only, and the app does not offer them:

• reporting.level: detailed. A run in Coalesce Quality never reads row values, so it refuses this level. A suite that still has it cannot run until you pick another level. To keep it for your local runs, put it in a local: block.
• setup_file and teardown_file. The app has no files. upload-config copies the SQL of each file into the suite.
• A local: block. The app drops it when you upload a suite.
• Connection parallelism and disabled. A workspace suite names its connections only, and binds each one to an integration.

While you edit, the app checks the suite as check-config does and shows each problem next to its field. In a reconciliation, each tab shows how many problems it has. You cannot save a suite that has an error, but you can save one with warnings.

Validate in the warehouse

The checks while you edit do not connect to a warehouse. Validate in warehouse does the checks of check-config --db on a workspace suite. It uses the same code as the CLI, so the two give the same result.

In the app, the button is on the suite and in each reconciliation’s editor. You need the permission to run the suite.

• On the suite, it checks the saved version.
• In a reconciliation, it checks your changes before you save them. If you did not change anything, it checks the saved version.
• Each problem shows next to its field, together with the problems of the checks while you edit, and is marked “found in the warehouse”. A problem that has no field, for example a connection that only setup or teardown uses, shows under the button.
• If a connection does not bind to an integration, the app asks you to pick one, as for Run.
• Validation runs only when you click the button. When you change the suite after a validation, the app says that the result is out of date. Click the button again to see the problems of the current version.

In the API, call SuiteConfigService.CheckSuiteInWarehouse:

1. Send the suite_id of a saved suite, or a suite that is not saved, such as a draft.
2. Optionally, send connection_mappings to bind each connection to an integration, as for a backend run. A connection without a mapping binds to the integration with the same name.
3. Read the issues of the response. Each issue has a severity, the path of its field and a message, in the same form as the checks while you edit.

The check plans each query with LIMIT 0, so it scans no table. It reports a query that refers to a table or column that does not exist, each column that its comparison settings cannot be applied to, and a key column that no index covers. It does not run setup SQL. It has the same time limit as a run of the suite.

If the suite has an error that check-config finds, the check does not connect, and the response has those errors. If a connection does not bind, for example because backend runs are not enabled for the integration, the request fails with FAILED_PRECONDITION and names the connection.

If someone else saved the suite after you opened it, the app does not overwrite their version. It shows who saved it and when. Choose Reload to see their version, or Save mine anyway to save yours over it. Every save is a new version. On the suite’s History tab, See changes shows what a version changed, and Restore makes an older version the newest one again.

To change the whole suite as YAML, open the suite, open its menu (⋮) and choose Edit YAML, or choose Edit suite YAML in a reconciliation. The editor suggests keys from the suite schema (press Ctrl+Space) and marks each problem on its line. A save from the app rewrites the YAML, so comments, key order and environment placeholders are lost. If you keep the suite in a repository and upload it from CI, make the change in the repository, because the next upload replaces a change made in the app. The app tells you when the suite was last saved from the CLI or the API.

Two capabilities exist only in the app, and are worth knowing about:

• Authoring from the catalog. The entity picker resolves a table you have already catalogued to its warehouse coordinates. From the CLI you supply coordinates yourself.
• Execution-plan preview. The app renders the resolved plan for a stored suite before you run it.

A complete example

Two reconciliations over the same pair of systems, in two different modes: row checksums for orders, and grouped totals for inventory, where a row-by-row comparison is the wrong tool:

ecommerce.yaml

# yaml-language-server: $schema=https://schemas.synq.io/synq-recon/v1/config.schema.json

name: ecommerce
title: E-commerce order and inventory reconciliation

connections:
  gateway:
    postgres:
      host: db.internal
      port: 5432
      database: payments
      username: ${PG_USER}
      password: ${PG_PASSWORD}
  warehouse:
    snowflake:
      account: myorg.us-east-1
      warehouse: COMPUTE_WH
      role: ANALYST
      username: ${SF_USER}
      password: ${SF_PASSWORD}
      databases: [ANALYTICS]

annotations:
  team: data-platform
  domain: [revenue]

reconciliations:
  ecommerce-orders:
    title: Orders (payment gateway vs warehouse)
    description: Row checksums. Finds missing orders and changed amounts.
    source:
      connection: gateway
      table: public.orders
      columns: [order_id, customer_id, order_date, total_amount, payment_status]
    target:
      connection: warehouse
      table: ANALYTICS.PUBLIC.ORDERS
      columns: [order_id, customer_id, order_date, total_amount, payment_status]
    key_columns: [order_id]
    mode: row_checksum

  inventory-by-sku:
    title: Inventory counts (warehouse vs ERP)
    description: Aggregate totals per SKU. Returns totals, not rows.
    source:
      connection: warehouse
      table: ANALYTICS.PUBLIC.INVENTORY
    target:
      connection: gateway
      table: public.erp_inventory
    key_columns: [sku]
    mode: aggregate
    aggregate:
      measures:
        - column: quantity
          function: [SUM, COUNT]
      group_columns: [sku, location]
      thresholds:
        absolute: 0

Next

Comparison modes

Which mode to use, aggregate measures, and threshold resolution.

Time windows and cutoffs

Comparing two live systems without inventing differences.

Running locally

Filters, concurrency, JSON output, audit logs and exit codes.

Workspace suites

Save it, start a backend run, promote it to Production.