Forem: Tarushi Vishnoi

Understanding Slowly Changing Dimensions (SCDs)

Tarushi Vishnoi — Mon, 10 Feb 2025 16:20:32 +0000

Data changes over time, and handling these changes efficiently is crucial in Data Warehousing. This is where Slowly Changing Dimensions (SCDs) come into play! SCDs help us manage changes in dimensional data while preserving or overwriting history as needed.

Let's dive into SCD Type 1, Type 2, and Type 3, what they are, and when to use them.

SCD Type 1: Overwrite (No History)

The old data is replaced with the new data and no history is maintained.
Best for: When historical data is not required (e.g., fixing typos, updating contact information).

Example Scenario: A customer updates their email address. We don't need to track the previous one.

Result: The old value is overwritten, and no history is maintained.

SCD Type 2: Historical Tracking (New Row for Each Change)

A new record is inserted when data changes.
A surrogate key is used, and additional columns like Start_Date, End_Date, and Is_Active are added to track history.
Best for: Tracking historical changes over time (e.g., changes in customer address, job role).

Example Scenario: A customer moves from New York to Los Angeles. We want to keep a record of both addresses.

Result: The table now holds both the old and new addresses, maintaining history.

SCD Type 3: Limited History with Extra Columns

A limited history is kept in additional columns (e.g., Previous_Value).
Only tracks one or two changes.
Best for: When only the previous value needs to be retained (e.g., tracking a customer’s last known city).

Example Scenario: A customer moves from Chicago to Houston. We store the previous and current values in separate columns.

Result: Only the most recent previous value is retained.

Managing Slowly Changing Dimensions effectively is key to maintaining accurate historical records in a data warehouse. Depending on your requirement, choose between SCD Type 1 (overwrite), SCD Type 2 (historical tracking), or SCD Type 3 (limited history).

Which SCD type do you use the most in your projects? Let’s discuss in the comments!

Beyond IT: How AI is Reshaping India's Economy

Tarushi Vishnoi — Sun, 09 Feb 2025 20:56:08 +0000

Been using AI for a while now but never realized the extent of its influence across various sectors in India beyond just IT. Recently, I came across an article in The Economic Times on how AI is amplifying consumption growth, and it sent me down a research spree. I was both surprised and excited to see the extent to which AI is actively shaping different aspects of the economy.

Artificial Intelligence is no longer just a buzzword; it’s becoming a major driver of economic transformation. India, with its rapidly expanding digital ecosystem, is leveraging AI to improve efficiency, reduce costs, and unlock new opportunities. From agriculture and manufacturing to healthcare, finance, and urban development, AI is streamlining operations and enabling new business models that are reshaping industries.

One of the most significant ways AI is influencing economic growth is through productivity enhancements. In manufacturing, AI-driven automation is optimizing production, reducing downtime, and minimizing inefficiencies. Predictive maintenance powered by AI is helping industries prevent equipment failures, saving costs and improving operational continuity. In agriculture, AI is assisting farmers with weather forecasts, soil analysis, and crop monitoring, improving yields while minimizing losses.

The financial sector is also undergoing a transformation. AI-powered solutions are making financial services more accessible by enabling faster loan approvals and digital banking. Fraud detection systems powered by AI are enhancing security, ensuring safer transactions for consumers and businesses alike. As access to financial tools becomes more widespread, more people can participate in economic activities, contributing to overall growth.

AI’s impact isn’t limited to industries, it’s also reshaping everyday consumption patterns. Higher productivity leads to cost reductions, ultimately increasing disposable income for consumers. AI-driven logistics solutions are optimizing supply chains, making goods more affordable. In the retail and digital entertainment sectors, AI is enabling personalized recommendations, enhancing user experiences, and driving increased engagement and spending.

Urban infrastructure is also benefiting from AI-driven advancements. Intelligent traffic management systems are helping to ease congestion in major cities, improving mobility and fuel efficiency. In governance, AI is optimizing processes such as tax collection and administrative efficiency, reducing inefficiencies, and enhancing service delivery. These improvements contribute to sustainable economic development, making cities smarter and more livable.

With increasing efforts to integrate AI into various sectors, its adoption is accelerating. AI is also creating opportunities in emerging fields such as data science, automation, and technology development, shaping India’s position in the global AI landscape. While concerns about automation replacing traditional jobs exist, AI also opens up new avenues for upskilling and reskilling, ensuring that the workforce is prepared for the evolving job market.

AI is not just a tech trend, it’s a game-changer for India’s economic landscape. By enhancing productivity, enabling financial inclusion, driving consumption, and improving governance, AI is actively contributing to GDP growth. As businesses, startups, and policymakers embrace AI-driven solutions, India is well on its way to becoming a global AI powerhouse.

Have you come across any AI-driven innovations making an impact? Let’s discuss!

Unlocking the Power of Dimensional Data Modeling (DDM)

Tarushi Vishnoi — Sun, 02 Feb 2025 12:52:22 +0000

Introduction
Dimensional Data Modeling (DDM) is a key technique used in data warehousing to optimize data for analytical and business intelligence (BI) applications. By organizing data into fact and dimension tables, DDM simplifies complex relationships, enabling fast and efficient data retrieval.

Why Dimensional Data Modeling?
Advantages:

Faster Query Performance – Optimized for OLAP (Online Analytical Processing) queries.
User-Friendly Structure – Simplifies data representation for better reporting.
Enhanced Business Insights – Supports trend analysis and decision-making.
Scalability – Handles growing data efficiently over time.

Real-World Applications of DDM

Amazon & E-commerce Platforms (Sales & Customer Analytics)

Use Case: Tracking sales, customer purchases, and inventory.
Fact Table: Stores sales transactions.
Dimension Tables: Product details, customer demographics, time, and store locations.
Example: Amazon leverages DDM to analyze best-selling products across different regions, aiding in demand forecasting and marketing.

Netflix & Streaming Platforms (User Behavior Analytics)

Use Case: Recommender systems & content performance analysis.
Fact Table: Stores user interactions like watch history and ratings.
Dimension Tables: User profiles, content categories, and viewing timestamps.
Example: Netflix personalizes recommendations based on a user’s watch history.

Uber & Ride-Sharing Apps (Operational Analytics)

Use Case: Trip analysis, driver performance, and surge pricing.
Fact Table: Stores ride details (pickup, drop-off, fare, time taken).
Dimension Tables: Rider details, driver details, locations, and time.
Example: Uber determines high-demand areas and dynamically adjusts driver distribution.

OLTP vs. OLAP: Understanding the Data Continuum
🔹 OLTP (Online Transaction Processing)

Handles real-time transactions (e.g., banking, order processing).
Optimized for fast inserts, updates, and deletes.

🔹 OLAP (Online Analytical Processing)

Used for business intelligence and reporting.
Works with aggregated historical data for complex queries.

🔹 The OLTP → OLAP Data Flow

Production Database (OLTP) → Snapshots → Master Data → OLAP Cubes → Metrics
This represents the flow of data from operational systems to analytical systems.

Production Database Snapshots (OLTP Stage): Extracts live transactional data.
Master Data: Consolidated and structured datasets.
OLAP Cubes: Pre-aggregated multidimensional structures for analytics.
Metrics & Reports: Key insights for decision-making.

🔹 Step-by-Step SQL Example
Step 1: Create an OLTP Table (Transaction Processing)

`CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(255),
    product_name VARCHAR(255),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity INT,
    total_price DECIMAL(10,2)
);`

Step 2: Create an OLAP Summary Table

`CREATE TABLE sales_summary AS
SELECT product_name, SUM(quantity) AS total_sold, SUM(total_price) AS total_revenue
FROM orders
GROUP BY product_name;`

Step 3: Generate Business Insights

`SELECT product_name, total_sold, total_revenue FROM sales_summary ORDER BY total_revenue DESC;`

Exploring Data with BI Tools
For a no-code approach, use:

Power BI / Tableau: Load transactional data and create dynamic reports.
Google Sheets / Excel: Use pivot tables for basic OLAP analysis.
Cloud Databases: Try Google BigQuery, AWS Redshift, or Snowflake for enterprise-level analytics.
Cumulative Table Design: A Smarter Approach to OLAP
Instead of querying raw transactions repeatedly, cumulative tables store pre-aggregated or running totals over time, optimizing query performance.

🔹 Example: Cumulative Sales Table

`CREATE TABLE cumulative_sales (
    sale_date DATE PRIMARY KEY,
    total_quantity INT,
    total_revenue DECIMAL(10,2)
);`

Benefits of Cumulative Tables:

Faster Queries- Reduces processing overhead.
Efficient Storage– Avoids scanning massive datasets.
Ideal for Trend Analysis– Tracks revenue growth, user signups, and sales trends.
Overcoming Data Challenges- Temporal Cardinality Explosion
Tracking time-based data significantly increases row count.

Example:
Airbnb Listings: 6 million properties × 365 days = ~2 billion rows!
Solution: Use optimized storage formats like Parquet, partitioning, and pre-aggregations.
Apache Spark: Handling Big Data Efficiently
Apache Spark is a distributed computing engine designed for large-scale data analytics.
Challenges: Spark Shuffle, high memory usage, inefficient joins.
Solutions: Use bucketing, partitioning, and columnar formats (Parquet, ORC) to improve performance.

Run-Length Encoding (RLE) Compression
Compression technique that reduces storage size by encoding repeated values efficiently.

Works Best With: Columnar storage (Parquet), where consecutive values repeat.
Avoid Disruptions: Minimize Spark shuffling to maintain compression efficiency.

Why DDM Matters?
Dimensional Data Modeling is at the heart of modern business intelligence, analytics, and data warehousing. Whether you are an analyst, data engineer, or business leader, understanding how to structure data for performance, scalability, and insights is key to making informed decisions.

Key Takeaways:

Use Fact & Dimension Tables for optimized analytics.
Understand OLTP → OLAP flow to manage real-time vs. historical data.
Optimize performance with cumulative tables, partitioning, and compression.
Leverage BI tools & cloud platforms for enterprise-level data analysis.