08.5 SCD Type 1 — Current-State Dimensions
1. What SCD Type 1 means
SCD Type 1 keeps the latest trusted state of an entity and overwrites the previous descriptive value. Historical versions are intentionally not retained.
Example:
| customer_id | city | status |
|---|---|---|
| C100 | Bengaluru | Active |
If the source changes C100 to Hyderabad, the target becomes Hyderabad. Bengaluru is no longer represented in the SCD1 dimension.
SCD1 answers: “What is true now?”
Databricks describes SCD Type 1 as keeping only the current state, while SCD Type 2 preserves historical versions. Official CDC guidance
2. Where SCD1 fits in a lakehouse
Source System
|
CDC / incremental / snapshot
|
v
Bronze — raw change evidence
|
ordering + deduplication
|
v
Silver — SCD1 current state
|
v
Gold / BI / data products
Bronze should preserve enough source evidence to replay or investigate changes. Silver establishes the current-state contract. Gold serves business consumers.
3. SCD1 vs SCD2
| Requirement | SCD1 | SCD2 |
|---|---|---|
| Current state | Yes | Yes |
| Full history | No | Yes |
| Point-in-time reporting | No | Yes |
| Storage | Lower | Higher |
| Query complexity | Lower | Higher |
| Typical use | Current master data | Historical dimensions |
Use SCD1 for current customer address, current supplier status, current product description, current employee department, or other attributes where historical values are not required.
Use SCD2 when the business asks what was true at a previous point in time.
4. Business key is the foundation
Every SCD1 design needs a stable entity identity.
| Domain | Example key |
|---|---|
| Customer | customer_id |
| Supplier | supplier_id |
| Material | material_id |
| Cost center | controlling_area + cost_center |
| Material plant | material_id + plant |
For a composite key, the complete combination identifies one entity.
Do not confuse a source technical key, a business key, and a warehouse surrogate key. SCD1 application logic must have a deterministic entity identity.
5. Source patterns
CDC feed
The source emits inserts, updates, and deletes. This is usually the strongest pattern for low-latency synchronization.
Incremental extraction
A source exposes a reliable watermark such as last-modified timestamp or change sequence.
Snapshot
The source periodically provides the complete table. The pipeline derives changes by comparing states.
Delta Change Data Feed
A Delta table with Change Data Feed enabled can provide row-level changes to downstream processing.
6. The simple MERGE pattern
MERGE INTO dim_customer AS target
USING customer_changes AS source
ON target.customer_id = source.customer_id
WHEN MATCHED AND source.operation = 'DELETE'
THEN DELETE
WHEN MATCHED
THEN UPDATE SET *
WHEN NOT MATCHED
THEN INSERT *
This expresses the basic business operation, but production CDC requires more than MERGE.
Potential problems include duplicate events, out-of-order events, multiple events for one key in a batch, deletes, retries, partial updates, and schema evolution.
MERGE is a DML primitive, not a complete CDC architecture.
Databricks recommends declarative AUTO CDC for CDC pipelines because it handles ordering, deduplication, out-of-order events, and related CDC concerns declaratively. Lakeflow best practices
7. AUTO CDC — modern Databricks approach
Current Lakeflow pipelines provide AUTO CDC APIs for processing change feeds into SCD Type 1 or Type 2 targets. They replace the older APPLY CHANGES APIs for new designs.
CDC feed
|
+--> business keys
+--> sequence
+--> delete condition
|
v
AUTO CDC
|
v
SCD Type 1 target
See Databricks AUTO CDC documentation.
8. SCD1 with SQL AUTO CDC
CREATE OR REFRESH STREAMING TABLE customer_current;
CREATE FLOW customer_scd1
AS AUTO CDC INTO customer_current
FROM STREAM customer_changes
KEYS (customer_id)
APPLY AS DELETE WHEN operation = 'DELETE'
SEQUENCE BY sequence_num
COLUMNS * EXCEPT (operation, sequence_num)
STORED AS SCD TYPE 1;
The important concepts are:
- KEYS identifies the entity.
- SEQUENCE BY establishes logical event order.
- APPLY AS DELETE defines delete semantics.
- SCD TYPE 1 means the latest state replaces the previous state.
9. SCD1 with Python AUTO CDC
from pyspark import pipelines as dp
from pyspark.sql.functions import col, expr
@dp.view
def customer_changes():
return spark.readStream.table("bronze.customer_changes")
dp.create_streaming_table("customer_current")
dp.create_auto_cdc_flow(
target="customer_current",
source="customer_changes",
keys=["customer_id"],
sequence_by=col("sequence_num"),
apply_as_deletes=expr("operation = 'DELETE'"),
stored_as_scd_type=1
)
SQL and Python express the same architecture. Choose the interface that fits the team's pipeline standards.
10. Sequence ordering is critical
Consider:
| key | city | sequence |
|---|---|---|
| C100 | Delhi | 10 |
| C100 | Mumbai | 20 |
| C100 | Bengaluru | 30 |
Even if the physical arrival order is 30, 10, 20, Bengaluru must win because sequence 30 is the latest logical state.
Do not blindly use ingestion time if it does not represent source-event order.
Good sequencing candidates include transaction sequence, log position, commit sequence, or another source-defined monotonically increasing order.
Databricks documents sequencing as a core AUTO CDC requirement. CDC sequencing guidance
11. Idempotency
A production pipeline must tolerate retries.
event 100
|
process
|
failure
|
retry
|
event 100 again
The final state must remain identical.
Design for idempotency using stable keys, deterministic sequencing, durable checkpoints, transactional Delta writes, and deterministic transformations.
Avoid random identifiers or transformations whose output changes on every retry.
12. Duplicate events
A source may emit:
| key | city | sequence |
|---|---|---|
| C100 | Delhi | 10 |
| C100 | Delhi | 10 |
| C100 | Mumbai | 20 |
The logical event identity should be deterministic, commonly based on the business key plus source sequence or another source event identifier.
13. Delete semantics
Physical delete
The entity disappears from the current-state table.
Logical delete
The row remains with a flag such as is_deleted = true.
Choose based on the data contract, not merely implementation convenience. Logical deletion can be valuable when downstream consumers need deletion awareness or when source deletion events must remain visible.
14. Partial updates
Some CDC systems emit only changed columns.
Example:
customer_id = C100
city = Bengaluru
If customer_name is absent, a naïve full-row replacement could incorrectly turn it into NULL.
Possible strategies:
- Require complete after-images.
- Reconstruct complete rows before SCD application.
- Use supported null-preserving update semantics.
- Normalize the source change feed before applying SCD logic.
Partial-update behavior must be explicit in the data contract.
15. Bronze → Silver → Gold architecture
Source
|
CDC / incremental
v
Bronze
| raw events + replay evidence
v
Silver SCD1
| current state
v
Gold / semantic layer
Bronze is evidence. Silver is the current-state contract. Gold is business consumption.
16. SCD1 in SAP landscapes
A typical SAP pattern can be:
SAP S/4HANA
|
v
CDC / replication
|
v
Databricks Bronze
|
v
Change normalization
|
v
SCD1 customer / supplier / material dimension
|
+--> BI
+--> Finance analytics
+--> Procurement analytics
+--> Data products
Good SCD1 candidates include current customer attributes, current supplier attributes, current material classification, current organizational ownership, and other master-data state where history is not required.
17. Surrogate keys
A warehouse may expose:
| customer_sk | customer_id | city |
|---|---|---|
| 10001 | C100 | Delhi |
After a Type 1 change:
| customer_sk | customer_id | city |
|---|---|---|
| 10001 | C100 | Bengaluru |
The descriptive value changes while the logical entity remains the same. This differs from SCD2, where a new historical version is normally created.
18. SCD1 and fact reporting
SCD1 works when a report needs the current descriptive value.
Example: “Show current customer city for sales.”
It does not answer: “Show customer city as it was when each invoice was posted.” That requires historical semantics such as SCD2.
19. Data quality controls
Recommended controls:
- business-key uniqueness;
- null-key detection;
- referential integrity where required;
- unexpected delete monitoring;
- unexpected update-volume monitoring;
- freshness and source-to-target latency;
- source/target reconciliation;
- schema-change detection.
A useful invariant is one current row per business key.
COUNT(*) = COUNT(DISTINCT business_key)
20. Operational metrics
| Metric | Purpose |
|---|---|
| Inserted rows | New entities |
| Updated rows | Attribute changes |
| Deleted rows | Source removals |
| Duplicate events | Source/data-quality health |
| Late events | Ordering health |
| Invalid keys | Data quality |
| Processing latency | SLA |
| Source lag | Freshness |
| Pipeline failures | Reliability |
Databricks AUTO CDC exposes CDC-related metrics including upserted and deleted row counts. Monitor these alongside pipeline health.
21. Performance engineering
SCD1 performance depends on target size, change rate, key distribution, and workload concurrency.
Recommended principles:
- process only changed data when possible;
- avoid unnecessary columns;
- keep Delta file sizes healthy;
- control small-file creation;
- choose clustering based on actual access patterns;
- avoid blind high-cardinality partitioning;
- benchmark merge/update workloads at realistic volumes;
- separate ingestion SLAs from BI query SLAs.
For large dimensions, table layout and change percentage can matter more than SQL syntax alone.
22. SCD1 vs full replacement
Full replacement may be reasonable for a small reference table.
Large table:
full replacement -> read everything -> rewrite everything
incremental SCD1 -> changed rows -> apply changes -> current state
Choose based on table size, percentage changed, latency, compute cost, source capability, and downstream dependencies.
23. When SCD1 is the wrong choice
Do not use SCD1 when the business needs:
- point-in-time reporting;
- regulatory history;
- historical organizational ownership;
- historical customer segmentation;
- historical product attributes;
- effective-dated analysis;
- audit reconstruction.
In these cases, SCD2 or another historical/event model is usually more appropriate.
24. Snapshot backfill with AUTO CDC FROM SNAPSHOT
Modern Databricks Lakeflow also provides AUTO CDC FROM SNAPSHOT. It compares ordered snapshots to derive changes when a native CDC feed is unavailable.
For SCD1, a powerful pattern can combine an authoritative snapshot with a lower-latency CDC feed:
Authoritative snapshot
|
v
AUTO CDC FROM SNAPSHOT
|
+----------+
|
CDC feed ----------+--> SCD1 target
Databricks documents constraints for combining snapshot and AUTO CDC flows, including common keys and sequencing types. AUTO CDC documentation
25. Lab 1 — Build SCD1 with MERGE
Create:
bronze.customer_changes
silver.customer_current
Sample changes:
C100 | Acme | Delhi | 10
C100 | Acme | Mumbai | 20
C200 | Globex | Pune | 10
Tasks:
- Create the target Delta table.
- Apply initial records.
- Apply updates.
- Apply inserts.
- Apply deletes.
- Verify one current row per key.
Expected current state:
C100 -> Mumbai
C200 -> Pune
26. Lab 2 — Out-of-order events
Create:
C100 | Delhi | 10
C100 | Bengaluru | 30
C100 | Mumbai | 20
The final state must be Bengaluru.
Explain why arrival order is insufficient and why the sequence column must represent logical source order.
27. Lab 3 — AUTO CDC
Build a Lakeflow pipeline with:
- streaming source;
- business key;
- sequence column;
- delete condition;
- SCD Type 1 target.
Then introduce duplicate, late, and delete events. Verify that the current-state target converges to the expected result.
Use the current AUTO CDC API for new production designs rather than starting with legacy APPLY CHANGES terminology.
28. Lab 4 — Enterprise design
Design an SCD1 customer dimension with:
- 500 million source rows;
- 2 million daily changes;
- 10,000 deletes per day;
- late events;
- source corrections;
- 15-minute freshness SLA;
- high BI concurrency.
Document:
- source ingestion;
- Bronze design;
- sequencing;
- SCD1 application;
- delete semantics;
- idempotency;
- data quality;
- table layout;
- monitoring;
- recovery and replay.
29. Architect exercise
Requirement A: “Show the customer's current credit limit.”
Requirement B: “Show the credit limit that was valid when the invoice was posted.”
Answer:
- A → SCD1.
- B → SCD2 or another effective-dated historical model.
This distinction is one of the most important dimensional-modeling decisions in a lakehouse.
30. Production checklist
- Business key explicitly defined.
- Key uniqueness tested.
- Source sequencing deterministic.
- Duplicate events handled.
- Out-of-order events handled.
- Delete semantics documented.
- Partial-update semantics documented.
- Retry behavior idempotent.
- Schema evolution controlled.
- Bronze retains required evidence.
- Current-state target separated from historical data.
- Data quality metrics implemented.
- Freshness SLA measurable.
- Update/delete spikes monitored.
- Replay and recovery tested.
- Performance benchmarked.
- Unity Catalog permissions defined.
- Consumers understand that historical values are not retained.
31. Architect mental model
WHAT IS TRUE NOW?
|
v
BUSINESS KEY
|
v
ORDERED CHANGES
|
+---------+---------+
| |
UPSERT DELETE
| |
+---------+---------+
|
v
ONE CURRENT ROW
PER ENTITY
SCD Type 1 is not simply an UPDATE statement. It is a current-state data contract.
The architecture must define identity, ordering, deletion semantics, replay behavior, quality, governance, and operational guarantees.