LFC ETL & Data Integration 2 — Questions and Answers
Question 1: In a Delta Lake ETL pipeline, what is the primary purpose of the MERGE INTO statement?
- To append new records only
- To perform upserts by matching source and target rows (Correct answer)
- To delete all records and reload the table
- To partition data by a specified column
Correct answer: To perform upserts by matching source and target rows
MERGE INTO enables upsert operations by matching source and target rows on a key and then inserting, updating, or deleting based on match conditions.
Question 2: Which Change Data Capture (CDC) mode in Delta Lake reads only the changes since the last processed version?
- Full table scan mode
- Snapshot isolation mode
- Incremental CDC using table history (Correct answer)
- Broadcast join mode
Correct answer: Incremental CDC using table history
Incremental CDC leverages Delta Lake's transaction log to read only the rows that changed since a specified version or timestamp.
Question 3: When ingesting JSON data with a schema that may evolve, which Spark option should you enable to handle new fields without failing?
- mergeSchema (Correct answer)
- enforceSchema
- dropMalformed
- columnPruning
Correct answer: mergeSchema
Setting mergeSchema to true allows Spark to union the new JSON schema with the existing Delta table schema when new columns appear.
Question 4: A pipeline reads from Kafka and writes to a Delta Lake table every 5 minutes. Which Structured Streaming trigger is most appropriate?
- Trigger.Once()
- Trigger.ProcessingTime('5 minutes') (Correct answer)
- Trigger.Continuous('5 minutes')
- Trigger.AvailableNow()
Correct answer: Trigger.ProcessingTime('5 minutes')
Trigger.ProcessingTime('5 minutes') causes the streaming query to execute micro-batches on a fixed 5-minute interval.
Question 5: What Databricks feature allows you to declaratively define ETL pipelines with automatic dependency resolution and retries?
- Databricks Workflows
- Delta Live Tables (DLT) (Correct answer)
- Databricks Repos
- Unity Catalog
Correct answer: Delta Live Tables (DLT)
Delta Live Tables lets you define pipeline steps as live tables or views, and Databricks automatically manages execution order, retries, and data quality.
Question 6: Which file format checkpoint mechanism does Structured Streaming use to track progress and enable fault tolerance?
- Parquet snapshot files
- WAL (Write-Ahead Log) in HDFS/S3 (Correct answer)
- Delta transaction log entries
- Kafka consumer group offsets only
Correct answer: WAL (Write-Ahead Log) in HDFS/S3
Structured Streaming writes a Write-Ahead Log (WAL) to a checkpoint directory in a distributed filesystem to record offsets and committed batches.
Question 7: In an ELT pattern on a lakehouse, where does the transformation logic primarily execute?
- On the source system before data lands
- Inside the ETL tool before writing to cloud storage
- Inside the lakehouse compute engine after raw data is loaded (Correct answer)
- On an external database after exporting the data
Correct answer: Inside the lakehouse compute engine after raw data is loaded
ELT loads raw data first and then transforms it using the lakehouse's compute engine (e.g., Spark), taking advantage of scalable cloud resources.
LFC ETL & Data Integration 2
In a Delta Lake ETL pipeline, what is the primary purpose of the MERGE INTO statement?