Lyft Business Intelligence Analyst Interview Preparation Guide (Mid-Level)

Direct answer
Code review becomes a coaching tool the moment you separate what has to change before this merges from what's worth teaching, and handle each differently, since blocking mixes poorly with explaining. What counts as the important risk to teach toward also shifts by what's being reviewed: correctness and style for typical application code, reproducibility and data leakage for ML work, and blast radius for infrastructure changes.

Separate blocking feedback from teaching feedback

• Mark comments explicitly as blocking versus non-blocking (or use a similar convention), so the author isn't left guessing what actually has to change before merge. Teaching comments that aren't required for merge belong in the non-blocking bucket, otherwise you either water down real teaching moments to keep the change unblocked, or block a mergeable change to make a point.
• Ask before you tell: a comment phrased as a question ("what happens if this list is empty?") invites the author to find the issue themselves, which teaches the underlying reasoning; a comment phrased as an instruction just transmits the fix.

What "the important risk" means shifts by artifact type

• Typical application code: the coaching focus is usually correctness, readability, and test coverage; the failure mode being taught against is a defect shipping or the next person not being able to follow the change.
• ML notebooks and experiment configs: the review risk is different in kind, not just degree. The critical things to check and teach toward are reproducibility (is the seed pinned, is the environment specified, can someone else get the same result) and data leakage (does the training data have any path back to the evaluation set, directly or through a shared preprocessing step). A notebook can be clean, readable code and still be dangerously wrong for reasons that have nothing to do with code style.
• Terraform and other infrastructure-as-code changes: the review risk is blast radius, not defects in the traditional sense. A small, correct-looking diff can still be catastrophic if it touches a shared resource or removes a safeguard. Coaching here means teaching someone to ask what does this affect beyond what's in the diff before asking is this line correct.

Making it a genuine teaching moment, not just a gate

• When there's something worth teaching, don't just fix it in the comment; explain the why, and where useful, point to a real example elsewhere in the codebase rather than a generic principle.
• For anything too deep to unpack asynchronously in a comment thread, offer a short pairing session instead of a long comment chain; some things teach faster live than in writing.
• Close the loop: after a pattern comes up more than once for the same person, raise it directly in a 1:1 rather than only ever surfacing it inside individual review threads, so it becomes a recognized growth area instead of a recurring surprise.

Worked example
Reviewing a teammate's change that added a new model training script, the code itself was clean and well-tested in the conventional sense. The actual coaching moment was elsewhere: the evaluation split was built after a preprocessing step that had already seen the full dataset, which meant the reported accuracy was optimistic in a way unit tests would never catch. Rather than just fixing the split order and moving on, the comment walked through why that ordering matters (what leakage actually does to the reported number) and pointed to another script in the repo where the split happened correctly, before the shared preprocessing step. That change did get blocked, since the leakage was a real correctness issue, but the teaching part was the explanation of why, not the fact that it was blocked.

Trade-offs and pitfalls

• Making every comment a teaching moment, including on merge-blocking issues, slows delivery and can read as review turning into a lecture; save the deeper explanations for the genuinely worthwhile ones and keep routine fixes routine.
• Applying the same review lens (say, defect-finding) to every artifact type misses the risks that matter most for that artifact; a Terraform change reviewed like application code will pass style and correctness checks while missing blast radius entirely.
• If teaching moments only ever show up as isolated review comments and never get named directly to the person as a pattern, growth stays implicit and slower than it needs to be.

Direct answer

A schema mismatch is a structural change, a field's presence, nesting, or shape has changed, for example a JSON field that sometimes arrives as an array and sometimes as a scalar. A data-type inconsistency is narrower: the field's structure is the same, but the type of the value itself is wrong, for example a numeric field arriving as the text string "42" instead of an integer.

Structured elaboration

Schema mismatches tend to cause hard failures: a strict loader that expects a scalar and receives an array will typically error out and refuse to load the row at all, which is disruptive but at least visible. Data-type inconsistencies are more dangerous precisely because they often do not fail loudly: many query engines will silently coerce a numeric-looking string during a comparison or aggregation, producing a technically-valid but wrong result rather than an error, which is how a type inconsistency turns into a silently miscalculated aggregate rather than a visible load failure.

Worked example

A structural schema mismatch: an events payload's properties field is sometimes {"tags": ["a","b"]} (an array) and sometimes {"tags": "a"} (a bare scalar) depending on which client SDK version sent it; a strict schema loader rejects the second form outright, a visible, fail-fast failure. A data-type inconsistency: a discount_pct column is declared numeric but a small fraction of rows arrive as the string "10%" instead of 0.10; depending on the query engine, SUM(discount_pct) might silently coerce the strings that happen to parse and skip or error on the ones that don't, producing an aggregate that is quietly wrong rather than an obvious failure, since nothing about the query itself errored. A silently broken join is the third named consequence, and it usually comes from the same type-inconsistency root cause rather than the schema-mismatch one: an order_id column is stored as an integer (42) in the orders table but arrives and is stored as the text string "42" in a newer order_events table after an upstream change. An equi-join ON orders.order_id = order_events.order_id does not error, most query engines simply find zero matches for rows where the compared types don't line up as expected, so the join silently returns fewer rows than it should (or, depending on the engine's coercion rules, matches inconsistently for some rows and not others). The downstream report quietly under-counts, with no failed load and no error anywhere in the pipeline to point at.

Trade-offs and pitfalls

The practical consequence of this distinction is where you invest detection effort: schema mismatches are largely self-reporting because they tend to break something loudly, so a basic schema-validation-at-load-time check catches most of them for free. Type inconsistencies need active, targeted detection (a cast-and-flag check, or a profiling pass looking for unexpected non-numeric values in a numeric-typed column) precisely because the default behavior of most systems is to silently coerce rather than fail, which is exactly the behavior that makes them dangerous.

Direct answer

I start from the initiative's goals and work outward: who is directly affected by the outcome, who has to approve or fund it, who has to execute it, and who will be blamed if it goes wrong. Those four questions surface almost everyone that matters, and I deliberately look past the org chart for the last group.

Structured elaboration

1. Start with the obvious names. The sponsor, the immediate delivery team, and anyone explicitly named in the project charter.
2. Trace dependencies, not titles. I look at who has to change something (a system, a process, a policy) for this to succeed, since that person is a stakeholder even if nobody invited them. Concrete sources for this: org charts (as a starting point, not the final word), CRM or project records showing who has historically owned related decisions, and recurring-attendee patterns in planning meetings.
3. Look for the quiet approvers. Legal, security, finance, and compliance rarely show up in early conversations but can stop a launch cold. I ask "who has to sign off" explicitly rather than assuming I already know.
4. Find the informal influencers. Recurring meeting attendees, people whose name keeps coming up when others hedge ("I'd want to check with X"), and prior decision owners on adjacent work are all signals of real influence that doesn't show up on an org chart.
5. Segment engagement, don't treat everyone the same. Once I have the list, I classify by how much they need to be consulted versus simply informed, so my time goes where it matters (see the power/interest grid discussion for the mechanics of that classification: in short, a 2x2 that plots how much power someone has over the outcome against how much interest they have in it, sorting people into engagement styles like manage closely, keep satisfied, keep informed, or monitor).

Worked example

For a project re-architecting a shared data-ingestion layer, the obvious stakeholders are the analytics team requesting the change and my own engineering lead. Tracing dependencies surfaces four producer teams who will need to change how they publish data, and two downstream consumer teams whose dashboards will briefly go stale during cutover. Asking "who has to approve" surfaces a data-governance reviewer nobody mentioned in the kickoff. Watching who gets referenced repeatedly in planning conversations ("we'd need X's sign-off on schema changes") surfaces a senior engineer with no formal authority over the project but effective veto power because their team owns the shared library everyone depends on.

Trade-offs and pitfalls

The common failure is stopping at the first list and treating it as complete, which is how "hidden" stakeholders surface late and expensively. The other common failure is over-including: mapping everyone remotely touched by the change and giving them all the same engagement, which burns your own time and theirs. The map should change your ACTIONS (who you talk to, how often, how much detail), not just exist as a document.

Direct answer

Daily active users divided by monthly active users, expressed as a percentage, is the DAU/MAU ratio, and it is commonly read as a stickiness signal: the higher the ratio, the larger the share of a product's monthly audience that comes back on a typical day. A ratio around 50% is often cited as very sticky (roughly half the monthly base shows up daily), while a ratio in the low single digits suggests most users engage only occasionally within a month.

Structured elaboration

The ratio is attractive because it is simple to compute and easy to explain, but it silently assumes that daily engagement is the right cadence for the product being measured. That assumption breaks for any product whose natural usage rhythm is not daily. A booking or travel product, for example, is used a handful of times a year by design, so even a healthy, engaged user base will show a DAU/MAU ratio that looks alarmingly low next to a social feed's ratio, even though the products are not failing at the same thing.

The ratio can also mislead in the other direction. A small subset of highly frequent users, or a population of bots and automated test traffic, can push the ratio up while the majority of the monthly base barely engages, so a healthy-looking DAU/MAU number does not by itself rule out a lopsided or contaminated user base. Seasonality causes the same kind of misleading movement: a ratio that dips every weekend for a workplace tool is not evidence of declining stickiness, it is evidence that the tool is used at work.

Worked example

For an infrequent-use product type such as a travel-booking app, DAU/MAU is a misleading stickiness signal. Consider a travel app with 100,000 monthly active users where each user books, at most, a handful of trips a year and opens the app mainly around those trips; on a typical day only a small fraction of that base has any reason to open the app at all, so a DAU/MAU ratio of, say, 2 to 3 percent can be entirely consistent with a healthy, retained user base rather than a sign that the product is failing to engage people. The metric that better complements DAU/MAU for this product type is something like trips-booked-per-active-user-per-year, or repeat-booking rate within a 12-month window, because it measures engagement at the cadence the product is actually used, rather than forcing a daily lens onto an inherently infrequent behavior.

Trade-offs and pitfalls

Before trusting a DAU/MAU trend, check that the definitions of "active" for both the daily and monthly windows are consistent (the same qualifying event, the same timezone convention), since a mismatch can move the ratio without any real change in user behavior. It is also worth pairing DAU/MAU with a distribution view, not just the single ratio, since a ratio computed from an average can hide a bimodal population of very frequent and very infrequent users that a single summary number flattens into an unremarkable middle.

Direct answer

When a client wants to independently verify your result, the deliverable is not just the finding, it's a minimal, self-contained package they can rerun themselves: the code that produced the numbers, a description of how to get equivalent data (or a synthetic/sanitized stand-in for it), and clear enough documentation that someone outside your team can execute it without you in the room.

Structured elaboration

1. Decide what is actually reproducible versus what has to stay described.
The code and the analysis logic should always be reproducible in full. The underlying data usually cannot be handed over as-is if it contains customer PII (personally identifiable information, such as names, emails, or account numbers), proprietary business data, or anything covered by a data-sharing agreement. The fix is not to skip reproducibility, it's to separate 'reproduce the LOGIC exactly' from 'reproduce the DATA exactly,' and hand over an artifact for each: the real code, plus either (a) a clearly labeled synthetic dataset with the same schema and similar statistical properties, or (b) precise instructions for how the client can pull the equivalent data from their own systems if they have access to comparable sources.

2. Package the minimal artifact set.
At minimum: the analysis code itself (scripts or a notebook, not just a slide describing the method), a requirements/environment specification (exact library versions, because 'it ran on my machine' is not reproducible), a data dictionary describing every column the code expects, and a short README describing the exact sequence of steps from raw input to the reported number. Anything beyond this (internal dashboards, ad hoc exploration) is noise the client did not ask for and should not be included.

3. Protect sensitive data without breaking reproducibility.
Three common patterns, useful in combination: synthetic data generation that preserves the schema and rough distributional shape of the real data without being traceable to real records; a sanitized sample where identifying fields are removed or hashed but the analytical structure is intact; or a documented data-access pattern (exact query, exact filters, exact time window) the client can run against their own copy of the data if they already have access to it. State explicitly which of these you used and why, so the client understands they are validating the LOGIC, and, if they used the synthetic data, that the specific NUMBERS may differ from production.

4. Document execution steps as if the reader has never seen the project.
A numbered list of exact commands (not prose describing what to do) is the standard that actually gets used: install X, run script Y with these arguments, expect output Z. Include the expected output or a checksum/summary statistic so the client knows immediately if their run matches yours or has diverged (and if it diverges, what that would signal, e.g. an environment or data mismatch rather than a code bug).

Worked example

A consulting analytics team tells a retail client that a new pricing rule increased average order value by 6.2%. The client's finance team is skeptical and wants to run it themselves. The team delivers: (1) the exact SQL/Python transformation code that computes average order value pre- and post-change, version-pinned to specific library versions in a requirements file, (2) a synthetic transactions dataset of 50,000 rows generated to match the real schema and the real data's approximate order-value distribution (mean and spread matched, no real customer identifiers), with a clear README stating this is synthetic and will not reproduce the exact 6.2% figure, only the method, (3) a one-page data dictionary defining every column, and (4) a documented query pattern the client's own analysts can run against their live warehouse, with the exact date range and filters used, so they can reproduce the real 6.2% figure against their own data if they choose to. The client's team runs the synthetic-data version, confirms the logic matches what was described, and separately reruns the documented query against their own warehouse to confirm the real number.

Trade-offs and pitfalls

• The most common failure is handing over a notebook that ran once on someone's laptop with no environment pinning; without exact versions, 'reproducible' code frequently produces silently different results months later.
• Do not confuse 'gave them the code' with 'gave them something they can run'; if the client needs data access, credentials, or infrastructure you did not describe, it is not actually reproducible for them.
• Synthetic data is a compromise, not a substitute for the real validation path; always be explicit that synthetic-data reruns validate the METHOD, and offer the real-data query pattern as the path to validating the actual NUMBER.
• Over-scoping the package (handing over your entire internal codebase or every exploratory notebook) creates a support burden and a larger attack surface for something to go wrong; keep the package to exactly what reproduces the stated result.

Direct answer

Integrate semi-structured or unstructured sources (JSON events, support tickets, web logs) into an analytics warehouse by landing them in their native shape first, then extracting a small, stable set of frequently-queried fields into real typed columns while keeping the full original payload accessible for anything not yet promoted, so both BI reporting and model-feature pipelines can rely on the same underlying data without either being blocked on a full upfront schema.

Structured elaboration

• Land raw, extract selectively: ingest the raw JSON/text payload into the warehouse largely as-is (a JSONB or VARIANT column, or a raw-text column for unstructured text), and only promote specific fields to their own typed, indexed columns once their query pattern is known and stable; this avoids blocking ingestion on a schema decision for every field a source might ever emit.
• Serving BI reporting: BI tools generally need typed, indexable columns for fast filtering and aggregation, so the promoted-fields layer (not the raw payload) is what most dashboards should query against; a view or a curated table exposes the promoted fields plus any commonly-needed derived fields (a sentiment score computed from support-ticket text, say) without every analyst needing to know how to query the raw JSON.
• Serving model features: ML feature pipelines often need BOTH the promoted structured fields AND access to the raw payload (to derive new features later that weren't anticipated at promotion time, such as extracting a new entity from raw ticket text), so keeping the raw payload retained and queryable, not discarded after extraction, matters specifically for this consumer.
• Unstructured text specifically (support tickets, free-text logs): typically needs an additional processing step beyond simple field extraction, such as NLP-derived structured fields (sentiment, extracted entities, a category classification) computed by a pipeline and stored as their own columns, since the raw text itself isn't directly filterable or aggregable the way a JSON field's value is.

Worked example

For web-log JSON events with a properties payload that varies by event type: a small set of near-universal fields (user_id, event_type, event_time) gets promoted to real columns immediately, since virtually every downstream consumer filters or groups by them; category-specific fields inside properties (say, a product_id present only on purchase-type events) stay in the JSON payload until a specific dashboard or feature pipeline demonstrates a stable, recurring need for it, at which point it's promoted too. This staged-promotion approach means the schema itself becomes a living record of which fields have proven valuable enough to warrant first-class treatment, rather than a guess made once at ingestion time.

Trade-offs and pitfalls

• The main risk of landing everything raw with no promotion discipline at all is that every BI query and every feature pipeline ends up independently parsing the same JSON paths with slightly different conventions (one query treats a missing field as NULL, another as an error), producing subtly inconsistent results across consumers for what should be the same underlying fact; promoting a field is what gives it one canonical, shared definition.
• The main risk of promoting too aggressively (extracting every field immediately) is reintroducing the schema-on-write rigidity this whole approach was meant to avoid, forcing a migration every time a new source or a new field shows up, which is exactly the friction semi-structured landing was chosen to prevent.
• Retaining the raw payload alongside the promoted fields has a real storage cost, but discarding it prematurely forecloses future feature engineering or debugging that needs to go back to the original, unprocessed data, which is usually the more expensive mistake of the two.

Requirements:

• Sales reps see only rows for their assigned territory.
• Managers see aggregated performance for all territories they manage (no individual-level PII).
• Easy admin maintenance and auditable mapping.

Design overview:

1. Security mapping table (in the data model)

• Table: UserTerritoryMap(UserPrincipalName, TerritoryID, Role) where Role ∈ {Rep, Manager}.
• Keep as imported table refreshed from HR/CRM or Azure AD group sync.

2. RLS implementation (model-level)

• Create two roles in Power BI: RepRole and ManagerRole.
• RepRole DAX filter on Sales table (or Territory table):

// RepRole: filter territories to those mapped to the current user
[TerritoryID] IN
    SELECTCOLUMNS(
        FILTER(UserTerritoryMap, UserTerritoryMap[UserPrincipalName] = USERPRINCIPALNAME() && UserTerritoryMap[Role] = "Rep"),
        "T", UserTerritoryMap[TerritoryID]
    )

• ManagerRole: allow territories they manage but mask row-level details; recommend creating a manager view table that aggregates (TerritoryID, TotalSales, NumOrders, AvgMargin) and applying:

// ManagerRole applied to ManagerView (if using table-level roles)
TRUE()

Then build report visuals pulling from ManagerView to prevent drilling into raw rows.

3. Workspace/group-based limits

• Restrict report Author/Member permissions in workspace; only admins can edit RLS roles. Use app publishing to distribute; do not give build permissions to broad groups.

Testing & validation:

• Use Power BI Desktop “View As” to test RepRole and ManagerRole with several USERPRINCIPALNAME() values; validate filters and aggregated measures.
• Test with sample users: rep with multiple territories, manager of multiple reps, user without mapping (should see no data).
• End-to-end: publish to a test workspace and use “Test as role” in Service; verify no export of underlying data (disable export if needed).
• Audit: enable dataset usage and RLS change tracking; log mapping table updates and periodic reconciliation against HR source.

Best practices:

• Keep mapping table authoritative and automate refresh.
• Prefer aggregated tables for managers to prevent accidental row exposure.
• Document roles, owners, and run quarterly audits.

Direct answer

When time is fixed and resources are limited, the job is to consciously choose what gets compressed rather than let everything degrade evenly. A strong answer states a specific commitment, explains how the work was triaged and sequenced, names a non-negotiable quality bar that was protected, and names what was deliberately traded away, and why that trade was made openly rather than discovered later.

Structured elaboration

1. Restate the commitment concretely: a fixed date and a fixed scope ask, not a vague sense of being busy.
2. Organize the work by triage: split it into what is truly required versus what is nice to have, find the real critical path (the one piece that, if slow, delays everything downstream of it), and start the riskiest or most uncertain piece first rather than last.
3. Decide upfront which quality bar is non-negotiable (correctness of the numbers, no crashes, no security gaps) versus which corners are safe to cut (polish, an edge case, doing something manually once instead of automating it), and make that decision explicitly rather than rationalizing it after the fact.
4. Say the trade-off out loud to whoever owns the outcome before making it, something like "I will hit the date by doing this step manually and skipping that piece of polish," so it lands as a decision rather than a surprise.
5. Put a fast, cheap check on the corners that were cut, so "quality intact" is verified rather than assumed.

Worked example

A dashboard needed to go in front of leadership two days before a board meeting, showing weekly signups by acquisition channel. The business question was fixed and non-negotiable: leadership needed this specific view by this specific date.

There were three candidate data sources: a marketing spend export, the core signups table, and a partner-referral spreadsheet maintained by hand. Only two of the three could be reliably validated in time. Rather than delaying the whole dashboard for the third, or quietly showing all three as if equally final, the call was to ship with the two verified sources live and label the partner-referral numbers "preliminary, refreshing next week."

Work was organized so the two clean pipelines were built in parallel on day one, with a teammate taking one source. Day two went to a fast manual spot-check: comparing the dashboard's aggregated totals against a raw data pull for two weeks already known to be correct, rather than building a full automated test suite that would not have finished in time anyway. The visualization itself reused a chart type already standard in the team's other dashboards, so the limited time went into verifying the numbers rather than learning new tooling. A stakeholder asked late in the process for a fourth metric to be added; that request was declined explicitly, with a note that it would come in the next iteration, rather than squeezed in unverified.

Trade-offs and pitfalls

A common pitfall is describing "I worked longer hours" as the trade-off. That is not a decision, it is the absence of one, and most interviewers read it as a sustainability red flag rather than a strength. A second pitfall is quality being silently sacrificed, shipping a number you privately doubted, instead of explicitly flagged the way the preliminary partner-referral figure was here. A third, subtler pitfall is failing to recognize when the right call was actually to escalate for more time or a smaller scope rather than absorb the whole gap yourself; a genuine trade-off made with the stakeholder's knowledge is very different from quietly missing a quality bar and hoping nobody notices.

Direct answer

Match records between the two systems on a natural key, then classify every unmatched or disagreeing pair with an explicit set of CASE/WHEN rules, in a fixed priority order: same amount but different period is a timing difference, different currency but equal after FX conversion is a currency difference, a small residual is rounding, and anything left over is a true mismatch. A single "total discrepancy" number collapses all of these into one figure that tells an investigator nothing about where to look first.

Structured elaboration

1. Key alignment: pick (or build) a natural key both systems share, such as invoice_id. If the two systems do not share a clean key (see fuzzy-matching below), this whole approach has to start from a matching step instead.
2. Dedup / uniqueness guard: before joining, aggregate each side down to one row per key (GROUP BY invoice_id, account_id, period, currency, amount, carrying a COUNT(*) AS posting_count) and treat posting_count > 1 as a signal to investigate, not something to pass through. A FULL OUTER JOIN enforces no uniqueness on its join key: a retried or duplicate GL posting for the same invoice_id fans out against billing's single row and produces two exact_match rows for one real transaction, silently double-counting it in any downstream sum. This is a distinct failure mode from the late-adjustment case in point 4 below (a genuine duplicate of the same transaction, not a correcting entry). See the adversarial check after the worked example, which reproduces this and shows the guard fixing it.
3. Full outer join: join billing to the general ledger on that key with a FULL OUTER JOIN, so rows present on only one side surface as their own bucket (missing_in_gl / missing_in_billing) rather than being silently dropped by an inner join.
4. Ordered classification: apply the bucket rules from most to least specific: exact match first, then timing (equal amount, different period), then currency/FX (different currency, equal after conversion), then rounding (tiny residual), then true mismatch as the catch-all. Order matters the same way it does in a CASE expression: a rule checked too late never fires because an earlier, looser rule already claimed the row.
5. Alternate reconciliation angles worth naming explicitly, beyond the four core buckets:
   • Fuzzy matching: when the two systems do not share a clean natural key (different invoice numbering, or one side batches multiple billing lines into a single GL posting), exact-key joins miss real matches entirely. That case needs a similarity-based match first (amount plus date window plus account, or a normalized reference string), producing a matched-pair candidate set before the same bucket logic can run on top of it.
   • Hierarchy mapping: billing and GL frequently model the account structure at different granularity, billing keyed at the customer/subsidiary level, GL rolled up to a parent cost center. Reconciling at the wrong level either double-counts or drops legitimate entries; the join key needs to go through an account-hierarchy mapping table before comparison, not assume a 1:1 account match.
   • Idempotency-breaking late adjustments (idempotency: re-running the same reconciliation on the same data should always produce the same result): a correcting entry posted after the original transaction (a credit memo, a late GL adjustment) shows up as an unmatched extra row and gets naively bucketed as missing_in_billing or a true_mismatch, when it is really resolving an earlier discrepancy, not creating a new one. The worked example below reproduces exactly this case.

Worked example (executed, sqlite3, FULL OUTER JOIN)

CREATE TABLE billing (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO billing VALUES
  ('INV001', 'A1', '2025-01', 'USD', 1000.00),
  ('INV002', 'A1', '2025-01', 'USD', 500.00),
  ('INV003', 'A2', '2025-01', 'USD', 300.00),
  ('INV004', 'A2', '2025-01', 'USD', 800.00),
  ('INV005', 'A3', '2025-01', 'USD', 150.00),
  ('INV007', 'A4', '2025-01', 'EUR', 100.00);

CREATE TABLE gl (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO gl VALUES
  ('INV001', 'A1', '2025-01', 'USD', 1000.00),
  ('INV002', 'A1', '2025-02', 'USD', 500.00),
  ('INV003', 'A2', '2025-01', 'USD', 299.99),
  ('INV004', 'A2', '2025-01', 'USD', 750.00),
  ('INV006', 'A3', '2025-01', 'USD', 90.00),
  ('INV007', 'A4', '2025-01', 'USD', 109.00),
  ('INV004-ADJ', 'A2', '2025-02', 'USD', 50.00);  -- a LATE correcting adjustment for INV004, GL-only

CREATE TABLE fx_rates (currency TEXT, period TEXT, rate_to_usd NUMERIC);
INSERT INTO fx_rates VALUES ('EUR', '2025-01', 1.09);

WITH matched AS (
  SELECT COALESCE(b.invoice_id, g.invoice_id) AS invoice_id,
    b.period AS billing_period, g.period AS gl_period,
    b.currency AS billing_currency, g.currency AS gl_currency,
    b.amount AS billing_amount, g.amount AS gl_amount, fx.rate_to_usd
  FROM billing b
  FULL OUTER JOIN gl g ON b.invoice_id = g.invoice_id
  LEFT JOIN fx_rates fx ON fx.currency = b.currency AND fx.period = b.period
)
SELECT invoice_id, billing_amount, gl_amount,
  CASE
    WHEN billing_amount IS NULL THEN 'missing_in_billing'
    WHEN gl_amount IS NULL THEN 'missing_in_gl'
    WHEN billing_currency = gl_currency AND ROUND(billing_amount,2) = ROUND(gl_amount,2)
         AND billing_period = gl_period THEN 'exact_match'
    WHEN billing_currency = gl_currency AND ROUND(billing_amount,2) = ROUND(gl_amount,2)
         AND billing_period <> gl_period THEN 'timing_difference'
    WHEN billing_currency <> gl_currency AND rate_to_usd IS NOT NULL
         AND ABS(ROUND(billing_amount * rate_to_usd, 2) - gl_amount) <= 0.02 THEN 'currency_fx'
    WHEN billing_currency = gl_currency AND ABS(billing_amount - gl_amount) <= 0.02
         AND ABS(billing_amount - gl_amount) > 0 THEN 'rounding'
    ELSE 'true_mismatch'
  END AS bucket
FROM matched
ORDER BY invoice_id;

Result (8 rows):

Bucket summary: exact_match=1, timing_difference=1, rounding=1, true_mismatch=1, missing_in_gl=1, missing_in_billing=2 (INV006, INV004-ADJ).

This is the idempotency-breaking late adjustment case, reproduced directly: INV004-ADJ gets bucketed identically to INV006 (both missing_in_billing), but they mean opposite things. INV006 is a genuine unexplained entry with no billing counterpart. INV004-ADJ is a $50.00 correcting entry, and $750.00 (the original GL amount for INV004) plus $50.00 (the adjustment) equals exactly $800.00, the billing amount for INV004. The naive bucket rules alone would report two open discrepancies (INV004 as true_mismatch, INV004-ADJ as missing_in_billing) when the underlying reality is one resolved discrepancy. A production version of this classifier needs an adjustment-linking step (match adjustment rows back to the invoice_id they correct, typically via a reference field, and net them against the original before classifying) so late corrections do not get double-counted as new problems.

Adversarial check (executed, sqlite3): duplicate GL postings double-count without the guard

The dedup guard in point 2 above is not decorative. Seed a single billing row and a GL side that received the same posting twice (a retried write, same invoice_id, same amount, same period), then run the classification query without the guard:

CREATE TABLE billing (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO billing VALUES ('INV001', 'A1', '2025-01', 'USD', 1000.00);

CREATE TABLE gl (invoice_id TEXT, account_id TEXT, period TEXT, currency TEXT, amount NUMERIC);
INSERT INTO gl VALUES
  ('INV001', 'A1', '2025-01', 'USD', 1000.00),
  ('INV001', 'A1', '2025-01', 'USD', 1000.00);  -- duplicate/retried posting, GL-only

SELECT COALESCE(b.invoice_id, g.invoice_id) AS invoice_id,
  b.amount AS billing_amount, g.amount AS gl_amount,
  CASE
    WHEN b.amount IS NULL THEN 'missing_in_billing'
    WHEN g.amount IS NULL THEN 'missing_in_gl'
    WHEN ROUND(b.amount,2) = ROUND(g.amount,2) AND b.period = g.period THEN 'exact_match'
    ELSE 'true_mismatch'
  END AS bucket
FROM billing b
FULL OUTER JOIN gl g ON b.invoice_id = g.invoice_id
ORDER BY invoice_id;

Result (naive, no guard):

One real transaction, reported as two reconciled matches: a downstream SUM(gl_amount) over exact_match rows would come out $1000 too high. Now apply the guard from point 2, pre-aggregating each side to one row per key before the same join and classification logic:

WITH gl_dedup AS (
  SELECT invoice_id, account_id, period, currency, amount, COUNT(*) AS posting_count
  FROM gl
  GROUP BY invoice_id, account_id, period, currency, amount
),
billing_dedup AS (
  SELECT invoice_id, account_id, period, currency, amount, COUNT(*) AS posting_count
  FROM billing
  GROUP BY invoice_id, account_id, period, currency, amount
)
SELECT COALESCE(b.invoice_id, g.invoice_id) AS invoice_id,
  b.amount AS billing_amount, g.amount AS gl_amount, g.posting_count AS gl_posting_count,
  CASE
    WHEN b.amount IS NULL THEN 'missing_in_billing'
    WHEN g.amount IS NULL THEN 'missing_in_gl'
    WHEN ROUND(b.amount,2) = ROUND(g.amount,2) AND b.period = g.period THEN 'exact_match'
    ELSE 'true_mismatch'
  END AS bucket
FROM billing_dedup b
FULL OUTER JOIN gl_dedup g ON b.invoice_id = g.invoice_id
ORDER BY invoice_id;

Result (guarded):

One row, correctly classified, with gl_posting_count = 2 surfaced as a flag: a real system would route rows where posting_count > 1 to a "duplicate posting" review queue instead of silently collapsing them, since a duplicate could also indicate a genuine double-billing error rather than a harmless retry.

Trade-offs and pitfalls

• The bucket order in the CASE expression is a real correctness dependency, not stylistic: check exact_match before rounding (an exact match is a rounding difference of exactly zero and should not be relabeled), and check timing_difference and currency_fx before falling through to true_mismatch, or a row that is genuinely just late gets filed as an error and routed to the wrong queue.
• Rounding and currency tolerances (0.02 here) need to be a deliberate, documented threshold, not a guess; too tight and rounding noise floods the true_mismatch bucket, too loose and it starts absorbing real discrepancies.
• FULL OUTER JOIN is not supported everywhere: MySQL has no native FULL OUTER JOIN at any version (still an unshipped feature per MySQL's own worklog WL#1604) and needs a UNION of a LEFT JOIN and a RIGHT JOIN ... WHERE left.key IS NULL to emulate it, on every MySQL release up to and including the current 8.4/9.x lines.
• Automating this: schedule the classification query to run after each close, persist bucket + root_cause + a link to the source rows for auditability, and route only true_mismatch rows to a human investigator, since the other buckets are self-explanatory once the classification is trusted.

Direct answer. The most plausible causes are that the two connections are actually hitting different underlying data (a lagging or differently-tuned replica), that the replica's statistics are stale relative to the primary's, or that a configuration difference between the two (memory settings, cost parameters) leads the same query to a genuinely different plan; isolate which one by comparing statistics freshness, configuration, and data currency between the two connections directly, rather than assuming the query itself is the variable.

Structured elaboration. Start by confirming the two connections are even hitting the same DATA: replication lag means a replica can be seconds, minutes, or more behind the primary, and while that usually doesn't change PLAN shape by itself, it's worth ruling out as a confounding factor before you go further, since a stale replica muddies any comparison you make. Next, compare statistics freshness on the specific tables involved: if the replica's statistics were refreshed at a different time (or on a different schedule) than the primary's, or if statistics don't replicate identically depending on your engine's replication mechanism, the two connections can genuinely be planning from different information even though the underlying schema and data are notionally "the same." Finally, compare configuration directly: a replica provisioned with different memory settings, different cost-model parameters, or even a different engine version can lead an otherwise-identical query to a genuinely different, and genuinely differently-optimal, plan.

Worked example. A replica whose statistics job runs on a different, less frequent schedule than the primary's is a very plausible explanation on its own: if the primary was recently ANALYZE'd after a data shift but the replica hasn't caught up yet, the replica's optimizer is working from an older picture of the data than the primary's, which can easily produce a different (and worse) plan for the identical query text.

Trade-offs and pitfalls. Resist jumping straight to "the replica's hardware must just be slower," which is possible but is a much less common actual cause than a genuine statistics or configuration difference, and treating it as the default explanation without checking the more likely causes first can send you chasing an infrastructure upgrade that wouldn't have fixed the real problem.