Click a heading to bookmark it
Slowly Changing Dimensions (SCDs) are a core technique in data warehousing that enables the preservation of historical integrity in analytical systems. Unlike operational databases that overwrite past information, data warehouses use SCDs to meticulously track every change to descriptive attributes within dimension tables (e.g., Customer, Product). This allows analytical queries to reflect past states accurately, enabling trend analysis, performance measurement, and strategic decision-making based on complete history.
SCDs transform data warehouses into a living history book, providing robust business intelligence. The foundation is historical integrity, managed through specific SCD strategies (Type 1 overwrite, Type 2 versioning, Type 3 partial), which ultimately deliver analytical value for informed decision-making.
Different types of SCDs offer varying levels of historical preservation and complexity. The primary types are:
Type 0 (Fixed Dimension): The dimension attribute never changes; original values are retained.
Type 1 (Overwrite): Old values are overwritten by new ones; no history is preserved.
Type 2 (Add New Row): A new row is added for each change, preserving full history with start and end dates or flags.
Type 3 (Add New Column): A new column is added to store the previous value of an attribute, preserving partial history (current and one previous state).
Type 4 (Add New Mini-Dimension): History is tracked in a separate mini-dimension table linked to the main dimension, suitable for rapidly changing attributes.
Type 5 (Hybrid of Type 1 and 4): Combines Type 1 and Type 4; a Type 1 attribute in the main dimension is combined with a Type 4 mini-dimension.
Type 6 (Hybrid of Type 1, 2, and 3): Combines all three methods within a single dimension.
Once set, ignore any changes; these values are considered immutable. For example, in a Customer dimension, attributes like FirstDesignation, JoinedDate, and DateFirstPurchase would use Type 0 — they are never updated.
No historical tracking performed for these attributes.
Suitable for whole tables in "insert-only" scenarios (e.g., incremental load of new events where past data never changes).
Simply overwrite data when changes occur. Used for filling in missing/null values or correcting errors where historical context is not critical (e.g., updating AnnualIncome).
Existing records are updated directly; no historical data preserved.
Often called Upsert scenarios: correctly track inserts of new primary keys (PKs), or updates if the row existed but some columns changed.
Example: If a customer's MaritalStatus changes from 'M' to 'S', the old value is lost.
The most popular method — history is preserved by adding new rows. Uses additional columns: StartDate, EndDate, and IsCurrent flag.
When an attribute of an existing record changes:
The current record's EndDate is updated to the effective date of the change, and IsCurrent is set to No.
A new record is inserted with the updated values, IsCurrent = Yes, StartDate set to the effective date, and EndDate set to a high date (e.g., '9999-12-31') indicating the active record.
This simplifies filtering the active record on a specific date.
Example: Customer changes designation from "Management" to "Snr. Management". Two rows now exist: the old one with EndDate = 2021-06-01, IsCurrent = No; the new one with StartDate = 2021-06-01, EndDate = 9999-12-31, IsCurrent = Yes.
Key columns for SCD 1 & 2:
SCD_Key: Unique surrogate key generated during SCD processing (sequential or GUID).
SCD_IdHash: Hash (MD5, SHA2) of the business primary key, used to match incoming data with existing records.
SCD_ColumnsHash: Hash of all non-primary key (or tracked columns) attributes, used to detect changes.
SCD_DataLoadId: Identifier (timestamp or batch ID) reflecting the data load process start time (optional).
SCD_LastModifiedDate: Timestamp of last modification.
For SCD2 only:
SCD_StartTime / SCD_EndTime
SCD_IsCurrent: Binary flag (1 for active version).
SCD_RowVersion: Incremental version number.
SCD_FirstKey: Optional; first key record to compare versions of the same logical record.
History kept in additional columns. Simple but limited to the last version only.
Example: Adding a PreviousDesgination column to store the prior value when a customer's designation changes.
Useful for tracking specific attribute changes where only the immediate previous and current values are needed (e.g., prior address, name change after marriage).
Provides simple access to the last known historical value without complex joins.
Limitation: Retains only a fixed, limited history (typically one or two previous states); unsuitable for full audit trails.
Designed for attributes that change very frequently, which would create an explosion of rows in Type 2. Attributes that change rapidly (e.g., customer risk type updated monthly) are moved to a separate mini-dimension table.
Problem: Incorporating frequently changing attributes directly into the main dimension (e.g., customer dimension) leads to massive row growth (1M customers × 12 months = 12M records per year).
Solution: Create a separate "outrigger" dimension for the volatile attributes. The main dimension holds a foreign key to this mini-dimension. This preserves full historical tracking for those attributes without bloating the core dimension.
Benefit: Main dimension remains stable and efficient; specialized mini-dimension handles high-volume changes.
A hybrid combining Type 2 and Type 3 — uses both rows and columns for enhanced analysis.
Provides a complete historical view (like Type 2) while allowing immediate access to current and previous attribute values within a single record (like Type 3).
Simplifies queries: analysts can see both current and historical states without complex joins.
Example table: contains columns like Occupation, CurrentOccupation, StartDate, EndDate, IsCurrent. For a given customer, multiple rows track history, and each row also stores the current occupation value for easy comparison.
Full loads: Need to account for hard deletes in source; use FULL OUTER JOIN or MERGE to identify changes.
Incremental loads: Apply the desired SCD logic:
Type 1: New data → If match, replace old records (change detection optional).
Type 2: New data → If match and change detected, mark old version obsolete, add new version.
Type 3: New data → If match and change detected, modify changed records (update previous value column).
MERGE example for SCD Type 2 (simplified): Use a MERGE statement to update existing current records (set end date and iscurrent to 'No') and insert new rows for changes.
Column naming conventions: Use a prefix like SCD_ to distinguish "business" columns from "metadata" columns generated during loading.
Time zones: Generate StartTime and EndTime in UTC to avoid time zone issues when integrating data from multiple locations.
Business timestamps: Instead of using current_date() for start time, translate from a business timestamp column (e.g., IssuedDate, BillingDate, CreatedAt) to keep records in the business domain.
End date of superseded record: Use the start date of the new record, not a separate generated timestamp.
Type 1 and Type 2 are the most commonly used. SCDs are fundamental for preserving historical data in data warehouses, ensuring accurate time-based analysis. Choosing the appropriate type (e.g., Type 2 for full history, Type 6 for combined row/column tracking) is crucial for meeting specific analytical requirements. Proper implementation ensures data integrity, supports complex business intelligence, and facilitates informed decision-making over time.
In a star schema, every foreign key in a fact table must join to its associated dimension. However, new fact data may arrive before the corresponding dimension records are available (late-arriving dimensions), causing join failures. This can happen due to:
Streaming data: Facts may arrive in close proximity to dimension updates, but not exactly synchronized.
Organizational migrations: Entity migration triggers SCD changes; facts streamed afterward use updated dimensions that haven't arrived yet.
Scheduling inefficiencies: If ETL jobs are not properly ordered, dimensions may be processed after facts.
Three common solutions exist:
Put fact records into "suspense" (process now and hydrate later).
Use a special <unknown> dimension record.
Detect early-arriving facts and retry (reconciliation pattern).
How it works: Incoming fact data is stored in a dedicated "suspense" or "retry" table with default values in dimension columns. A separate hydration process periodically tries to update missing dimension keys.
Limitations:
Does not work if dimension keys are foreign keys in fact tables (data integrity constraint).
If facts are written to multiple destinations, hydration must update all of them for consistency.
Fundamentally unpredictable — no guarantee when (or if) missing dimension data will arrive.
Risk of facts cycling endlessly through retries, never becoming visible to users.
<unknown> DimensionWidely used for optional relationships. For example, in an aviation bus matrix, a dim_delay_reason dimension might contain a "N/A" record for flights that were not delayed. Similarly, for late-arriving dimensions, create a specially-created dimension record with description "Not known yet".
For optional relationships, often there's no need for a lookup; special records are allocated meaningful (negative) surrogate keys.
Limitations:
When the dimension data eventually arrives, you must go back and update fact records pointing to "Not known yet" — expensive and error-prone.
For SCD2, the natural key must be stored alongside the surrogate key in the fact record to enable later updates.
For SCD1, if using a surrogate key, store the hash of the dimension PK in the fact table as a FK to facilitate later correction.
Detect early-arriving facts (facts for which the corresponding dimension has not arrived yet), put them on hold, and retry after a period until they are processed or exhaust retries. This ensures data quality and consistency.
Reconciliation pattern using streaming data (Kafka, Spark, Delta Lake):
Read from source: Read new events (e.g., from Kafka).
Transform and join: Transform facts and left-join to dimension tables.
Flag retryable: If dimension missing, flag the fact record as is_retryable = true.
Write errors: Write retryable records to a streaming_pipeline_errors delta table (append-only).
Merge valid: MERGE valid (non-retryable) records into the target delta table.
Reconciliation: A scheduled batch job consolidates multiple failed retries for the same event into a single row in a reconciliation table, with updated retry count and status.
Retry loop: The streaming pipeline reads from the reconciliation table (UNION with new Kafka data) and retries the join. If still missing, write back to errors; if resolved, proceed to merge.
Key advantages:
No need to put data back on Kafka (avoids duplicate events).
Avoids concurrent MERGE issues by using a separate reconciliation table.
Works with both streaming and batch ETLs.
Automates retries with alerting and monitoring.
Example: Login Events Pipeline
Login events processed in near real-time, joined with user dimension table (updated by batch ETL).
If a user dimension is missing, the event is flagged retryable.
The reconciliation table tracks retries: e.g., event_id 29 was retried twice before successfully joining.
Monitoring: a spike in retryable records indicates a problem with the batch job loading dimensions.
Best practices:
Partition the reconciliation table on status column to only read unresolved records.
Clean old error logs periodically to keep size minimal.
Use Trigger-once for batch mode to track new data via latest timestamp.
The reconciliation pattern is easy to plug into existing pipelines with a small boilerplate framework. It provides automated retries with built-in alerting and monitoring, ensuring data quality without manual intervention.
The fundamental difference is data layout on disk.
Row-oriented storage (e.g., PostgreSQL, MySQL): Values for a single row are stored contiguously. Ideal for OLTP workloads with frequent single-row reads, writes, and updates.
Column-oriented storage (e.g., DuckDB, Snowflake, ClickHouse): Values for a single column are stored contiguously. Ideal for OLAP workloads with large scans, aggregations, filtering over a subset of columns.
Key differences:
I/O pattern: Row-oriented reads entire rows (even if only few columns needed); column-oriented reads only the required columns, minimizing I/O.
Compression: Column-oriented is highly effective because similar data types are stored together (low entropy). Row-oriented is less effective due to mixed types.
Query performance: Column-oriented is significantly faster for analytical queries on column subsets; row-oriented faster for retrieving complete records (SELECT *).
Examples: MySQL, PostgreSQL (row); DuckDB, Snowflake, BigQuery, ClickHouse (column).
Apache Parquet: Open-source, widely adopted, strong compression and encoding. Use cases: cloud data lakes, Spark, Presto/Trino, AWS Athena, Azure Synapse.
Apache ORC: Optimized Row Columnar for Hadoop ecosystem. Use cases: Hadoop/Hive environments, legacy big data pipelines.
Apache Arrow: In-memory columnar format for fast analytics and cross-system data interchange. Use cases: DataFrames (Pandas, R), ML pipelines.
Delta Lake: Open-source storage layer that adds ACID transactions, time travel, and schema enforcement on top of Parquet. (See detailed comparison below.)
Primary workload is OLAP: Queries using SUM(), AVG(), GROUP BY over large datasets.
Read performance over write performance: Columnar systems optimized for high-speed retrieval; frequent single-row updates (OLTP) are better served by row-based databases.
Wide tables, few columns queried: Avoid wasted I/O from reading entire rows.
Compression is a priority: Superior compression reduces storage costs.
Because identical values are often adjacent, columnar storage compresses exceptionally well:
Dictionary Encoding: Replaces repeated strings with integer IDs. Best for low-cardinality columns.
Run-Length Encoding (RLE): Stores a value once with a count of its repetitions. Best for sorted columns or long runs of identical values.
Delta Encoding: Stores differences between consecutive values. Best for slowly changing series like timestamps.
Frame of Reference (FOR): Subtracts a minimum value and stores smaller offsets. Best for integer columns with limited range.
FSST (Fast Static Symbol Table): Tokenizes strings and builds a static dictionary of common substrings. Best for high-cardinality strings where dictionary encoding fails.
General-purpose (ZSTD, LZ4, GZIP): Applied on top of specialized encodings for further compression.
Superior compression: Reduces storage costs and speeds up queries by minimizing data read from disk.
Reduced I/O: If query needs 2 columns out of 100, reads only ~2% of total data.
Natural fit for modern CPUs: Data loads into CPU registers in vectors, processed in parallel using SIMD instructions.
Efficient aggregations: Operations like SUM(), AVG(), COUNT() loop over contiguous memory blocks.
Vectorized query execution: Process batches of thousands of values, amortizing function call overhead and leveraging CPU parallel processing.
Smart query optimization: Predicate pushdown applies WHERE filters at storage level; zone maps store min/max per block to skip irrelevant data.
Out-of-core processing: Can analyze datasets larger than RAM by offloading intermediate data to disk.
Updates: Column stores organize data into immutable column chunks. Updating a row requires rewriting entire chunks. Modern systems like ClickHouse use patch parts (lightweight deltas) to improve update performance by up to 1000×.
Denormalization: Historically, column stores lacked efficient joins, leading to denormalized wide tables. Modern stores have improved joins, but some denormalization still benefits query performance.
Knowing query patterns: Physical organization (sort keys, partitioning) dramatically impacts performance. Sorting by commonly filtered columns (e.g., timestamp) improves compression and data skipping.
Not a silver bullet: Not suitable for transactional workloads, SELECT * queries (row reconstruction overhead), small datasets, or high-concurrency single-row writes.
Delta Lake is a storage layer that builds on Parquet, adding critical features.
Parquet:
Immutable, binary, columnar format.
Advantages: column pruning, schema in metadata, better compression than row-based formats.
Pain points with multiple Parquet files:
No ACID transactions – data corruption risk.
Deleting rows is extremely difficult.
No DML transactions or change data feed.
Slow file listing overhead in cloud storage (key-value stores).
Expensive footer reads for file skipping.
Renaming, dropping columns requires full table rewrite.
Delta Lake:
Stores data in Parquet files plus a transaction log (_delta_log folder with JSON files).
Key advantages over Parquet:
ACID transactions: If a cluster dies mid-write, partially written files are ignored; subsequent reads continue without error.
Fast file listing: File paths stored in transaction log; no cloud file listing needed.
Small file compaction: OPTIMIZE command consolidates small files automatically, with data_change=False flag preventing downstream reprocessing.
Predicate pushdown: Min/max statistics in transaction log for all files — no need to read 10,000 file footers before querying (10x–100x speed gains).
Z-Order indexing: Co-locate similar data for more effective file skipping.
Schema enforcement and evolution: Rejects mismatched schemas; allows adding new columns without rewriting data.
Check constraints: Custom SQL checks on columns.
Time travel and rollback: Query any prior version; rollback with a single command.
Deleting rows: Simple DELETE command rewrites only impacted files; deletion vectors flag deleted rows for faster execution.
Merge transactions: Powerful MERGE command for upserts and SCDs.
Additional features: Metadata-accelerated aggregations, generated columns, Change Data Feed (CDF).
When to use Parquet instead of Delta Lake:
Interfacing with systems that don't support Delta Lake. Since Delta tables store data in Parquet files, conversion is straightforward. Delta connectors are increasingly available, so incompatibility is rare.
For any production data system, especially in the cloud, Delta Lake provides reliability, performance, and operational simplicity that plain Parquet cannot match. It makes common operations (dropping/renaming columns, deleting rows, DML) simple and efficient. Transactions and schema enforcement prevent costly corruption. The transaction log enables fast queries via Z-Ordering and file skipping.
Data growth requires distributed processing. Hadoop's MapReduce was disk-intensive and slow. Spark was designed to solve these flaws.
HDFS (Hadoop Distributed File System):
Distributed, scalable, fault-tolerant file system for very large files across commodity hardware.
Core components:
NameNode: Master server managing file system namespace and metadata (block locations). Does not store actual data.
DataNode: Worker nodes storing actual data blocks. Report to NameNode via heartbeats.
Secondary NameNode: Periodically merges edit log with filesystem image to prevent log growth.
Block: Fundamental storage unit (default 128 MB). Files split into blocks, replicated across nodes.
Replication: Default factor of 3; one replica on local node, one on different node in same rack, one on a node in a different rack (rack awareness).
Data locality: Tasks scheduled on the same node where data block resides to minimize network I/O.
Tightly coupled compute and storage: In traditional Hadoop, HDFS data nodes also served as compute nodes, leading to inefficiencies. Modern systems separate storage and compute.
Apache Spark is a unified computing engine and set of libraries for parallel data processing on computer clusters. It provides one platform for batch, streaming, SQL, machine learning, and graph processing.
Key benefit: In-memory computing — stores intermediate data in memory, up to 100× faster than Hadoop MapReduce.
Languages supported: Java, Scala, Python, R, SQL.
Fault tolerance: Via RDD lineage (recomputation lost partitions).
Origins:
2004: MapReduce model.
2009: Developed at UC Berkeley's AMPLab by Matei Zaharia.
2010: Open sourced under BSD license.
2013: Joined Apache Software Foundation.
2014: Became top-level Apache project.
Key features:
Batch processing (Spark Core)
Interactive queries (Spark SQL) — up to 100× faster than MapReduce, with cost-based optimizer (Catalyst), columnar storage, code generation.
Machine learning (MLlib) — scalable algorithms for classification, regression, clustering.
Graph processing (GraphX) — distributed graph processing (PageRank, connected components).
Near real-time streaming (Structured Streaming) — micro-batch processing using DataFrame/Dataset API.
ETL operations — extract, transform, load complex transformations.
Spark operates on a master-worker architecture:
Driver Program: Central coordinator containing SparkContext. Translates logical plans into physical execution plans, coordinates with cluster manager, monitors job execution.
Cluster Manager: Allocates CPU and memory resources. Supports Standalone, YARN, Apache Mesos, Kubernetes.
Executors: Worker processes that execute tasks, report results to driver, store cached data, handle shuffle operations.
Worker Nodes: Machines hosting multiple executors.
Execution flow:
Driver creates SparkSession (entry point).
SparkContext connects to cluster manager.
Cluster manager allocates executors.
Driver sends tasks to executors.
Executors run tasks and return results.
Memory allocation in executors:
Storage memory: Cached RDDs/DataFrames.
Execution memory: Shuffles, joins, sorts.
User memory: User data structures (Python objects, UDFs).
Reserved memory: 300 MB safety zone.
Off-heap memory (Tungsten): Binary format, UnsafeRow storage, reduces GC pressure.
Overhead memory: Auto-allocated (max(384MB, 10% of executor memory)), for Python worker processes, native libraries, JVM overhead.
RDD (Resilient Distributed Dataset):
Low-level API (Spark 1.x original).
Immutable, fault-tolerant, distributed collection of objects.
Tracks lineage (graph of transformations) for fault recovery.
Does not use Catalyst optimizer.
Still available for low-level use cases but not recommended default.
Structured APIs (DataFrame, Dataset, SQL):
High-level, declarative: you describe what you want, Spark decides how.
Automatically optimized via Catalyst Optimizer.
DataFrame: Schema-structured, supports SQL style API, off-heap memory, less type safety, runtime errors.
Dataset: Object-oriented API, strong type safety, compile-time errors, available in Scala and Java.
Best for ETL, analytics, ML workflows. DataFrames are the modern, optimized way.
RDD vs Structured API comparison:
RDD: Program how to do; OOPs style API; on-heap JVM objects; serialization unavoidable; GC impacts performance; strong type safety; no optimization; compile-time errors; Java/Scala/Python/R; no schema.
DataFrame: Program what to do; SQL style API; off-heap also used; serialization can be avoided (off-heap); GC impact mitigated; less type safety; Catalyst optimizer; runtime errors; Java/Scala/Python/R; schema structured.
Dataset: Program what to do; OOPs style API; off-heap also used; serialization can be avoided (encoder); GC impact mitigated; strong type safety; optimization; compile-time errors; Scala and Java; schema structured.
DAG (Directed Acyclic Graph):
Spark's execution blueprint. Vertices = RDDs; edges = transformations.
When an action is called, the DAG Scheduler splits the graph into stages at shuffle boundaries.
Narrow transformations (e.g., map(), filter()) — no shuffle, grouped into one stage.
Wide transformations (e.g., reduceByKey(), groupByKey()) — data shuffles, create stage boundaries.
Fault Tolerance: Lost partitions recomputed via lineage.
DAG vs MapReduce:
MapReduce writes to disk after each map/reduce step; no memory caching; limited to map + reduce.
Spark's DAG supports multiple levels, avoids intermediate disk writes, enables global optimization.
Job, Stage, Task:
Job: One per action (e.g., count(), collect()).
Stage: Job split at shuffle boundaries. Each stage contains a set of parallel tasks.
Task: One per partition per stage. Smallest unit of work executed by an executor.
Catalyst Optimizer:
Transforms SQL/DataFrame query into an optimized execution plan through phases:
Analysis (resolve references, bind to catalog)
Logical optimization (predicate pushdown, constant folding, projection pruning)
Physical planning (cost model selects best plan)
Code generation (produce efficient JVM bytecode)
Key optimizations: Predicate pushdown, constant folding, projection pruning.
Tungsten Engine:
Low-level execution engine focusing on memory and CPU efficiency.
Uses off-heap memory, fuses entire sections of execution plan into one Java function (no virtual function calls, no intermediate objects).
Catalyst decides WHAT; Tungsten decides HOW.
Adaptive Query Execution (AQE):
Re-optimizes queries at runtime using actual statistics (table sizes, partition sizes, join cardinalities).
Dynamically adjusts execution plan to fix inaccurate pre-execution estimates.
PySpark:
Spark's Python API. Uses Py4J bridge to communicate between Python driver and JVM driver.
For most DataFrame/SQL operations, data processed in JVM; data brought into Python only for actions like collect(), toPandas(), or Python/pandas UDFs.
SparkSQL Examples:
Read CSV, JSON, Parquet, Delta formats using spark.read.format("...").
Write with partitioning: df.write.format("parquet").partitionBy("keyColumn").save(path).
Advanced queries: multi-table joins, column wrangling, deduping using window functions.
Spark's built-in functions are preferred over UDFs for performance.
Cheat Sheet (common operations):
Read CSV: spark.read.format("csv").option("header","true").load(filePath)
Infer schema: option("inferSchema","true")
Custom schema: StructType().add(...)
Write CSV/JSON/Parquet: df.write.format("...").mode("overwrite").save(path)
Read Delta: spark.sql("SELECT * FROM delta./path/to/delta")
Write Delta: df.write.format("delta").partitionBy("col").save(path)
Spark is often used with Hadoop for storage (HDFS), combining strengths.
Efficient file formats: Use Parquet or ORC; avoid CSV for large datasets.
Cache wisely: Persist DataFrames reused multiple times; unpersist when done.
Partition and bucket data: Improve skipping of irrelevant data and speed up joins.
Minimize data volume: Push filters early; avoid expensive sorts when unnecessary.
Repartition/coalesce: Tune partition count to match cluster resources and data size.
Avoid UDFs: Use built-in Spark functions (bypass Catalyst if UDF).
Cluster autoscaling (Databricks): Dynamically adjust worker nodes based on workload for cost optimization.
Spark is a unified engine for batch, streaming, SQL, ML, and graph processing.
Lazy evaluation + DAG allows building an optimized execution plan before processing.
Three-tier architecture: Driver → Cluster Manager → Executors.
Automatic optimization: Catalyst (logical/physical), Tungsten (execution), AQE (runtime).