Lyft Data Engineer (Mid-Level) Interview Preparation Guide

Situation: We're bringing a mid-level data engineer into our 10-person team that owns ETL and the warehouse.

Task: Get them productive, confident in the codebase, and delivering safe changes within 90 days.

Action:

• 0–30 days: access (cloud, VPN, repo, CI, monitoring), read docs/data dictionary, walkthroughs, pair with buddy on small bug fixes, attend standups.
• 31–60 days: own a minor ETL task (schema change or new ingestion), write tests, run canary in staging, shadow on incidents.
• 61–90 days: deliver a full-ticket feature (pipeline addition/optimization), lead PR reviews, document design, present to team.

Success metrics: access setup ≤3 days, first PR merged by day 20–30, independent deploys by day 60, feature shipped and monitored by day 90, positive feedback from buddy and data consumers.

Remote vs onsite tweaks: remote gets scheduled pairing blocks, daily check-ins, recorded walkthroughs, and virtual socials; onsite adds live whiteboard sessions and in-person shadowing.

Direct answer

Before your work depends on it, put a written interface contract in place (the shape of the data or API, error cases, and versioning), a single named owner on each side, and an SLA (service level agreement: the vendor's contractual uptime/response commitment) for questions and changes with a defined escalation path. Then build against a mock or stub (a fake stand-in for the real API that returns data matching the agreed contract, so your team can build and test without waiting on the real thing) that matches that contract, so a late dependency delays true integration, but doesn't block your team's progress.

Framework

Before you start building. Agree the contract explicitly (schema, error handling, versioning), name one owner per side rather than 'the team', and set an SLA for response time and change turnaround, with an escalation path if it slips.

While you wait. Build and test against a mock or stub that matches the agreed contract, so your team keeps moving. Pair it with automated contract tests, so if the mock and the real dependency drift apart, you find out at build time instead of at release.

Internal-team dependency vs external vendor dependency. The mechanics differ once the other side is a vendor rather than a team you can walk over to.

Worked example

Situation: a product depends on a vendor-managed API (for example a payments or identity provider). The vendor's contract commits to 99.5% availability, but the product's own reliability target requires 99.95%.
Quantifying the gap: a year has 8,760 hours. At 99.5% availability, permitted downtime is 0.5% of 8,760 = 43.8 hours per year. At 99.95%, permitted downtime is 0.05% of 8,760 = 4.38 hours per year. The vendor's contract therefore permits about 43.8 minus 4.38 = 39.42 hours per year more downtime than the product can actually tolerate.
Action: negotiated for a higher committed SLA where possible; where the vendor would not move the number, negotiated a compensation or credit clause tied to a downtime threshold, documented in writing. Regardless of the contract terms, added caching on the read path so a short vendor blip doesn't cascade immediately, and a fallback path that degrades the feature gracefully instead of erroring during an outage window.
Result: the contract negotiation raises the ceiling on paper, but the caching and fallback layer is what actually protects users during the gap between what the vendor promises and what the product needs, since a credit clause compensates you after an outage, it doesn't prevent one.

Trade-offs and pitfalls

• Mocks and stubs only help if kept in sync with the real contract. A stale mock creates a different kind of surprise at integration time.
• Vendor SLA credits are usually a small fraction of the real cost of downtime (lost trust, lost usage). Treat them as compensation, not as risk mitigation on their own, and pair them with technical fallbacks.
• Applying heavy contract-and-SLA process to a short, low-risk internal dependency slows down partners who need speed more than ceremony. Calibrate the rigor to the risk and duration of the dependency, not the same weight for every one.

Direct answer. Expand each view definition in place, substituting it for a plain subquery, until you're looking at the actual raw tables and joins underneath every layer, then run EXPLAIN against that fully-expanded query rather than the outer view alone, since the outer query's own plan will already reflect the full expansion but reading the SQL text itself, layer by layer, is what tells you where in that stack the expensive work actually lives.

Structured elaboration. A chain of views (a view built on another view, built on another) is transparent to the query planner, which sees straight through to the underlying tables regardless of how many logical layers you've stacked, but it's NOT transparent to a human reading the SQL, who sees only the outermost view's definition unless they deliberately expand each layer. Most databases let you inspect a view's definition directly (a system catalog or an equivalent introspection command); walk that chain, substituting each view reference for its actual underlying query, until you have one fully-expanded query with no view references left, and THAT is the query whose real execution plan you should be reading, cross-referenced against the expanded SQL so you know which line of text a given expensive plan node actually corresponds to.

Worked example. A "customer_summary" view built on top of an "active_customers" view, itself built on top of a raw customers table joined to a status lookup, might look innocuous when you only read the outermost customer_summary definition; expanding both layers might reveal that the underlying join involves an unindexed column, or a function-wrapped predicate, buried two layers down where nobody reading only the top-level report query would think to look.

Trade-offs and pitfalls. It's tempting to optimize the outer, visible query (adding an index that seems related to what the report is doing) without actually finding where the real cost lives inside the view chain; that often produces no improvement at all, since the fix has to target the actual underlying tables and predicates the expensive operator touches, which view-layer abstraction can easily hide from a reader who never expands past the top level.

Direct answer

Before shipping a significant ETL (extract, transform, load) change, instrument the minimum set of telemetry that would let you catch a regression FAST: schema shape, output volume, processing latency, error rate, and downstream-consumer impact, and present that to a non-technical stakeholder as a simple "here's how we'll know quickly if this went wrong" framing, not a list of metric names.

Structured elaboration

• Schema: compare the new version's output schema against the previous version's, confirming no unexpected column removal or type change, since a schema regression can silently break a downstream consumer even when the pipeline itself runs successfully.
• Volume: compare output row counts against the historical baseline for a comparable run, a significant, unexplained volume shift is often the first visible sign something in the new logic changed unintentionally.
• Latency: compare processing time against the previous version's typical runtime, a meaningful regression here can indicate an unintentional performance regression in the new logic (a join that got less efficient, for instance).
• Error rate: track any new or increased error/rejection rate during the rollout window specifically, isolated to the new version if you're running it alongside the old one during a transition period.
• Downstream-consumer metrics: check whether any downstream table or dashboard that depends on this pipeline's output shows an unexpected change immediately after the new version runs, the ultimate signal of whether the change actually mattered to anyone.
• Presenting to a non-technical stakeholder: translate this into a plain-language commitment, "we'll be watching row counts, timing, and whether any downstream report looks different, and we'll know within the first run whether something's wrong, before it reaches you," rather than listing the five technical signals by name, which conveys confidence and a concrete safety net without requiring the stakeholder to understand the underlying mechanics.

Worked example

Concretely: rolling out a rewritten transformation step, the team runs the new version alongside the old one for one cycle (a shadow run) rather than a hard cutover, comparing all five signals between old and new output for the exact same input data. The new version's row count matches within 0.1% (expected, since the row-level logic didn't change), but latency is 40% higher than the old version, flagged before cutover as a performance regression worth investigating (traced to an unindexed lookup added in the rewrite) rather than discovered after the fact in production. Once fixed and re-validated, the cutover proceeds, and the stakeholder update states plainly: "the new version is live, we validated it produces matching results with acceptable performance before switching over, and we're watching the same five signals for the next 48 hours as an extra safety margin."

Trade-offs and pitfalls

Running the new and old versions in PARALLEL (a shadow run) before a full cutover, rather than deploying directly and watching for problems after the fact, is what turns this from a reactive "we'll notice if something breaks" plan into a proactive validation that catches issues BEFORE they ever reach production consumers, worth the extra short-term compute cost of running both versions briefly. The pitfall in stakeholder communication is either over-explaining the technical detail (losing the non-technical audience) or under-explaining to the point the commitment sounds vague and unconvincing ("we'll keep an eye on it"), the middle ground, naming the specific categories of things being watched in plain language without the metric jargon, is what actually builds confidence without requiring technical literacy.

Direct answer

Records that fail parsing or validation go to a dead-letter queue (DLQ), a separate durable holding area, tagged with the original payload, the specific error, and a stable identifier tying them back to their place in the source. Correction happens outside the main flow (automated repair for known-fixable patterns, manual remediation for the rest), and replay pushes the corrected record back through the exact same validation gate it failed under a new attempt identifier, so it either passes cleanly or fails again visibly. Nothing is ever silently patched and slipped back in.

Structured elaboration

Put the gate as early as possible. Validation belongs at the ingestion boundary, before a record reaches any join, transform, or aggregation stage. Catching a bad record before it's mixed into shared state is cheap; unwinding an aggregate it already corrupted is not.

Parsing failures and validation failures are different problems. A parsing failure (malformed structure, broken encoding) usually points at a producer bug and is rarely safe to auto-fix. A validation failure (a missing required field, a value out of range, a reference that doesn't resolve) is often fixable given a bit more context, which is why it's worth routing these two differently rather than treating every failure the same way.

What has to travel with a record into the DLQ. The raw original payload, the specific rule or field that failed, a timestamp, a stable identifier that preserves its position in the source stream, and a retry or attempt counter. Without this, remediation is guesswork.

Two correction paths, not one. A known, safe failure pattern (for example, a schema version the pipeline hasn't rolled out support for yet) can go through an automated repair-and-resubmit step with no human involved. Everything else goes to a remediation surface where a person can see the failure reason and the payload, fix or annotate it, and mark it ready to go back through.

Replay through the same gate, under a new identity. A corrected record is resubmitted through the identical validation logic it originally failed, not around it, so a bad fix still gets caught. It carries a new attempt identifier rather than overwriting the original entry, preserving an audit trail of what failed, what changed, and when it was retried.

Watch the queue itself, not just individual failures. A DLQ that only ever grows is itself a signal. The right response to a small, steady trickle (normal background noise) is different from the right response to a sudden spike (usually a systemic break, like an upstream schema change), and the pipeline needs a way to tell those apart rather than routing an ever-growing stream to a queue nobody is watching.

Worked example

Say the pipeline ingests 2,400,000 records a day, and historically 0.25 percent fail validation:

Spread evenly across 24 hours, that's a baseline arrival rate of:

Now suppose an upstream schema change causes 8 percent of one hour's arriving records (100,000 that hour) to fail validation:

Compared to the 250-per-hour baseline, that's:

If the alerting rule fires whenever the DLQ arrival rate exceeds 5 times the trailing baseline, this spike (32 times baseline) triggers it clearly, which is exactly the signal that should prompt someone to check for a systemic cause, like a schema change, instead of assuming it's more of the same background trickle.

flowchart LR
    A[Incoming record] --> B[Validation gate]
    B -->|passes| C[Continue through pipeline]
    B -->|fails| D[Dead-letter queue]
    D --> E[Automated repair, known patterns]
    D --> F[Manual remediation]
    E --> G[Replay, new attempt id]
    F --> G
    G --> B

Trade-offs & pitfalls

• A DLQ with no remediation and replay loop is a write-only graveyard; it's no better than silently dropping the records, just more expensive to store.
• Reprocessing a corrected record under its original identity, instead of a new attempt identifier, erases the audit trail of "this failed once, here was the original data."
• Auto-retrying validation failures that are genuinely permanent (a business-rule violation, not a transient glitch) wastes effort on records that will fail identically every time until the rule or the data changes.
• Placing the validation gate after joins or aggregation means a bad record has already corrupted shared downstream state before anyone catches it; moving the gate earlier is nearly free, unwinding a corrupted aggregate is not.
• Treating every spike in DLQ volume as "more of the same" instead of a signal worth investigating is how a schema break goes unnoticed for hours.

Month 0 (onboard week): set expectations, access, documents, 1:1 goals, assign a small “read + run” repo (simple Spark job) and observability dashboard to explore.

Month 1 (foundations)

• Learning milestone: distributed systems fundamentals (partitioning, shuffles, eventual consistency), Spark core, job lifecycle, cluster resource model.
• Pairing: daily 1–2h pairing sessions — I drive one day, they drive next.
• Small ownership: make a non-prod copy of an existing ETL, run it end-to-end.
• Code review: focus on clarity, tests, and resource/config safety.
• Metrics: time-to-merge, PR size, number of clarification questions (should drop).

Month 2–3 (applied practice)

• Milestones: performance tuning (caching, partitioning), fault tolerance patterns, checkpointing and idempotency.
• Pairing: 3x/week; they lead two paired refactors per week.
• Ownership: own a minor production job’s monitoring and SLAs (alerting runbooks).
• Code review: require unit/integration tests, explain design choices in PR description.
• Metrics: flaky job count, mean time to detect (MTTD), PR review feedback count.

Month 4–5 (independence building)

• Milestones: infra-as-code for pipelines, deployment pipelines, cost optimization.
• Pairing: weekly architectural reviews; async reviews otherwise.
• Ownership: full ownership of a non-critical pipeline (deploy, docs, runbook, pager rotation).
• Code review: mentor-level checklist (scalability, cost, failure modes).
• Metrics: successful independent deploys, incidents caused by owned pipeline, time to resolve incidents.

Month 6 (handoff → independent)

• Milestones: architecture proposal for a small new pipeline, mentoring a junior/peer.
• Final ownership: shipping that new pipeline end-to-end.
• Ongoing practices: weekly 1:1 coaching, quarterly learning goals.
• Success metrics: can independently design/deploy pipelines, reduced review comments on architecture, <1 preventable incident/month for owned services, shorter cycle time, positive feedback from peers.

Feedback cadence: weekly 1:1, biweekly performance checkpoints with examples, 360 feedback at month 3 and 6. Emphasize growth: document decisions, write postmortems, and pair-to-teach to solidify knowledge.

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.

Direct answer

Treat the choice like a hypothesis worth testing cheaply before committing years to it: weigh the personal pull you already feel toward a specific part of your current workflow against external signal, where your organization or the market is actually investing, then run a small, time-boxed validation before fully committing.

Structured elaboration

• Factors to weigh: personal pull (the lightest-weight version of this question is simply which part of your current workflow you already find yourself wanting to deepen next), organizational trajectory (where your company or industry is actually investing), durability (is this likely to matter in several years or is it a narrow fad), and transferability (how much of the skill still carries over if the bet turns out wrong).
• State the hypothesis explicitly. Something like "depth in this direction will make me more valuable and more capable of X," rather than committing on a vague sense that it seems interesting.
• Validate cheaply before committing. A short, deliberately scoped trial project measured in weeks rather than years, informational conversations with people already deep in that direction, and a check against real market-demand signals, what's actually being hired for or invested in around you, rather than a single trend or headline.
• Set a deliberate decision checkpoint after the validation window: commit harder, pivot, or abandon. Don't let a time-boxed trial quietly become the multi-year commitment by default without a real re-decision.

Worked example

Rather than picking a multi-year direction cold, I started from what I was already gravitating toward inside my current work, the part of the job I'd stay late on even when nobody asked. I treated that pull as a hypothesis rather than a conclusion: I spent a few weeks on a small, deliberately scoped side project in that direction and talked to people already working in it about what the day-to-day looks like once the novelty wears off. I also checked it against real market-demand signals, what was actually being invested in around me, not just where I personally found it interesting, since a direction with no organizational pull is a much harder multi-year bet. Only after that validation window did I commit to it as a focus, with a deliberate check-in point rather than letting the trial quietly become the decision by default.

Trade-offs & pitfalls

• Committing years on personal interest alone, with no external demand signal, risks specializing into something the market or your organization doesn't actually value.
• Committing purely on market demand with no personal pull risks burnout in a direction you don't actually want to spend years in.
• Skipping validation and committing on a single conversation or a trend headline is the most common shortcut, and the most common regret.
• Letting a time-boxed trial silently become the multi-year commitment without a deliberate re-decision point.

Architecting this pipeline means deliberately splitting transformation work by WHERE it needs to run, not defaulting to one pattern everywhere. The two workloads (sub-5-second personalized recommendations, and daily batch analytics) have genuinely different constraints, and the split should be visible in the design, not incidental to it.

The transform-placement decision, explicit

• Clickstream, feeding real-time recommendations (pre-load ETL, by necessity): light validation and enrichment (session stitching, basic feature computation) has to happen in the streaming path itself, before the recommendation service can use it, because ELT's "load raw, transform later in the warehouse" pattern is fundamentally too slow for a 5-second SLA (service-level agreement). This is ETL because it MUST be, not by preference.
• CDC (Change Data Capture) from transactional databases, feeding daily analytics (ELT): these changes get captured with minimal transformation (log-based CDC via Debezium, landed close to raw) and the heavy transformation, joins, aggregation, dimensional modeling, happens after landing in the warehouse, where it benefits from batch compute, is trivially reprocessable if a transform has a bug, and doesn't need to keep pace with a sub-5-second SLA.
• The clickstream ALSO feeds daily analytics, and for that path, the same raw (or lightly-enriched) events land in a data lake and get transformed in batch alongside everything else, meaning the same source data takes two different paths depending on which downstream consumer it's serving.

Architecture

flowchart LR
  CS[Clickstream 1B events/day] --> K[Kafka]
  DB[(Transactional DBs)] --> CDC[Debezium CDC] --> K
  K --> SP[Stream processor: light ETL - validate, enrich, feature-compute]
  SP --> RT[(Real-time feature store)]
  RT --> REC[Recommendation service <5s SLA]
  K --> LAKE[(Raw landing: data lake)]
  LAKE --> ELT[Batch ELT: dbt models in the warehouse]
  ELT --> DW[(Analytics warehouse)]
  DW --> BI[Daily analytics and dashboards]

Storage choices: the data lake (object storage, Parquet) is the durable raw landing zone for everything, cheap and replayable. The real-time feature store (a low-latency key-value store, not the warehouse) serves the recommendation path because a warehouse query, even a fast one, is the wrong latency class for a 5-second end-to-end budget that also has to include model inference. The analytics warehouse holds the ELT-transformed, dimensionally modeled data for BI.

Streaming vs. batch split: the recommendation path is pure streaming, no batch step in its critical path at all. The daily-analytics path is batch (scheduled dbt runs), consuming from the same raw landing zone the streaming path also reads from, so there is exactly one ingestion point (Kafka/Debezium into the lake) feeding two independently-paced downstream consumers.

Failure and recovery: the streaming path's failure mode is "recommendations get staler or fall back to a non-personalized default," recoverable by replaying from the last committed Kafka offset once the stream processor is healthy again. The batch ELT path's failure mode is "the daily analytics refresh is late," recoverable by simply rerunning the failed dbt run, since ELT models built on landed, durable raw data are naturally re-runnable without any data loss risk, which is exactly the property that justifies deferring most of the transformation work to that side of the split.

Direct answer

An organization-wide data-quality program needs to cover people (roles like data stewards with clear accountability), process (policies, standards, and an incident-response playbook), and technology (a chosen tooling stack), rolled out in phases (pilot, scale, sustain) with measurable milestones, since attempting a company-wide rollout all at once, without a proven pilot, is the most common way this kind of initiative fails.

Structured elaboration

• Pilot phase (roughly months 1-2): select one or two high-visibility, high-pain pipelines as the pilot, implement the full program (stewardship, standards, tooling) end-to-end for just those, and use the pilot to prove the model works and generate a concrete before/after story to build momentum with.
• Scale phase (roughly months 3-4): expand from the pilot to the next tier of critical pipelines, using lessons learned (what tooling choices worked, what process friction showed up) to refine the approach before it becomes standard practice everywhere.
• Sustain phase (roughly months 5-6 and beyond): make the standards and tooling the default for new pipelines going forward, establish ongoing training and onboarding for new team members, and define success KPIs that are tracked continuously rather than only during the initial rollout.
• Tooling choices: the technology leg is not just "pick a tool," it is picking the tool whose friction matches how the organization already works. For a team already heavily invested in dbt-based SQL transformations, dbt tests (the built-in and dbt-native unique, not_null, accepted_values, relationships tests, plus custom SQL-based tests) are the lower-friction choice: they live alongside the existing models, run in the same dbt test invocation the team already uses, and require no new language or deployment surface. For an organization needing richer, cross-language validation (Python-based pipelines, non-dbt sources, or a need for a structured, queryable validation report rather than a pass/fail test run), Great Expectations is the better fit: it is a standalone Python framework that produces a structured "data docs" report and integrates with a wider range of data sources than a SQL-only tool can. The right call is decided by which cost the team can least afford, the friction of adopting a new tool outside their existing stack, or the limitation of a SQL-only tool when the pipeline is not SQL-only, not by which tool is abstractly "better."
• Driving adoption across teams that do not report to you: this is fundamentally an influence problem, not an authority one; the pilot's concrete, quantified success story (a real incident prevented, a real cost saved) is usually the most effective adoption lever, more so than a mandate from above, because it gives other team leads a self-interested reason to opt in rather than a compliance obligation to resent.

Worked example

A six-month program: months 1-2 pilot the full program on the two pipelines that generated the most incidents last quarter, producing a concrete result (for example, a measured 80% reduction in data-quality-related incident tickets for those two pipelines); months 3-4 use that result to recruit three more team leads to adopt the same standards and tooling voluntarily, refining the onboarding process based on friction the pilot teams reported; months 5-6 make the validated tooling the default for any new pipeline going forward and establish a recurring quarterly review of the program's KPIs (incident rate, time-to-detection, percentage of critical datasets with defined quality checks) to the leadership team.

Trade-offs and pitfalls

The failure mode this phased approach specifically avoids is a mandated, all-at-once rollout with no proven pilot: teams asked to adopt an unproven process purely by directive tend to comply minimally and revert as soon as attention moves elsewhere, whereas teams who see a peer team's concrete, quantified success story are far more likely to adopt genuinely and sustain it. The trade-off is speed, a pilot-first approach is slower to reach full coverage than a mandate, but it is far more likely to actually stick once it gets there.