Task 9.1: Design scalable Delta/Iceberg table layouts for large data assets by mapping partitioning to data grain, aligning clustering to relationship access patterns, and maintaining balanced file sizes through compaction.

• ARun VACUUM on the table to clear out the smaller files

  Incorrect

  VACUUM removes files no longer referenced by the table; it does not merge the current files.

• BSet autoOptimize.autoCompact to true on the table

  Incorrect

  With true, auto compaction targets 128 MB files and does not rewrite older files.

• CCall coalesce(1) before each write to the table

  Incorrect

  Forcing one output file per write slows writes and does not fix the existing files.

• DSet delta.targetFileSize to 1gb, then OPTIMIZE

  Correct

  When the autotuned target grows, OPTIMIZE does not re-optimize existing files; a fixed target makes it compact them to that size.

Databricks autotunes target file size by table size, from 256 MB for smaller tables up to 1 GB above 10 TB. Existing files are not rewritten when the target grows, so a fixed targetFileSize is needed if all files must reach the larger size.

• AUse liquid clustering on the fact table's join keys, such as store_id

  Correct

  Clustering on the columns used in joins colocates matching rows, so dynamic file pruning can skip more files.

• BPartition the fact table by store_id

  Incorrect

  Partitioning a large table by a high-cardinality key creates many small partitions and is not the recommended layout.

• CCluster the small dimension tables by region

  Incorrect

  Pruning is applied to the scan of the large table, so the fact table's layout is what matters.

• DRun the queries and MERGE statements on Photon-enabled compute

  Correct

  Dynamic file pruning in MERGE requires Photon, and Photon gives broader pruning for SELECT.

• EAdd a BROADCAST hint for the dimension tables

  Incorrect

  Broadcasting changes the join strategy but does not make the fact layout prunable.

Dynamic file pruning pushes join filter results into the scan of the large Delta table at runtime. It works best when the join columns are clustered, and for MERGE, UPDATE and DELETE it requires Photon.

• ARaise delta.targetFileSize to 10gb on the table

  Incorrect

  Larger files hold even more rows.

• BTurn off optimized writes for the narrow table

  Incorrect

  That changes file sizing but does not cap rows per file.

• CSet maxRecordsPerFile, as a Spark conf or a write option

  Correct

  Capping records per file avoids the error; Databricks recommends it only when necessary.

• DPartition the table by both columns to spread the rows

  Incorrect

  Partitioning a narrow table creates many tiny partitions.

Narrow tables can hit the Parquet limit on rows per file. Use spark.sql.files.maxRecordsPerFile or the DataFrameWriter option maxRecordsPerFile, which takes precedence, only when needed.

• AIn the cluster event log, as COMPACTION events for each write

  Incorrect

  Compaction is recorded in table history, not compute events.

• BIn table history, as OPTIMIZE with operationParameters.auto true

  Correct

  Auto compaction appears as OPTIMIZE with auto true; a manual OPTIMIZE has auto false.

• CIn SHOW TBLPROPERTIES, as a counter of the compactions that ran

  Incorrect

  Properties show settings, not operations.

• DIn the Spark UI's Storage tab for the writing cluster

  Incorrect

  The Storage tab shows cached data.

Review table history to confirm auto compaction: it appears as an OPTIMIZE operation with operationParameters.auto set to true.

• AA streaming append with a processing-time trigger

  Incorrect

  Streaming appends are not in the default list.

• BA DataFrame write after coalesce(10)

  Incorrect

  Databricks advises against coalesce or repartition before writes when optimized writes are used.

• CCOPY INTO from a classic cluster

  Incorrect

  COPY INTO is not among the operations listed as enabled by default.

• DA MERGE INTO statement that upserts changes

  Correct

  Optimized writes are enabled by default for MERGE, and for UPDATE and DELETE with subqueries.

Optimized writes shuffle data before writing to produce fewer, larger files. They are on by default for MERGE and for UPDATE and DELETE with subqueries, and for CTAS and INSERT on SQL warehouses.

• ARemove the setting so background auto compaction applies

  Correct

  Background auto compaction is available for managed tables once the old Spark setting is removed.

• BSet it to legacy to keep the old fixed 128 MB file size

  Incorrect

  Fixed legacy sizing works against automatic tuning.

• CAdd autoOptimize.optimizeWrite = false to the table's properties

  Incorrect

  Turning off optimized writes does not enable background compaction.

• DRun OPTIMIZE on the table after every write instead

  Incorrect

  Manual OPTIMIZE after every write adds cost.

Background auto compaction is available for Unity Catalog managed tables and does not need predictive optimization. When migrating, remove the autoCompact Spark configuration from cluster or notebook settings.