Apple Business Intelligence Analyst (Senior Level) Interview Preparation Guide

Direct answer. An execution plan is the concrete recipe the database picked to run your query: which tables it reads, in what order, using which access method and join algorithm, and roughly how expensive it expects each step to be. The optimizer builds it by considering multiple candidate plans and picking the one with the lowest estimated cost, based on table and index statistics.

Structured elaboration. A plan is a tree of operators. Leaves are typically scans (sequential scan, index scan, index-only scan) that read data from a table or index. Interior nodes combine or transform rows: joins (nested loop, hash, merge), sorts, aggregates, filters. Execution happens bottom-up: leaves produce rows first, and those rows flow upward through the tree until the top node returns the final result set. Reading a plan means identifying which node is producing or consuming the most rows and time, not just glancing at the top-level operator.

Worked example. Say a dashboard query joins a customers table to an orders table and groups by region. A plan for it might look like: a sequential scan on orders at the bottom (because there's no index on the join or filter column), feeding into a hash join with an index scan on customers, feeding into a hash aggregate for the GROUP BY. If that dashboard suddenly gets slow after orders grows from 100K to 50M rows, the sequential scan at the bottom of the tree is very likely the answer, since its cost scales with table size while the other nodes' costs are closer to the number of rows that already survived the scan. Recognizing that shape, rather than treating the query as an opaque black box, is what lets you jump straight to "add an index that lets this become an index scan" instead of guessing.

Trade-offs and pitfalls. It is tempting to read only the very top operator of a plan (the last thing printed) and stop there; the expensive node is almost always further down, closer to the leaves, because costs accumulate as rows flow up. It's also worth remembering the plan reflects the optimizer's ESTIMATE of the best strategy, not a guarantee: if the estimates are wrong (stale statistics, skewed data), the "recipe" can be provably suboptimal even though it was picked correctly given what the optimizer believed at planning time.

Direct answer. Placing a filter on the right-hand table's column in WHERE, rather than in the join's ON clause, forces that column to be non-NULL to pass the filter, which throws away every left row that had no match (and therefore had NULL there), functionally converting your LEFT JOIN back into an INNER JOIN.

Structured elaboration. A LEFT JOIN's whole purpose is to preserve unmatched left rows by filling the right side with NULLs. WHERE runs AFTER the join has already produced its rows (including the NULL-padded ones), so a condition like WHERE o.status = 'SHIPPED' evaluates to unknown for any row where o.status is NULL, and WHERE discards anything that isn't TRUE. The fix is to move the condition into the ON clause instead, so it only constrains WHICH right-side rows are allowed to match in the first place, rather than filtering the joined result afterward; unmatched left rows still make it through with NULLs, exactly as a LEFT JOIN should behave.

Worked example. customers(1),(2),(3). orders(order_id=100, customer_id=1, status='SHIPPED'), (order_id=101, customer_id=2, status='PENDING'). Customer 3 has no orders at all.

-- buggy: WHERE demotes this to an INNER JOIN
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'SHIPPED';
-- returns only (1, 100). Customers 2 and 3 vanish, even though the LEFT JOIN
-- was written specifically to keep customers with no matching order.

-- fixed: move the condition into ON
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = 'SHIPPED';
-- returns (1, 100), (2, NULL), (3, NULL). Every customer still appears.

Trade-offs and pitfalls. A quick check for this class of bug: run the query with and without the suspect WHERE clause and compare row counts against COUNT(*) on the left table; if the "LEFT JOIN" version returns fewer rows than the left table has, something downstream demoted it. The same bug hides in more places than a literal comparison: o.status IS NOT NULL, o.amount > 0, even a seemingly harmless AND o.region = 'US' in WHERE all have the identical effect. As a review habit, any filter that mentions a LEFT-JOINed table's column should be treated as suspicious until you've confirmed it's meant to belong in ON rather than WHERE.

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.

Enforce the row filter and the column mask as close to the data as possible, at the warehouse layer, so every consumer (SQL client, BigQuery-style authorized view, or Looker's semantic layer) inherits the same rule instead of each client re-implementing it. The three approaches (a database-level row-level security policy, BigQuery-style authorized views, and a BI tool's model-level access) differ in where the enforcement logic lives, which changes both the performance profile and how many places you have to keep in sync.

The core mechanism

Whichever layer enforces it, the mechanism is the same: a predicate that filters rows based on the querying user's identity (row-level security, RLS) and an expression that conditionally transforms a column's value based on the user's role (masking). Below is a runnable, portable demonstration of that mechanism using SQLite so the logic can be verified directly; the mapping to each real product's specific feature is explained afterward.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

# Base fact table: one shared underlying table, as in the question.
cur.execute('''
    CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        region TEXT NOT NULL,
        customer_email TEXT NOT NULL,
        amount_usd REAL NOT NULL
    )
''')
rows = [
    (1, "US", "alice@example.com", 120.00),
    (2, "US", "bob@example.com", 340.50),
    (3, "EU", "carla@example.com", 89.99),
    (4, "EU", "dieter@example.com", 275.25),
    (5, "APAC", "eun@example.com", 512.10),
]
cur.executemany("INSERT INTO orders VALUES (?,?,?,?)", rows)
conn.commit()

def query_as(session_region, session_role):
    # session_region drives the row filter (RLS); session_role drives the column mask.
    mask_expr = (
        "customer_email"
        if session_role == "PRIVILEGED"
        else "substr(customer_email, 1, 2) || '***@' || substr(customer_email, instr(customer_email,'@')+1)"
    )
    sql = (
        f"SELECT id, region, {mask_expr} AS customer_email, amount_usd "
        "FROM orders WHERE region = ? ORDER BY id"
    )
    cur.execute(sql, (session_region,))
    return cur.fetchall()

for r in query_as("US", "ANALYST"):
    print(r)

Output:

(1, 'US', 'al***@example.com', 120.0)
(2, 'US', 'bo***@example.com', 340.5)

A PRIVILEGED session against the same table and region returns the unmasked emails, and an APAC-scoped session never returns US or EU rows at all, even though every session queries the identical underlying table. The full run (including both assertions and a compliance-role session) is in the code artifact used to write this answer; the point being demonstrated is that a single shared table plus a session-scoped predicate and conditional expression is sufficient to produce per-user row and column visibility without duplicating the data.

How this maps to each real layer

• Database-level RLS policy (for example a Snowflake ROW ACCESS POLICY or a masking policy). The predicate above corresponds to a policy object attached directly to the table: CREATE ROW ACCESS POLICY region_policy AS (region STRING) RETURNS BOOLEAN -> region = CURRENT_REGION_CONTEXT(), attached with ALTER TABLE orders ADD ROW ACCESS POLICY region_policy ON (region). Every query against the table, from any tool, is filtered automatically; there's exactly one place to define and audit the rule.
• BigQuery-style authorized views. Instead of a policy object on the base table, you create a view with the filter and masking baked into its SELECT, and grant users access to the view instead of the base table (CREATE VIEW orders_masked AS SELECT ..., CASE WHEN ... END AS customer_email FROM orders WHERE region = SESSION_USER_REGION()). The base table itself stays locked down; the view is the only grantable surface. This is functionally similar to the RLS policy but the enforcement lives in a separate object you must remember to route every consumer through, rather than attaching automatically to the base table.
• Looker's model-level access. Looker sits above the warehouse and expresses the same row filter as an access_filter in its LookML model (access_filter: { field: orders.region, user_attribute: region }), and column-level masking as a restricted "always filter" or a hidden/derived field shown only to certain permission groups. This enforcement only applies to traffic that goes through Looker; a user with direct warehouse SQL access bypasses it entirely.

Integrating with SSO for identity

None of the three layers know who the user is on their own; identity comes from single sign-on (SSO), typically via SAML (Security Assertion Markup Language) or OIDC (OpenID Connect) federated into the warehouse's own user/role system and separately into Looker's user attributes. The pattern: SSO authenticates the user and asserts group/attribute claims (for example region: EU); the warehouse's identity provider integration maps those claims into session context variables (CURRENT_REGION_CONTEXT() in the demo above); Looker separately maps the same SSO claims into its own user attributes via SCIM (System for Cross-domain Identity Management) provisioning, so both layers derive the same attribute from the same source of truth rather than each maintaining its own copy of "which region is this user."

Performance and maintainability trade-offs

Trade-offs & pitfalls

The recurring mistake is enforcing this exclusively at the BI tool layer because it's the easiest place to configure, then discovering that anyone with direct SQL access to the warehouse (a data scientist's notebook, a scheduled export job) bypasses every rule Looker enforces. Enforcing at the warehouse layer is more work up front but is the only approach that holds regardless of which client connects; BI-tool-level policy should be treated as a convenience layer for the common case, not the actual security boundary.

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.

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.

Start with scope and reproducibility

• Ask the stakeholder which report, timeframe, filters, and exact numbers don’t match. Reproduce the mismatch yourself using the same user account, filters, and timeframe.

Source extract checks

• Verify extract schedule and watermark: confirm last successful refresh/time and row counts.
• Run a direct count/aggregation on source DB to compare:

SELECT COUNT(*) AS rows, SUM(amount) AS total
FROM sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-01-31';

• Check incremental extract logic for missed or duplicated batches (timestamps, CDC windows).

ETL/Transformation checks

• Review ETL job logs for errors, warnings, restart points, duplicates.
• Validate intermediate staging tables: row counts, distinct keys, NULL patterns.
• Re-run critical transformation SQL for a small sample and compare before/after values.

Joins and keys

• Examine join types (INNER vs LEFT) that can drop rows; test key cardinality:

-- find orphaned fact rows
SELECT f.id FROM fact f
LEFT JOIN dim d ON f.dim_id = d.id
WHERE d.id IS NULL;

• Check for many-to-many or duplicated dimension keys causing inflated totals.

DAX / Tableau calculations and filters

• Inspect calculation definitions, context modifiers, and filter scopes.
• For Power BI, test measure with ALL/REMOVEFILTERS to isolate filter impact:

TotalSalesNoFilters = CALCULATE([TotalSales], ALL('Date'))

• In Tableau, verify table calculations, LOD expressions, and whether dashboard filters are applied at worksheet vs dashboard level.

Timezone and currency conversions

• Confirm source timestamps’ timezone and any conversion applied during ETL or visuals.
• Verify currency rates: source currency, conversion rate table validity, and whether conversion applied before/after aggregation.

Compare row-level values

• For a small date/customer sample, trace a single transaction from source -> staging -> warehouse -> visualization to see where value diverges.

Performance and caching

• Check dashboard cache, materialized views, or aggregates that may be stale.

Documentation and communication

• Log each check, SQL queries and results, timestamps, and screenshots. Summarize findings: root cause, evidence (diffs, counts), impact, and recommended fix (code change, re-run ETL, refresh cache, or filter adjustment).
• Provide an action plan with owner, estimate, and verification steps. Share results with stakeholder and close loop after confirmation.

This systematic trace-first approach isolates the layer causing discrepancy and produces reproducible evidence for fixes.