8000 fix: reject unrunnable check configs and harden sensor/runner error paths by BaraaZ95 · Pull Request #19 · zaherab/DQOPS_API · GitHub
[go: up one dir, main page]

Skip to content

fix: reject unrunnable check configs and harden sensor/runner error paths - #19

Merged
zaherab merged 6 commits into
mainfrom
fix/unrunnable-check-hardening
Apr 27, 2026
Merged

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

Conversation

@BaraaZ95
Copy link
Copy Markdown

Summary

Eliminates the class of "phantom failing check" that polluted MLG dimension scores and fixes several related error-handling gaps in the check execution pipeline. Also bundles seven earlier commits from the superseded branch plus a small worker-networking tweak.

Phantom-check hardening (5fd81ca)

Checks were being persisted without required params (reference_table, timestamp_column, sql_expression, check_id) — every execution rendered invalid SQL, but the runner masked the failure by falling through to a GX executor that returned a fake "success" placeholder. Net effect: dimension scoring showed check_count > 0, passed_count = 0 for these checks, indistinguishable from real failures.

Fixes, layered so a regression requires breaking all of them:

  • Sensor-level: required_params declarations on every sensor whose Jinja template references a variable the framework does not inject automatically. test_sensor_contract.py AST-walks the catalog and enforces this for all future sensors.
  • Service-level: CheckService.create_check / update_check dry-run the sensor template before persisting, so bad configs get 422 instead of being saved and failing on every execution.
  • Runner-level: check_runner scopes its GX fallback try/except to the enum lookup only. Real render/SQL/rule errors now propagate to the job's error_message instead of surfacing as "GX not implemented".
  • GX executor: raises NotImplementedError instead of returning a hardcoded success.
  • Dimension scoring: returns not_assessed (null) when total == 0 and exposes not_run_count so the UI distinguishes unrun from failing.
  • CheckTypeInfo: exposes rule_type + default_params so MLG builds correctly-shaped rule_parameters without hardcoding per-type logic.
  • Migrations 015–017: purge pre-existing unrunnable checks. Narrow scope — only checks with no historical results AND missing the required parameter. No real data at risk.
  • CheckService switches commit() → flush() to match ConnectionService / ExecutionService / ScheduleService.

Worker networking (be02b6c)

docker-compose.yml adds host.docker.internal:host-gateway to the worker, matching what the API already has. Without it, worker-side check executions against host-bound connections fail at the network layer.

Also included (carried over from the prior branch)

  • 9fb2ed8 connect_timeout 10s → 30s across connectors
  • 22c29c5 + 18bf5c0 SQL sensor render hardening + 39 tests
  • f50f8fb Incident-scoped FOR UPDATE + stuck-job status fix
  • b3fbd52 + 44fb82f identifier dot restriction + partition-filter hardening
  • 671197a human-readable check result messages

Test plan

  • pytest tests/test_sensor_required_params.py tests/test_sensor_contract.py — sensor contract
  • pytest tests/test_check_service_validation.py — API-boundary rejection
  • pytest tests/test_check_runner_error_propagation.py — real errors surface
  • pytest tests/test_dimension_scoring.py — not_assessed semantics
  • pytest tests/test_checks.py tests/test_service_check.py — updated existing tests
  • Apply migrations 015–017 against a staging DB with known phantom checks; confirm only those with no results get purged
  • Trigger a cross-table check without reference_table via the API; expect 422 instead of silent persistence
  • Run a check that fails sensor render in production; confirm the job's error_message shows the real cause, not "GX not implemented"

🤖 Generated with Claude Code

BaraaZ95 and others added 3 commits April 24, 2026 14:46
Eliminates the class of "phantom failing check" that showed up in MLG
dimension scores: checks persisted without required params (reference_table,
timestamp_column, sql_expression, check_id) rendered invalid SQL on every
execution, but the runner masked the failure by falling through to a GX
executor that returned a misleading "not implemented" success placeholder.

- Sensors now declare required_params; create/update dry-run renders the
  Jinja template so bad configs are rejected with 422 instead of persisted.
- check_runner scopes its fallback try/except to the enum lookup only, so
  real render/SQL/rule errors propagate into the job's error_message.
- gx_executor raises NotImplementedError instead of returning fake success.
- Dimension scoring returns not_assessed (null) when no check has results,
  and exposes not_run_count so the UI distinguishes unrun from failing.
- CheckTypeInfo exposes rule_type + default_params so MLG can build
  correctly-shaped rule_parameters without hardcoding per-type logic.
- Migrations 015-017 purge pre-existing unrunnable checks (narrow scope:
  only checks with no historical results and missing required params).
- CheckService switches commit()->flush() to match the other services'
  request-scoped transaction handling.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Lets the Celery worker reach services running on the host (e.g. a
Snowflake tunnel, a local postgres, user-facing dev databases) via
host.docker.internal the same way the API container already can.
Without host-gateway, worker check executions against host-bound
connections fail at the network layer.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Runs all 69 active seeded checks, compares against ground truth, and
fixes the issues that surfaced.

## Bug A — batch-run race (services/execution_service.py)

`ExecutionService.submit_job` used to call `execute_check.delay()` before
flushing `celery_task_id` to the DB. A fast worker could pick up the task
and look up the Job row before the outer request committed. On batch
runs this lost ~7% of jobs (5 of 69 seen locally) — they stayed
`pending` with no `celery_task_id`, invisible to recover_orphaned_jobs
until its 10-minute cutoff.

Fix: commit the Job row BEFORE dispatching to Celery, then commit
celery_task_id in a second short transaction.

## Bug B — missing `severity` in API schema (schemas/result.py)

`check_results.severity` is populated on every row but `ResultResponse`
Pydantic schema omitted it. Added the field.

## Bug C — `execution_time_ms` and `rows_scanned` never populated

Neither metric was ever captured:
- `CheckRunResult` now carries `execution_time_ms` (perf_counter around
  `run_dqops_check` / GX fallback / cross-source).
- `DQOpsExecutor` runs a paired `SELECT COUNT(*) FROM schema.table`
  with the same partition filter, so every result gets a consistent
  `rows_scanned` value regardless of sensor category. Best-effort on
  failure (returns None).
- Worker threads both fields into `ResultService.create_result`.

## Bug D — distinct_count mislabeled as "duplicates"

`_humanize_message`'s uniqueness branch unconditionally wrote
"duplicates", even for DISTINCT_COUNT / DISTINCT_PERCENT checks.

Fix: thread `sensor_type` through `evaluate_rule` (authoritative) and
fall back to description string matching only when sensor_type isn't
available. `DQOpsExecutor` now passes `check.sensor_type.value`.

## Bug E — nulls counted as duplicates

`DUPLICATE_COUNT` / `DUPLICATE_PERCENT` / `DISTINCT_PERCENT` sensors
computed `(COUNT(*) - COUNT(DISTINCT col))`, which treats every NULL
row as a duplicate bucket. On a column with 10 nulls of 100 rows and
all distinct non-null values, `duplicate_percent` returned 10% (wrong).

Fix: `WHERE col IS NOT NULL` on those three sensors. `DISTINCT_COUNT`
is unchanged — SQL's `COUNT(DISTINCT)` already ignores NULL.

Semantic note: `DISTINCT_PERCENT` on a partially-null column now
reads as "distinct among non-null values" (100% for the example
above, was 90%). Sensor descriptions updated to reflect this.

## Connection delete cascade (services/connection_service.py)

`ConnectionService.delete` was a soft-delete on the connection row
only — its checks stayed `is_active=True`. Scheduler, dimension
scores, and incident lifecycle all treated those checks as live.

Fix: cascade deactivation in a single `UPDATE checks SET
is_active=false WHERE connection_id = X AND is_active = true`.

## Tests

- 5 new cases in `tests/test_rules_humanize.py` for sensor_type-based
  labeling (distinct vs duplicate, sensor_type overrides description).
- New `test_delete_cascades_to_checks` in `tests/test_service_connection.py`.
- Updated `test_submit_job` to assert the double-commit pattern.

All unit tests pass except two pre-existing failures
(`test_list_jobs_with_filters`, `test_list_connections_pagination`)
that are unrelated mock setup bugs predating this branch.

## E2E verification

Reseeded 5 fixture tables with deterministic defects. Re-ran all 69
active checks:
- 69/69 completed (was 64/69 — Bug A)
- severity populated, execution_time_ms populated (25-150ms)
- rows_scanned matches seeded row counts exactly
  (customers=100, orders=200, transactions=500, products=50, employees=80)
- distinct_count results now read "N distinct values"
- customers.phone duplicate_percent = 0% (was 10% — 10 nulls)

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
BaraaZ95 and others added 3 commits April 24, 2026 23:08
`test_list_connections_pagination`, `test_list_connections_filter_by_type`,
and `test_list_jobs_with_filters` were failing because the tests stubbed
`result.all.return_value = [(i,) for i in range(N)]` while the services
actually consume the count with `result.scalar()`. Both list_connections
(connection_service.py:126-130) and list_jobs use the idiomatic COUNT +
`.scalar()` pattern; the test mocks had drifted out of sync with it at
some point and started silently returning a `MagicMock` that compared
unequal to the expected integer.

Fix at the root instead of patching each test:

- Add three reusable helpers to tests/conftest.py:
    mock_count_result(n)       — shape for `.scalar()` on COUNT queries
    mock_scalars_result(items) — shape for `.scalars().all()` on row queries
    mock_scalar_one_result(x)  — shape for `.scalar_one_or_none()`

  These capture the canonical SQLAlchemy async result access patterns in
  one place so any future SQL refactor (say, switching to `.one()`, or
  a scalar subquery) only needs the helper updated — not every test.

- Rewrite the three failing tests to use the helpers. Two tests drop
  from ~12 lines of mock scaffolding each to 3 lines; the intent is
  now obvious at the call site.

All 230 unit tests pass (was 227 passed + 3 failed).

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
## Why

Of the 401 tests in the unit suite, 79 were erroring before any
assertion ran and another 7 were failing. Root cause: the `async_engine`
fixture called `Base.metadata.create_all`, which tries to create a FK
from `incidents.result_id` to `check_results(result_id)` — but
`check_results` is a TimescaleDB hypertable with a composite PK, so
there's no unique constraint backing that FK. Alembic migrations handle
this correctly in prod (migration 004 explicitly omits the FK); the test
path bypassed them and failed at setup for every fixture that touched
the DB.

Fixing the fixture surfaced a cascade of test-infra rot that had been
hiding behind the earlier error. Each is addressed at the root.

## Changes

### Schema / migrations
- `001_initial.py`: prepend `CREATE EXTENSION IF NOT EXISTS timescaledb`.
  The Docker image ships the binaries but each DB needs its own
  `CREATE EXTENSION`. Safe on existing prod (no-op) and now works on
  fresh test DBs.

### `tests/conftest.py` — single file carries the bulk of the work
- `async_engine` fixture now runs `alembic.command.upgrade(cfg, "head")`
  via `asyncio.to_thread` (Alembic's env.py does its own `asyncio.run`,
  which would collide with pytest-asyncio's loop). Teardown is
  `DROP SCHEMA public CASCADE; CREATE SCHEMA public;` — cleanly wipes
  hypertables, chunks, enums, and `alembic_version` in one shot.
- `TEST_DATABASE_URL` default shifts to a dedicated `dq_platform_test`
  DB; `_bootstrap_test_db` autouse fixture creates it once per session
  via asyncpg. Hard-fail assert prevents the destructive teardown from
  ever targeting a non-test DB.
- Engine uses `NullPool` so each checkout makes a fresh connection in
  the current event loop. Fixes "Future attached to a different loop"
  errors when starlette's TestClient (its own loop) borrows a pooled
  asyncpg conn bound to pytest-asyncio's loop.
- `client` and `sync_client` fixtures now own their own session
  factory (bound to `async_engine`) with `get_db`-equivalent
  commit-on-yield / rollback-on-error semantics. No more sharing a
  single `db_session` across event-loop boundaries. Default
  `X-API-Key: test-key` header so tests don't need to supply it per-call.
- Module-level `dq_platform.db.session.engine` and
  `dq_platform.main.engine` are monkey-patched at conftest import time
  to NullPool equivalents. Fixes `/health` (and anything else that
  bypasses `get_db` to talk to the DB directly).
- AsyncClient constructed with `transport=ASGITransport(app=app)`
  (new httpx API; old `app=app` keyword was removed).

### Service-layer: refresh after update
Three services updated an ORM object, flushed, and returned — without
calling `await self.db.refresh(obj)`. Server-set columns like
`updated_at` stayed unloaded; response serialization later tried to
lazy-load them from a closed greenlet context and raised MissingGreenlet.
Adding `refresh` after flush is the idiomatic SQLAlchemy fix.
- `connection_service.update`
- `incident_service.update_status`
- `schedule_service.update`
  (`notification_service.update` already did this — copied the pattern.)

### Test commit semantics
`db_session.flush()` in API test fixtures was sufficient when the same
session served the subsequent request — but with `sync_client`'s
fresh-per-request sessions, flushed-but-uncommitted rows were invisible
to the request. Bulk-replaced `flush()` → `commit()` in the fixtures
that seed rows (9 sites across 5 files).

### Test rot surfaced by the fix
- `test_error_handlers.py`: `app.routes = [...]` → `app.router.routes[:] = [...]`.
  FastAPI made `app.routes` read-only.
- `test_scheduler_and_notifications.py`: patch target
  `_create_task_session_factory` → `_get_task_session_factory` (prod
  function was renamed; tests weren't updated).
- `test_service_incident.py`, `test_service_schedule.py`: replaced
  hand-rolled `result.all.return_value` mocks with the
  `mock_count_result`/`mock_scalars_result` helpers already in conftest.
  Fixes three `test_list_*` tests whose mocks stubbed `.all()` while
  the services use `.scalar()` on COUNT queries.
- `test_api_checks_routes.py::test_run_check`: patch target
  `dq_platform.services.execution_service.execute_check` →
  `dq_platform.workers.tasks.execute_check` (import is lazy inside
  `submit_job`, has to be patched at the definition site).

## Results

| Metric    | Before | After |
|-----------|-------:|------:|
| Errors    |     79 |     0 |
| Failures  |      7 |     4 |
| Passing   |    315 |   397 |
| Total     |    401 |   401 |

The 4 remaining failures are isolated API/behavior drift (response
status code, category filter semantics, webhook mocking, generic
exception handler) — each needs its own small fix but none shares a
root cause with the other. Scoped for a follow-up; this commit focuses
on the systemic issue.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
All four root causes identified and fixed.

## run_check endpoint returns 200, test expected 202

`POST /checks/{id}/run` dispatches async work but returned 200 OK. 202
Accepted is the correct HTTP semantic — the job is queued but not yet
complete. Added `status_code=status.HTTP_202_ACCEPTED` to the route
decorator. Aligns with the `/checks/batch/run` convention the rest of
the async-dispatch endpoints should follow too.

## `/checks/types?category=X` leaked entries with no category

The handler filtered DQOps checks by category but then appended legacy
Great Expectations checks (which have no category) unconditionally. A
request for `?category=volume` returned volume DQOps checks plus every
GX check, violating the filter contract. Wrapped the GX appendix in
`if not category:` — filter requests now return only matching entries.

## Webhook test endpoint mock was never awaitable

Two `test_test_channel_*` tests stubbed `httpx.AsyncClient.__aenter__`
and `.post()` with `MagicMock` instead of `AsyncMock`. The service
awaits both, and a `MagicMock` returned from a sync call isn't
awaitable — the service caught the `TypeError` into its
`{"success": False, "error": ...}` fallback. Switched to `AsyncMock`
and added the missing `AsyncMock` import. Also set
`allow_private_network_connections=True` in `get_test_settings` so
`validate_url` doesn't DNS-lookup `hooks.example.com` (fails inside
the container) — both a test-mock fix and a test-config fix for the
same endpoint.

## Generic exception test got raw exception, not 500

`test_generic_exception_returns_500` expected the TestClient to return
500 for an unhandled `ValueError`. Starlette's `TestClient` defaults
to `raise_server_exceptions=True`, which re-raises instead of returning
500 when the app's `BaseHTTPMiddleware` subclasses don't route the
exception through FastAPI's registered `@app.exception_handler`s.
Setting `raise_server_exceptions=False` on both test clients makes
them behave like a real HTTP client would (500 response with the
registered handler's body), and kills a class of future "test framework
disagrees with prod" bugs.

## Two cascading fixes surfaced after the above

- `check_service.update_check` was missing `await db.refresh(check)`
  after `flush()`, same lazy-load-outside-greenlet bug fixed earlier in
  connection/incident/schedule services. Added the refresh.
- `test_check_service_validation.py::service` fixture needed
  `db.refresh = AsyncMock()` to mock the new call.

## Result

| Metric   | Before this commit | After |
|----------|-------------------:|------:|
| Failures | 4                  | **0** |
| Errors   | 0                  | **0** |
| Passing  | 397                | **401** |
| Total    | 401                | 401   |

The DQ platform unit suite is now fully green.

Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
@zaherab
zaherab merged commit f3ad333 into main Apr 27, 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