Data / ETL QA Engineer interview questions and answers

9 of 9 questions shown

1. juniorValidationWhat do you validate after an ETL load?

   Model answer. Row counts between source and target after documented filters, and aggregates such as the sum of amounts. Null and duplicate checks on keys, data types and formats, and referential integrity. Then sample the transformation rules and review the reject table.

   I can answer this
2. juniorSQLWrite a query to find duplicate customer emails.

   Model answer. SELECT LOWER(TRIM(email)) AS e, COUNT(*) FROM customers GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1; Normalise case and whitespace first, otherwise obvious duplicates slip through. Fix the cause with a unique constraint on the normalised value.

   I can answer this
3. midSQLHow do you find rows in the source that are missing in the target?

   Model answer. Use a LEFT JOIN on the key and filter where the target key IS NULL, or NOT EXISTS. For whole-row comparison use EXCEPT or MINUS. Avoid NOT IN with nullable columns, because a single NULL makes it return nothing.

   I can answer this
4. midWarehousingHow do you test a Type 2 slowly changing dimension?

   Model answer. When a tracked attribute changes, the old row is closed (end date set, current flag false) and a new row is inserted with a new surrogate key and start date. Check there is exactly one current row per natural key, no gaps or overlaps in date ranges, and that facts join to the version valid at transaction time.

   I can answer this
5. midLoadsHow do you test incremental loads?

   Model answer. Run batches with inserts, updates and deletes, then rerun the same batch to confirm idempotency with no duplicates. Test late-arriving records, a failure mid-batch followed by a restart, and watermark boundaries including time-zone effects.

   I can answer this
6. midQualityWhich data-quality dimensions do you test?

   Model answer. Completeness, uniqueness, validity, consistency, accuracy and timeliness. Encode them as automated checks, for example Great Expectations suites or dbt tests, and run them on every load.

   Tools: Python

   I can answer this
7. seniorScaleHow do you validate a multi-billion-row migration when you cannot compare every row?

   Model answer. Compare counts and hash checksums per partition or key range inside the database, then narrow down any mismatching partition. Use statistical sampling and distribution comparison for the rest, and do a full comparison only for critical tables.

   I can answer this
8. seniorStreamingHow do you test a streaming pipeline end to end?

   Model answer. Publish known events including duplicates, out-of-order and late ones. Verify delivery semantics (at-least-once needs idempotent sinks), consumer lag and schema evolution through a registry. Finish with dead-letter handling and replay.

   I can answer this
9. seniorComplianceHow do you provide production-like test data compliantly?

   Model answer. Mask or anonymise personal data deterministically so joins still work, or generate synthetic data that preserves distributions and referential integrity. Assess re-identification risk and restrict access to the test environment.

   I can answer this

No question matches those filters. Clear the search or choose All.