Skip to content

Reconciliation modes reference

Reconciliation checks span multiple entities in a delivery — for example, a bookings file that must balance against a totals file. These rules live on the Project (not on transformers, which stay pure mappers), and run during a Session after transform.

Two ways to express a rule

A reconciliation rule is one of two types:

  • Expression (type: "expression", or no type) — written in the expression language and parsed to DuckDB SQL.
  • SQL (type: "sql") — a raw DuckDB SELECT in a sql field, validated by the SQL validator (sqlglot AST) before execution.

Both run in a single shared DuckDB connection per session, after the role Parquets are registered as views and the dictionary and context tables are loaded.

How a SQL rule reports

A SQL rule is a query that returns the offending rows:

  • 0 rows returned → the rule passes.
  • rows returned → the rule fails, with total_mismatches equal to the row count and the first 10 rows kept as samples. If the result has a reconcile_key column, those keys are collected into mismatched_keys.
-- Accounts in the bookings file with no matching chart-of-accounts entry
SELECT b."account" AS reconcile_key
FROM buchungen b
LEFT JOIN dict_buchungen_kontenplan d ON b."account" = d."code"
WHERE d."code" IS NULL

What's available to a rule

  • Role views — each entity role's transformed Parquet is registered as a view you can query by role name (e.g. buchungen).
  • Dictionary tables — each dictionary of a role's entity is loaded as dict_{role}_{dictionary_name} (e.g. dict_buchungen_kontenplan).
  • Context tables — tabular session context is materialized as ctx_{table_name} (e.g. ctx_sap_balances), so you can reconcile against a reference balance supplied with the delivery.

Conceptual modes

Two shapes cover most reconciliations:

  • Keyed — join two roles on a grouping key and compare per key (the unmatched / unbalanced keys become the violations).
  • Global — compare scalar aggregates across roles (e.g. sum of one file vs. a total), typically a cross join of single-row aggregates.

Safety

SQL rules go through the same guard rails as everything else: DDL/DML like DROP TABLE is rejected at parse time, blocked functions like READ_PARQUET / READ_CSV are refused, and a reference to a table that isn't among the registered views/tables is rejected with the available names listed.