8000 fix(dq): seven bug fixes from end-to-end DQ flow audit by BaraaZ95 · Pull Request #20 · zaherab/DQOPS_API · GitHub
[go: up one dir, main page]

Skip to content
8000

fix(dq): seven bug fixes from end-to-end DQ flow audit - #20

Merged
BaraaZ95 merged 2 commits into
fix/unrunnable-check-hardeningfrom
fix/dq-audit-bugs
Apr 24, 2026
Merged

BaraaZ95 merged 2 commits into
fix/unrunnable-check-hardeningfrom
fix/dq-audit-bugs

Conversation

@BaraaZ95
Copy link
Copy Markdown

Summary

End-to-end audit of MLG → DQOps → source DB surfaced 7 bugs. This PR fixes all 7 at the root. Stacked on #19.

Depends on #19.

The bugs

Bug Symptom Fix
A Batch-run race 5 of 69 jobs stuck pending with no celery_task_id Commit Job row before execute_check.delay()
B Missing severity in API DB populated, API omitted field Added to ResultResponse
C execution_time_ms never set NULL on every row perf_counter around execution
C' rows_scanned never set NULL on every row Paired COUNT(*) query in DQOpsExecutor
D distinct_count labeled as "duplicates" Message says "95 duplicates" for a distinct check evaluate_rule takes sensor_type; authoritative over description
E NULLs counted as duplicates duplicate_percent=10% on a column with 10/100 NULLs, all non-NULLs unique WHERE col IS NOT NULL on DUPLICATE_* and DISTINCT_PERCENT sensors
Cascade Soft-deleted connection's checks stayed active Scheduler kept running them ConnectionService.delete cascades is_active=false to checks

Verification

Against seeded fixtures (customers=100, orders=200, transactions=500, products=50, employees=80):

  • 69/69 batch jobs completed (was 64/69)
  • rows_scanned matches seeded counts exactly for all 5 tables
  • customers.phone duplicate_percent = 0% (was 10%)
  • distinct_count messages now read "N distinct values"
  • severity + execution_time_ms populated on every recent row

Test plan

  • pytest tests/test_rules_humanize.py tests/test_dqops_checks.py tests/test_service_connection.py tests/test_service_execution.py — 72/73 green (1 pre-existing failure unrelated to this PR)
  • New tests: 5 for sensor_type labeling, 1 for cascade delete, updated test_submit_job for commit ordering
  • E2E re-run against local stack confirms every fix above

🤖 Generated with Claude Code

BaraaZ95 and others added 2 commits April 24, 2026 22:50
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>
`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>
@BaraaZ95
BaraaZ95 merged commit 157bcec into fix/unrunnable-check-hardening Apr 24, 2026
@BaraaZ95
BaraaZ95 deleted the fix/dq-audit-bugs branch April 24, 2026 19:12
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.

1 participant

0