Authoring suites - Coalesce Quality Documentation

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. The app’s wizard is a fast path to a common shape, and the app can also take a suite as pasted YAML — but everything the product can do is expressible in the file, and some things are only expressible there. What needs YAML is the honest map.

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 comparisons themselves
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 comparisons
teardown: {}
strict_time_references: false           # turn NOW()/CURRENT_DATE warnings into errors
execution: {}                          # how long a run may take, and how many comparisons run at once

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 checking and inline docs as you type — and it is the same schema our loader validates against.

The exhaustive field list — every field, 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

Thirteen databases are supported, each with its own connection block: PostgreSQL, MySQL, MSSQL, Oracle, Snowflake, BigQuery, Redshift, Databricks, ClickHouse, Trino, Athena, Microsoft Fabric and DuckDB (local files and MotherDuck). Source and target need not be the same platform — that is the point.

Choosing the dataset

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

table (preferred)
query

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

With table: the tool resolves the column list for you, which means it can compare columns you did not have to enumerate, warn when the two sides disagree on shape, and narrow the comparison without you writing SQL:

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.

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

Reach for query: when the comparison genuinely needs SQL — a join, a union, a computed column, a platform-specific function. You give up the automatic column resolution, and columns, exclude_columns and where are not accepted alongside it (put the predicate 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 this, and strict_time_references: true makes it an error.

Key columns decide everything

key_columns: (or key_column: — both accept a string or a list) names the column tuple that identifies a row. It is the single most consequential choice in a suite, because the drill-down orders and range-filters on it. Two rules:

It must be unique. A non-unique key makes segment boundaries ambiguous and the drill-down unreliable.
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.

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. That is only a formatting detail of synq-recon suite yaml output — write whichever 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 auto-discovers .connections.yaml or connections.yaml in the working directory; --connections <path> points at one explicitly. Commit a .connections.yaml.example with ${ENV_VAR} placeholders so the next person knows which names to fill in.

This is not only hygiene. A suite with no credentials in it is a suite you can safely upload-config to your workspace, and the same file then runs on our backend, where the connection names bind to workspace integrations instead. Generate a matching skeleton from the integrations you already have:

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

Values come out as ${ENV} placeholders — no secrets are fetched or written.

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 comparisons, per connection. Useful for staging a snapshot, materialising a view, or building 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 even when a comparison 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.

ignore_setup_errors: true is right for idempotent bootstrap SQL (CREATE TABLE IF NOT EXISTS) and wrong when the setup is what produces the data being compared — you would be comparing two empty tables and calling it a match.

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 shorthand above, and the canonical list form, are equivalent — the loader normalises to a sorted list, so version-history diffs never show order-only churn. 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, stored with the suite so a local run, an ad-hoc run in the workspace and a production deployment all 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 run in the workspace after 15. 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. Teardown still runs after the run’s timeout expires, as long as teardown_on_failure asks for it. Each reconciliation running in parallel queries both warehouses at once, so concurrency multiplies the load on them. Every connection still caps its own parallel queries with parallelism: (default 8). A more specific setting wins over the suite’s:

Where	Timeout	Concurrency
Local run	`--timeout`	`--concurrency`
Run in the workspace	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`	—

Runs in the workspace clamp the timeout to a supported range and may cap concurrency lower than the suite asks for. 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.

Parse and sanity-check — costs nothing

synq-recon check-config suite.yaml

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

Check the wiring

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

Confirms every connection a reconciliation refers to actually resolves. If your suite’s connection: values are workspace integration ids rather than local definitions, offline validation reports no connections defined — this is the step that tells you why.

Check against the databases

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

Connects to every connection and runs each query through the database’s planner with LIMIT 0 — no scan, 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.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 suite’s setup: SQL, so a suite whose tables are created by setup will report every query as failed. That is expected — go straight to a run.

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. That is why the analysis is confined to this interactive command and does not run before every comparison.

What the wizard covers and what needs YAML

In the app, Health → Reconciliations → Development has a new-suite wizard with four steps — Data Sources, Comparison, Suite Info, Review — and an Upload Suite Config dialog that takes 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. It covers a deliberate subset of the format:

Setting	Wizard	YAML
Source and target, by catalog entity or typed table	✓	✓ (coordinates only)
Raw SQL query as a dataset	✓	✓
`where` on a table dataset	✓	✓
Key columns, including composite	✓	✓
Mode — `row_count`, `row_checksum`, `aggregate`	✓	✓
Compared columns and `column_mapping`	✓	✓
Bisection: `enabled`, `factor`, `threshold`, `strategy`, time column and granularity	✓	✓
Aggregate `measures`, including several functions per measure, and `group_columns`	✓	✓
Aggregate `thresholds` — `absolute`, `percentage`, `percentage_mode`	✓	✓
`cutoff` — every setting: column or per-side columns, `aggregate`, per-side watermark `query`, `truncate`, `combine`, `offset`, `apply` column and operator	✓	✓
Annotations, on the suite and per reconciliation	✓	✓
Aggregate `thresholds` overrides — `per_column`, `per_measure`	—	✓
`variables` (the app displays them read-only)	—	✓
`window` — sliding or fixed lookback	—	✓
`as_of` time travel	—	✓
`exclude_columns`	—	✓
`reporting` — privacy level, sample limit	—	✓
`setup` / `teardown` / `*_file`	—	✓
`hash_algorithm`, `case_insensitive`, `error_handling`	—	✓
Connection `parallelism` / `disabled`	—	✓
`strict_time_references`	—	✓
`execution` — run timeout and concurrency (also on the suite’s Execution tab)	—	✓
Several reconciliations authored in one pass	—	✓

The same forms edit a stored reconciliation. Anything they cannot edit is listed under Configured in YAML when you open it, and saving the form keeps those settings exactly as they are.

Nothing in the right-hand column is out of reach in the app: open the suite and choose Edit YAML to change the whole suite as YAML, or paste a new one into Upload Suite Config. The practical workflow for anything past the basics — and the only workflow for an agent — is to author the file, validate it with check-config, and save it to the workspace with upload-config.

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-level checksums for orders, and grouped totals for inventory, where row-level comparison would be the wrong instrument:

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-level checksum — catches 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 — no row-level access needed.
    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

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, run it on our backend, promote it to production.

​Anatomy of a suite

Configuration reference

config.schema.json

​The smallest useful suite

​Choosing the dataset

​Key columns decide everything

​Matching columns across the two sides

​Keep credentials out of the suite

​Setup and teardown

​Annotations

​Run timeout and parallelism

​Validate before you spend a query

​What the wizard covers and what needs YAML

​A complete example

​Next

Comparison modes

Time windows and cutoffs

Running locally

Workspace suites

Anatomy of a suite

The smallest useful suite

Choosing the dataset

Key columns decide everything

Matching columns across the two sides

Keep credentials out of the suite

Setup and teardown

Annotations

Run timeout and parallelism

Validate before you spend a query

What the wizard covers and what needs YAML

A complete example

Next