Data Modeling Skill

Design data models for analytics warehouses and operational systems. Cover dimensional modeling, normalization, entity relationships, slowly changing dimensions, and schema design patterns.

Workflow

Step 1: Understand the Modeling Context

Before designing, establish:

1. Purpose: OLTP (transactional) or OLAP (analytical)?
2. Consumers: BI tools, SQL analysts, applications, ML pipelines?
3. Query patterns: What questions will this model answer?
4. Data volume: How much data and how fast is it growing?
5. Update patterns: Append-only, upserts, full replacements?
6. History requirements: Do we need to track changes over time?
7. Performance requirements: Sub-second dashboards or batch reports?

Step 2: Choose the Modeling Approach

Recommendation for most analytics use cases: Start with a star schema. It balances query performance, simplicity, and maintainability.

Step 3: Identify Facts and Dimensions

Facts (Measures -- What Happened)

Facts are the measurable events of the business:

Fact Table Checklist:
- [ ] What business process does this measure?
- [ ] What is the grain? (One row per ___)
- [ ] What are the numeric measures? (amount, quantity, duration)
- [ ] What are the foreign keys to dimensions?
- [ ] Is this additive, semi-additive, or non-additive?

Types of fact tables:

-- Transaction fact table
CREATE TABLE fct_orders (
    order_key       BIGINT PRIMARY KEY,      -- Surrogate key
    order_id        VARCHAR(50) NOT NULL,     -- Natural/business key
    customer_key    BIGINT REFERENCES dim_customers,
    product_key     BIGINT REFERENCES dim_products,
    date_key        INTEGER REFERENCES dim_date,
    channel_key     INTEGER REFERENCES dim_channels,

    -- Measures
    quantity        INTEGER NOT NULL,
    unit_price      DECIMAL(12,2) NOT NULL,
    discount_amount DECIMAL(12,2) DEFAULT 0,
    gross_revenue   DECIMAL(12,2) NOT NULL,
    net_revenue     DECIMAL(12,2) NOT NULL,
    tax_amount      DECIMAL(12,2) DEFAULT 0,

    -- Metadata
    _loaded_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

Dimensions (Context -- Who, What, Where, When)

Dimensions provide the context for analyzing facts:

-- Dimension table
CREATE TABLE dim_customers (
    customer_key      BIGINT PRIMARY KEY,        -- Surrogate key
    customer_id       VARCHAR(50) NOT NULL,       -- Natural key
    customer_name     VARCHAR(200),
    email             VARCHAR(200),
    segment           VARCHAR(50),                -- Enterprise, SMB, Consumer
    industry          VARCHAR(100),
    country           VARCHAR(100),
    region            VARCHAR(50),
    acquisition_channel VARCHAR(50),
    first_order_date  DATE,
    lifetime_orders   INTEGER,
    lifetime_revenue  DECIMAL(14,2),
    is_active         BOOLEAN,

    -- SCD metadata
    _valid_from       TIMESTAMP NOT NULL,
    _valid_to         TIMESTAMP DEFAULT '9999-12-31',
    _is_current       BOOLEAN DEFAULT TRUE,

    -- Audit
    _loaded_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

Step 4: Design the Date Dimension

Every warehouse needs a date dimension:

CREATE TABLE dim_date (
    date_key          INTEGER PRIMARY KEY,       -- YYYYMMDD format
    full_date         DATE NOT NULL,
    day_of_week       INTEGER,                   -- 1=Monday, 7=Sunday
    day_name          VARCHAR(10),               -- Monday, Tuesday...
    day_of_month      INTEGER,
    day_of_year       INTEGER,
    week_of_year      INTEGER,
    iso_week          INTEGER,
    month_number      INTEGER,
    month_name        VARCHAR(10),
    month_short       VARCHAR(3),                -- Jan, Feb...
    quarter_number    INTEGER,
    quarter_name      VARCHAR(2),                -- Q1, Q2, Q3, Q4
    year_number       INTEGER,
    year_month        VARCHAR(7),                -- 2024-01
    year_quarter      VARCHAR(7),                -- 2024-Q1
    fiscal_year       INTEGER,
    fiscal_quarter    INTEGER,
    is_weekend        BOOLEAN,
    is_holiday        BOOLEAN,
    holiday_name      VARCHAR(100)
);

Step 5: Handle Slowly Changing Dimensions (SCD)

SCD Type 2 Implementation

-- Merge pattern for SCD Type 2
MERGE INTO dim_customers target
USING staging_customers source
ON target.customer_id = source.customer_id AND target._is_current = TRUE

-- Existing record with changes: close the old version
WHEN MATCHED AND (
    target.segment <> source.segment
    OR target.region <> source.region
    OR target.is_active <> source.is_active
) THEN UPDATE SET
    _valid_to = CURRENT_TIMESTAMP(),
    _is_current = FALSE

-- No changes: do nothing (handled by not matching)

-- New record or changed record: insert new version
WHEN NOT MATCHED THEN INSERT (
    customer_key, customer_id, customer_name, segment, region, is_active,
    _valid_from, _valid_to, _is_current
) VALUES (
    seq_customer_key.NEXTVAL, source.customer_id, source.customer_name,
    source.segment, source.region, source.is_active,
    CURRENT_TIMESTAMP(), '9999-12-31', TRUE
);

-- Also insert new versions for changed records (requires a second pass or CTE)

Querying SCD Type 2

-- Current state (most common)
SELECT * FROM dim_customers WHERE _is_current = TRUE;

-- State as of a specific date
SELECT * FROM dim_customers
WHERE '2024-06-15' BETWEEN _valid_from AND _valid_to;

-- Join fact to dimension for point-in-time accuracy
SELECT f.*, d.segment, d.region
FROM fct_orders f
JOIN dim_customers d
  ON f.customer_key = d.customer_key
  AND f.order_date BETWEEN d._valid_from AND d._valid_to;

Step 6: Define Relationships and Constraints

Document the entity-relationship model:

fct_orders
  |-- customer_key -> dim_customers.customer_key
  |-- product_key  -> dim_products.product_key
  |-- date_key     -> dim_date.date_key
  |-- channel_key  -> dim_channels.channel_key

dim_products
  |-- category_key -> dim_categories.category_key  (snowflake extension)

dim_customers
  |-- geography_key -> dim_geography.geography_key  (snowflake extension)

Cardinality:

dim_customers  1---*  fct_orders  (one customer, many orders)
dim_products   1---*  fct_orders  (one product, many orders)
dim_date       1---*  fct_orders  (one date, many orders)

Step 7: Normalization Decisions

When to denormalize:

• Dashboard performance requires it (eliminate joins)
• BI tool cannot handle complex joins
• The dimension is small and rarely changes
• Query simplicity is more valuable than storage savings

When to keep normalized:

• Data changes frequently (avoid update anomalies)
• Storage costs matter (very large dimensions)
• Multiple fact tables reference the same dimension

Step 8: Document the Model

Create documentation that includes:

## Data Model: [Business Process]

### Overview
[1-2 paragraph description of what this model covers]

### Grain
[What one row represents in each fact table]

### Facts
| Table | Grain | Key Measures | Update Frequency |
|-------|-------|-------------|-----------------|
| fct_orders | One row per order line | revenue, quantity | Daily incremental |

### Dimensions
| Table | SCD Type | Key Attributes | Cardinality |
|-------|----------|----------------|-------------|
| dim_customers | Type 2 | segment, region | ~500K |

### Relationships
[ER diagram or relationship list]

### Known Limitations
[What this model cannot answer]

Output Format

Deliver a data model design with:

1. Entity-relationship diagram (text-based or visual)
2. DDL statements for all tables
3. Grain documentation for each fact table
4. SCD strategy for each dimension
5. Sample queries showing how the model answers business questions
6. Naming conventions used

Examples

Example 1: E-commerce Star Schema

Fact table for orders with dimensions for customers, products, dates, channels. SCD Type 2 on customers for segment tracking. Conformed date dimension shared across all facts.

Example 2: SaaS Product Analytics

Fact tables for events (transaction grain) and daily active users (periodic snapshot). Dimensions for users, features, plans. Wide event fact with JSON properties column.

Example 3: Financial Reporting

Fact table for journal entries with dimensions for accounts (chart of accounts hierarchy), cost centers, and periods. Supports drill-down from P&L line items to individual transactions.

Best Practices

1. Define the grain first. Everything else follows from a clear grain definition.
2. Use surrogate keys. Integer surrogate keys perform better than natural keys for joins and enable SCD Type 2.
3. Conformed dimensions. Use the same customer dimension across all fact tables. Do not create per-fact copies.
4. Prefer star over snowflake. The join reduction of star schemas outweighs the storage savings of snowflake schemas in most cases.
5. Model for the questions. Design the model to answer known business questions. Do not model everything just because it exists.
6. Track history where it matters. Use SCD Type 2 for attributes that affect analysis (segment, region) and Type 1 for attributes that do not (display name corrections).
7. Name consistently. Use fct_ prefix for facts, dim_ prefix for dimensions. Use snake_case. Be descriptive.
8. Avoid nulls in dimension keys. Use a "Unknown" or "Not Applicable" row in dimensions instead of null foreign keys.
9. Add metadata columns. Include _loaded_at, _updated_at, and _source on every table.
10. Start simple, iterate. Build the minimum model that answers the priority questions. Add complexity only when justified by new requirements.