Databricks-DE-Pro Data Modelling — Timed 25 Questions

Question 1

A data engineer is designing a Bronze-to-Silver pipeline for high-velocity IoT sensor data. The raw JSON logs arrive with varying schemas. Which approach best supports schema evolution while maintaining query performance in Silver?

25 minute time limit — choose an answer to begin.

Accepted Answer

Enable schema evolution on the Delta table and use a merge pattern to handle new fields dynamically.. Using schema evolution in Delta Lake allows the Silver layer to absorb new fields without pipeline failure, while enforcing schema constraints ensures data quality. By leveraging Delta's schema evolution capabilities alongside compacting small files during the write process, the engineer ensures that downstream medallion layers remain performant. This design is critical in Databricks environments where source systems frequently update their payloads without notifying downstream consumers.

Answer

Define a rigid schema in the Bronze table to force all incoming data to match the expected format.

Answer

Cast all columns to string types in the Silver layer to avoid schema mismatch errors during ingestion.

Answer

Drop all columns that do not match the pre-defined target schema before writing to the Silver layer.

Question 2

Which TWO of the following strategies best optimize a large-scale fact table in the Gold layer for point-in-time analytical queries?

Accepted Answer

Apply Z-Ordering to high-cardinality columns used in frequent join operations.. The explanation justifies partitioning by date, but Option A (partitioning) is marked as wrong. The correct options B and C are valid, but the explanation needs to be updated to focus on Z-Ordering and Star Schema design rather than partitioning.

Answer

Partition the table by every column used in the WHERE clause.

Answer

Implement row-level security for every query to ensure partition pruning works correctly.

Answer

Store all historical data in a single massive file for optimal compression.

Question 3

Which THREE factors should be considered when choosing a partitioning strategy for a Delta table?

Accepted Answer

The query patterns; prioritize columns frequently used in filters.. Option A states to avoid low-cardinality columns, which is actually correct for partitioning (you should avoid high-cardinality columns like timestamps/IDs because they cause the small file problem). The common trap note incorrectly advises to 'always prioritize low-cardinality columns', which contradicts best practices and the explanation.

Answer

The cardinality of the column; avoid low-cardinality columns.

Answer

File format compatibility; ensure all files are in CSV format.

Question 4

Refer to the exhibit. The pipeline is failing during a transformation. What is the most likely cause, and how should it be resolved?

Accepted Answer

The source schema has changed, and the pipeline needs to enable schema evolution.. The failure is caused by a schema mismatch where the source data contains new fields not present in the target Delta table. Delta Lake enforces schema by default to prevent accidental data corruption. To resolve this, schema evolution must be enabled (e.g., using .option('mergeSchema', 'true') in Spark or the evolution mode in Delta Live Tables), allowing the target table to automatically update its schema to match the incoming data.

Answer

The table needs to be VACUUMed to remove corrupted metadata.

Answer

The table partition was dropped, causing the lookup to fail.

Answer

The table size exceeds the limit for metadata management.

Question 5

A retail company uses a Databricks Lakehouse. The dimension table `dim_product` has a high rate of updates, and the fact table `fact_sales` is huge. Analysts frequently run queries that join these tables and filter on product attributes. The team wants to optimize this pattern. Which two design choices are appropriate? (Choose two.)

Accepted Answer

Use a star schema with a surrogate key in `dim_product` and a foreign key in `fact_sales`.. Using a star schema with surrogate keys ensures stable joins and simplifies dimension updates, while Z-ORDERing on join keys co-locates data to reduce shuffling during joins. Together, these choices optimize the frequent join and filter pattern in Databricks. Denormalizing or snowflaking adds complexity and does not address the performance need. Partitioning by category is not effective for join optimization.

Answer

Denormalize `dim_product` into `fact_sales` by embedding product attributes directly into the fact table.

Answer

Implement a snowflake schema by normalizing `dim_product` into multiple related tables.

Answer

Partition `fact_sales` by the product's category to improve join performance.

Question 6

A data engineer is designing a fact table in the Gold layer that will be used for monthly sales reporting. The fact table will be partitioned by 'sale_month' and will contain billions of rows. Queries typically filter by 'sale_month' and join to dimension tables on 'product_id' and 'store_id'. The engineer wants to optimize the fact table for these queries. Which approach is most effective?

Accepted Answer

Partition by sale_month and Z-ORDER BY product_id, store_id. Partitioning by sale_month allows efficient pruning for monthly filters, and Z-Ordering by product_id and store_id improves data skipping for joins. This is the recommended strategy for large fact tables in Delta Lake. Other options either cause high cardinality partitioning, use less proven features, or rely on non-native techniques.

Answer

Partition by product_id and store_id

Answer

Use liquid clustering on sale_month, product_id, store_id

Answer

Bucket the table by product_id into 256 buckets

Question 7

A streaming pipeline writes order events into a Delta Lake Silver table using Structured Streaming with a checkpoint. The team now needs to add a new derived column, order_margin, that depends on a lookup table of supplier costs which is updated daily. The Silver table already contains billions of historical rows. Which approach best lets the engineer backfill order_margin for history and keep it current going forward while minimizing cost?

Accepted Answer

Use ALTER TABLE ADD COLUMN to add order_margin, then run a one-time batch UPDATE joining the supplier cost lookup, and let the stream populate it for new rows. Evolving a Delta table with a metadata-only column addition followed by a targeted batch UPDATE is the low-cost way to backfill a derived column, and the already-running stream then maintains it for new events. This avoids replaying billions of rows from source and keeps a single authoritative Silver table.

Answer

Stop the stream, drop and recreate the Silver table with the new column, and restart from the original source

Answer

Create a new Gold table with order_margin and point all consumers there, leaving the Silver table unchanged

Answer

Enable schema evolution on the stream and rely on automatic type widening to introduce order_margin

Question 8

A data engineer is designing a Gold layer table to support analytical queries for a retail company. The table will store sales transactions and must be optimized for queries that filter on store_id and product_id, and also join with dimension tables on those keys. The table is expected to have billions of rows. Which two strategies should the engineer use to optimize query performance? (Choose two.)

Accepted Answer

Use liquid clustering on store_id and product_id.. Liquid clustering on both store_id and product_id automatically optimizes data layout for filters and joins on those columns, reducing data scanned. Z-ORDER BY product_id provides similar benefits for product_id but is less flexible than liquid clustering. Together, they offer robust optimization, though liquid clustering alone could suffice. The other options either do not address both columns or are not design strategies.

Answer

Partition the table by store_id.

Answer

Z-ORDER BY store_id.

Answer

Enable Delta Lake caching on the table.

Question 9

A healthcare provider needs to analyze patient readmissions within 30 days. The fact table `fact_admissions` records each admission with `admission_date` and `discharge_date`. The team wants to create a dimension that allows grouping by readmission status. Which dimension design is most appropriate?

Accepted Answer

Create a factless fact table to capture readmission events.. A factless fact table is ideal for tracking events that don't have numeric measures, such as readmissions. It can store the admission and readmission keys, allowing analysis of readmission intervals. This design is flexible and avoids complex derivations in the main fact table. Other dimension types do not directly support this event-based analysis.

Answer

Create a degenerate dimension in the fact table for readmission status.

Answer

Create a role-playing dimension for readmission status based on the admission date.

Answer

Create a junk dimension that combines readmission status and other flags.

Question 10

A data engineer is working with a Delta table 'user_events' that contains nested JSON data in a column 'event_properties'. The engineer needs to extract specific fields from this nested structure for a Silver layer transformation. Which SQL function should be used to extract a field named 'device_type' from the 'event_properties' column?

Accepted Answer

get_json_object(event_properties, '$.device_type'). get_json_object is the correct function to extract a single field from a JSON string column in Spark SQL. It uses a JSON path expression and returns the value as a string. The other options are either invalid syntax, unsupported functions, or less efficient for this specific task.

Answer

json_extract(event_properties, '$.device_type')

Answer

from_json(event_properties, 'device_type string')

Answer

event_properties:device_type

Question 11

Refer to the exhibit. An engineer observes that queries filtering on 'customer_id' are running slowly despite Z-Ordering. What is the most likely cause?

Accepted Answer

The queries lack filters on 'region' or 'date', preventing effective partition pruning.. The exhibit shows that the table is partitioned by 'region' and 'date', while Z-Ordering is applied to 'customer_id'. If queries filter on 'customer_id' but do not provide 'region' or 'date', the engine must scan all partitions. Z-Ordering is only effective within each partition. If the data is not well-clustered or the partitions are too large, the engine cannot skip files effectively, leading to high latency during execution.

Answer

The partition columns should include 'customer_id' to improve the pruning speed.

Answer

Z-Ordering must be performed on the partition columns instead of the join columns.

Answer

The file format should be changed to Parquet to improve individual file read performance.

Question 12

In the medallion architecture, which layer is primarily responsible for applying business logic and historical aggregations?

Accepted Answer

The Gold layer is designed for applying business logic and historical aggregations.. The Gold layer is the final stage of the medallion architecture, where data is prepared for consumption by BI tools and data science applications. It involves cleaning, transforming, and aggregating data according to business rules. This layer represents the 'source of truth' for analytical reporting, as it is structured to support specific business use cases rather than representing the raw or cleaned granular data found in earlier layers.

Answer

The Bronze layer acts as the primary layer for historical aggregation and business logic.

Answer

The Silver layer is designed for applying complex business logic and final aggregations.

Answer

The Raw layer stores all business logic results for future auditing.

Question 13

A company requires data to be physically deleted from the Bronze layer for GDPR compliance. What is the correct procedure to ensure complete removal?

Accepted Answer

Run the DELETE statement and then run the VACUUM command.. Under GDPR, 'deletion' requires the actual removal of data from all storage locations. In Delta Lake, running a DELETE statement followed by a VACUUM command is necessary. DELETE removes the records from the active state, but the underlying data files remain until VACUUM removes them. This two-step process ensures the data is logically removed, and then physically purged from the storage provider, satisfying the right to be forgotten.

Answer

Simply run the DELETE statement; Delta handles physical deletion automatically.

Answer

Use the OPTIMIZE command to force the deletion of the records.

Answer

Overwriting the table with a filtered subset using the OVERWRITE option.

Question 14

Which design pattern is best suited for handling late-arriving data in a medallion architecture?

Accepted Answer

Update the Silver layer using a MERGE operation that matches on event time.. Late-arriving data is common in streaming and batch pipelines. Using a watermarking strategy combined with a merge or upsert operation allows the system to process incoming data while maintaining the integrity of historical windows. By allowing late records to update existing states, the system remains accurate despite delays from the source, which is critical for time-sensitive financial or operational reporting where accuracy is paramount.

Answer

Discard all late-arriving records to ensure a clean source-of-truth.

Answer

Append late-arriving data to the Bronze layer but ignore it in all downstream layers.

Answer

Create a separate 'late_data' table and join it to the main table every month.

Question 15

What is the primary benefit of the Medallion architecture in a Databricks Lakehouse?

Accepted Answer

It provides a clear progression of data quality and structure.. The Medallion architecture provides a clear, structured progression of data quality. By segregating data into Bronze (raw), Silver (cleaned), and Gold (refined) layers, organizations can maintain an immutable audit trail while enabling both technical teams and business analysts to consume the data at the appropriate level of abstraction. This structure simplifies data governance, incremental processing, and data quality management, which are fundamental to building a reliable Lakehouse.

Answer

It eliminates the need for data partitioning and Z-Ordering.

Answer

It forces all data to be stored in a star schema at all layers.

Answer

It automatically converts all incoming data to a structured format.

Question 16

Which of the following describes the purpose of the 'Gold' layer in a Lakehouse?

Accepted Answer

To store business-ready data for analytical use cases.. The Gold layer is designed for high-performance analytical queries and business intelligence. By storing data in a denormalized or star schema, and by applying business-level aggregations and business rules, this layer is the primary source of truth for downstream reporting tools. It is optimized for the needs of business users, ensuring that they can access consistent and performant data without having to perform complex joins or transformations themselves.

Answer

To store the raw data in an immutable format for historical auditing.

Answer

To serve as a staging area for data cleaning and filtering.

Answer

To store only metadata for the entire Lakehouse architecture.

Question 17

An engineer needs to optimize a massive table that is frequently joined with other large tables. Which strategy is most effective for performance?

Accepted Answer

Apply Z-Ordering to the join keys.. For large-scale joins, the most effective strategy is to Z-Order the join keys. This ensures that records with the same join keys are physically stored together, allowing the Databricks engine to use merge joins rather than costly shuffle-heavy hash joins. By minimizing data movement across the cluster during the shuffle phase, Z-Ordering on join keys significantly reduces execution time and resource consumption for complex, large-scale join operations.

Answer

Increase the number of partitions to spread the data across more nodes.

Answer

Cast all join keys to strings to ensure data type compatibility.

Answer

Use a broadcast join for all large-table join operations.

Question 18

Refer to the exhibit. The engineer wants to replace only one specific partition in the 'orders' table. What is the best method in Databricks?

Accepted Answer

Enable dynamic partition overwrite mode and perform an INSERT OVERWRITE.. Using 'partitionOverwriteMode=dynamic' with a standard INSERT OVERWRITE operation is the correct way to replace a single partition without affecting the entire table. The default behavior is 'static', which overwrites the whole table. By setting the dynamic mode, the system identifies the partitions present in the incoming data and replaces only those, ensuring minimal impact and preventing accidental data loss across the entire dataset during the overwrite process.

Answer

Run a DELETE statement for the partition and then an APPEND.

Answer

Use the MERGE command to overwrite only the records in the specific partition.

Answer

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

Question 19

A Databricks workspace has a Delta table 'transactions' partitioned by 'txn_date'. Analysts frequently run queries that filter on 'txn_date' but also occasionally filter on 'account_id' alone. The table has 10 TB of data, and the team wants to improve performance for the 'account_id' queries without changing the partitioning scheme. Which Delta feature should they implement?

Accepted Answer

Z-ORDER BY account_id. Z-Ordering is the correct approach because it reorganizes data within existing partitions to improve data skipping for the specified columns without altering the partition structure. It is specifically designed to optimize queries on high-cardinality columns like account_id. Partitioning by account_id would cause scalability issues, CDF is for change tracking, and Hive tables lack Delta optimizations.

Answer

Partition the table by account_id as well as txn_date

Answer

Enable Delta Lake change data feed

Answer

Convert the table to a Hive table

Question 20

A retail company uses a Databricks Lakehouse with a star schema in the Gold layer. Their fact_sales table has billions of rows and is partitioned by sale_date. Analysts frequently run queries that filter on product_id and join to dim_product. Currently, queries scanning the entire fact table are slow. To improve performance for these queries, which approach is most appropriate?

Accepted Answer

Apply Z-ORDER BY product_id on the fact_sales table.. Z-ORDER BY product_id clusters data within each partition, allowing Delta Lake to skip files that do not contain the filtered product_id values. This reduces I/O and speeds up queries that filter or join on product_id. Partitioning by product_id is impractical due to high cardinality, and the other options do not provide the needed optimization.

Answer

Partition the fact_sales table by product_id.

Answer

Convert the fact_sales table to a Parquet table and use partition pruning.

Answer

Add a bloom filter index on product_id.