Repository navigation
<
8000
!--$-->
fix(dq): schema-drift tolerance + Oracle regex/length check fix - #22
Merged
Merged
Conversation
A contentSchema can declare a column that no longer exists in the physical table (renamed/dropped). The profiler built one SELECT over every declared column, so a single absent column failed the whole table's profile + sample run — the product got zero checks. POST /v1/profile/run now resolves the live column set via connector.get_columns() first, profiles only the columns that exist, and returns the rest in a new `missing_columns` field. If introspection itself fails (permission/network) it falls back to profiling every declared field — no regression. Exact-name match: the declared name is reused verbatim as a quoted identifier in the profile/sample SQL, so a case-mismatched name is unusable anyway — dropping it is the safe outcome. Verified live against MySQL and Oracle (cross-dialect): profile, emit, and check execution all succeed; missing columns are reported, not fatal. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Sensor templates normalise columns with the Postgres `::TEXT` cast so LENGTH() and regex work on non-text columns. sqlglot transpiles TEXT to Oracle CLOB, but Oracle's REGEXP_LIKE and LENGTH reject CLOB (ORA-22849) — so text_matching_regex_percent and text_length_in_range_percent (and every length/pattern sensor) errored on every Oracle table. _transpile_sensor_sql now rewrites TEXT data types to a bounded VARCHAR2(4000) on the parsed tree before emitting Oracle SQL. Other dialects are untouched. Found by an exhaustive check-type matrix (every emittable check type, pass + fail case) run live against PostgreSQL, MySQL and Oracle: 25/25 on PostgreSQL and MySQL; Oracle went 20/25 -> 24/25 with this fix (the last is column_count's all_tab_columns UPPER() heuristic, correct for standard unquoted Oracle DDL — verified separately). Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
The deterministic emitter produced 15 check types; the DQOps catalog has
~178. Add the 10 a single-run, no-history, no-user-SQL engine can emit
from existing inputs:
- dirty-text detectors (empty / whitespace / null-placeholder /
surrounding-whitespace) on every text column — hidden values that
nulls_percent cannot see
- date_values_in_future_percent + date_in_range_percent on date
columns; date_in_range closes a gap the module docstring promised
but never implemented
- column_exists per declared field
- column_types_changed — type drift, companion to column_list_changed
- table_availability
- duplicate_record_percent — full-row dedup, only when no PK declared
Supporting: inference_engine gains infer_date_range() + DateRange;
InferenceResult.date_range flows through the /profile/run <->
/checks/emit-deterministic payloads. Rule 4 now branches on column
shape — date columns route to date_in_range_percent, others stay on
number_in_range_percent.
All new checks gate on their promised dimension, preserving the
emitter's no-orphan-churn invariant. Adds tests + a design doc
(docs/deterministic-engine-expansion.md) classifying the full catalog
as emittable / not-emittable with rationale.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
run_async_migrations connected once and failed hard when Postgres was still starting — a docker-compose race where the migrate step beats the DB to ready. Add _connect_with_retry: exponential backoff with jitter (30 attempts, 8s cap), retrying only transient connect errors (connection refused, starting up, shutting down) and failing fast on auth/schema errors. session.py: pool_recycle=1800 and a 10s connect timeout with JIT disabled — drop stale pooled connections, bound connect latency. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Sampling — and therefore format/codelist inference — only runs when the profiler is given a primary key (it's needed for deterministic ORDER-BY-hash sampling). Until now MLG was the only source for that key: when a product's contentSchema declared no `primaryKey`, body.pk arrived empty and no sample was fetched, so e.g. an ISO-3166 country column never produced its accuracy check. _profile_and_infer already calls get_columns() for schema-drift tolerance, and ColumnInfo.is_primary_key is already populated by the postgres/mysql/sqlserver connectors. Derive resolved_pk from those columns when body.pk is absent. Zero extra round-trip. The declared PK still wins; connectors that don't populate is_primary_key (snowflake, bigquery, duckdb, oracle, redshift, databricks) degrade gracefully — same as today. Uses getattr() for the is_primary_key access so the test fakes (which historically only carried a name attr) keep working — fake-column shim updated accordingly, plus three new tests covering the fallback, declared-pk-wins, and no-pk-no-sample paths. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
One endpoint replaces N per-check create/delete round-trips between MLG and dq-platform with a single atomic call. MLG posts the product's full desired check set; dq-platform diffs it against the product's owned checks (metadata.productId), then creates the missing, hard-deletes the undesired (dead columns / dropped dimensions / duplicates / stale), and updates drifted rule parameters — all in one transaction. Atomic, per-product isolated, hard-delete (no inactive accumulation), O(1) HTTP calls per product instead of O(checks). Surface: - schemas/check.py: DesiredCheck / ReconcileRequest / ReconcileResponse - services/check_service.py: CheckService.reconcile_product_checks - api/v1/checks.py: POST /checks/reconcile Tests cover create, delete-undesired, idempotent re-runs, and per-product isolation on a shared connection/table. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Summary
Follow-up to the merged deterministic-engine PR (#21). Two fixes, both
found by exhaustive live testing across PostgreSQL, MySQL and Oracle.
1. Schema-drift tolerance (
2c89dc4)A contentSchema can declare a column that no longer exists in the
physical table. The profiler built one SELECT over every declared
column, so a single absent column failed the whole table's profile +
sample run — the product got zero checks.
POST /v1/profile/runnow resolves the live column set viaconnector.get_columns(), profiles only the columns that exist, andreturns the rest in a new
missing_columnsfield. If introspectionitself fails it falls back to profiling every declared field.
2. Oracle — TEXT casts → VARCHAR2, not CLOB (
715d80b)Sensor templates normalise columns with the Postgres
::TEXTcast.sqlglot maps TEXT → Oracle CLOB, but Oracle's
REGEXP_LIKEandLENGTHreject CLOB (ORA-22849) — sotext_matching_regex_percentand
text_length_in_range_percenterrored on every Oracle table._transpile_sensor_sqlnow rewrites TEXT → boundedVARCHAR2(4000)for Oracle. Other dialects untouched.
Testing
Exhaustive check-type matrix — every emittable check type, pass + fail
case, run live:
column_countverified separately on standard DDL)tests/test_profile_routes.py— 5 tests, schema-drift tolerance.tests/test_sensor_rendering.py— +4 tests, Oracle TEXT→VARCHAR2 transpile.🤖 Generated with Claude Code