Amazon Data Analyst (Staff Level) Interview Preparation Guide

Investigation plan (high-level steps)

1. Scope & urgency: quantify discrepancy (absolute and %), impact on P&L, set SLA for investigation with finance.
2. Reproduce & timestamp: pick a recent window where divergence is observed, snapshot both systems at same cutoff time.
3. Lineage mapping: list services/ETLs/transforms between warehouse orders and finance extract; identify join keys, aggregation windows, timezone logic.
4. Data comparison: compare raw events, aggregated rows, and late-arriving data; search for schema/timestamp changes or job failures.
5. Root-cause isolation: binary search over time (divide-and-conquer) to find first divergent batch.
6. Remediation & monitoring: suggest fixes, reconciliation SQL, and add alerts + reconciliation dashboards.

Data to compare

• Granular events: order_id, created_at, status, amount, currency, customer_id, source_service, ingestion_ts
• Aggregates: date_partition, sum(amount), count(orders), distinct(order_id)
• Processing metadata: ETL run_id, processed_ts, batch boundaries, offsets
• Checksums/hashes per partition (e.g., MD5 of concatenated order_ids)

SQL examples

1. Quick aggregate comparison by day (find days with mismatch)

WITH ware AS (
  SELECT date(created_at) as day, COUNT(*) as w_count, SUM(amount) as w_sum
  FROM warehouse.orders
  WHERE created_at BETWEEN '2025-11-01' AND '2025-11-30'
  GROUP BY 1
),
fin AS (
  SELECT date(tx_date) as day, COUNT(*) as f_count, SUM(amount) as f_sum
  FROM finance.nightly_orders
  WHERE tx_date BETWEEN '2025-11-01' AND '2025-11-30'
  GROUP BY 1
)
SELECT coalesce(w.day,f.day) as day,
       w_count, f_count, (w_count - f_count) as diff_count,
       w_sum, f_sum, (w_sum - f_sum) as diff_sum
FROM ware w FULL OUTER JOIN fin f USING (day)
ORDER BY day;

2. Binary-search style: detect earliest date with cumulative divergence

-- cumulative sums to find first point divergence grows
SELECT day,
       SUM(w_sum) OVER (ORDER BY day) AS cum_w,
       SUM(f_sum) OVER (ORDER BY day) AS cum_f,
       SUM(w_sum) OVER (ORDER BY day) - SUM(f_sum) OVER (ORDER BY day) AS cum_diff
FROM daily_comparison
ORDER BY day;

3. Row-level mismatches (orders present in warehouse but missing or different in finance)

SELECT w.order_id, w.amount as ware_amount, f.amount as fin_amount, w.created_at
FROM warehouse.orders w
LEFT JOIN finance.nightly_orders f USING (order_id)
WHERE date(w.created_at) BETWEEN '2025-11-15' AND '2025-11-20'
  AND (f.order_id IS NULL OR w.amount <> f.amount)
LIMIT 1000;

4. Checksum per partition (fast equality test)

SELECT date(created_at) as day, md5(string_agg(order_id || '|' || amount, ',' ORDER BY order_id)) as checksum
FROM warehouse.orders
GROUP BY 1;
-- compare to finance similar checksum

Communication plan (to finance & engineering)

• Immediate update: send a short executive summary with numbers (total discrepancy, % of revenue, earliest affected date), what you’re investigating, and expected timeline.
• Technical brief to engineering: include lineage, SQL artifacts, sample mismatched IDs, ETL run metadata, and suggested hypotheses (late-arrival, timezone/truncation, double-counting, missing partitions).
• Joint review: schedule 30–60 minute war-room with finance, engineering, and ETL owners to validate hypotheses and approve remediation.
• Deliverables: timeline of divergence, root cause, list of affected transactions, remediation steps, and follow-up reconciliation queries + monitoring to prevent recurrence.

What to watch for / common root causes

• Timezone or date-trunc differences
• Late-arriving or reprocessed events excluded from nightly extract
• Rounding/currency conversion mismatches
• Duplicate suppression or dedup logic differences
• Partial failures or backfills in ETL

Outcome & next steps

• Provide reproducible SQL snippets and a reconciliation dashboard; recommend automated daily diff alerts and a post-run verification checksum to catch regressions early.

Direct answer

Do not rebuild the existing department's star schema from scratch: keep it running, and introduce a compatibility view layer between its existing dimensions and a newly-built conformed version, so its current reports keep working unmodified while every new mart is built against the conformed dimensions from day one. Sequence the three new departments' marts in whichever order matches the bus matrix's widest-touching dimensions first, exactly as you would for a greenfield build, since from their point of view they are joining an enterprise warehouse that already has its shared dimensions defined.

Structured elaboration

What to do with the existing star schema. The existing department's fact table and dimensions almost certainly still work correctly for that department's own reports; the problem is only that its dimensions were never designed to be reused by anyone else. Do not touch the fact table. Build new, properly conformed versions of whichever dimensions the other departments will also need (typically customer and date), migrate the existing department's fact table to reference the new conformed dimension's keys, and leave everything else about that department's schema alone.

Introducing conformed dimensions without breaking existing reports. The mechanism that makes this safe is a compatibility view: create a view with the OLD dimension's name and column names, backed by the NEW conformed dimension underneath, so any report or dashboard query that has not been touched keeps running against the old names while the underlying table is now the shared one. Reports get migrated to query the conformed dimension directly (picking up any new attributes it offers) on the team's own schedule, not as a synchronized cutover, and the compatibility view is retired only once nothing depends on it anymore.

Sequencing the migration. For the three new departments, treat this the same way you would size a bus matrix for a greenfield enterprise build (see the sales-orders-first reasoning used when a company plans from scratch): whichever new department's mart shares the most dimensions with the others should be built first against the now-conformed dimensions, so its build validates that the conformed dimensions actually generalize before a second and third department also depend on them.

Worked example

A minimal, runnable illustration of the compatibility-view mechanism:

CREATE TABLE dim_customer_conformed (
    customer_sk   BIGINT PRIMARY KEY,
    customer_id   VARCHAR,
    customer_name VARCHAR,
    region_code   VARCHAR      -- new attribute the conformed dimension adds
);
INSERT INTO dim_customer_conformed VALUES
  (1, 'CUST-100', 'Ada Lovelace', 'EMEA'),
  (2, 'CUST-200', 'Grace Hopper', 'AMER');

-- The original department's dimension name and column names, preserved as a view
-- backed by the new conformed table underneath:
CREATE VIEW dim_customer_v1 AS
SELECT customer_sk, customer_id AS cust_id, customer_name AS cust_nm
FROM dim_customer_conformed;

-- The department's existing report, unmodified, still returns correct results:
SELECT cust_id, cust_nm FROM dim_customer_v1 ORDER BY cust_id;

Executed, the unmodified report query returns [('CUST-100', 'Ada Lovelace'), ('CUST-200', 'Grace Hopper')], exactly what it returned before the migration, while a new report written directly against dim_customer_conformed can immediately use the new region_code attribute the old schema never had.

Trade-offs and pitfalls

The most damaging mistake is treating this as a single cutover weekend: forcing every existing report to move to the new conformed dimension at once maximizes the chance something breaks in production with no fallback. The compatibility view exists precisely so migration can happen gradually, dashboard by dashboard, with the old and new dimensions correct and consistent with each other for as long as both are in use. The second common mistake is skipping the conformance work for a "small" attribute mismatch (say, the old dimension calls it cust_nm and everyone assumes it obviously maps to customer_name), which is exactly the kind of undocumented assumption that produces the four-different-customer-dimensions problem a badly-managed enterprise rollout tends to create.

Issue tree (MECE) — top node: Decision on +15% subscription price

1. Revenue impact
   a) Price per unit effect (ARPU increase for unaffected customers)
   b) Volume effect (churn / downgrades / slowed acquisition)
   c) Net revenue = (ARPU change * retained base) + new business delta - churned MRR
2. Customer segmentation (mutually exclusive)
   a) By plan: Free / Basic / Pro / Enterprise
   b) By tenure: <3m, 3–12m, >12m
   c) By usage intensity: low / medium / high
   d) By contract type: monthly vs annual
3. Competitor & market response
   a) Immediate competitor pricing moves
   b) Feature parity / perceived value changes
   c) Market elasticity benchmarks
4. Legal / contract implications
   a) Fixed-term contracts and renewal rules
   b) Notice requirements and price-change clauses
   c) Refund/penalty exposure and regulatory constraints (regions)
5. Operational implementation
   a) Billing system changes and rollback plan
   b) Communication plan / messaging & timing
   c) Customer support load & playbooks for objections
   d) Monitoring KPIs & rollback triggers

Three data analyses to estimate elasticity & revenue impact

1. Historical price-change quasi-experiment

• Data: past internal price changes, MRR, churn, new ARR, customer attributes (SQL from billing DB)
• Method: difference-in-differences (compare cohorts before/after vs control segments not affected) to estimate % change in quantity demanded per % price change.

2. Cohort survival / churn regression

• Data: subscription events, usage metrics, demographics (billing + product analytics)
• Method: Cox/survival model or logistic regression with price as covariate and controls (tenure, usage). Predict incremental churn from +15%.

3. Conjoint / A/B price experiment (if possible) or demand curve estimation via randomized pricing

• Data: run an A/B test on a representative customer sample (or use historical promo elasticity), collect conversion, churn, LTV
• Method: estimate short-term conversion lift/loss and long-term retention using uplift modeling; translate to revenue impact using cohort LTV simulation.

For each: produce expected-range scenarios (best/likely/worst), run sensitivity analysis, and surface key metrics for monitoring: ARPU, churn rate, net MRR change, CLTV, and support volume.

Direct answer

Psychological safety, in a mentoring relationship, is a mentee's confidence that they can ask a question, admit a mistake, or push back on something without it costing them standing or opportunity. It's built through small, consistent moments early on, and it's genuinely tested the first time the mentee takes a visible risk and sees how you respond.

Concrete early actions

• Name failure modes yourself first. Mentioning a mistake you made in a similar situation signals that admitting error is normal here, not a one-way expectation.
• Model uncertainty openly. Say "I don't know, let's find out" instead of bluffing, so not-knowing reads as acceptable.
• Treat early mistakes as expected, not exceptional. React to a mistake by focusing on the fix and what it reveals, not on assigning blame.
• Be consistent between casual moments and anything formal. If private conversations are open but a formal review contradicts them, trust breaks immediately.
• Give credit publicly, give hard feedback privately. This is the pattern most people are watching for even if they never say so.
• Agree explicitly that disagreement is welcome, and actually respond well the first time it happens.

Worked example

Early in a relationship, a mentee admitted they'd made a mistake that caused some rework. The response focused entirely on understanding what happened and fixing it, walking through the reasoning openly rather than assigning blame, and treating it as a useful, expected part of learning. In the sessions that followed, the mentee started surfacing problems earlier and asking more pointed questions, rather than waiting until something couldn't be hidden.

Trade-offs and pitfalls

A common mistake is treating psychological safety as a one-time opening statement ("feel free to ask me anything") rather than an ongoing pattern that has to survive contact with a real mistake. The mentee will judge safety retrospectively, based on what actually happened the first time they took a risk, not on what was said at the start. It's also worth not confusing psychological safety with lowered standards: it's about how failure is handled and discussed, not about removing accountability for the work.

Direct answer
Exposing analytics outside your own BI (business intelligence) tool, whether embedding a dashboard in a customer-facing product or offering an API, means the access-control boundary that used to be "whoever has a login to our internal BI tool" now has to be enforced explicitly at the edge, with strong authentication, strict per-customer data isolation, and real thought about what's safe to mask or exclude now that this data is leaving your internal environment.

Structured elaboration
Authentication and authorization: for an API, this typically means OAuth (a standard token-based authorization protocol) or scoped service tokens rather than a shared secret, with rate limiting to prevent abuse; for an embedded dashboard, single sign-on (SSO) integration with the host application and token rotation so an embedded session can't be hijacked or reused indefinitely.

Data isolation for multi-tenant scenarios: the same row-level-security discipline applies here with higher stakes, since a leak here means one customer seeing another customer's data through your product, not just an internal access-control slip; this needs to be enforced at a layer that can't be bypassed by a misconfigured embed, ideally the warehouse or semantic layer, not just the embedding code.

Performance for external-facing use: caching and, where relevant, pre-rendering matter more here than for internal BI, since an external customer's expectations for load time in your product are different from an internal analyst's tolerance for a BI tool; pagination and precomputation for heavy reports keep response times reasonable without exposing the underlying warehouse to arbitrary external query load.

What to mask or exclude specifically because data is now external: internal-only fields (cost structure, internal notes, another customer's benchmark data used for context) that were fine for an internal analyst to see alongside a customer's data need to be explicitly excluded from anything customer-facing, since the audience and trust boundary have fundamentally changed. Concretely, this includes PII (personally identifiable information) handling: masking, tokenization, or differential privacy for user-level detail, and a clear policy for what user-level detail is even appropriate to show externally versus only in aggregate.

Legal and privacy considerations: sharing metrics externally, even with the data's own "owner," the customer, can trigger data-sharing agreements, regional privacy regulation requirements, or contractual limits on what can be exposed, which need review before anything goes live, not after.

Auditing: a record of who accessed what through the external-facing surface, which matters more here than internally since an external access anomaly (a token being used from an unexpected pattern) is a stronger signal of a real problem.

Worked example
A company embeds a usage-analytics dashboard into its own product so customers can see their own account's data, and separately offers a read-only API for customers who want to pull the same data into their own systems. For the embed: SSO ties the embedded dashboard's session to the customer's existing login in the host product, with short-lived, rotating tokens so a session can't be replayed later; row-level security at the warehouse layer ensures the underlying query can only ever return that specific customer's rows, regardless of what the embedding code requests, so even if the embed were misconfigured, no cross-customer leak is possible. For the API: requests authenticate via OAuth-issued, scoped tokens (a token only grants access to that customer's data), rate-limited to prevent one customer's automated pulls from degrading performance for others, and heavy report endpoints are paginated with precomputed daily summaries available for the common case rather than forcing every API call to hit the raw warehouse.

Trade-offs and pitfalls
A common mistake is treating an embedded dashboard's access control as "whatever the embedding code passes in" without a warehouse- or semantic-layer-level enforcement backing it up, which means a single bug in the embedding integration becomes a genuine cross-customer data leak rather than a contained failure; the same layered-defense principle from row-level security generally applies here with more urgency, since the audience is external. The other frequent gap is legal review happening too late: teams sometimes build the full technical embedding or API solution before checking whether the specific data being exposed is even contractually or legally permissible to share in that form, which can mean re-architecting after the fact rather than designing the right constraints in from the start.

Federated queries let you read external S3 data directly from the warehouse's query engine without a separate ingestion step, which is attractive for ad-hoc, one-off, or exploratory analysis, but it trades ingestion effort for ongoing query-time cost and weaker governance every time that external data is touched.

Performance trade-offs

A federated query typically has no warehouse-native statistics, indexing, or clustering benefits for the external data (the warehouse cannot apply the same optimizations it would to its own managed tables), and pays the read latency of the external storage system directly on every query, so repeated queries against the same external dataset are each paying that cost fresh, unlike an ingested copy that only pays the transfer cost once.

Cost trade-offs

Federation avoids the storage duplication and ingestion-pipeline cost of copying the data in, which is a real saving for data queried rarely or only once. But for data queried repeatedly, the cumulative federated-query cost (repeated external reads, no benefit from the warehouse's caching or materialized-view mechanisms the way an ingested table would get) can exceed the one-time cost of ingesting it, especially once you account for how much cheaper a warehouse's own storage and compute usually are per query compared to a federated read across a network boundary.

Governance trade-offs

Federated data generally sits outside the warehouse's own access-control, lineage, and data-quality tooling, since it is not truly "in" the warehouse, which makes it harder to apply consistent row-level security, audit who queried what, or trust that the external source has not silently changed shape or been deleted out from under a running query.

Criteria for choosing

Federation is appropriate for genuinely one-off or rarely-repeated exploratory queries against data that is not expected to become a regular part of the analytics workload, where the ingestion pipeline's setup cost would not be recouped by future use. Ingest first when the data will be queried repeatedly (the cumulative federated cost will likely exceed one-time ingestion), when it needs to participate in the warehouse's governance and access-control model consistently with the rest of your analytics data, or when query performance against it matters enough that the warehouse's own optimizations (partitioning, clustering, materialized views) are worth having.

Trade-offs and pitfalls

The common mistake is defaulting to federation for convenience and letting a dataset that was meant to be a one-off exploration quietly become a repeatedly-queried, business-critical source without anyone deciding to formally ingest it; put a lightweight review trigger in place (a usage-frequency threshold, or a periodic audit of federated-query volume by source) so a dataset that has organically become a repeat dependency gets promoted to a properly ingested and governed table rather than staying an ungoverned federated read indefinitely.

Direct answer
A chart using 12 colors, 3 axes, overlapping lines, no axis labels, and a rainbow palette fails on nearly every principle of clear encoding at once; the fix is to cut the series count, pick one axis per unit of measurement, label everything directly, and replace the rainbow palette with a small categorical or sequential palette matched to the data's actual structure.

Structured elaboration
Six concrete problems and their fixes:

1. Too many series (12 colors): past about 6-8 distinct lines, colors become indistinguishable. Fix: keep the 3-4 series that matter, move the rest to "other" or a drill-down, or switch to small multiples (one mini-chart per series).
2. Three axes: more than two axes (and ideally just one) makes it impossible to know which line maps to which scale. Fix: one axis per unit; if units genuinely differ, use small multiples instead of overlaying.
3. Overlapping lines: dense overlap hides individual series. Fix: reduce series count (as above) or use a small-multiples grid.
4. No axis labels: the chart is uninterpretable without units and time range. Fix: label both axes with units and a time range in the title or subtitle.
5. Rainbow palette: implies false ordering and clashes visually. Fix: a categorical palette of 4-6 distinguishable hues for categories with no order, or a sequential palette for ordered/quantitative series.
6. No annotation of the key insight: even a clean chart still needs a headline for an executive briefing.

Worked example
A revised version for an executive briefing: keep this a time-series comparison (the data is inherently a trend over time), rendered as a decluttered multi-line chart, but with only the top 3 series by magnitude, a single y-axis, direct end-of-line labels instead of a legend, a 3-4 color categorical palette, axis labels with units, and one annotation naming the key takeaway (e.g. "Channel A overtook Channel B in March"). If the audience's actual question is a snapshot comparison rather than a trend (e.g. "who is winning right now"), a sorted horizontal bar chart of the same top 3-4 series is the better chart-type choice instead of a line chart.

Trade-offs and pitfalls
Cutting to 3-4 series means some information is genuinely lost; disclose that the remaining series were grouped into "other" rather than silently dropping them, and offer a drill-down link for anyone who needs the full breakdown.

Direct answer. Confirm the OR is defeating index usage by checking whether each branch, on its own, would use an index efficiently but the combined OR forces the optimizer to consider matches from either branch and fall back to a scan; the fix is to rewrite the OR as a UNION ALL of the individual branches, each independently indexable, or an IN-list where the branches are all equality checks on the same column.

Structured elaboration. Many optimizers can use an index efficiently for a single equality or range predicate, but a compound OR across DIFFERENT columns (or a mix of predicate shapes) often can't be satisfied by walking a single index in one pass, since the matching rows for each branch could live anywhere relative to each other in that index's ordering. Rewriting the OR as a UNION ALL of separately-filtered queries lets each branch use its own most-appropriate index independently, with the results combined afterward, rather than forcing one combined scan to somehow satisfy both conditions at once. When every branch of the OR is an equality check on the SAME column, an IN-list is the more natural and equally effective rewrite instead of a UNION ALL.

Worked example. I verified this rewrite preserves results exactly with a small dataset of three events (click, view, purchase) filtered for (type = 'click' OR type = 'view') AND created_at > '2025-01-01':

-- OR across the same column: index usage depends on the optimizer's transform ability
SELECT * FROM events
WHERE (type = 'click' OR type = 'view') AND created_at > TIMESTAMP '2025-01-01';

-- explicit UNION ALL: each branch can use its own index independently
SELECT * FROM events WHERE type = 'click' AND created_at > TIMESTAMP '2025-01-01'
UNION ALL
SELECT * FROM events WHERE type = 'view'  AND created_at > TIMESTAMP '2025-01-01';

Both forms return the same two matching rows (the click and view events, correctly excluding the purchase event), confirming the rewrite is correctness-preserving for this predicate shape.

Trade-offs and pitfalls. UNION ALL, not UNION, is the correct rewrite whenever the original OR's branches can't produce overlapping duplicate rows (as here, since a row can't simultaneously be type='click' and type='view'); using UNION instead would silently add an unnecessary deduplication pass. When branches CAN overlap and produce genuine duplicates under a UNION ALL rewrite, you need either a UNION (accepting its dedup cost) or an explicit condition making the branches mutually exclusive.

Complexity

This rewrite doesn't change the total rows scanned in the worst case, but it changes each branch from a potential full scan to an independent, appropriately-indexed lookup, which is where the real savings comes from.

Edge cases

If the OR's branches can produce the exact same row (for example, an OR across ranges on the SAME column that overlap), a UNION ALL rewrite would duplicate that row in the output; verify the branches are genuinely mutually exclusive before choosing UNION ALL over UNION.

Direct answer

A data contract needs three layers: what the data looks like (schema, types, semantics), what service level the producer promises (freshness, availability, and a compatibility policy for future changes), and who is accountable when either breaks (ownership and escalation path). Enforcement means those layers are checked automatically at the moment a break would actually happen, before a producer ships a change and continuously against what is actually being served, rather than living only in a wiki page that nobody re-reads under deadline pressure.

Structured elaboration

Enforcement mechanisms, focused on when each one fires:

• Schema registry gate: the producer's build must pass a compatibility check against the registered schema before a change can ship, catching a breaking change before production, not after.
• Runtime validation: ingestion rejects or quarantines records that violate the contract instead of letting them flow through as silent nulls or coerced types.
• Consumer-driven checks: tests that fail the producer's build if a real consumer's stated expectations would break, so a producer is not only checking its own schema in isolation.
• Staged rollout of enforcement itself: new contracts start in alert-only mode and tighten to blocking mode over time, so enforcement does not halt unrelated work the first day it exists.

Worked example

A catalog team produces a shared product dataset (product_id, name, price, category, currency), consumed by a pricing service, a search index, and a recommendation pipeline. The contract specifies that price is always an integer in minor units (cents), category is one of a fixed enum, and only additive changes ship without notice, a renamed or removed field requires two weeks' notice. Enforcement: the catalog team's build runs a schema-compatibility check against the registry before any migration ships. If a change tries to remove category or switch price from integer cents to a float dollar amount, that check fails the build immediately, because both are breaking changes the contract explicitly covers, catching the problem before the recommendation pipeline (which assumes integer cents) would have silently mispriced results for every consumer downstream.

Trade-offs & pitfalls

Writing the contract as documentation only, without the registry and build-time checks, means it decays the first time someone ships in a hurry; a contract nobody's tooling reads is a contract nobody actually follows under pressure. Enforcing everything in blocking mode from day one, on an organization with no existing baseline compliance, halts unrelated work and burns trust in the contract itself; starting in alert-only or pilot mode and tightening over time avoids that. A contract that only covers schema shape and ignores semantics, a currency or unit change that does not change the field's type, can pass every schema check and still silently break every consumer that assumed the old meaning.

Instead of re-scanning the whole history every time you suspect drift, track the CUMULATIVE difference day by day and find the first date where that running total crosses your tolerance, which pinpoints where an investigation should start rather than requiring you to eyeball a long time series.

Approach (verified by execution)

```sql
WITH diffs AS (
SELECT d, source_amt, target_amt, source_amt - target_amt AS daily_diff
FROM recon
),
cum AS (
SELECT d, daily_diff,
SUM(daily_diff) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_diff,
SUM(source_amt) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_source
FROM diffs
)
SELECT d, cum_diff, cum_source, ROUND(cum_diff * 100.0 / cum_source, 2) AS pct_cum_diff
FROM cum
WHERE cum_diff * 1.0 / cum_source > 0.02
ORDER BY d LIMIT 1;
```

Worked example (verified by execution)

Over five days with small, growing daily discrepancies (0, 1, 2, 50, 100 out of roughly 1000 per day), the cumulative percentage difference crosses a 2% threshold for the first time on day five (3.06%), even though day four's own daily discrepancy (50) was already the visibly larger jump; `LIMIT 1` on the ordered result correctly returns exactly that first crossing date rather than every subsequent day that also exceeds the threshold.

Trade-offs and pitfalls

A cumulative running total is sensitive to the STARTING point of the window: if you run this over "all history," a genuinely resolved discrepancy from months ago can still show up as inflating today's cumulative percentage, even though the two systems have agreed perfectly every day since; consider resetting the cumulative sum at a meaningful reconciliation checkpoint (like the last date both systems were confirmed to agree) rather than always summing from the dawn of time. Once you have the first-crossing date, that becomes the natural starting point for a narrower, partition-level investigation into what changed on or just before that specific day.