Design a strategy for historical data management in a transactional database.

Example Answer

I would first define the history query. “What did the database record at 10:00?” differs from “What price was valid for the customer on that date?” The former tracks system history; the latter needs business-effective time. Some applications need both.

I would compare the database's temporal features, transactional triggers and change-data capture against those requirements. For a trigger-based design, the business update and its history record should commit or roll back together. Store the entity identity, operation and version/order information, along with the relevant before or after state. Define deletion handling and how equal timestamps are ordered; a timestamp alone is not a unique sequence.

Full snapshots simplify reconstruction but use more space. Deltas can reduce repeated values, yet require an initial state, an unbroken ordered chain and schema-version rules. JSON is a representation choice, not an automatic storage or performance improvement. I would test reconstruction after updates, deletes, schema changes and interrupted processing.

For asynchronous capture, define lag and recovery from the source position, including deduplication and gaps. A transactional snapshot and change stream must have a consistent handoff if used together.

Index and partition for measured history queries. Apply approved retention, access and erasure policies to historical copies as well as current data. Audit evidence may need controls beyond ordinary application history.

Acceptance checks

Verify committed changes are recoverable at the required point, rolled-back changes do not appear as committed history, duplicates do not create extra versions, and the write overhead and reconstruction time meet measured requirements. This is a design proposal, not a claim of past implementation.