Data Engineer (US)

How do you design an ETL pipeline to be idempotent so reruns don't duplicate data?

I design loads around upserts keyed on a natural or surrogate key rather than plain inserts, and I make each pipeline stage stateless with respect to its inputs so rerunning it with the same input produces the same output. Partitioning by a processing date and overwriting the whole partition on rerun is another reliable pattern, especially for batch pipelines.

When would you choose a star schema over a snowflake schema for a data warehouse?

A star schema keeps dimension tables denormalized, which trades storage and some data redundancy for simpler, faster queries. That's the right call when query performance and ease of use for analysts matter more than storage efficiency. A snowflake schema is worth the extra join complexity when dimension tables are large, change frequently, and normalization meaningfully reduces storage and update anomalies.

How do you handle schema evolution in a pipeline that ingests data from a source you don't control?

I treat the ingestion layer as schema-tolerant by landing raw data first, before any strict schema is enforced, and applying schema validation at a later transformation stage. This way a new or renamed field upstream doesn't break ingestion outright. I also version the schema explicitly so downstream consumers can detect and handle changes rather than silently breaking.

What's your approach to choosing between a batch and a streaming architecture for a new data pipeline?

The deciding factor is how fresh the data needs to be for the downstream use case, not how technically interesting streaming is. If consumers only need daily or hourly updates, batch is simpler to build, debug, and operate. Streaming is worth the added complexity only when there's a real requirement for near-real-time data, like fraud detection or live dashboards.