Data Migration, Backfill, and Schema Evolution Model

1. Core Idea

Enterprise data model tidak pernah diam.

Dalam CPQ / Quote / Order / Billing / Catalog / Telco BSS/OSS, schema dan data berubah karena:

• produk baru,
• pricing model baru,
• order lifecycle baru,
• billing integration baru,
• TM Forum API alignment,
• customer/account hierarchy berubah,
• approval policy berubah,
• migration dari legacy system,
• refactoring monolith ke microservices,
• performance optimization,
• data correctness repair,
• regulatory/privacy requirement,
• reporting/KPI need.

Mental model:

Schema evolution is not only DDL. It is a controlled production change across database, application code, APIs, events, projections, reports, and existing data.

2. Why Migration Modelling Matters

Data migration yang buruk bisa menyebabkan:

• downtime,
• locked production table,
• broken API compatibility,
• old app version gagal membaca new schema,
• new app gagal membaca old data,
• backfill duplicate,
• historical data corrupted,
• quote/order/billing mismatch,
• invoice/reporting berubah,
• projections stale,
• event consumers break,
• rollback impossible,
• migration partially completed tanpa visibility,
• data repair dilakukan manual tanpa audit.

Migration harus diperlakukan sebagai production workflow, bukan hanya SQL script.

3. Types of Data Change

Common change types:

Each type needs different risk management.

4. Expand and Contract Pattern

Zero-downtime schema evolution often uses expand/contract.

Phase 1 — Expand

Add new schema without breaking old code.

Add nullable new column/table.
Add new code that can write both old and new.
Keep old readers working.

Phase 2 — Backfill

Populate new data for old rows.

Backfill new column/table in batches.
Validate old vs new consistency.

Phase 3 — Switch reads

New code reads from new schema.

Shadow-read compare old/new.
Then switch primary read path.

Phase 4 — Contract

Remove old schema only after all clients/code are migrated.

Stop writing old.
Wait compatibility window.
Drop old column/table.

Do not rename/drop columns in one deploy if multiple app versions may run.

5. Compatibility Window

Compatibility window is period where old and new code/data coexist.

During compatibility window:

• old app version can still run,
• new app version can still read old data,
• event consumers tolerate old/new schema,
• API clients tolerate added fields,
• backfill may be incomplete,
• read logic handles null/missing new fields,
• write logic may dual-write.

Model explicitly:

schema_change
- id
- change_code
- expand_deployed_at
- backfill_started_at
- backfill_completed_at
- read_switched_at
- contract_deployed_at
- compatibility_status

6. Migration State Model

Migration should have status.

data_migration
- id
- migration_code
- migration_type
- target_entity
- status
- owner_group
- started_at
- completed_at
- failed_at
- rollback_strategy
- correlation_id

Statuses:

PLANNED
EXPAND_DEPLOYED
BACKFILL_RUNNING
BACKFILL_PAUSED
BACKFILL_COMPLETED
VALIDATION_RUNNING
VALIDATED
READ_SWITCHED
CONTRACT_READY
CONTRACT_DEPLOYED
FAILED
ROLLED_BACK
CANCELLED

This makes migration visible and operable.

7. Migration Batch Model

Backfill should run in batches.

data_migration_batch
- id
- migration_id
- batch_number
- range_start
- range_end
- status
- row_count
- success_count
- failure_count
- started_at
- completed_at
- error_message

Why batches:

• avoid long transactions,
• avoid large locks,
• support retry,
• limit blast radius,
• monitor progress,
• pause/resume,
• handle failed records separately.

Do not update millions of production rows in one unbounded transaction unless fully tested and safe.

8. Idempotent Backfill

Backfill must be idempotent.

Running it twice should not corrupt data.

Bad:

update charge set amount = amount + 10 where ...

Better:

update charge
set normalized_amount = source_amount
where normalized_amount is null
  and migration_code = '...';

Or deterministic derivation:

new_value = function(old_row, reference_data, version)

Backfill should be safe to retry after failure.

9. Backfill Source of Truth

Every backfill must define source.

Examples:

If source is ambiguous, migration should produce exception records, not guess silently.

10. Migration Exception Model

Some rows cannot be migrated automatically.

Fields:

data_migration_exception
- id
- migration_id
- entity_type
- entity_id
- exception_code
- exception_message
- severity
- owner_group
- status
- resolution_action
- resolved_at

Exception examples:

• missing source quote,
• multiple possible billing accounts,
• invalid currency,
• duplicate mapping,
• closed invoice conflict,
• data violates new invariant,
• external ID missing.

Exceptions should feed repair workflow.

11. Shadow Read and Comparison

Before switching reads, compare old/new.

Example:

Old path computes billing account from customer/account.
New path reads order.billing_account_id.

Shadow comparison:

read old value
read new value
compare
record mismatch
do not affect user response yet

Model:

shadow_read_comparison
- migration_id
- entity_type
- entity_id
- old_value_hash
- new_value_hash
- comparison_result
- mismatch_code
- compared_at

This reduces risk of cutover.

12. Dual-Write

Dual-write means writing both old and new schema during transition.

Example:

Write old quote_item.config_json
Write new quote_item_characteristic rows

Risks:

• one write succeeds, another fails,
• old/new drift,
• complicated rollback,
• hidden performance cost.

Safer if both writes are in same local DB transaction.

If cross-service dual-write, prefer outbox/saga/reconciliation.

Track dual-write mismatches.

13. Cutover

Cutover switches primary read/write path.

Before cutover:

• backfill complete,
• exceptions resolved or accepted,
• shadow-read mismatch below threshold,
• dashboards healthy,
• rollback plan tested,
• downstream consumers ready,
• API/event contract compatible,
• support notified if needed.

Cutover should be feature-flagged if possible.

Model:

cutover_record
- migration_id
- cutover_type
- from_path
- to_path
- status
- started_at
- completed_at
- approved_by

14. Rollback Strategy

Not every migration can be rolled back.

Types:

For each migration, define:

• rollback trigger,
• rollback scope,
• data loss risk,
• compatibility requirement,
• backup/snapshot,
• validation after rollback.

If rollback is impossible, be explicit and use stronger pre-cutover validation.

15. Schema Migration and Application Deployment

Avoid DDL that blocks production traffic.

Risky operations:

• adding non-null column with default on huge table depending DB/version,
• rewriting table,
• creating index without concurrent mode,
• dropping column used by old code,
• long foreign key validation,
• big update in one transaction,
• changing enum type used by app,
• locking hot table.

Production migration should be tested with production-like data volume.

16. PostgreSQL Migration Considerations

General PostgreSQL considerations:

• use create index concurrently for large hot tables,
• add nullable column first,
• backfill in batches,
• add not null only after validation,
• use not valid foreign key/check then validate later where appropriate,
• monitor locks,
• set lock timeout,
• avoid long transactions,
• avoid table rewrites unexpectedly,
• test query plans after index/schema change.

Example safer pattern:

alter table product_order add column billing_account_id uuid;

-- Backfill in application/batch job.

alter table product_order
  add constraint chk_order_billing_account_present
  check (billing_account_id is not null) not valid;

alter table product_order
  validate constraint chk_order_billing_account_present;

Adapt to internal PostgreSQL version and standards.

17. Event Schema Migration

Event evolution also needs expand/contract.

Example:

Old event:

{
  "billingAccountId": "..."
}

New event:

{
  "billingAccount": {
    "id": "...",
    "number": "..."
  }
}

Migration strategy:

• add new field while keeping old,
• consumers support both,
• producers emit both during compatibility,
• consumers migrate,
• remove old after compatibility window.

Do not break consumers with sudden field removal/meaning change.

18. API Contract Migration

API migration strategy:

• add optional field,
• document deprecation,
• support old and new fields temporarily,
• add new endpoint/version for breaking change,
• monitor client usage,
• remove only after agreed timeline.

Example:

billingAccountId deprecated
payer.billingAccountId introduced

Server may accept both during transition, but define precedence and conflict behavior.

19. Projection Rebuild

Read model or analytics projection may need rebuild after schema/semantic change.

Rebuild requirements:

• source of truth available,
• deterministic transformation,
• checkpoint management,
• idempotent upsert,
• backfill status,
• validation result,
• cutover to rebuilt projection,
• old projection retention for comparison.

Projection rebuild state:

projection_rebuild
- id
- projection_name
- source_version
- target_version
- status
- rows_processed
- mismatch_count
- started_at
- completed_at

20. Legacy Migration

Legacy migration introduces mapping problems.

Model:

legacy_entity_mapping
- legacy_system
- legacy_entity_type
- legacy_entity_id
- new_entity_type
- new_entity_id
- mapping_status
- confidence
- migrated_at

Need handle:

• duplicate legacy IDs,
• missing mandatory fields,
• invalid state,
• incompatible status,
• historical data gap,
• external references,
• customer/account merge,
• old product codes,
• old billing identifiers.

Do not lose legacy reference. Support and reconciliation often need it.

21. Data Repair vs Migration

Migration changes many records due to planned model change.

Data repair fixes incorrect records.

Both need:

• reason,
• audit,
• before/after,
• approval if sensitive,
• validation,
• rollback/forward-fix plan.

But repair should link to incident/root cause.

data_repair_case.incident_reference
data_repair_case.root_cause_code

Migration should link to change/release.

data_migration.release_reference

22. Migration Observability

Monitor:

• rows processed,
• rows remaining,
• batch failure rate,
• migration lag,
• lock wait,
• DB CPU/IO,
• replication lag,
• app error rate,
• query latency,
• mismatch count,
• exception count,
• rollback trigger metrics.

Example queries:

-- Migration batch progress
select status, count(*), sum(row_count)
from data_migration_batch
where migration_id = :migration_id
group by status;

-- Open migration exceptions
select exception_code, severity, count(*)
from data_migration_exception
where migration_id = :migration_id
  and status <> 'RESOLVED'
group by exception_code, severity;

23. PostgreSQL Physical Design

Migration metadata:

create table data_migration (
  id uuid primary key,
  migration_code text not null unique,
  migration_type text not null,
  target_entity text not null,
  status text not null,
  owner_group text,
  release_reference text,
  rollback_strategy text,
  correlation_id text,
  started_at timestamptz,
  completed_at timestamptz,
  failed_at timestamptz,
  created_at timestamptz not null,
  updated_at timestamptz not null
);

Batch:

create table data_migration_batch (
  id uuid primary key,
  migration_id uuid not null references data_migration(id),
  batch_number integer not null,
  range_start text,
  range_end text,
  status text not null,
  row_count bigint,
  success_count bigint,
  failure_count bigint,
  started_at timestamptz,
  completed_at timestamptz,
  error_message text
);

Exception:

create table data_migration_exception (
  id uuid primary key,
  migration_id uuid not null references data_migration(id),
  entity_type text not null,
  entity_id uuid,
  exception_code text not null,
  exception_message text,
  severity text not null,
  owner_group text,
  status text not null,
  resolution_action text,
  resolved_at timestamptz,
  created_at timestamptz not null
);

Indexes:

create index idx_migration_status
on data_migration (status, updated_at);

create index idx_migration_batch_status
on data_migration_batch (migration_id, status, batch_number);

create index idx_migration_exception_open
on data_migration_exception (migration_id, severity, exception_code)
where status <> 'RESOLVED';

24. Java/JAX-RS Backend Implications

Migration APIs may be internal/admin only:

GET /internal/data-migrations
POST /internal/data-migrations/{id}/start-backfill
POST /internal/data-migrations/{id}/pause
POST /internal/data-migrations/{id}/resume
GET /internal/data-migrations/{id}/exceptions
POST /internal/data-migrations/{id}/validate
POST /internal/data-migrations/{id}/cutover

Backfill worker should:

• process bounded batches,
• use idempotent writes,
• record progress,
• capture exceptions,
• respect rate limits,
• support pause/resume,
• avoid large memory load,
• emit metrics.

25. MyBatis/JPA/JDBC Implications

MyBatis

Good for explicit batch queries and migration-specific SQL.

JPA

Risky for huge migrations due to persistence context memory, cascading, lazy loading, and N+1.

JDBC

Often best for controlled batch migration.

General rule:

Use the simplest deterministic data access style for migration; prioritize observability, idempotency, and bounded transactions.

26. Release Checklist

Before production migration:

• schema change reviewed,
• lock risk assessed,
• backfill design reviewed,
• rollback/forward-fix plan documented,
• compatibility with old/new app verified,
• API/event compatibility checked,
• data volume estimated,
• index/query plan checked,
• monitoring/alerts prepared,
• runbook written,
• support impact known,
• dry run performed,
• data quality validation prepared.

After migration:

• validate row counts,
• validate business invariants,
• monitor error rate,
• monitor query performance,
• resolve exceptions,
• update documentation,
• schedule contract cleanup.

27. Failure Modes

28. PR Review Checklist

When reviewing migration/schema evolution, ask:

• Is this additive or breaking?
• Is expand/contract needed?
• Can old and new app versions coexist?
• Is backfill required?
• Is backfill idempotent?
• What is source of truth for backfill?
• What happens to ambiguous records?
• Is migration state tracked?
• Is batch size bounded?
• Is rollback/forward-fix defined?
• Is shadow read/comparison needed?
• Are API/event contracts affected?
• Are projections/reports affected?
• Are locks/performance risks understood?
• Are validation/reconciliation checks prepared?
• Is sensitive data classification/retention affected?

29. Internal Verification Checklist

Verify these in the internal CSG/team context:

• Database migration tooling and standards.
• PostgreSQL version and online DDL practices.
• Whether expand/contract is standard.
• Whether app deploys are rolling/multi-version.
• Whether backfill jobs have metadata/progress tables.
• Whether migration exceptions are persisted.
• Whether migration runbooks are required.
• Whether data repair/migration approval is required.
• Whether event/API compatibility policy exists.
• Whether projection rebuild process exists.
• Whether legacy ID mapping tables exist.
• Whether production-like dry runs are required.
• Whether incidents mention blocking migration, failed backfill, partial migration, or schema compatibility failure.

30. Summary

Schema evolution is production data engineering.

A strong model must define:

• migration type,
• expand/contract phases,
• compatibility window,
• migration state,
• batch progress,
• idempotent backfill,
• source of truth,
• exception handling,
• shadow read,
• dual-write risk,
• cutover,
• rollback/forward fix,
• API/event/projection compatibility,
• validation,
• observability,
• runbook.

The key principle:

Production schema change is not a single SQL file. It is a controlled lifecycle that must keep old code, new code, old data, new data, APIs, events, projections, and reports correct during the entire transition.