Data Modeling for Operational and Analytical Systems

In plain terms

A data model describes entities, relationships, constraints, and the meaning of stored values. Declaring what one row represents is an early decision: an order, a customer’s current state, and a customer observed on a particular day have different grains. These tables may share attributes but retain different information. Before joining them, check the intended row identity, time basis, and matching relationship.

Operational and analytical workloads often need different models. A checkout database uses keys, constraints, and transaction logic to record purchases consistently; duplicate requests still require explicit handling. An analytical model makes recurring queries such as “revenue by country by month” easier to express; speed still depends on implementation and workload. The same order lives in both, in different shapes, and a large part of data engineering is carrying it from one shape to the other without losing meaning.

This article is the vocabulary and the decisions: keys, grain, normalization, facts and dimensions, events versus state, and how to keep history that the source throws away. These concepts support both operational and analytical models. The lab compares current-state joins, daily snapshots, type 2 history, and observed changes on a generated dataset and shows, in numbers, what each choice does to a report.

Entities, relationships, keys

An entity is a thing the business talks about: a customer, a product, an order, a subscription. A relationship is how entities connect, and its cardinality, how many of one go with how many of the other, decides the table structure.

A candidate key uniquely identifies a row with no unnecessary columns; the chosen one is the primary key (PK). A foreign key (FK) refers to an allowed key in another table. A natural key comes from the business domain; a surrogate key is generated for identification. Email is only a candidate key if the business guarantees uniqueness and defines how changes are handled. A surrogate key helps decouple identity from mutable attributes, but does not itself prevent duplicate business entities.

Keep identifiers stable within a documented namespace. If two systems both issue customer 123, preserve the source-system identifier as well. Text is appropriate for source codes with leading zeros or letters; integer warehouse surrogate keys and native UUID types are also valid. Choose the type for identity preservation and the database, not according to whether arithmetic will be performed.

Grain: the most important word in this series

The grain of a table is the exact statement of what one row represents. Not roughly: exactly, including the time dimension.

Every column must have a defined meaning at the table’s grain. On an order, customer_country could mean country at purchase, current customer country, or shipping destination; one customer does not make those meanings interchangeable. Likewise, customer lifetime spend can be a documented as-of-order feature or a current value requiring refresh. Specify the time basis and whether a repeated customer-level measure may be aggregated across orders.

A join can repeat a measure at the wrong grain. An order worth 60 with three lines becomes three joined rows carrying 60 each; summing that order total gives 180. Sum line amounts, or aggregate each side to a shared grain before joining. The same failure hides in less obvious places: a daily table that also contains a “month to date” row, an events table where some rows are individual events and others are hourly rollups, a customer table where test accounts have one row per environment. The defence is procedural: state the grain in the table’s documentation, in one sentence, and check every join against it. Reviewers should ask for it; if the author cannot say it in one sentence, the table is not finished.

Keep the detail needed for supported questions and reprocessing, subject to retention, access, and cost requirements. A daily total alone cannot reconstruct its individual orders. Some measures can be aggregated from lines to orders, but shipping fees, taxes, and discounts need explicit allocation rules.

Modeling for operations: normalization

An operational database supports online transaction processing (OLTP): recording orders, updating stock, and handling concurrent changes. Normalization reduces redundant facts and the inconsistencies they can cause. Transactions and constraints are still needed; normalization alone does not prevent duplicate requests or guarantee correct writes.

The problem it solves

-- one flat table: one row per order line, with repeated customer and product attributes
order_id | customer_email    | customer_city | product_code | product_name | unit_price | qty
A17      | kim@example.com   | Seoul         | P3           | Coffee 1kg   | 19.99      | 2
A17      | kim@example.com   | Seoul         | P7           | Filter x100  | 4.50       | 1
A18      | kim@example.com   | Seoul         | P3           | Coffee 1kg   | 19.99      | 1

Assume customer_city means the current city and product_name is the current catalog name. Then the table has three anomalies. Update anomaly: the customer moves to Busan; three rows must change, and if one is missed, the customer is in two cities. Insert anomaly: you cannot record a new product until someone orders it. Delete anomaly: delete the only order for a product and the product disappears. Each is a way for the database to contradict itself, and the second lab counts the rows the first one touches.

The normal forms, in plain language

• First normal form (1NF): every cell holds one value. No “P3, P7” in a products column, no repeating groups (product1, product2, product3). Lists become rows in another table.
• Second normal form (2NF): after 1NF, a non-key attribute must not depend on only part of a candidate key. If each product appears at most once per order and the key is (order_id, product_code), product_name depends only on product_code, so it moves to a products table.
• Third normal form (3NF): remove transitive dependencies of non-key attributes on keys in this example: order_id → customer_id → current_city. Put current city in customers. If the column instead means shipping city for this order, it is an order attribute and belongs with the order.

A useful introductory mnemonic, rather than a complete formal definition: every non-key column depends on the key, the whole key, and nothing but the key. Applied to the flat table:

customers (customer_id PK, email UNIQUE, city, ...)
products  (product_id  PK, product_code UNIQUE, product_name, current_price, ...)
orders    (order_id    PK, customer_id FK, ordered_at, status, ...)
order_items (order_id FK, line_no, product_id FK, quantity, unit_price_at_order, PK (order_id, line_no))

These four tables separate current customer and product attributes from order facts. The pair (order_id, line_no) identifies a line even when the same product appears twice. Notice unit_price_at_order in order_items: it looks like a violation, the price is a product attribute, but it is not, because the price at the moment of the order is a fact about the order line, and it must not change when the catalogue price does. Knowing when to keep a historical copy is the first appearance of the history problem that dominates the second half of this article.

Normalized models can support analysis, but repeatedly assembling the same business view creates work and opportunities for join mistakes. Performance depends on data volume, indexes, storage, and the query plan, not simply the number of joins. An analytical model makes recurring questions easier to express.

Modeling for analysis: facts and dimensions

An online analytical processing (OLAP) model supports queries that filter, compare, and aggregate many records. Dimensional modeling organizes those queries around measurements and their descriptive context. Denormalizing selected attributes can simplify queries, with trade-offs in storage, refresh cost, and consistency.

The dominant pattern is the star schema: a central fact table of measurements at a declared grain, surrounded by dimension tables that describe the who, what, where, when of each measurement.

                 dim_customer                dim_product
                 (customer_key, name,        (product_key, name,
                  country, segment, …)        category, brand, …)
                          \                      /
                           \                    /
                         fact_order_items  (grain: one row per order line)
                         order_line_key, order_id, date_key, customer_key, product_key,
                         quantity, unit_price, line_amount, discount_amount, currency
                           /                    \
                          /                      \
                 dim_date                         dim_store / dim_channel
                 (date_key, day, week, month,     (channel_key, channel_name, …)
                  quarter, is_holiday, …)

• Measures need aggregation rules. Sales amounts can be additive within one currency; account balances are not additive across dates; percentages and unit prices generally cannot be summed. Compute an overall ratio from its numerator and denominator. Fact tables may also record events without a numeric measure.
• Dimensions are descriptive context: names, categories, countries, dates. Their size depends on the entities and retained history; a date dimension and a versioned customer dimension can have very different row counts. They are where the filters and group-bys come from.
• “Revenue by country by month” becomes: fact joined to dim_customer for country and dim_date for month, sum(line_amount), group by. Group by currency too unless the amounts share one currency. An appropriate date filter can enable partition pruning when the storage and engine support it.

Kimball’s dimensional modeling techniques distinguish additive, semi-additive, and non-additive measures.

A transaction fact records one occurrence, such as an order line. A periodic snapshot fact records a regular observation, such as each account’s closing daily balance. An accumulating snapshot fact follows one process instance, updating milestones such as accepted, shipped, and delivered dates. Its latest row cannot by itself reproduce what was known yesterday; that requires retained versions or events.

Conformed dimensions give different fact tables compatible attribute definitions and values. Reports using customer segment must agree on its meaning and historical interpretation; compatible copies can serve separate systems. Before comparing orders and support tickets, aggregate each fact separately to the chosen shared reporting grain. Joining their detail rows by customer can multiply both measures.

Wide tables and when to use them

A one big table (OBT) prejoins dimension attributes onto facts. It can simplify a stable consumer workload, and columnar compression can reduce repeated-value storage. When attributes mean current values, updates may touch many rows; when they mean values at event time, preserving the old value may be intentional. Choose stars or wide tables according to workload, history semantics, and maintenance cost rather than a universal core-versus-mart rule.

Wide and long describe a reshape, not a star schema

Wide and long data describe where repeated measurements are placed. In this synthetic population example, wide data has one row per country with a column for each year. Long data has one row per country-year with a population value. A long table may still have separate population and average-age columns. Adding years then adds rows; it does not require a new population column for every year.

wide_population = [
    {"country": "A", "pop_2023": 100, "pop_2024": 110},
    {"country": "B", "pop_2023": 80, "pop_2024": None},
]
years = (2023, 2024)
long_population = [(row["country"], year, row[f"pop_{year}"])
                   for row in wide_population for year in years]
print(long_population)
# [('A', 2023, 100), ('A', 2024, 110), ('B', 2023, 80), ('B', 2024, None)]

def pivot_population(records):
    cells = {}
    for country, year, value in records:
        key = (country, year)
        if key in cells:
            raise ValueError("duplicate country-year; choose an aggregation explicitly")
        cells[key] = value
    return [{"country": country, **{f"pop_{year}": cells[(country, year)] for year in years}}
            for country in sorted({country for country, _ in cells})]

print(pivot_population(long_population) == wide_population)
# True

The missing population remains None rather than becoming zero or disappearing. Returning to wide form requires at most one value per country-year and a rule for missing combinations. This example requires every country-year cell to be present, even if its value is None. Aggregating duplicate cells changes information; it is not a lossless reshape. A prejoined order-line table (OBT) is a different design choice: it may repeat customer attributes without pivoting any measurement dimension. Neither long format nor fewer columns alone guarantees lower storage cost or faster queries.

Events and state

Two useful representations are current state and recorded events. Specify which one a table contains before counting changes.

Operational models often maintain current state for actions such as checkout, while also retaining transaction or event records. A current-state table alone cannot answer which customers became inactive last month after those values have been overwritten. Choose retained events, repeated observations, or version history to support the particular change question; not every analytical question requires history.

Reconstructing state from events requires an initial state, complete relevant events, ordering, and event semantics. Keeping only the latest row works when each event contains the full resulting state; a balance built from deposits and withdrawals requires accumulation. Comparing snapshots yields observed differences, not every event between observations. A change that is reversed before the next snapshot disappears from that comparison. Log-based CDC and periodic snapshot comparison therefore provide different evidence. Keep either or both according to the questions and available history.

Keeping history

When a source overwrites an attribute without retaining history, the platform needs a deliberate capture policy to support historical questions. Slowly changing dimension (SCD) techniques describe how dimension attributes change. Choose the required interpretation before deciding how to store versions.

Type 1: overwrite

Replace the old dimension value. Historical orders joined to that current value are grouped by the customer’s current city. That is appropriate for a report explicitly asking about current customer location or for an agreed correction, but not for location at order time.

Type 2: a new row per version

dim_customer (type 2)
customer_key | customer_id | city  | segment | valid_from  | valid_to    | is_current
1001         | C9          | Seoul | basic   | 2024-01-10  | 2025-06-30  | false
2417         | C9          | Seoul | premium | 2025-07-01  | 2026-03-13  | false
3902         | C9          | Busan | premium | 2026-03-14  | 9999-12-31  | true

One customer, three rows, each valid for a date range. Facts join to the version that was current when the fact happened: order A17 on 2026-03-14 joins to key 3902 (Busan, premium); an order on 2025-06-01 joins to 1001 (Seoul, basic), while one on 2025-08-01 joins to 2417 (Seoul, premium). “Revenue by city” now follows the captured validity intervals, provided the history and date boundaries are correct, and “how many customers upgraded to premium in July 2025” is a query on the dimension itself. This is the workhorse of analytical history, and its costs are real: the dimension grows, each version needs a distinct key (the same customer_id has three keys), and the load logic must detect changes and close the old row. The third lab derives one from daily snapshots; stable incremental version allocation is a separate implementation task.

The table uses inclusive calendar-date bounds. For timestamps, use a half-open interval such as valid_from ≤ event_time < valid_to, and define the timezone. A fact must resolve to exactly one version. A stable surrogate key can identify that version after lookup; a composite customer/version key is another possible representation.

Type 3: keep the previous value in a column

Store selected previous or alternate values in additional columns, such as segment and previous_segment. This retains limited history without another row. A change timestamp is also needed to answer when the change happened; the two value columns alone cannot identify who changed recently.

Snapshots: a copy per period

A periodic snapshot records selected attributes at a defined observation time. Daily snapshots cannot reveal changes within the day, and a night-time snapshot is not automatically safe for a prediction made that morning. Training features must have been available by prediction time. Type 2 or snapshot joins help select historical values but do not prevent leakage from late corrections, future-derived aggregates, or label construction. Exercise 6 isolates one current-versus-historical feature mismatch.

Choosing

Decide per attribute and document the policy. Overwritten history can only be recovered if another retained source, log, snapshot, or backup contains it. Preserve the evidence needed for supported questions within the agreed retention policy; daily snapshots cannot reconstruct every intermediate event.

Time in the model

Historical questions can involve two time axes. Some models retain only one; name which axis the stored timestamps represent. A value’s business validity and its availability to a particular system can differ.

• Valid time (business time): when the fact was true in the world. The customer moved on March 14.
• Transaction time (system time): when the modeled system recorded a version. The move was recorded on March 20, because the customer updated their profile late.

A March 16 report may use the city then known to the platform, while a later report restates that date using a correction received on March 20. A bitemporal model tracks both business validity and system-version intervals so those interpretations can be distinguished. A single loaded_at column does not preserve overwritten system versions. Reproducing an old report also requires its query logic and other inputs. For ML, check availability in the serving or feature system, not just the source database’s transaction time. A March 14-effective value received March 20 must not become a feature for a March 16 prediction simply because its valid-time interval covers that date.

Smaller decisions that cause large problems

• Money. Record the currency alongside each amount, or in an explicit single-currency dataset contract; amounts are DECIMAL or integer minor units; a converted amount carries the rate and the rate’s date (amount_usd, fx_rate, fx_rate_date) so it can be reproduced.
• Units. duration_ms, weight_g, distance_km. A column called duration will be summed in seconds by one person and milliseconds by another.
• Unknown versus not applicable. A dimension usually has a special row (key -1, name UNKNOWN) so that facts with a missing dimension value still join; a fact-preserving join must account for every intended fact. Distinguish “we do not know the country” from “country does not apply to this kind of order” if the business does.
• Booleans and status codes. Case comparison depends on the engine and collation. Map source spellings to agreed statuses, and decide explicitly whether paid and captured have the same business meaning. Preserve the source value.
• Derived values. If line_amount = quantity × unit_price − discount, store the inputs and compute the result in the model; storing only the result loses the ability to check it.
• Identity across systems. A web user, app user, email subscriber, and support contact may represent the same person under different IDs; establish that link with evidence rather than assuming it. Modeling that link, explicitly, as a mapping table with history, never by overwriting one ID with another, is an identity-resolution task.

The modeling process

Modeling turns business questions into definitions, structures, and checks that can be reviewed and implemented.

1. Collect the questions. From the consumer requirements: what do people actually ask? “Revenue by country by month”, “time to second order”, “churn by cohort”. Each question names its measures, its dimensions, and its grain.
2. List the business processes. Ordering, paying, shipping, subscribing, cancelling, contacting support. Choose transaction, periodic snapshot, or other fact grains that serve the questions; one process can need several tables.
3. List the entities that describe them. Customer, product, date, channel, location. Use a dimension where the questions require that context, and conform definitions across facts that need comparison.
4. Declare grain and keys. One sentence per table. Surrogate keys for dimensions; the natural key stored alongside.
5. Decide history per attribute. Type 1, 2, snapshot, or event.
6. Walk the questions through the model. Can each one be answered with the tables as drawn, with the joins you expect? If a join fans out, decide which measures would repeat and whether pre-aggregation, allocation, or an explicit many-to-many model is needed.
7. Write the data dictionary. For every table: grain, keys, history policy, owner. For every column: meaning, type, unit, nullability, example. This is the document analysts read; it is also the input to the catalog and the contract.

Worked example: the subscription commerce company

A fictional subscription commerce company. The operational schema is what the application team built; the analytical model is what the platform derives from it.

OPERATIONAL (Postgres, 3NF, current state)          ANALYTICAL (core layer, star, history kept)
──────────────────────────────────────────          ──────────────────────────────────────────────────────────
customers (id, email, name, city, country,          dim_customer  (type 2 on country, segment, plan;
           plan, created_at, updated_at)                            type 1 on name, email)
addresses (id, customer_id, line1, city, …,         dim_product   (type 2 on category, price band)
           is_default)                              dim_date      (generated; 1 row per day, 20 years)
products  (id, sku, name, category, price)          dim_channel   (web / ios / android / support)
subscriptions (id, customer_id, plan, status,
           started_at, next_billing_at)             fact_order_items   grain: one row per order line
orders    (id, customer_id, status, placed_at)                          keys: date, customer(v), product(v), channel
order_items (order_id, line_no, product_id, qty,                                 measures: qty, unit_price, line_amount, discount
           unit_price)                              fact_payments      grain: one row per payment attempt (Stripe)
payments  (id, order_id, provider_ref, amount,                          measures: amount, fee; status as a dimension
           status, captured_at)                     fact_subscription_events  grain: one row per status change
                                                                        (from CDC on subscriptions)
                                                    snap_subscription_daily   grain: one row per subscription per day
                                                                        (for "active subscribers on date X", and ML)

Decisions to notice. subscriptions is a state table in the source, so the platform derives an event table from its changes and a daily snapshot for questions at the supported observation times; the operational table alone could answer neither “churn in March” nor “active on Feb 1”. dim_customer is type 2 on the attributes the business segments by and type 1 on the ones it does not, chosen per attribute, written down. fact_payments is separate from fact_order_items because they have different grains and different sources; joining them is a reconciliation, not a single table. And addresses did not become a dimension at all: the questions in the consumer table only needed country, which lives on the customer.

Anti-patterns

• Undeclared grain. The table “has orders in it”. Nobody can join to it safely. Write the sentence.
• Entity-attribute-value. A table of (entity_id, attribute_name, value) to avoid deciding on columns. Useful for some sparse, evolving attributes, but typing and queries need extra rules. Prefer explicit typed columns for stable, frequently queried fields.
• Everything in a JSON column. JSON can preserve nested or evolving data. For frequently queried fields, define types and validation rules, and choose native JSON queries or extracted columns according to the engine and workload.
• Natural keys as identity. Email as the customer key. Changing a mutable key can require coordinated key updates and complicate history; define identity independently when that is the intended business meaning.
• Overwriting history by default. Type 1 everywhere because it was easiest. Discovered two years later when someone asks a question about the past.
• Modeling the source instead of the business. Copying the application’s 40 tables into the warehouse as-is and calling it a model. The application’s structure exists for the application’s reasons; the analytical model exists to answer questions.
• Premature wide tables in core. A 300-column customer table that must be rebuilt whenever anything changes. Choose the shape from query needs and refresh semantics.

Lab

The following standalone setup creates 500 customers observed at the start of each UTC day for 90 days and 5,000 orders with one to three lines each. All simulated attribute changes take effect at midnight and are immediately available; no customers disappear and tracked attributes are non-null. These assumptions make a daily as-of join sufficient for this lab, not for arbitrary source data. All amounts use one assumed currency, USD, with REAL arithmetic for compactness; use an exact monetary representation in a financial implementation. Run setup once, then the exercises in order in the same Python session. The SQL uses SQLite date and window functions; other engines may need syntax changes.

import random, sqlite3
from datetime import date, datetime, timedelta

rng = random.Random(0)
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE customer_snapshots (snapshot_date TEXT NOT NULL, customer_id TEXT NOT NULL, country TEXT NOT NULL, segment TEXT NOT NULL, PRIMARY KEY (customer_id, snapshot_date));
CREATE TABLE orders (order_id TEXT PRIMARY KEY NOT NULL, customer_id TEXT NOT NULL, ordered_at TEXT NOT NULL, amount REAL);
CREATE TABLE order_items (order_id TEXT NOT NULL, line_no INTEGER NOT NULL, product_id TEXT, quantity INTEGER, unit_price REAL, PRIMARY KEY (order_id, line_no));
CREATE TABLE products (product_id TEXT PRIMARY KEY NOT NULL, product_name TEXT, category TEXT);
""")
customers = {f"C{i}": [rng.choice(["KR", "JP", "US"]), rng.choice(["basic", "premium"])] for i in range(1, 501)}
first, last = date(2026, 1, 1), date(2026, 3, 31)
day = first
while day <= last:
    if day.day == 15:
        # mid-month, attempt to change twenty-five customers; values may stay the same
        for cid in rng.sample(sorted(customers), 25):
            attr = rng.randrange(2)
            customers[cid][attr] = rng.choice(["DE", "BR"]) if attr == 0 else "premium"
    con.executemany("INSERT INTO customer_snapshots VALUES (?, ?, ?, ?)",
                    ((day.isoformat(), cid, c[0], c[1]) for cid, c in customers.items()))
    day += timedelta(days=1)
con.executemany("INSERT INTO products VALUES (?, ?, ?)",
                ((f"P{i}", f"Product {i}", rng.choice(["coffee", "tea", "gear"])) for i in range(1, 21)))
for i in range(1, 5001):
    ordered = datetime.combine(first, datetime.min.time()) + timedelta(days=rng.randrange(90), seconds=rng.randrange(86400))
    con.execute("INSERT INTO orders VALUES (?, ?, ?, ?)", (f"O{i}", f"C{rng.randint(1, 500)}", ordered.isoformat(sep=" ", timespec="seconds"), 0.0))
    for k in range(rng.randint(1, 3)):
        con.execute("INSERT INTO order_items VALUES (?, ?, ?, ?, ?)",
                    (f"O{i}", k + 1, f"P{rng.randint(1, 20)}", rng.randint(1, 3), round(rng.uniform(3, 40), 2)))
con.execute("UPDATE orders SET amount = (SELECT round(sum(quantity * unit_price), 2) "
            "FROM order_items i WHERE i.order_id = orders.order_id)")
for table in ("customer_snapshots", "orders", "order_items", "products"):
    print(table, con.execute(f"SELECT count(*) FROM {table}").fetchone()[0])
# customer_snapshots 45000
# orders 5000
# order_items 10010
# products 20
print(con.execute("SELECT count(*) FROM (SELECT DISTINCT customer_id, country, segment FROM customer_snapshots)").fetchone())
# (556,)
def dimension_issues():
    checks = {
        "version identity": """SELECT count(*) FROM (
            SELECT customer_key FROM dim_customer
            GROUP BY customer_key HAVING customer_key IS NULL OR count(*) != 1)""",
        "required version fields": """SELECT count(*) FROM dim_customer
            WHERE customer_id IS NULL OR country IS NULL OR segment IS NULL
               OR valid_from IS NULL OR valid_to IS NULL""",
        "current flag": """SELECT count(*) FROM dim_customer
            WHERE is_current IS NULL OR is_current NOT IN (0,1)
               OR is_current <> (valid_to = '9999-12-31')""",
        "invalid ranges": "SELECT count(*) FROM dim_customer WHERE valid_from > valid_to",
        "overlaps": """SELECT count(*) FROM dim_customer a JOIN dim_customer b
            ON a.customer_id=b.customer_id AND a.customer_key < b.customer_key
            AND a.valid_from <= b.valid_to AND b.valid_from <= a.valid_to""",
        "current row count": """SELECT count(*) FROM (
            SELECT s.customer_id FROM (SELECT DISTINCT customer_id FROM customer_snapshots) s
            LEFT JOIN dim_customer d ON d.customer_id=s.customer_id
            GROUP BY s.customer_id HAVING coalesce(sum(d.is_current),0) <> 1)""",
        "snapshot coverage": """SELECT count(*) FROM (
            SELECT s.customer_id, s.snapshot_date FROM customer_snapshots s
            LEFT JOIN dim_customer d ON s.customer_id=d.customer_id
                AND s.snapshot_date BETWEEN d.valid_from AND d.valid_to
            GROUP BY s.customer_id,s.snapshot_date HAVING count(d.customer_key) <> 1)""",
    }
    return [name for name, sql in checks.items() if con.execute(sql).fetchone()[0] != 0]

The snapshots contain 556 distinct combinations of customer, country, and segment. This shows repetition in the dataset, but does not generally give the number of type 2 versions: an A → B → A sequence has two distinct states and three version intervals. Exercise 3 finds consecutive runs rather than deduplicating attribute combinations.

1. State the grain. For each of the four tables, write the one-sentence grain. Then say which two derived tables from later exercises have grains that are easy to get wrong.

2. Normalize and denormalize. Flatten orders, items, products, and current customer attributes into one wide table and demonstrate the update anomaly: change one customer’s country and count the rows you had to touch. Then build the star and answer “revenue by country by month” against both.

con.execute("""CREATE TABLE flat_orders AS
SELECT o.order_id, i.line_no, o.customer_id, s.country, s.segment, o.ordered_at,
       i.product_id, p.product_name, p.category, i.quantity, i.unit_price
FROM   orders o JOIN order_items i USING (order_id) JOIN products p USING (product_id)
JOIN   customer_snapshots s ON s.customer_id = o.customer_id AND s.snapshot_date = '2026-03-31'""")
print(con.execute("SELECT count(*) FROM flat_orders").fetchone())
# (10010,)
con.execute("UPDATE flat_orders SET country = 'FR' WHERE customer_id = 'C1'")
print("flat table rows touched:", con.execute("SELECT changes()").fetchone()[0])
# flat table rows touched: 27

con.executescript("""
CREATE TABLE dim_customer_current AS
    SELECT customer_id, country, segment FROM customer_snapshots WHERE snapshot_date = '2026-03-31';
CREATE TABLE dim_product AS SELECT * FROM products;
CREATE TABLE fact_order_items AS
    SELECT o.order_id, i.line_no, i.product_id, o.customer_id, substr(o.ordered_at, 1, 10) AS order_date,
           i.quantity, i.unit_price, round(i.quantity * i.unit_price, 2) AS line_amount
    FROM orders o JOIN order_items i USING (order_id);
""")
con.execute("UPDATE dim_customer_current SET country = 'FR' WHERE customer_id = 'C1'")
print("star rows touched:", con.execute("SELECT changes()").fetchone()[0])
# star rows touched: 1

by_flat = con.execute("""SELECT country, substr(ordered_at, 1, 7) AS month, round(sum(quantity * unit_price), 2)
                         FROM flat_orders GROUP BY 1, 2 ORDER BY 1, 2""").fetchall()
by_star = con.execute("""SELECT c.country, substr(f.order_date, 1, 7) AS month, round(sum(f.line_amount), 2)
                         FROM fact_order_items f JOIN dim_customer_current c USING (customer_id)
                         GROUP BY 1, 2 ORDER BY 1, 2""").fetchall()
print(by_flat == by_star, by_star[:3])
# True [('BR', '2026-01', 7229.55), ('BR', '2026-02', 5885.79), ('BR', '2026-03', 6135.78)]

3. Build a type 2 dimension. From the ninety daily snapshots, derive dim_customer with valid_from, valid_to, is_current, one row per version. Verify: each customer has exactly one current row, and no two rows for the same customer overlap in time.

con.execute("""CREATE TABLE dim_customer AS
WITH changes AS (
    SELECT snapshot_date, customer_id, country, segment,
           lag(country) OVER w AS prev_country, lag(segment) OVER w AS prev_segment
    FROM   customer_snapshots
    WINDOW w AS (PARTITION BY customer_id ORDER BY snapshot_date)
),
starts AS (
    SELECT snapshot_date AS valid_from, customer_id, country, segment
    FROM   changes
    WHERE  prev_country IS NULL OR country <> prev_country OR segment <> prev_segment
)
SELECT row_number() OVER (ORDER BY customer_id, valid_from) AS customer_key,
       customer_id, country, segment, valid_from,
       coalesce(date(lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from), '-1 day'),
                '9999-12-31') AS valid_to,
       lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from) IS NULL AS is_current
FROM   starts""")
print(con.execute("SELECT count(*), sum(is_current), count(DISTINCT customer_id) FROM dim_customer").fetchone())
# (556, 500, 500)
print(con.execute("""SELECT * FROM dim_customer
                     WHERE customer_id IN (SELECT customer_id FROM dim_customer GROUP BY 1 HAVING count(*) = 3)
                     ORDER BY customer_id, valid_from LIMIT 3""").fetchall())
# [(15, 'C11', 'JP', 'basic', '2026-01-01', '2026-01-14', 0), (16, 'C11', 'BR', 'basic', '2026-01-15', '2026-02-14', 0), (17, 'C11', 'DE', 'basic', '2026-02-15', '9999-12-31', 1)]
print("overlapping versions:", con.execute("""SELECT count(*) FROM dim_customer a JOIN dim_customer b
    ON a.customer_id = b.customer_id AND a.customer_key < b.customer_key
    AND a.valid_from <= b.valid_to AND b.valid_from <= a.valid_to""").fetchone()[0])
# overlapping versions: 0
print("customers without exactly one current row:", con.execute("""SELECT count(*) FROM
    (SELECT customer_id FROM dim_customer GROUP BY 1 HAVING sum(is_current) <> 1)""").fetchone()[0])
# customers without exactly one current row: 0
issues = dimension_issues()
if issues:
    raise ValueError(issues)

4. Join facts to the right version. Join order lines to dim_customer on customer_id and order_date BETWEEN valid_from AND valid_to. Compute revenue by country. Then compute it again with a type 1 join (current country only) and explain, per country, why the numbers differ.

con.execute("CREATE TABLE IF NOT EXISTS fact_order_items AS "
            "SELECT o.order_id, i.line_no, i.product_id, o.customer_id, substr(o.ordered_at, 1, 10) AS order_date, "
            "i.quantity, i.unit_price, round(i.quantity * i.unit_price, 2) AS line_amount "
            "FROM orders o JOIN order_items i USING (order_id)")
con.execute("""CREATE TABLE IF NOT EXISTS dim_customer AS
WITH changes AS (
    SELECT snapshot_date, customer_id, country, segment,
           lag(country) OVER w AS prev_country, lag(segment) OVER w AS prev_segment
    FROM customer_snapshots WINDOW w AS (PARTITION BY customer_id ORDER BY snapshot_date)),
starts AS (
    SELECT snapshot_date AS valid_from, customer_id, country, segment FROM changes
    WHERE prev_country IS NULL OR country <> prev_country OR segment <> prev_segment)
SELECT row_number() OVER (ORDER BY customer_id, valid_from) AS customer_key, customer_id, country, segment, valid_from,
       coalesce(date(lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from), '-1 day'), '9999-12-31') AS valid_to,
       lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from) IS NULL AS is_current
FROM starts""")

issues = dimension_issues()
if issues:
    raise ValueError(issues)

as_it_was = con.execute("""SELECT d.country, round(sum(f.line_amount), 2) FROM fact_order_items f
    JOIN dim_customer d ON d.customer_id = f.customer_id AND f.order_date BETWEEN d.valid_from AND d.valid_to
    GROUP BY 1 ORDER BY 1""").fetchall()
as_it_is = con.execute("""SELECT d.country, round(sum(f.line_amount), 2) FROM fact_order_items f
    JOIN dim_customer d ON d.customer_id = f.customer_id AND d.is_current
    GROUP BY 1 ORDER BY 1""").fetchall()
print("type 2:", as_it_was)
# type 2: [('BR', 9447.09), ('DE', 8979.64), ('JP', 133538.91), ('KR', 152023.59), ('US', 126685.29)]
print("type 1:", as_it_is)
# type 1: [('BR', 19251.12), ('DE', 14891.04), ('JP', 126321.9), ('KR', 149043.89), ('US', 121166.57)]
print(con.execute("SELECT count(*) FROM fact_order_items").fetchone(),
      con.execute("""SELECT count(*) FROM fact_order_items f JOIN dim_customer d
                     ON d.customer_id = f.customer_id AND f.order_date BETWEEN d.valid_from AND d.valid_to""").fetchone())
# (10010,) (10010,)
def fact_match_issues():
    return con.execute("""SELECT f.order_id, f.line_no, count(d.customer_key) AS matches
        FROM fact_order_items f LEFT JOIN dim_customer d
          ON d.customer_id = f.customer_id
         AND f.order_date BETWEEN d.valid_from AND d.valid_to
        GROUP BY f.order_id, f.line_no HAVING count(d.customer_key) <> 1""").fetchall()

if fact_match_issues():
    raise ValueError("Each fact must match exactly one customer version")

5. Derive events from state. From the snapshots, produce customer_events: one row per attribute change, with the old and new value and the date. Count segment upgrades per month.

con.execute("""CREATE TABLE customer_events AS
WITH changes AS (
    SELECT snapshot_date, customer_id, country, segment,
           lag(country) OVER w AS prev_country, lag(segment) OVER w AS prev_segment
    FROM   customer_snapshots WINDOW w AS (PARTITION BY customer_id ORDER BY snapshot_date)
)
SELECT snapshot_date AS event_date, customer_id, 'country' AS attribute, prev_country AS old_value, country AS new_value
FROM   changes WHERE prev_country IS NOT NULL AND country <> prev_country
UNION ALL
SELECT snapshot_date, customer_id, 'segment', prev_segment, segment
FROM   changes WHERE prev_segment IS NOT NULL AND segment <> prev_segment""")
print(con.execute("SELECT attribute, count(*) FROM customer_events GROUP BY 1 ORDER BY 1").fetchall())
# [('country', 40), ('segment', 16)]
print(con.execute("""SELECT substr(event_date, 1, 7) AS month, count(*) FROM customer_events
                     WHERE attribute = 'segment' AND new_value = 'premium' GROUP BY 1 ORDER BY 1""").fetchall())
# [('2026-01', 3), ('2026-02', 7), ('2026-03', 6)]

6. Leak the future. Build a training table for “will this customer order again within 30 days” using each customer’s current segment. Then rebuild it using the segment as of the order date from the type 2 dimension. Count the segment mismatches. Under this lab’s assumptions they identify future values in that one feature.

con.execute("""CREATE TABLE IF NOT EXISTS dim_customer AS
WITH changes AS (
    SELECT snapshot_date, customer_id, country, segment,
           lag(country) OVER w AS prev_country, lag(segment) OVER w AS prev_segment
    FROM customer_snapshots WINDOW w AS (PARTITION BY customer_id ORDER BY snapshot_date)),
starts AS (
    SELECT snapshot_date AS valid_from, customer_id, country, segment FROM changes
    WHERE prev_country IS NULL OR country <> prev_country OR segment <> prev_segment)
SELECT row_number() OVER (ORDER BY customer_id, valid_from) AS customer_key, customer_id, country, segment, valid_from,
       coalesce(date(lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from), '-1 day'), '9999-12-31') AS valid_to,
       lead(valid_from) OVER (PARTITION BY customer_id ORDER BY valid_from) IS NULL AS is_current
FROM starts""")

issues = dimension_issues()
if issues:
    raise ValueError(issues)

con.execute("""CREATE TABLE training AS
WITH base AS (
    SELECT o.order_id, o.customer_id, substr(o.ordered_at, 1, 10) AS prediction_date,
           EXISTS (SELECT 1 FROM orders n WHERE n.customer_id = o.customer_id AND n.ordered_at > o.ordered_at
                   AND n.ordered_at <= datetime(o.ordered_at, '+30 days')) AS reordered_30d
    FROM   orders o WHERE o.ordered_at < '2026-03-01'
)
SELECT b.order_id, b.reordered_30d, now.segment AS segment_now, asof.segment AS segment_asof
FROM   base b
JOIN   dim_customer now  ON now.customer_id = b.customer_id AND now.is_current
JOIN   dim_customer asof ON asof.customer_id = b.customer_id AND b.prediction_date BETWEEN asof.valid_from AND asof.valid_to""")
print(con.execute("""SELECT count(*), sum(segment_now <> segment_asof),
                     round(100.0 * sum(segment_now <> segment_asof) / count(*), 1) FROM training""").fetchone())
# (3320, 77, 2.3)
print(con.execute("SELECT segment_now, round(avg(reordered_30d), 3) FROM training GROUP BY 1 ORDER BY 1").fetchall())
# [('basic', 0.957), ('premium', 0.968)]
print(con.execute("SELECT segment_asof, round(avg(reordered_30d), 3) FROM training GROUP BY 1 ORDER BY 1").fetchall())
# [('basic', 0.958), ('premium', 0.968)]
expected_rows = con.execute("SELECT count(*) FROM orders WHERE ordered_at < '2026-03-01'").fetchone()[0]
actual_rows, unique_orders = con.execute("SELECT count(*), count(DISTINCT order_id) FROM training").fetchone()
if actual_rows != expected_rows or unique_orders != expected_rows:
    raise ValueError("Training must retain exactly one row per eligible order")

7. Write the data dictionary. Every table and column from exercises 2 and 3, in the format from the process section. Add it to docs/ in the project.