Mapping Business Logic Into The Model

Why this matters

BI models power decisions. If business logic is unclear or poorly encoded, dashboards contradict each other, audits fail, and teams lose trust. Your job is to translate how the business counts things into consistent, testable data structures and metrics.

• Real tasks you will face: define Active Customer, compute Monthly Recurring Revenue (MRR), calculate churn, implement fiscal calendars, handle refunds and cancellations, enforce row-level security, and align marketing attribution windows.
• Goal: make business definitions explicit and reproducible in the model so every analyst, dashboard, and report agrees.

Concept explained simply

Mapping business logic means turning plain-language rules into unambiguous model definitions.

Key patterns you will reuse

• Time intelligence: rolling windows, fiscal calendars, cohort periods, as-of dates
• Status as-of logic: determine status at a date using effective_from/to ranges
• Slowly Changing Dimensions (SCD): keep history of changing attributes
• Many-to-many bridges: orders with multiple campaigns or products
• Allocations: split revenue across months, regions, or product bundles
• Currency conversion: choose rate (transaction-day, month-end) and apply consistently
• Row-level security flags: entitlements mapped to dimension keys
• Late-arriving facts: insert with event timestamps; avoid double counting

Worked examples

-- Dimensions/facts (simplified)
-- dim_subscription(id, customer_id, start_date, end_date, status, monthly_price)
-- dim_date(d, first_of_month)

-- Active subscriptions per day
active_subscriptions AS (
  SELECT d.d AS as_of_date, s.id AS subscription_id, s.customer_id, s.monthly_price
  FROM dim_date d
  JOIN dim_subscription s
    ON s.start_date <= d.d
   AND (s.end_date IS NULL OR d.d < s.end_date)
   AND s.status = 'active'
)

-- MRR at month start
mrr_by_month AS (
  SELECT dd.first_of_month AS month_start,
         SUM(a.monthly_price) AS mrr
  FROM active_subscriptions a
  JOIN dim_date dd ON dd.d = a.as_of_date
  WHERE dd.d = dd.first_of_month
  GROUP BY dd.first_of_month
)

-- Logo churn counts
logo_churn AS (
  SELECT DATE_TRUNC('month', end_date) AS month_start,
         COUNT(DISTINCT id) AS churned_subscriptions
  FROM dim_subscription
  WHERE end_date IS NOT NULL
  GROUP BY 1
)

-- sessions(user_id, session_id, session_ts, channel)
-- orders(user_id, order_id, order_ts, revenue)

WITH candidate AS (
  SELECT o.order_id,
         o.order_ts,
         s.session_id,
         s.channel,
         s.session_ts,
         ROW_NUMBER() OVER (
           PARTITION BY o.order_id ORDER BY s.session_ts ASC
         ) AS rn
  FROM orders o
  JOIN sessions s
    ON s.user_id = o.user_id
   AND s.session_ts BETWEEN o.order_ts - INTERVAL '7 days' AND o.order_ts
)
SELECT order_id, order_ts,
       session_id AS first_touch_session,
       channel AS first_touch_channel
FROM candidate
WHERE rn = 1;

-- movements(product_id, move_ts, qty_change)  -- receipts positive, shipments negative
-- dim_date(d)

SELECT d.d AS as_of_date,
       m.product_id,
       SUM(m.qty_change) FILTER (WHERE m.move_ts <= d.d) AS qty_on_hand
FROM dim_date d
JOIN movements m ON m.move_ts::date <= d.d
GROUP BY 1,2;

Who this is for

• BI Developers and Analytics Engineers defining semantic layers and metrics
• Data Analysts who need consistent, reusable definitions across reports
• Data-savvy PMs validating KPI definitions with data teams

Prerequisites

• Comfort with SQL joins, aggregations, window functions
• Basic data modeling concepts: facts, dimensions, grain
• Understanding of the business domain you are modeling (subscriptions, orders, etc.)

Learning path

1. Interview the business: capture KPI recipe cards (grain, filters, time logic, edge cases).
2. Choose model shapes: facts, dimensions, snapshots, and bridges.
3. Implement time logic: calendars, as-of joins, rolling windows.
4. Encode rules: statuses, inclusions/exclusions, currency, allocations.
5. Test: unit tests for edge cases; reconcile to known totals.
6. Document: plain-language definitions tied to model objects.

Exercises

Complete the tasks below, then check your work. The Quick Test at the end is available to everyone; only logged-in users get saved progress.

Checklist for your model

• [ ] Each metric has a clear grain and time basis (event vs effective)
• [ ] Inclusion/exclusion filters are explicit and testable
• [ ] Edge cases defined: refunds, cancellations, test data, currency
• [ ] One source of truth per metric (no duplicated logic across views)
• [ ] Dimensions are conformed across facts where needed
• [ ] As-of logic covered with date ranges or snapshots
• [ ] Unit/reconciliation tests in place for key KPIs

Common mistakes and how to self-check

• Mixing grains: aggregating daily metrics with monthly prices without aligning to a reference date. Self-check: validate row-grain before joining.
• Inconsistent time: using order_ts for revenue and refund_ts for refunds without alignment. Self-check: decide the month for both and document the choice.
• Implicit filters: silently excluding cancelled orders. Self-check: list all filters next to each metric definition.
• Double counting via many-to-many joins. Self-check: use bridges and distinct counts; test totals against source-of-truth reports.
• Ignoring late-arriving facts. Self-check: compare current-day totals over time to detect backfills.

Practical projects

• Subscription scorecard: active subs, MRR, churn, and expansion MRR with clear monthly reference points.
• Ecommerce funnel: sessions to orders with first-touch attribution and refund-adjusted net revenue.
• Operational SLA: ticket status durations using as-of snapshots and effective dating.

Mini challenge

Write a KPI recipe for "Repeat purchase rate in the last 180 days": define grain, inclusion rules, time window, and edge cases. Then sketch a short SQL snippet that counts customers with 2+ paid orders in the window divided by customers with 1+ paid orders.

Next steps

• Pair with a business stakeholder to validate two KPI recipes; adjust model until a manual sample reconciles.
• Add lightweight documentation inline with your model objects.
• Automate one reconciliation check so it runs daily.

Quick Test

Anyone can take the test. Only logged-in users will have progress saved.