Data Modeling: free practice questions

In Delta Lake, Z-ORDER operates within partition boundaries — it does not reorganize data across partitions. When a table is partitioned by year and month, running OPTIMIZE ZORDER BY (account_category) clusters files within each individual partition by account_category values. This allows the engine to skip files within a partition that don't contain the queried account_category values. Option A is incorrect — Z-ORDER never moves data across partition boundaries; partitioning and Z-ORDER are complementary, not conflicting. Option C is incorrect — Z-ORDER does not create nested partition directories; it is purely a file-level clustering mechanism within existing partitions. Option D is incorrect — Z-ORDER and partitioning work together effectively; partitioning handles coarse-grained filtering (year/month) while Z-ORDER handles fine-grained filtering (account_category) within each partition.

Option D is correct. No single Unity Catalog object type simultaneously satisfies all three requirements. A standard SQL VIEW always reflects the latest table state without a refresh job, but it does NOT natively support incremental streaming updates — it is re-executed at query time. A STREAMING TABLE supports incremental updates for streaming consumers but requires continuous or triggered pipeline updates (it is not a simple passthrough view). A MATERIALIZED VIEW pre-computes results and must be refreshed; it does not always reflect the latest state in real time without a refresh mechanism. The requirements as stated — real-time latest state, no refresh job, AND streaming incremental support — span two different object types. Option A is partially correct (views are always fresh) but misses the streaming consumer requirement. Option B is wrong — Materialized Views require explicit refresh and do not inherently support streaming incremental updates. Option C is wrong — Streaming Tables are for streaming ingestion pipelines, not ad-hoc queries that always reflect the latest state without a pipeline.

Option C is correct. DLT does not have a native QUARANTINE action. The standard pattern to quarantine invalid rows is to omit the ON VIOLATION clause (which defaults to keeping the row and recording the violation as a metric) on the primary table, then define a second DLT table that selects only the rows failing the expectation from the upstream source. This keeps violating rows accessible without silently discarding or halting the pipeline. Option A (DROP ROW) discards invalid records — they are not quarantined for analysis. Option B (FAIL UPDATE) halts the entire pipeline update on any violation. Option D is a distractor — QUARANTINE ROW is not a valid DLT ON VIOLATION action; valid options are DROP ROW and FAIL UPDATE (or omitting the clause).

Option B is correct. The three consumer workloads have fundamentally different access patterns, granularities, and time horizons: the dashboard needs recent, high-frequency store/SKU-level data; the finance report needs 12-month aggregations at region/category grain; and the ML feature store needs individual transaction records for 90 days. Creating separate, purpose-built Gold tables as DLT Materialized Views allows each to be scoped to the minimal columns and rows needed, clustered or partitioned for their specific query pattern, and refreshed independently—maximizing query performance and minimizing cost. Option A is wrong because duplicating the full 8 TB Silver table into Gold is extremely costly and inefficient; Z-ORDERing on 5 columns simultaneously dilutes clustering effectiveness (Z-ORDER works best on 1-3 high-cardinality columns), and a single table cannot be optimally organized for all three divergent access patterns. Option C is wrong because partitioning by two high-cardinality date and region columns creates a risk of over-partitioning and still forces all three workloads to scan a single large table—query performance will suffer for the finance report (12-month range) and ML workload (90-day individual records) because partition pruning alone cannot compensate for the lack of workload-specific clustering. Option D is wrong because the Silver layer should not be exposed directly to Gold consumers—this violates medallion architecture separation of concerns; Silver tables typically contain partially cleaned data and the unified consumption layer (Gold) should provide business-ready, semantically enriched datasets. Using dynamic views for access control is valid in Unity Catalog but does not address performance optimization for divergent workloads. Option E is wrong because a single pre-aggregated Gold Materialized View at the daily/store/SKU/region/category grain cannot serve the ML feature store, which needs individual transaction-level records—aggregated data cannot be disaggregated back to transaction rows, making this design fundamentally incompatible with one of the three consumer requirements.

Apache Hudi was originally designed at Uber specifically for high-throughput upsert/incremental pipelines. Its MergeOnRead (MOR) table type writes delta (log) files on upsert and merges them at read time, dramatically increasing write throughput at the cost of slightly more expensive reads — a well-known trade-off optimized for streaming upsert use cases. Option A is false — both Hudi (upsert API) and Iceberg (MERGE INTO with newer versions) support row-level updates; they do not require full table rewrites. Option C is misleading — Iceberg's hidden partitioning helps with partition management but does not inherently eliminate data file rewrites during updates; Copy-on-Write (CoW) in Iceberg still rewrites affected files. Option D is false — OPTIMIZE and ZORDER are post-write optimization commands in Delta Lake and are never prerequisites for MERGE INTO or other DML operations.