Real-Time Data Quality: Handling Late Data, Reconciliation, and Alerting

In offline warehouses, you can wait for a batch to finish before checking quality. In real-time pipelines, the picture changes minute by minute: a 10:00 query and a 10:03 query may return different row counts and amounts.

Missing orders could mean data hasn't arrived yet or was lost during processing. Mismatched amounts between two tables might stem from calculation errors or from querying different points in time. Until these questions are answered, adding more NOT NULL or uniqueness checks won't build business trust.

The author recommends starting with a single order pipeline — trace why amounts disagree, then generalize the checks. Follow the order from the source database into Flink, written to Paimon, and finally queried in Doris.

How Late Counts as a Problem

Suppose operations needs the payment amount for the 10:00–10:05 window, keyed by payment occurrence time (a payment at exactly 10:05 belongs to the next window). By 10:05 the source may still have one payment unsynchronized. If you reconcile immediately, that payment appears as a discrepancy; seconds later it arrives and the alert auto-recovers. Daily repetitions teach recipients to ignore notifications.

Therefore, agree with consumers on a deadline: by what time must the five-minute window be ready? For example, 10:07 — two minutes for sync, computation, and validation. The two minutes is illustrative; actual latency and business tolerance decide.

Before the deadline, dashboards can show updating amounts with a “data not complete” label. At the deadline, missing payments should trigger an exception. Otherwise the system forever hides behind “wait a bit more” without a hard timeout.

Flink watermarks are often misused here. Watermarks advance event time and control allowed disorder; they cannot alone prove all source orders have arrived. Validation must also examine source progress, backlog, and whether any partition has stopped advancing. Flink event-time documentation (https://nightlies.apache.org/flink/flink-docs-stable/docs/concepts/time.html).

Another pitfall: a period with zero new orders does not necessarily mean a stalled pipeline. Combine source heartbeats or CDC offsets to decide; don't treat “no updates for ten minutes” as an automatic failure.

If a late payment arrives at 10:09, re-compute and re-validate to mark the window usable again. But the fact that the 10:07 deadline was missed must remain recorded — monthly on-time delivery rates cannot erase the lateness just because data was later repaired.

When Amounts Don't Match, First Find a Few Orders

Summing source and result tables and seeing inequality is only the start of investigation. Next, pinpoint the discrepancy: break down by payment channel or merchant to narrow scope, then locate missing, duplicate, or amount-mismatched records by business primary key. A rule that only says “total amount mismatch” forces analysts to rewrite SQL every time — it's not yet daily-usable.

With a handful of concrete orders, many definition mismatches surface:

Source filters by payment time, downstream by order creation time. An order created at 09:59 and paid at 10:01 naturally falls into different windows on each side.

Both sides filter by the same payment time, but one side has already received a subsequent refund update while the other hasn't synced that state yet.

Hence reconciliation SQL must first align business time, filter conditions, and read cut-offs. Real-time tables update continuously; two ad-hoc queries may compare different snapshots.

CDC record counts need care: one order from creation to payment generates multiple change events. Counting “change events” differs from counting “current orders”. How refunds and chargebacks factor into amounts must be encoded in rules, not covered by a single “amount > 0” check.

Paimon primary-key tables merge same-key records per configuration. Absence of duplicate keys in the final table does not prove upstream didn't send duplicate events. To detect re-emission or duplicate consumption, inspect event identifiers and source offsets — don't rely solely on the merged table. Paimon primary-key table documentation (https://paimon.apache.org/docs/master/concepts/primary-key-table/).

Check results should include a few difference samples plus queryable context. Mask PII like phone numbers and addresses, but retain order IDs, window ranges, and rule versions needed for drill-down.

Check SQL

If the pipeline already uses Flink, push low-cost, row-level checks into the processing stage: null order IDs, parsing failures, state values outside agreed enums. These don't need to wait until data lands in the warehouse.

Bad records must be retrievable. Dropping parsing failures silently leaves downstream with fewer rows; when business asks why rows are missing and whether they can be backfilled, answers are hard. Write exceptions to a separate dataset preserving source payload and failure reason, then decide per business policy whether the main pipeline continues.

Cross-table reconciliation need not all live in the streaming job. Checks requiring multi-table scans and multi-state joins can run after data lands, in Doris or similar query engines — easier to hand the difference query to investigators.

But “scheduled SQL” must not become a full-table scan every minute. Restrict time ranges and partitions; aggregate total rows and exception rows in the same scan; set timeouts and concurrency limits. A quality job saturating the query cluster slows regular reports.

Expensive checks can be sampled, but the UI must clearly state the scope. “Sampled 10,000 rows, no issues” must not be displayed as “entire table passed”.

An often overlooked issue: data that passed morning checks may be updated by afternoon. To reproduce the earlier conclusion, preserve the data version read at check time. Paimon supports snapshot or tag queries, but the actual query path must support and use it, and the snapshots must be retained. Merely storing a snapshot ID in the check record has no effect. Paimon snapshot query documentation (https://paimon.apache.org/docs/master/maintenance/snapshot/).

Alert Fired — Should Data Still Be Served Downstream?

For ops dashboards, a few late records may be acceptable: show the amount, label update time and “pending completion”, correct later. Settlement data is stricter: if amounts don't reconcile, the window is withheld from settlement programs.

This decision must be enforced at the query entry point. A management UI flagged red while the API silently returns an unexplained amount leads business to treat it as normal. If fallback to the previous usable window is allowed, the API must explicitly tell callers which time range the returned data covers. Accounts with direct table access must also be considered.

State design must distinguish “check failed” from “check didn't run”. Query timeout, engine unavailable, missing rule configuration — none count as pass. A window that should have been checked but had no task created must be detected.

Notifications should be actionable. Instead of “data quality anomaly”, say “10:00–10:05, WeChat Pay channel missing three orders, difference orders viewable here”.

When data is repaired, re-run the original rules and save the new result. A manual “handled” click only notes human intervention; it cannot replace re-validation. If filters or thresholds were changed during handling, record the rule version — otherwise a later “pass” leaves ambiguity: was the data fixed or the standard relaxed?

For erroneous results already consumed downstream, identify affected reports or callers and notify them to re-fetch or re-compute. A quality dashboard turning green does not auto-correct yesterday's exported spreadsheets.

Quality Results

Starting from zero, pick one owned, actively used metric — e.g., payment amount. Wire its source tables, detail table, aggregate result, and API together; don't rush to score every table.

First round: verify sync timeliness and key-field completeness, then add same-window amount reconciliation. Run rules for a while, inspect each anomaly: which are real issues, which are refund-definition misalignments, which are caused by too-short wait windows. Only after clarification let rules participate in blocking.

At this point, proactively inject a failure: pause sync, let the window miss its deadline, then resume sync. Observe whether the alert pinpoints the window, whether the strict API blocks, whether re-check after repair restores usability, and whether the original lateness record persists. Then stop the detection engine to confirm the system shows “check error” instead of silently passing.

Once this end-to-end loop works, extend rule templates, more tables, and higher concurrency — now with a proven pattern to follow.

The priority is making the difference-query link in alerts work. Next time amounts drop, the on-call engineer can directly locate the discrepant orders and trace back via source offsets. Additional features are added based on gaps discovered during these investigations.