Databricks-DA-Assoc Data Modeling with Databricks SQL — Timed 20 Questions

Question 1

Which TWO of the following statements are true regarding the use of Delta Lake constraints in Databricks SQL?

Accepted Answer

CHECK constraints ensure data integrity by preventing invalid records during inserts or updates.. Delta Lake supports CHECK and NOT NULL constraints. CHECK constraints validate data during writes. NOT NULL constraints ensure columns do not contain nulls. However, Delta Lake does not enforce primary keys, and there is no automatic application of NOT NULL constraints to primary keys.

Answer

CHECK constraints are enforced on existing rows when initially defined.

Answer

NOT NULL constraints are automatically applied to all primary key columns.

Answer

Delta Lake constraints allow for automatic row-level data repair during violations.

Question 2

Which object type in Unity Catalog is most appropriate for organizing a set of related tables and views into a logical container that supports cross-schema queries?

Accepted Answer

A Schema. While a Schema is the correct answer for organizing tables and views, the explanation incorrectly states that Schemas are the primary container for organizing related tables across a catalog, while the prompt asks for a container that supports cross-schema queries. Furthermore, the explanation claims 'Catalog' is confused with 'Schema', but the hierarchy is Catalog > Schema > Table. The explanation should clarify that a Schema is the logical container for tables/views, and cross-schema queries are supported by referencing the three-level namespace (catalog.schema.table).

Answer

A Workspace

Answer

A Volume

Answer

A Cluster

Question 3

A retail analytics team maintains a Delta table named sales_transactions in Unity Catalog. They need to add a new column named loyalty_tier of type STRING to the table without affecting existing data or requiring a full rewrite. Which Databricks SQL command should they use?

Accepted Answer

ALTER TABLE sales_transactions ADD COLUMN loyalty_tier STRING. The correct command to add a new column to an existing Delta table in Databricks SQL is ALTER TABLE ... ADD COLUMN. This operation is lightweight and does not rewrite existing data. The other options either use incorrect syntax or attempt to modify a non-existent column. Thus, only the ADD COLUMN variant with singular COLUMN is valid.

Answer

ALTER TABLE sales_transactions MODIFY COLUMN loyalty_tier STRING

Answer

ALTER TABLE sales_transactions ADD loyalty_tier STRING

Answer

ALTER TABLE sales_transactions ADD COLUMNS (loyalty_tier STRING)

Question 4

A data analyst is designing a star schema in Databricks SQL and wants to ensure that dimension tables are easily discoverable and can be joined efficiently with fact tables. Which Unity Catalog feature should be used to organize related tables and provide a logical namespace?

Accepted Answer

Use a single schema to contain all fact and dimension tables for the star schema. In Unity Catalog, schemas are the logical containers for tables and views. Placing all fact and dimension tables of a star schema in a single schema groups them under a common namespace, making them easier to find and manage. This is the recommended approach for organizing related data objects. Other options do not provide the same level of organization or discoverability.

Answer

Create a view that joins all dimension tables to the fact table

Answer

Create a separate schema for each dimension table

Answer

Use table comments to describe the relationships between tables

Question 5

A data analyst is working with a Delta table that contains a column 'timestamp' of type TIMESTAMP. The analyst needs to create a new table that includes an additional column 'date_key' derived from 'timestamp' as an integer in the format YYYYMMDD. The new table should be updated automatically whenever new data is inserted into the original table. Which approach should the analyst use?

Accepted Answer

Create a new Delta table with a generated column defined as GENERATED ALWAYS AS (CAST(DATE_FORMAT(timestamp, 'yyyyMMdd') AS INT)).. Generated columns in Delta Lake automatically compute values based on other columns when data is inserted. By defining date_key as a generated column with the expression CAST(DATE_FORMAT(timestamp, 'yyyyMMdd') AS INT), the new table will always have the correct date_key for each row upon insert. This is a native Databricks SQL feature that satisfies the automatic update requirement without additional orchestration.

Answer

Create a new table and schedule a job to run periodically that inserts new rows with date_key computed using the DATE_FORMAT function.

Answer

Create a view on the original table that computes date_key using the expression CAST(DATE_FORMAT(timestamp, 'yyyyMMdd') AS INT).

Answer

Use a Delta Live Tables pipeline with a streaming live table that reads from the original table and applies the transformation.

Question 6

A data analyst is building a star schema in Databricks SQL. The fact table contains a column 'order_date' of type DATE, and the dimension table 'dim_date' has a surrogate key 'date_key' of type INT. The analyst wants to ensure that queries joining fact and dimension tables can leverage data skipping on the fact table when filtering by date. Which approach should the analyst take?

Accepted Answer

Add a generated column on the fact table that extracts the date key from 'order_date', and cluster the fact table by that generated column.. To enable data skipping on the fact table when joining via a surrogate date key, the analyst should create a generated column that derives the key from the date and cluster the fact table by that generated column. This physically stores the key and allows the optimizer to skip files based on join predicates, improving query performance without altering the star schema design.

Answer

Use Z-ORDER on the 'order_date' column of the fact table.

Answer

Create a view that joins the fact and dimension tables and cluster the view by 'date_key'.

Answer

Partition the fact table by 'order_date' and rely on partition pruning when filtering by date.

Question 7

A data analyst is building a star schema in Databricks SQL. The fact table contains sales transactions with a foreign key to a date dimension. The analyst needs to ensure that queries joining the fact and dimension tables can leverage partition pruning on the fact table. Which design choice should the analyst make?

Accepted Answer

Partition the fact table by the date column used in the join.. Partitioning the fact table by the date column enables partition pruning, which significantly reduces data scanned when queries filter by date. This is a common pattern in star schemas because date is a frequent filter and join key. The date dimension is typically small and does not need partitioning. Z-ORDER or generated columns are not necessary when a direct date column exists.

Answer

Use a generated column on the fact table for the date and partition by that column.

Answer

Create a Z-ORDER on the date column in the fact table.

Answer

Partition the date dimension by the date column.

Question 8

A data analyst is designing a Delta Lake table that will store sensitive customer data. The analyst needs to implement column-level security to restrict access to certain columns based on user groups. Which TWO of the following features can be used to achieve this in Databricks SQL? (Choose two.)

Accepted Answer

Dynamic view functions. Column-level security in Databricks SQL can be implemented using dynamic view functions to create views that conditionally expose columns based on user groups, or using column masks in Unity Catalog to apply masking functions to columns. Both approaches restrict access to sensitive columns without duplicating data. Table ACLs, row filters, and constraints operate at different levels and do not provide column-level security.

Answer

Row filters

Answer

Table ACLs

Answer

Delta table constraints

Question 9

A data analyst needs to optimize query performance for a large sales table that is frequently filtered by 'region_id'. Which physical data modeling strategy should be implemented to minimize data scanning?

Accepted Answer

Apply Z-Ordering on the region_id column.. Z-Ordering is a technique to co-locate related information in the same set of files, significantly reducing the amount of data read during filter operations. By applying Z-Ordering on the 'region_id' column, the Databricks engine can skip irrelevant files more effectively during query execution. This strategy is essential for large-scale datasets where traditional partitioning alone may lead to excessive file fragmentation or suboptimal data distribution across the cluster nodes.

Answer

Implement a primary key constraint on the region_id column.

Answer

Execute the ANALYZE TABLE command periodically without any data clustering.

Answer

Change the file format to CSV to allow easier manual partitioning.

Question 10

Refer to the exhibit. The 'sales_data' table is growing rapidly. You notice queries filtering by 'event_date' are fast, but queries filtering by 'id' are slow. What is the most effective data modeling change to optimize for 'id' lookups?

Accepted Answer

Use Z-Ordering or Liquid Clustering on the 'id' column.. The table is currently partitioned by 'event_date', which creates a folder structure that only aids queries filtering on dates. Partitioning by a high-cardinality column like 'id' is a major anti-pattern as it leads to the 'small file problem'. Instead, the analyst should retain the partitioning on 'event_date' but apply Z-Ordering or Liquid Clustering on the 'id' column to facilitate efficient data skipping without creating excessive directory partitions.

Answer

Re-partition the table by 'id' instead of 'event_date'.

Answer

Create a secondary table for 'id' lookups that is partitioned by 'id'.

Answer

Change the table type to a standard Parquet table without partitioning.

Question 11

When designing a star schema in Databricks SQL, why is it recommended to use Delta Lake for both Fact and Dimension tables?

Accepted Answer

Delta Lake supports ACID transactions, which are necessary for reliable SCD updates.. Delta Lake provides ACID compliance and time travel, which are critical for maintaining the integrity of star schemas. Analytical workloads often rely on SCD (Slowly Changing Dimension) updates and complex joins. By using Delta Lake, you ensure that dimension updates are consistent and atomic, preventing users from seeing partial updates or corrupted data states, which is essential for accurate business intelligence reporting and historical analysis.

Answer

It forces the use of Star Schema optimization, which is only supported for Delta format.

Answer

Delta Lake automatically reorders dimension tables to improve join performance.

Answer

It is required to store dimensions in the same schema as fact tables for performance.

Question 12

Which THREE of the following are benefits of using Liquid Clustering instead of traditional partitioning in Databricks SQL?

Accepted Answer

It simplifies data layout management by removing the need for manual partitioning.. Liquid Clustering provides a flexible alternative to manual partitioning by automatically managing data layout based on query patterns. It eliminates the need for manual 'partition evolution' and solves issues related to skewed data distribution or small file problems. By offloading cluster management to the Databricks engine, analysts can spend more time on business logic and less on the intricacies of physical data file management for large-scale datasets.

Answer

It provides faster write throughput by disabling transaction logs during updates.

Answer

It forces data to be sorted by every column in the table automatically.

Question 13

You are modeling a table where users need to query based on a 'user_id' but also need to perform historical point-in-time analysis. Which feature is most appropriate?

Accepted Answer

Use Delta Time Travel.. Time Travel is a native feature of Delta Lake that allows users to query previous versions of a table using a timestamp or version number. This is crucial for point-in-time analysis, as it lets analysts reproduce reports or audit changes without needing to maintain manual snapshots or separate history tables. It leverages the transaction log to provide a consistent, historical view of the data efficiently.

Answer

Implement a materialized view with a static snapshot.

Answer

Create a new table for every daily load.

Answer

Store all historical records in a single table with an 'is_current' flag.

Question 14

An organization requires that certain sensitive columns be removed from a table for specific groups of users. Which Databricks feature should be used to enforce this at the data modeling level?

Accepted Answer

Use Delta Lake dynamic masking.. Dynamic Data Masking and Row-Level Security are the preferred methods for controlling access to sensitive data within Databricks SQL. By using SQL functions to define masking policies, administrators can ensure that users only see the data they are authorized to view. This approach keeps the underlying data intact while providing secure, role-based access control, ensuring compliance with data privacy regulations without duplicating data for different security profiles.

Answer

Create separate physical tables for each security level.

Answer

Change the file format to JSON to strip sensitive fields.

Answer

Hard-code the filtering logic in every user's SQL query.

Question 15

Refer to the exhibit. You are attempting to enable Change Data Feed (CDF) on an existing Delta table but receive this error. Why is this error occurring?

Accepted Answer

The table requires column mapping to track physical schema evolution for CDF.. Change Data Feed (CDF) tracks row-level changes, which requires a stable mapping between column names and their underlying physical file data. If the table was created without column mapping, it cannot safely track changes if column names are altered. Setting 'delta.columnMapping.mode' to 'name' decouples the column names from their physical file identifiers, which is a prerequisite for the metadata tracking required by the CDF feature.

Answer

The table is not in the Delta format.

Answer

The table has too many rows to enable CDF.

Answer

CDF is only supported on tables with primary keys.

Question 16

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 17

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 18

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 19

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 20

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.