Delta Lake Patterns

Question 1

What is the MERGE operation in Delta Lake used for?

Answer 1

To perform upserts and deletes by matching records from a source dataset to a target Delta table based on a join condition and applying INSERT, UPDATE, or DELETE actions.

Question 2

What is a typical use case for MERGE INTO with a Delta table?

Answer 2

Applying change data capture (CDC) feeds or incremental updates from source systems into a target table without full reloads.

Question 3

How does a basic MERGE INTO statement look conceptually?

Answer 3

MERGE INTO target USING source ON join_condition WHEN MATCHED THEN UPDATE/DELETE WHEN NOT MATCHED THEN INSERT.

Question 4

Why is MERGE preferred over manual UPDATE + INSERT logic for upserts?

Answer 4

It encapsulates matching logic and multiple actions in a single atomic transaction, simplifying code and ensuring consistency.

Question 5

What is upsert in the context of Delta Lake?

Answer 5

An operation that updates existing rows if they match a key and inserts new rows otherwise, commonly implemented via MERGE.

Question 6

How can you implement an ‘insert-only’ incremental pattern with Delta?

Answer 6

By appending new records to the Delta table, using either simple append mode or MERGE with only NOT MATCHED THEN INSERT logic.

Question 7

What is CDC (Change Data Capture) in data engineering?

Answer 7

A pattern for capturing and applying incremental changes from a source system, such as inserts, updates, and deletes.

Question 8

How can Delta Lake support CDC-style ingestion?

Answer 8

By using MERGE INTO against a Delta table with a CDC feed that encodes operation types and keys, or by using structured streaming to apply changes incrementally.

Question 9

What is an example of a CDC merge pattern?

Answer 9

Match on business key, update non-key fields for ‘update’ events, insert new rows for ‘insert’ events, and delete rows or mark flags for ‘delete’ events.

Question 10

Why is idempotency important for MERGE-based pipelines?

Answer 10

So that rerunning the merge with the same CDC data does not corrupt or duplicate target state, allowing safe retries and backfills.

Question 11

What is the difference between UPDATE and MERGE in Delta Lake?

Answer 11

UPDATE modifies rows based on a filter, while MERGE joins source and target and can conditionally update, insert, or delete rows in a single operation.

Question 12

When might you use DELETE directly on a Delta table?

Answer 12

When removing rows that meet certain conditions, such as GDPR requests, test data cleanup, or correcting known bad data ranges.

Question 13

What is OPTIMIZE in Delta Lake?

Answer 13

A command that rewrites small files into larger, more efficient files and can optionally reorder data (e.g., via ZORDER) to improve performance.

Question 14

Why is OPTIMIZE important for long-lived Delta tables?

Answer 14

Ingestion and updates can create many small files and suboptimal layouts; OPTIMIZE consolidates and organizes files for better scan and skip efficiency.

Question 15

What trade-off does OPTIMIZE introduce?

Answer 15

It consumes compute and time to rewrite files, so it should be run with appropriate frequency based on table activity and query patterns.

Question 16

What is ZORDER BY used for in OPTIMIZE?

Answer 16

To co-locate rows with similar values for specified columns, improving data skipping when queries filter on those columns.

Question 17

When is it beneficial to ZORDER a Delta table?

Answer 17

When you consistently filter or join on certain columns (e.g., user_id, country, date) and want better clustering for those access paths.

Question 18

What is partitioning in Delta tables?

Answer 18

Physically organizing table data into directories based on one or more partition columns to enable partition pruning and focused scans.

Question 19

Why should you be careful when choosing partition columns for Delta tables?

Answer 19

Too many partitions or partitions with very high cardinality can create many small files; partitions should match common filters and produce balanced file sizes.

Question 20

What is the difference between partitioning and ZORDER in Delta?

Answer 20

Partitioning creates directory-level segmentation on a column; ZORDER reorders data within files across partitions to improve skipping on additional columns.

Question 21

How does Delta Lake support streaming reads from a table?

Answer 21

A Delta table can be used as a structured streaming source, where new data committed to the table is incrementally processed as it appears.

Question 22

How does Delta Lake support streaming writes to a table?

Answer 22

A structured streaming query can write to a Delta table as a sink, with each micro-batch or continuous update committed as a new transaction.

Question 23

What are common patterns for using Delta with streaming?

Answer 23

Streaming ingestion into bronze Delta tables, streaming transformations into silver tables, and updating gold tables via streaming or batch merges.

Question 24

Why is Delta well-suited for unified batch and streaming pipelines?

Answer 24

The transaction log and ACID semantics allow both batch and streaming jobs to read and write the same tables with consistent behavior.

Question 25

What is a medallion architecture (bronze/silver/gold) in Databricks?

Answer 25

A layered design where bronze holds raw ingested data, silver holds cleaned and conformed data, and gold holds curated, business-ready models.

Question 26

How are MERGE operations typically used in medallion architectures?

Answer 26

Silver tables often apply MERGE to integrate CDC or correct data; gold tables may MERGE to maintain slowly changing dimensions or aggregated fact tables.

Question 27

What is a typical pattern for building a silver table from a bronze Delta table?

Answer 27

Read bronze events, apply cleaning, deduplication, and business rules, then MERGE into a silver Delta table keyed by business keys and timestamps.

Question 28

What is vacuuming in Delta and how does it relate to MERGE and DELETE?

Answer 28

VACUUM removes obsolete files that are no longer referenced after updates, merges, or deletes, freeing storage while preserving time-travel retention.

Question 29

Why must you be aware of retention when vacuuming tables with recent MERGE/DELETE operations?

Answer 29

Removing files too soon can break time travel, rollback, or queries that expect older versions to exist within the retention window.

Question 30

What is the 'merge-on-read' nature of Delta tables?

Answer 30

Delta queries logically combine the effects of all logged file additions and removals at read time to present a consistent table view.

Question 31

How can large MERGE operations impact performance?

Answer 31

They may rewrite many files, create small files, and require later OPTIMIZE; tuning partitioning, join keys, and batch sizes is important.

Question 32

What are some performance best practices for MERGE on Delta tables?

Answer 32

Cluster/partition on join keys when appropriate, avoid full table scans in the ON clause, filter the source to relevant time windows, and run OPTIMIZE periodically.

Question 33

What is the importance of primary key design in Delta table operations?

Answer 33

Although Delta does not enforce primary keys natively, having clear business keys is essential for reliable MERGE logic and idempotent upserts.

Question 34

How do you handle slowly changing dimensions (SCD Type 2) with Delta?

Answer 34

Use MERGE to close out old records and insert new records with updated attributes and effective date ranges based on change detection logic.

Question 35

What is the impact of concurrent writes on Delta table design?

Answer 35

You must consider write patterns, partitioning, and job scheduling so that optimistic concurrency conflicts are rare and handled gracefully.

Question 36

How can you debug Delta operations when something goes wrong?

Answer 36

Inspect the transaction log and history (DESCRIBE HISTORY), examine recent versions, and compare snapshots before and after MERGE/DELETE operations.

Question 37

In one sentence, what is the core mental model for Delta operations on Databricks?

Answer 37

Delta tables are transactionally managed Parquet folders where you use MERGE/UPDATE/DELETE, OPTIMIZE, partitioning, and streaming reads/writes to implement robust, incremental lakehouse pipelines.