Data Modelling Anti-Patterns, Smell Detection, and Refactoring Model

1. Core Idea

Data model enterprise jarang rusak dalam satu perubahan besar. Biasanya rusak perlahan melalui field kecil, status baru, shortcut query, JSON tambahan, manual repair, duplicate table, and integration exception.

Dalam CPQ / Quote / Order / Billing / Catalog / Telco BSS/OSS, data model anti-pattern dapat menyebabkan:

• quote tidak bisa dipercaya,
• order lifecycle ambigu,
• fulfillment dan billing tidak sinkron,
• product inventory tidak mencerminkan installed base,
• reporting/KPI berdebat,
• migration menjadi berisiko,
• support membutuhkan tribal knowledge,
• event consumers break,
• manual SQL repair menjadi rutinitas.

Mental model:

Data modelling excellence is not only designing clean models. It is detecting model decay early and refactoring safely before production correctness collapses.

2. What Is a Data Smell?

Data smell adalah tanda bahwa model mungkin bermasalah.

Smell belum tentu bug, tetapi perlu investigasi.

Examples:

• field name terlalu generic,
• same concept appears in multiple places,
• status has too many meanings,
• nullable field with unclear semantics,
• JSONB contains business-critical fields,
• dashboard filters by magic string,
• manual SQL repair frequently needed,
• integration mapping done in application constants,
• event payload grows into full database dump,
• table has many unrelated optional columns,
• enum changes every sprint,
• reports disagree on same KPI,
• query joins across service-owned tables directly.

Smell adalah early warning.

3. Anti-Pattern Categories

A senior engineer should learn to recognize these quickly.

4. Duplicate Source of Truth

Smell:

quote.billing_account_id
order.billing_account_id
billing_trigger.billing_account_id
charge.billing_account_id
invoice.billing_account_id

This can be valid if snapshots are intentional. It is dangerous if nobody knows which one is authoritative.

Questions:

• Which is source-of-truth?
• Which are snapshots?
• Which are derived?
• Which can be changed?
• What happens if they differ?
• Which one is used for billing?
• Which one is used for reporting?
• Is reconciliation defined?

Refactoring:

• document ownership,
• rename snapshot fields explicitly,
• add lineage,
• add data quality rule,
• centralize mutation,
• remove duplicate mutable source where possible.

5. Overloaded Status Field

Bad:

order.status = COMPLETED

But completed could mean:

• customer submitted order,
• fulfillment completed,
• billing completed,
• order closed,
• all tasks done,
• no further action allowed.

Better:

order.lifecycle_status
order.fulfillment_status
order.billing_status
order.closure_status

Status smell indicators:

• many if status in (...) scattered across code,
• UI label differs from API meaning,
• reporting creates custom status buckets,
• billing and fulfillment interpret status differently,
• new statuses added to fix one scenario.

Refactoring:

• split status dimensions,
• define state machines,
• migrate existing values,
• publish compatibility mapping,
• update reporting grouping,
• add transition guards.

6. God Table

God table contains too many unrelated concepts.

Example:

order table contains:
  quote fields
  customer fields
  billing fields
  fulfillment fields
  invoice fields
  workflow fields
  audit fields
  external payload fields

Symptoms:

• many nullable columns,
• unclear ownership,
• updates from many services,
• row lock contention,
• confusing API DTOs,
• table hard to migrate,
• index bloat,
• accidental sensitive data exposure.

Refactoring:

• separate lifecycle core from details,
• move one-to-many details to child tables,
• split snapshots from current state,
• separate operational metadata,
• define aggregate boundaries,
• preserve compatibility view if needed.

7. Free-Text Business Meaning

Bad:

cancel_reason = "customer changed mind"
fallout_reason = "bad address maybe"
adjustment_reason = "fix billing"

Problems:

• impossible reliable reporting,
• no owner,
• no routing,
• no severity,
• no localization,
• no integration mapping.

Better:

reason_code = CUSTOMER_REQUEST
reason_text = optional human details

Refactoring:

• create code set,
• map historical free text to code if possible,
• preserve original text as evidence,
• require code for new records,
• update reports and dashboards.

8. Magic Strings and Hidden Codes

Smell:

if ("X12".equals(order.getReason())) { ... }

or SQL:

where status in ('A', 'B', 'Z9')

without reference data.

Problems:

• business meaning hidden in code,
• hard to audit,
• hard to localize,
• hard to integrate,
• hard to change safely.

Refactoring:

• define code set,
• document values,
• expose reference data,
• update code to use named constants or reference lookup,
• add contract tests for allowed values.

9. JSONB Abuse

JSONB is powerful but dangerous when used to avoid modelling.

Smell:

order.extra_data contains:
  billingAccountId
  approvalStatus
  fulfillmentState
  productConfig
  margin
  externalIds

Problems:

• no constraints,
• no indexing strategy,
• no field-level security,
• no lineage,
• hard reporting,
• hidden breaking changes,
• unclear ownership.

Valid use:

• immutable snapshot,
• external payload reference,
• flexible characteristics,
• audit evidence,
• extension fields with schema governance.

Refactoring:

• extract fields used for query/invariant/security,
• keep JSON snapshot for evidence,
• add schema validation,
• add classification,
• define versioned payload structure.

10. Entity-as-DTO Anti-Pattern

Bad:

Expose database entity directly as API response.

Problems:

• internal fields leak,
• sensitive data exposed,
• DB schema becomes API contract,
• versioning impossible,
• lazy loading/performance issues,
• clients depend on internal structure.

Refactoring:

• define DTOs,
• map only allowed fields,
• add field-level authorization,
• version API contract,
• add OpenAPI/contract tests,
• classify sensitive fields.

11. Shared Database Coupling

Smell:

Service A reads/writes Service B tables directly.

Problems:

• ownership unclear,
• deployments coupled,
• schema change risky,
• invariants bypassed,
• audit incomplete,
• service boundary fake.

Sometimes reporting/read-only access exists, but must be governed.

Refactoring:

• expose API/event/read model,
• migrate direct writes to domain commands,
• create projection for read needs,
• add ownership documentation,
• restrict DB permissions,
• plan strangler migration.

12. Current-State Overwrite

Bad:

product.price = latest price
customer.segment = latest segment
billing_address = latest address

without historical snapshot.

Problems:

• old quote changes meaning,
• invoice dispute cannot be explained,
• historical reporting changes,
• approval evidence invalid,
• billing period unclear.

Refactoring:

• snapshot at transaction boundary,
• use effective dating,
• add history table,
• separate current reference from historical evidence,
• rebuild historical snapshots if possible.

13. Audit-as-Dump Anti-Pattern

Bad:

audit.payload = full request/response/entity dump

Problems:

• PII leak,
• secrets leak,
• huge storage,
• difficult search,
• no structured change,
• retention hard,
• before/after unclear.

Better:

command audit + structured field changes + secure evidence reference

Refactoring:

• classify audit payload,
• redact sensitive fields,
• store structured before/after for important fields,
• store large payload in secure object with retention,
• add audit schema version.

14. Status Patch API

Bad:

PATCH /orders/{id}
{
  "status": "COMPLETED"
}

Problems:

• bypasses business command,
• no transition guard,
• no reason,
• no side effect,
• no audit semantics,
• no fulfillment/billing consistency.

Better:

POST /orders/{id}/complete
POST /orders/{id}/cancel
POST /orders/{id}/mark-fulfillment-complete

Refactoring:

• replace status patch with command endpoints,
• keep patch only for safe metadata,
• enforce transition service,
• audit command intent,
• emit correct events.

15. Mutable Immutable Records

Bad:

Update accepted quote price after customer acceptance.
Update issued invoice amount directly.
Update approved decision evidence.

Problems:

• audit/legal issues,
• customer dispute,
• financial mismatch,
• reporting changes historically.

Refactoring:

• create revision,
• create correction record,
• create adjustment/credit/debit,
• supersede artifact,
• preserve original,
• add immutable constraint/guard.

16. Missing Idempotency

Smell:

• create endpoint can be retried,
• external callback may duplicate,
• event consumer side effect not deduped,
• file row can be reprocessed,
• charge creation has no natural unique key.

Problems:

• duplicate order,
• duplicate charge,
• duplicate product instance,
• duplicate notification,
• duplicate external request.

Refactoring:

• define idempotency key,
• add unique constraint,
• persist idempotency record,
• include request hash,
• return existing result on retry,
• test duplicate scenario.

17. Reporting from Unclear Grain

Smell:

Dashboard counts quote rows but table stores quote revisions.

Problems:

• double counting,
• conversion rate wrong,
• trend inconsistent,
• cancellation/expiry unclear.

Refactoring:

• define fact grain,
• create analytics fact/read model,
• document metric definition,
• include revision handling,
• add data quality checks,
• deprecate old dashboard.

18. Derived Field Without Lineage

Bad:

mrr_amount
billing_health_status
serviceability_status
conversion_status

without explanation of how computed.

Problems:

• consumer trusts wrong field,
• stale value invisible,
• recalculation impossible,
• debugging hard.

Refactoring:

• store source fields/version,
• store calculated_at,
• store calculation rule version,
• define owner,
• add freshness/reconciliation,
• expose lineage.

19. External ID Confusion

Smell:

external_id
crm_id
billing_id
source_id
legacy_id

used inconsistently.

Problems:

• callback updates wrong entity,
• support cannot search,
• reconciliation joins wrong,
• tenant/environment collision.

Refactoring:

• create external_reference model,
• include source_system/entity_type/tenant/environment,
• preserve history,
• add uniqueness checks,
• add mapping audit,
• normalize identifier formats.

20. Refactoring Strategy

Production-safe refactoring:

1. Identify smell and impact.
2. Document current behavior.
3. Add tests around existing behavior.
4. Introduce new model additively.
5. Dual-write or backfill if needed.
6. Shadow-read compare old/new.
7. Migrate consumers.
8. Switch read/write path.
9. Reconcile.
10. Deprecate old model.
11. Remove old model after compatibility window.

Do not "clean up" critical production data model in one risky deploy.

21. Refactoring Decision Matrix

22. PostgreSQL Refactoring Examples

Extract field from JSONB:

alter table product_order
add column billing_account_id uuid;

-- Backfill in batches:
update product_order
set billing_account_id = (extra_data ->> 'billingAccountId')::uuid
where billing_account_id is null
  and extra_data ? 'billingAccountId';

Add uniqueness safely:

create unique index concurrently uq_order_source_quote_version
on product_order (source_quote_id, source_quote_version)
where source_quote_id is not null;

Add code set while preserving text:

alter table product_order
add column cancellation_reason_code text;

-- Keep cancellation_reason_text for historical/original explanation.

Validate before enforcing constraints.

23. Refactoring Metadata Model

Track refactoring.

data_refactoring
- id
- refactoring_code
- smell_type
- affected_asset
- risk_level
- status
- owner_group
- started_at
- completed_at

Phases:

IDENTIFIED
APPROVED
EXPAND
BACKFILL
SHADOW_COMPARE
CUTOVER
CONTRACT
VERIFIED
CANCELLED

This aligns with migration/governance.

24. Java/JAX-RS Backend Implications

During refactor:

• accept both old/new API fields if needed,
• define precedence rules,
• map old contract to new domain model,
• write compatibility tests,
• keep command semantics stable,
• expose deprecation warnings,
• prevent new code from using old field directly,
• add feature flag for read switch.

Example:

Old: order.status
New: order.lifecycleStatus + fulfillmentStatus + billingStatus

API v1 may still expose status mapped from new fields during compatibility window.

25. Test Strategy for Refactoring

Test:

• old behavior preserved,
• new model produces same result,
• backfill idempotent,
• shadow-read mismatch captured,
• old/new API compatibility,
• event consumer compatibility,
• reporting totals unchanged or intentionally changed,
• DQ/reconciliation passes,
• rollback/forward-fix works.

Golden datasets are essential for safe refactoring.

26. Observability

Monitor:

• old field usage,
• deprecated API/event usage,
• shadow-read mismatches,
• backfill progress,
• compatibility error rate,
• data quality mismatch,
• consumer migration status,
• query performance after new index/model,
• manual repair count before/after refactor.

Refactoring success should be measured.

27. Anti-Pattern Detection Queries

Examples:

-- Suspicious nullable-column-heavy table metadata would usually come from catalog,
-- not pure SQL alone.

-- Duplicate source quote conversion
select source_quote_id, source_quote_version, count(*)
from product_order
where source_quote_id is not null
group by source_quote_id, source_quote_version
having count(*) > 1;

-- Current effective-dated duplicates
select product_instance_id, characteristic_name, count(*)
from product_instance_characteristic
where effective_to is null
group by product_instance_id, characteristic_name
having count(*) > 1;

-- Active product without charge
select pi.id
from product_instance pi
left join recurring_charge rc
  on rc.product_instance_id = pi.id
 and rc.status = 'ACTIVE'
where pi.status = 'ACTIVE'
  and rc.id is null;

These queries are illustrative and depend on internal schema.

28. Failure Modes

29. PR Review Checklist

When reviewing suspicious data changes, ask:

• Is this introducing duplicate source-of-truth?
• Is this field semantically clear?
• Is null meaning defined?
• Is this status overloaded?
• Is JSONB hiding query/invariant fields?
• Is entity being exposed directly as DTO?
• Is this bypassing domain command?
• Is historical truth being overwritten?
• Is idempotency missing?
• Is external ID scoped?
• Is reporting grain clear?
• Is lineage defined?
• Is sensitive data classified?
• Is refactoring plan safe and incremental?
• Is old behavior covered by tests?

30. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Common historical data modelling debt areas.
• Tables/services with overloaded status or many nullable fields.
• Any direct cross-service DB access.
• Any critical JSON fields used for billing/order/fulfillment.
• Any known duplicate source-of-truth.
• Any free-text reason codes in production flow.
• Any status patch/admin update endpoints.
• Any manual SQL repair patterns.
• Any reports with disputed KPI definitions.
• Any incidents caused by stale cache/projection, duplicate external mapping, or missing idempotency.
• Existing refactoring governance/process.

31. Summary

Senior engineers must be able to detect and fix data model decay.

A strong anti-pattern/refactoring model must define:

• smells,
• duplicate source-of-truth detection,
• overloaded status detection,
• god table detection,
• JSONB abuse detection,
• free-text reason replacement,
• DTO boundary,
• shared DB coupling,
• temporal correctness,
• idempotency gaps,
• external ID confusion,
• reporting grain issues,
• safe expand/backfill/cutover/contract refactoring,
• tests and observability.

The key principle:

The best data modellers are not only good at designing new models. They are good at recognizing when an existing model is lying, leaking, duplicating, drifting, or becoming impossible to evolve.