Apple Business Intelligence Analyst (Mid-Level) Interview Preparation Guide 2026

Direct answer. Pre-aggregate the "many" side down to one row per key BEFORE joining it to the fact table, or deduplicate it down to one canonical row per key using an explicit tie-breaking rule; either one fixes the duplication at its source, whereas slapping DISTINCT on the final result only hides symptoms and can silently drop legitimately different rows.

Structured elaboration. The two real fixes and the one fake fix:

• Pre-aggregate before joining: if you need SUM(orders.total) per customer and orders is being joined through a one-to-many table like order_items, compute the per-order (or per-customer) aggregate in a subquery or CTE first, and join the already-aggregated result. The join can no longer multiply anything because the thing you're joining is already at the right grain.
• Deduplicate the many side with a canonical-row rule: when what you actually want is ONE representative row per key (the latest address, the primary email), pick a specific tie-breaking rule (most recent by timestamp, a boolean is_primary flag, or ROW_NUMBER() partitioned by the key) and join against only that one row per key.
• DISTINCT on the final result (the band-aid): this looks like it fixes the symptom because the visible duplicate ROWS disappear, but it operates on the wrong thing. If two DIFFERENT orders happen to produce an identical row on the columns you selected, DISTINCT silently merges them too, and you've now lost real data rather than fixed a join. It also does nothing to fix an aggregate computed from the pre-DISTINCT, already-inflated rows.

Worked example. customers(1,'Alice'). orders(100, customer_id=1, total=50), (101, customer_id=1, total=30). order_items has two rows for order 100 and one for order 101 (three item rows total).

-- naive (inflated): joining orders to order_items before aggregating double-counts order 100
SELECT c.customer_id, SUM(o.total) AS inflated_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id;
-- returns (1, 130.00): order 100's 50 got summed twice (once per item)

-- fixed: aggregate orders on their own; never join order_items for this metric at all
SELECT c.customer_id, SUM(o.total) AS correct_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
-- returns (1, 80.00), the correct sum of order totals (50 + 30)

The fix here is really "don't join the many-side table at all if this metric doesn't need it," which is the pre-aggregation principle taken to its logical conclusion: join only what the specific metric actually requires.

Trade-offs and pitfalls. Pre-aggregation is the more scalable fix (the join now operates on a table that's already the right size) but requires you to correctly identify every place a metric is computed and make sure the aggregation grain matches; get the grain wrong and you've traded a visible bug for a subtle one. The canonical-row approach needs an explicit, deterministic tie-breaking rule, an ambiguous one (pick "any" row when several are equally recent) produces answers that vary between runs. Treat any DISTINCT you find sitting after a multi-table join as a signal to go find out WHY duplicates existed in the first place, not as the fix itself.

Requirements & constraints:

• Enforce consistent row-level security (RLS) across Tableau, Power BI, Looker and direct SQL queries.
• Low latency for dashboard queries, centralized policy authoring, auditability, gradual migration from existing tool-native RLS.

High-level architecture:

• Policy Service (central policy engine): stores RLS rules (role -> predicates, attribute-based rules, dynamic filters), exposes REST + gRPC and a policy decision point (PDP).
• Attribute Provider / Claims Service: resolves user attributes (groups, org, region, manager, ABAC attributes) from IdP / HR / LDAP / SCIM and emits signed short-lived claims/tokens.
• Token Exchange Gateway: exchanges IdP token for a signed Policy Token (JWT or MAC token) containing evaluated, minimal RLS claims (e.g., allowed org_ids, regions, predicate references). TTL short (1–5m).
• Connector/Proxy / SDKs: lightweight client libraries and connectors for each BI tool and database that validate policy tokens, fetch policies, and inject RLS predicates into SQL or API queries.
• Audit & Monitoring: log decisions, token issuance, policy changes, and usage metrics.

Dataflow:

1. User authenticates with IdP; BI tool obtains IdP token.
2. BI tool calls Token Exchange Gateway with IdP token.
3. Token Exchange calls Attribute Provider → Policy Service to evaluate and produce policy token with encoded RLS predicates or IDs.
4. BI connector attaches policy token to each query or connection. Connector either:
   • Injects WHERE clauses (predicate pushdown) into generated SQL; or
   • Uses database-side policy enforcement via session variables or delegated policy checks (for systems like Snowflake or Redshift using external functions).
5. PDP logs decision and returns metrics.

How tools consume policies:

• Native connector plugins (Tableau/Power BI/Looker): use SDK to request policy tokens at session start and refresh periodically; driver rewrites queries adding safe predicate fragments or sets session context (e.g., SET myapp.allowed_orgs = '1,2').
• For tools that don’t allow rewriting, use a proxy DB endpoint that applies predicate filtering before forwarding queries.

Synchronization & consistency:

• Single source of truth in Policy Service, versioned policies with immutable IDs; connectors cache policy metadata keyed by policy-version and refresh on TTL or webhook push.
• Webhooks/Message bus (Kafka) push policy-change events to connectors to invalidate caches.
• Policy tokens include policy-version to detect stale tokens; PDP can reject tokens when policy revoked.

Scalability & performance:

• PDP stateless; scale with replicas behind load balancer. Cache evaluated attribute sets in Attribute Provider using Redis with short TTL.
• Use precomputed materialized ACL tables for very large cardinalities and join predicates instead of long IN lists.
• For high QPS dashboards, use query-level caching and avoid per-row enforcement by reducing predicate complexity and pushing predicates to the data warehouse where they can use indexes/partitions.
• Rate limiting and batching token issuance for concurrent users.

Security & correctness:

• Tokens signed and short-lived. Validate integrity in connectors.
• Sanity-check predicate generation to avoid SQL injection (use parameterized injection).
• Fall-back fail-closed: if policy cannot be retrieved, deny or return empty result depending on contract.

Migration strategy from tool-native RLS:

1. Inventory: catalog existing RLS rules per tool and map to canonical policy model.
2. Pilot: choose low-risk dashboards and implement centralized enforcement using proxy/connector, run centralized enforcement in parallel (mirror mode) and compare results.
3. Dual-write: for a period enforce both native and central; log any discrepancies and resolve edge cases.
4. Gradually cutover by workspace/team: enable central tokens as primary and disable native RLS once validated.
5. Clean-up: remove tool-native configurations and continue monitoring/auditing.

Trade-offs:

• Predicate injection is simplest but requires careful SQL-safe mechanisms; DB session context is cleaner but requires DB support.
• Short-lived tokens add load but improve security; precomputed ACLs increase storage but improve query performance.

This design gives BI analysts a single place to author and audit RLS, consistent results across tools, safe rollouts, and options to optimize for scale and warehouse capabilities.

Direct answer: Once you allow reactivation, "retained in week W" has to mean "had at least one event in week W," checked independently for every week, rather than "still inside one continuous active streak." The counting itself is simple: COUNT(DISTINCT user_id) per (cohort_week, week) never double-counts, because a user either had an event that week or didn't, regardless of how many separate active stretches they've had. Where overlapping intervals actually cause trouble is in any metric that isn't a plain per-week yes/no flag, like a session count or average active-streak length; those need the user's raw active days coalesced into non-overlapping intervals first, or a two-events-in-the-same-week pattern can inflate a total that a simple DISTINCT count never would.

Structured elaboration

The core, correct query needs no interval logic at all:

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('week', MIN(event_date)) AS cohort_week
  FROM events GROUP BY user_id
),
activity_weeks AS (
  SELECT DISTINCT user_id, DATE_TRUNC('week', event_date) AS active_week
  FROM events
),
flagged AS (
  SELECT c.user_id, c.cohort_week,
         DATE_DIFF('week', c.cohort_week, a.active_week) AS week_offset
  FROM cohort c JOIN activity_weeks a ON a.user_id = c.user_id
)
SELECT cohort_week, week_offset, COUNT(DISTINCT user_id) AS retained_users
FROM flagged
GROUP BY cohort_week, week_offset
ORDER BY cohort_week, week_offset;

activity_weeks is already DISTINCT (user_id, active_week), so a user active on three separate days inside the same week only produces one row for that week, and COUNT(DISTINCT user_id) in the final aggregation is a second safety net even if that dedup step were skipped. A user who churns after week_offset 1 and comes back at week_offset 5 simply produces two rows in flagged: one at offset 1, one at offset 5, both counted independently, which is exactly "retained again on return, no double count."

Where overlapping intervals genuinely matter: the moment the question moves from "was this user active in week W" to any statistic that involves the span or count of active stretches, e.g. average session length, number of distinct engagement streaks per cohort, or a churn definition based on N consecutive inactive periods. Those require turning raw active days into non-overlapping intervals first (the classic gaps-and-islands pattern), because summing or averaging over raw, unmerged, possibly-overlapping-or-adjacent day ranges double-counts the days where two source intervals touch:

WITH ordered AS (
  SELECT user_id, event_date,
         LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_date
  FROM (SELECT DISTINCT user_id, event_date FROM events) d
),
flagged AS (
  SELECT user_id, event_date,
         SUM(CASE WHEN prev_date IS NULL OR event_date - prev_date > 14 THEN 1 ELSE 0 END)
           OVER (PARTITION BY user_id ORDER BY event_date) AS session_id
  FROM ordered
)
SELECT user_id, session_id, MIN(event_date) AS session_start, MAX(event_date) AS session_end
FROM flagged
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

This assigns a new session_id every time the gap since the previous active day exceeds a business-defined threshold (14 days here), then collapses each session to its start/end. That gives you clean, non-overlapping intervals to compute session-level statistics from, without touching the weekly retention flag at all: the two queries answer different questions and don't need to be merged into one pipeline.

Worked example (executed in DuckDB)

Two users, cohort_week = the week containing 2026-01-05. User 1 is active in week_offset 0 (2026-01-05), then goes quiet, then reactivates in week_offset 5 (2026-02-09). User 2 is active continuously in week_offsets 0, 1, 2, then stops. Running the retention query gives retained_users of 2 at offset 0, 1 at offset 1, 1 at offset 2, and 1 at offset 5 (user 1 alone, reappearing), with no row at offsets 3 or 4. Each (cohort_week, week_offset) pair appears exactly once per qualifying user, confirming no double count on the reactivation. Running the sessionization query separately on the same data produces two sessions for user 1 (2026-01-05 to 2026-01-10, then a second session starting 2026-02-09, since the gap exceeds 14 days) and one continuous session for user 2 (2026-01-05 to 2026-01-19, since consecutive active weeks are within the 14-day threshold of each other).

Trade-offs & pitfalls

• The temptation to build a heavier gaps-and-islands-then-coalesce pipeline for the basic retention matrix is a real trap: it adds real complexity (and, if the coalescing step is written carelessly, real bugs) for a guarantee that COUNT(DISTINCT user_id) already provides for a simple per-week flag.
• The gap threshold used to define a "session" (14 days, 30 days, or something else) is a business decision, not a technical one, and changes what counts as one continuous engagement versus two separate reactivations; state it explicitly rather than hardcoding it silently.
• If cohort assignment itself uses each user's first event, a user's cohort never moves due to reactivation; only their weekly retention flags do. Conflating "cohort" with "current active streak" is a common source of retroactively-shifting cohort bugs.
• Time zone and week-boundary definitions (Monday-start vs Sunday-start weeks) change which week a borderline event lands in; keep the boundary consistent between the cohort assignment and the activity-week bucketing, or reactivation timing can appear off by one week.
• The sessionization query above uses an iterative window-function scan (LAG plus a running SUM); a recursive CTE (one that refers to itself to walk forward day by day or session by session) is an alternative way to express the same gap-and-island merge, and can be easier to reason about if the merge rule itself is more complex than a fixed day threshold, at the cost of the recursion's own overhead versus a single windowed pass.

Direct answer
Most BI organizations structure dashboards into three tiers: strategic/executive (a handful of high-level KPIs, monthly/quarterly cadence, informs long-term decisions), tactical/manager (departmental metrics at weekly cadence, informs resource allocation and course-correction), and operational/exploratory (granular, near-real-time or daily metrics, informs day-to-day action), with drilldowns connecting each tier to the next so an executive can trace a summary number down to raw events.

Structured elaboration

• Strategic/executive tier: e.g. total revenue, refund rate, AOV for an e-commerce business; monthly cadence; enables decisions like budget reallocation or strategic pivots.
• Tactical/manager tier: e.g. traffic, cart-conversion, and revenue by channel; weekly cadence; enables decisions like reallocating marketing spend between channels.
• Operational/exploratory tier: e.g. real-time cart-conversion by session, refund-rate by SKU, and AOV by promotion code; daily or intraday cadence; enables decisions like investigating a specific incident or campaign performance.
• Drilldown paths: a click on the strategic tier's "revenue" tile should lead to the tactical tier's channel or product breakdown, which in turn should let an analyst drill to the operational tier's raw transaction or session-level data, preserving filter context (date range, segment) at every step.

Worked example
An executive sees "refund rate 3.2%, up from 2.8%" (strategic); drills to the tactical view and sees the increase is concentrated in one product category; drills further to the operational view and sees a spike in refunds tied to a specific SKU's recent quality issue.

Trade-offs and pitfalls
Building true drilldown continuity across all three tiers is real engineering work (consistent dimensions, preserved filter state); many organizations end up with three disconnected dashboards instead, which forces manual cross-referencing and slows root-cause investigation.

Direct answer
Supporting multi-region or multi-currency reporting from one data model means keeping the raw, unconverted truth at the transactional level and computing consolidated views on top of it, rather than baking a single currency or region's assumptions into how the data is stored in the first place; that way local teams get their own numbers correctly and leadership gets one consistent consolidated view from the same underlying source.

Structured elaboration
What to store versus compute: store the transaction in its original, raw currency and locale (never convert at write time, since that destroys the ability to recompute correctly later or audit against the original). Compute the converted, consolidated value (e.g. company-currency revenue) as a derived layer on top, using an explicit exchange-rate table with a clear rule for which rate applies (the rate as of the transaction date is the common default, though some financial reporting conventions use a period-end or period-average rate instead, and the choice has to be a deliberate, documented decision, not an accident of whatever rate happened to be cached).

Handling corrections: when an exchange rate gets corrected after the fact (a data provider issues a restated rate), the model needs a defined backfill process: recompute the converted values for the affected period using the corrected rate, and be able to show both the originally-reported and the corrected figure if financial reporting requires an audit trail of what changed and why.

Delivering region-specific dashboards without maintaining N copies: rather than building a separate dashboard per region, use parameterization (one dashboard definition, region selected as a filter/parameter) or a templated approach where region-specific formatting and a region's particular KPI (key performance indicator) variant are driven by configuration, not by forking the dashboard file. This keeps maintenance centralized: a fix to the underlying logic applies everywhere at once instead of needing to be manually propagated across a dozen regional copies.

Testing and deploying regional variants: because each region can have a genuinely different KPI definition (a legally mandated revenue-recognition rule that differs by jurisdiction, for instance), changes need to be tested against each region's specific rule, not just the default, and a deployment needs a way to confirm a change to the shared template didn't break a region-specific override.

Worked example
A transaction table stores amount: 1200, currency: 'EUR', transaction_date: '2026-01-09'. A separate exchange_rates table stores daily rates per currency pair. The consolidated revenue view joins the transaction to the exchange rate as of the transaction date (not today's rate), computing amount_usd = amount * rate, so a transaction from January is always converted at January's rate even if queried in March, giving a stable historical figure. When the data provider issues a corrected EUR/USD rate for January 9th two weeks later, a backfill job recomputes amount_usd for every transaction on that date using the corrected rate, and the change is logged (old value, new value, reason) for audit purposes rather than silently overwritten. Meanwhile, the same underlying dashboard template serves all 12 regions: each region's view is the identical dashboard definition with region as a parameter, automatically applying that region's currency formatting and, where legally required, that region's specific revenue-recognition rule via a lookup table rather than a hand-forked copy of the dashboard.

Trade-offs and pitfalls
Storing raw currency and computing conversions on the fly is more correct but costs more at query time than pre-converting and storing a single currency, so at very high query volumes some teams pre-materialize the converted values on a schedule (accepting a small staleness window) rather than converting live on every query; that's a legitimate trade-off as long as it's a deliberate choice, not an accident that quietly makes historical figures wrong when rates get corrected. Parameterized regional dashboards also break down when a region's requirements diverge too far from the common template (a genuinely different report structure, not just different formatting or a different rate rule); at that point, forcing it into the shared template creates a worse maintenance problem than a deliberate, clearly-labeled regional fork would, so the parameterization approach needs a real limit, not an assumption that it scales to any amount of regional divergence.

I would create a PDT that aggregates customers into cohorts by signup_month and first_purchase_month so downstream explores can join to it efficiently. Key LookML settings: sql_table_name (defines the physical table name), datagroup_trigger (controls rebuild when source data changes), and persist_for (time-to-live for the persisted table).

Example LookML PDT:

view: customer_cohorts_pdt {
  derived_table: {
    # physical table name in the DB
    sql_table_name: analytics.customer_cohorts_pdt ;;

    # SQL to compute cohorts: signup_month and first_purchase_month per customer
    sql: SELECT
           customer_id,
           DATE_TRUNC('month', MIN(signup_date))    AS signup_month,
           DATE_TRUNC('month', MIN(first_order_date)) AS first_purchase_month,
           COUNT(order_id) AS total_orders_first_month
         FROM raw.orders_and_customers
         GROUP BY 1,2,3 ;;
    
    # Persist for 24 hours unless datagroup triggers earlier rebuild
    persist_for: "24 hours"

    # Optional: rebuild when the source datagroup indicates new data
    datagroup_trigger: source_db.daily_events
  }

  dimension: customer_id { type: string sql: ${TABLE}.customer_id ;; }
  dimension: signup_month { type: date sql: ${TABLE}.signup_month ;; }
  dimension: first_purchase_month { type: date sql: ${TABLE}.first_purchase_month ;; }
  measure: total_orders_first_month { type: number sql: ${TABLE}.total_orders_first_month ;; }
}

Why these properties:

• sql_table_name: Ensures the PDT materializes to a predictable schema/place (useful for grants, DBA visibility, and manual inspection).
• datagroup_trigger: Links PDT rebuilds to upstream ingestion jobs (datagroups capture freshness of source tables). Use this to rebuild immediately when source data updates.
• persist_for: Sets a TTL so Looker will keep the table for that duration and then drop/recreate on demand—useful as a safety net if datagroups miss changes.

When to persist PDT vs ephemeral:

• Persist PDT when the query is expensive, used by many users/reports, or when joins benefit from pre-aggregation (reduces compute and latency). Also when you need scheduleable freshness and consistent snapshots for downstream analytics.
• Use ephemeral derived tables when the computation is cheap, dataset is small, or you need always-up-to-the-second results and want to avoid DB storage/maintenance. Ephemeral is good for one-off joins inside a single explore where materializing would be unnecessary overhead.

Best practices:

• Keep SQL in the PDT simple and push heavy work to the warehouse.
• Use datagroups tied to ETL schedules to avoid stale cohorts.
• Monitor build times and storage; tune persist_for accordingly (shorter for high-frequency data, longer for stable historical cohorts).

Direct answer

CUPED (Controlled-experiment Using Pre-Experiment Data) is a variance-reduction technique, published by Microsoft researchers (Deng, Xu, Kohavi and Walker, 2013), that uses a metric measured before the experiment started to strip out some of the between-user noise in the outcome metric, without touching the unbiasedness of the treatment-effect estimate. It requires one thing: a pre-experiment value of a covariate, ideally the same metric you are about to test, measured for the same units, from a period before assignment happened (so it cannot itself have been affected by treatment). The payoff is a tighter estimate of the same treatment effect from the same traffic, which functionally means you can detect the same effect size with less traffic or in less time.

Structured elaboration

What it requires and assumes

• A pre-experiment covariate $X$ for every unit, measured strictly before random assignment (so treatment cannot have influenced it) and ideally correlated with the outcome $Y$ you're testing.
• The scaling coefficient must be estimated without using the treatment label, typically pooled across both arms or from control alone. This is what keeps the adjustment from injecting bias: you are not letting "knowledge of which arm someone is in" leak into how you weight their pre-period value.

How the adjustment is computed, in plain terms

For each unit, adjust its outcome by subtracting a scaled version of how far its pre-experiment covariate was from the population average:

$Y_i^{\text{CUPED}} = Y_i - \theta \left(X_i - \bar{X}\right)$

where the scaling coefficient is the population covariance of $X$ and $Y$ divided by the variance of $X$:

$\theta = \frac{\operatorname{Cov}(X, Y)}{\operatorname{Var}(X)}$

Intuitively: if a user's pre-period activity was above average, some of their post-period activity being above average too is "expected" and not informative about treatment, so it gets subtracted out. The treatment effect is then estimated as the difference in adjusted outcome means between arms, $\bar{Y}^{\text{CUPED}}_{\text{treat}} - \bar{Y}^{\text{CUPED}}_{\text{control}}$, which is algebraically the same as the raw mean difference minus $\theta$ times the pre-period mean difference between arms; since randomization makes that pre-period difference expected to be zero, the adjustment does not shift the expected value of the estimate, it only removes the part of each unit's variance that $X$ already explained.

The resulting variance reduction has a clean form: if $\rho$ is the correlation between the pre-experiment covariate and the outcome, the adjusted estimator's variance is $(1-\rho^2)$ times the unadjusted variance. A stronger pre-period predictor buys a bigger reduction; a weak one buys almost nothing.

What makes a covariate good or poor

What goes wrong with a poor choice

• Using a post-treatment or treatment-adjacent value as $X$ breaks the "cannot be affected by treatment" requirement and reintroduces bias: you would be conditioning on something treatment itself moved, which is a different and more serious problem than losing precision.
• Using a weakly correlated covariate costs almost nothing in bias but also buys almost nothing in variance reduction; the $(1-\rho^2)$ factor is close to 1.
• Using a covariate that is missing for a large share of units (commonly new users with no pre-period history) forces a choice: drop those units (losing sample and possibly biasing toward existing users) or impute a value (introducing a modeling assumption you did not need before).

Worked example

Suppose, for illustration, we assume a pre-experiment 7-day conversion rate correlates with the in-experiment conversion rate at $\rho = 0.6$ for a given product (a hypothetical, stated input, not an empirical claim about any real product). The variance reduction from CUPED is then derived directly:

$\frac{\operatorname{Var}(Y^{\text{CUPED}})}{\operatorname{Var}(Y)} = 1 - \rho^2 = 1 - 0.6^2 = 1 - 0.36 = 0.64$

So the adjusted estimator's variance is 64% of the unadjusted one, a 36% reduction, given only the stated assumption $\rho = 0.6$. If $\rho$ had instead been a weak 0.2, the reduction would be $1 - 0.04 = 0.96$, i.e., only a 4% cut, illustrating why the strength of the pre-period correlation, not just its existence, determines whether CUPED is worth implementing for a given metric.

Trade-offs and pitfalls

• CUPED does not help metrics with no meaningful pre-experiment history, most obviously new-user-only experiments; for those, look at stratification or other covariate-adjustment techniques instead.
• Estimating $\theta$ using data that has already been split by arm (rather than pooled or control-only) risks contaminating the coefficient with treatment information; keep the estimation step blind to arm assignment.
• CUPED reduces variance, it does not change what the true effect is; do not present the tighter confidence interval as evidence the effect itself grew, only that you can now see it more precisely.
• CUPED composes with stratified randomization (apply the adjustment within strata), which is a reason to know both rather than treat them as competing choices.

Situation: You need fast queries over the most recent 90 days from a 3B-row Postgres transactions table, plus occasional audit queries over older data. Goal: maximize hot-path performance, enable cheap archive/restore, and keep maintenance manageable.

Proposal (partition key & size)

• Partition by transaction_date (range) on DATE/TIMESTAMP column.
• Hybrid granularity:
  • Recent “hot” window: daily partitions for the last 90 days (excellent partition pruning for frequent queries).
  • Older “cold” window: monthly partitions for data older than 90 days (reduces number of partitions and metadata overhead).

Maintenance / merge approach

• Automate partition lifecycle with pg_partman or custom cron job:
  • Create daily partitions ahead of time (e.g., 30 days).
  • Daily job: create next day’s partition; run ANALYZE on new partitions.
  • Weekly job: for partitions moving beyond 90 days, MERGE/detach rising daily partitions into the corresponding monthly partition (create monthly if not exists) by:
    1. CREATE monthly partition for month M (range).
    2. INSERT INTO monthly_partition SELECT * FROM daily_partition WHERE ... (or use ALTER TABLE ... ATTACH if empty).
    3. DROP/DETACH daily partition.
  • Keep partition counts bounded (≈90 daily + ~60 monthly for 5 years = manageable).
• Indexing: local indexes per partition on commonly filtered columns (user_id, status). Avoid global indexes.
• VACUUM/ANALYZE per partition to maintain stats; use parallel workers for hot partitions.

Retention & Archiving

• When monthly partitions exceed retention (e.g., 3 years keep online, 2 years archive), detach whole monthly partitions and:
  • Option A (cold store): COPY TO Parquet and push to S3; drop local partition.
  • Option B (archive DB): ATTACH to an archive DB via FDW or physically move via pg_dump/pg_restore.
• For audit queries, query archive via FDW or Presto/Trino over S3. Restoring: reimport whole monthly partition.

Migration plan (existing 5 years -> new scheme)

1. Create new partitioned parent table with same schema and constraints.
2. Create partitions for monthly range covering entire 5-year span and daily partitions for recent 90 days.
3. Backfill in date-ordered batches (parallel workers):
   • Use COPY or INSERT ... SELECT in chunks by date range to populate partitions (e.g., load month-by-month).
   • Disable/skip nonessential indexes during bulk load, recreate afterwards per partition.
   • Use UNLOGGED or adjust WAL settings for bulk import where safe, but ensure durability for production.
4. Validate counts and checksums per month/day between old and new tables.
5. Swap application reads to partitioned table (rename, using transactional ALTER TABLE if needed).
6. After cutover, start lifecycle automation to merge daily→monthly and archive beyond retention.

Reasoning / Benefits

• Range partitioning by date enables partition pruning: queries limited to last 90 days touch only ~90 small partitions, reducing IO and improving planner choices.
• Daily partitions keep hot data small and easy to VACUUM; monthly merge reduces partition bloat for long-term data.
• Detaching whole partitions makes archiving and restores efficient (no row-by-row deletes).
• Automated merges/archives keep operational overhead low and predictable.

Edge considerations

• Avoid too many partitions (Postgres performance); merge old ones.
• Monitor planner statistics; run ANALYZE after operations.
• Consider retention SLAs and audit latency when choosing archive storage and access method.
• Test migration on staging with representative load and query patterns.

Direct answer

Don't treat it as narrative versus dashboard, give both, but change which one is the default view. Lead with a short narrative stating the decision-relevant takeaway, and keep the full dashboard one click or one slide away so the stakeholder can self-serve or verify a number without you gatekeeping the underlying data.

Structured elaboration

• Diagnose why they want the full dashboard. It could be distrust of your synthesis, a habit from before narrative summaries existed, or a genuine need to slice the data themselves.
• If it's distrust, that is a relationship problem to fix with a track record of accurate synthesis over time, not a formatting problem you can solve in one deliverable.
• If it's a genuine self-serve need, make the narrative the front page and link the dashboard as the appendix rather than dropping the narrative altogether.

Worked example

A BI analyst's sponsor insists on seeing the full 40-metric dashboard every week. The analyst starts sending a 3-bullet narrative email ("signups up 8% week over week, driven by the paid channel; retention flat; one metric to watch, refund rate ticked up half a point") with the dashboard link at the bottom. After a month the sponsor mostly replies to the narrative and stops opening the dashboard link most weeks, but keeps it available for the weeks they want to check a specific number themselves.

Trade-offs and pitfalls

Removing the detailed view entirely risks looking like you're hiding something. Always leading with the raw dashboard loses your chance to frame the interpretation, and the stakeholder may draw their own, sometimes wrong, conclusions from the unframed numbers.

What the interviewer probes next

Expect a follow-up on what you'd do if a stakeholder explicitly says they don't trust your narrative and want to see everything raw, every time.

Direct answer

No single attribution model is right for every use case, so the actual deliverable is a decision: heuristic models (linear, time-decay) are cheap, transparent, and good enough for a quarterly report a non-technical stakeholder needs to trust at a glance; regression-based and Shapley-value models are data-driven and defensible under scrutiny but expensive to build and maintain, appropriate when real budget decisions ride on channel comparisons; and Markov chain modeling sits in between, data-driven and computationally tractable enough to refresh frequently, which makes it the strongest candidate for near-real-time optimization despite giving up some of Shapley's theoretical guarantees.

Structured elaboration

Recommendation. For a quarterly marketing report, where the audience needs to trust the number quickly and the cost of a slightly less statistically rigorous credit split is low, a heuristic model (time-decay, since it at least reflects recency better than pure linear credit) is the right default: it is fast to produce, easy to explain and defend in the room, and does not require maintaining a fitted model across reporting periods. For real-time bidding or budget-reallocation optimization, where the credit split directly drives a repeated, automated decision and the cost of a systematically biased heuristic compounds every cycle, the Markov chain approach is the strongest fit: it is genuinely data-driven (unlike the heuristic), fast enough to refresh on the cadence an optimization loop needs (unlike Shapley at meaningful channel-set sizes), and its removal-effect credit responds to real shifts in user behavior automatically as the transition matrix is re-estimated. Regression-based and Shapley-value models are best reserved for a periodic, deeper validation exercise, run quarterly or less often, to check whether the operational Markov-based credit split still roughly agrees with a more rigorous method, rather than as the always-on production system themselves.

Markov chain attribution, in depth

The model. Represent every distinct marketing channel as a state, plus a Start state and two absorbing states, Conversion and Null (drop-off without converting). Estimate the transition matrix $P$ directly from observed touchpoint sequences: $P_{ij}$ is the empirical fraction of times a user at state $i$ moved next to state $j$.

Absorption probability via the fundamental matrix. Partition $P$ into the transient states (Start plus every channel) and the two absorbing states, giving the standard block form

where $Q$ is the transient-to-transient sub-matrix and $R$ is the transient-to-absorbing sub-matrix. The fundamental matrix

gives, entry by entry, the expected number of visits to each transient state before absorption, and

gives, for every transient state, the probability of eventually being absorbed at Conversion versus Null. The overall baseline conversion probability is $B$'s entry for the Start state and the Conversion column.

Removal effect: turning absorption probability into channel credit. To credit channel $c$, rebuild the transition matrix with every transition that would have gone INTO $c$ redirected instead to Null (the touch simply does not happen, so that user falls out of the funnel at that point), recompute the Start-state conversion probability under this modified chain, and take the difference from the baseline. Formally, if $p^*$ is the baseline conversion probability and $p^*_{-c}$ is the conversion probability with channel $c$ removed, channel $c$'s removal effect is $p^* - p^*_{-c}$. Normalizing every channel's removal effect to sum to 1 and multiplying by the total observed conversions gives each channel's credited conversion count, which is the standard way this method is applied in practice.

Sparse-channel handling. A channel with very few observed touches produces an unstable transition-probability estimate (a handful of paths through it can push its outgoing probabilities to extreme, noisy values), and removing it can produce an unrealistically large or small removal effect purely from sampling noise. Two practical fixes: apply additive (Laplace-style) smoothing to the raw transition counts before normalizing into probabilities, which pulls a sparse channel's estimated transitions toward the overall average rather than trusting a handful of observations completely, or group genuinely low-volume channels into a single "Other" state before estimating the chain at all, sacrificing per-channel granularity for a materially more stable estimate on the channels that matter.

Algorithmic complexity. Building the transition-count matrix is a single linear pass over the touchpoint data, $O(\text{total touches})$. The fundamental-matrix computation is a $k \times k$ matrix inversion, $O(k^3)$ with standard dense linear algebra, where $k$ is the number of transient states (channels plus Start); computing the removal effect for every channel means repeating this inversion once per channel, so the full removal-effect attribution run is $O(k^4)$ in the worst case. This stays entirely practical for a realistic channel count (tens of channels, not thousands), which is exactly why the Markov approach can be refreshed far more often than a Shapley computation whose cost grows exponentially in the same $k$.

Worked example

Ten pinned synthetic user paths across three channels (Search, Social, Email), actually computed with numpy linear algebra rather than hand-derived:

import numpy as np

paths = [
    (["Search", "Social"], "Conversion"), (["Search"], "Conversion"),
    (["Social", "Email"], "Conversion"), (["Email"], "Conversion"),
    (["Search", "Social"], "Null"), (["Social"], "Null"),
    (["Search", "Email"], "Conversion"), (["Email", "Social"], "Conversion"),
    (["Search"], "Null"), (["Email"], "Null"),
]
STATES = ["Start", "Search", "Social", "Email", "Conversion", "Null"]
idx = {s: i for i, s in enumerate(STATES)}
n = len(STATES)

counts = np.zeros((n, n))
for seq, outcome in paths:
    prev = "Start"
    for ch in seq:
        counts[idx[prev], idx[ch]] += 1
        prev = ch
    counts[idx[prev], idx[outcome]] += 1

P = counts.copy()
row_sums = P.sum(axis=1, keepdims=True)
for i in range(n):
    P[i, i] = 1.0 if row_sums[i] == 0 else P[i, i]
    if row_sums[i] > 0:
        P[i] = P[i] / row_sums[i]

def conversion_probability(P):
    transient = [i for i, s in enumerate(STATES) if s not in ("Conversion", "Null")]
    absorbing = [idx["Conversion"], idx["Null"]]
    Q = P[np.ix_(transient, transient)]
    R = P[np.ix_(transient, absorbing)]
    N = np.linalg.inv(np.eye(len(transient)) - Q)
    B = N @ R
    return B[transient.index(idx["Start"]), 0]

baseline = conversion_probability(P)
print(f"Baseline P(eventually Convert | Start) = {baseline:.4f}")

def remove_channel(P, channel):
    P2 = P.copy()
    c = idx[channel]
    for i in range(n):
        if i != c and P2[i, c] > 0:
            P2[i, idx["Null"]] += P2[i, c]
            P2[i, c] = 0.0
    return P2

removal_effects = {}
for channel in ["Search", "Social", "Email"]:
    p_removed = conversion_probability(remove_channel(P, channel))
    effect = baseline - p_removed
    removal_effects[channel] = effect
    print(f"Remove {channel}: P(Convert) drops to {p_removed:.4f}, removal effect = {effect:.4f}")

total_conversions = sum(1 for _, outcome in paths if outcome == "Conversion")
sum_effects = sum(removal_effects.values())
print()
print("Normalized channel credit (removal effect / sum of removal effects):")
for channel in ["Search", "Social", "Email"]:
    norm = removal_effects[channel] / sum_effects
    credited = norm * total_conversions
    print(f"{channel}: normalized credit = {norm:.4f}  ->  credited conversions = {credited:.2f}")

Output (actually executed):

Baseline P(eventually Convert | Start) = 0.6000
Remove Search: P(Convert) drops to 0.3208, removal effect = 0.2792
Remove Social: P(Convert) drops to 0.3400, removal effect = 0.2600
Remove Email: P(Convert) drops to 0.2600, removal effect = 0.3400

Normalized channel credit (removal effect / sum of removal effects):
Search: normalized credit = 0.3175  ->  credited conversions = 1.91
Social: normalized credit = 0.2957  ->  credited conversions = 1.77
Email: normalized credit = 0.3867  ->  credited conversions = 2.32

The baseline conversion probability of 0.6 exactly matches the raw empirical rate (6 of the 10 pinned paths converted), which is the expected sanity check for a first-order Markov chain estimated directly from this data. Email receives the highest removal effect and the highest credited-conversion share (2.32 of the 6 total conversions), consistent with Email appearing in three of the six converting paths including as the sole touch in one of them, while still discounting for the fact that Search and Social also carry real, non-zero contribution.

Trade-offs and pitfalls

• The Markov chain's removal effect is a modeled counterfactual estimated from observational data, not a causal experiment; a channel that happens to be touched mostly by users who were already likely to convert for unrelated reasons will still show a large removal effect, since the model has no way to separate correlation from causation on its own.
• Shapley's exponential cost is a real, not theoretical, blocker at scale: attributing credit across dozens of individual campaigns rather than a handful of channel groups makes exact Shapley infeasible, and Monte Carlo approximation trades exactness for tractability in a way that needs its own convergence checking, which is easy to skip under time pressure.
• A common wrong turn is picking one model and reporting its output as ground truth rather than as one estimate among several reasonable ones; running two different models (a heuristic and the Markov chain, for example) side by side and treating a large disagreement between them as a signal worth investigating, rather than picking whichever number is more convenient, is closer to how a senior analyst actually uses these outputs.
• The first-order Markov assumption (the next state depends only on the current state, not the full history) is a real approximation; a channel that behaves very differently depending on what came before it (a retargeting ad that only works after an initial organic visit, for instance) will not be captured correctly without moving to a higher-order chain, which increases both data requirements and the state-space size sharply.