@@ -19,6 +19,8 @@ target: |
19 | 19 | primary_keys: ["user_id"] |
20 | 20 | ``` |
21 | 21 |
|
| 22 | +`primary_keys` must name at least one column, and together the keys must be unique on each side. |
| 23 | + |
22 | 24 | File sources may omit `type` (it defaults to `file`) and continue to use `path`, `format`, and optional `options`. |
23 | 25 |
|
24 | 26 | ### File formats |
@@ -49,25 +51,29 @@ uv add 'veridelta[iceberg]' |
49 | 51 | uv add 'veridelta[all]' |
50 | 52 | ``` |
51 | 53 |
|
52 | | -Do not commit `password` or `access_token` in YAML. Inject secrets from the environment or your orchestrator's secret store at runtime. |
| 54 | +Do not commit `password` or `access_token` in YAML. The loader does not expand environment variables, so either render the file from your secret store at runtime, or build the connection in Python (for example `SnowflakeConfig(..., password=os.environ["SNOWFLAKE_PASSWORD"])`) and pass it to `DiffEngine.run_from_configs`. |
53 | 55 |
|
54 | | -Same-warehouse SQL pushdown runs only when both sides are Snowflake or both sides are Databricks, the connection fields match (`account`, `user`, `warehouse`, `database`, `schema_name`, `role`, and `password` for Snowflake; `server_hostname`, `http_path`, `access_token`, `catalog`, and `schema_name` for Databricks), and the `table` names differ. Mixed file/lakehouse and warehouse backends, or Snowflake paired with Databricks, raise `ConnectorError`. |
| 56 | +Same-warehouse SQL pushdown runs only when both sides are Snowflake or both sides are Databricks, the connection fields match (`account`, `user`, `warehouse`, `database`, `schema_name`, `role`, and `password` for Snowflake; `server_hostname`, `http_path`, `access_token`, `catalog`, and `schema_name` for Databricks), and the `table` names differ; naming the same table twice raises `ConfigError`, since a table compared with itself always matches. Mixed file/lakehouse and warehouse backends, or Snowflake paired with Databricks, raise `ConnectorError`. |
55 | 57 |
|
56 | | -`table` must be one to three unquoted identifier segments (`EVENTS`, `schema.table`, or `catalog.schema.table`). Pattern-only `DiffRule` entries are not compiled to SQL; they raise `ConnectorError` on the warehouse path. |
| 58 | +`table` must be one to three unquoted identifier segments (`EVENTS`, `schema.table`, or `catalog.schema.table`). Rules that select columns by `pattern` are matched against the probed column names before any SQL is compiled, so they apply in the warehouse exactly as they do locally. |
57 | 59 |
|
58 | 60 | Pushdown issues eight statements per run: a zero-row column probe and a `COUNT(*)` per side, then inner-join mismatches, target-only added rows, source-only removed rows, and a per-column mismatch tally. Those fill every `DiffSummary` field including `column_mismatches`, so `threshold`, `match_rate_percentage`, and the drift report mean the same thing they do for local comparisons. |
59 | 61 |
|
60 | | -Every column present on both sides is compared, exactly as it is locally. Columns without an explicit rule inherit the global `default_*` settings, so a `default_absolute_tolerance` applies in the warehouse too. Columns marked `ignore` are excluded, and `rename_to` pairs a source column with its renamed target counterpart. |
| 62 | +Every column present on both sides is compared, exactly as it is locally. Columns without an explicit rule inherit the global `default_*` settings, so a `default_absolute_tolerance` applies in the warehouse too. As in a local run, a tolerance only loosens a column that is numeric once normalized, such as a text column with `cast_to: Float64`; text, boolean, and temporal columns are compared exactly. Columns marked `ignore` are excluded, and `rename_to` pairs a source column with its renamed target counterpart. |
61 | 63 |
|
62 | | -The column probes enforce `schema_mode` and primary-key existence before any comparison runs, raising `ConfigError` on drift. Probed names are compared exactly as the compiler quotes them, with no case folding, so YAML identifiers must match the stored column case (Snowflake stores unquoted names uppercase). |
| 64 | +The column probes enforce `schema_mode` and primary-key existence before any comparison runs, raising `ConfigError` on drift. Probed names are compared exactly as the compiler quotes them, with no case folding, so YAML identifiers must match the stored column case (Snowflake stores unquoted names uppercase). `normalize_column_names` cannot change that: pushdown raises `ConfigError` if it would rename a stored column. |
63 | 65 |
|
64 | | -All nine transform stages compile, so a rule means the same thing in a warehouse as it does locally. Two behaviors still differ from the file and lakehouse path: |
| 66 | +All nine transform stages compile for compared columns, so a rule means the same thing in a warehouse as it does locally. These behaviors still differ from the file and lakehouse path: |
65 | 67 |
|
66 | 68 | - Artifacts contain primary keys only, since the comparison SQL never projects full rows. They are written as `added_rows_pks_only`, `removed_rows_pks_only`, and `changed_rows_pks_only` so they cannot be confused with local artifacts, which hold complete records. |
67 | | -- Primary-key uniqueness is not verified, so duplicate keys inflate the inner-join mismatch count instead of raising `DataIntegrityError`. |
| 69 | +- Primary keys are joined as stored. Stages 1 through 7 normalize compared columns but not key columns, so keys that differ only by a transform (a `case_insensitive` or `pad_zeros` rule, or a global `default_whitespace_mode`) are reported as added and removed rather than matched. |
| 70 | +- Primary-key uniqueness is not verified. Duplicate keys inflate the mismatch count, or pass unnoticed when the duplicated rows are identical, instead of raising `DataIntegrityError`. |
| 71 | +- `strict_types` applies to local runs only. When the two relations store a column as different types, the warehouse compares them under its own coercion rules. |
68 | 72 |
|
69 | 73 | Parity is verified by a differential test harness that runs both engines over the same frames and compares the results. The harness executes compiled SQL through DuckDB, which catches semantic errors -- null propagation, three-valued logic, operator precedence -- but cannot catch vendor-specific divergence. Snowflake and Databricks spellings are pinned by direct assertions on the emitted SQL instead. |
70 | 74 |
|
| 75 | +Write `regex_replace` patterns, `value_map` entries, and text `null_values` exactly as you would for a local run. Each is escaped for the target warehouse's string-literal rules, so a backslash in `\d` or `\N` and an apostrophe in `O'Brien` arrive intact; do not double them yourself. Escaping preserves the text, but each warehouse still runs its own regex engine: keep replacements free of capture-group references, which Polars and Databricks write as `$1` and Snowflake as `\1`. Likewise `whitespace_mode` trims only spaces in a warehouse, where Polars also strips tabs and line breaks. |
| 76 | + |
71 | 77 | Two stages need explaining: |
72 | 78 |
|
73 | 79 | - `pad_zeros` compiles to a sign-aware, non-truncating expression rather than a bare `LPAD`, which pads in front of a minus sign (`0-12` where Python gives `-012`) and discards characters past the target width. |
@@ -123,9 +129,9 @@ veridelta run -c veridelta.yaml --quiet |
123 | 129 | veridelta run -c veridelta.yaml --html report.html --html-max-rows 1000 |
124 | 130 | ``` |
125 | 131 |
|
126 | | -`--json` prints `DiffSummary` as JSON on stdout. `--quiet` suppresses progress chatter on stderr (the JSON line still prints). Progress chatter always goes to stderr, so `veridelta run --json | jq` does not have to strip anything first. `--html` writes a standalone report with no CDN references, capped at `--html-max-rows` (default 1000) so a large diff cannot produce an unopenable file. Pushdown reports are labeled as primary-keys-only. |
| 132 | +`--json` prints `DiffSummary` as JSON on stdout. `--quiet` suppresses progress chatter on stderr (the JSON line still prints). Progress chatter always goes to stderr, so `veridelta run --json | jq` does not have to strip anything first. `--html` writes a standalone report with no CDN references, capped at `--html-max-rows` (zero or more; default 1000) so a large diff cannot produce an unopenable file. Pushdown reports are labeled as primary-keys-only. |
127 | 133 |
|
128 | | -Exit codes stay at `0` for a match within `threshold` and `1` for drift or any failure. |
| 134 | +Exit codes are `0` for a match within `threshold`, `1` for drift or any failure while running, and `2` for invalid command-line arguments. |
129 | 135 |
|
130 | 136 | ```yaml |
131 | 137 | source: |
@@ -224,10 +230,10 @@ Global directives control the strictness of the underlying Polars evaluation eng |
224 | 230 | | :--- | :--- | |
225 | 231 | | `schema_mode` | Enforces column structure constraints. Options: `intersection` (default, compares common columns only), `exact`, `allow_additions`, `allow_removals`. | |
226 | 232 | | `strict_types` | If `false` (default), the engine dynamically soft-casts target columns to source types to prevent execution halts on mismatched types. If `true`, type mismatches automatically fail the row. | |
227 | | -| `normalize_column_names`| If `true`, strips whitespace and lowercases all column headers prior to schema alignment. | |
| 233 | +| `normalize_column_names`| If `true`, strips whitespace and lowercases all column headers prior to schema alignment, on every entry point including `DiffEngine(...).run()` and `validate_schemas`. Configured `primary_keys`, `column_names`, and `rename_to` are normalized the same way; a `pattern` is not, so write it against the lowercase names. Headers that collide once normalized raise `ConfigError`. | |
228 | 234 | | `threshold` | The allowable mismatch ratio (0.0 to 1.0) before the pipeline exits with a failure code. | |
229 | | -| `default_absolute_tolerance` | Global absolute numeric tolerance. A column without its own `absolute_tolerance` inherits this. | |
230 | | -| `default_relative_tolerance` | Global relative numeric tolerance. A column without its own `relative_tolerance` inherits this. | |
| 235 | +| `default_absolute_tolerance` | Global absolute numeric tolerance. A column without its own `absolute_tolerance` inherits this. Columns that are not numeric after normalization are compared exactly. | |
| 236 | +| `default_relative_tolerance` | Global relative numeric tolerance. A column without its own `relative_tolerance` inherits this. Columns that are not numeric after normalization are compared exactly. | |
231 | 237 | | `default_treat_null_as_equal` | Global `NULL == NULL` policy. Defaults to `true`. A column rule can override it. | |
232 | 238 | | `default_whitespace_mode` | Global whitespace stripping: `none` (default), `left`, `right`, or `both`. | |
233 | 239 | | `default_null_values` | Global sentinel list. Applied only to columns whose type can hold each value. | |
@@ -266,14 +272,14 @@ It raises `ConfigError` on a violation and returns nothing otherwise. |
266 | 272 |
|
267 | 273 | ## Column-Level Overrides (Rules) |
268 | 274 |
|
269 | | -The `rules` array defines granular, per-column or regex-pattern tolerances. A rule selects columns by exact `column_names` or by a regular expression in `pattern`, and every other field is optional. When a column is named by more than one rule, the first rule listing it by exact name wins, then the first whose `pattern` matches. |
| 275 | +The `rules` array defines granular, per-column or regex-pattern tolerances. A rule selects columns by exact `column_names` or by a regular expression in `pattern`, and every other field is optional. When a column is named by more than one rule, the first rule listing it by exact name wins, then the first whose `pattern` matches. One rule governs each column, `ignore` included, so an exact-name rule keeps a column that a broader ignore `pattern` would otherwise drop. A renamed column answers to both spellings: a rule listing its target name wins, then the rule listing its source name. Local runs and warehouse pushdown resolve rules the same way. |
270 | 276 |
|
271 | 277 | | Field | Description | |
272 | 278 | | :--- | :--- | |
273 | 279 | | `column_names` | Exact source column names this rule governs. | |
274 | | -| `pattern` | Regular expression matched against the start of each column name. Pattern-only rules are not compiled to warehouse SQL and raise `ConnectorError` there. | |
275 | | -| `absolute_tolerance` | Maximum absolute numeric difference. Overrides `default_absolute_tolerance`. | |
276 | | -| `relative_tolerance` | Maximum relative numeric difference (`0.01` is 1%). Overrides `default_relative_tolerance`. | |
| 280 | +| `pattern` | Regular expression matched against the start of each column name. | |
| 281 | +| `absolute_tolerance` | Maximum absolute numeric difference. Overrides `default_absolute_tolerance`. Must be finite; use `ignore` to stop comparing a column. | |
| 282 | +| `relative_tolerance` | Maximum relative numeric difference (`0.01` is 1%). Overrides `default_relative_tolerance`. Must be finite. | |
277 | 283 | | `case_insensitive` | Lowercase text before comparing. | |
278 | 284 | | `whitespace_mode` | `none`, `left`, `right`, or `both`. Overrides `default_whitespace_mode`. | |
279 | 285 | | `regex_replace` | Mapping of regex pattern to replacement, applied in order to text columns. | |
@@ -284,7 +290,7 @@ The `rules` array defines granular, per-column or regex-pattern tolerances. A ru |
284 | 290 | | `datetime_format` | `strptime` pattern that parses text into timestamps. | |
285 | 291 | | `timezone` | Zone that timezone-aware timestamps are converted to. | |
286 | 292 | | `cast_to` | `Int64`, `Float64`, `String`, `Boolean`, `Date`, or `Datetime`. | |
287 | | -| `ignore` | Exclude the matched columns from the comparison entirely. | |
| 293 | +| `ignore` | Exclude the columns this rule governs from the comparison entirely. | |
288 | 294 | | `rename_to` | Target name for a single source column. | |
289 | 295 |
|
290 | 296 | ### Transform order |
@@ -301,7 +307,7 @@ Rules are not applied in the order you write them. Every column follows one fixe |
301 | 307 | 8. Comparison (equality, or numeric tolerance) |
302 | 308 | 9. Null-safe equality (`treat_null_as_equal`) |
303 | 309 |
|
304 | | -Stages 1 through 7 normalize each dataset on its own, before any join. That means they apply to primary keys as well: a `case_insensitive` rule on a key column changes how rows are matched, not just how they are compared. If normalizing a key collapses two rows into one, `DataIntegrityError` is raised rather than allowing a join explosion. |
| 310 | +Stages 1 through 7 normalize each dataset on its own, before any join. In local runs that means they apply to primary keys as well: a `case_insensitive` rule on a key column changes how rows are matched, not just how they are compared. If normalizing a key collapses two rows into one, `DataIntegrityError` is raised rather than allowing a join explosion. Warehouse pushdown joins keys as stored; see [Warehouse and lakehouse sources](#warehouse-and-lakehouse-sources). |
305 | 311 |
|
306 | 312 | Stages 2, 3, and 4 operate on text and are skipped for non-string columns, so a global `default_whitespace_mode` is safe to set on a mixed schema. Stage 1 is filtered per column instead, as described below. |
307 | 313 |
|
@@ -401,3 +407,14 @@ rules: |
401 | 407 | - column_names: ["legacy_customer_id"] |
402 | 408 | rename_to: "customer_id" |
403 | 409 | ``` |
| 410 | + |
| 411 | +The rule's other settings apply to the renamed pair on both sides, so a rename can carry a tolerance or a transform: |
| 412 | + |
| 413 | +```yaml |
| 414 | +rules: |
| 415 | + - column_names: ["legacy_amt"] |
| 416 | + rename_to: "amount" |
| 417 | + absolute_tolerance: 0.01 |
| 418 | +``` |
| 419 | + |
| 420 | +`primary_keys` are written with the target spelling, so a renamed key works in local runs. Warehouse pushdown joins on stored column names and raises `ConfigError` for a key that exists on the source only under its old name. |