build-data-reconciliation-pipeline

Build Data Reconciliation Pipeline

Generate an Orchestra pipeline that uses the platform's built-in Data Reconciliation task types to confirm two systems match, rather than hand-rolling comparison SQL in a generic task. Orchestra ships two flavors, and the right migration story usually uses both, one after the other:

• DATA_RECONCILIATION_MANUAL_QUERY — runs one query against each side and diffs the result. This is the full, one-off "prove the migration landed" check on cutover day: every table, row counts plus content-level aggregates.
• DATA_RECONCILIATION_CURSOR_FIELD — compares row count and/or max value of one monotonic column (an id or updated_at), using a cache so it only scans new rows each run. This is the cheap, ongoing drift monitor you schedule after the cutover has already been validated — it's not meant to replace the full check, it's meant to catch future drift without re-scanning everything every time.

Both are restricted to SNOWFLAKE, SQL_SERVER, and DATABRICKS as source/destination — if the user names a different system (Postgres, BigQuery, ...), say so up front; there's no native DataRec task for that pair, and the fallback (independent query tasks plus a Python task doing the diff by hand) is a materially different, more manual pipeline — don't build it silently as if it were the same thing. See references/unsupported-engine-fallback.md for the pattern and a worked example.

References

• references/unsupported-engine-fallback.md — read this first whenever either system isn't SNOWFLAKE/SQL_SERVER/DATABRICKS. The hand-rolled fallback pattern (independent query tasks + a Python diff task) with a worked Postgres↔Snowflake example, validated against the live API.
• references/query-templates.md — read before writing any query. Per-engine SQL for row counts, column-level aggregates, identifier qualification, and — importantly — why timestamps need converting to epoch-seconds and why every task needs an explicit threshold (the single most common way this silently does nothing).
• references/pipeline-patterns.md — the matrix-over-checks authoring pattern (one task group per check kind, fanned out over tables/columns via ${{ MATRIX.x['key'] }}), full example YAML for both the validation and monitor pipelines, and how to handle scope that's a schema/database rather than a fixed table list.
• ../../references/orchestra/pipeline/yaml-authoring.md — base pipeline schema, variable syntax, validation workflow.
• ../../references/orchestra/mcp/tools-quick-ref.md — MCP tool names for validating and registering the pipeline.