Dimensional Modeling and Schema Design Questions

Direct answer

Join each user's cohort-defining attribute (like acquisition source) from the Slowly Changing Dimension (SCD) Type 2 version of user_dim that was effective AT ACQUISITION TIME, not the current version, so a user's cohort assignment stays fixed even if their acquisition-source attribute is later corrected or reclassified, and compute retention by comparing distinct active users per month against each acquisition-month cohort.

Structured elaboration

• Why current-value cohort assignment is wrong: if a user's acquisition_source is later reclassified (a data-quality fix, or a business-rule change in how sources are categorized) and you use the CURRENT value to assign historical cohort membership, users can silently shift between cohorts in a re-run report, changing historical retention numbers for reasons unrelated to actual user behavior.
• As-of join for cohort assignment: JOIN user_dim d ON e.user_key = d.user_key AND e.acquisition_date BETWEEN d.effective_from AND d.effective_to, resolving each user's acquisition-time attribute value, consistent with the same as-of pattern used throughout SCD Type 2 dimensional joins.
• Retention computation: for each acquisition month cohort, count distinct users active in each subsequent month, divided by the cohort's total size, producing the month-0-through-6 retention curve.

Worked example

import pandas as pd
import numpy as np

# Synthetic events: user_id, acquisition_month, activity_month
np.random.seed(42)
users = pd.DataFrame({
    'user_id': range(1, 101),
    'acq_month': np.random.choice(['2026-01', '2026-02'], 100)
})
# Simulate each user's activity in months 0-3 with decaying probability
records = []
for _, u in users.iterrows():
    acq = pd.Period(u['acq_month'], freq='M')
    for m in range(4):
        retained_prob = 0.8 ** m  # known decay for verification
        if np.random.rand() < retained_prob:
            records.append({'user_id': u['user_id'], 'acq_month': u['acq_month'],
                             'activity_month': str(acq + m)})
events = pd.DataFrame(records)

cohort_size = users.groupby('acq_month')['user_id'].nunique()
events['month_offset'] = events.apply(
    lambda r: (pd.Period(r['activity_month'], freq='M') - pd.Period(r['acq_month'], freq='M')).n, axis=1)
retention = events.groupby(['acq_month', 'month_offset'])['user_id'].nunique().unstack()
retention_pct = retention.div(cohort_size, axis=0) * 100
print(retention_pct.round(1))

Running this (seeded, so reproducible) prints:

month_offset      0     1     2     3
acq_month
2026-01       100.0  90.9  56.8  50.0
2026-02       100.0  75.0  58.9  46.4

Month 0 is 100% for both cohorts by construction, since the decay formula's month-0 term is 0.8**0 = 1.0, guaranteeing every user gets an activity record in their own acquisition month. The later months trend downward for both cohorts (roughly toward, though noisier than, the target 0.8**m curve of 80%/64%/51%), which is expected sampling noise at this cohort size (44-56 users each) rather than a bug; the key confirmation is that the aggregation logic reconstructs a genuinely decaying curve from raw per-user event data, not an arbitrary, flat, or inverted one.

Trade-offs and pitfalls

The bug this exercise specifically probes is subtle: using the user's CURRENT acquisition_source (or any current dimension attribute) to bucket historical cohort membership, rather than the value that was true at acquisition time, silently reshuffles cohort membership whenever that attribute is corrected or reclassified later, without any error or obvious signal, only a retention curve that mysteriously shifts between report runs.

Direct answer

An accumulating snapshot fact table has one row per process instance (one order, one loan application), created when the process starts and updated in place as it passes through each milestone, with a column per milestone date and the current status. It's appropriate whenever you need to measure how long a multi-step process takes and where it stalls.

Structured elaboration

• Structure: order_fulfillment_fact(order_id, created_date_key, shipped_date_key, delivered_date_key, status, order_amount). created_date_key is set at insert time; shipped_date_key and delivered_date_key start NULL and get filled in as those milestones occur, via an UPDATE to the same row (not a new row per milestone).
• When it's appropriate: any well-defined, finite process with a small number of known milestones where you care about elapsed time between them, or about where instances currently sit in the pipeline.
• Reporting time-to-fulfillment: AVG(DATEDIFF(day, created_date_key, delivered_date_key)) WHERE status = 'delivered' computes average fulfillment time directly from one row per order, no joins across milestone events needed.
• Reporting funnel conversion: COUNT(*) WHERE shipped_date_key IS NOT NULL versus COUNT(*) WHERE created_date_key IS NOT NULL gives the order-to-ship conversion rate; the same pattern extended through each milestone column produces a full funnel.

Worked example

Order O500 is created on day 1 (row inserted with only created_date_key populated), ships on day 3 (the SAME row updated to fill shipped_date_key), and delivers on day 6 (the same row updated again). A single query now shows this order took 5 days end-to-end, without joining three separate milestone-event records together.

Trade-offs and pitfalls

The design assumes a bounded, well-known set of milestones; if the process can branch into many different paths or has an unbounded number of possible steps, an accumulating snapshot with one column per milestone doesn't scale and a transaction fact table of discrete milestone events (joined and pivoted at query time) is a better fit. Also remember this is the one fact-table type that violates insert-only append behavior, so extract, transform, load (ETL) and any downstream caching that assumes facts never change needs to explicitly account for these in-place updates.

Direct answer

Deriving a daily balance from transactional facts requires replaying every transaction for every account up to the requested date on every query, which gets slower as history accumulates and is easy to get subtly wrong (a single missed or duplicated transaction silently shifts every subsequent balance). A periodic snapshot fact table instead pre-computes and stores the balance once per account per day, so any query for "balance on date X" is a direct lookup.

Structured elaboration

• Why snapshot over derive: balance is a semi-additive measure, correct to look at on any single day but meaningless to SUM across days (summing 30 daily balances doesn't give you anything useful). Storing it directly at daily grain sidesteps both the replay-cost problem and the risk of a reporting query accidentally summing it incorrectly.
• Table structure: account_balance_fact(account_key, date_key, closing_balance), one row per account per day, generated by a nightly job that computes each account's end-of-day balance (from the transaction log, computed once, correctly, in the extract, transform, load (ETL) layer rather than repeatedly at query time).
• Load strategy: the nightly job typically computes each day's balance incrementally, today's balance = yesterday's balance + today's net transactions, rather than replaying full history every night, keeping the daily load cost proportional to one day of transactions, not the account's entire lifetime.
• Partitioning: partition by date_key so a query for a specific date range scans only the relevant partitions, and so the daily load only touches the newest partition.

Worked example

Account A100 has a balance of $500 on day 1. Day 2's net transactions are +$200, -$50. Rather than a dashboard replaying all transactions from account opening every time someone asks for the balance on day 2, the nightly job computes $500 + $200 - $50 = $650 once and stores it as one row: (A100, day2, $650). A query for "average balance over the last 30 days" is now AVG(closing_balance) over 30 stored rows, not a transaction replay.

Trade-offs and pitfalls

The storage cost is real: one row per account per day, every day, whether or not the balance changed, which can be a large table for millions of accounts over years. The alternative (storing a row only when the balance changes) saves storage but complicates every query, since you now need a "last known value as of date X" lookup instead of a direct match; for genuinely daily-cadence reporting needs, the simpler always-one-row-per-day design is usually worth the extra storage.

Direct answer

Store the raw event timestamp in UTC on the fact table (never convert at write time), and store the local business date/time as a SEPARATE derived attribute computed using the relevant time zone at query or extract, transform, load (ETL) time, keyed off the dimension that determines which time zone applies (the user's or store's location). Never overwrite or discard the original UTC timestamp.

Structured elaboration

• Why keep UTC as the source of truth: local time is a function of UTC time plus a time zone, and time zones (and their daylight-saving offsets) change; if you only store a converted local time and discard the UTC original, you lose the ability to correctly re-derive local time later if the zone or DST rules change, and you can't reliably compare events across different local time zones at all.
• Deriving local business time: join the fact (with its UTC timestamp) to a time-zone lookup (either on the relevant dimension, like store_dim.timezone, or a separate country_timezone table) and compute local time at query or ETL time: event_time_utc AT TIME ZONE store_dim.timezone. This correctly accounts for daylight-saving transitions if the underlying time zone conversion function is DST-aware (most modern SQL engines and time zone libraries are).
• Avoiding double-counting across date boundaries: aggregating "daily" metrics must group by LOCAL date (derived from local time), not the UTC date, or a single business day's activity gets split across two UTC dates near midnight local time, undercounting or overcounting depending on the time zone offset direction. Precompute a local_date_key attribute per event during ETL if this grouping happens frequently, to avoid repeating the conversion in every query.
• Materializing versus computing on demand: for high query volume against this pattern, materialize the local date/time as its own column at load time (computed once) rather than repeating the timezone conversion in every downstream query, which is both a performance optimization and a consistency guarantee (every query using the same precomputed local date, rather than each one potentially implementing the conversion slightly differently).

Worked example

An event occurs at 2026-03-08 06:30:00 UTC. For a user in New York (UTC-5 in early March, before the typical spring-forward DST transition), the local time is 2026-03-08 01:30:00, still March 8th locally. For a user in Tokyo (UTC+9), the same UTC timestamp is 2026-03-08 15:30:00, also March 8th locally, but a different clock time. If instead the event happened at 2026-03-08 03:00:00 UTC, the New York local time (UTC-5) is 2026-03-07 22:00:00, a DIFFERENT calendar date locally than the UTC date, exactly the kind of boundary case that breaks naive UTC-date-based daily aggregation for that user.

Trade-offs and pitfalls

The classic bug is grouping "daily active users" or similar metrics by the UTC date column directly, which systematically misattributes events near midnight to the wrong LOCAL day for every time zone except UTC itself, and does so silently (the query runs fine, the numbers just don't match what a local observer would report). Precomputing and testing the local-date derivation against known DST transition dates for major time zones is worth the ETL investment for any dashboard reporting daily metrics to a geographically distributed audience.

Direct answer

Build a factless fact table with one row per relevant event (a request submitted, an approval completed), and compute the service-level agreement (SLA) breach percentage by comparing, for each request, whether a matching approval event exists within 48 hours, using conditional aggregation rather than a stored numeric measure.

Structured elaboration

• Schema: sla_event_fact(request_id, event_type, event_time, request_date_key), factless, with event_type distinguishing 'REQUEST_SUBMITTED' from 'APPROVAL_COMPLETED'.
• Deriving the metric: for each request, find its submission time and its (possibly absent) approval time, compute the elapsed duration, and classify as breached if either no approval exists or it exceeds 48 hours.
• Handling requests with no approval yet: these should count as breaches (or be excluded, depending on the exact business definition) if the 48-hour window has already elapsed as of the reporting date; the query needs an explicit rule for "not yet approved but window not yet elapsed" versus "not yet approved and window has elapsed" (the latter is a breach in progress).

Worked example

WITH requests AS (
  SELECT request_id, event_time AS requested_at
  FROM sla_event_fact WHERE event_type = 'REQUEST_SUBMITTED'
),
approvals AS (
  SELECT request_id, MIN(event_time) AS approved_at
  FROM sla_event_fact WHERE event_type = 'APPROVAL_COMPLETED'
  GROUP BY request_id
)
SELECT
  DATE_TRUNC('month', r.requested_at) AS report_month,
  100.0 * SUM(
    CASE WHEN a.approved_at IS NULL AND CURRENT_TIMESTAMP > r.requested_at + INTERVAL '48 hours' THEN 1
         WHEN a.approved_at IS NOT NULL AND a.approved_at > r.requested_at + INTERVAL '48 hours' THEN 1
         ELSE 0 END
  ) / COUNT(*) AS breach_pct
FROM requests r
LEFT JOIN approvals a ON r.request_id = a.request_id
GROUP BY 1

For a month with 100 requests, if 8 either exceeded 48 hours to approval or remain unapproved past that window, this computes 8/100 = 8.0% breach rate, an exact figure derived directly from the counted rows, not a stored measure.

Trade-offs and pitfalls

Getting the "not yet approved" case wrong is the most common bug: excluding still-pending requests entirely from the denominator understates the breach rate (since a pending request that's already blown the 48-hour window IS a breach, and shouldn't be silently dropped), while including every pending request (even ones still within the window) as neither a breach nor a pass, without a clear rule, produces an inconsistent metric month to month as requests move from pending into either breach or on-time state.