Data Modelling practice questions

Define a rigid schema in the Bronze table to force all incoming data to match the expected format.

Why it fails: Rigid schemas in Bronze cause ingestion pipelines to fail when source systems introduce new fields or modify existing ones. This forces manual intervention, breaks automated pipelines, and prevents the storage of raw data needed for historical reprocessing if business requirements evolve downstream later in the project lifecycle.

Cast all columns to string types in the Silver layer to avoid schema mismatch errors during ingestion.

Why it fails: Casting everything to string degrades query performance significantly as downstream users must perform manual casting for analytical operations. It also prevents the use of efficient Delta Lake data skipping and column-level statistics, which are essential for optimizing performance on large-scale analytical datasets within the Databricks Lakehouse architecture.

Drop all columns that do not match the pre-defined target schema before writing to the Silver layer.

Why it fails: Dropping non-matching columns leads to data loss, as valuable new information is discarded without being stored. This approach negates the benefit of having a Bronze layer and forces the engineering team to manage schema updates manually, which is inefficient and error-prone in highly dynamic IoT environments.

Partition the table by every column used in the WHERE clause.

Why it fails: Partitioning by high-cardinality columns or too many columns creates a 'small file problem' and excessive metadata overhead. This degrades performance significantly because the Delta engine spends more time managing thousands of tiny files than actually executing the query, resulting in slower performance than a well-indexed, non-partitioned table.

Implement row-level security for every query to ensure partition pruning works correctly.

Why it fails: Row-level security is an access control mechanism, not a performance optimization technique. While important for data governance, it does not assist with partition pruning or data skipping. Implementing it incorrectly can actually hinder performance by preventing the query optimizer from efficiently identifying the required data files for execution.

Store all historical data in a single massive file for optimal compression.

Why it fails: Storing data in a single massive file prevents parallel processing, as only one executor can read the file at a time. Furthermore, it negates the benefits of data skipping and pruning, forcing the engine to perform a full table scan for even the simplest point-in-time query, causing severe latency.

The cardinality of the column; avoid low-cardinality columns.

Why it fails: Low-cardinality columns are actually good for partitioning. High-cardinality columns should be avoided because they create too many partitions, leading to the small file problem and metadata management issues, which can significantly degrade the performance of both reads and writes within the Databricks Lakehouse architecture.

File format compatibility; ensure all files are in CSV format.

Why it fails: Delta Lake uses the Parquet format for its underlying data files, not CSV. Parquet is highly optimized for analytical workloads, providing columnar storage and efficient compression. Storing data in CSV would negate all the performance benefits of Delta Lake, including fast data skipping and efficient predicate pushdown.

The table needs to be VACUUMed to remove corrupted metadata.

Why it fails: VACUUM is used to remove stale files from the file system, not to resolve schema-related issues. It does not affect the metadata stored in the Delta log or the schema definitions of the table. Running VACUUM will not fix a column resolution error in a transformation query.

The table partition was dropped, causing the lookup to fail.

Why it fails: Dropping a partition would result in an empty result set or a filter error, but it wouldn't cause an 'AnalysisException' for a missing column name. This error specifically points to a schema mismatch where the column definition itself is missing from the table's current metadata state.

The table size exceeds the limit for metadata management.

Why it fails: Databricks tables do not have a metadata size limit that would cause a 'Cannot resolve column' error. This error is strictly related to the logical schema structure of the table. Metadata limits, if they were to exist, would typically involve timeouts or performance degradation, not schema-resolution failures.

Denormalize `dim_product` into `fact_sales` by embedding product attributes directly into the fact table.

Why it fails: Denormalizing product attributes into the fact table increases storage and update complexity. When product attributes change, all historical facts would need updates, which is costly and error-prone in Delta Lake. It also bloats the fact table, slowing down scans. This approach is not recommended for frequently changing dimensions.

Implement a snowflake schema by normalizing `dim_product` into multiple related tables.

Why it fails: Snowflaking increases the number of joins, which can degrade performance in a Lakehouse environment where joins across many tables are expensive. While it reduces redundancy, it complicates queries and may not leverage Databricks' optimizations as effectively. For high-frequency updates and large facts, a star schema is generally preferred.

Partition `fact_sales` by the product's category to improve join performance.

Why it fails: Partitioning the fact table by product category may lead to uneven partitions and does not directly improve join performance with the dimension table. Join performance benefits more from Z-ORDERing on join keys. Partitioning by a high-cardinality column like product_id could cause too many small files, harming performance.

Partition by product_id and store_id

Why it fails: Partitioning by product_id and store_id would create an enormous number of partitions due to high cardinality, leading to small-file problems and metadata overhead. This would degrade performance for monthly filters because partition pruning on sale_month would not be possible. Partitioning should be on low-cardinality columns like sale_month.

Use liquid clustering on sale_month, product_id, store_id

Why it fails: Liquid clustering is a newer feature that can optimize data layout, but it is not yet as widely supported or mature as Z-Ordering for this scenario. While it can handle multiple columns, it may not provide the same level of performance for join-heavy queries. Partitioning by sale_month combined with Z-Ordering is a proven approach.

Bucket the table by product_id into 256 buckets

Why it fails: Bucketing is not a native Delta Lake feature and would require additional management. It is more common in Hive or Spark tables. Delta Lake relies on partitioning and Z-Ordering for optimization. Bucketing would not integrate seamlessly with Delta's transaction log and could complicate queries and maintenance.

Stop the stream, drop and recreate the Silver table with the new column, and restart from the original source

Why it fails: Dropping and recreating the table discards all processed history and forces a full replay from the source, which is extremely costly at billions of rows and may not even be possible if the source retains limited data. It also loses the checkpoint's position semantics, so the pipeline would reprocess or skip events unpredictably, making it a disruptive and expensive choice.

Create a new Gold table with order_margin and point all consumers there, leaving the Silver table unchanged

Why it fails: Introducing a separate Gold table avoids modifying Silver but does not actually compute order_margin for historical data unless a full transformation is run, and it fragments the model by duplicating order data. The requirement is to enrich the Silver layer and keep it current, so a parallel table adds maintenance overhead without satisfying the backfill-and-continue goal efficiently.

Enable schema evolution on the stream and rely on automatic type widening to introduce order_margin

Why it fails: Schema evolution lets a stream add new columns when incoming data contains them, but it does not compute a derived value that depends on a separate lookup table, nor does it backfill existing rows. Relying on it here would leave historical rows with null order_margin and would not implement the join logic needed to calculate margin from supplier costs.

Partition the table by store_id.

Why it fails: Partitioning by store_id, which may have moderate cardinality, can help with filtering on store_id, but if store_id has high cardinality, it may create too many partitions. Also, it does not address filtering on product_id. Partitioning alone is not sufficient for optimizing both filter columns.

Z-ORDER BY store_id.

Why it fails: Z-ORDER BY store_id would cluster data by store_id, which could help if store_id is frequently used in filters. However, if store_id is already a partition column, Z-ORDER on it is redundant. Also, the question emphasizes filtering on both store_id and product_id; Z-ORDER on product_id is more critical if store_id is partitioned.

Enable Delta Lake caching on the table.

Why it fails: Delta Lake caching can improve performance for repeated queries, but it is not a table design strategy and does not address the fundamental data layout for filtering and joining. It also consumes cluster resources and is not persistent across clusters. Thus, it is not a primary optimization for this scenario.

Create a degenerate dimension in the fact table for readmission status.

Why it fails: A degenerate dimension is a dimension key stored in the fact table without a corresponding dimension table, often used for transaction numbers. Readmission status is not a key but a derived attribute. Storing it as a degenerate dimension would not allow grouping without additional logic, and it would not be a proper dimension.

Create a role-playing dimension for readmission status based on the admission date.

Why it fails: A role-playing dimension is a single dimension used multiple times in a fact table with different meanings, like date dimensions. Readmission status is not a role of an existing dimension; it is a derived metric. Therefore, this approach is not applicable and would not provide the desired grouping.

Create a junk dimension that combines readmission status and other flags.

Why it fails: A junk dimension combines low-cardinality flags into a single dimension to reduce the number of dimensions. However, readmission status is derived from fact table dates and is not an independent attribute. Using a junk dimension would require precomputing readmission status during ETL, which may not be flexible for changing definitions. It is not the best fit for this scenario.

json_extract(event_properties, '$.device_type')

Why it fails: json_extract is not a built-in Spark SQL function. While it exists in other SQL dialects like SQLite or MySQL, Databricks Spark SQL does not support it. Using it would cause an error. The correct function is get_json_object or from_json.

from_json(event_properties, 'device_type string')

Why it fails: from_json is used to parse a JSON string into a struct or map, not to extract a single field directly. It returns a complex type that requires further selection. While it can be used in combination with dot notation, it is not the most direct or efficient way to extract a single field.

event_properties:device_type

Why it fails: The syntax event_properties:device_type is not valid Spark SQL for JSON extraction. While some databases use colon notation for nested fields, Spark SQL requires functions like get_json_object or from_json. This would result in a syntax error or unexpected behavior.

The partition columns should include 'customer_id' to improve the pruning speed.

Why it fails: Adding high-cardinality columns like 'customer_id' to the partition strategy leads to the small file problem. This increases metadata overhead and creates thousands of unnecessary directories, which actually slows down query performance and makes file management significantly more difficult to automate within the Databricks environment.

Z-Ordering must be performed on the partition columns instead of the join columns.

Why it fails: Z-Ordering is meant for non-partition columns, typically those with high cardinality used in filters or joins. Z-Ordering on partition columns is redundant because partition pruning already handles the filtering logic. The issue here is the lack of partition pruning in the query, not the Z-Ordering configuration itself.

The file format should be changed to Parquet to improve individual file read performance.

Why it fails: Delta Lake is built on top of Parquet and provides features like Z-Ordering and partition pruning that raw Parquet does not support natively. Changing the format to Parquet would remove the ability to use Delta-specific optimizations, making the query performance even worse while losing critical ACID transaction capabilities.

The Bronze layer acts as the primary layer for historical aggregation and business logic.

Why it fails: Bronze is the raw storage layer. Data is stored in its original format to ensure reproducibility and provide a source for reprocessing. Applying business logic here defeats the purpose of maintaining an immutable raw history, as it would modify the source data before it is ever properly validated.

The Silver layer is designed for applying complex business logic and final aggregations.

Why it fails: Silver is for cleaning, filtering, and joining data. While it contains structured data, it is not optimized for end-user reporting. Applying final business aggregations here is premature, as the Silver layer should remain flexible enough to support multiple business use cases that might require different analytical aggregations later.

The Raw layer stores all business logic results for future auditing.

Why it fails: The Raw layer only holds ingestion-time copies of data. It does not contain any processed results or business calculations. Storing audit logs or business results in the Raw layer is a misuse of the storage architecture and violates the principle of keeping Raw storage strictly for original source data.

Simply run the DELETE statement; Delta handles physical deletion automatically.

Why it fails: DELETE only marks records as deleted in the current state of the Delta table. The underlying Parquet files still contain the data until the VACUUM command is run. For GDPR compliance, this is insufficient, as the data remains retrievable through time travel or by accessing the files directly.

Use the OPTIMIZE command to force the deletion of the records.

Why it fails: OPTIMIZE is a compaction command used to reorganize data into larger files for better read performance. It does not perform deletions. While it might overwrite files containing the data, it is not a reliable method for data deletion and cannot be used to guarantee compliance with privacy regulations.

Overwriting the table with a filtered subset using the OVERWRITE option.

Why it fails: While overwriting effectively removes records, it is a destructive operation that replaces the entire table. If you have many users or concurrent processes, this approach is dangerous and not standard practice for GDPR deletion. It is prone to error and does not offer the same granularity as DELETE.

Discard all late-arriving records to ensure a clean source-of-truth.

Why it fails: Discarding data leads to inaccurate business metrics, especially in systems where event time is crucial. Data engineering should focus on designing robust pipelines that can handle late arrivals, rather than simply ignoring them, which would compromise the completeness and validity of the final analytical datasets in the Gold layer.

Append late-arriving data to the Bronze layer but ignore it in all downstream layers.

Why it fails: Ignoring data in downstream layers means the business never sees the complete picture. This creates a disconnect between the data captured in Bronze and the insights provided in Gold. Effective data modeling requires that late arrivals are integrated into the final reporting tables to ensure consistency across the architecture.

Create a separate 'late_data' table and join it to the main table every month.

Why it fails: Creating a separate table adds unnecessary complexity to the model and makes querying significantly harder for end-users. It complicates maintenance and reporting, as users must perform complex joins to see the full dataset. The preferred approach is to integrate late data into the main table structure immediately.

It eliminates the need for data partitioning and Z-Ordering.

Why it fails: Partitioning and Z-Ordering are performance tuning techniques that remain essential regardless of the architecture. The medallion architecture is about logical data organization and quality, not replacing physical storage optimizations. Both are required for a high-performing system at scale, regardless of how many layers are implemented in the pipeline.

It forces all data to be stored in a star schema at all layers.

Why it fails: Star schemas are typically used in the Gold layer for reporting. Forcing this structure on the Bronze or Silver layers would be inefficient, as those layers are meant for raw ingestion and cleaning. The architecture is flexible enough to allow different storage patterns at each stage of the pipeline.

It automatically converts all incoming data to a structured format.

Why it fails: The architecture does not automatically convert data. While the Silver and Gold layers are highly structured, the conversion from unstructured or semi-structured data to a structured format requires deliberate engineering effort, such as defining schemas, parsing JSON, and enforcing data quality rules throughout the ingestion process.

To store the raw data in an immutable format for historical auditing.

Why it fails: This describes the Bronze layer. Bronze is where raw, source-originated data is kept in its original format. The Gold layer has a completely different purpose: to provide highly refined, aggregated, and business-oriented data that is ready for consumption by end-users and analytical tools, rather than raw audit history.

To serve as a staging area for data cleaning and filtering.

Why it fails: This describes the Silver layer. Silver is the place where initial cleaning, standardization, and deduplication of data occur. The Gold layer is the result of applying further business logic and aggregation to the already cleaned and structured data found in the Silver layer, preparing it for end-user reporting.

To store only metadata for the entire Lakehouse architecture.

Why it fails: The Gold layer is not a metadata store; it is a data layer containing processed business information. Metadata is managed by the Unity Catalog or the Delta log. Storing metadata in the Gold layer would be a misuse of the storage layer and would not provide any analytical value.

Increase the number of partitions to spread the data across more nodes.

Why it fails: Increasing partitions on large tables without regard for the data size per partition often leads to the small file problem. This increases metadata load and slows down both read and write operations. It does not help with join performance unless the data is skewed, which is a different problem.

Cast all join keys to strings to ensure data type compatibility.

Why it fails: Casting to strings is a performance anti-pattern. It increases memory usage and forces the engine to perform string comparisons, which are much slower than integer comparisons. It also breaks the effectiveness of data skipping and Z-Ordering, making the overall join operation significantly slower than it would be with native types.

Use a broadcast join for all large-table join operations.

Why it fails: Broadcast joins are only effective when one of the tables is small enough to fit into the memory of every executor. For two large tables, a broadcast join will cause an OutOfMemory error or fail entirely. Attempting to force broadcast joins on large tables is a common cause of pipeline failures.

Run a DELETE statement for the partition and then an APPEND.

Why it fails: This approach is two separate transactions, which can lead to data inconsistency if a reader accesses the table in between. It is also less performant than a single atomic overwrite operation. Using partition overwrite is a more reliable and efficient way to achieve the same result in one step.

Use the MERGE command to overwrite only the records in the specific partition.

Why it fails: While MERGE can perform updates, it is not the standard or most efficient way to replace an entire partition. MERGE is designed for row-level upserts. Partition overwrite is optimized for the bulk replacement of data, which is faster when the entire contents of a partition are being replaced.

Set 'spark.databricks.delta.retentionDurationCheck.enabled' to false.

Why it fails: This setting is for managing the retention period of deleted files during VACUUM operations. It has nothing to do with overwriting partitions or controlling the behavior of the write operation. This configuration change would not help with replacing a specific partition and is irrelevant to the requirement presented.

Partition the table by account_id as well as txn_date

Why it fails: Adding account_id as a partition column would create a huge number of small partitions (high cardinality), leading to many tiny files and metadata overhead. This would degrade performance for all queries, not improve account_id filters. Partitioning is best for low-cardinality columns, and account_id likely has millions of distinct values.

Enable Delta Lake change data feed

Why it fails: Change data feed (CDF) tracks row-level changes for downstream consumption, not for accelerating query performance. It adds metadata and does not reorganize data for skipping. Enabling CDF would not help queries filtering on account_id; it is designed for CDC scenarios, not for optimizing existing read patterns.

Convert the table to a Hive table

Why it fails: Converting to a Hive table would lose Delta Lake's advanced features like ACID transactions, time travel, and data skipping. Hive tables do not support Z-Ordering or Delta-specific optimizations. This would likely worsen performance and is not a recommended approach for improving query speed in Databricks.

Partition the fact_sales table by product_id.

Why it fails: Partitioning by product_id would create a very large number of small partitions because product_id is high-cardinality. This leads to the small file problem, increased metadata overhead, and degraded performance. Partitioning is better suited for low-cardinality columns like sale_date.

Convert the fact_sales table to a Parquet table and use partition pruning.

Why it fails: Converting to Parquet would lose Delta Lake features such as ACID transactions and time travel. While Parquet supports partition pruning, the existing partitioning by sale_date already provides that. This change would not address filtering on product_id and could introduce data consistency issues.

Add a bloom filter index on product_id.

Why it fails: Databricks Delta Lake does not support user-defined bloom filter indexes. While bloom filters are used internally for data skipping on some columns, there is no direct SQL command to create them. Thus, this is not a valid optimization technique in Databricks.