Batch Ingestion & ELT

Question 1

What is batch ingestion in data engineering?

Answer 1

The process of collecting and loading data in discrete chunks at scheduled intervals rather than continuously as events arrive.

Question 2

What are common batch intervals used in ingestion?

Answer 2

Hourly, daily, weekly, or custom intervals based on business needs and source system constraints.

Question 3

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

Answer 3

A full refresh reloads all data from the source each time, while an incremental load only processes new or changed records since the last run.

Question 4

When is a full refresh acceptable for batch ingestion?

Answer 4

When the dataset is small enough that reloading it regularly is feasible and simple, and the business can tolerate the cost and latency.

Question 5

Why are incremental loads preferred for large tables?

Answer 5

They reduce processing time, I/O, and load on source systems by only handling new or changed data.

Question 6

What is a high-water mark (watermark) in incremental loading?

Answer 6

A stored value representing the last successfully processed point in a sequence, such as the latest timestamp or ID seen.

Question 7

How is a high-water mark used in practice?

Answer 7

Each batch load reads rows with a key greater than the stored watermark, then updates the watermark when the load completes successfully.

Question 8

What is change data capture (CDC)?

Answer 8

A set of techniques for capturing and replicating changes from a source system, typically by reading logs or change tables.

Question 9

What are two main approaches to CDC from databases?

Answer 9

Log-based CDC, which reads database transaction logs, and query-based CDC, which uses queries on timestamps or change flags.

Question 10

Why is log-based CDC often preferred over query-based CDC?

Answer 10

It captures all changes with minimal impact on the source, including deletes and updates, and avoids heavy polling queries.

Question 11

What is an upsert operation in the context of batch loads?

Answer 11

A combined insert and update operation that inserts new rows and updates existing rows based on a key match.

Question 12

Why are upserts important in incremental pipelines?

Answer 12

Because they allow incremental loads to keep target tables in sync with source changes without full reloads.

Question 13

What is a typical upsert pattern for batch ELT?

Answer 13

Load new data into a staging table and then MERGE from staging into the target table based on business keys and change logic.

Question 14

Why is staging data before merging into the final table a good practice?

Answer 14

It isolates raw ingest from business logic, simplifies debugging, and allows validation before impacting curated tables.

Question 15

What is ELT (Extract-Load-Transform)?

Answer 15

A pattern where data is extracted from sources, loaded into a central store, and then transformed there using its compute engine.

Question 16

How does ELT differ from ETL (Extract-Transform-Load)?

Answer 16

ETL performs transformations before loading into the target store, while ELT performs most transformations after loading, inside the warehouse or lakehouse.

Question 17

Why has ELT become popular with modern warehouses and lakehouses?

Answer 17

Because they provide scalable compute close to storage, making in-place transformations efficient and reducing the need for separate ETL servers.

Question 18

What is a common layout for ELT in a warehouse?

Answer 18

Raw tables hold ingested data as-is, staging tables standardize and clean it, and curated tables provide modeled data for consumption.

Question 19

What is the role of a landing zone or raw layer in batch ingestion?

Answer 19

To capture incoming data with minimal transformation as a source-of-truth that can be reprocessed if downstream logic changes.

Question 20

Why should raw data be treated as append-only where possible?

Answer 20

Append-only designs simplify consistency, auditing, and reprocessing without worrying about in-place edits to historical records.

Question 21

What is deduplication in batch pipelines?

Answer 21

The process of identifying and removing duplicate records that may arise from retries, CDC anomalies, or source system behavior.

Question 22

What keys are commonly used for deduplication?

Answer 22

Natural business keys combined with timestamps or monotonically increasing IDs, or surrogate event identifiers if available.

Question 23

Why must deduplication logic be idempotent?

Answer 23

Because pipelines may rerun the same batch or reprocess overlapping windows; idempotent logic ensures consistent results without duplicates.

Question 24

What is backfilling in batch pipelines?

Answer 24

Reprocessing historical data for past periods, often to fix bugs, rebuild models, or populate new tables with older data.

Question 25

What considerations are important when backfilling large ranges?

Answer 25

Managing compute load, avoiding interference with current production loads, and ensuring backfill logic is consistent with current transformations.

Question 26

What is a batch window in ingestion design?

Answer 26

The time frame during which a batch job must complete to deliver data freshness requirements, such as within an hour of data generation.

Question 27

Why is aligning batch windows with upstream and downstream SLAs important?

Answer 27

So that source systems are not overloaded, and consumers get data updates within agreed timeframes.

Question 28

What is late-arriving data in batch systems?

Answer 28

Data that arrives after the expected processing period for its event time, such as records with old timestamps appearing in later batches.

Question 29

How can a batch system handle late-arriving data?

Answer 29

By using watermark and windowing strategies, periodic backfills, or upsert logic that can revise historical partitions.

Question 30

What is a common pattern for loading fact tables incrementally?

Answer 30

Stage new fact rows, deduplicate by key, then MERGE into the fact table based on the defined grain and keys.

Question 31

How are slowly changing dimensions typically updated in batch?

Answer 31

By detecting changes in dimension attributes and applying SCD Type 1 (overwrite) or Type 2 (add new version rows) logic in batch jobs.

Question 32

Why is batch ordering and dependency management important?

Answer 32

Downstream jobs may rely on outputs from upstream jobs; running them out of order can produce inconsistent or incomplete datasets.

Question 33

What is atomicity in the context of batch loads into target tables?

Answer 33

The property that either the entire set of changes is applied or none is, ensuring consumers never see partial updates.

Question 34

How can atomic batch loads be implemented?

Answer 34

By writing to temporary tables and then swapping or renaming them in a single operation, or using transactional table formats.

Question 35

What role do checksums and row counts play in batch ingestion?

Answer 35

They help verify that the number of records and data integrity match expectations, serving as basic data quality checks.

Question 36

What is a typical data validation step after loading a batch?

Answer 36

Comparing row counts and key distributions to source or prior runs, checking for unexpected nulls, and ensuring basic constraints hold.

Question 37

Why should batch jobs be idempotent and restartable?

Answer 37

Because failures, restarts, and partial runs are inevitable, and pipelines must recover without manual data repair whenever possible.

Question 38

What is the difference between schedule-based and event-based triggering of batch jobs?

Answer 38

Schedule-based jobs run at fixed times; event-based jobs run in response to triggers such as file arrival or a prior job’s completion.

Question 39

Why is it important to log metadata for each batch run?

Answer 39

Run metadata such as timestamps, status, input ranges, and row counts supports debugging, auditing, and observability.

Question 40

What is the role of orchestration tools in batch ELT?

Answer 40

They manage job schedules, dependencies, retries, and alerting, providing end-to-end visibility over pipelines.

Question 41

What is a good one-sentence mental model for batch ingestion and ELT?

Answer 41

Regularly move and stage new or changed data in append-only fashion, validate and deduplicate it, then transform it inside the central store into curated, incremental views of the business.