# Intro It is a core responsibility of a data warehouse to be a librarian - or better said, an "archivist" of data. As opposed to a "librarian" (who is more into organizing, preserving and providing access to information), an "archivist" is especially focused on: - describing documents in historical context - preserving historical records - helping researchers interpret past artifacts A data engineer building a data warehouse wears both the "librarian" and the "archivist" hat (among many others). So, what's so fancy about being an archivist of data anyway? Why is this worthy of a headline? Let's dig into the reasons, starting with data sources - as these are usually the culprit. # Data source systems Before complaining about source systems too much, let's not forget that: - **sources** are about _accuracy_, _performance_ and _compliance_ - **data warehouses** are about _changes (SCD)_ , _business validity_ and _integrated state_ This isn't a mandate for source systems to work against common sense and not implement anything historization-related just because "this is a DWH responsibility". We have encountered in our engagements many data architectures in which source systems _need to store historical data_ in order to: - manage time-dependent entitlements, contracts, and pricing conditions whose validity changes over time (i.e. tariff changes, discount periods, product lifecycle states) - comply with legal, regulatory, or audit requirements that explicitly mandate retention of original records and their subsequent changes over time - provide business users with complete audit trails of who changed what, when, and why at the operational level (i.e. user activity logs, approval workflows, versioned documents or records) On the opposite, this isn't a mandate for source systems needing to implement full-fledged historization capabilities. It is enough if sources: - retain the core historical data that they need for operational purposes (they need to do that anyway!) - build a time-limited historization, driven by a retention policy (long-term historization to be provided by the DWH) - provide transparency into the business processes (a key question driving historization implementations, in our experience - how long in the past can data be retroactively changed?) # Data consumption interfaces Let's walk through possible source system data interfaces which are of interest to an "archivist". We analyze context, pros & cons, responsibilities that the DWH needs to handle in order to be able to expose the desired clean historized shape and the inherent limitations of each. | Interface Type | Description | Value | DWH Responsibilities | Limitations | |----------------|-------------|--------|----------------------------------|-------------| | **Current data only** | • Only latest state stored
• No change tracking | 🔴 **Limited** | • Poll more often than the data update frequency (inefficient!) in order not to miss updates
• Extract full data on every run
• Deduplicate overlapping extracted data | • Historical rebuild impossible
• Change timestamps can only be inferred from technical timestamps (no business markers)
• No support for late-arriving data or retroactive corrections | | **Current data only + change cursor** | • _same as above_
• cursor field (technical timestamp) is set by source whenever a record is changed | 🔴 **Low** | • Extract changes only (incremental)
• _Still need to guess the data update frequency and poll more often than that (inefficient!), otherwise changes like A -> B -> A would be completely missed
_ | • Deletes cannot be captured
• Cursor reliability risk (updates can be missed if the source omits to update the cursor field)
• No support for late-arriving data or retroactive corrections | | **Lifecycle history fields** | • Status transitions stored
• Process milestones captured | 🟠 **Low-Medium** | • Bridge state gaps
• Align with fact timelines | • No attribute history
• Partial business coverage only | | **Field- or record-level audit log (change log)** | • Granular change tracking
• User + timestamp captured | 🟠 **Medium** | • Rebuild full row states
• Correct sequencing/grouping of changes | • High storage requirements on source side (limited retention period)
• Complex reconstruction logic (sub-optimal for analytics)
| | **Validity-based temporal data** | • `valid_from` / `valid_to` fields maintained by source, with all historical versions of the data
• Data can be retroactively updated by users | 🟠 **Medium-high** | • _Still need to guess the data update frequency and poll more often than that (inefficient!)_
• Extract redundant historical data in order to catch retroactive changes (usually within a look-back window)
• Deduplicate overlapping extracted data | • Business historization can be built on source-provided fields, yet technical historization must still rely on technical timestamps driven by extraction (provides _some_ level of audit, but not perfect)
• Complex deduplication mechanism for change- and deletion-detection | | **CDC (Change Data Capture)** | • Record-level insert / update / delete captured
• Log-based or trigger-based | 🟢 **High** | • Order and deduplicate events, as needed
• Transform data to the desired state and rehistorize it (i.e. business-wise we might not care that a record was changed several times in a row if the payload didn't change, in such case we want deduplicate redundant technical changes) | • Short retention in source
• Highly coupled operations (if analytics consumer doesn't pull data, source could get saturated and starts failing)
• Technical, not business semantics | | **Event sourcing** | • The primary source of truth are "changes", not "records"
• All changes get captured as immutable events (entire history can be rebuilt based on them) | 🟢 **High** | • Manage entity states
• Derive facts & dimensions from changes (events) | • Complex modeling (no persisted current state, needs to be built on-the-fly or managed persistency) | | **Bitemporal data** | • Valid time + transaction time
• Comprehensive tracking or historical truth at-source | 🟢 **High** | • Historization comes out-of-the-box for primary entities; re-historize derived entities only
• Mostly mapping & conformance | • Very rare for source systems to provide this, in our experience
• High source system complexity | # Next steps In subsequent posts we deep dive in implementation aspects, blueprints and further complexities of each interface type.