> ## Documentation Index > Fetch the complete documentation index at: https://docs.synq.io/llms.txt > Use this file to discover all available pages before exploring further. # 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](#what-the-wizard-covers-and-what-needs-yaml) is the honest map. ## Anatomy of a suite ```yaml suite.yaml theme={null} # 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: Every field, rendered and browsable. 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: ```yaml suite.yaml theme={null} 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. ```yaml theme={null} 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`. ```yaml theme={null} 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](/reconciliation/time-windows-and-cutoffs#template-variables) 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: ```yaml theme={null} key_columns: [workspace, path] # ✓ orders and filters on the column tuple ``` not ```yaml theme={null} 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: ```yaml theme={null} 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: ```yaml .connections.yaml theme={null} 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 ` 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: ```bash theme={null} 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: ```yaml theme={null} 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. ```yaml theme={null} 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. ```bash theme={null} 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!`. ```bash theme={null} 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. ```bash theme={null} 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](/reconciliation/workspace-suites) 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: ```yaml ecommerce.yaml theme={null} # 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 Which mode to use, aggregate measures, and threshold resolution. Comparing two live systems without inventing differences. Filters, concurrency, JSON output, audit logs and exit codes. Save it, run it on our backend, promote it to production.