Data Modeling with Databricks SQL practice questions

CHECK constraints are enforced on existing rows when initially defined.

Why it fails: Adding a CHECK constraint to an existing Delta table does not retroactively validate historical data. The constraint only applies to new writes or updates performed after the constraint is added. Consequently, existing invalid records in the table remain, which can still cause issues for queries expecting clean, compliant data.

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

Why it fails: In Databricks SQL, columns defined as primary keys are implicitly treated as NOT NULL. This architectural decision ensures that identifying records is always possible and avoids ambiguity during join operations or lookups, preventing null-related errors in relational logic. This consistency is fundamental for reliable data modeling in lakehouses.

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

Why it fails: Delta Lake constraints are strictly validation mechanisms that block write operations upon failure. They do not possess auto-repair capabilities, such as filtering out bad rows or applying default values. Developers must handle data cleansing processes in the ETL pipeline before the data reaches the constraint-protected Delta table.

A Workspace

Why it fails: Workspaces are administrative boundaries for compute and collaboration, not data organization containers. While you can access data across workspaces if they share a Unity Catalog, workspaces themselves do not exist within the hierarchy of schema-based data organization that is used to manage tables and views for query access.

A Volume

Why it fails: Volumes are specific objects in Unity Catalog designed to manage non-tabular data, such as unstructured files, images, or model artifacts. They are not intended for organizing structured tables or views. Attempting to use volumes for tabular data organization would prevent users from executing SQL queries against the underlying contents.

A Cluster

Why it fails: A cluster is a compute resource that provides the processing power required to execute SQL queries. It is not a container for data objects and does not exist within the Unity Catalog hierarchy. Clusters are transient resources that interact with data stored in tables, views, and files.

ALTER TABLE sales_transactions MODIFY COLUMN loyalty_tier STRING

Why it fails: MODIFY COLUMN is used to change the data type or position of an existing column, not to add a new one. Since loyalty_tier does not exist yet, this command would throw an error. It is not the appropriate way to add a new column to a Delta table in Databricks SQL.

ALTER TABLE sales_transactions ADD loyalty_tier STRING

Why it fails: The correct syntax requires the keyword COLUMN after ADD. Omitting COLUMN makes the statement invalid in Databricks SQL. While some databases allow ADD without COLUMN, Databricks SQL does not; it expects ADD COLUMN. Thus, this command would fail and not achieve the goal.

ALTER TABLE sales_transactions ADD COLUMNS (loyalty_tier STRING)

Why it fails: Although ADD COLUMNS (plural) with parentheses is valid in some SQL dialects, Databricks SQL uses ADD COLUMN (singular) without parentheses for Delta tables. The plural form with parentheses is not supported in Databricks SQL and would result in a syntax error. Therefore, this option fails in this scenario.

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

Why it fails: A view can simplify queries by pre-joining tables, but it does not organize the underlying tables or provide a logical namespace. The dimension and fact tables still exist independently and need to be managed. While views can be useful, they do not fulfill the requirement of organizing related tables for discoverability and namespace.

Create a separate schema for each dimension table

Why it fails: Creating a separate schema for each dimension table would lead to excessive fragmentation and make it harder to manage and discover related tables. Schemas are meant to group related objects, so putting each dimension in its own schema defeats that purpose and complicates queries and permissions. It does not provide a logical namespace for the star schema as a whole.

Use table comments to describe the relationships between tables

Why it fails: Table comments provide documentation but do not create a logical namespace or organize tables. They are helpful for discovery but do not group tables together. The requirement is to organize related tables, which is achieved through schemas, not comments. Comments are supplementary and do not replace proper schema design.

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

Why it fails: This approach requires manual scheduling and does not automatically update the table on insert. It introduces latency and potential for missed data. Generated columns provide an automatic, real-time solution without external jobs, making this option less efficient and not meeting the 'automatically' requirement.

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

Why it fails: A view does not store data and is not a table. While it dynamically computes date_key, it does not create a new table and does not automatically update a separate table. The requirement is for a new table that is updated automatically, which a view does not provide.

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

Why it fails: Delta Live Tables can automate transformations, but it is a separate orchestration service and may be overkill for this simple derivation. It also requires setting up a pipeline and may not be the most straightforward solution within Databricks SQL. The question asks for an approach using Databricks SQL, and generated columns are a native feature.

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

Why it fails: Z-ORDER improves data skipping by co-locating related data, but it operates on existing columns. Since the join is on 'date_key', Z-ORDER on 'order_date' would not directly optimize the join. Additionally, Z-ORDER is a one-time operation that requires maintenance and may not be as efficient as clustering on a generated column that aligns with the join key.

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

Why it fails: Views are logical and do not store data; clustering a view is not supported in Databricks SQL. While a view can simplify queries, it cannot provide physical data skipping on the underlying fact table. The analyst needs to optimize the physical storage of the fact table, not just the query interface.

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

Why it fails: Partitioning by 'order_date' can help with pruning, but the scenario specifies that the join uses a surrogate key 'date_key' of type INT. Filtering by date through the dimension table would not directly prune partitions on 'order_date' unless the query includes that column. Moreover, partitioning by a high-cardinality column like date can lead to too many small files, degrading performance.

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

Why it fails: A generated column can be useful for deriving partitions, but if the fact table already has a date column, it is unnecessary. Adding a generated column adds complexity and does not provide additional pruning benefits beyond partitioning by the existing date column. The scenario does not indicate that the date is derived; it is a foreign key, so a direct partition on that column is simpler and equally effective.

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

Why it fails: Z-ORDER improves data skipping but does not provide the same level of pruning as partitioning. It requires periodic optimization and may not be as effective for date-based filters, especially if the table is frequently updated. Partitioning is the recommended approach for date columns in fact tables to enable partition pruning, which is more efficient and automatic for range queries.

Partition the date dimension by the date column.

Why it fails: The date dimension is typically small and does not benefit from partitioning. Partitioning it would not help with pruning the fact table, which is the larger table. The goal is to prune the fact table, so partitioning the dimension is ineffective. Additionally, partitioning a small dimension can create unnecessary overhead and many small files without performance gains.

Row filters

Why it fails: Row filters are used to restrict access to rows based on a filter condition, not columns. They are useful for row-level security but do not provide column-level security. The requirement is to restrict access to certain columns, so row filters are not applicable.

Table ACLs

Why it fails: Table ACLs control access at the table level, not at the column level. They can grant or revoke privileges like SELECT on the entire table, but they cannot restrict access to specific columns. Therefore, they do not meet the requirement for column-level security.

Delta table constraints

Why it fails: Delta table constraints, such as CHECK constraints, enforce data quality rules but do not control user access. They cannot be used to restrict which columns a user can see. Therefore, they are not suitable for implementing column-level security.

Implement a primary key constraint on the region_id column.

Why it fails: Primary keys in Databricks SQL are informational and do not enforce referential integrity or physically reorder data on disk. While they document relationships, they provide zero performance benefit for query filtering or data skipping, making them ineffective for addressing the requirement of reducing data scanning for large tables.

Execute the ANALYZE TABLE command periodically without any data clustering.

Why it fails: The ANALYZE TABLE command collects statistics for the query optimizer, which helps with join ordering and plan selection. However, it does not physically reorganize the underlying Parquet files. Without clustering or Z-Ordering, the data remains in its insertion order, forcing the engine to scan more files during lookups.

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

Why it fails: Changing to CSV is detrimental to performance because CSV is a row-based format that does not support file-level statistics or column pruning. Databricks SQL is optimized for Delta Lake, which uses Parquet with metadata-rich footers. CSVs lack these features, preventing effective data skipping and significantly slowing down query execution.

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

Why it fails: Partitioning by a high-cardinality column like 'id' creates millions of small files, severely impacting metadata performance and storage efficiency. This approach would lead to increased listing times and poor query performance, effectively negating the benefits of Delta Lake’s metadata management and optimization features provided by the platform.

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

Why it fails: Creating a secondary table introduces data redundancy and requires complex synchronization logic to ensure both tables remain consistent. This creates unnecessary overhead and risk of data drift, failing to leverage the native indexing and clustering capabilities built into Databricks SQL for high-performance retrieval on large tables.

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

Why it fails: Standard Parquet tables lack the transaction log and metadata management features of Delta Lake, making it impossible to perform reliable updates or schema evolution. Furthermore, removing partitioning without a secondary clustering strategy will force full table scans for every query, significantly reducing performance for any specific record retrieval.

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

Why it fails: Databricks SQL does not have a unique feature called 'Star Schema optimization' exclusive to Delta. While Delta Lake provides significant performance improvements and reliability, the star schema design pattern is a logical modeling choice that can be implemented across different formats, though Delta provides the best performance and consistency.

Delta Lake automatically reorders dimension tables to improve join performance.

Why it fails: Delta Lake does not automatically reorder tables based on join patterns. While features like Z-Ordering can be applied manually to optimize performance, there is no inherent 'auto-reordering' feature for dimensions. Optimizing joins requires intentional data modeling choices such as clustering or selecting appropriate join algorithms for specific query types.

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

Why it fails: Physical location or logical schema placement does not impact join performance in Databricks. Join efficiency is determined by data distribution, shuffling, and the availability of statistics. Storing tables in the same schema is a organizational best practice, but it provides no inherent technical performance benefit for cross-table joins.

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

Why it fails: Delta Lake cannot function without the transaction log. Liquid Clustering operates within the Delta framework and relies entirely on the transaction log to maintain ACID properties. Disabling the log would result in the loss of consistency, time travel, and rollback capabilities, which are fundamental to the Databricks SQL experience.

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

Why it fails: Liquid Clustering does not sort by every column, as this would be computationally prohibitive and provide no benefit for most queries. It focuses on the specific columns defined as clustering keys, ensuring those dimensions are well-indexed for data skipping. Sorting everything would destroy write performance and waste significant compute resources.

Implement a materialized view with a static snapshot.

Why it fails: Materialized views represent the data as of the last refresh, not the history of changes. They do not provide a native mechanism to query 'as of' a specific point in time, requiring manual snapshots which are difficult to manage and consume unnecessary storage for large datasets.

Create a new table for every daily load.

Why it fails: Creating daily tables leads to massive technical debt and complicates the query layer significantly. Users would have to union multiple tables to perform longitudinal analysis, which is inefficient and prone to errors. Time Travel renders this redundant by providing a built-in historical view in a single table.

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

Why it fails: While the Type 2 SCD approach works, it complicates query logic as users must filter by the 'is_current' flag or specific dates for every query. It also increases the size of the active table, potentially slowing down standard performance. Delta Time Travel provides this functionality natively without complicating the schema.

Create separate physical tables for each security level.

Why it fails: Managing separate tables for security introduces massive redundancy, synchronization challenges, and storage overhead. It is a highly inefficient way to handle access control and makes schema evolution nearly impossible to manage correctly. Modern Databricks security features allow for granular control without needing to physically separate the data architecture.

Change the file format to JSON to strip sensitive fields.

Why it fails: Changing file formats does not provide security or access control. Anyone with access to the storage layer could still read the files. Furthermore, JSON format is significantly less performant for analytical queries than Delta Lake, making this an inappropriate choice for both security and performance optimization in Databricks SQL.

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

Why it fails: Relying on end-users to apply security filters is a major security risk, as it is prone to human error and bypasses. Security must be enforced at the platform level (Unity Catalog) to ensure it is consistent, immutable, and auditable, which is the standard practice in a mature data governance framework.

The table is not in the Delta format.

Why it fails: If the table were not in Delta format, the error would not specifically mention Delta column mapping or the specific mode configuration. The error clearly indicates that the table is a Delta table, but its current configuration lacks the metadata layer required to support the Change Data Feed functionality.

The table has too many rows to enable CDF.

Why it fails: Delta Lake does not have a row count limit for enabling Change Data Feed. CDF is designed to work at scale on large production tables. The error is strictly related to configuration and table properties, not the volume of data contained within the existing Delta table structure at the moment.

CDF is only supported on tables with primary keys.

Why it fails: Change Data Feed does not require primary keys to function. It tracks inserts, updates, and deletes by recording the delta changes in the transaction log. While primary keys are useful for some data modeling patterns, they are not a technical prerequisite for the CDF feature in Databricks SQL.

To shrink the transaction log file size.

Why it fails: VACUUM processes the actual data files, not the transaction log. The transaction log is managed automatically by Delta Lake through checkpoints. While VACUUM helps clean up the storage layer, it does not directly manage or 'shrink' the log files themselves; that is handled by the Delta engine's internal maintenance.

To re-index the table for faster performance.

Why it fails: VACUUM is a maintenance command for storage cleanup, not a performance optimization tool. To improve query performance, one should use Z-Ordering, Liquid Clustering, or OPTIMIZE. VACUUM has no impact on query speed and should not be used as a strategy for optimizing table read or write performance.

To force a merge of all Delta logs into a single Parquet file.

Why it fails: Merging logs is not the function of VACUUM. Delta Lake automatically performs log checkpointing to combine transaction logs into Parquet files. This process is managed automatically by the system and does not involve deleting files, which is the core function of the VACUUM command during table maintenance operations.

Store the data in a CSV file format.

Why it fails: CSV is not an optimal format for MERGE operations. Because CSV lacks metadata (statistics, indices, and transaction logs), every merge would force a full table scan and rewrite. This is highly inefficient, does not support ACID transactions, and makes concurrent writes nearly impossible to manage correctly in a data lake.

Disable the Delta transaction log.

Why it fails: Disabling the transaction log is not possible in Delta Lake and would destroy the ACID guarantees that make MERGE operations reliable. The transaction log is what allows Delta to perform atomic updates. Without it, there would be no way to ensure the integrity of the data during the merge process.

Ensure the target table has no indexes or clustering.

Why it fails: Having no indexes or clustering is the worst-case scenario for MERGE operations. The engine would have to scan the entire table to identify matching records, resulting in extremely slow performance and high compute costs. Proper clustering is a fundamental requirement for efficient DML operations on large tables in Databricks SQL.

Apply the CLUSTER BY clause on the primary key of the fact table.

Why it fails: Clustering the fact table improves data skipping and storage organization but does not inherently optimize joins with smaller tables. While it enhances overall table scans, it fails to address the network overhead of shuffling large datasets across the cluster nodes during join operations, which broadcast joins solve more efficiently.

Use the Z-ORDER BY clause on the dimension table's primary key.

Why it fails: Z-Ordering is an excellent technique for optimizing data skipping on specific columns, particularly for range queries or equality filters. However, it does not influence the join strategy chosen by the Catalyst optimizer. Shuffling remains the default behavior for large-to-large joins regardless of Z-Order indices applied to the tables.

Convert the fact table into a temporary view before joining.

Why it fails: Converting a table to a temporary view is purely a syntactic abstraction and does not alter the physical execution plan or the underlying join algorithm. The optimizer treats the view essentially like a table, meaning standard shuffling mechanisms will still occur unless specific broadcast instructions or hints are explicitly provided.

Enforce strict schema validation during ingestion.

Why it fails: Schema validation is handled by Delta Lake's schema enforcement features, not by clustering. Liquid clustering is purely concerned with the physical layout of the files on storage to optimize read performance and data skipping, whereas schema enforcement ensures data integrity and type safety during write operations into the table.

Implement row-level security policies.

Why it fails: Row-level security is managed through Unity Catalog's security features, such as GRANT statements and row filters. Clustering only affects the physical storage layout and has no role in restricting data access or ensuring that specific users only see certain rows based on their identity or organizational roles.

Manage concurrent write conflicts in Delta tables.

Why it fails: Write conflicts are governed by Delta Lake's optimistic concurrency control mechanism, which checks for version conflicts during commit operations. Clustering settings do not influence how write transactions are serialized or how conflicts are resolved; they are purely performance-oriented settings for the storage layer of the Delta table files.

It forces all writes to be synchronous, ensuring immediate data consistency across all cluster nodes.

Why it fails: Auto-optimization properties do not alter the consistency model or transaction isolation of Delta Lake. Delta Lake already provides ACID guarantees by default. These properties focus on the physical layout and file size optimization rather than the synchronization of write operations across nodes, which is handled by the Delta transaction log.

It automatically triggers a FULL OPTIMIZE command every hour regardless of file counts.

Why it fails: Auto-compaction is an asynchronous background process that occurs based on write patterns and file sizes, not a fixed time schedule. It does not perform a full table optimization, which is a resource-intensive operation. Instead, it opportunistically compacts small files to maintain read performance during normal table operations.

It forces the table to use a liquid clustering layout for all future data partitions.

Why it fails: Auto-optimization properties are independent of liquid clustering. While both aim to improve performance, they operate on different mechanisms. Auto-optimization manages file sizing and compaction, whereas liquid clustering manages data organization within files. Applying one does not automatically enable or convert the table to use the other feature.