Data Ingestion and Loading practice questions

Database managed connectors replicate row changes (including deletes) from the source's change tracking or CDC log.

Lakeflow Connect database connectors read the database's change tracking or CDC information through an ingestion gateway, stage the changes and apply them to destination streaming tables, so only changed rows move and deletes are propagated. A JDBC overwrite is the full reload that already takes too long. Federation does not copy data at all. CSV extracts lose deletes and add a custom export job.

1. Note the requirements: inserts, updates and deletes, freshness in minutes, no full reloads.
2. Recognize that capturing deletes incrementally needs change tracking or CDC on the source.
3. Pick the managed database connector, which consumes those changes through its gateway.
4. Reject full reloads, in-place federation and file extracts.

Exam tip: Need deletes and minutes-level freshness from a database: managed CDC/change-tracking connector, not reloads or extracts.

Log-based CDC sees every change including hard deletes; a query-based (cursor) connector sees only the current state of rows that still exist.

Two facts decide this. First, a query-based connector reads the source with a cursor column on each run, so it captures only the latest state of changed rows and can detect deletes only when they are soft deletes flagged in a column; a hard-deleted row is invisible to it. Log-based CDC through the ingestion gateway records every change, including hard deletes and values that change twice between runs. Second, keeping history needs SCD type 2; SCD type 1 keeps only the current version of each row.

1. List the requirements: hard deletes, intermediate values, full history.
2. Rule out query-based capture: it polls current state and misses hard deletes.
3. Choose log-based CDC through the ingestion gateway.
4. Choose SCD type 2 so every version is retained, not overwritten.

Exam tip: Hard deletes + intermediate changes need log-based CDC; history needs SCD type 2 on top of it.

Streaming ingestion processes events continuously as they arrive; it is the pattern for seconds-level latency.

Message buses such as Kafka are standard-connector sources read with Structured Streaming or Lakeflow Spark Declarative Pipelines into streaming tables. Only continuous processing delivers events within seconds. An hourly triggered run reads the same stream correctly but waits up to an hour between runs. COPY INTO and nightly batch reads are batch patterns with hours of latency.

1. Identify the source: a Kafka topic (a message bus).
2. Identify the latency: seconds.
3. Choose a streaming read that runs continuously, not on an hourly or nightly schedule.

Exam tip: Seconds-level latency from a message bus = streaming read running continuously.

Triggered pipelines process the available data and stop; continuous pipelines keep compute running for low latency.

In triggered mode, a pipeline update processes whatever new data is available, incrementally for a streaming table, and then shuts its compute down. With data arriving once a night and a morning deadline, one scheduled triggered update meets the requirement. Continuous mode only pays off when data must be processed within seconds or minutes of arrival. A full refresh reprocesses all historical files, which defeats incremental loading.

1. Compare data arrival (once nightly) with the freshness needed (by 07:00).
2. Recognize that continuous mode is for low latency and keeps compute up.
3. Choose a triggered update scheduled after arrival.
4. Avoid a full refresh, which rereads everything.

Exam tip: Data arrives on a schedule and latency is hours: triggered mode on a schedule, not continuous.

Incremental batch: run a streaming source with an availableNow trigger on a schedule so it processes the backlog and stops.

This combines two facts. First, the checkpoint is what makes Auto Loader exactly-once, so it must persist across runs. Second, trigger(availableNow=True) processes everything available since the last checkpoint and then ends the query, which lets a scheduled job on cheaper job compute replace an always-on all-purpose cluster. A long processing-time trigger still keeps the cluster running. A directory batch read loses incremental tracking, and a fresh checkpoint reloads every file.

1. Find the cost driver: an always-on all-purpose cluster for hourly needs.
2. Pick a trigger that processes the backlog and stops: availableNow.
3. Keep the same checkpoint so files are loaded exactly once.
4. Run it as a scheduled job on job compute.

Exam tip: Hourly freshness from a stream: availableNow on a schedule, same checkpoint, job compute.

Incremental loading processes only data that arrived since the last successful run.

A full batch reload rereads everything, so its cost grows with the history. Incremental loading, for example Auto Loader or a streaming table over the folder, records what it has already processed and loads only new files, keeping each run proportional to new data. Continuous streaming is also incremental but adds always-on compute the nightly requirement does not need.

1. Spot the symptom: runtime grows with total history, not with new data.
2. Keep the nightly cadence the business accepts.
3. Switch to a pattern that loads only new files since the last run.

Exam tip: Growing full reloads with a fixed cadence: switch to incremental loading, not more compute.