Courseiva

Databricks-DA-Assoc · domain

Data Modeling with Databricks SQL

This domain covers designing and governing tables, views, and data layout in Databricks SQL on Delta Lake and Unity Catalog. Questions test Change Data Feed, view creation and permissions, Liquid Clustering, and partitioning trade-offs, usually as scenario-based multiple-choice items where you pick the correct SQL statement or diagnose a configuration error.

23 questions4 easy14 medium5 hard

Focused practice

Practice Data Modeling with Databricks SQL questions

Scored sessions drawing only from this domain — pick a length below.

What this domain covers

What to know about Data Modeling with Databricks SQL

Write correct Databricks SQL DDL for tables and views, enable and reason about Change Data Feed, and choose between Liquid Clustering and partitioning. The key is knowing which layout or feature matches the stated performance, lifecycle, and access requirement.

Enabling Change Data Feed on Delta tables and interpreting the resulting error conditions

Creating Unity Catalog views with CREATE VIEW that join tables and apply filters

Using Liquid Clustering for data lifecycle and query performance management

Comparing Liquid Clustering benefits against traditional Hive-style partitioning

Watch out for

Common Data Modeling with Databricks SQL exam traps

  • ▸Assuming CDF can be enabled on any existing table without checking table properties or supported operations first.
  • ▸Forgetting that a view's accessibility depends on Unity Catalog schema grants and the definer's privileges, not just the SELECT.
  • ▸Treating Liquid Clustering as a drop-in replacement for partitioning without accounting for when each layout actually helps.

Question index

All Data Modeling with Databricks SQL questions (23)

Click any question to see the full explanation, or start a practice session above.

1

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?

Medium
2

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?

Medium
3

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?

Hard
4

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?

Hard
5

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

Medium
6

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?

Hard
7

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?

Medium
8

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?

Easy
9

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?

Hard
10

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?

Easy
11

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

Medium
12

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?

Medium
13

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?

Medium
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?

Medium
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?

Medium
16

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

Medium
17

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?

Easy
18

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?

Easy
19

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

Medium
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?

Hard
21

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?

Medium
22

A data analyst is modeling a Delta table in Databricks SQL that stores product inventory snapshots. The table has columns snapshot_date (DATE), product_id (STRING), warehouse_id (STRING), and quantity_on_hand (INT). Queries frequently filter on snapshot_date and then join to a product dimension. The analyst wants to minimize the amount of data scanned for date-filtered queries while keeping the table simple and avoiding manual file management. Which approach best achieves this in Databricks SQL?

Medium
23

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?

Medium

Frequently asked questions

What does the Data Modeling with Databricks SQL domain cover on the Databricks-DA-Assoc exam?
Write correct Databricks SQL DDL for tables and views, enable and reason about Change Data Feed, and choose between Liquid Clustering and partitioning. The key is knowing which layout or feature matches the stated performance, lifecycle, and access requirement.
How many questions are in this domain?
This page lists all 23 Data Modeling with Databricks SQL questions in the Databricks-DA-Assoc question bank. The actual exam draws from this domain proportionally to its weighting in the official exam blueprint.
What is the best way to practise this domain?
Start with a short focused session (10 questions) to identify gaps, then work through explanations. Repeat with a longer session once the weak areas feel solid.
Can I practise only Data Modeling with Databricks SQL questions?
Yes — the session launcher on this page filters questions to this domain only. Choose any session length for inline explanations and scoring.
Databricks Certified Data Analyst Associate Databricks-DA-Assoc Data Modeling with Databricks SQL Practice Questions