Back to interview questions

100+ ETL Interview Questions and Answers

100+ ETL Interview Questions and Answers

Explore 120 ETL interview questions and answers on extraction, transformations, data quality, incremental loads, orchestration, reliability, and performance.

Explore 120 ETL interview questions and answers on extraction, transformations, data quality, incremental loads, orchestration, reliability, and performance.

By

Team Sherlock.sh

Add Sherlock as a preferred source on Google

ETL interviews should reveal whether a candidate can turn changing source records into trustworthy downstream data. Recruiters need to assess more than familiarity with a pipeline tool: the important questions concern identity, transformation rules, completeness, safe retries, and what happens when a job fails after part of its work has committed. A green scheduler status is useful only when the delivered data means what its consumers expect.

This guide to 100+ ETL Interview Questions and Answers contains 120 questions across beginner, intermediate, and experienced levels. It progresses from extraction, schemas, and data quality to incremental loading, change capture, historical corrections, orchestration, performance, and recovery. The scope is vendor-neutral: product documentation illustrates specific guarantees without implying that every connector, database, or execution engine behaves identically.

How can recruiters assess genuine ETL engineering skills when interview assistance can conceal gaps in reasoning about data loss, replay, and inconsistent results?

Give candidates a small dataset with a duplicate event, a late correction, and a missing lookup. Ask them to define the intended output, then introduce a timeout after a write and ask how they would determine what actually committed. Their explanation of evidence and recovery often reveals more than a polished architecture diagram.

Sherlock AI helps hiring teams assess signals of potential AI cheating, deepfakes, impersonation, and unauthorized assistance during interviews.

Use those signals alongside practical work and the candidate’s explanation, not as conclusive proof of misconduct. A useful ETL exercise rewards explicit assumptions, correct data handling, and a proportionate recovery design rather than memorized product terminology.

ETL Interview Questions

The complete question bank appears first so interviewers can choose prompts without revealing the answers. Select questions that match the role’s sources, destinations, freshness needs, and operational responsibilities, and state the relevant platform assumptions.

ETL Interview Questions for Beginners

Start with the core concepts of extraction, transformation, loading, data identity, quality, and business meaning. Ask candidates to connect each concept to a concrete dataset and a consumer expectation.

  1. What is ETL, and what business problem does it solve?

  2. How does ETL differ from ELT?

  3. What are typical ETL sources and targets?

  4. What happens during the extraction phase?

  5. What kinds of work belong in transformation?

  6. What happens during the loading phase?

  7. What is the difference between a full load and an incremental load?

  8. How does batch processing differ from streaming processing?

  9. What is a staging area, and why use one?

  10. What is a source-to-target mapping?

  11. What does the grain of a dataset mean?

  12. How do natural keys and surrogate keys differ?

  13. What is a data schema?

  14. How do structured, semi-structured, and unstructured data affect ETL?

  15. Why should a CSV file be parsed with a CSV parser?

  16. How would you handle missing values?

  17. Why are data types important in ETL?

  18. How would you handle money or other exact decimal quantities?

  19. Why are timestamps difficult in ETL?

  20. What is data profiling?

  21. How do validation and transformation differ?

  22. What are common dimensions of data quality?

  23. What is deduplication?

  24. How can joins introduce data errors?

  25. How do filtering and aggregation change a dataset?

  26. What is a lookup transformation?

  27. What is a fact table?

  28. What is a dimension table?

  29. How do Type 1 and Type 2 slowly changing dimensions differ?

  30. What is change data capture?

  31. What is a watermark in an incremental batch load?

  32. What is the difference between append, replace, and upsert loading?

  33. What is idempotency in an ETL pipeline?

  34. Why are transactions useful during loading?

  35. What does an ETL orchestrator do?

  36. How do a schedule and a data dependency differ?

  37. What should an ETL run log contain?

  38. How would you handle a rejected record?

  39. What is data reconciliation?

  40. How would you describe a simple reliable ETL design?

ETL Interview Questions for Intermediate

Explore incremental boundaries, historical data, parsing, retries, orchestration, and diagnosis. Strong answers identify edge cases and describe how they would verify the proposed behavior.

  1. How would you choose a safe incremental extraction key?

  2. How should lower and upper extraction boundaries be managed?

  3. Why can timestamp-only incremental queries miss changes?

  4. How would you process deletes in an incremental pipeline?

  5. How do snapshot extraction and CDC fit together?

  6. How would you detect and handle a CDC retention gap?

  7. How would you make an upsert safe against out-of-order updates?

  8. When is a merge operation insufficient for deduplication?

  9. How would you select one deterministic row from duplicate versions?

  10. How would you test a join before using it in production ETL?

  11. How should late-arriving facts be handled?

  12. How should a fact be handled when its dimension record has not arrived?

  13. How would you implement Type 2 dimension intervals safely?

  14. How do corrections differ from new historical dimension changes?

  15. What is a conformed dimension?

  16. How would you validate numeric conversion and rounding?

  17. How would you normalize text without destroying meaning?

  18. How would you validate nested JSON input?

  19. How would you choose between failing a batch and quarantining bad rows?

  20. How do you prevent retries from duplicating side effects?

  21. How should an orchestrated task choose its input interval?

  22. How would you design checkpointing for a multi-step pipeline?

  23. How would you perform a controlled backfill?

  24. How would you handle an API source with pagination?

  25. How should ETL respond to API rate limits?

  26. Why is file arrival not always proof that a file is complete?

  27. How would you make a file-based load reproducible?

  28. What is the difference between event time and processing time?

  29. What does a streaming watermark mean?

  30. How would you reconcile an aggregation pipeline?

  31. How do unit, integration, and end-to-end tests differ for ETL?

  32. What makes a useful ETL test fixture?

  33. How would you detect schema drift?

  34. How would you protect credentials in ETL jobs?

  35. How would you minimize sensitive data in a pipeline?

  36. What is data lineage, and how is it useful?

  37. How would you diagnose a slow ETL job?

  38. When can partitioning improve ETL performance?

  39. What is data skew, and why does it matter?

  40. How would you decide whether transformation pushdown is appropriate?

ETL Interview Questions for Experienced

Use these scenarios to assess end-to-end guarantees, distributed failure boundaries, publication, governance, migrations, and recovery. Look for precise scope and evidence rather than universal claims about a tool.

  1. How would you define an end-to-end exactly-once claim?

  2. How would you recover from a timeout after a destination commit?

  3. How would you coordinate data publication with checkpoint advancement?

  4. How would you prevent overlapping runs from corrupting the same target?

  5. How would you publish a rebuilt dataset without exposing partial results?

  6. How would you design a CDC pipeline across a source schema migration?

  7. How would you handle source identifiers that can be reused?

  8. How would you reconcile a source that changes during a long extract?

  9. How would you choose between reprocessing a partition and applying row-level corrections?

  10. How would you manage a backfill that competes with current ingestion?

  11. How would you prove that a pipeline has not silently lost data?

  12. How would you design observability around business correctness?

  13. How would you define a freshness service-level objective?

  14. How would you investigate a sudden drop in daily row count?

  15. How would you investigate a sudden increase in a business total after deployment?

  16. How would you handle a poison record that repeatedly crashes a stream?

  17. How would you design retention for raw, staged, and curated data?

  18. How would you assess a request to anonymize data by hashing identifiers?

  19. How would you secure an ETL pipeline's dependency and deployment process?

  20. How would you choose an ETL tool without relying on feature checklists?

  21. How would you migrate an ETL pipeline to a new platform?

  22. How would you version transformation logic and reference data together?

  23. How would you handle a change to the business definition of a metric?

  24. How would you design a data contract between producer and consumer?

  25. How would you test a failure between two independently committed systems?

  26. How would you prevent an old worker from publishing after a newer run?

  27. How would you reason about order when combining multiple source streams?

  28. How would you handle a correction that changes an aggregation key?

  29. How would you assess whether a checksum is sufficient for reconciliation?

  30. How would you handle floating-point nondeterminism in distributed aggregation?

  31. How would you balance small files against parallelism in a data lake?

  32. How would you evaluate a query-plan change in an ETL release?

  33. How would you respond to an ETL incident that exposed sensitive data?

  34. How would you decide whether to repair data in place or rebuild it?

  35. How would you design a practical ETL interview exercise?

  36. How would you assess an AI-generated ETL transformation?

  37. How would you manage conflicting requirements for freshness and correctness?

  38. How would you retire an old ETL pipeline safely?

  39. What evidence would you require before declaring an ETL migration complete?

  40. What does a mature ETL operating model look like?

ETL Interview Questions and Answers

The answers follow the exact wording, numbering, and order of the question bank. ETL and ELT are distinguished by where the relevant transformation occurs; batch and streaming concepts are called out separately. All ten code examples were executed as safe offline fixtures using Python 3.13.7 and SQLite 3.50.4 where applicable. They use fictional in-memory data and explicitly labeled SQLite SQL inside Python; they do not demonstrate production connector behavior, distributed concurrency, or cross-system atomicity. Check the documentation for the actual runtime, database, connector, and deployment before adapting a pattern.

ETL Interview Questions and Answers for Beginners

Start with the core concepts of extraction, transformation, loading, data identity, quality, and business meaning. Ask candidates to connect each concept to a concrete dataset and a consumer expectation.

1. What is ETL, and what business problem does it solve?

ETL means extract, transform, and load: obtain data from sources, apply defined rules, and deliver usable data to a destination. Its purpose is to make information consistent enough for a particular decision or application, not merely move bytes. For example, a sales pipeline may align customer identifiers and currencies before producing a reporting table. I would define the target grain, quality expectations, freshness, and reconciliation criteria first, because a technically successful job can still produce misleading business information.

2. How does ETL differ from ELT?

ETL performs the relevant transformation before loading the intended analytical target; ELT loads data first and transforms it using destination capabilities. Real platforms often combine both patterns, so I would describe where each transformation actually runs rather than rely on a product label. ELT can preserve flexible raw data and exploit warehouse compute, while ETL can enforce contracts or minimize sensitive data before delivery. Neither pattern removes the need for validation, ownership, lineage, access control, or reliable recovery.

3. What are typical ETL sources and targets?

Sources can include relational databases, APIs, files, event streams, and operational applications; targets include warehouses, databases, lakes, and downstream services. Each interface has a different contract for consistency, paging, schema, authentication, and change detection. A file extract is not equivalent to a transactional database snapshot. I would identify the actual read and write guarantees before choosing a connector. The same business dataset can require different extraction strategies depending on the source's operational load and available change history.

4. What happens during the extraction phase?

Extraction reads an explicitly defined population from the source while retaining enough identity and context to interpret it later. I would specify fields, selection boundaries, consistency assumptions, and how progress is recorded. For a daily order extract, the business date alone may not capture orders corrected afterward. Extraction should also respect source capacity and authorized access. The result needs provenance such as source identifier, extraction interval, and run identifier so failures can be reconciled without guessing which records were read.

5. What kinds of work belong in transformation?

Transformation includes parsing, type conversion, validation, standardization, joins, deduplication, business calculations, and aggregation. Each rule should have a clear meaning and owner. For example, trimming whitespace from a country code may be safe, while changing an unfamiliar code to a default country can corrupt reporting. I distinguish reversible normalization from interpretation that discards information. Preserve rejected or original values where justified and record rule versions so analysts can explain why a value changed between pipeline releases.

6. What happens during the loading phase?

Loading writes prepared data into the destination using an agreed append, replace, merge, or versioning strategy. I would consider keys, constraints, transaction boundaries, visibility to readers, and what a retry does. A bulk upload completing does not prove every row is available in the final table. Loading should finish with reconciliation and a deliberate publication boundary. For example, consumers should not see half of a refreshed daily partition while the remaining rows are still being transferred.

7. What is the difference between a full load and an incremental load?

A full load processes the complete defined dataset, while an incremental load processes detected changes since an established boundary. Full loads are often simpler to reason about but can be costly and disruptive at scale. Incremental loads reduce repeated work but introduce state, deletion handling, late-change, and recovery problems. I would choose based on volume, source guarantees, freshness, and correction requirements. Even an incremental design benefits from periodic reconciliation that detects records missed by its change-detection mechanism.

8. How does batch processing differ from streaming processing?

Batch processing works on a bounded input set, while streaming processing handles an ongoing sequence whose completion may never be known. Micro-batching processes small bounded groups repeatedly and can support low-latency delivery without being identical to record-at-a-time processing. I would compare required freshness, ordering, correction behavior, and operating cost. Streaming does not automatically mean instant or complete results: late events, backpressure, and window policies influence when a result is emitted and whether it is revised.

9. What is a staging area, and why use one?

A staging area holds intermediate data before it becomes consumer-facing output. It can support validation, reconciliation, restart, and controlled publication without exposing partial transformations. For example, a batch can be loaded into a run-specific table and checked before replacing a reporting partition. Staging is not permission to retain everything indefinitely. I would define access, lifecycle, encryption, and cleanup policies, and distinguish immutable source evidence from temporary working tables that can be recreated safely.

10. What is a source-to-target mapping?

It documents how each target field is produced, including source fields, joins, filters, conversions, defaults, and rejection rules. A useful mapping specifies business meaning as well as syntax. For example, a revenue field needs its currency, tax treatment, grain, and rounding policy, not just a formula. I would include ownership and representative examples so developers and stakeholders can resolve ambiguity before implementation. The mapping should remain versioned with the transformation instead of becoming an outdated document after the first release.

11. What does the grain of a dataset mean?

Grain defines what one row represents, such as one order line, one customer version, or one account per day. It determines which keys should be unique and which joins or aggregates are valid. Mixing order-level shipping amounts into an order-line table can multiply totals if the allocation rule is unclear. I would state the grain before designing the schema and test it with representative cases. A table name or a primary key alone does not explain its business meaning.

12. How do natural keys and surrogate keys differ?

A natural key comes from the business or source system, while a surrogate key is assigned to identify a target record independently. Natural keys support source reconciliation but may change, collide across systems, or be reused. Surrogate keys can simplify relationships and historical versioning, yet they require a maintained mapping to the source identity. I would preserve source-system scope and define key lifecycle rules. Generating a new surrogate key on every retry can create duplicates rather than solve identity problems.

13. What is a data schema?

A schema describes the structure and types of data, often including constraints and relationships. It is part of the interface between producer and consumer, but business meaning extends beyond field names. An amount stored as a decimal still needs currency and unit definitions. I would validate incoming data against the applicable schema version and distinguish permitted evolution from unexpected drift. Treating every new field as harmless can conceal changed semantics, especially when an existing field retains its name but changes meaning.

14. How do structured, semi-structured, and unstructured data affect ETL?

Structured data follows an explicit tabular model; semi-structured data such as JSON has organization but can vary between records; unstructured content requires additional interpretation to extract useful features. These categories do not determine quality. A JSON object can be well governed, while a relational table can contain inconsistent business values. I would choose parsing and validation methods appropriate to the actual contract, preserve provenance, and avoid silently flattening repeated or nested values in ways that change the target grain.

15. Why should a CSV file be parsed with a CSV parser?

Delimited text can contain quoted delimiters, escaped quotes, and embedded newlines, so splitting each line on a comma is unreliable. I would establish encoding, delimiter, quoting, header, and null conventions and use a parser configured for that contract. A field containing a comma is still one field when properly quoted. Parsing success is only the first check: validate column count, required values, and types afterward. Spreadsheet display should not be treated as proof that the underlying file has been interpreted correctly.

Python 3 — parse quoted CSV fields

import csv
import io

text = 'id,note\n1,"east, west"\n2,"two\nlines"\n'
rows = list(csv.DictReader(io.StringIO(text, newline="")))
assert rows == [
    {"id": "1", "note": "east, west"},
    {"id": "2", "note": "two\nlines"},
]
import csv
import io

text = 'id,note\n1,"east, west"\n2,"two\nlines"\n'
rows = list(csv.DictReader(io.StringIO(text, newline="")))
assert rows == [
    {"id": "1", "note": "east, west"},
    {"id": "2", "note": "two\nlines"},
]
import csv
import io

text = 'id,note\n1,"east, west"\n2,"two\nlines"\n'
rows = list(csv.DictReader(io.StringIO(text, newline="")))
assert rows == [
    {"id": "1", "note": "east, west"},
    {"id": "2", "note": "two\nlines"},
]

The fixture preserves both a quoted comma and an embedded newline. For disk files, open with the agreed encoding and newline="". The parser does not establish required-field or business validity; those checks follow parsing.

16. How would you handle missing values?

I would distinguish unknown, not applicable, absent, and deliberately blank values before applying a default. Replacing every missing amount with zero changes the meaning of aggregates and can hide source problems. The appropriate response may be rejection, quarantine, a nullable field, or an explicitly documented imputation rule. I would measure missingness by source and time and retain the reason when practical. Required fields should reflect the business contract, and any default needs an owner who accepts its consequences.

17. Why are data types important in ETL?

Types determine which values can be represented and how comparisons or calculations behave. A customer identifier with leading zeros should not automatically become an integer, and an oversized numeric value can overflow a destination column. I would define precision, scale, timezone, encoding, and range expectations explicitly. Test boundary values and distinguish parsing errors from genuine missing data. Converting a failed value to null without recording the error can make a pipeline appear healthy while discarding important information.

18. How would you handle money or other exact decimal quantities?

I would use a suitable exact decimal representation with an explicit scale and rounding rule, or integer minor units when the domain supports them. Binary floating point is not a universal substitute for decimal business arithmetic. Currency and unit must travel with the value, and conversion rates need an effective time and provenance. I would avoid rounding repeatedly at intermediate stages unless the rule requires it. The representation alone does not establish correct accounting treatment or make mixed currencies safely additive.

Python 3 — explicit decimal rounding

from decimal import Decimal, ROUND_HALF_UP

amount = Decimal("12.345")
rounded = amount.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
assert rounded == Decimal("12.35")
assert Decimal("0.10") + Decimal("0.20") == Decimal("0.30")
from decimal import Decimal, ROUND_HALF_UP

amount = Decimal("12.345")
rounded = amount.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
assert rounded == Decimal("12.35")
assert Decimal("0.10") + Decimal("0.20") == Decimal("0.30")
from decimal import Decimal, ROUND_HALF_UP

amount = Decimal("12.345")
rounded = amount.quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)
assert rounded == Decimal("12.35")
assert Decimal("0.10") + Decimal("0.20") == Decimal("0.30")

ROUND_HALF_UP is an illustrative policy, not a universal financial rule. Constructing the value from text preserves the intended decimal input; the applicable currency, scale, and rounding policy still need agreement.

19. Why are timestamps difficult in ETL?

A timestamp may represent an instant, a local wall time, a business date, or an ingestion time, and those meanings are not interchangeable. I would record timezone or offset information where relevant and define how ambiguous or nonexistent local times are handled. Converting an unspecified local time to UTC requires an assumption, not just formatting. Preserve precision and distinguish source event time from processing time. Tests should include daylight-saving transitions and date boundaries when the business process depends on local calendars.

Python 3 — preserve an instant while changing its offset

from datetime import datetime, timezone

source = datetime.fromisoformat("2026-03-08T01:30:00-05:00")
utc = source.astimezone(timezone.utc)
assert utc.isoformat() == "2026-03-08T06:30:00+00:00"
assert source == utc
from datetime import datetime, timezone

source = datetime.fromisoformat("2026-03-08T01:30:00-05:00")
utc = source.astimezone(timezone.utc)
assert utc.isoformat() == "2026-03-08T06:30:00+00:00"
assert source == utc
from datetime import datetime, timezone

source = datetime.fromisoformat("2026-03-08T01:30:00-05:00")
utc = source.astimezone(timezone.utc)
assert utc.isoformat() == "2026-03-08T06:30:00+00:00"
assert source == utc

An explicit offset makes this instant unambiguous. This does not infer a named timezone from an offset, validate a local daylight-saving transition, or define how an offset-free source value should be interpreted.

20. What is data profiling?

Profiling examines actual data characteristics such as distributions, missingness, uniqueness, ranges, and format variation before or during pipeline design. It helps discover assumptions that a schema alone does not express. For example, a field documented as unique may contain duplicates from historical migrations. I would profile representative periods and sources rather than trust a small clean sample. Profiling identifies evidence and anomalies; it does not authorize deleting unusual records or prove that an observed pattern will remain stable.

21. How do validation and transformation differ?

Validation checks whether data satisfies a contract, while transformation changes its representation or derives new information. They often occur together, but keeping their purposes distinct makes failures easier to explain. Parsing a date transforms text into a typed value; checking whether the date is permitted validates it. I would avoid a transformation that silently repairs every invalid value without an agreed rule. Record whether a row passed, failed, or was corrected so quality metrics reflect what actually happened.

22. What are common dimensions of data quality?

Useful dimensions include completeness, validity, uniqueness, consistency, timeliness, and accuracy relative to an authoritative reference. They measure different things: a field can be complete and valid yet factually wrong. I would choose checks based on the dataset's intended use and assign thresholds with its owner. A generic quality score can hide a critical failure in one dimension. Report the population and denominator for each metric, and distinguish unavailable evidence of accuracy from evidence that values are accurate.

23. What is deduplication?

Deduplication identifies records that represent the same intended entity or event and applies a defined retention or merge rule. It requires a key and a conflict policy, not simply a DISTINCT operation over all columns. Two rows may describe the same event with different ingestion timestamps, while two identical purchases can be legitimate separate events. I would retain provenance and test conflicting versions. If the business identity is uncertain, quarantine or investigate instead of inventing a convenient rule that loses real activity.

Python 3 — distinguish replay duplicates from conflicts

def deduplicate(events):
    seen = {}
    for event in events:
        key = event["event_id"]
        if key in seen and seen[key] != event:
            raise ValueError("conflicting content for event identity")
        seen[key] = dict(event)
    return list(seen.values())

one = {"event_id": "e1", "amount_minor": 250}
assert deduplicate([one, dict(one)]) == [one]
try:
    deduplicate([one, {"event_id": "e1", "amount_minor": 300}])
except ValueError:
    pass
else:
    raise AssertionError("conflict was not rejected")
def deduplicate(events):
    seen = {}
    for event in events:
        key = event["event_id"]
        if key in seen and seen[key] != event:
            raise ValueError("conflicting content for event identity")
        seen[key] = dict(event)
    return list(seen.values())

one = {"event_id": "e1", "amount_minor": 250}
assert deduplicate([one, dict(one)]) == [one]
try:
    deduplicate([one, {"event_id": "e1", "amount_minor": 300}])
except ValueError:
    pass
else:
    raise AssertionError("conflict was not rejected")
def deduplicate(events):
    seen = {}
    for event in events:
        key = event["event_id"]
        if key in seen and seen[key] != event:
            raise ValueError("conflicting content for event identity")
        seen[key] = dict(event)
    return list(seen.values())

one = {"event_id": "e1", "amount_minor": 250}
assert deduplicate([one, dict(one)]) == [one]
try:
    deduplicate([one, {"event_id": "e1", "amount_minor": 300}])
except ValueError:
    pass
else:
    raise AssertionError("conflict was not rejected")

This in-memory fixture assumes a non-null, unique source event identity and compares complete dictionaries. Production code needs schema validation, a bounded or durable deduplication strategy, and a defined retention horizon. Two distinct event IDs remain distinct even if their business values match.

24. How can joins introduce data errors?

A join can drop unmatched rows or multiply rows when the relationship is not the expected one-to-one or many-to-one mapping. I would verify key uniqueness and compare counts and business totals before and after the join. For example, joining an order to several customer-history versions without an effective-date condition can inflate revenue. The correct join type depends on the intended output and missing-reference policy. A syntactically valid query is not evidence that the relationship matches the business model.

25. How do filtering and aggregation change a dataset?

Filtering removes records according to a predicate; aggregation summarizes records at a new grain. Both can make reconciliation harder if their effects are not explicit. I would record why records are excluded and define the dimensions of each aggregate. Summing line amounts by day produces a different dataset from counting distinct orders by day. Validate null handling, duplicate effects, and boundary conditions. Retaining a clear mapping from source population to output helps explain differences without assuming every reduced row count is data loss.

26. What is a lookup transformation?

A lookup enriches or validates an input using reference data, such as mapping a source country code to a controlled identifier. I would define key uniqueness, effective dates, and behavior for missing or multiple matches. A cached lookup improves some workloads but can return stale values if the reference changes during processing. For repeatable results, use an appropriate reference snapshot or version. Do not choose the first matching row merely because the implementation returns it first.

27. What is a fact table?

A fact table records measurements or events at a defined grain, often linked to descriptive dimensions. For example, a sales fact might represent one order line with quantity and amount. Not every measure is additive across every dimension: balances cannot usually be summed across dates without changing their meaning. I would define the grain, measures, and allowed aggregations before loading. A fact table may also be factless, recording that an event or relationship occurred without a numeric measure.

28. What is a dimension table?

A dimension table stores descriptive context used to interpret facts, such as product category, customer segment, or location. Its key and history policy determine which description a fact should reference. I would ask whether a report needs the current description or the description effective when the event happened. Treating all attributes as permanent can rewrite historical interpretation unintentionally. Dimensions also need data-quality controls; a successful fact load does not guarantee that its descriptive relationships are complete or correct.

29. How do Type 1 and Type 2 slowly changing dimensions differ?

Type 1 overwrites a tracked attribute with its current value, while Type 2 preserves versions so historical facts can be associated with the appropriate context. A Type 2 design commonly uses a surrogate version key and effective interval, but the exact schema varies. I would decide history behavior per attribute and define how corrections differ from genuine changes. Preserving versions increases complexity, especially for late data and overlapping intervals; it is useful only when that history supports a clear requirement.

30. What is change data capture?

Change data capture identifies source inserts, updates, and deletes so downstream systems can process changes instead of repeatedly scanning everything. Implementations may use logs, triggers, or comparison mechanisms, each with different guarantees and operational costs. I would check whether the capture includes old values, transaction ordering, schema changes, and deletion events. CDC is not automatically a complete audit log or an exactly-once delivery guarantee. A reliable pipeline must handle snapshots, restart positions, retention gaps, and sink behavior explicitly.

31. What is a watermark in an incremental batch load?

A batch watermark records an extraction boundary such as a source update timestamp or monotonic sequence. It helps determine what to read next, but its safety depends on how the source assigns and exposes that value. I would use explicit lower and upper boundaries and advance committed progress only after the corresponding destination work is durable. This differs from a streaming event-time watermark, which concerns progress through event time. Equal timestamps, late commits, and backdated corrections require additional design rather than blind reliance on a maximum value.

32. What is the difference between append, replace, and upsert loading?

Append adds records, replace substitutes a defined target population, and upsert inserts new keys or updates existing ones under a conflict rule. Each can be correct for a different contract. An immutable event table may append by unique event ID, while a current-state customer table may upsert. I would define scope and retry behavior before choosing. An upsert does not by itself prevent an older source version from overwriting a newer one, and replace must not expose a partially rebuilt dataset.

33. What is idempotency in an ETL pipeline?

An idempotent operation produces the same intended durable result when the same logical input is applied again. This is important because retries and replays are normal. For example, replacing a specific batch partition from a stable input can be idempotent, while incrementing a balance on every retry is not. I would define identity and side effects carefully: duplicate notifications or new surrogate keys can violate the intended result even if row counts look correct. Idempotency must cover the complete operation, not only one statement.

34. Why are transactions useful during loading?

Transactions can make a group of destination changes commit together or roll back together within the database's supported scope. They help prevent partial visibility and inconsistent related updates. I would still define isolation, retry behavior, and transaction size because long transactions can cause contention and resource pressure. A database transaction does not automatically include a file upload or an external API call. When a workflow spans systems, recovery and reconciliation must address the gap between those independently committed effects.

35. What does an ETL orchestrator do?

An orchestrator coordinates dependencies, schedules, retries, parameters, and operational state for pipeline tasks. It does not automatically validate business correctness or make every task idempotent. I would use it to express meaningful dependencies and observable recovery boundaries, while keeping transformation logic testable independently where practical. A task marked successful can still have produced incomplete data if its own validation was weak. Ownership, alert routing, and clear run context are as important as drawing a dependency graph.

36. How do a schedule and a data dependency differ?

A schedule determines when work is eligible to run; a data dependency describes what must be available or complete before it can run correctly. A job scheduled at midnight may start before its source file arrives. I would verify readiness through an explicit contract, such as a manifest or upstream completion signal, rather than assume time implies completeness. The readiness signal itself needs a defined meaning. An empty dataset can be a legitimate completed input, so absence of rows is not always absence of data.

37. What should an ETL run log contain?

A useful log records run identity, code and configuration version, input boundaries, task outcomes, timing, counts, and actionable error context. It should help trace a result without exposing secrets or unnecessary personal data. I would distinguish rows read, rejected, deduplicated, and written so a single success count is not misleading. Retain correlation identifiers across tasks and define log access and retention. Logs support diagnosis, but they do not replace durable progress state or an independently checkable data reconciliation.

38. How would you handle a rejected record?

I would preserve enough authorized context to explain the rejection, assign a reason code, and separate it from valid output according to the dataset contract. Depending on severity, the pipeline may continue with a disclosed partial result or fail the batch. A quarantine location needs security, retention, and a repair process rather than becoming a permanent dumping ground. After correction, replay should use the same identity rules as normal ingestion so the record is neither silently lost nor loaded twice.

39. What is data reconciliation?

Reconciliation compares the expected and actual populations and values across pipeline boundaries. Counts are useful, but matching counts alone can conceal missing records replaced by duplicates or incorrect field mappings. I would compare stable identifiers, partitions, control totals, and selected field-level evidence appropriate to the risk. Explain intentional differences from filtering or aggregation. Reconciliation should produce actionable exceptions and a defined acceptance decision, not merely a dashboard that turns green whenever two broad totals happen to match.

40. How would you describe a simple reliable ETL design?

I would define a bounded input and its identity, extract it reproducibly, validate and transform with versioned rules, load into a controlled destination scope, and reconcile before making it available. Persist progress only when the corresponding result is durable, and make replay safe. For a daily orders feed, a manifest, run-specific staging table, key checks, and atomic publication can provide a clear recovery story. The simplest design is the one that meets actual correctness and freshness needs with understandable failure behavior.

ETL Interview Questions and Answers for Intermediate

Explore incremental boundaries, historical data, parsing, retries, orchestration, and diagnosis. Strong answers identify edge cases and describe how they would verify the proposed behavior.

41. How would you choose a safe incremental extraction key?

I would prefer a source-supported change sequence or capture mechanism whose ordering and visibility semantics are documented. A last-modified timestamp is usable only if updates reliably change it and late commits or equal values are handled. A composite cursor can resolve ties but cannot recover changes that the source never exposes beyond the cursor. I would test inserts, updates, deletes, and concurrent commits around the boundary. When guarantees are insufficient, use overlap with deduplication, periodic comparison, or a stronger capture mechanism.

42. How should lower and upper extraction boundaries be managed?

I would capture a stable upper boundary for the run and process a clearly defined interval from the previously committed position. The exact inclusive or exclusive convention must match the cursor contract and be tested at equal values. Advance durable progress only after successful loading and reconciliation. If progress and output cannot commit atomically, design replay to close that gap safely. Reading an unbounded latest view while moving the watermark can make the selected population change underneath the job.

43. Why can timestamp-only incremental queries miss changes?

Timestamp precision can create ties, application clocks can disagree, updates may preserve timestamps, and a transaction can commit after the extractor has passed its recorded time. A strict greater-than comparison against the last maximum does not solve these cases. I would investigate the source's assignment and commit semantics before proposing a fix. An overlap window reduces some risks only within an explicit lateness assumption. For stronger guarantees, use supported log positions or another mechanism that captures committed changes reliably.

44. How would you process deletes in an incremental pipeline?

I would identify how the source represents deletion: an explicit flag, a change event, a tombstone, or absence from a complete snapshot. Each requires a different rule. Absence from a partial extract is not evidence of deletion. I would define whether the target physically deletes, marks inactive, or preserves history, and coordinate retention obligations with the data owner. Replays and out-of-order events must not resurrect a record unintentionally. Reconcile deleted identities as carefully as inserted and updated ones.

45. How do snapshot extraction and CDC fit together?

A snapshot establishes an initial state, while CDC carries subsequent changes. Their boundary must avoid both missing and duplicating changes that occur during the snapshot. I would use the connector's supported snapshot-and-log-position procedure and verify its guarantees for the specific source. Independently copying tables and then starting capture at an arbitrary current position can create a gap. The sink still needs identity and version handling because duplicate delivery may occur even when the snapshot transition is correctly coordinated.

46. How would you detect and handle a CDC retention gap?

I would monitor the consumer's progress relative to the source's retained change history and alert before recovery becomes impossible. If the required position is no longer available, do not silently resume from the newest position and claim completeness. Determine the missing interval and use an approved resnapshot or reconciliation process. Capture permissions, replication-slot behavior, and retention controls are source-specific. The recovery plan should also assess source load and downstream consistency while the replacement baseline is constructed.

47. How would you make an upsert safe against out-of-order updates?

I would compare an authoritative source version or ordering token and apply an incoming state only when its precedence is valid. The comparison must be defined for ties and deletes, not improvised from ingestion time. A later-arriving event is not necessarily a newer business state. For a simple source with strictly increasing versions per key, a conditional upsert can ignore older versions. I would test concurrent writes and conflicting equal versions, and avoid assuming a timestamp establishes a total order across unrelated systems.

Python 3 with SQLite — reject older versions on replay

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("""CREATE TABLE customer (
        id INTEGER PRIMARY KEY,
        version INTEGER NOT NULL,
        name TEXT NOT NULL
    )""")
    sql = """INSERT INTO customer (id, version, name) VALUES (?, ?, ?)
             ON CONFLICT(id) DO UPDATE SET
                 version = excluded.version, name = excluded.name
             WHERE excluded.version > customer.version"""
    db.execute(sql, (1, 2, "New"))
    db.execute(sql, (1, 1, "Old"))
    db.execute(sql, (1, 2, "New"))
    assert db.execute("SELECT * FROM customer").fetchall() == [(1, 2, "New")]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("""CREATE TABLE customer (
        id INTEGER PRIMARY KEY,
        version INTEGER NOT NULL,
        name TEXT NOT NULL
    )""")
    sql = """INSERT INTO customer (id, version, name) VALUES (?, ?, ?)
             ON CONFLICT(id) DO UPDATE SET
                 version = excluded.version, name = excluded.name
             WHERE excluded.version > customer.version"""
    db.execute(sql, (1, 2, "New"))
    db.execute(sql, (1, 1, "Old"))
    db.execute(sql, (1, 2, "New"))
    assert db.execute("SELECT * FROM customer").fetchall() == [(1, 2, "New")]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("""CREATE TABLE customer (
        id INTEGER PRIMARY KEY,
        version INTEGER NOT NULL,
        name TEXT NOT NULL
    )""")
    sql = """INSERT INTO customer (id, version, name) VALUES (?, ?, ?)
             ON CONFLICT(id) DO UPDATE SET
                 version = excluded.version, name = excluded.name
             WHERE excluded.version > customer.version"""
    db.execute(sql, (1, 2, "New"))
    db.execute(sql, (1, 1, "Old"))
    db.execute(sql, (1, 2, "New"))
    assert db.execute("SELECT * FROM customer").fetchall() == [(1, 2, "New")]

SQLite UPSERT requires SQLite 3.24.0 or later. The example assumes strictly ordered per-key source versions and that equal versions have identical content; conflicting equal versions must be detected separately. It demonstrates stale-update rejection and replay, not cross-system exactly-once delivery or deletion semantics.

48. When is a merge operation insufficient for deduplication?

A merge or upsert handles conflicts according to its key and statement semantics, but it does not decide which of several source versions is authoritative. Duplicate source keys can cause errors or ambiguous behavior depending on the engine. I would normalize the source population first using a deterministic business rule and reject unresolved conflicts. Also examine side effects and related tables. One destination row per key is not proof that the correct version was chosen or that repeated execution produced no extra events.

49. How would you select one deterministic row from duplicate versions?

I would partition by the intended identity and order by a documented precedence, including a unique tie-breaker when needed. A window function such as ROW_NUMBER can implement that rule, but an incomplete ORDER BY leaves ties unresolved. If two records share a source version yet disagree on content, arbitrarily choosing one can hide corruption. I would distinguish harmless replay duplicates from conflicting versions and quarantine the latter where required. The selection rule should be stable across reruns and independent of physical row order.

Python 3 with SQLite — deterministic version selection

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("""CREATE TABLE incoming (
        id INTEGER, version INTEGER, event_id TEXT, value TEXT
    )""")
    db.executemany("INSERT INTO incoming VALUES (?, ?, ?, ?)", [
        (1, 1, "a", "old"), (1, 2, "b", "new"), (2, 1, "c", "other")
    ])
    result = db.execute("""
        SELECT id, value FROM (
            SELECT *, ROW_NUMBER() OVER (
                PARTITION BY id ORDER BY version DESC, event_id DESC
            ) AS rn
            FROM incoming
        ) WHERE rn = 1 ORDER BY id
    """).fetchall()
    assert result == [(1, "new"), (2, "other")]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("""CREATE TABLE incoming (
        id INTEGER, version INTEGER, event_id TEXT, value TEXT
    )""")
    db.executemany("INSERT INTO incoming VALUES (?, ?, ?, ?)", [
        (1, 1, "a", "old"), (1, 2, "b", "new"), (2, 1, "c", "other")
    ])
    result = db.execute("""
        SELECT id, value FROM (
            SELECT *, ROW_NUMBER() OVER (
                PARTITION BY id ORDER BY version DESC, event_id DESC
            ) AS rn
            FROM incoming
        ) WHERE rn = 1 ORDER BY id
    """).fetchall()
    assert result == [(1, "new"), (2, "other")]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("""CREATE TABLE incoming (
        id INTEGER, version INTEGER, event_id TEXT, value TEXT
    )""")
    db.executemany("INSERT INTO incoming VALUES (?, ?, ?, ?)", [
        (1, 1, "a", "old"), (1, 2, "b", "new"), (2, 1, "c", "other")
    ])
    result = db.execute("""
        SELECT id, value FROM (
            SELECT *, ROW_NUMBER() OVER (
                PARTITION BY id ORDER BY version DESC, event_id DESC
            ) AS rn
            FROM incoming
        ) WHERE rn = 1 ORDER BY id
    """).fetchall()
    assert result == [(1, "new"), (2, "other")]

SQLite window functions require SQLite 3.25.0 or later. The source contract must justify the version ordering and any tie-breaker; lexicographic event IDs are not inherently a business precedence rule. This fixture has no conflicting equal versions, which should be validated rather than silently resolved by an arbitrary tie-break.

50. How would you test a join before using it in production ETL?

I would state the expected relationship, validate uniqueness on the lookup side, and include matched, unmatched, duplicate, null, and historical-key cases. Compare output identities and measures, not only row count. For a many-to-one enrichment, unexpected multiplication is a strong signal that the reference key is not unique under the chosen conditions. I would also inspect whether filters on the joined table accidentally convert an outer join into an effective inner join. The test should encode the business contract rather than merely reproduce the SQL.

Python 3 with SQLite — expose join multiplication

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
        CREATE TABLE customers (id INTEGER, segment TEXT);
        INSERT INTO orders VALUES (10, 1), (11, 2);
        INSERT INTO customers VALUES (1, 'A'), (1, 'B');
    """)
    duplicates = db.execute("""
        SELECT id FROM customers GROUP BY id HAVING COUNT(*) > 1
    """).fetchall()
    joined = db.execute("""
        SELECT o.id, c.segment FROM orders AS o
        LEFT JOIN customers AS c ON c.id = o.customer_id
        ORDER BY o.id, c.segment
    """).fetchall()
    assert duplicates == [(1,)]
    assert joined == [(10, "A"), (10, "B"), (11, None)]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
        CREATE TABLE customers (id INTEGER, segment TEXT);
        INSERT INTO orders VALUES (10, 1), (11, 2);
        INSERT INTO customers VALUES (1, 'A'), (1, 'B');
    """)
    duplicates = db.execute("""
        SELECT id FROM customers GROUP BY id HAVING COUNT(*) > 1
    """).fetchall()
    joined = db.execute("""
        SELECT o.id, c.segment FROM orders AS o
        LEFT JOIN customers AS c ON c.id = o.customer_id
        ORDER BY o.id, c.segment
    """).fetchall()
    assert duplicates == [(1,)]
    assert joined == [(10, "A"), (10, "B"), (11, None)]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
        CREATE TABLE customers (id INTEGER, segment TEXT);
        INSERT INTO orders VALUES (10, 1), (11, 2);
        INSERT INTO customers VALUES (1, 'A'), (1, 'B');
    """)
    duplicates = db.execute("""
        SELECT id FROM customers GROUP BY id HAVING COUNT(*) > 1
    """).fetchall()
    joined = db.execute("""
        SELECT o.id, c.segment FROM orders AS o
        LEFT JOIN customers AS c ON c.id = o.customer_id
        ORDER BY o.id, c.segment
    """).fetchall()
    assert duplicates == [(1,)]
    assert joined == [(10, "A"), (10, "B"), (11, None)]

The fixture deliberately violates the intended many-to-one lookup contract and reveals both multiplication and an unmatched key. Do not fix this by choosing an arbitrary segment or applying DISTINCT to the final output; resolve the reference-data identity and history rule.

51. How should late-arriving facts be handled?

I would preserve their event time and process them according to an explicit correction policy rather than relabel them as current events. They may require updating an earlier partition or aggregate and choosing the dimension version effective at the event time. The acceptable lateness window depends on business needs and source behavior. I would make corrections visible to downstream consumers and reconcile affected periods. Silently dropping late facts to keep a schedule green can produce consistently incomplete historical reports.

52. How should a fact be handled when its dimension record has not arrived?

Options include a controlled unknown member, an inferred member, quarantine, or delaying publication, depending on the contract. I would preserve the source key and enough context to repair the relationship when the dimension arrives. An inferred member needs a clear update path so later enrichment does not create duplicate identities. The approach must be visible in quality metrics. Assigning an unrelated existing dimension merely to satisfy a foreign key creates a plausible-looking but incorrect relationship.

53. How would you implement Type 2 dimension intervals safely?

I would define nonoverlapping effective intervals, commonly half-open intervals that include the start and exclude the end, and ensure the fact lookup uses the same convention. A change closes the appropriate prior version and creates the new version within a controlled transaction or equivalent publication boundary. Enforce or test one valid version at each relevant instant per business key. Late corrections may split historical intervals, so a simple current-row update is not sufficient for every case. Use explicit timezone and precision rules.

Python 3 with SQLite — test half-open history intervals

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE dimension (
            version_key INTEGER, business_key TEXT,
            valid_from INTEGER, valid_to INTEGER
        );
        INSERT INTO dimension VALUES (1, 'C1', 0, 10), (2, 'C1', 10, 20);
    """)
    def lookup(event_time):
        return db.execute("""
            SELECT version_key FROM dimension
            WHERE business_key = ? AND valid_from <= ? AND ? < valid_to
        """, ("C1", event_time, event_time)).fetchall()
    assert lookup(9) == [(1,)]
    assert lookup(10) == [(2,)]
    assert lookup(20) == []
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE dimension (
            version_key INTEGER, business_key TEXT,
            valid_from INTEGER, valid_to INTEGER
        );
        INSERT INTO dimension VALUES (1, 'C1', 0, 10), (2, 'C1', 10, 20);
    """)
    def lookup(event_time):
        return db.execute("""
            SELECT version_key FROM dimension
            WHERE business_key = ? AND valid_from <= ? AND ? < valid_to
        """, ("C1", event_time, event_time)).fetchall()
    assert lookup(9) == [(1,)]
    assert lookup(10) == [(2,)]
    assert lookup(20) == []
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE dimension (
            version_key INTEGER, business_key TEXT,
            valid_from INTEGER, valid_to INTEGER
        );
        INSERT INTO dimension VALUES (1, 'C1', 0, 10), (2, 'C1', 10, 20);
    """)
    def lookup(event_time):
        return db.execute("""
            SELECT version_key FROM dimension
            WHERE business_key = ? AND valid_from <= ? AND ? < valid_to
        """, ("C1", event_time, event_time)).fetchall()
    assert lookup(9) == [(1,)]
    assert lookup(10) == [(2,)]
    assert lookup(20) == []

Integer times keep the interval boundary visible without timezone distractions. A real dimension needs constraints or validation against overlapping intervals and a policy for open-ended current versions. An event at the exclusive end is not part of the prior interval.

54. How do corrections differ from new historical dimension changes?

A new change records that the business state became different at a known effective time; a correction states that a previously recorded value or interval was wrong. They may require different treatment of existing facts and audit history. I would ask whether reports should preserve what was believed then, what is now known to have been true then, or both. Those are distinct temporal questions. Do not apply a generic Type 2 routine without agreeing which history the organization intends to preserve.

55. What is a conformed dimension?

A conformed dimension provides consistent definitions and compatible keys or mappings across datasets so measures can be compared meaningfully. It is more than copying a table into multiple systems. For example, customer segment needs shared business rules and an agreed effective-time policy. I would define ownership and how source-specific differences are represented. Forcing superficially similar attributes into one definition can erase important distinctions, so conformance should be based on semantics rather than matching column names.

56. How would you validate numeric conversion and rounding?

I would specify accepted syntax, decimal separator, precision, scale, allowed range, and rounding mode before conversion. Reject or explicitly handle values outside the contract, including nonfinite values where the runtime permits them. Test negative amounts, boundary magnitudes, and halfway rounding cases. In Python, constructing Decimal from a decimal string avoids importing a binary floating-point approximation. Conversion and business rounding are separate decisions; successful parsing does not mean the value is appropriate for the target field.

57. How would you normalize text without destroying meaning?

I would apply only transformations justified by the field's semantics, such as trimming surrounding whitespace from a controlled code. Case folding, Unicode normalization, accent removal, and punctuation removal can change identity or matching behavior, so they need explicit rules. Preserve an original value where traceability requires it. Names and free text should not inherit rules designed for case-insensitive technical identifiers. I would test representative languages and distinguish normalization for comparison from the value displayed to users.

58. How would you validate nested JSON input?

I would validate structure, required fields, types, cardinality, and allowed variations before flattening. Missing, null, and empty arrays may have different meanings. Repeated child elements can create several output rows, so the target grain and parent identity must remain clear. I would retain schema version and reject or quarantine incompatible records with useful reasons. A parser accepting valid JSON proves only syntactic validity, not that the document meets the expected business contract or can be flattened without information loss.

59. How would you choose between failing a batch and quarantining bad rows?

I would consider whether partial output remains safe and interpretable for the consumer. A few optional-description errors may permit a disclosed partial result, while missing financial keys or widespread schema drift may require stopping publication. Define thresholds and severity before the incident, with owners and escalation paths. Quarantine must be counted and repairable. Continuing every time can normalize silent loss, while failing on every harmless anomaly can make a pipeline unusable; the decision belongs in the data contract.

60. How do you prevent retries from duplicating side effects?

I would identify every durable effect, including destination rows, progress markers, notifications, and external calls. Use stable operation identifiers, uniqueness constraints, conditional writes, or transactional coordination where supported. A retry that reuses stable input is easier to reconcile than one that reads a changing latest view. If a timeout leaves the write outcome uncertain, inspect the intended result before repeating it. Database idempotency does not automatically protect an email or API action executed outside the transaction.

61. How should an orchestrated task choose its input interval?

It should use the logical interval or explicit run parameters assigned to that execution rather than derive a moving boundary from the current wall clock. This makes backfills and retries reproducible. For example, a daily partition job should continue to process the requested day even when retried several days later. The interval still needs a timezone and completeness contract. I would record the selected input version or manifest so a repeated logical interval does not unknowingly consume different source contents.

62. How would you design checkpointing for a multi-step pipeline?

I would place checkpoints at meaningful durable boundaries and record the input identity, completed output, and transformation version. A checkpoint should describe what can safely be resumed, not merely the last line of code executed. If a stage has partially committed, its restart procedure must reconcile or replace that partial state. I would test failures immediately before and after each boundary. Overly frequent checkpoints add complexity, while a single end marker can force expensive replay and hide uncertain intermediate effects.

Python 3 with SQLite — atomic batch marker and rollback

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE output (id INTEGER PRIMARY KEY, value TEXT NOT NULL);
        CREATE TABLE completed (batch_id TEXT PRIMARY KEY);
    """)
    def load(batch_id, rows, fail=False):
        with db:
            if db.execute("SELECT 1 FROM completed WHERE batch_id = ?",
                          (batch_id,)).fetchone():
                return
            db.executemany("INSERT INTO output VALUES (?, ?)", rows)
            if fail:
                raise RuntimeError("injected before batch marker")
            db.execute("INSERT INTO completed VALUES (?)", (batch_id,))
    try:
        load("b1", [(1, "A")], fail=True)
    except RuntimeError:
        pass
    assert db.execute("SELECT * FROM output").fetchall() == []
    assert db.execute("SELECT * FROM completed").fetchall() == []
    load("b1", [(1, "A")])
    load("b1", [(1, "A")])
    assert db.execute("SELECT * FROM output").fetchall() == [(1, "A")]
    assert db.execute("SELECT * FROM completed").fetchall() == [("b1",)]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE output (id INTEGER PRIMARY KEY, value TEXT NOT NULL);
        CREATE TABLE completed (batch_id TEXT PRIMARY KEY);
    """)
    def load(batch_id, rows, fail=False):
        with db:
            if db.execute("SELECT 1 FROM completed WHERE batch_id = ?",
                          (batch_id,)).fetchone():
                return
            db.executemany("INSERT INTO output VALUES (?, ?)", rows)
            if fail:
                raise RuntimeError("injected before batch marker")
            db.execute("INSERT INTO completed VALUES (?)", (batch_id,))
    try:
        load("b1", [(1, "A")], fail=True)
    except RuntimeError:
        pass
    assert db.execute("SELECT * FROM output").fetchall() == []
    assert db.execute("SELECT * FROM completed").fetchall() == []
    load("b1", [(1, "A")])
    load("b1", [(1, "A")])
    assert db.execute("SELECT * FROM output").fetchall() == [(1, "A")]
    assert db.execute("SELECT * FROM completed").fetchall() == [("b1",)]
import sqlite3

with sqlite3.connect(":memory:") as db:
    db.executescript("""
        CREATE TABLE output (id INTEGER PRIMARY KEY, value TEXT NOT NULL);
        CREATE TABLE completed (batch_id TEXT PRIMARY KEY);
    """)
    def load(batch_id, rows, fail=False):
        with db:
            if db.execute("SELECT 1 FROM completed WHERE batch_id = ?",
                          (batch_id,)).fetchone():
                return
            db.executemany("INSERT INTO output VALUES (?, ?)", rows)
            if fail:
                raise RuntimeError("injected before batch marker")
            db.execute("INSERT INTO completed VALUES (?)", (batch_id,))
    try:
        load("b1", [(1, "A")], fail=True)
    except RuntimeError:
        pass
    assert db.execute("SELECT * FROM output").fetchall() == []
    assert db.execute("SELECT * FROM completed").fetchall() == []
    load("b1", [(1, "A")])
    load("b1", [(1, "A")])
    assert db.execute("SELECT * FROM output").fetchall() == [(1, "A")]
    assert db.execute("SELECT * FROM completed").fetchall() == [("b1",)]

This single-connection fixture uses the sqlite3 default legacy transaction behavior and a database-local transaction. The batch ID must refer to immutable input; a production design should detect conflicting content under a reused ID. It tests rollback and completed-batch replay, not concurrent workers or atomicity with an external source checkpoint.

63. How would you perform a controlled backfill?

I would define the affected interval, source versions, transformation version, destination scope, and downstream consequences before execution. Test a representative subset and isolate the backfill from normal runs when they might write the same keys or partitions. A historical replay may need historical reference data rather than today's lookup values. Reconcile results and communicate expected revisions. Backfill is not simply rerunning every old task: it can alter dashboards, trigger notifications, and consume resources needed by current production workloads.

64. How would you handle an API source with pagination?

I would follow that endpoint's documented cursor or offset contract and termination condition, retaining progress only in a way compatible with the source's consistency guarantees. Mutable datasets can shift between pages, so a requested page size or total count is not a stable snapshot guarantee. Use supported snapshot tokens, bounded filters, or reconciliation where available. Test empty and partial pages, repeated tokens, and failures after a page is received. Never assume all endpoints in the same service paginate identically.

65. How should ETL respond to API rate limits?

It should recognize the documented limit response, respect applicable retry timing, and use bounded backoff with jitter where appropriate. Limit concurrency and request only necessary data rather than repeatedly retrying at full speed. The retry budget should distinguish a temporary throttle from a persistent authorization or contract error. I would retain enough progress to resume safely and monitor lag. For API writes, retry design must also account for uncertain outcomes and duplicate prevention; a rate-limit strategy alone does not make writes safe.

66. Why is file arrival not always proof that a file is complete?

A producer may still be writing the file, or a multipart delivery may not yet contain every expected object. I would use a documented completion protocol such as a manifest, finalization marker, or atomic rename where supported. File size remaining unchanged briefly is only a heuristic. The completion signal should identify expected files, checksums, and logical batch scope. Consumers must also distinguish a legitimate empty batch from an incomplete one and avoid reading a filename that the producer can overwrite during processing.

67. How would you make a file-based load reproducible?

I would record an immutable object version or content checksum, its logical batch identity, parsing contract, and transformation version. A path named latest.csv is insufficient if its contents can change between attempts. Preserve authorized source evidence or a reliable reference to it for the required retention period. Validate the manifest and reject conflicting content under the same batch identity. Reproducibility also depends on reference data and configuration, so freezing the input file alone may not reproduce the output.

68. What is the difference between event time and processing time?

Event time represents when the source event occurred under its contract; processing time represents when a system handles it. Network delay, retries, and offline producers can make them differ substantially. I would choose windowing and freshness metrics according to the business question. A processing-time daily count can move an old event into today's result, while event-time handling may revise an earlier period. Source clocks and timestamp quality also matter, so event time should not be treated as automatically authoritative.

69. What does a streaming watermark mean?

A streaming watermark is a system's estimate or declaration of progress through event time, used with windowing and lateness policies. It is not a universal proof that no earlier event can ever arrive. The meaning depends on the source and execution framework. I would examine allowed lateness, triggers, and accumulation or correction behavior before interpreting an emitted result as final. This concept differs from storing a maximum modified timestamp for an incremental batch extract, even though both are often called watermarks.

70. How would you reconcile an aggregation pipeline?

I would compare the defined source population with aggregates using measures that reflect the transformation, such as grouped counts, sums in consistent units, and distinct-key counts. A global total can conceal offsetting errors between groups. Include rejected and excluded records in an explainable accounting of the population. Test nulls, duplicate events, and late corrections. For floating-point measures, define appropriate comparison tolerances; for exact monetary totals, preserve the agreed decimal rules instead of accepting unexplained differences as rounding noise.

Python 3 — reconciliation beyond equal row counts

expected = {"a": 100, "b": 200, "c": 300}
actual = {"a": 100, "b": 250, "d": 300}
assert len(expected) == len(actual)
missing = set(expected) - set(actual)
unexpected = set(actual) - set(expected)
changed = {key for key in expected.keys() & actual.keys()
           if expected[key] != actual[key]}
assert missing == {"c"}
assert unexpected == {"d"}
assert changed == {"b"}
expected = {"a": 100, "b": 200, "c": 300}
actual = {"a": 100, "b": 250, "d": 300}
assert len(expected) == len(actual)
missing = set(expected) - set(actual)
unexpected = set(actual) - set(expected)
changed = {key for key in expected.keys() & actual.keys()
           if expected[key] != actual[key]}
assert missing == {"c"}
assert unexpected == {"d"}
assert changed == {"b"}
expected = {"a": 100, "b": 200, "c": 300}
actual = {"a": 100, "b": 250, "d": 300}
assert len(expected) == len(actual)
missing = set(expected) - set(actual)
unexpected = set(actual) - set(expected)
changed = {key for key in expected.keys() & actual.keys()
           if expected[key] != actual[key]}
assert missing == {"c"}
assert unexpected == {"d"}
assert changed == {"b"}

Values represent exact integer minor units under an assumed common currency. Equal counts conceal one missing identity, one unexpected identity, and one changed value. Real reconciliation must also account explicitly for intentional filters, aggregation, and duplicates before constructing a unique-key mapping.

71. How do unit, integration, and end-to-end tests differ for ETL?

Unit tests isolate transformation rules, integration tests exercise real interfaces or compatible test systems, and end-to-end tests validate a complete business flow. Each catches different failures. A pure function test cannot prove connector pagination or database permissions are correct. I would use small deterministic fixtures for edge cases, then representative interface tests and reconciliation of a controlled full run. Test failure and replay paths as well as successful processing, and state clearly when an example was only statically reviewed.

72. What makes a useful ETL test fixture?

It includes deliberate cases that challenge the contract: duplicates, missing values, boundary timestamps, conflicting versions, unmatched keys, deletes, and malformed records. Expected outputs should be independently reasoned rather than generated by the same transformation being tested. Keep fixtures small enough to inspect and free of unnecessary sensitive data. I would also include a normal case so failure handling does not overshadow the intended behavior. A large random dataset can supplement tests, but it does not replace carefully chosen edge cases.

73. How would you detect schema drift?

I would compare incoming structure and semantics with a versioned contract and classify changes by compatibility. A new optional field may be acceptable, while a changed type, missing required field, or altered unit may require intervention. Field-name comparison alone misses semantic drift. I would monitor unexpected values and distributions as complementary evidence. The response should be deliberate: accept a compatible change, route it for review, or stop affected publication rather than automatically coercing all deviations into the old schema.

74. How would you protect credentials in ETL jobs?

I would use an approved secret-management mechanism and scoped service identities, keeping credentials out of code, source files, logs, and command-line output. Define rotation and ownership so the pipeline does not depend on one employee's unmanaged account. Test authorization using the actual job identity. Encryption in transit protects one exposure path but does not fix excessive destination access or secret leakage in exceptions. I would also review connector configuration and diagnostic logging for accidental disclosure of tokens or connection strings.

75. How would you minimize sensitive data in a pipeline?

I would identify which fields are necessary for the agreed purpose and remove or transform unnecessary sensitive fields as early as the architecture and requirements allow. Apply appropriate access, retention, and encryption controls to staging, quarantine, backups, and logs as well as final tables. Masking one reporting column does not remove raw copies elsewhere. I would involve the responsible data owner for retention and use decisions. Technical controls support the policy but do not by themselves establish legal compliance.

76. What is data lineage, and how is it useful?

Lineage records how datasets and outputs relate to sources, transformations, and runs. It helps assess impact, explain results, and investigate failures. I would capture identities and versions at a level useful for the risk, distinguishing dataset-level lineage from column-level derivations. A diagram alone can become stale; operational events and versioned transformation metadata improve traceability. Lineage does not prove correctness or completeness, but it makes those questions easier to investigate when paired with quality and reconciliation evidence.

77. How would you diagnose a slow ETL job?

I would identify which stage became slower and compare input volume, data distribution, resource use, query plans, network behavior, and recent changes. Measure before changing parallelism or infrastructure. A join that unexpectedly multiplies rows can appear to be a capacity problem while actually being a correctness defect. I would reproduce a representative case and test one targeted change at a time. End-to-end latency matters, so optimizing a fast transformation may not help when extraction or destination contention dominates.

78. When can partitioning improve ETL performance?

Partitioning can reduce the data scanned or rewritten when processing aligns with partition boundaries, and it can support independent work units. Benefits depend on the engine, file layout, predicate form, and workload. Too many tiny partitions can increase metadata and scheduling overhead, while uneven partitions can create skew. I would choose keys based on access and update patterns, not simply high cardinality. Verify actual pruning and output file sizes rather than assuming a partitioned table is automatically faster.

79. What is data skew, and why does it matter?

Skew occurs when some keys or partitions carry much more work than others, causing a few tasks to dominate runtime or memory use. Increasing worker count may not help if one hot key remains concentrated. I would inspect distributions and the physical plan before choosing a mitigation such as preaggregation, a different partitioning strategy, or carefully designed salting. Each mitigation has correctness and recombination requirements. A faster plan is not acceptable if it changes grouping semantics or loses records.

80. How would you decide whether transformation pushdown is appropriate?

Pushdown executes work closer to the source or destination engine, potentially reducing transfer and exploiting optimized operations. I would compare semantics, source load, permissions, cost, and observability before moving logic. A function's null, collation, timezone, or rounding behavior may differ across engines. Test representative edge cases and inspect the plan. Pushdown is an implementation choice, not an unconditional optimization; it can make an operational source overloaded or make results harder to reproduce if dependencies are not controlled.

ETL Interview Questions and Answers for Experienced

Use these scenarios to assess end-to-end guarantees, distributed failure boundaries, publication, governance, migrations, and recovery. Look for precise scope and evidence rather than universal claims about a tool.

81. How would you define an end-to-end exactly-once claim?

I would specify the input identity, state boundary, sink effects, failure model, and observable outcome before using that phrase. A framework may provide exactly-once state updates under certain conditions without preventing duplicate effects in an external system. The claim must cover replay, checkpoint recovery, and sink commits together. I would test failures at those boundaries and retain reconciliation evidence. When guarantees are narrower, describe them precisely, such as at-least-once delivery with idempotent destination updates, rather than promise universal duplicate-free behavior.

82. How would you recover from a timeout after a destination commit?

I would treat the outcome as uncertain and inspect the destination using stable batch or operation identity before retrying. A client timeout does not prove the transaction rolled back. If the intended output is already committed, reconcile it and advance progress through the approved recovery path; if it is partial or absent, use the designed replay procedure. Blindly inserting again can duplicate data. The pipeline should retain enough durable correlation evidence to distinguish these cases without relying solely on transient logs.

83. How would you coordinate data publication with checkpoint advancement?

I would prefer committing output and its progress marker in one supported transactional boundary when feasible. When they reside in different systems, explicitly handle both possible gaps: output committed but progress not advanced, and progress advanced without complete output. The first can often be recovered through idempotent replay; the second can cause data loss and should be prevented by the protocol. A run-success flag alone is insufficient. I would document ordering, fencing, reconciliation, and recovery ownership for the chosen design.

84. How would you prevent overlapping runs from corrupting the same target?

I would define which runs may write each key or partition and enforce that ownership through scheduling constraints, locks, conditional writes, or fencing tokens as appropriate. A scheduler setting alone may not cover manual runs or another deployment. Detect stale workers so a delayed old run cannot publish over newer output after its lease expires. I would test concurrent execution and failure recovery. The objective is a verifiable write-order policy, not arbitrary delays intended to make races less likely.

85. How would you publish a rebuilt dataset without exposing partial results?

I would construct and validate the replacement separately, then switch reader visibility through a supported atomic mechanism such as a transactional table operation or a versioned manifest pointer. The exact guarantee depends on the storage and query engine. Merely renaming several files one after another is not necessarily atomic for the dataset. Preserve the previous valid version for the agreed recovery period and ensure readers use a consistent version. Reconciliation should happen before publication, with post-publication checks for actual consumer access.

86. How would you design a CDC pipeline across a source schema migration?

I would coordinate the producer schema, connector representation, transformation contract, and sink compatibility before the change. Test representative old and new events, including replay from retained history. A schema change may affect keys or value meaning, not just add a nullable field. I would stage compatible changes and define a rollback or recovery boundary when old consumers cannot interpret new data. Do not assume a connector emitting schema metadata means every downstream system automatically handles the migration correctly.

87. How would you handle source identifiers that can be reused?

I would model the entity lifecycle explicitly, adding a source namespace, generation, or another authoritative discriminator when the business system reuses an identifier. Otherwise, a new account may overwrite the history of a previous account with the same number. The solution must come from a reliable source contract, not an invented timestamp guess. I would examine deletes, recreations, and cross-system collisions in tests. Retaining only a convenient natural key can make later reconciliation impossible once the original identity context is lost.

88. How would you reconcile a source that changes during a long extract?

I would first determine whether the source offers a consistent snapshot, export version, or transaction isolation suitable for the extraction. Without one, different pages or tables can reflect different moments. A bounded query alone does not guarantee cross-table consistency. I would document the permitted inconsistency and use change capture or a follow-up reconciliation strategy when necessary. Long snapshots can have operational costs, so coordinate with source owners rather than enabling stronger isolation without assessing retention, locking, or maintenance effects.

89. How would you choose between reprocessing a partition and applying row-level corrections?

I would compare correction volume, dependency complexity, write cost, reader consistency, and the ability to reproduce the original input. Rebuilding a bounded partition can simplify correctness, while row-level changes may be more efficient for sparse corrections but require careful handling of aggregates and history. Neither is universally safer. I would test late deletes and changed grouping keys, which can require removing an old contribution as well as adding a new one. Reconcile the entire affected scope after either approach.

90. How would you manage a backfill that competes with current ingestion?

I would define separate resource budgets and priorities while enforcing safe target ownership. Current freshness requirements may justify throttling historical work, but the backfill still needs a completion and monitoring plan. Estimate volume and test a representative interval before scaling. Coordinate downstream refreshes so partially backfilled history is not mistaken for a complete correction. I would monitor both data correctness and operational impact, and retain a pause or recovery mechanism that does not leave progress markers ahead of durable output.

91. How would you prove that a pipeline has not silently lost data?

I would establish source completeness evidence and reconcile identities and relevant values through each intentional transformation boundary. Track rejects, exclusions, merges, and deletions so every difference has an explanation. Counts and successful task statuses are necessary signals but cannot prove absence of loss by themselves. The strength of the conclusion is limited by source visibility and retained history. I would state those limits and use periodic independent comparison to detect gaps that the normal ingestion mechanism might not notice.

92. How would you design observability around business correctness?

I would combine operational metrics with measures tied to the data contract: freshness by source, expected population, key uniqueness, reconciliation exceptions, rejected records, and distribution changes. Alert thresholds should reflect meaningful consumer impact and known seasonality. A pipeline can meet its runtime target while delivering the wrong currency or missing a region. I would connect alerts to owners and diagnostic evidence, and test alerting with controlled failures. Avoid a single green status that conflates infrastructure health with trustworthy data.

93. How would you define a freshness service-level objective?

I would define the measurement start and end points, population, percentile or threshold, exclusions, and consumer-visible availability. Event creation to queryable output differs from job start to job finish. A source that delivers late can violate end-to-end freshness even when ETL runs quickly. I would measure the age of relevant data and distinguish source delay from processing delay. The objective should be agreed with consumers and paired with completeness expectations so an empty or partial result cannot satisfy it trivially.

94. How would you investigate a sudden drop in daily row count?

I would compare the affected partition with source availability, extraction boundaries, filters, schema changes, rejection counts, and expected business patterns. Determine whether the drop is real activity or a pipeline defect before replaying or changing thresholds. Inspect stable identifiers and source totals, not only the destination dashboard. A successful run may have read an incomplete file or lost permission to part of the source. I would preserve evidence and correct the narrow cause, then reconcile downstream aggregates that used the incomplete result.

95. How would you investigate a sudden increase in a business total after deployment?

I would compare the old and new transformation versions on the same controlled input, tracing changed keys and measures through joins, filters, units, and aggregation grain. A duplicated lookup or altered currency rule can increase totals without changing source activity. Check whether the release also changed reference data or parsing behavior. I would avoid patching the final total to match expectations without explaining the discrepancy. Recovery should address affected outputs and downstream consumers, with evidence that the corrected logic produces the intended result.

96. How would you handle a poison record that repeatedly crashes a stream?

I would capture a bounded, appropriately sanitized diagnostic record and classify whether the failure is malformed data, an unsupported schema, or a code defect. The contract determines whether processing may continue after quarantine or must stop to preserve correctness. Prevent infinite retry loops from hiding growing lag, but do not silently discard the record. Preserve its identity for repair and replay, and monitor the quarantine population. A dead-letter mechanism needs ownership and a tested recovery path, not merely a destination queue.

97. How would you design retention for raw, staged, and curated data?

I would define each layer's purpose, recovery requirements, sensitivity, cost, and applicable organizational obligations. Raw data can support replay, but retaining it indefinitely may be unnecessary or inappropriate. Temporary staging often needs a shorter lifecycle than approved historical output. Deletion policies must include derived copies, quarantine, logs, backups, and downstream systems where relevant. I would involve responsible owners and test lifecycle behavior. A storage setting alone does not establish that every copy is removed or that legal retention requirements are satisfied.

98. How would you assess a request to anonymize data by hashing identifiers?

I would explain that deterministic hashing usually preserves linkability and may be reversible by guessing a small input space. It should not automatically be described as anonymization. The appropriate technique depends on the purpose, threat model, access controls, and whether reidentification is required. I would review the complete dataset because combinations of other attributes can identify people. Use approved privacy and security guidance and involve qualified owners; a technical transformation alone does not establish compliance or eliminate disclosure risk.

99. How would you secure an ETL pipeline's dependency and deployment process?

I would use reviewed, versioned dependencies and controlled build artifacts, restrict deployment privileges, and protect configuration and secrets separately from source code. Test the artifact that will actually run and retain its provenance. A dependency update can change parsing or serialization semantics as well as introduce security issues. I would plan rollback and monitor behavior after release. The controls should cover connectors, plugins, container images, and execution identities, not only the main transformation script.

100. How would you choose an ETL tool without relying on feature checklists?

I would test representative correctness and recovery scenarios, including source consistency, schema evolution, replay, observability, access control, and destination publication. Evaluate operational ownership, portability, cost, and the skills required to maintain the system. A connector existing in a catalog does not prove it supports the needed change semantics or scale. I would prototype the hardest failure case and document limitations before committing. The best choice is the one whose guarantees and operating costs fit the actual workload.

101. How would you migrate an ETL pipeline to a new platform?

I would inventory transformations, schedules, state, identities, dependencies, and consumer expectations, then compare old and new outputs on controlled inputs. Differences in SQL semantics, timezone handling, null ordering, or precision can appear even when the logic looks equivalent. Run a bounded parallel validation phase with clear authority over production writes. Define checkpoint cutover and rollback carefully to avoid gaps or duplicates. Acceptance should include business reconciliation and recovery exercises, not merely successful execution on the new platform.

102. How would you version transformation logic and reference data together?

I would record a reproducible combination of code, configuration, schema, and reference-data versions for each run. A code commit alone is insufficient if exchange rates or classification mappings change independently. Decide whether a replay should reproduce the original interpretation or apply corrected rules, and label the result accordingly. I would retain the necessary versions for the agreed recovery period. Explicit versioning makes differences explainable and prevents a historical rerun from silently producing current-policy results.

103. How would you handle a change to the business definition of a metric?

I would distinguish a corrected implementation from a genuinely new definition, identify affected consumers, and decide whether history will be restated or a measurement boundary preserved. Version the definition and show examples of changed inclusion, grain, or calculation rules. A column retaining the same name can conceal a major semantic change. I would validate both old and new results and communicate the transition. The pipeline should support an honest comparison rather than make historical numbers appear directly comparable when they are not.

104. How would you design a data contract between producer and consumer?

I would define meaning, schema, identity, change semantics, completeness, freshness, quality, access, retention, and ownership, with a process for compatible and breaking changes. Include examples and failure behavior so the contract is testable. A schema registry can enforce some structural rules but cannot express every business invariant. I would agree how consumers learn about delays or corrections and who resolves exceptions. Contracts work when both sides can observe and discuss violations, not when they are merely a document signed once.

105. How would you test a failure between two independently committed systems?

I would inject a controlled failure after the first durable effect and before the second, then exercise the documented recovery path. Verify identities, values, progress state, and side effects after replay. The test should include the uncertain-outcome case where the client did not observe the commit response. This reveals whether reconciliation and idempotency actually close the gap. A successful happy-path integration test cannot establish distributed atomicity, and a local transaction should not be described as covering another system without explicit support.

106. How would you prevent an old worker from publishing after a newer run?

I would use a monotonic generation or fencing mechanism checked by the publication target, not merely assume that cancelling the old task stopped every process. A worker can resume after a network partition or delayed operation. The target must reject stale ownership when the architecture requires that guarantee. I would test lease expiry, delayed commits, and concurrent publishers. If the storage interface cannot enforce the needed condition, redesign publication or serialize ownership through a suitable coordination boundary.

107. How would you reason about order when combining multiple source streams?

I would distinguish per-key or per-partition order from a global order and ask which the business result actually requires. Independent sources may not provide a common authoritative sequence, and timestamps can tie or reflect skewed clocks. Define conflict and reconciliation rules rather than assume arrival order represents causality. If cross-source consistency matters, the design may require explicit correlation or a coordinated source model. I would document uncertainty and avoid manufacturing a total order that has no defensible business meaning.

108. How would you handle a correction that changes an aggregation key?

I would remove or reverse the prior contribution from its old group and apply the corrected contribution to the new group, or rebuild the affected groups from authoritative detail. Updating only the new group leaves the old total inflated. Retain enough identity and prior state to determine both effects. I would test deletes, repeated corrections, and out-of-order versions. The publication boundary must keep the related changes consistent for readers, and reconciliation should cover both groups rather than only the corrected record.

109. How would you assess whether a checksum is sufficient for reconciliation?

A checksum can efficiently detect some differences, but its usefulness depends on stable canonicalization, ordering, included fields, and collision characteristics. Matching checksums are not a substitute for a trustworthy source population or a clear comparison contract. I would define how nulls, types, timestamps, and separators are encoded and avoid ambiguous concatenation. Use partition-level checks to locate differences and retain a path to record-level comparison. For high-risk results, combine checksums with identity, count, and business-invariant evidence.

110. How would you handle floating-point nondeterminism in distributed aggregation?

Different aggregation orders can produce slightly different floating-point results because arithmetic is not associative at finite precision. I would determine whether the domain requires exact decimal behavior, a deterministic method, or a documented numerical tolerance. Do not dismiss large discrepancies as harmless rounding without analysis. Tests should include scale and cancellation-sensitive inputs. The chosen comparison rule must reflect business requirements, and any tolerance should be explicit rather than widened until a failing result happens to pass.

111. How would you balance small files against parallelism in a data lake?

Small files can increase listing, planning, and open costs, while a few very large files can limit parallel work or make rewrites expensive. I would measure the query engine's behavior, partition layout, ingestion cadence, and compaction costs before selecting target sizes. There is no universal ideal file size across formats and engines. Compaction must preserve snapshot or publication semantics so readers do not see duplicates or gaps during replacement. Monitor both storage efficiency and end-to-end freshness after changing the layout.

112. How would you evaluate a query-plan change in an ETL release?

I would compare actual representative workloads, data distributions, statistics, and resource usage, not just estimated cost numbers. A plan that improves one partition may regress badly on a skewed one. Be careful with execution-plan commands that actually run statements, particularly when writes are involved. Test in an appropriate controlled environment and preserve correctness checks alongside timing. I would also consider contention and maintenance effects, since the fastest isolated query may not be the best production choice.

113. How would you respond to an ETL incident that exposed sensitive data?

I would follow the organization's incident process, limit further exposure through authorized controls, preserve necessary evidence securely, and identify affected copies and recipients. Coordinate with security, privacy, and data owners rather than independently inventing notification or deletion obligations. Correcting the visible target alone may leave staging, logs, exports, or downstream copies. I would document the actual scope and remediation evidence, then fix the design or access path that caused the exposure. Avoid placing sensitive incident details into broadly accessible tickets.

114. How would you decide whether to repair data in place or rebuild it?

I would compare the confidence in before-values, the affected scope, source reproducibility, downstream dependencies, and recovery time. A narrow verified repair can be safer than a broad rebuild when authoritative history is unavailable; a reproducible rebuild can be clearer when many derived values are affected. Neither choice should be driven only by speed. Test the recovery on a representative subset, retain evidence, and reconcile the final state. Communicate whether consumers must refresh or reinterpret previously delivered results.

115. How would you design a practical ETL interview exercise?

I would provide a small dataset with deliberate duplicates, a late update, a missing lookup, and a retry scenario, then ask the candidate to define the target grain and implement a bounded transformation. Ask for tests and a recovery explanation, not only a successful output. A follow-up change can reveal whether the design depends on unstated assumptions. Evaluate correctness, clarity, and proportionate trade-offs against job-relevant criteria. Avoid rewarding tool-specific menu recall when the role primarily requires reasoning about data and failures.

116. How would you assess an AI-generated ETL transformation?

I would review its assumptions, data contract, security implications, and edge cases exactly as I would any untrusted proposed implementation. Execute controlled fixtures and compare outputs independently, especially for joins, null handling, retries, and deletion behavior. Generated code can be syntactically convincing while using an unsupported API or wrong business rule. Do not send sensitive source data to an unapproved service for debugging. The responsible team retains ownership of the result; an explanation from the model is not verification evidence.

117. How would you manage conflicting requirements for freshness and correctness?

I would make the trade-off explicit with consumers, distinguishing provisional results from complete reconciled results where the use case permits that distinction. Some decisions can tolerate later corrections; others require a stronger completeness boundary before publication. Measure both latency and error or revision behavior. I would not silently weaken validation to meet a runtime target. The agreed contract should state what users may rely on, how corrections are communicated, and who can authorize exceptions during degraded operation.

118. How would you retire an old ETL pipeline safely?

I would identify actual consumers, schedules, credentials, retained outputs, and recovery dependencies before disabling it. Confirm that replacement data meets agreed criteria and observe a suitable transition period. A pipeline with no recent dashboard views may still feed an infrequent regulatory or operational process. Coordinate archival and access changes through authorized owners, preserve required history, and verify that downstream jobs no longer depend on it. Retirement is complete when the dependency and recovery story is understood, not merely when a scheduler entry disappears.

119. What evidence would you require before declaring an ETL migration complete?

I would reconcile source-to-target identities, values, relationships, intentional exclusions, and historical boundaries, then verify consumer queries and operational recovery. Check permissions, schedules, monitoring, lineage, checkpoint cutover, and rollback readiness. Document known differences and obtain acceptance against agreed criteria. A matching row count and a successful deployment are insufficient because wrong mappings can preserve both. The evidence should show that normal processing, late corrections, failures, and replays behave as intended on the new system.

120. What does a mature ETL operating model look like?

It combines explicit data contracts, accountable ownership, versioned transformations, reliable state management, controlled publication, and evidence-based reconciliation. Teams can explain not only a successful run but also uncertain commits, late data, schema changes, and recovery. Security and retention cover every layer, and consumers understand freshness and completeness limits. I would measure useful outcomes and recurring failure causes rather than celebrate task counts alone. Maturity means trustworthy data and manageable operations, not the largest toolset or the most complicated dependency graph.

ETL Documentation and Further Reading

These primary references cover task design, change capture, stream processing, lineage, SQL behavior, and the languages used in the examples. Product-specific guarantees must be interpreted within their documented versions and deployment assumptions; a capability in one engine is not a promise made by every ETL platform.

Team Sherlock.sh

Building fraud intelligence for live high-stake video interactions (financial discussions, hiring interviews, board meeting etc).

Catch candidate fraud before it costs you.

Sherlock AI helps you detect AI-assisted, proxy, and deepfake candidates, so every hiring decision is backed by evidence.

Ask AI about Sherlock.sh

Similar Posts