92 ) Slowly Changing Dimensions (SCD) Types

 

Slowly Changing Dimensions (SCD) Types and Implementation

SCD Type	Definition & Strategy	SQL Implementation Approach
1. SCD Type 1	Overwrite: Old values are directly overwritten with new updates. No historical tracking is maintained.	UPDATE dim_customer SET address = 'New Address' WHERE customer_id = 101;
2. SCD Type 2	Add New Row: A new record row is inserted to capture the change, using effective start/end dates and an active flag.	UPDATE dim_customer SET end_date = CURRENT_DATE, is_current = 0 WHERE customer_id = 101 AND is_current = 1; INSERT INTO dim_customer (customer_id, address, start_date, end_date, is_current) VALUES (101, 'New Address', CURRENT_DATE, '9999-12-31', 1);
3. SCD Type 3	Add New Attribute: A new column is added to track the previous value alongside the current value, keeping a limited history window.	UPDATE dim_customer SET previous_address = address, address = 'New Address' WHERE customer_id = 101;
4. SCD Type 4	Mini-Dimension: Volatile attributes are split off from the main dimension into a separate secondary table to prevent row bloat.	Separate the profile into dim_customer_core and dim_customer_rapid_behavior, joining them via foreign keys.
5. SCD Type 5	Mini-Dimension with Type 1 Outrigger: A hybrid combining a Type 4 mini-dimension with a Type 1 current-state override overlay.	Combines historical tracking in a mini-dimension with a direct pointer column for fast current-state filtering.
6. SCD Type 6	Hybrid (Types 2 + 3 + 1): Combines historical row-version tracking (Type 2) with embedded current-attribute values (Type 1/3) in every row.	Updates all historical rows for a given key with the newest current attribute value while maintaining distinct start/end version intervals.

91 ) Differences Between OLTP and OLAP

 

10 Key Differences Between OLTP and OLAP

OLTP (Online Transaction Processing)	OLAP (Online Analytical Processing)
1. Primary Purpose: Executes and manages real-time day-to-day transactional business operations.	1. Primary Purpose: Enables complex data analysis, business intelligence reporting, and historical trend discovery.
2. Data Structure: Highly normalized (typically 3NF) to minimize data redundancy and ensure write integrity.	2. Data Structure: Denormalized (Star or Snowflake schemas or multidimensional cubes) optimized for read performance.
3. Query Workload: High volume of short, fast, write-heavy/read-light transactions (INSERT, UPDATE, DELETE).	3. Query Workload: Low volume of long-running, complex, read-heavy analytical queries scanning millions of rows.
4. Data Window: Contains current, live, operational data representing the active state.	4. Data Window: Contains historical, integrated, and aggregated data spanning years or decades.
5. Response Time: Measured in milliseconds for rapid point lookups.	5. Response Time: Measured in seconds or minutes due to heavy data aggregation scans.
6. Data Volume: Generally smaller storage footprints (Megabytes to Gigabytes) active per operational slice.	6. Data Volume: Massive storage capacity required (Terabytes to Petabytes).
7. User Base: Customer-facing or frontline operational staff (clerks, cashiers, online shoppers).	7. User Base: Business analysts, data scientists, managers, and executives driving strategy.
8. Concurrency: Supports thousands of concurrent users executing small transactions simultaneously.	8. Concurrency: Supports fewer concurrent users, as each query demands intense sequential CPU and I/O resources.
9. Backup Requirements: Continuous and rigorous backups to maintain immediate operational continuity and compliance.	9. Backup Requirements: Periodic backups or simple data reloads from sources since historical data can be re-extracted via ETL pipelines.
10. Example Project Tables: * Orders (OrderID, CustomerID, OrderDate, Status) * Order_Items (OrderItemID, OrderID, ProductID, Quantity) * Inventory (ProductID, WarehouseID, StockLevel) * Customers (CustomerID, Name, Email, Address)	10. Example Project Tables: * Fact_Sales (SaleKey, DateKey, CustomerKey, ProductKey, StoreKey, QuantitySold, TotalRevenue) * Dim_Customer (CustomerKey, CustomerName, Region, Segment) * Dim_Product (ProductKey, ProductName, Category, Brand) * Dim_Date (DateKey, FullDate, Month, Quarter, Year)