Courseiva

CCNA Data Modeling with Databricks SQL Questions

23 questions · Data Modeling with Databricks SQL · All types, answers revealed

Practice these questionsDomain overviewAll questions
1
MCQmedium

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?

A.Apply the CLUSTER BY clause on the primary key of the fact table.
B.Use the Z-ORDER BY clause on the dimension table's primary key.
C.Leverage the BROADCAST join hint on the dimension table.
D.Convert the fact table into a temporary view before joining.
AnswerC

The broadcast hint forces the optimizer to send a copy of the smaller dimension table to every executor node. This prevents the large fact table from being repartitioned or shuffled across the network, significantly reducing join execution time. This is the optimal configuration for star schemas in Databricks SQL environments.

Why this answer

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.

Exam trap

Candidates often suggest partitioning or Z-Ordering for every scenario. They miss that the broadcast join is specifically intended to eliminate shuffling by moving small tables instead of large ones.

Practice this question →
2
MCQmedium

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?

A.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.
B.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.
C.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.
D.Define product_sk as GENERATED ALWAYS AS IDENTITY and add a PRIMARY KEY constraint on product_sk and a UNIQUE constraint on product_code.
AnswerD

Using GENERATED ALWAYS AS IDENTITY automatically generates unique surrogate keys. Adding a PRIMARY KEY on product_sk enforces uniqueness and non-nullability, while a UNIQUE constraint on product_code enforces the natural key's uniqueness. This combination meets both requirements and is supported in Delta Lake tables.

Why this answer

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.

Exam trap

The trap here is assuming that any function that generates numbers, such as MONOTONICALLY_INCREASING_ID(), guarantees uniqueness and persistence in a table, when in fact it does not.

Practice this question →
3
MCQhard

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?

A.Re-partition the table by 'id' instead of 'event_date'.
B.Use Z-Ordering or Liquid Clustering on the 'id' column.
C.Create a secondary table for 'id' lookups that is partitioned by 'id'.
D.Change the table type to a standard Parquet table without partitioning.
AnswerB

Z-Ordering or Liquid Clustering allows the engine to organize data within the existing partitions to facilitate efficient skipping on the 'id' column. This keeps the physical folder structure manageable based on 'event_date' while providing high-performance access paths for queries filtering by specific 'id' values without creating excess files.

Why this answer

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.

Exam trap

Candidates often suggest partitioning by high-cardinality columns like 'id', which leads to the 'small file problem' and degrades performance rather than improving it.

Practice this question →
4
MCQhard

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?

A.The table is not in the Delta format.
B.The table requires column mapping to track physical schema evolution for CDF.
C.The table has too many rows to enable CDF.
D.CDF is only supported on tables with primary keys.
AnswerB

CDF tracks changes to rows over time. If a table allows schema evolution (renaming columns), there must be a way to map the old column name to the new column name in the files. Column mapping enables this decoupling, which is essential for CDF to function correctly during schema changes.

Why this answer

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.

Exam trap

Candidates often assume that CDF can be enabled on any existing table without configuration changes. They miss that column mapping is a mandatory architectural prerequisite for tracking schema evolution in CDF.

Practice this question →
5
MCQmedium

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

A.To shrink the transaction log file size.
B.To permanently remove files no longer referenced by the table state.
C.To re-index the table for faster performance.
D.To force a merge of all Delta logs into a single Parquet file.
AnswerB

VACUUM identifies files that are no longer part of the current version of the Delta table (or within the retention threshold) and deletes them from cloud storage. This is the primary mechanism for reclaiming storage space and ensuring the storage directory only contains necessary, active data files for the table.

Why this answer

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.

Exam trap

Candidates often mistakenly believe VACUUM is required to improve query performance. While it deletes files, its primary purpose is storage management and compliance, not immediate query speed optimization.

Practice this question →
6
MCQhard

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?

A.CHECK constraint on the status column.
B.Column mask that filters out invalid statuses.
C.NOT NULL constraint on the status column.
D.Generated column that maps status to an integer.
AnswerA

A CHECK constraint allows defining a Boolean expression that must be true for all rows. By specifying status IN ('active','inactive','pending'), the analyst enforces the allowed values. Delta Lake supports CHECK constraints, and they are enforced on write operations, ensuring data integrity. This is the correct way to restrict column values to a specific set.

Why this answer

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.

Exam trap

The trap here is confusing access control features like column masks with data validation constraints.

Practice this question →
7
MCQmedium

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?

A.Partition the table by order_date.
B.Use Z-ORDER BY on order_date and customer_id.
C.Use Liquid Clustering on order_date and customer_id.
D.Create a materialized view that aggregates orders by order_date and customer_id.
AnswerC

Liquid Clustering is a physical data modeling technique that automatically clusters data based on specified columns. It supports multiple columns and incrementally maintains clustering as data changes, optimizing queries that filter on those columns. It is defined at table creation and is more flexible than partitioning.

Why this answer

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.

Exam trap

The trap here is thinking that partitioning or Z-ORDER BY alone can optimize multiple columns, when Liquid Clustering is specifically designed for multi-column clustering without the drawbacks of partitioning.

Practice this question →
8
MCQeasy

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?

A.A Delta table with change data feed enabled
B.A temporary view that joins the current customer table with a history table
C.A managed table with partitioning by customer_id
D.A Delta table with appropriate columns and MERGE operations to maintain history
AnswerD

To implement SCD2, you need a table that includes columns like valid_from, valid_to, and is_current, and you use MERGE operations to insert new versions and expire old ones. Delta Lake supports ACID transactions and MERGE, making it ideal for maintaining SCD2 history. This approach gives full control over the history tracking logic.

Why this answer

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.

Exam trap

The trap here is thinking that Change Data Feed automatically provides SCD2 history; it only records changes and does not maintain valid_from/valid_to or current flags.

Practice this question →
9
MCQhard

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?

A.Use a MERGE statement with a condition that only updates rows where 'transaction_amount' > 0 and 'customer_id' IS NOT NULL.
B.Add a column mask that returns NULL for 'transaction_amount' when it is not positive.
C.Define a CHECK constraint on the table for 'transaction_amount > 0' and a NOT NULL constraint on 'customer_id'.
D.Create a view on top of the table that filters out rows where 'transaction_amount' is not positive or 'customer_id' is null.
AnswerC

Delta Lake supports CHECK constraints and NOT NULL constraints. CHECK constraints enforce a boolean expression on new data, and NOT NULL ensures a column cannot contain nulls. These constraints are enforced on all writes, including inserts, updates, and merges, and will reject violating rows. This directly meets the requirement to reject invalid data at write time.

Why this answer

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.

Exam trap

The trap here is confusing data validation constraints with data filtering or masking mechanisms that only affect query results.

Practice this question →
10
MCQeasy

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?

A.CREATE MATERIALIZED VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
B.CREATE TEMPORARY VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
C.CREATE TABLE my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
D.CREATE VIEW my_view AS SELECT ... FROM table1 JOIN table2 WHERE ...;
AnswerD

The CREATE VIEW statement creates a virtual view that can be queried like a table. It stores the query definition, not the data. This is the standard way to create a view in Databricks SQL, and it will be accessible to users with appropriate permissions on the schema. It meets the requirement of combining tables and applying a filter without duplicating data.

Why this answer

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.

Exam trap

The trap here is confusing a standard view with a materialized view or a table, which have different persistence and refresh characteristics.

Practice this question →
11
MCQmedium

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

A.Store the data in a CSV file format.
B.Use Z-Ordering on the join keys.
C.Disable the Delta transaction log.
D.Ensure the target table has no indexes or clustering.
AnswerB

When MERGE operations are performed, the engine joins the source and target tables. If the join keys in the target table are Z-Ordered, the engine can efficiently find and update only the relevant files. This significantly reduces the volume of data processed, leading to much faster performance for large-scale operations.

Why this answer

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.

Exam trap

Candidates often suggest partitioning by high-cardinality columns for performance. This is a major anti-pattern that leads to the small file problem and degrades MERGE performance significantly.

Practice this question →
12
MCQmedium

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?

A.Implement a primary key constraint on the region_id column.
B.Execute the ANALYZE TABLE command periodically without any data clustering.
C.Apply Z-Ordering on the region_id column.
D.Change the file format to CSV to allow easier manual partitioning.
AnswerC

Z-Ordering maps multi-dimensional data to one dimension while preserving locality. By clustering data with similar 'region_id' values into the same files, the engine can utilize min-max statistics to skip entire files that do not contain the requested region, drastically reducing I/O and improving overall query latency.

Why this answer

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.

Exam trap

Candidates often confuse partitioning strategies with Z-Ordering, recommending partitioning for columns with high cardinality like IDs instead of applying Z-Ordering.

Practice this question →
13
MCQmedium

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?

A.Partition the table by event_date and use Z-ORDER on user_id.
B.Use Liquid Clustering on event_date and user_id.
C.Create a materialized view that pre-aggregates clicks by event_date and user_id.
D.Partition the table by user_id and use Z-ORDER on event_date.
AnswerB

Liquid Clustering allows incremental clustering on multiple columns without rewriting existing data, and it automatically optimizes data layout for both event_date and user_id. It avoids over-partitioning and supports efficient data skipping for filters on either column. This is the recommended approach for multi-dimensional clustering in Delta Lake, especially when query patterns involve multiple columns and data volume is large.

Why this answer

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.

Exam trap

The trap here is assuming that partitioning by a high-cardinality column like user_id is beneficial, when it actually causes small file and metadata issues.

Practice this question →
14
MCQmedium

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?

A.Create separate physical tables for each security level.
B.Use Delta Lake dynamic masking.
C.Change the file format to JSON to strip sensitive fields.
D.Hard-code the filtering logic in every user's SQL query.
AnswerB

Dynamic masking allows for the definition of policies that transform or redact column content based on the user's role at query time. This ensures security is enforced consistently across all applications and users, without altering the underlying data on disk, which is vital for maintaining data governance and compliance standards.

Why this answer

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.

Exam trap

Candidates often get confused between 'Data Masking' and 'Row Filters'. They might select row-level security when the requirement specifically asks for column-level removal or obscuring of sensitive data attributes.

Practice this question →
15
MCQmedium

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?

A.Row filters
B.Column masks
C.Dynamic views
D.Table ACLs
AnswerB

Column masks in Unity Catalog allow dynamic masking of column values based on the user's group membership. The analyst can define a mask function that returns a redacted value for the 'marketing' group and the original value for others. This feature is designed exactly for this scenario, providing row-level and column-level security without altering the underlying data.

Why this answer

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.

Exam trap

The trap here is confusing row-level security with column-level masking, or assuming that table ACLs can provide granular column masking.

Practice this question →
16
Multi-Selectmedium

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

Select 3 answers
A.It simplifies data layout management by removing the need for manual partitioning.
B.It prevents the creation of small files caused by high-cardinality partitions.
C.It provides faster write throughput by disabling transaction logs during updates.
D.It allows for easier clustering key updates without needing to rewrite the entire table.
E.It forces data to be sorted by every column in the table automatically.
AnswersA, B, D

Liquid Clustering eliminates the rigid structure of traditional partitioning. It allows users to define clustering keys, and the system automatically reorganizes data to optimize for query performance over time. This removes the administrative overhead of re-partitioning tables when business requirements or filter patterns shift significantly over the project lifecycle.

Why this answer

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.

Exam trap

Candidates frequently assume Liquid Clustering is a complete replacement for all partitioning. They often fail to recognize that Liquid Clustering is specifically designed to replace manual partitioning, not all storage optimization techniques.

Practice this question →
17
MCQeasy

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?

A.A Delta table with Z-ORDER applied on a schedule.
B.A Delta table with liquid clustering enabled on high-cardinality columns.
C.A Delta table partitioned by a low-cardinality column.
D.A Parquet table with manually defined partitions.
AnswerB

Liquid clustering automatically optimizes data layout as new data is added, without requiring manual partitioning or Z-ORDER. It is designed for tables that need continuous optimization. By enabling liquid clustering on columns frequently used in filters, the table maintains good query performance with minimal maintenance, directly meeting the requirement for automatic optimization.

Why this answer

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.

Exam trap

The trap here is assuming that partitioning or scheduled Z-ORDER provides automatic optimization, when they actually require manual setup and maintenance.

Practice this question →
18
MCQeasy

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?

A.A temporary view
B.A streaming view
C.A standard view
D.A materialized view
AnswerC

A standard view in Databricks SQL is a saved query that runs against the underlying tables each time it is queried. It always returns the current data because it does not store results. Therefore, any changes to the base tables are immediately visible, satisfying the requirement for up-to-date data.

Why this answer

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.

Exam trap

The trap here is confusing materialized views with standard views, thinking that a materialized view automatically updates, when it actually requires a refresh to reflect changes.

Practice this question →
19
MCQmedium

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

A.It forces the use of Star Schema optimization, which is only supported for Delta format.
B.Delta Lake supports ACID transactions, which are necessary for reliable SCD updates.
C.Delta Lake automatically reorders dimension tables to improve join performance.
D.It is required to store dimensions in the same schema as fact tables for performance.
AnswerB

SCD (Slowly Changing Dimension) patterns require atomic updates to history and status flags. Delta Lake ensures these operations are ACID compliant, meaning they are either fully committed or not committed at all. This prevents partial writes that could invalidate downstream analytical queries or lead to erroneous historical reporting.

Why this answer

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.

Exam trap

Candidates often choose standard Parquet tables assuming storage format has no impact on data warehouse updates, ignoring the necessity of transaction logs for dimensions.

Practice this question →
20
MCQhard

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?

A.It forces all writes to be synchronous, ensuring immediate data consistency across all cluster nodes.
B.It automatically triggers a FULL OPTIMIZE command every hour regardless of file counts.
C.It improves read performance by reducing the number of small files created during ingestion.
D.It forces the table to use a liquid clustering layout for all future data partitions.
AnswerC

By enabling these properties, the table writer attempts to optimize file sizes during the write phase, and the background process cleans up lingering small files. This significantly improves read performance because the query engine needs to scan fewer files, which reduces I/O overhead and speeds up overall data retrieval.

Why this answer

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.

Exam trap

Candidates often fear that 'Auto-Optimize' will negatively impact write latency. They fail to recognize that the primary goal is to prevent metadata degradation and improve long-term read performance.

Practice this question →
21
MCQmedium

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?

A.Implement a materialized view with a static snapshot.
B.Use Delta Time Travel.
C.Create a new table for every daily load.
D.Store all historical records in a single table with an 'is_current' flag.
AnswerB

Time Travel in Delta Lake allows querying data as of a specific version or timestamp using the 'VERSION AS OF' or 'TIMESTAMP AS OF' clauses. This provides seamless access to historical states without requiring extra storage for snapshots or complex architectural workarounds for point-in-time analytical requirements.

Why this answer

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.

Exam trap

Candidates often confuse Time Travel with Change Data Feed (CDF). While both relate to history, they are used for different purposes: Time Travel for state snapshots and CDF for granular row-level changes.

Practice this question →
22
MCQmedium

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?

A.Convert the table to a view that filters snapshot_date to the last 30 days.
B.Use Z-ORDER BY on product_id and warehouse_id when writing the table.
C.Create a Bloom filter index on product_id to speed up date-range queries.
D.Partition the Delta table by snapshot_date using PARTITIONED BY (snapshot_date) in the CREATE TABLE statement.
AnswerD

Partitioning by snapshot_date lets Databricks SQL prune files for date-filtered queries, reducing data scanned. Because Delta Lake manages partition metadata automatically, the analyst avoids manual file management. This directly matches the scenario's need to minimize scanned data for date predicates while keeping the table simple, and it works with subsequent joins to a product dimension.

Why this answer

Partitioning by snapshot_date aligns the physical layout with the dominant filter, enabling file pruning and lower data scanned. Delta Lake manages partitions automatically, so the analyst avoids manual file management. The other choices either target the wrong column, require ongoing maintenance, or change query results, so they do not satisfy the scenario's goals.

Exam trap

The trap here is assuming that any performance feature, such as a Bloom filter or Z-ORDER, will accelerate all filters, when in fact each optimizes specific access patterns and must match the columns used in the WHERE clause.

Practice this question →
23
Multi-Selectmedium

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?

Select 2 answers
A.Enforce strict schema validation during ingestion.
B.Optimize data skipping for frequently filtered columns.
C.Automatically resolve high-cardinality partition issues.
D.Implement row-level security policies.
E.Manage concurrent write conflicts in Delta tables.
AnswersB, C

Liquid clustering dynamically organizes data based on the columns specified in the CLUSTER BY clause. This creates metadata that allows the engine to skip unnecessary files during query execution. By focusing on frequently filtered columns, analysts can dramatically improve performance without the management burden of traditional static partitioning.

Why this answer

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.

Exam trap

Candidates often confuse Liquid Clustering with traditional partitioning or Z-Ordering. They struggle to identify that it specifically solves the 'small file' and 'high cardinality' issues that plague static partitioning.

Practice this question →

Ready to test yourself?

Try a timed practice session using only Data Modeling with Databricks SQL questions.

Start 20-question session