Apple Business Intelligence Analyst (Senior Level) Interview Preparation Guide

Direct answer

A 6% MRR (monthly recurring revenue) drop should be decomposed into its standard components: new MRR, expansion MRR, contraction MRR, churned MRR, and often-overlooked MRR lost to failed payments. Any one of these moving the wrong way can produce the same top-line drop, so the diagnosis starts by building the bridge from last month's MRR to this month's and seeing which component actually moved.

Structured elaboration

Worked example

A bridge that reproduces the stated 6% drop:

Starting MRR: $500,000.

• New MRR: +$20,000
• Expansion MRR: +$15,000
• Contraction MRR: -$25,000
• Churned MRR: -$40,000

$\$500{,}000 + \$20{,}000 + \$15{,}000 - \$25{,}000 - \$40{,}000 = \$470{,}000$

Ending MRR = $470,000, a decline of $30,000:
$\frac{\$500{,}000 - \$470{,}000}{\$500{,}000} = \frac{\$30{,}000}{\$500{,}000} = 6\%$

In this bridge, churned MRR (-$40,000) is the largest single driver, larger than the contraction line and larger than what new and expansion MRR combined offset, so the investigation should start with which accounts churned and why, not with the sales pipeline.

Trade-offs & pitfalls

• Looking only at the net 6% figure hides which single component actually moved; always build the bridge before drawing a conclusion.
• Churn concentrated in a few large accounts explains the same dollar drop very differently than churn spread across many small accounts, and calls for a different fix, account management versus a broad retention campaign.
• Involuntary churn from failed payments is often temporary and self-corrects with better dunning, unlike voluntary cancellations; separating the two before reacting avoids treating a payments problem as a product problem.
• A weekly, not just monthly, view of each component catches a developing problem a month earlier than waiting for the monthly MRR bridge to close.

Direct answer

The mistake was sending a demand forecast to leadership that was off by a meaningful margin because I misunderstood a default filter in a reporting tool I had just started using, not because I was careless. I found out when a stakeholder cross-checked the number against a different report and it didn't match, and what changed afterward wasn't just personal caution, it became an automated check that catches that specific class of error before a report goes out.

What happened and how I found out

I was new to a business intelligence tool the team had recently adopted and built a demand forecast that, unknown to me, was silently excluding a large customer segment because of a default filter left over from a template I had copied. The number went into a deck that leadership used to plan inventory for the following quarter. I found out three days later when a colleague, cross-referencing the number against an older report format, flagged that the totals didn't reconcile. As soon as I confirmed it was a real error and not a discrepancy in his numbers, I told the people who had received the deck that same day, with the corrected figure and a plain explanation of the cause, rather than waiting until I had a full write-up ready.

Recovery and what changed

For the immediate damage, I worked with the planning team to understand what decisions had already been made off the wrong number and flagged which of those needed a second look before anything was locked in. Longer term, I didn't trust myself to just be more careful next time, since the error came from a tool default I didn't know existed, not from rushing. Instead, I built a validation step into the report template itself, a total-reconciliation check against a known-good source that runs automatically before the report is finalized, so the same class of mistake gets caught by the process rather than relying on me remembering to check a filter I didn't know to look for.

Trade-offs and pitfalls

The instinct after a mistake like this is often to promise to be more careful, which sounds responsible but doesn't actually prevent a repeat if the root cause was unfamiliarity rather than carelessness. The fix that actually holds is the one that doesn't depend on me remembering; a habit can lapse under pressure, an automated check in the template can't.

Direct answer

A hub stores only a business key and its metadata (never descriptive attributes); a link stores a relationship between hubs; a satellite stores the actual descriptive, time-variant attributes for a hub or a link. The raw vault is insert-only because that is what makes it a faithful, reproducible record of everything ever received: a change becomes a new satellite row with a later load timestamp, not an overwrite of the old one. That buys you an audit trail a dimensional model does not give you by default, plus resilience to source-system change, at the cost of needing a business vault or dimensional layer before an analyst can use it comfortably.

Structured elaboration

Hub. Columns: a hashed business key (customer_hk), the natural business key itself (customer_bk), a load timestamp, and the record source. A hub never holds descriptive attributes like name or email: it exists purely to give every business entity a single, stable identity that every satellite and link can reference.

Link. Represents a relationship or an event that connects two or more hubs (a specific order connecting a customer hub and a product hub, for instance). It stores the hashed keys of the hubs it relates, a load timestamp, and the source. Links are also insert-only: a relationship that no longer holds is not deleted, a new fact about the relationship is layered on top through its own satellite.

Satellite. Stores the descriptive attributes for a hub or a link, tagged with a load timestamp and (once superseded) a load-end timestamp, plus a hash of the tracked attributes so a loader can cheaply detect "did anything actually change" before inserting a new row. A hub can have several satellites split by source system or by how often the attributes change, which is one of Data Vault's genuine advantages over a single wide dimension: a fast-changing "current status" satellite and a slow-changing "demographic" satellite for the same hub do not force each other's load cadence.

Why insert-only, and what it buys you. In a dimensional Type 2 slowly changing dimension, history is preserved too, but the row still lives inside the ONE table that also has to serve current-state lookups, joins to facts, and business intelligence (BI) tooling all at once. A Data Vault satellite's only job is to be a faithful, append-only record of what a specific source said about a specific entity at a specific time: nothing is ever corrected in place, so re-deriving any historical state, or proving to an auditor exactly what the system held on a given date, is a pure read, never a question of whether an UPDATE silently lost something. The cost is that a satellite alone is not a convenient shape for BI: an analyst asking "what is this customer's current email" has to filter for the open-ended row (load_end_dts IS NULL), and a query spanning many hubs and satellites needs several joins that a single conformed dimension would not.

Worked example

Below is a minimal, runnable sketch: one hub, one satellite tracking a customer's email across a change, and the point-in-time query pattern.

CREATE TABLE hub_customer (
    customer_hk   VARCHAR PRIMARY KEY,
    customer_bk   VARCHAR NOT NULL,
    load_dts      TIMESTAMP NOT NULL,
    record_source VARCHAR NOT NULL
);

CREATE TABLE sat_customer_details (
    customer_hk   VARCHAR NOT NULL REFERENCES hub_customer(customer_hk),
    load_dts      TIMESTAMP NOT NULL,
    load_end_dts  TIMESTAMP,          -- NULL = still the current value
    hash_diff     VARCHAR NOT NULL,
    email         VARCHAR,
    record_source VARCHAR NOT NULL,
    PRIMARY KEY (customer_hk, load_dts)
);

INSERT INTO hub_customer VALUES ('hk_c1', 'CUST-100', '2026-01-01', 'crm_system');
-- original email, later superseded (load_end_dts closed once the new row lands)
INSERT INTO sat_customer_details VALUES
  ('hk_c1', '2026-01-01', '2026-03-15', 'hash_v1', 'ada@old-domain.com', 'crm_system');
-- the new email, current (load_end_dts IS NULL)
INSERT INTO sat_customer_details VALUES
  ('hk_c1', '2026-03-15', NULL, 'hash_v2', 'ada@new-domain.com', 'crm_system');

-- what was the email as-of 2026-02-01 (before the change)?
SELECT email FROM sat_customer_details
WHERE customer_hk = 'hk_c1'
  AND load_dts <= TIMESTAMP '2026-02-01'
  AND (load_end_dts IS NULL OR load_end_dts > TIMESTAMP '2026-02-01');

Executed against DuckDB, the "as-of 2026-02-01" query returns ada@old-domain.com, and the identical query with the date changed to 2026-04-01 returns ada@new-domain.com. Nothing was ever overwritten: both rows still exist, and which one answers "what was true then" is purely a matter of the load_dts/load_end_dts filter, which is exactly the auditability property that makes a Data Vault defensible to a regulator asking "prove what you knew, and when."

Trade-offs and pitfalls

A hub/link/satellite model for a regulated organization onboarding several frequently-changing source systems is the right fit specifically because the insert-only satellite gives a complete, provable history per source without ever having to touch an existing hub or fact table when a sixth source system appears; you add a hub, a link, and a satellite, you do not redesign anything that already exists. But it is not the end state: you still need a business vault (derived, conformed, business-rule-applied tables sitting on top of the raw vault) or a dimensional layer built from the vault before an analyst can query it without knowing what a hub or a satellite is. Skipping that layer is the most common real-world mistake: a raw vault handed directly to BI tooling produces correct but nearly unusable multi-join queries, and defeats the entire point of having done the modeling work.

Framework: treat this as a product-investment decision — gather evidence, estimate impact, validate with experiments, then recommend with ROI and risk.

1. Clarify scope & success criteria

• Define the interaction (what problem it solves, target user segments, primary KPI e.g., conversion or retention).
• Constraints: engineering effort, time, legal/privacy, UX guidelines.

2. Data to collect

• Product telemetry: current funnels, activation rates, DAU/WAU, session length, feature usage by cohort.
• Business metrics: conversion to paid, average revenue per user (ARPU), churn, LTV.
• Qualitative: competitor demo transcript, user interviews, support tickets, NPS comments.
• Operational: implementation cost estimates, expected maintenance.

3. Hypothesis & leading metrics

• Hypothesis: "Adding interaction X increases activation by Y% and reduces churn by Z% for segment S."
• Leading metrics to forecast ROI: activation lift (first-week conversion), engagement depth (time on task, events/session), short-term conversion uplift, retention delta (30-day), funnel drop-off reduction.
• Map metric changes to revenue: ΔARPU and retention → ΔLTV; use cohort LTV model.

4. Experiments/pilots

• Instrument prototype as feature-flagged A/B test (control vs variant). If risky, run an opt-in pilot with power users.
• Variants: simple mimic of competitor, smaller/lightweight version, and control.
• Sample size & duration: compute power for expected minimum detectable effect (e.g., 3–5% lift), run across representative segments for 2–4 weeks.
• Track secondary metrics for harm (error rates, task abandonment, support volume).
• If backend impact is large, run a business-simulated pilot in a sandbox environment and usability sessions before A/B.

5. Analysis approach

• Pre-register metrics and success thresholds; use sequential testing guardrails.
• Use uplift modeling, cohort analysis, and segmented treatment effects (by acquisition channel, device).
• Back-of-envelope ROI: incremental revenue = users_exposed * conversion_lift * ARPU; incremental cost = development + infra + ops. Compute payback period and NPV over expected feature lifetime.

6. Presentation to leadership

• One-page executive summary: recommendation (Go/No-Go/Iterate), top-line projected ROI, key assumptions and sensitivity ranges.
• Visuals: funnel before/after, cohort LTV scenarios, A/B results with confidence intervals, cost breakdown, timeline and risks.
• Ask for a clear decision: pilot approval, budget, or kill. Provide contingency plan (roll-back criteria, monitoring dashboard).
• Deliverables: BI dashboard with live experiment metrics, SQL queries and data lineage, and a reproducible ROI model so leadership can adjust assumptions.

This plan balances quantitative forecasting, rapid validated learning, and clear decision criteria suited for BI-driven product decisions.

Maintaining an SCD (Slowly Changing Dimension) Type 2 dimension from a near-real-time CDC (Change Data Capture) stream, landing in a warehouse with no multi-statement transactions and only eventual consistency, means the two-statement transactional pattern that works for a batch load isn't available: you can't rely on "wrap it in a transaction" to prevent readers from seeing a broken intermediate state, because the warehouse doesn't offer that guarantee.

The design

1. Land CDC events as an append-only changelog first, never write directly to the dimension table. Every incoming change event (with its own event timestamp and a sequence number from the source, if available) gets appended to a customer_changelog table. This step is naturally idempotent (appending the same event twice just duplicates a row you can later dedupe on read or on a scheduled merge) and never risks corrupting the dimension itself.
2. Deterministic keys and buffering to handle out-of-order events. Because CDC events can arrive out of order (network retries, partition rebalancing), you buffer briefly (a short watermark-based window) and order by the event's own timestamp or source sequence number, not arrival order, before deciding what the "new current" state actually is.
3. Scheduled merges from changelog to dimension, not a per-event write. Rather than updating the dimension table on every single incoming event (which is where the "no multi-statement transaction" limitation actually bites), a scheduled job runs periodically (every few minutes) and applies the SCD2 expire-and-insert logic from the changelog into the dimension in one pass, using the two-statement pattern from the batch case, but now the "staging table" is the changelog filtered to unapplied events rather than a full snapshot.
4. Sequence numbers to make each scheduled merge safe against races. Track, per customer, the highest changelog sequence number already applied to the dimension. The scheduled merge only considers changelog rows with a sequence number greater than what's recorded, which makes an accidental double-run of the merge job (two scheduled runs overlapping) a safe no-op rather than a source of duplicate SCD2 versions.

Why this avoids needing a real transaction: the dimension table itself is only ever touched by ONE process (the scheduled merge job), running one merge at a time (enforced by the sequence-number check plus, ideally, a simple job-level lock). Readers of the dimension always see the state as of the last completed merge, a normal eventually-consistent read, never a state corrupted mid-write, because there's never more than one writer racing against itself.

The race this specifically guards against: two change events for the same customer arriving close together (one from the CDC stream directly, one from a retried delivery of an earlier event) could, without the sequence-number check, both try to expire the same current row, and depending on execution order, one of them could "expire" a row that a later, more-current event had already superseded, corrupting the dimension's history. Ordering strictly by sequence number (source-assigned, not arrival time) and applying only in that order is what keeps the Type 2 history correct despite eventual consistency and out-of-order delivery.

Direct answer

When a small number of superusers dominate an average behavioral metric, the average stops representing a typical user, so robust reporting means leading with metrics less sensitive to extreme values, such as the median or a specific percentile, and reporting alternative cuts like top-decile share separately rather than folding everything into one mean.

Structured elaboration

A mean is pulled arbitrarily far by a small number of extreme values because it weights every unit of activity equally regardless of who generated it; a median is far more robust because it only cares about the middle of the distribution's ordering, not the magnitude of the extremes. For behavioral data specifically, a useful complementary pair is the median (what a typical user looks like) alongside a measure of concentration, such as what share of total activity comes from the top decile of users, since that number directly answers "how dependent is this metric on a small group" in a way neither the mean nor the median does alone. When stakeholders need to see the effect of a change on power users specifically, reporting a percentile (p90 or p99) alongside the median gives visibility into that population without letting it distort the headline number.

Worked example

import numpy as np
np.random.seed(5)

n = 1000
activity = np.random.poisson(5, n).astype(float)          # most users: modest activity
superuser_idx = np.random.choice(n, size=int(0.02 * n), replace=False)   # top 2%
activity[superuser_idx] += np.random.gamma(shape=2, scale=150, size=len(superuser_idx))

print('mean:', round(activity.mean(), 2), ' median:', round(np.median(activity), 2))
top_decile_share = activity[np.argsort(activity)[-100:]].sum() / activity.sum()
print('top-decile share of total activity:', f"{top_decile_share:.1%}")
print('superuser mean:', round(activity[superuser_idx].mean(), 1),
      ' non-superuser mean:', round(np.delete(activity, superuser_idx).mean(), 2))

Simulating 1,000 users' weekly event counts, where most users generate a modest, Poisson-distributed amount of activity but the top 2% (20 users) are superusers with much higher activity, gives a mean of 12.24 events per user but a median of only 5.00, a mean over twice the median. The top 100 users (the top decile) account for 65.5% of all activity in the simulation, and the 20 identified superusers alone average 361.8 events each, compared to 5.11 for everyone else. Reporting "average weekly engagement is 12.24 events" without qualification would meaningfully overstate what a typical user actually experiences, since more than 90% of users generated fewer events than that average.

Trade-offs and pitfalls

The main pitfall is reporting only the mean, or only the median, since each hides something the other reveals: the mean alone hides how unrepresentative it is of a typical user, and the median alone hides how much of the metric's total the tail contributes, which matters for questions like revenue or infrastructure cost that scale with total activity rather than with a typical user's activity. A second pitfall is treating superusers purely as a reporting nuisance to smooth away, when in practice they are often the most valuable and highest-retention segment of the user base, worth understanding and serving deliberately rather than only excluding from headline metrics.

Direct answer

NULLs appearing after a schema change most often trace to one of three causes: a migration that added a column without backfilling existing rows (so old rows are legitimately null by construction), a query that assumes the old schema shape and silently mismatches against the new one (an outer join now failing to find a match, a column rename the query wasn't updated for), or a downstream ETL step that hasn't been updated to populate the new/changed field. Triage means checking WHEN the nulls started relative to the migration, and WHICH rows are affected, before choosing a fix.

Structured elaboration

1. Confirm the exact timing. Check whether nulls appear only in rows created/modified AFTER the migration (points at an ETL or write-path gap that isn't populating the field going forward) or also in rows that existed before it (points at a backfill that either wasn't run or ran incorrectly).
2. Check whether it's a genuine data gap or a query-side artifact. Run the underlying query manually against a known-good row and confirm it's actually pulling a null from the table, versus the possibility that a JOIN condition changed (a renamed key column, a changed join type) and is now failing to match rows it used to match, which would surface as NULL in the query output even though the underlying data is fine.
3. Check the migration script itself for whether it included a backfill step, and if so, whether that backfill actually completed successfully (a backfill job that failed partway through, or silently skipped rows matching some edge-case condition, produces exactly this symptom for a SUBSET of rows).
4. Check any ETL/pipeline steps that write to the changed column for whether they were updated in lockstep with the schema migration; a common gap is a migration that changes the SOURCE schema while a downstream transform still reads/writes the old shape, silently dropping or nulling the new field.
5. Decide rollback versus backfill versus forward-fix based on what's found. If the migration itself is sound but a backfill is incomplete: re-run or resume the backfill. If the migration broke an assumption a query relies on: fix the query, not the data. If it's a genuine, permanent gap for historical data that can't be backfilled (the source information no longer exists): document it as an expected, permanent null rather than continuing to chase a "fix" that isn't possible.

Worked example

Dashboards start showing NULL for a region column starting exactly at the migration's deploy timestamp, but ONLY for newly-inserted rows; historical rows still show the correct value. That timing rules out a backfill issue (historical data is fine) and points at the write path: checking the ingestion service shows it wasn't updated to populate the new region field, since the migration only changed the schema, not the application code writing to it. The fix: deploy the ingestion service update that populates region going forward, and separately backfill the narrow window of rows written between the schema migration and the code fix, which is now a small, bounded gap rather than an ongoing one.

Rollback/backfill options and prevention:

• Rollback: appropriate only if the migration itself introduced the bug and can be safely reverted without losing legitimate new data written since; often not viable once real traffic has flowed through the new schema.
• Backfill: the right choice when the data CAN be reconstructed (from a related source, from logs, from recomputation) for the affected window; scope it precisely to the confirmed-affected rows rather than re-running against the whole table.
• Prevention: require that any schema migration changing a column's population source ships an explicit, tested backfill step (not just a schema DDL change) in the same review, and add a post-migration data-quality check (a null-rate assertion on the changed column) that runs automatically and pages before the change reaches dashboards, rather than relying on someone noticing a dashboard looking wrong.

Trade-offs and pitfalls

The trap is assuming "schema change caused nulls" automatically means "the migration is broken," when in this worked example the migration itself was correct and the actual gap was in application code that was never updated to match; fixing the wrong layer (attempting to "fix" the migration) would have left the real gap in place. Precisely bounding WHICH rows are affected before touching anything prevents an overly broad, unnecessary backfill across data that was never actually wrong.

Direct answer

Don't try to average everyone's position into a compromise nobody's happy with. Ground the negotiation in the shared outcome, make the trade-offs between options explicit with evidence, and force a real decision (with an owner and a documented rationale) within a fixed timeframe. A compromise sticks when people can see why it was chosen, not just that it split the difference.

Structured elaboration

1. Reframe around outcome, not position. Ask each stakeholder what success looks like for them, not what they want built. Two stakeholders who seem opposed on the "what" often agree on the "why," which is where the real compromise lives.
2. Bring evidence, not opinions. Gather whatever is available and relevant: usage data, cost/effort estimates, prior incidents, qualitative feedback. A room full of opinions negotiates forever; a room with a shared set of facts converges faster.
3. Make trade-offs visible. Lay out 2-3 real options with their costs and benefits side by side, instead of a single proposal to accept or reject. People compromise more easily when they're choosing between concrete alternatives than when they're being asked to give up a specific ask.
4. Use a structured negotiation move. Propose a balanced default option first, then invite each side to request a bounded concession from it, rather than starting from each side's maximal ask and negotiating down. Time-box the discussion so it doesn't drift into re-litigating the same points.
5. Document the decision and name an owner. Write down what was decided, why, who owns it, and when it will be revisited. If the group truly can't converge, escalate with a specific recommendation rather than an open question, so the escalation itself doesn't become another unresolved debate.
6. Build in a review point. Treat the agreement as provisional and testable, not permanent. A short follow-up (after the next milestone, or a fixed number of weeks) to check whether the compromise is actually working keeps people bought in because they know it isn't final and unappealable.

Worked example

Three stakeholders disagree on scope for a feature: one wants the full version shipped now, one wants it deferred a quarter, one wants a stripped-down version shipped immediately. Instead of negotiating "how much scope," the facilitator asks each what outcome they're protecting: the first is protecting a customer commitment, the second is protecting engineering capacity for other work, the third is protecting the team's ability to learn before over-investing. That reframing surfaces a real option none of them had proposed: ship a narrow version that satisfies the customer commitment, explicitly scoped as a first iteration, with the deferred work logged and re-prioritized at the next planning cycle. The decision, the scope boundary, and the re-prioritization date are written down and shared with all three stakeholders.

Trade-offs & pitfalls

• Pitfall: false compromise, where everyone gets a token piece of what they asked for and the result satisfies no one's actual underlying need.
• Pitfall: skipping documentation. An undocumented "agreement" gets re-argued the moment someone's memory of it differs.
• Pitfall: treating consensus as required. Some decisions need a single accountable owner to make the call after input, not unanimous agreement, especially under a deadline.
• Senior differentiator: designing the forcing function (a default option, a timebox, a named decision owner) instead of facilitating an open-ended discussion indefinitely. That's what turns "several people who each want something different" into an actual decision.

Direct answer

The unit of analysis is the entity you count and average over when computing a metric. Event-level counts every individual action, session-level counts per visit, and user-level counts per unique person. For measuring feature adoption specifically, the correct unit is user-level, because adoption is a question about how many people used a feature, and event-level or session-level aggregation lets a small number of heavy users dominate the number and make adoption look far higher than it really is.

Structured elaboration

• Event-level. Each recorded action is one row (e.g. every chat_send event). Good for measuring volume and throughput, but says nothing about how many distinct people generated that volume.
• Session-level. A time-bounded visit is the unit (e.g. actions per session, session duration). Good for engagement intensity within a visit, but a user with 10 sessions counts 10 times.
• User-level. Each unique person, over a defined window, is the unit (e.g. percent of monthly active users who used a feature at least once). Good for adoption, retention, and anything answering "how many people."

The reason event-level aggregation misleads for adoption specifically is a units mismatch: "adoption" is inherently a statement about people, but a raw event count is dominated by whoever generates the most events, which is rarely a representative measure of how widely a feature spread across the user base.

Worked example

Suppose a new chat feature is live for a product with 10,000 monthly active users. In one month, the feature generates 597 chat_send events. If those events came from 300 distinct users sending on average ~2 messages each, an event-level headline of "597 uses this month" and a user-level headline of "300 of 10,000 users adopted the feature (3.0%)" describe the same underlying data very differently. Now suppose most of that volume is concentrated: 3 power users (support agents testing the feature, or a bot account) send 100 messages each (300 events), and the remaining 297 events come from 297 distinct users sending one message each. Event-level volume (597) barely tells you anything about reach. User-level adoption is unambiguous: $300 / 10{,}000 = 3.0\%$ of MAU tried the feature at least once, which is the number a PM deciding whether to invest further in the feature actually needs. Reporting "597 uses" to that PM without the user-level breakdown risks a decision to double down on a feature that only 3% of the base has touched.

Trade-offs & pitfalls

User-level adoption is the right primary metric, but it should be paired with a session- or event-level intensity metric (actions per active user, or per session) to distinguish "many people tried it once" from "many people use it repeatedly," since those imply very different next steps. A common mistake is picking the time window inconsistently with the metric's intent: reporting "monthly adoption" but computing it over a 7-day window inflates apparent adoption relative to the stated period. It is also worth deduplicating bot or internal-test accounts before computing user-level adoption, since a handful of automated accounts generating disproportionate event volume can distort even a correctly-chosen user-level metric if they are not excluded.

Given events(id, user_id, amount, event_time) and a materialized daily_user_totals(date, user_id, total_amount), the incremental update needs to fold only the dates present in staging_events into the existing totals, adding new activity to matching (date, user_id) rows and inserting fresh rows for combinations that do not exist yet, all without holding a lock on the whole target table for longer than necessary.

Approach

1. Aggregate staging_events down to one row per (date, user_id) first, so the merge step only ever has to reconcile one summarized delta row per key, not many raw event rows.
2. Upsert that delta into daily_user_totals: insert a new row where the key does not exist, and add the delta's amount onto the existing total where it does.
3. For deletes (a correction that removes a previously-counted event), apply the negative of that row's amount as a delta in the same way, rather than trying to recompute the whole day from scratch.

Code (executed and verified)

-- fixture: existing totals plus a new staging batch
CREATE TABLE daily_user_totals (date DATE, user_id INT, total_amount DECIMAL(12,2), PRIMARY KEY(date, user_id));
INSERT INTO daily_user_totals VALUES (DATE '2024-01-01', 1, 100.00), (DATE '2024-01-01', 2, 50.00);

CREATE TABLE staging_events (id INT, user_id INT, amount DECIMAL(12,2), event_time TIMESTAMP);
INSERT INTO staging_events VALUES
  (100, 1, 25.00, '2024-01-01 10:00:00'),
  (101, 3, 40.00, '2024-01-01 11:00:00'),
  (102, 1, 5.00,  '2024-01-01 12:00:00');

-- 1. summarize new/changed activity to one row per (date, user_id)
CREATE TABLE staging_daily AS
SELECT CAST(event_time AS DATE) AS date, user_id, sum(amount) AS delta_amount
FROM staging_events
GROUP BY 1, 2;

-- 2. upsert the delta into the materialized target
INSERT INTO daily_user_totals (date, user_id, total_amount)
SELECT date, user_id, delta_amount FROM staging_daily
ON CONFLICT (date, user_id)
DO UPDATE SET total_amount = daily_user_totals.total_amount + EXCLUDED.total_amount;

Executed against a target table pre-seeded with (2024-01-01, user 1, 100.00) and (2024-01-01, user 2, 50.00), and a staging batch containing two new events for user 1 (25.00 and 5.00) and one new event for user 3 (40.00): the result was user 1 = 130.00 (100 + 25 + 5, correctly accumulated), user 2 = 50.00 (untouched, as expected since no new activity existed for them), and user 3 = 40.00 (a new row inserted). All three outcomes matched the expected arithmetic exactly.

Handling deletes and idempotency

For a genuine delete of a previously-processed event, apply it as a negative delta through the same staging_daily aggregation path (summing a negative amount for a deleted row) rather than a separate code path, so the merge logic only has one shape to reason about. Concretely, using the fixture above: if event id 100 (user 1, amount = 25.00) later turns out to be a duplicate that needs to be removed, a later staging batch carries a compensating row, (user_id=1, amount=-25.00, event_time='2024-01-01 13:00:00'), rather than deleting anything from staging_events. That row flows through the same step-1 GROUP BY and sum(amount), contributing -25.00 to staging_daily for (2024-01-01, user 1), and the step-2 upsert applies it exactly like any other delta: total_amount = daily_user_totals.total_amount + EXCLUDED.total_amount becomes 130.00 + (-25.00) = 105.00, which is exactly the total user 1 would have had if the erroneous 25.00 event had never been staged (the 100.00 seed plus the remaining 5.00 event). The negative amount is not produced by a special delete code path; it is simply the deleted row's own original amount, sign-flipped, staged like any other event. For idempotency (the same staging batch being replayed, for example after a job retry), the delta must be computed from events not yet applied, not from all events in the staging table unconditionally; track a processed-flag or a watermark on staging_events and only aggregate unprocessed rows, or the same delta will be double-applied on a retry.

Complexity and edge cases

The aggregation step is a single pass over the staging batch, and the upsert is bounded by the number of distinct (date, user_id) pairs touched, not the full target table, so cost scales with the size of the incoming batch rather than the table's total history. Edge cases worth naming explicitly: a staging_events batch that spans more than one date needs the GROUP BY to include date (as written above) so events do not get folded into the wrong day; and a batch containing both an insert and a same-key correction in the same run must net out correctly before the upsert, which the sum(amount) in step 1 already handles since it aggregates all of a key's staged rows together before touching the target.

Trade-offs and pitfalls

A blind INSERT ... ON CONFLICT without first deduplicating and aggregating the staging batch (skipping step 1) would apply each raw event row as its own tiny update, which is both slower (many small writes instead of one aggregated write per key) and more exposed to a partial-batch retry double-applying individual rows rather than one clean delta per key.