ETL Developer Interview Prep Guide

Discuss source-specific extraction strategies: CDC for databases, scheduled API polling with incremental parameters, file watchers for flat files. Cover the landing zone pattern (raw data first, then transform), idempotent loading, error handling and dead letter queues, parallel extraction, and monitoring. Address the 1-hour SLA by identifying the slowest sources and building the pipeline schedule backward from the deadline.

ETL transforms data before loading, useful when the target has limited compute (traditional warehouses) or when you need to reduce data volume before loading. ELT loads raw data first, then transforms in the warehouse, leveraging cloud warehouse compute power. ELT is the modern standard with Snowflake, BigQuery, and Redshift because it preserves raw data, simplifies extraction, and pushes transformation to SQL where analysts can contribute. Discuss specific scenarios where each is appropriate.

Check the orchestration logs to identify the failing task. Common failures: source system timeout, schema change in source data, disk space exhaustion, or credential expiration. Assess whether you can fix and rerun versus implement a manual workaround. Communicate estimated resolution time to stakeholders. After fixing, add monitoring and alerting to catch this failure mode earlier next time. Discuss on-call procedures and how you prevent recurring issues.

Highlight specific engineering practices: idempotent operations (rerunnable without duplicates), comprehensive logging and monitoring, graceful error handling with retry logic, data quality checks at each stage, automated testing, documentation, and performance optimization. Quantify the data volumes, source count, SLA, and uptime achieved. Show that reliability was designed in, not bolted on after failures.

Options: implement CDC using database transaction logs (Debezium for Kafka-based CDC), use a hash comparison of all columns to detect changes, maintain a full snapshot and compute diffs, or negotiate with the source team to add a modified timestamp. Discuss the tradeoffs of each approach in terms of complexity, performance, and accuracy. Mention that the best long-term solution is often fixing the source system, but you need a working solution in the meantime.

Discuss schema detection and comparison at extraction time, alerting on unexpected changes, schema evolution support in your warehouse (Snowflake autodetects, BigQuery supports schema updates), and versioning of transformation logic. Cover your testing strategy: CI tests that validate against expected schemas, and how you communicate breaking changes to downstream consumers. Mention schema registries for event-driven architectures.

Use ROW_NUMBER() window function partitioned by the natural key and ordered by load timestamp descending, then filter for row_number = 1. Discuss alternatives: QUALIFY clause in Snowflake, MERGE statements for upsert operations, and how you prevent duplicates from being loaded in the first place through idempotent pipeline design. Address performance considerations for deduplication at scale.

Describe the legacy system (SSIS, Informatica, custom scripts), the target architecture, your migration strategy (parallel run, phased cutover, or big bang), validation approach to ensure data parity, and the challenges you faced (undocumented business logic, tribal knowledge, performance differences). Show that you respected the existing system while building something better, and that you validated thoroughly before decommissioning the legacy pipeline.