Databricks-DA-Assoc Data Modeling with Databricks SQL — 10 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.