Data Engineering
46 bites tagged Data Engineering — interview questions with model answers, and 60-second explainers.
ETL: Extract, Transform, Load
ETL moves data through three phases from sources to containers. It handles one or more inputs and outputs via software that automates the process on recurring schedules or in batches. The footgun is defaulting to manual runs when automation is typical.
Build a system to measure viral coefficient and attribute invites to signups
Tests if you can map K=i×c to logged events and resilient pipeline. Good answers define invite_sent, click, signup events with referral tokens; sketch stream joins; and flag cross-device and organic attribution gaps. Red flag: assuming perfect attribution.
Design a pre-aggregation architecture for low-latency experiment results
Tests OLAP-at-scale trade-offs. Strong answers design streaming rollups into a real-time OLAP store, use partial cubes for high-cardinality dimensions, and retain raw events.
What data pipelines and infrastructure feed a viral user acquisition model?
Tests causal attribution architecture. Great answers cover invite instrumentation with identity resolution, streaming pipelines that split organic and viral signups, and feature stores for network-state features.
Describe the end-to-end data flow for tracking a 'Share' button click
Payload carries event type, user ID, timestamp, device, content; client batches with retry; backend validates and lands in a partitioned store. Full analytics pipeline design. Raw PII, no timestamps, no dedup.
How would you instrument events and query a 3-invite aha moment?
Tests taxonomy and stateful aggregation across sessions. Strong answers instrument Teammate Invited with timestamps, compute 7-day per-user counts via stream or SQL windowing, and materialize cohorts.
Describe the architecture for multi-touch attribution with time-decay
Stitch IDs, stream events to warehouse, sessionize journeys, then apply decay weights in SQL. Architecture for identity resolution and multitouch attribution.
How would you architect long-term holdback experiment groups?
Tests longitudinal causal inference and engineering tradeoffs for multi-month isolation. A strong answer covers bucketing, delayed metrics, and cross-experiment guards. Red flag: daily re-randomization or ignoring survivorship bias in aging cohorts.
What is the difference between WHERE and HAVING in SQL?
Tests SQL execution order and aggregation. A strong answer states WHERE filters rows before grouping, HAVING filters groups after aggregation, and gives an aggregate example that WHERE cannot evaluate.
Design a rate-limited REST API data collection script
Tests client-side throttling discipline versus reactive 429 handling. Strong answers proactively pace calls using rate-limit headers, cap concurrency, and apply exponential backoff with jitter. Red flag: tight-loop retries or ignoring headers.
Most efficient way to convert list of dicts to pandas DataFrame
Tests knowledge of vectorized DataFrame construction versus slow row-wise assembly. Answer: pass the list directly to pd.DataFrame(data); C-backed and handles missing keys as NaN. Red flag: recommending loops with pd.concat or iterative DataFrame building.
ETL: The Three-Phase Data Pipeline
ETL is a three-phase pipeline: extract from sources, transform, and load into containers. It supports many sources and destinations and runs as automated software, manual jobs, or scheduled batches. The footgun is manual execution of recurring jobs.
What are the engineering challenges and biases in overlapping A/B tests?
This tests inference across concurrent experiments. A strong answer names interaction effects and collision bias, proposes mutual exclusion or MVT for coupled UI, and stratified bucketing. Red flag: assuming additive lifts without combination validation.
Design column-level data lineage from source to dashboard
Propose AST extractors for Spark and dbt, a graph DB for column edges, and an API for impact analysis. Metadata architecture tracing columns through heterogeneous batch and SQL engines.
How do you root-cause a 20% revenue drop with no pipeline failures?
Reconcile against raw events, slice by dimension for silent gaps, audit schema drift. Incident leadership and validating data integrity before calling a downturn.
Describe dbt's role and how it differs from traditional ETL
Position dbt as ELT's T with lineage, tests, docs; contrast with Python ETL using external compute and Airflow only scheduling tasks. Declarative warehouse transformation vs imperative ETL.
Describe tracking a user event end-to-end from frontend to BI tool
Mention frontend instrumentation, routing, ingestion, warehouse transforms, and BI querying. Grasp of data pipeline layers from product to warehouse. Stopping at the backend or confusing analytics with application logs.
How do you manage event schema evolution without breaking reports?
Backward-compatible serialization, nullable new fields, raw versus modeled layers, versioned schemas, and consumer alerts. Contract-change discipline across ingestion, warehouse, and BI.
Design a CDC pipeline that handles schema evolution gracefully
Tests designing resilient CDC pipelines against schema drift. A strong answer covers schema registries with versioning, backward-compatible serialization, and automated compatibility checks.
How would you implement data lineage for microservices analytics?
Tests end-to-end provenance tracking, not just docs. Strong answers cover automated metadata capture at service boundaries, a central catalog such as DataHub or OpenLineage, and column-level tracing.
Data Versioning: Git for Your Datasets
Think of data versioning as Git for datasets. It tracks changes to your data, allowing you to reproduce ML experiments or roll back to a previous state. The footgun is using regular Git, which chokes on the large binary files common in ML.
Scraping Dynamic Sites: Find the API, Not Just Render
To scrape a dynamic site, find the hidden API call its JavaScript makes to fetch data instead of rendering the whole page. This is faster and more reliable. This applies when your scraper gets empty HTML but you see data in your browser.
Get Data Engineering bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.