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

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)

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.

query

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:

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.

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.

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 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!.

2. 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.

3. 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 and group_columns	✓	✓
cutoff — column, per-side columns, truncate, combine, offset	✓	✓
Annotations, on the suite and per reconciliation	✓	✓
Aggregate thresholds — absolute, percentage, 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	—	✓
Cutoff apply, per-side watermark query, aggregate: MIN	—	✓
Connection parallelism / disabled	—	✓
strict_time_references	—	✓
Several reconciliations authored in one pass	—	✓

Nothing in the right-hand column is out of reach in the app: paste the YAML 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

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