Databricks-DA-Assoc Data Modeling with Databricks SQL Practice Test 2 — 15 Questions

Question 1

What is the primary function of the 'VACUUM' command in Databricks SQL data modeling?

Accepted Answer

To permanently remove files no longer referenced by the table state.. The VACUUM command is essential for storage management and compliance. It removes old, unreferenced data files that are no longer part of the current table state beyond a specified retention period. This is vital for managing cloud storage costs and ensuring that data is physically purged, which helps companies meet regulatory requirements like GDPR by permanently deleting sensitive data that is no longer needed in the historical logs.

Answer

To shrink the transaction log file size.

Answer

To re-index the table for faster performance.

Answer

To force a merge of all Delta logs into a single Parquet file.

Question 2

When designing a table to support frequent 'MERGE' operations, which data modeling practice will lead to the best performance?

Accepted Answer

Use Z-Ordering on the join keys.. MERGE operations are expensive because they involve reading, joining, and rewriting data. To optimize this, the target table should be well-organized using partitioning and Z-Ordering or Liquid Clustering. This allows the MERGE operation to perform 'data skipping', only loading the relevant files into memory during the join and update process. Without these optimizations, the engine must perform a full table scan for every single merge statement, leading to significant latency.

Answer

Store the data in a CSV file format.

Answer

Disable the Delta transaction log.

Answer

Ensure the target table has no indexes or clustering.

Question 3

A data analyst is designing a star schema in Databricks SQL to optimize query performance for a large sales dataset. Which strategy most effectively minimizes data shuffling during join operations between a large fact table and a small dimension table?

Accepted Answer

Leverage the BROADCAST join hint on the dimension table.. Utilizing the broadcast join strategy is essential when joining a massive fact table with a significantly smaller dimension table. By distributing the small table to all worker nodes, Databricks eliminates the need for expensive network shuffles of the fact table rows. This approach is fundamental for maintaining low latency in BI dashboards where users require sub-second query responses on complex star schema structures.

Answer

Apply the CLUSTER BY clause on the primary key of the fact table.

Answer

Use the Z-ORDER BY clause on the dimension table's primary key.

Answer

Convert the fact table into a temporary view before joining.

Question 4

An analyst needs to manage data lifecycle and performance in Databricks SQL. Which TWO of the following tasks are best achieved using the Liquid Clustering feature?

Accepted Answer

Optimize data skipping for frequently filtered columns.. Liquid Clustering is a flexible way to manage data layout in Delta tables, replacing static partitioning and Z-Ordering. It automatically adapts to data distribution changes over time without manual intervention. Choosing the correct clustering columns ensures that queries filter data efficiently while minimizing the storage overhead associated with maintaining high-cardinality partitions, which often lead to small-file problems in large datasets.

Answer

Enforce strict schema validation during ingestion.

Answer

Implement row-level security policies.

Answer

Manage concurrent write conflicts in Delta tables.

Question 5

Refer to the exhibit. An analyst is troubleshooting a performance issue where frequent small inserts into a Delta table result in degraded query performance over time. The exhibit shows the configuration applied. What is the expected behavior of these properties?

Accepted Answer

It improves read performance by reducing the number of small files created during ingestion.. These properties enable Delta Lake's auto-optimization features. 'optimizeWrite' dynamically adjusts the write size to produce larger, more efficient files, while 'autoCompact' automatically merges small files into larger ones during background processes. This combination is critical for sustaining query performance in streaming or high-frequency update environments, preventing the 'small-file problem' that leads to excessive metadata overhead and slow scan times in Databricks SQL.

Answer

It forces all writes to be synchronous, ensuring immediate data consistency across all cluster nodes.

Answer

It automatically triggers a FULL OPTIMIZE command every hour regardless of file counts.

Answer

It forces the table to use a liquid clustering layout for all future data partitions.

Question 6

An analyst is building a dimensional model in Databricks SQL and needs to create a table that stores slowly changing dimension type 2 (SCD2) history for customers. The table must track valid_from and valid_to timestamps and a current flag. Which table type in Databricks SQL is best suited for this purpose?

Accepted Answer

A Delta table with appropriate columns and MERGE operations to maintain history. Delta tables with SCD2 columns and MERGE operations are the standard way to implement slowly changing dimensions type 2 in Databricks SQL. Delta Lake's ACID transactions and MERGE support allow you to update existing rows (expire old records) and insert new versions atomically. Other options like change data feed or temporary views do not provide the necessary persistent history structure.

Answer

A Delta table with change data feed enabled

Answer

A temporary view that joins the current customer table with a history table

Answer

A managed table with partitioning by customer_id

Question 7

A data analyst is designing a dimension table in Databricks SQL that will be used in a star schema. The table contains a natural business key (e.g., product_code) and a surrogate key (e.g., product_sk). The analyst wants to ensure that the surrogate key is unique and automatically generated for each new row, while also enforcing that the natural key is unique. Which approach best achieves these requirements?

Accepted Answer

Define product_sk as GENERATED ALWAYS AS IDENTITY and add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.. The requirement is for an automatically generated, unique surrogate key and a unique natural key. The IDENTITY column property in Delta Lake automatically generates unique, sequential values for new rows. Combining it with a PRIMARY KEY constraint on the surrogate key and a UNIQUE constraint on the natural key enforces both uniqueness rules. Other options either misuse functions, lack persistence, or do not enforce uniqueness correctly.

Answer

Define product_sk as BIGINT and set a default value using the UUID() function, then add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.

Answer

Use a Delta table with a CHECK constraint that validates product_code IS NOT NULL, and generate product_sk using the ROW_NUMBER() window function in a view.

Answer

Define product_sk as STRING and use the MONOTONICALLY_INCREASING_ID() function during inserts to populate it, then add a PRIMARY KEY constraint on product_sk.

Question 8

A data analyst is creating a view in Databricks SQL that joins a fact table with several dimension tables. The analyst wants to ensure that the view always returns the latest data and that any changes to the underlying tables are immediately reflected. Which type of view should the analyst create?

Accepted Answer

A standard view. A standard view is a saved query that executes on demand, so it always reflects the current state of the underlying tables. Materialized views store data and require refreshes, temporary views are session-bound, and streaming views are for continuous processing. Thus, a standard view best meets the need for immediate data visibility and persistence.

Answer

A temporary view

Answer

A streaming view

Answer

A materialized view

Question 9

A data analyst is designing a Delta table in Databricks SQL to store clickstream events. The table will be queried primarily by filtering on event_date and then by user_id. The analyst wants to optimize data skipping for both columns without over-partitioning. Which approach should the analyst use?

Accepted Answer

Use Liquid Clustering on event_date and user_id.. Liquid Clustering on event_date and user_id provides automatic, incremental clustering that optimizes data skipping for filters on either column without the drawbacks of over-partitioning. It is designed for multi-column clustering and adapts as data changes, making it ideal for clickstream analysis where queries filter by date and user. Partitioning or Z-ORDER alone would not achieve the same balanced performance.

Answer

Partition the table by event_date and use Z-ORDER on user_id.

Answer

Create a materialized view that pre-aggregates clicks by event_date and user_id.

Answer

Partition the table by user_id and use Z-ORDER on event_date.

Question 10

A data analyst is designing a table to store customer orders. The table will be frequently queried by order_date and customer_id. The analyst wants to optimize query performance for these filters. Which physical data modeling technique should the analyst use?

Accepted Answer

Use Liquid Clustering on order_date and customer_id.. Liquid Clustering is designed to optimize query performance on multiple columns by clustering data dynamically. It is a table-level property that can be applied to new or existing tables and supports incremental clustering as data is added. Unlike partitioning, it handles high-cardinality columns well and does not require manual maintenance, making it ideal for optimizing filters on both order_date and customer_id.

Answer

Partition the table by order_date.

Answer

Use Z-ORDER BY on order_date and customer_id.

Answer

Create a materialized view that aggregates orders by order_date and customer_id.

Question 11

An analyst is designing a Delta table in Databricks SQL that will store customer transactions. The table must enforce that the 'transaction_amount' is always positive and that 'customer_id' is not null. The analyst wants to ensure that any future inserts or updates that violate these rules are rejected. Which approach should the analyst use?

Accepted Answer

Define a CHECK constraint on the table for 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id'.. Delta Lake's CHECK and NOT NULL constraints are enforced at write time, ensuring that any data violating the rules is rejected. A CHECK constraint on 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id' will prevent invalid inserts or updates, maintaining data integrity directly in the table definition.

Answer

Use a MERGE statement with a condition that only updates rows where 'transaction_amount' > 0 and 'customer_id' IS NOT NULL.

Answer

Add a column mask that returns NULL for 'transaction_amount' when it is not positive.

Answer

Create a view on top of the table that filters out rows where 'transaction_amount' is not positive or 'customer_id' is null.

Question 12

A data analyst is working with a Delta table that contains a column 'status' with values 'active', 'inactive', and 'pending'. The analyst wants to enforce that only these three values can be inserted or updated. Which Databricks SQL feature should the analyst use?

Accepted Answer

CHECK constraint on the status column.. A CHECK constraint is the appropriate feature to enforce that the status column only contains a specific set of values. It is evaluated on insert and update, rejecting rows that violate the condition. This ensures data integrity directly in the table definition. Other options like NOT NULL, generated columns, or column masks do not restrict the domain of values for a column.

Answer

Column mask that filters out invalid statuses.

Answer

NOT NULL constraint on the status column.

Answer

Generated column that maps status to an integer.

Question 13

A data analyst is creating a table in Databricks SQL to store product information. The analyst wants to ensure that the table is automatically optimized for query performance as data is added, without manual intervention. Which table type should the analyst use?

Accepted Answer

A Delta table with liquid clustering enabled on high-cardinality columns.. Liquid clustering is a feature in Delta Lake that automatically optimizes data layout as new data is written, without manual intervention. It is ideal for tables where query patterns may change or where continuous optimization is desired. By enabling liquid clustering, the analyst ensures that the table remains performant without needing to manually partition or schedule Z-ORDER operations.

Answer

A Delta table with Z-ORDER applied on a schedule.

Answer

A Delta table partitioned by a low-cardinality column.

Answer

A Parquet table with manually defined partitions.

Question 14

A data analyst needs to create a view in Databricks SQL that combines data from two tables and applies a filter. The view should be accessible to other users in the same Unity Catalog schema. Which SQL statement should the analyst use?

Accepted Answer

CREATE VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;. The CREATE VIEW statement creates a persistent, virtual view that other users can access if they have permissions. It dynamically reflects changes in the underlying tables and does not store data. A table would duplicate data and not stay current. A materialized view adds unnecessary complexity, and a temporary view is not shared across users.

Answer

CREATE MATERIALIZED VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;

Answer

CREATE TEMPORARY VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;

Answer

CREATE TABLE my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;

Question 15

A data analyst is working with a Delta table that contains a column 'sensitive_info' which should be redacted for users in the 'marketing' group. The analyst wants to ensure that users in that group see a masked value while other users see the actual data. Which Databricks feature should the analyst use?

Accepted Answer

Column masks. Column masks in Unity Catalog are designed to dynamically redact column values based on the user's identity or group membership. They allow the analyst to define a masking expression that applies only to specified users or groups, leaving the original data intact for others. This provides fine-grained security without duplicating data or creating multiple views.

Answer

Row filters

Answer

Dynamic views

Answer

Table ACLs