Recycled source ids
Choose a key strategy when an operational system reuses ids after a device is retired.
Concept
surrogate-keys
The primary modeling idea this problem reinforces.
Requirements
1
Business needs the model must satisfy.
An IoT platform reissues device_id values after a device is decommissioned, so the same device_id can refer to two different physical devices over time. The warehouse must keep each physical device’s history separate.
A recycled natural id silently merges two real-world entities if used as the key. A surrogate key plus a validity window keeps the histories apart.
- Pick the key strategy that keeps two reuses of one id distinct.
- The answer uses a surrogate key, not the recycled device_id.
- The explanation keeps each physical device’s history separate.
- The same device_id can mean two different devices over time.
- A surrogate plus validity window keeps them distinct.
- Using the recycled device_id as the primary key and merging histories.
- Assuming operational ids are globally unique over time.
- Overwriting the first device’s rows when the id is reissued.
Surrogate key for a recycled natural id
Assign a surrogate key per physical device and keep device_id as an attribute with a validity window, so a reissued id maps to a new surrogate and histories stay separate. The Kimball surrogate-key pattern handles the reissue.
Optimizes for
- • Separate histories per physical device
- • Stable identity under id reuse
Trade-offs
- • Requires detecting reissue events in ETL
Try the question first.
The discussion has other people's approaches and solutions. Give it a real attempt before you read them.