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
Merged
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension

Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
333 changes: 333 additions & 0 deletions docs/deterministic-engine-expansion.md

Large diffs are not rendered by default.

28 changes: 28 additions & 0 deletions src/dq_platform/api/v1/checks.py
Original file line number Diff line number Diff line change
Expand Up @@ -31,6 +31,8 @@
CheckResponse,
CheckTypeInfo,
CheckUpdate,
ReconcileRequest,
ReconcileResponse,
RunCheckRequest,
)
from dq_platform.schemas.common import PaginatedResponse
Expand Down Expand Up @@ -226,6 +228,32 @@ async def batch_run_checks(
return jobs


@router.post("/reconcile", response_model=ReconcileResponse)
async def reconcile_checks(
data: ReconcileRequest,
db: AsyncSession = Depends(get_db),
) -> ReconcileResponse:
"""Declaratively reconcile a product's check set to the desired state.

One call replaces N per-check create/delete round-trips and is atomic —
a failure leaves the prior set intact (no partial registration). Owned
checks not in the desired set (dead columns, dropped dimensions,
duplicates, stale leftovers) are hard-deleted; missing ones created.
"""
service = CheckService(db)
created, deleted, updated, check_ids = await service.reconcile_product_checks(
connection_id=data.connection_id,
product_id=data.product_id,
desired=data.desired,
)
return ReconcileResponse(
created=created,
deleted=deleted,
updated=updated,
check_ids=check_ids,
)


@router.post("/validate/preview", response_model=CheckExecutionDetail)
async def validate_check_preview(
request: CheckPreviewRequest,
Expand Down
8000 64 changes: 54 additions & 10 deletions src/dq_platform/api/v1/profile_routes.py
Original file line number Diff line number Diff line change
Expand Up @@ -87,6 +87,8 @@ class InferenceResp(BaseModel):
length_max: int | None = None
numeric_min: float | None = None
numeric_max: float | None = None
date_min: str | None = None
date_max: str | None = None


class ProfileResponse(BaseModel):
Expand All @@ -95,6 +97,10 @@ class ProfileResponse(BaseModel):
inferences: dict[str, InferenceResp]
schema_fingerprint: str | None
cached_sample: bool
# Declared fields absent from the physical table (schema drift). Profiled
# fields skip these; the caller should drop them before emitting checks so
# it never registers a check against a non-existent column.
missing_columns: list[str] = []


class FieldDeclarationBody(BaseModel):
Expand Down Expand Up @@ -160,14 +166,42 @@ async def run_profile_endpoint(


async def _profile_and_infer(connector: Any, body: ProfileRequest) -> ProfileResponse:
# Resolve the live column set first. A declared field that no longer
# exists in the physical table (schema drift — column renamed/dropped)
# must not blow up the whole profile + sample run. Profile the columns
# that exist, report the rest as missing.
fields = body.fields
missing_columns: list[str] = []
# A declared PK wins. When the caller declares none — e.g. an ODPS-only
# contentSchema with no DQ extensions — fall back to the table's real
# primary key so sampling (and therefore format/codelist inference) can
# still run. Derived from get_columns() below, which we already call, so
# this adds no extra round-trip.
resolved_pk = body.pk
try:
# Exact-name membership. The declared name is reused verbatim as a
# quoted identifier in the profile/sample SQL, so a case-mismatched
# name is unusable anyway — treating it as missing (drop it) is the
# safe outcome, vs. keeping it and failing the whole query.
cols = connector.get_columns(body.schema_name, body.table)
live_cols = {c.name for c in cols}
fields = [f for f in body.fields if f.name in live_cols]
missing_columns = [f.name for f in body.fields if f.name not in live_cols]
if not resolved_pk:
resolved_pk = next((c.name for c in cols if getattr(c, "is_primary_key", False)), None)
except Exception:
# Schema introspection unavailable (permission/network). Fall back to
# profiling every declared field — no regression vs prior behaviour.
fields = body.fields

# Profile (aggregate round-trip).
field_specs = [
FieldProfileSpec(
name=f.name,
logical_type=f.logical_type,
classification=f.classification,
)
for f in body.fields
for f in fields
]
profile_sql = connector.build_profile_sql(
body.schema_name,
Expand All @@ -178,7 +212,7 @@ async def _profile_and_infer(connector: Any, body: ProfileRequest) -> ProfileRes
"logical_type": f.logical_type,
"classification": f.classification,
}
for f in body.fields
for f in fields
],
)
async with audit(
Expand All @@ -192,11 +226,12 @@ async def _profile_and_infer(connector: Any, body: ProfileRequest) -> ProfileRes
profile = await run_profile(connector, body.schema_name, body.table, field_specs)
rec.set_result(list(profile.columns.values()))

# Sample fetch (cached). Only when a PK is declared and at least one
# field is text-shaped — otherwise inference has no work to do.
# Sample fetch (cached). Only when a PK is available (declared or
# introspected) and at least one field is text-shaped — otherwise
# inference has no work to do.
cached = False
rows: list[dict[str, Any]] = []
if body.pk and any(f.sample_needed and f.classification != "PII" for f in body.fields):
if resolved_pk and any(f.sample_needed and f.classification != "PII" for f in fields):
cache = get_sample_cache()
cache_key = CacheKey(
org_id=body.org_id,
Expand All @@ -211,14 +246,14 @@ async def _profile_and_infer(connector: Any, body: ProfileRequest) -> ProfileRes
if rows:
cached = True
else:
pii_cols = {f.name for f in body.fields if f.classification == "PII"}
sample_cols = [f.name for f in body.fields if f.name not in pii_cols]
spec = SampleSpec(columns=sample_cols, pk=body.pk, n=body.sample_size, seed=body.seed)
pii_cols = {f.name for f in fields if f.classification == "PII"}
sample_cols = [f.name for f in fields if f.name not in pii_cols]
spec = SampleSpec(columns=sample_cols, pk=resolved_pk, n=body.sample_size, seed=body.seed)
sample_sql = connector.build_sample_sql(
body.schema_name,
body.table,
sample_cols,
body.pk,
resolved_pk,
body.sample_size,
body.seed,
)
Expand All @@ -237,7 +272,7 @@ async def _profile_and_infer(connector: Any, body: ProfileRequest) -> ProfileRes

# Inference per column.
inferences: dict[str, InferenceResp] = {}
for f in body.fields:
for f in fields:
if f.classification == "PII":
inferences[f.name] = InferenceResp()
continue
Expand All @@ -262,6 +297,8 @@ async def _profile_and_infer(connector: Any, body: ProfileRequest) -> ProfileRes
length_max=result.length_range.max if result.length_range else None,
numeric_min=result.numeric_range.min if result.numeric_range else None,
numeric_max=result.numeric_range.max if result.numeric_range else None,
date_min=result.date_range.min if result.date_range else None,
date_max=result.date_range.max if result.date_range else None,
)

return ProfileResponse(
Expand All @@ -281,6 +318,7 @@ async def _profile_and_infer(connector: Any, body: ProfileRequest) -> ProfileRes
inferences=inferences,
schema_fingerprint=profile.schema_fingerprint,
cached_sample=cached,
missing_columns=missing_columns,
)


Expand Down Expand Up @@ -328,6 +366,7 @@ async def emit_deterministic(body: EmitRequest) -> EmitResponse:
# Rehydrate InferenceResult per column from the response shape.
from dq_platform.profilers.inference_engine import (
CodelistRef,
DateRange,
EnumCandidate,
FormatRef,
LengthRange,
Expand All @@ -354,6 +393,11 @@ async def emit_deterministic(body: EmitRequest) -> EmitResponse:
if inf.numeric_min is not None and inf.numeric_max is not None
else None
),
date_range=(
DateRange(min=inf.date_min, max=inf.date_max)
if inf.date_min is not None and inf.date_max is not None
else None
),
)

out = emit(field_decls, table_decl, body.dq_profile, field_profiles, field_inferences)
Expand Down
23 changes: 23 additions & 0 deletions src/dq_platform/checks/dqops_executor.py
Original file line number Diff line number Diff line change
Expand Up @@ -7,6 +7,7 @@
import sqlglot
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession
from sqlglot import exp

from dq_platform.checks.dqops_checks import DQOpsCheckType, get_check
from dq_platform.checks.rules import Severity, evaluate_rule
Expand Down Expand Up @@ -60,6 +61,20 @@ def __init__(self, sensor_name: str, dialect: str) -> None:
_NO_TRANSPILE = {"postgresql", "duckdb", ""}


def _text_to_bounded_varchar(node: exp.Expression) -> exp.Expression:
"""Rewrite a bare TEXT data type to a bounded VARCHAR(4000).

Sensor templates cast columns with the Postgres `::TEXT` idiom so
LENGTH()/REGEXP work on non-text columns. sqlglot maps TEXT → Oracle
CLOB, but Oracle's REGEXP_LIKE and several functions reject CLOB
(ORA-22849). VARCHAR2(4000) — what TEXT becomes here — is accepted in
every sensor context. 4000 is Oracle's non-extended VARCHAR2 ceiling.
"""
if isinstance(node, exp.DataType) and node.this == exp.DataType.Type.TEXT:
return exp.DataType.build("VARCHAR(4000)")
return node


def _transpile_sensor_sql(sql: str, conn_type: str) -> str:
"""Transpile Postgres-authored sensor SQL to the connection's dialect.

Expand All @@ -73,6 +88,14 @@ def _transpile_sensor_sql(sql: str, conn_type: str) -> str:
if not target:
return sql
try:
if target == "oracle":
# Oracle: TEXT → CLOB breaks REGEXP_LIKE/LENGTH. Rewrite the
# TEXT casts to bounded VARCHAR2 on the parsed tree.
ast = sqlglot.parse_one(sql, read="postgres")
ast = ast.transform(_text_to_bounded_varchar)
transpiled = ast.sql(dialect=target)
metrics.incr(SENSOR_TRANSPILE_OK)
return transpiled or sql
out = sqlglot.transpile(sql, read="postgres", write=target)
metrics.incr(SENSOR_TRANSPILE_OK)
return out[0] if out else sql
Expand Down
56 changes: 51 additions & 5 deletions src/dq_platform/db/migrations/env.py
Original file line number Diff line number Diff line change
@@ -1,14 +1,25 @@
"""Alembic migration environment."""

import asyncio
import logging
import socket
from logging.config import fileConfig

from alembic import context
from sqlalchemy import pool
from sqlalchemy.engine import Connection
from sqlalchemy.exc import DBAPIError, OperationalError
from sqlalchemy.ext.asyncio import async_engine_from_config

from dq_platform.models.base import Base

logger = logging.getLogger("alembic.env")

CONNECT_MAX_ATTEMPTS = 30
CONNECT_BACKOFF_BASE = 1.0
CONNECT_BACKOFF_CAP = 8.0
TRANSIENT_CONNECT_ERRORS = (ConnectionRefusedError, socket.gaierror, OSError)

config = context.config

if config.config_file_name is not None:
Expand Down Expand Up @@ -39,6 +50,41 @@ def do_run_migrations(connection: Connection) -> None:
context.run_migrations()


def _is_transient_connect_error(exc: BaseException) -> bool:
"""True if exc is a transient DB-not-ready error (vs. auth/schema)."""
if isinstance(exc, TRANSIENT_CONNECT_ERRORS):
return True
if isinstance(exc, DBAPIError | OperationalError):
cause = exc.__cause__ or exc.orig
if isinstance(cause, TRANSIENT_CONNECT_ERRORS):
return True
msg = str(cause or exc).lower()
return any(s in msg for s in ("connection refused", "cannot connect", "starting up", "shutting down"))
return False


async def _connect_with_retry(connectable):
"""Retry connect on transient failures with exponential backoff + jitter."""
import random

for attempt in range(1, CONNECT_MAX_ATTEMPTS + 1):
try:
return await connectable.connect().__aenter__()
except Exception as exc:
if attempt == CONNECT_MAX_ATTEMPTS or not _is_transient_connect_error(exc):
raise
delay = min(CONNECT_BACKOFF_CAP, CONNECT_BACKOFF_BASE * (2 ** (attempt - 1)))
delay += random.uniform(0, delay * 0.1)
logger.warning(
"DB not ready (attempt %d/%d): %s — retry in %.1fs",
attempt,
CONNECT_MAX_ATTEMPTS,
exc.__class__.__name__,
delay,
)
await asyncio.sleep(delay)


async def run_async_migrations() -> None:
"""Run migrations in async mode."""
import os
Expand All @@ -53,16 +99,16 @@ async def run_async_migrations() -> None:
poolclass=pool.NullPool,
)

async with connectable.connect() as connection:
connection = await _connect_with_retry(connectable)
try:
await connection.run_sync(do_run_migrations)

await connectable.dispose()
finally:
await connection.close()
await connectable.dispose()


def run_migrations_online() -> None:
"""Run migrations in 'online' mode."""
import asyncio

asyncio.run(run_async_migrations())


Expand Down
2 changes: 2 additions & 0 deletions src/dq_platform/db/session.py
Original file line number Diff line number Diff line change
Expand Up @@ -14,6 +14,8 @@
max_overflow=settings.database_max_overflow,
echo=settings.debug,
pool_pre_ping=True,
pool_recycle=1800,
connect_args={"timeout": 10, "server_settings": {"jit": "off"}},
)

async_session_factory = async_sessionmaker(
Expand Down
Loading
Loading
0