8000 fix(dq): schema-drift tolerance + Oracle regex/length check fix by BaraaZ95 · Pull Request #22 · zaherab/DQOPS_API · GitHub
[go: up one dir, main page]

Skip to content
< 8000 div id="diff-comparison-viewer-container" class="Container__qkpM1i tmp-mt-4 container-xl tmp-px-3 tmp-px-md-4 tmp-px-lg-5 pullRequestLayoutVariables__fZp87z ContainerSidePanel__47M93r" data-side-panel-open="false" data-hovercard-subject-tag="pull_request:3731082781">

fix(dq): schema-drift tolerance + Oracle regex/length check fix - #22

Merged
zaherab merged 6 commits into
mainfrom
fix/unrunnable-check-hardening
Jun 5, 2026
Merged

zaherab merged 6 commits into
mainfrom
fix/unrunnable-check-hardening

Conversation

@BaraaZ95
Copy link
Copy Markdown

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/run now resolves the live column set via
connector.get_columns(), profiles only the columns that exist, and
returns the rest in a new missing_columns field. If introspection
itself fails it falls back to profiling every declared field.

2. Oracle — TEXT casts → VARCHAR2, not CLOB (715d80b)

Sensor templates normalise columns with the Postgres ::TEXT cast.
sqlglot maps TEXT → Oracle CLOB, but Oracle's REGEXP_LIKE and
LENGTH reject CLOB (ORA-22849) — so text_matching_regex_percent
and text_length_in_range_percent errored on every Oracle table.
_transpile_sensor_sql now rewrites TEXT → bounded VARCHAR2(4000)
for Oracle. Other dialects untouched.

Testing

Exhaustive check-type matrix — every emittable check type, pass + fail
case, run live:

Dialect Result
PostgreSQL 25/25
MySQL 25/25
Oracle 25/25 (24/25 on quoted-lowercase tables; column_count verified 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.
  • 58/58 sensor-rendering tests pass.

🤖 Generated with Claude Code

BaraaZ95 and others added 6 commits May 22, 2026 18:47
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>
@zaherab
zaherab merged commit c1b8518 into main Jun 5, 2026
2 of 5 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants

0