Data Modeling hard
What is 'Bi-Temporal' data modeling (e.g., in SQL:2011 standard)?
- A Tracking two independent timelines for every record: 'Valid Time' (real-world truth) and 'Transaction Time' (when the database recorded the knowledge).
- B Utilizing high-precision atomic clocks to synchronize timestamps across two or more geographically distributed data centers to prevent clock drift errors.
- C Maintaining two separate copies of the data in different timezones to ensure that regional reporting queries can be served with zero latency offsets.
- D Leveraging 128-bit 'wide' timestamps that combine both a Unix epoch and a nanosecond counter to provide absolute precision for high-frequency trading.
Answer: A. Tracking two independent timelines for every record: 'Valid Time' (real-world truth) and 'Transaction Time' (when the database recorded the knowledge).
Bi-temporal modeling is critical for audit-heavy domains like insurance or finance. It allows you to answer questions like: 'As of last Friday (Transaction Time), what did we believe the customer's coverage was for an accident that happened two weeks ago (Valid Time)?'. While it provides unmatched historical accuracy and retroactive correction capability, it significantly increases schema complexity and the physical storage footprint.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- When modeling a Distributed Transaction using the Saga Pattern (Choreography or Orchestration), what type of consistency do you achieve?
- Which practice prevents SQL Injection attacks?
- Adding an Index to a database column generally improves Read performance. What is the negative trade-off?
- Log Systems Merge (LSM) Trees (used in RocksDB, Cassandra) optimize for fast writes. What is their primary downside regarding Disk I/O?
- What is the primary objective of normalizing a relational database schema (e.g., to 3NF)?
- You are modeling a 'Likes' counter for a global social network. You choose an AP model. What anomaly must your data model handle?