Data Quality, Reconciliation, and Data Health Model

1. Core Idea

Data quality dan reconciliation adalah mekanisme untuk memastikan bahwa data production tetap benar setelah melewati banyak API, workflow, event, batch job, external system, retry, manual repair, and asynchronous processing.

Validation mencegah data buruk masuk.

Constraint mencegah state mustahil tersimpan.

Reconciliation mendeteksi mismatch yang tetap lolos atau muncul karena distributed system.

Data health menjadikan correctness sebagai sesuatu yang bisa dimonitor, diberi owner, diperbaiki, dan dilaporkan.

Mental model:

Data quality is not a one-time check. It is an operational control loop: define rule, detect violation, assign owner, repair, verify, and prevent recurrence.

2. Why Data Quality Modelling Matters

Enterprise quote-to-cash systems sangat rentan data drift.

Contoh:

• quote accepted tetapi order tidak pernah dibuat,
• order completed tetapi mandatory item masih open,
• product active tetapi recurring charge tidak active,
• charge active tetapi billing account inactive,
• product terminated tetapi billing masih jalan,
• invoice total tidak sama dengan invoice lines,
• fulfillment completed tetapi product inventory tidak updated,
• service active di OSS tetapi product inactive di BSS,
• usage rated tetapi charge tidak dibuat,
• approval approved tetapi quote version berubah,
• outbox event failed tetapi tidak ada alert,
• projection stale tetapi dipakai untuk decision.

Tanpa data quality dan reconciliation model, masalah ini hanya ditemukan saat customer complaint, billing dispute, incident, or financial close.

3. Data Quality vs Reconciliation

Example:

Data quality rule:
  active billing account must have billing profile.

Reconciliation:
  active product instance should have active recurring charge.

Data health:
  product-to-charge reconciliation has 12 open critical mismatches.

4. Categories of Data Quality Rules

Common categories:

Each category may require different severity, owner, and repair process.

5. Data Quality Rule Model

A rule should be first-class metadata.

Fields:

data_quality_rule
- id
- rule_code
- rule_name
- description
- domain_area
- entity_type
- category
- severity
- owner_group
- check_type
- active
- schedule
- sla
- documentation_url

Example rule:

rule_code = ACTIVE_PRODUCT_REQUIRES_ACTIVE_CHARGE
domain_area = BILLING
entity_type = PRODUCT_INSTANCE
category = RECONCILIATION
severity = CRITICAL
owner_group = billing-ops

Rule metadata supports dashboards, ownership, and incident routing.

6. Rule Result Model

Rule result stores detection.

Fields:

data_quality_result
- id
- rule_id
- entity_type
- entity_id
- entity_number
- result_status
- severity
- detected_at
- first_detected_at
- last_detected_at
- occurrence_count
- owner_group
- resolution_status
- resolved_at
- resolution_action
- correlation_id

Important distinction:

• detected_at: this run detected it.
• first_detected_at: when it first became known.
• last_detected_at: latest observed time.
• occurrence_count: repeated detections.

This prevents noisy duplicates and supports aging.

7. Reconciliation Model

Reconciliation compares expected and actual across systems/entities.

Fields:

reconciliation_result
- id
- reconciliation_type
- source_system
- target_system
- source_entity_type
- source_entity_id
- target_entity_type
- target_entity_id
- expected_state
- actual_state
- result_status
- mismatch_code
- severity
- detected_at
- owner_group
- repair_status

Examples:

8. Severity Model

Severity must reflect business impact.

Severity should drive:

• alerting,
• SLA,
• owner,
• escalation,
• repair priority,
• incident creation.

Do not make every rule critical. That creates alert fatigue.

9. Ownership Model

Every rule needs owner.

Owner can be:

• domain team,
• service owner,
• ops group,
• billing ops,
• fulfillment ops,
• catalog admin,
• data platform,
• support team.

Fields:

owner_group
primary_owner
escalation_group
business_owner
technical_owner

Without owner, data quality result becomes ignored dashboard noise.

10. SLA and Aging

Data quality issues must have aging.

Fields:

sla_duration
due_at
breached_at
aging_bucket
escalation_status

Example SLA:

Aging supports:

• operational review,
• incident escalation,
• backlog prioritization,
• trend reporting.

11. Repair Workflow

Detection is not enough. Model repair.

Repair states:

OPEN
ASSIGNED
INVESTIGATING
REPAIR_PENDING
REPAIR_IN_PROGRESS
REPAIRED
VERIFICATION_PENDING
VERIFIED
FALSE_POSITIVE
WONT_FIX
CANCELLED

Repair fields:

data_repair_case
- id
- data_quality_result_id
- assigned_to
- assigned_group
- repair_type
- status
- root_cause_code
- repair_action
- approval_reference
- applied_at
- verified_at

This turns data quality from passive report into operational workflow.

12. Root Cause Model

Root cause should be captured.

Categories:

• application bug,
• missing validation,
• missing constraint,
• integration failure,
• event lost,
• consumer failed,
• manual correction error,
• stale projection,
• external system mismatch,
• migration bug,
• configuration error,
• rule defect,
• data import issue.

Root cause fields:

root_cause_code
root_cause_description
preventive_action
linked_incident
linked_defect

Without root cause, same data issue returns repeatedly.

13. False Positive and Suppression

Some rules may produce false positives.

Example:

Active product without charge

May be valid for free product, test account, internal account, or non-billable product.

Model suppression:

data_quality_suppression
- id
- rule_id
- entity_type
- entity_id
- reason_code
- approved_by
- effective_from
- effective_to

Suppression must be:

• time-bound,
• reasoned,
• approved,
• audited.

Do not disable the rule globally because of one exception.

14. Data Quality Run

Batch/rule execution should be tracked.

Fields:

data_quality_run
- id
- run_type
- started_at
- completed_at
- status
- rule_count
- checked_entity_count
- violation_count
- error_count
- triggered_by

This helps detect:

• rule job not running,
• rule runtime too slow,
• rule execution failure,
• sudden violation spike.

15. Continuous vs Batch Checks

Checks can be:

Example:

• duplicate order from quote: synchronous DB uniqueness.
• accepted quote without order: scheduled or event-driven reconciliation.
• active product without charge: scheduled reconciliation.
• invoice total mismatch: batch financial control.

16. Quote-to-Order Reconciliation

Rule:

Accepted quote should have exactly one corresponding order unless conversion was cancelled/failed with explicit state.

Potential query:

select q.id, q.quote_number, q.version
from quote q
left join product_order o
  on o.source_quote_id = q.id
 and o.source_quote_version = q.version
where q.status = 'ACCEPTED'
  and q.accepted_at < now() - interval '15 minutes'
  and o.id is null;

Potential mismatch codes:

• ORDER_MISSING
• MULTIPLE_ORDERS
• ORDER_SOURCE_VERSION_MISMATCH
• CONVERSION_STUCK

17. Order-to-Fulfillment Reconciliation

Rule:

Submitted/decomposed order should have fulfillment tasks.

Checks:

• order submitted but no decomposition,
• decomposition completed but no tasks,
• task completed but order item still in progress,
• order completed with task in fallout,
• cancelled order with active downstream task.

Mismatch codes:

• DECOMPOSITION_MISSING
• TASK_MISSING
• TASK_ORDER_STATE_MISMATCH
• CANCELLED_ORDER_ACTIVE_TASK

18. Fulfillment-to-Inventory Reconciliation

Rule:

Fulfilled ADD order item should create active product instance.
Fulfilled DISCONNECT should terminate product instance.

Mismatch codes:

• PRODUCT_INSTANCE_MISSING
• PRODUCT_STATUS_NOT_UPDATED
• DUPLICATE_PRODUCT_INSTANCE
• SOURCE_ORDER_ITEM_MISSING
• FULFILLMENT_COMPLETED_INVENTORY_PENDING

This is critical because inventory drives future modify/disconnect/billing.

19. Product-to-Billing Reconciliation

Rule:

Active billable product should have active recurring charge.
Terminated product should not have active recurring charge past billing end.

Mismatch codes:

• ACTIVE_PRODUCT_WITHOUT_CHARGE
• TERMINATED_PRODUCT_WITH_ACTIVE_CHARGE
• CHARGE_BILLING_ACCOUNT_MISMATCH
• CHARGE_AMOUNT_MISMATCH
• CHARGE_EFFECTIVE_PERIOD_GAP
• DUPLICATE_ACTIVE_CHARGE

This is often financially critical.

20. Billing-to-Invoice Reconciliation

Rules:

• billed charge should appear on invoice line,
• invoice total equals invoice lines,
• invoice billing account matches charge billing account,
• paid invoice should have payment record/status,
• voided invoice should not contribute to revenue.

Mismatch codes:

• INVOICE_LINE_MISSING
• INVOICE_TOTAL_MISMATCH
• BILLING_ACCOUNT_MISMATCH
• PAYMENT_STATUS_MISMATCH
• VOIDED_INVOICE_REVENUE_INCLUDED

21. Event/Outbox Reconciliation

Rules:

• critical state change has outbox event,
• pending outbox does not age beyond threshold,
• published event has broker acknowledgement if tracked,
• consumer inbox processed expected event,
• projection updated to event version.

Mismatch codes:

• OUTBOX_EVENT_MISSING
• OUTBOX_STUCK
• CONSUMER_INBOX_MISSING
• PROJECTION_STALE
• EVENT_VERSION_REGRESSION

This protects event-driven reliability.

22. Data Health Score

A data health score can summarize domain status.

Example formula:

health_score = 100
  - critical_open * 10
  - error_open * 3
  - warning_open * 1
  - sla_breach_penalty

Use carefully. Do not let score hide critical issues.

Better dashboard:

• open critical count,
• oldest critical age,
• SLA breach count,
• trend by domain,
• top mismatch codes,
• owner backlog,
• repaired count,
• recurrence rate.

23. PostgreSQL Physical Design

Rule table:

create table data_quality_rule (
  id uuid primary key,
  rule_code text not null unique,
  rule_name text not null,
  description text,
  domain_area text not null,
  entity_type text not null,
  category text not null,
  severity text not null,
  owner_group text,
  check_type text not null,
  schedule text,
  sla_interval interval,
  active boolean not null default true,
  created_at timestamptz not null,
  updated_at timestamptz not null
);

Run table:

create table data_quality_run (
  id uuid primary key,
  run_type text not null,
  status text not null,
  started_at timestamptz not null,
  completed_at timestamptz,
  rule_count integer,
  checked_entity_count bigint,
  violation_count bigint,
  error_count bigint,
  triggered_by text
);

Result table:

create table data_quality_result (
  id uuid primary key,
  rule_id uuid not null references data_quality_rule(id),
  run_id uuid references data_quality_run(id),
  entity_type text not null,
  entity_id uuid not null,
  entity_number text,
  result_status text not null,
  severity text not null,
  detected_at timestamptz not null,
  first_detected_at timestamptz,
  last_detected_at timestamptz,
  occurrence_count integer not null default 1,
  owner_group text,
  resolution_status text not null,
  resolved_at timestamptz,
  resolution_action text,
  correlation_id text,
  metadata jsonb
);

Reconciliation result:

create table reconciliation_result (
  id uuid primary key,
  reconciliation_type text not null,
  source_system text not null,
  target_system text not null,
  source_entity_type text not null,
  source_entity_id text not null,
  target_entity_type text,
  target_entity_id text,
  expected_state text,
  actual_state text,
  result_status text not null,
  mismatch_code text,
  severity text not null,
  detected_at timestamptz not null,
  owner_group text,
  repair_status text not null,
  metadata jsonb
);

Indexes:

create index idx_dq_result_open
on data_quality_result (severity, owner_group, first_detected_at)
where resolution_status not in ('RESOLVED', 'VERIFIED', 'FALSE_POSITIVE');

create index idx_dq_result_rule_open
on data_quality_result (rule_id, severity, first_detected_at)
where resolution_status not in ('RESOLVED', 'VERIFIED', 'FALSE_POSITIVE');

create index idx_recon_open
on reconciliation_result (reconciliation_type, severity, detected_at)
where repair_status not in ('RESOLVED', 'VERIFIED', 'FALSE_POSITIVE');

create index idx_recon_source
on reconciliation_result (source_entity_type, source_entity_id);

24. Java/JAX-RS Backend Implications

Possible APIs:

GET /data-quality/rules
GET /data-quality/results?severity=CRITICAL&status=OPEN
POST /data-quality/runs
POST /data-quality/results/{id}/assign
POST /data-quality/results/{id}/resolve
GET /reconciliation-results
POST /reconciliation-runs

Service responsibilities:

• execute checks safely,
• avoid heavy queries on primary OLTP if risky,
• deduplicate repeated violations,
• assign owner,
• persist results,
• publish alerts/events,
• support repair workflow,
• verify resolution.

Data quality checks should not silently overload production database. Use replicas/read models when appropriate.

25. MyBatis/JPA/JDBC Implications

MyBatis

Useful for explicit SQL-based checks and reconciliation queries.

JPA

Less ideal for large batch checks; can cause N+1 and memory issues.

JDBC

Good for scheduled SQL scans, batch insert results, and repair jobs.

General rule:

Data quality checks are operational workloads. Design their queries and indexes intentionally.

26. Reporting and Dashboard Impact

Dashboard should show:

• open issues by severity,
• open issues by owner,
• oldest issue,
• SLA breaches,
• new vs resolved,
• recurrence by rule,
• domain health,
• financial impact estimate,
• customer impact estimate,
• repair backlog,
• false positive count,
• rule execution health.

For executive view:

• critical mismatches,
• billing-impacting issues,
• customer-impacting issues,
• trend and root cause.

For engineer view:

• entity IDs,
• mismatch details,
• SQL evidence,
• last event/correlation ID,
• suggested repair action.

27. Alerting

Not every violation should page.

Alert policy:

Alert should include:

• rule code,
• entity,
• severity,
• owner,
• age,
• correlation ID,
• dashboard link,
• suggested action.

28. Repair Safety

Repair can be dangerous.

Before repair:

• identify source of truth,
• understand impact,
• check if business approval required,
• verify no concurrent process,
• preserve before/after snapshot,
• use command/API where possible,
• avoid direct SQL unless approved,
• emit audit event,
• rerun reconciliation.

After repair:

• verify data quality rule passes,
• update repair case,
• link incident/root cause,
• create preventive action if repeated.

29. Anti-Patterns

Avoid:

• one-off SQL scripts with no audit,
• dashboards without owner,
• rules with no severity,
• alerts for every warning,
• suppressing rules permanently,
• checking only current state, not historical/effective period,
• reconciliation that reports mismatch but no repair workflow,
• data quality queries that overload primary DB,
• treating data warehouse checks as substitute for operational reconciliation,
• manual spreadsheet tracking of critical mismatch.

30. Failure Modes

31. PR Review Checklist

When reviewing data quality/reconciliation changes, ask:

• What correctness risk is being monitored?
• Is this synchronous validation or async reconciliation?
• What is the rule code?
• What entity/system is source of truth?
• What is expected vs actual state?
• What severity applies?
• Who owns the issue?
• What is the SLA?
• How are repeated detections deduplicated?
• Is there a repair workflow?
• Is false positive/suppression handled?
• Is query/index safe for production?
• Does this rule need dashboard/alert?
• How is resolution verified?
• Is root cause captured?
• Are incidents/financial impact linked?

32. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Existing data quality framework or dashboards.
• Existing reconciliation jobs between quote/order/fulfillment/inventory/billing.
• Whether DQ rules have owners and SLAs.
• Whether DQ results are persisted or only logged.
• Whether repair workflow exists.
• Whether manual data fixes are audited.
• Whether active product vs charge reconciliation exists.
• Whether accepted quote vs order reconciliation exists.
• Whether order/fulfillment stuck state monitors exist.
• Whether outbox/inbox/projection health is monitored.
• Whether DQ queries run on primary DB, replica, or analytics store.
• Whether false positives are managed.
• Whether root cause/preventive action is tracked.
• Whether incidents mention data drift, missed reconciliation, stale projection, or ignored DQ alerts.

33. Summary

Data quality and reconciliation turn correctness into operations.

A strong model must define:

• rule metadata,
• severity,
• owner,
• SLA,
• run history,
• result persistence,
• reconciliation expected vs actual state,
• deduplication,
• suppression,
• repair workflow,
• root cause,
• verification,
• dashboard,
• alerting,
• production-safe execution.

The key principle:

Validation prevents known bad input. Reconciliation detects distributed truth drift. Data health makes correctness visible, owned, repairable, and measurable.