Data Engineer Professional · Domain 2: Data Ingestion & Acquisition · 12% of the exam
Task 2.3: Configure CDC ingestion pipelines from relational database sources, including SQL Server, MySQL, and PostgreSQL, using Lakeflow Connect.
Change tracking and CDC on the source, the ingestion gateway and the ingestion pipeline, staging, schedules, history tracking and full refreshes.
Study it
Lakeflow Connect database connectors: gateway, pipeline and history tracking
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 2
A SQL Server database will be ingested with the Lakeflow Connect SQL Server connector. Of its 60 tables, 55 have primary keys and 5 are heaps without one. The DBAs want the least possible extra load on the database while every table is ingested incrementally. Which TWO source settings should be used? (Choose TWO.)
AChange tracking on the 5 heap tables
BChange tracking on the 55 keyed tables
CCDC on the 5 heap tables
DCDC on the 55 keyed tables
ENightly full snapshots of the 5 heap tables
Show the answer and why
AChange tracking on the 5 heap tables
Incorrect
Change tracking requires a primary key, so heaps cannot use it.
BChange tracking on the 55 keyed tables
Correct
Change tracking is lightweight and is recommended for every table that has a primary key.
CCDC on the 5 heap tables
Correct
CDC does not need a primary key, so it covers the heaps that change tracking cannot.
DCDC on the 55 keyed tables
Incorrect
CDC works without keys but adds more load than change tracking where a key exists.
ENightly full snapshots of the 5 heap tables
Incorrect
Full reloads are not incremental ingestion and add load each night.
Databricks recommends change tracking for any table with a primary key, to minimize load on the source database. CDC captures every operation and does not require a primary key, at a higher cost to the source.
To save cost, a team plans to stop the SQL Server ingestion gateway between the hourly runs of its ingestion pipeline. What does Databricks warn about?
AIt must run continuously, or changes can be lost to log retention
BNothing; the gateway only runs while the ingestion pipeline updates
CThe pipeline switches to snapshot mode while the gateway is off
DThe gateway then runs on serverless compute and costs less
Show the answer and why
AIt must run continuously, or changes can be lost to log retention
Correct
If changes are dropped because of log retention while the gateway is down, all tables need a full refresh.
BNothing; the gateway only runs while the ingestion pipeline updates
Incorrect
The gateway is designed to run continuously, unlike the pipeline.
CThe pipeline switches to snapshot mode while the gateway is off
Incorrect
There is no such fallback; dropped changes require a full refresh.
DThe gateway then runs on serverless compute and costs less
Incorrect
Gateways run on classic compute; stopping them risks data loss.
The ingestion gateway must run in continuous mode so that changes are not dropped because of the source's log retention. If changes have been dropped, a full refresh is required for all tables.
DBAs ask that the Lakeflow Connect SQL Server connector read from a read-only secondary replica to protect the primary. Is that supported?
AYes, if change tracking is enabled on the replica
BYes, if the gateway uses a read-only login
CNo; only primary instances are supported
DYes, but only for tables without primary keys
Show the answer and why
AYes, if change tracking is enabled on the replica
Incorrect
Change tracking cannot run on read replicas.
BYes, if the gateway uses a read-only login
Incorrect
The login does not change replica support.
CNo; only primary instances are supported
Correct
Change tracking and CDC are not supported on read replicas or secondary instances.
DYes, but only for tables without primary keys
Incorrect
Replica support does not depend on keys.
Support is limited to primary SQL Server instances, because change tracking and change data capture are not supported on read replicas or secondary instances.
A SQL Server heap table without a primary key, ingested with CDC, contains some fully duplicate rows. How does the connector treat this table?
AThe table is skipped until a primary key is added at the source
BEach duplicate row is kept, with a generated row ID added
CThe first column of the table is used as its primary key
DNon-LOB columns form a bundled key; duplicates become one row
Show the answer and why
AThe table is skipped until a primary key is added at the source
Incorrect
Tables without a key can be ingested with CDC.
BEach duplicate row is kept, with a generated row ID added
Incorrect
The connector does not add a row ID; duplicates collapse.
CThe first column of the table is used as its primary key
Incorrect
All non-LOB columns are bundled as the key, not one column.
DNon-LOB columns form a bundled key; duplicates become one row
Correct
The connector uses all non-LOB columns as a bundled primary key, so duplicate rows are ingested as a single row.
For a table without a primary key, the connector treats all columns except large objects as a bundled primary key. Duplicate source rows become a single row in the destination.
A new Lakeflow Connect pipeline ingests a 2 TB SQL Server database. After the first run of the ingestion pipeline, the destination tables hold only part of the source rows, and no errors are reported. What explains this?
AThe connector ingests only changes made after the pipeline was first created
BThe pipeline ran before the gateway finished extracting the existing rows
CThe destination tables apply the rest only after a manual full refresh
DThe gateway must be restarted once before it extracts existing rows
Show the answer and why
AThe connector ingests only changes made after the pipeline was first created
Incorrect
The ingestion gateway extracts historical data as well as change data when it starts.
BThe pipeline ran before the gateway finished extracting the existing rows
Correct
The gateway extracts historical and change data from its start, and an ingestion run applies only what has been extracted so far.
CThe destination tables apply the rest only after a manual full refresh
Incorrect
No full refresh is needed; the next runs of the ingestion pipeline apply the remaining rows.
DThe gateway must be restarted once before it extracts existing rows
Incorrect
The gateway extracts data as soon as it starts; restarting it only provisions its virtual machine again.
The ingestion gateway starts extracting historical and change data as soon as it runs. An ingestion pipeline run can start before all of that data is extracted, so it can take a few runs before every row reaches the target tables.