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.

Don't refuse the request outright, and don't comply with it silently either. Acknowledge the real decision the executive is trying to support, make the risk of the specific metric concrete rather than arguing methodology in the abstract, and bring an alternative that meets the underlying need without shipping something misleading.

How to handle it

1. Find the real decision behind the ask. Ask what the executive will actually do with this number, what question it's meant to answer, before pushing back on the number itself.
2. Make the risk visible, don't just argue it. A concrete demonstration on real data, showing how the metric can point in different directions depending on an arbitrary choice, is far more persuasive than a principled objection about methodology.
3. Offer an alternative, not just a no. Publish with a transparent methodology note and caveats, or pair the requested metric with a companion breakdown of what's actually driving it, so the executive still gets a clear headline but nothing is hidden.
4. Protect the decision with process. Document the metric's definition and the reasoning behind it in the dashboard's own metadata or governance log, so this doesn't quietly become an unreviewed exception the next time someone asks for a similar shortcut.

When to comply, caveat, or escalate

Worked example

A VP asks for a single blended "engagement score" on the executive dashboard, aggregating several disparate signals with no stated weighting logic. If shipped as requested, a week-to-week swing in the score could easily be an artifact of how the components happen to be weighted rather than a real change in the business, and a decision made off that swing (say, reallocating budget away from a channel) would trace back to an arbitrary choice nobody examined.

Rather than arguing methodology in principle, the analyst pulls two plausible weighting schemes and applies both to the same period of real data. The two lines diverge noticeably, showing the VP directly that the "score" would tell two different stories depending on a choice nobody had actually made deliberately. The analyst then proposes shipping the metric with an explicit methodology note and a companion view of the underlying components, so the VP still gets a single number to lead with, but anyone drilling in can see what's actually driving it.

The VP is more persuaded by seeing the two divergent lines side by side than by any abstract argument about aggregation risk, and agrees to ship with the documented methodology and the companion breakdown attached.

What a senior person does differently here: leads with a demonstration on real data rather than a principled objection, and turns a "no" into a documented "yes, defined this way," which the executive can accept without it reading as a refusal.

Trade-offs and pitfalls

• Refusing outright with no alternative reads as obstruction, not integrity, and burns the relationship for no gained clarity.
• Complying silently, without raising the concern or documenting it anywhere, creates real exposure later if the metric ends up driving a bad call; there's no record that the risk was ever flagged.
• Offering caveats only works if they're actually visible where the number is used (in the dashboard itself, not buried in a separate document nobody opens).

Customer Acquisition Cost (CAC)
Definition: Average cost to acquire a new rider (or driver) over a period.
Standard formula:
CAC = Total Acquisition Spend / Number of New Customers Acquired
Where Total Acquisition Spend includes paid marketing, referral bonuses, creative/agency costs, and measurable channel-specific costs in the same period. Number of New Customers = users with first trip (or first app install + activation rule) in that period.

Customer Lifetime Value (LTV)
Definition: Present-value (or simple) expected gross contribution from a customer over a chosen horizon.
Simple cohort LTV = Sum over cohort of (Net Revenue per Customer) over T months
Net Revenue per Customer = Gross Fare + Fees − Driver Payouts − Platform Costs − Refunds − Direct Promotions (if recorded as discount) per customer.

Data sources & key table fields

• users table: user_id, signup_date, acquisition_channel, first_touch, campaign_id
• trips/payments table: trip_id, user_id, trip_date, fare_gross, platform_fee, driver_payout, tax, promo_code_id
• marketing_spend table: date, channel, campaign_id, spend, media_type
• referrals table: referrer_id, referee_id, bonus_amount, bonus_type, posted_date
• refunds/adjustments table: transaction_id, user_id, amount, reason, date
• promos table: promo_id, promo_type, face_value, applied_amount, accounted_as_marketing_boolean

How to treat refunds, promotions, and multi-channel acquisition

• Refunds: Treat as negative revenue in the trips/payments stream and attribute them to the original trip/customer date. For LTV, subtract refunds from gross revenue; for CAC, do not include refunds in acquisition spend.
• Promotions:
  • If promo is a marketing acquisition incentive (e.g., first-ride credit tied to acquisition campaign), count its cost in Total Acquisition Spend (CAC) and also reflect reduced first-trip revenue for LTV (or treat promo as both spend and discount but avoid double-counting).
  • If promo is retention/engagement (e.g., loyalty credits), treat as cost to service (subtract from revenue in LTV) but not in CAC.
  • Store promo metadata to categorize promo_type and accounting treatment.
• Multi-channel acquisition:
  • Choose an attribution model consistent with business needs: first-touch (simpler, often used for CAC), last-touch, or multi-touch weighted (e.g., time-decay).
  • Ensure marketing_spend and user acquisition mapping are joined by campaign_id / click/impression logs. For multi-touch, allocate fractional acquisition spend across channels per user journey and use that for channel-level CAC.
    Other considerations
• Define cohort window and horizon (30/90/365 days) and discount rates if using present value.
• Exclude internal transfers and bots; dedupe users (e.g., multiple devices).
• Report both aggregated and channel-level CAC and cohort LTVs for decision-making.

Direct answer

Start at the KPI's own defining table or view and walk its lineage graph upstream one hop at a time, using whatever lineage source is available (a transformation tool's dependency graph, a data catalog, or the warehouse's own query-history metadata) to list its immediate producers. Then prioritize which of those to inspect first by what changed most recently and which carries the most complex logic, rather than checking every upstream table with equal weight, and confirm a hypothesis by comparing actual numbers against historical baselines before calling it the root cause.

Structured elaboration

1. Confirm the symptom precisely. Which number is wrong, since when, and by how much. The "since when" matters most, because it turns an open-ended search into "what changed upstream around that date."
2. Pull the first-pass dependency graph from tooling, not from memory. A lineage tool, whether it's a transformation framework's dependency graph, a data catalog, or the warehouse's own lineage or query-history view, gives the KPI's immediate upstream tables and transformations in seconds. This should always be the first move, before reading any transformation logic by hand.
3. Prioritize the candidates instead of sweeping all of them:
   • Recency of change is the strongest signal; a code or schema change close to when the KPI diverged is the top suspect.
   • Logic complexity matters next; joins, window functions, and aggregations hide subtle bugs far more often than a straight pass-through does.
   • Recent operational incidents on a source (a known late or failed load) are an obvious, cheap first check.
4. Validate quantitatively, not by inspection alone. Compare a suspect's current row counts, key distributions, or aggregate values against its own historical baseline for the same period. A real KPI bug shows up as a measurable divergence somewhere in the chain, and that comparison either confirms or rules out a candidate before more time is spent on it.
5. Fix at the actual point of defect, not by patching the KPI layer to compensate; patching the transformation or coordinating with the upstream data owner, then re-running affected models forward, is what actually resolves it rather than hiding it.
6. Add a targeted check to prevent recurrence on exactly the field or transformation that broke, so the same failure class is caught before it reaches the KPI again.

Worked example

Say the monthly revenue KPI comes in 8 percent below expectation for November, and the trace starts at the orders table feeding the revenue model. The typical daily order count in November is about 150,000. On November 14, the day the divergence first appears, the orders table shows only 122,000 rows:

Spread across a 30-day month, one day's 18.7 percent shortfall contributes roughly:

That's far smaller than the 8 percent monthly miss actually observed, which rules out the single-day volume dip as the primary cause and points the trace toward a sustained, multi-day issue instead. Following the lineage graph one more hop, to the pricing table the revenue model joins against, turns up a schema change (a new discount field) that landed around the same time and caused the join to double-count discounted rows for every day the field has existed, a defect whose scale (spread across many days, not one) is consistent with an 8 percent sustained miss. The arithmetic above is what rules the first hypothesis out and justifies moving one hop further upstream, rather than stopping at the first plausible-looking suspect.

flowchart LR
    A[KPI shows unexpected value] --> B[Pull lineage graph from KPI object]
    B --> C[List immediate upstream sources]
    C --> D[Prioritize by recency and logic complexity]
    D --> E[Compare suspect vs historical baseline]
    E -->|rules out| D
    E -->|confirms| F[Fix at the actual source]
    F --> G[Add targeted check to prevent recurrence]

Trade-offs & pitfalls

• Reading every model's transformation logic by hand before checking the automated lineage graph wastes time the tooling already answers in seconds; lineage-first is almost always the faster path.
• Checking every upstream table with equal priority, instead of ranking by recency and complexity, turns a targeted trace into an unfocused audit that takes far longer than it needs to.
• Patching the KPI view itself to compensate for a known-bad upstream input, instead of fixing the actual defective transformation, hides the bug until the next time that upstream table feeds something else.
• A common wrong turn is treating lineage as purely structural (what depends on what) without also checking when each dependency last changed; the timing correlation is usually what actually narrows the search from many candidates to one.

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.