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.
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.
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?
Medium2A 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?
Medium3Refer 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?
Hard4Refer 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?
Hard5What is the primary function of the 'VACUUM' command in Databricks SQL data modeling?
Medium6A 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?
Hard7A 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?
Medium8An 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?
Easy9An 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?
Hard10A 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?
Easy11When designing a table to support frequent 'MERGE' operations, which data modeling practice will lead to the best performance?
Medium12A 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?
Medium13A 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?
Medium14An 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?
Medium15A 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?
Medium16Which THREE of the following are benefits of using Liquid Clustering instead of traditional partitioning in Databricks SQL?
Medium17A 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?
Easy18A 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?
Easy19When designing a star schema in Databricks SQL, why is it recommended to use Delta Lake for both Fact and Dimension tables?
Medium20Refer 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?
Hard21You 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?
Medium22A 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?
Medium23An 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?
MediumOther domains
All Databricks-DA-Assoc exam domains
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.