Databricks-DE-Assoc Data Transformation and Modeling Practice Test 5 — 15 Questions

Question 1

A data engineer needs to optimize the layout of a massive Delta Lake table that suffers from poor query performance due to a large number of small files and unsorted data records. Which TWO operations should the engineer execute to resolve these performance bottlenecks?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Execute the OPTIMIZE command to compact small files into larger files.. Optimizing a Delta table requires addressing both small file consolidation and multidimensional physical organization. Running OPTIMIZE compacts small files into larger, uniform file sizes, while combining it with Liquid Clustering or Z-Ordering organizes data records physically to maximize data skipping during filter queries. Together, these maintenance operations significantly reduce read input/output overhead across cloud object storage.

Answer

Execute the VACUUM command with a retention period of zero hours.

Answer

Run REPARTITION on the Delta table using the Spark DataFrame API directly.

Answer

Enable predictive optimization on the Databricks Unity Catalog managed table.

Question 2

A data engineering team is building a medallion architecture in Databricks. In the Silver layer, streaming data from Kafka must be cleaned, deduplicated, and written into a Delta table. Which Structured Streaming output mode should the engineer select to ensure append-only storage of fully processed, stateful deduplicated records?

Accepted Answer

Append output mode. When performing stateful operations such as deduplication with dropDuplicates() on a streaming DataFrame, Structured Streaming requires the output mode to be set to append. This mode allows newly emitted unique rows to be written continuously to the target Delta table without requiring complete table rewrites, fitting the streaming append-only nature of the Silver medallion layer.

Answer

Complete output mode

Answer

Update output mode

Answer

Overwrite output mode

Question 3

A data engineer is working on a Delta Lake table that has accumulated millions of small files due to frequent streaming updates. This fragmentation has significantly degraded query performance. Which operation should the engineer execute to optimize file layout without altering table data?

Accepted Answer

Invoke the OPTIMIZE command to compact small parquet files into larger files using bin-packing.. Running the OPTIMIZE command on a Delta table compacts many small files into larger, optimized files using the bin-packing algorithm. This directly addresses fragmentation issues caused by frequent streaming micro-batches, improving read efficiency and scan speeds. Data engineers frequently schedule optimization jobs as part of bronze-to-silver and silver-to-gold maintenance pipelines in production environments to maintain optimal query performance and reduce cloud storage request latency.

Answer

Execute REFRESH TABLE to update the file metadata cache across all cluster worker nodes.

Answer

Run the VACUUM command with a retention threshold of zero hours to purge all historical data files.

Answer

Execute ALTER TABLE SET TBLPROPERTIES to enable automatic file compaction on every write transaction.

Question 4

A data engineer is designing a Delta Lake pipeline that processes streaming sales transactions. The schema evolves frequently, and the pipeline must handle these changes without manual intervention. Which feature should the engineer enable to support automatic schema updates while preventing data corruption?

Accepted Answer

Enable 'mergeSchema' in the DataFrameWriter options.. Schema evolution is critical in production pipelines to accommodate changing source data structures. Delta Lake handles this automatically when .option('mergeSchema', 'true') is used during write operations. This ensures that new columns are added to the metadata without requiring a table rebuild. Understanding this mechanism is vital for maintaining robust, automated pipelines in Databricks environments where source systems are prone to upstream schema modifications.

Answer

Set the table property 'delta.autoOptimize.optimizeWrite' to true.

Answer

Use the ALTER TABLE command to manually add missing columns before writing.

Answer

Set the 'overwriteSchema' option to true during every write.

Question 5

Which TWO of the following scenarios are valid use cases for utilizing Delta Lake's Change Data Feed (CDF)?

Accepted Answer

Auditing data changes for compliance reporting.. Change Data Feed allows users to track row-level changes (inserts, updates, deletes) in a Delta table. It is essential for incremental processing, auditing, and syncing data between systems. By enabling CDF, engineers can decouple the processing logic from the source table history, simplifying complex pipelines that need to respond to specific data mutations rather than re-scanning entire datasets periodically.

Answer

Performing full table overwrites in a batch job.

Answer

Compressing large files into smaller partitions.

Answer

Querying the table history using the VERSION AS OF syntax.

Question 6

Refer to the exhibit. A data engineer is attempting to run a VACUUM command on a table, but the command fails with an error indicating that the retention period is too short. Given the configuration, what is the most appropriate action the engineer should take to safely remove files older than 7 days?

Accepted Answer

Execute VACUUM table_name RETAIN 168 HOURS.. The configuration restricts the vacuuming of files to ensure Time Travel remains available. Databricks prevents setting a retention period shorter than the log retention to avoid data loss. To safely vacuum, the engineer must temporarily adjust the configuration or ensure the retention period respects the safety threshold. Understanding these safety constraints is critical for data lifecycle management and cost control in cloud storage environments.

Answer

Set 'spark.databricks.delta.retentionDurationCheck.enabled' to false.

Answer

Delete the underlying parquet files directly from cloud storage.

Answer

Run the OPTIMIZE command followed by REFRESH TABLE.

Question 7

Which object type in Databricks Unity Catalog acts as the top-level container for organizing schemas and tables, providing a unified namespace for data assets?

Accepted Answer

Catalog. Understanding the Unity Catalog hierarchy is fundamental for data governance. The Catalog is the primary organizational unit that contains schemas, which in turn contain tables, views, and functions. This structure allows engineers to manage access control and discovery at different levels of granularity, which is essential for managing enterprise-scale data environments securely and efficiently.

Answer

Workspace

Answer

Schema

Answer

Table

Question 8

A data engineer is working with a large, partitioned table and needs to perform a complex transformation. Which THREE of the following strategies will optimize query performance for this transformation?

Accepted Answer

Use Z-Ordering on columns frequently used in WHERE clauses.. Performance optimization in Databricks relies on intelligent data layout and compute utilization. Techniques like Z-Ordering, proper partitioning, and effective filtering are essential for reducing the amount of data scanned. By selecting the right combinations of these strategies, engineers can significantly reduce the I/O overhead and compute costs, which is paramount for large-scale production ETL/ELT workflows.

Answer

Partition the table by every column in the dataset.

Answer

Always use a full table scan to ensure data integrity.

Question 9

A data engineer needs to join two massive datasets. One dataset is very small (10MB), and the other is very large (1TB). To ensure the join operation is performed as efficiently as possible, which join strategy should be enforced?

Accepted Answer

Enforce a Broadcast Hash Join.. Selecting the correct join strategy is vital for preventing data shuffles, which are expensive. A Broadcast join is perfect for scenarios where one side is small enough to fit in memory on every worker node. By broadcasting the small table, the engine avoids a massive shuffle of the large table, resulting in significantly faster performance and reduced network traffic.

Answer

Enforce a Sort-Merge Join.

Answer

Enforce a Cartesian Product Join.

Answer

Enforce a Shuffle Hash Join.

Question 10

A data engineer is building a Databricks job that processes millions of small JSON files landed in cloud storage each hour. The job currently spends most of its runtime on file listing and task scheduling overhead. The engineer wants to improve throughput without changing the downstream table schema. Which change should be made to the ingestion step?

Accepted Answer

Use Auto Loader with cloudFiles and enable file notification mode to ingest new files incrementally.. The bottleneck is file discovery and task scheduling for many small files, not the target table layout or columnar format. Auto Loader with cloudFiles incrementally discovers new files, and file notification mode avoids full directory listings by using cloud storage event notifications, which scales to high file counts. This preserves the existing schema while improving ingestion throughput.

Answer

Convert the source JSON files to Parquet with a separate Spark batch job before each hourly run.

Answer

Enable Auto Compaction on the target Delta table so small files are merged after each write.

Answer

Repartition the DataFrame to one partition per input file before writing to the target table.

Question 11

A data engineer has a Delta table named `sales` with columns `sale_id`, `customer_id`, `amount`, and `sale_date`. They need to create a new table that contains only the `customer_id` and the total `amount` per customer for all sales in 2023. Which SQL statement correctly creates this aggregated table?

Accepted Answer

CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023 GROUP BY customer_id;. The correct approach uses a CREATE TABLE AS SELECT statement with a WHERE clause filtering for 2023 sales before aggregation, followed by GROUP BY customer_id and SUM(amount). This materializes the desired summary table. The other options either misuse HAVING, omit GROUP BY, or add an extra grouping column that changes the granularity of the result.

Answer

CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023 GROUP BY customer_id, sale_date;

Answer

CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales WHERE YEAR(sale_date) = 2023;

Answer

CREATE TABLE customer_totals AS SELECT customer_id, SUM(amount) AS total_amount FROM sales GROUP BY customer_id HAVING YEAR(sale_date) = 2023;

Question 12

A data engineer maintains a Delta table where each row represents a customer record, and updates arrive continuously as change data capture events. The engineer needs to apply inserts, updates, and deletes from a staging table into the target table in a single atomic operation, matching records on customer_id. Which Delta Lake operation should be used?

Accepted Answer

MERGE INTO the target table using the staging table as the source, with matched update/delete clauses and a not-matched insert clause.. MERGE INTO is the Delta Lake primitive designed for upsert and delete semantics in one atomic transaction. By matching on customer_id, the engineer can update or delete matched rows and insert new ones from the staging table, applying CDC events idempotently. This avoids data gaps, prevents duplicate versions, and keeps the target table representing current state for downstream consumers.

Answer

Run a DELETE followed by an INSERT on the target table inside the same notebook cell.

Answer

Append the staging rows to the target table and deduplicate later with a window function during reads.

Answer

Use INSERT OVERWRITE to replace all partitions touched by the staging table with the staged rows.

Question 13

A data engineer maintains a Delta table named inventory.products with columns product_id, category, price, and updated_at. The engineer needs to create a new table that contains one row per category with the average price and the most recently updated product_id in that category. The query must be efficient and use only standard Databricks SQL. Which statement should the engineer run?

Accepted Answer

SELECT category, AVG(price) AS avg_price, MAX_BY(product_id, updated_at) AS latest_product FROM inventory.products GROUP BY category. MAX_BY is designed exactly for the pattern of retrieving a value associated with the maximum of another column within a group. Grouping by category and applying AVG for the price and MAX_BY for the product_id returns the average price and latest product per category in one aggregation. The alternatives rely on non-deterministic ordering or array indexing, which do not guarantee the row with the greatest updated_at.

Answer

SELECT category, AVG(price) AS avg_price, COLLECT_LIST(product_id)[0] AS latest_product FROM inventory.products GROUP BY category

Answer

SELECT category, AVG(price) AS avg_price, LAST(product_id) AS latest_product FROM inventory.products GROUP BY category ORDER BY updated_at

Answer

SELECT category, AVG(price) AS avg_price, FIRST(product_id) AS latest_product FROM inventory.products GROUP BY category ORDER BY updated_at DESC

Question 14

A data engineer is implementing a Type 2 slowly changing dimension in Delta Lake for a customers table. The table has columns customer_id, name, address, effective_date, end_date, and is_current. When a customer's address changes, the engineer wants to expire the existing current row and insert a new current row in a single atomic operation. Which Delta Lake feature should the engineer use?

Accepted Answer

MERGE INTO the customers table using a source of changed records, with WHEN MATCHED UPDATE to set end_date and is_current on the existing row and WHEN NOT MATCHED INSERT to add the new version. A Type 2 dimension change requires atomically expiring the current row and inserting a new current row. Delta Lake MERGE INTO expresses both actions in one statement, and its ACID transaction guarantees that readers see either the old state or the new state, never a partial update. INSERT OVERWRITE, separate DELETE and INSERT, and OPTIMIZE do not provide the same incremental, atomic behavior.

Answer

Use OPTIMIZE with ZORDER BY customer_id to reorganize the table so that new versions are placed next to old versions

Answer

Use INSERT OVERWRITE to replace the entire customers table with a new snapshot that includes the expired and new rows

Answer

Use DELETE followed by INSERT in two separate statements to expire the old row and add the new one

Question 15

A data engineer is creating a Silver table in a Delta Live Tables pipeline. The pipeline must continuously ingest new files from a cloud storage location as they arrive, and the engineer wants to avoid reprocessing files that were already ingested. Which approach should be used to read the source data?

Accepted Answer

Use Auto Loader with cloudFiles as the streaming source and let it track discovered files in its state store.. Auto Loader with cloudFiles is the purpose-built streaming source for incremental ingestion from cloud storage. It discovers new files, records processed files in a persistent state store so they are not read twice, and integrates with Delta Live Tables streaming tables. This provides continuous ingestion with exactly-once processing semantics and optional schema evolution.

Answer

Use dbutils.fs.ls(path) in a loop and append new file paths to a Delta table before reading them.

Answer

Define the source as a batch table and schedule the pipeline to run every minute with a full refresh.

Answer

Use spark.read.format("parquet").load(path) inside a streaming table definition.