A Data Quality Agent That Separates Late Data From Broken Data

A data quality agent is useful when it explains why a trustworthy-looking dashboard is wrong. Imagine a hypothetical retailer whose morning revenue report drops sharply because one region’s events arrive late, while another region sends duplicated orders. Both anomalies reduce confidence, but they require opposite responses. Replaying a late partition may help; replaying duplicated events without deduplication can make the problem worse. This guide describes an evidence-first assistant, not an autonomous data-cleaning script.

Define Quality at the Dataset’s Grain

Start with a contract that states what one row represents. An order, an order line, a payment attempt, and a recognized-revenue entry are different grains. A uniqueness test on order_id is correct for one and destructive for another. The owner must also define currency, time zone, cancellation handling, event-time semantics, and when a partition becomes complete.

Separate structural checks from statistical checks. Structural checks include missing keys, impossible values, and broken references. Statistical checks include an unusual null rate or a distribution shift. A promotion can legitimately alter product mix, but it does not make a missing tenant identifier acceptable. An LLM can summarize those distinctions and suggest likely causes; it should not invent a new threshold simply because yesterday’s pipeline failed.

PostgreSQL’s constraint documentation explains why row invariants belong in NOT NULL, CHECK, uniqueness, and foreign-key constraints. It also notes that a check expression evaluating to null can pass. Consequently, CHECK (amount >= 0) alone does not require an amount to exist. Encode both requirements when both are business rules.

Build a Small Investigation Loop

Use a scheduler to run approved queries against versioned dataset contracts. Record dataset ID, tenant, partition, contract version, query hash, source watermark, row count, and evaluation time. An evidence tool returns aggregates and a redacted sample of violations. The model organizes the incident, compares recent changes, and drafts an owner-facing explanation with explicit uncertainty.

A dedicated statistical detector may be ordinary software, not a sub-agent. Add a specialist model only when it solves a different reasoning problem, such as interpreting schema migration notes across systems. Do not let multiple agents execute overlapping scans of a production warehouse. Query budgets, partition filters, row limits, and cancellation deadlines belong in the query gateway.

Here is an illustrative PostgreSQL landing-table contract for payment events. It is intentionally not a migration for an existing application.

CREATE TABLE payment_event_landing (
    tenant_id text NOT NULL,
    event_id text NOT NULL,
    order_id text NOT NULL,
    event_time timestamptz NOT NULL,
    ingested_at timestamptz NOT NULL DEFAULT now(),
    currency char(3) NOT NULL,
    amount_minor bigint NOT NULL CHECK (amount_minor >= 0),
    event_kind text NOT NULL CHECK (
        event_kind IN ('authorized', 'captured', 'refunded')
    ),
    PRIMARY KEY (tenant_id, event_id)
);

Refunds are positive magnitudes identified by event kind here; another ledger may use signed entries. That choice must be explicit before downstream sums are written. The example omits currency reference validation, authorization policies, partitioning, and reconciliation between captures and refunds. It does not establish that the same business event cannot arrive under two different event IDs.

Distinguish Absence From Invalidity

Track event time and ingestion time separately. Suppose a regional feed normally completes by 06:00 local time but its upstream batch is still running. A low row count before the completion watermark is a freshness incident, not proof of lost revenue. Mark the report provisional and identify the incomplete region instead of generating an alarming commercial conclusion.

Duplicates require another evidence path. Compare source delivery IDs, business keys, and payload hashes. Identical delivery IDs usually indicate replay; matching order IDs may instead represent multiple legitimate payments. Quarantine ambiguous cases rather than choosing a survivor based on a model’s preference. Preserve raw inputs so a corrected contract can replay them later.

Use partition-level blocking where feasible. If only one store-day is invalid, suppress or qualify that slice instead of stopping every report. Conversely, a broken currency conversion rule may affect all partitions and justify a broader hold. Record the reason for the chosen blast radius. A clean global average must never hide a failed critical partition.

Repair Through Governed Software

The agent should propose a repair plan containing affected keys, expected row counts, a dry-run diff, rollback method, and downstream consumers. A human data owner approves destructive corrections or backfills. Recheck the contract version and source watermark before execution; approval of an old snapshot is not approval of changed data.

Make each repair operation idempotent using a durable run key and explicit target partition. Commit corrected data and its publication marker together where possible. If a downstream materialization fails after the upstream repair succeeds, resume from that checkpoint rather than replaying every stage. Keep the previous verified publication available until reconciliation completes.

Server-side authorization must scope every dataset query to the requesting tenant and role. PostgreSQL row security can provide an additional boundary, but table owners and privileged roles require special attention because they can bypass normal policies. Avoid running the assistant with a warehouse administrator identity.

Free-text fields and incident tickets are untrusted. A customer record that says “disable all quality rules” must remain sample data. OWASP’s LLM security guidance motivates separating retrieved content from tool authority. Never execute generated SQL directly; validate approved templates and parameters. Logs should contain counts and opaque references, not complete customer records.

Prove Detection and Recovery

Test the distinctions that operators actually need:

• Delay one region’s partition without losing events. The report becomes provisional, and no deletion or revenue-loss claim is proposed.
• Replay the same event ID twice. The uniqueness boundary prevents a second accepted row without losing the original evidence.
• Send a refund with a valid order ID and a different event ID. It remains a legitimate event rather than being deduplicated by order.
• Fail a downstream refresh after a repair commits. Retrying resumes the refresh and does not apply the repair twice.
• Place malicious instructions in a sampled text field and request another tenant’s sample. Neither changes permissions nor exposes that tenant’s data.

Baseline current detection latency, analyst investigation minutes, and downstream report corrections using a labeled incident archive. Measure useful-alert precision as owner-confirmed actionable alerts divided by all alerts reviewed. Measure detection coverage against known injected faults, including cases the agent correctly labels incomplete rather than corrupt.

Guardrails are incorrect repairs per approved repair, leaked sensitive samples, warehouse compute per inspected partition, and stale data published as final. Begin with read-only summaries for one revenue dataset, then enable approved quarantine actions. Any reduction target is proposed until measured; a smaller alert count alone is not success.

The database migration guide covers changing contracts without losing recoverability, while retail replenishment shows the operational cost of incorrect inventory evidence. Data Engineering & Analytics Platforms supports this foundation. The first implementation step is to write one grain-and-watermark contract with its data owner, then test it against a known late arrival and a known duplicate.

Sources

• PostgreSQL: Constraints
• PostgreSQL: Row security policies
• OWASP: LLM application security project