Databricks-DE-Pro Data Modelling Practice Test 2 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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.

Question 6

A retail company is designing a Gold-layer dimension table in Delta Lake for its product catalog. The catalog changes slowly: a product's category is occasionally reclassified, but historical sales fact rows must continue to reflect the category that was valid at the time of each sale. The team wants to avoid duplicating the entire product row for every change. Which Delta Lake modeling technique should the engineer implement?

Accepted Answer

Type 2 slowly changing dimension with effective-date and current-flag columns populated via MERGE INTO. Preserving point-in-time accuracy for a slowly changing attribute requires versioned dimension rows with validity ranges and a current flag, which is the defining behavior of a Type 2 slowly changing dimension. A MERGE INTO statement can close the existing row and insert a new one atomically, letting fact rows resolve the correct historical category through an effective-date join or surrogate key.

Answer

A junk dimension that collapses category combinations into a separate table keyed by a surrogate

Answer

Type 1 slowly changing dimension implemented with MERGE INTO that overwrites the category column in place

Answer

A Type 3 dimension that adds a previous_category column to the existing product row

Question 7

A data engineer is designing a Gold layer table that must support slowly changing dimension (SCD) Type 2 for a customer dimension. The source data arrives daily with updates to customer attributes. The engineer wants to implement this using Delta Lake. Which two features or techniques are essential for maintaining SCD Type 2? (Choose two.)

Accepted Answer

MERGE INTO with condition on business key and effective dates. SCD Type 2 requires a mechanism to expire old records and insert new ones, which is achieved with MERGE INTO. Additionally, the dimension table must include columns to track the validity period of each version, such as effective dates and a current flag. Time travel, generated columns, and partitioning are not essential for implementing SCD Type 2.

Answer

Using Delta Lake's generated columns for surrogate keys

Answer

Partitioning the dimension table by effective_start_date

Answer

Delta Lake time travel to query previous versions

Question 8

A financial institution uses a Databricks Lakehouse with a Silver table transactions that is partitioned by transaction_date. The table is frequently queried with filters on transaction_date and account_id. The data engineering team notices that queries filtering on account_id are slow because they scan all partitions. They want to optimize the table to accelerate these queries without repartitioning. Which Delta Lake feature should they use?

Accepted Answer

Z-ORDER BY account_id. Z-ORDER BY account_id clusters data within each partition by account_id, allowing Delta Lake to skip files that do not contain the filtered account_id values. This accelerates queries filtering on account_id without altering the existing partitioning by transaction_date. Other options either do not improve performance or introduce negative side effects.

Answer

Partition the table by account_id as well.

Answer

Convert the table to a Parquet table and use predicate pushdown.

Answer

Enable change data feed on the table.

Question 9

A data engineer is building a Silver layer table that combines data from multiple Bronze tables. The engineer wants to ensure that the Silver table only contains the most recent version of each record based on a 'last_updated' timestamp. Which Delta Lake operation should be used to achieve this?

Accepted Answer

MERGE INTO with a condition that updates when the source timestamp is greater. MERGE INTO is the correct operation because it can conditionally update existing rows with newer timestamps and insert new rows, ensuring the Silver table contains only the latest version of each record. INSERT OVERWRITE, DELETE+INSERT, and time travel do not provide the same atomic upsert capability and would either be inefficient or incorrect for this scenario.

Answer

DELETE then INSERT the new records

Answer

Use Delta Lake time travel to revert to a previous version

Answer

INSERT OVERWRITE with the entire dataset

Question 10

A data engineering team is modeling a large Delta Lake fact table that stores clickstream events. Analysts frequently run queries that filter by event_date and then aggregate by user_id, and the table receives continuous appends plus occasional late-arriving corrections. The team wants to reduce bytes scanned and improve join performance. Which two design choices are most appropriate? (Choose two.)

Accepted Answer

Partition the table by event_date and apply Z-ORDER on user_id within each partition. The workload filters on event_date and aggregates by user_id, so layout must accelerate both. Partitioning by event_date with Z-ORDER on user_id gives reliable partition pruning plus co-located user data, while liquid clustering on both columns achieves similar skipping without rigid high-cardinality directories and can be adapted incrementally as access patterns change.

Answer

Convert the table to a Parquet external table and rely on the query engine to infer partitioning

Answer

Enable Change Data Feed on the table so late-arriving corrections are captured automatically

Answer

Partition the table by user_id to maximize file skipping for user-level aggregations

Question 11

A data engineer is designing a Gold layer table for a retail company. The table must support efficient queries that filter on product_category (low cardinality) and sort by transaction_timestamp (high cardinality). The table is expected to grow to petabytes. Which Delta Lake table design should the engineer choose to optimize both filtering and sorting?

Accepted Answer

Partition by product_category and Z-ORDER BY transaction_timestamp.. Partitioning by the low-cardinality product_category enables efficient partition pruning for filters. Z-ORDER BY the high-cardinality transaction_timestamp clusters data within partitions, speeding up sorting and range queries on that column. This combined approach leverages both partitioning and Z-ORDER appropriately, avoiding the pitfalls of partitioning on high-cardinality columns.

Answer

Z-ORDER BY both product_category and transaction_timestamp.

Answer

Partition by product_category only, without Z-ORDER.

Answer

Partition by transaction_timestamp and Z-ORDER BY product_category.

Question 12

An engineer is building a Gold-layer star schema for a sales analytics workload. The business wants to analyze revenue by product, by store, and by promotion independently, and also drill down through a hierarchy of region to country to city. Which dimensional modeling structure best supports these requirements?

Accepted Answer

A star schema with conformed dimensions that include hierarchical attributes for drill-down. Independent analysis by product, store, and promotion with hierarchical drill-down is the classic use case for a star schema built on conformed dimensions. Denormalized dimensions keep joins few, conformed dimensions keep definitions consistent across facts, and embedded hierarchy attributes like region, country, and city enable drill-down without additional tables.

Answer

A fully normalized third-normal-form schema with separate tables for each attribute level

Answer

A snowflake schema that normalizes each hierarchy level into its own dimension table

Answer

A single wide denormalized table that embeds all dimensional attributes directly in the fact rows

Question 13

A financial institution is building a Gold layer table that must support point-in-time queries to reconstruct account balances as of any past date. The source data includes transactions with effective dates and an audit log of changes. Which modeling technique is most appropriate?

Accepted Answer

Implement a Type 2 slowly changing dimension (SCD) with effective start and end dates.. A Type 2 SCD retains full history by adding new rows with effective date ranges, enabling accurate point-in-time queries. This is essential for financial data where past states must be reconstructable. Delta Lake time travel is limited by retention and not designed for continuous history. Type 1 and Type 3 SCDs lack the necessary historical depth.

Answer

Use Delta Lake time travel to query previous versions of the table.

Answer

Use a Type 1 slowly changing dimension (SCD) to overwrite old values.

Answer

Create a Type 3 slowly changing dimension (SCD) with previous value columns.

Question 14

A healthcare company uses a Databricks Lakehouse. The Silver layer contains a table patient_visits that is updated with late-arriving data. The table is partitioned by visit_date. The data engineering team needs to efficiently merge new data that may include updates to existing records and inserts of new records. They want to minimize the impact on existing data and ensure ACID compliance. Which Delta Lake operation should they use?

Accepted Answer

MERGE INTO patient_visits USING new_data ON patient_visits.visit_id = new_data.visit_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *. The MERGE statement is designed for upserts, allowing updates to existing records and inserts of new records in one atomic operation. It minimizes data rewriting by only touching affected files and maintains ACID compliance. Other options either overwrite data, are non-atomic, or do not apply changes.

Answer

Use Delta Lake change data feed to apply changes.

Answer

INSERT OVERWRITE patient_visits SELECT * FROM new_data

Answer

DELETE FROM patient_visits WHERE visit_id IN (SELECT visit_id FROM new_data); INSERT INTO patient_visits SELECT * FROM new_data

Question 15

A financial services firm maintains a Delta Lake table of account transactions that must support both current-state queries and full audit history of every change, including corrections that arrive days later. Regulators require the ability to query the table as it existed at any prior date. Which Delta Lake capability should the engineer rely on to satisfy the audit requirement?

Accepted Answer

Time travel using the transaction log to query historical versions of the table by version number or timestamp. Delta Lake's transaction log records each commit atomically, enabling time travel to any retained version or timestamp. Because late corrections create new versions while prior versions remain queryable, auditors can reconstruct the exact table state at a prior date, which is precisely what the regulatory requirement demands.

Answer

Relying on the underlying cloud object store's versioning to recover previous Parquet files

Answer

Maintaining a separate archive table populated by a nightly full copy of the transactions table

Answer

Enabling Change Data Feed to capture row-level changes and querying the feed instead of the table