Browse cheat sheets · Microsoft Fabric

Technology: Microsoft FabricCategory: Data EngineeringDifficulty: IntermediateUpdated

Delta Lake Read and Write Patterns

Read and write Delta tables in Microsoft Fabric safely using append, overwrite, MERGE, partitioning, schema controls, concurrency-aware patterns, and table maintenance.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Read a managed Delta table by table name
  5. 2. Read Delta by path
  6. 3. Read only the columns you need
  7. 4. Filter early
  8. 5. Append new immutable rows
  9. 6. Add an ingestion batch identifier
  10. 7. Overwrite a full table only when full replacement is intentional
  11. 8. Overwrite schema only when the schema change is deliberate
  12. 9. Use mergeSchema carefully for additive schema evolution
  13. 10. Upsert with Delta MERGE
  14. 11. Prefer explicit update mappings for stable production contracts
  15. 12. Deduplicate the source before MERGE
  16. 13. Validate source uniqueness before a merge
  17. 14. Use a compound business key when one column is not enough
  18. 15. Update only when the incoming row is newer
  19. 16. Insert only new keys
  20. 17. Delete via MERGE only with a deliberate source contract
  21. 18. Use SQL MERGE when SQL is clearer
  22. 19. Use staging + merge for concurrent ingestion
  23. 20. Design retry logic for concurrent Delta conflicts
  24. 21. Understand Fabric Low Shuffle Merge
  25. 22. Partition only on useful low/moderate-cardinality dimensions
  26. 23. Do not over-partition small tables
  27. 24. Inspect partition distribution before changing layout
  28. 25. Avoid repartition(1) before Delta writes
  29. 26. Reduce excessive write partitions when output is small
  30. 27. Compact small files with OPTIMIZE
  31. 28. Use Z-Order only for a real access pattern
  32. 29. Understand V-Order in Fabric
  33. 30. Inspect Fabric optimization settings before changing them
  34. 31. Use VACUUM for storage cleanup, not query tuning
  35. 32. Use VACUUM LITE only on supported Fabric runtimes
  36. 33. Do not reduce VACUUM retention casually
  37. 34. Read an earlier Delta version for investigation
  38. 35. Inspect Delta history
  39. 36. Compare counts before and after writes
  40. 37. Reconcile business keys after an upsert
  41. 38. Store execution metadata for incremental loads
  42. 39. Avoid reading and rewriting the entire target for a small incremental change
  43. 40. Keep raw ingestion append-only when possible
  44. What the result means
  45. What to look for
  46. Target row count grows on every rerun
  47. MERGE touches much more data than expected
  48. Concurrent jobs fail during writes
  49. Table has many small files
  50. Reads slow down after many updates/merges
  51. Old table versions stop working
  52. Production notes
  53. Delta gives ACID table transactions, not arbitrary business constraints
  54. Fabric Low Shuffle Merge is already enabled by default
  55. Concurrent writes require layout-aware design
  56. OPTIMIZE and VACUUM solve different problems
  57. VACUUM affects recoverability
  58. V-Order defaults have changed over time
  59. Keep table maintenance out of peak write windows when practical
  60. Partitioning is not mandatory
  61. Common mistakes
  62. 1. Using append for a replayable mutable feed
  63. 2. Using overwrite for every rerun
  64. 3. Using whenMatchedUpdateAll() without thinking about schema
  65. 4. Merging duplicate source keys
  66. 5. Partitioning by a high-cardinality identifier
  67. 6. Calling repartition(1) before every write
  68. 7. Running VACUUM to make queries faster
  69. 8. Lowering VACUUM retention without understanding time travel
  70. 9. Treating a successful Delta transaction as proof of correct metrics
  71. 10. Retrying every write exception
  72. Next step
  73. Related deep dives

Problem

Microsoft Fabric Lakehouse tables use Delta Lake, which gives Spark workloads transactional table semantics on top of Parquet files in OneLake.

The syntax for reading and writing Delta tables is simple.

The production questions are harder:

  • Should this load append, overwrite, or merge?
  • Is the operation idempotent?
  • Will concurrent writers touch the same files?
  • Will schema changes be accepted or rejected?
  • Is partitioning helping or creating small files?
  • Are repeated updates creating file fragmentation?
  • When should you run OPTIMIZE or VACUUM?
  • How do you avoid rewriting far more data than necessary?

The goal is to choose the write pattern from the data contract, not from whichever command is easiest to rerun.

Quick answer

Use this decision flow:

New immutable rows only
→ APPEND

Rebuild the whole table intentionally
→ OVERWRITE

Insert + update existing business keys
→ MERGE

Replace only a known slice/partition
→ selective overwrite / partition-aware pattern

Many concurrent producers
→ append to staging, then reconcile with controlled MERGE

Read a Lakehouse Delta table:

df = spark.read.table("sales")

or:

df = (
    spark.read
    .format("delta")
    .load("Tables/sales")
)

Append:

(
    df.write
    .format("delta")
    .mode("append")
    .saveAsTable("sales")
)

Upsert:

from delta.tables import DeltaTable

target = DeltaTable.forName(
    spark,
    "sales",
)

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Do not use full-table overwrite as a default rerun strategy.

Code / commands

1. Read a managed Delta table by table name

sales = spark.read.table(
    "sales"
)

Equivalent Spark SQL:

sales = spark.sql(
    "SELECT * FROM sales"
)

Use table names when the table is registered in the Lakehouse metastore and the notebook is attached to the intended Lakehouse.


2. Read Delta by path

sales = (
    spark.read
    .format("delta")
    .load("Tables/sales")
)

Use path-based reads when:

  • the path itself is part of the design
  • you are working with an explicit OneLake location
  • you do not want to depend on the current default Lakehouse table namespace

For production code, avoid mixing table-name and path semantics casually.

Choose a consistent pattern for the workload.


3. Read only the columns you need

sales = (
    spark.read.table("sales")
    .select(
        "sale_id",
        "merchant_id",
        "sale_date",
        "amount",
    )
)

Column pruning reduces the amount of data carried through downstream transformations.

This matters on wide Delta tables.


4. Filter early

from pyspark.sql import functions as F

sales = (
    spark.read.table("sales")
    .filter(
        F.col("sale_date") >= "2026-10-01"
    )
    .select(
        "sale_id",
        "merchant_id",
        "sale_date",
        "amount",
    )
)

Filtering before joins and aggregations can reduce:

  • scan volume
  • shuffle volume
  • memory pressure
  • output size

Whether every predicate can be pushed down depends on the table layout and expression.


5. Append new immutable rows

(
    new_sales.write
    .format("delta")
    .mode("append")
    .saveAsTable("sales")
)

Use append when rows are genuinely new and existing rows should not be changed.

Typical examples:

  • immutable event ingestion
  • raw landing data
  • append-only audit history

Append is not automatically idempotent.

If the same batch can be replayed, duplicate prevention must be designed separately.


6. Add an ingestion batch identifier

For replay detection:

from pyspark.sql import functions as F

new_sales = (
    new_sales
    .withColumn(
        "ingestion_batch_id",
        F.lit("2026-10-04T1700Z"),
    )
)

This can help identify:

  • duplicate batch loads
  • reruns
  • partial processing
  • lineage

A batch identifier does not prevent duplicates by itself.

It gives you evidence to enforce or audit idempotency.


7. Overwrite a full table only when full replacement is intentional

(
    rebuilt_table.write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("sales_summary")
)

Use this for:

  • small derived tables
  • full deterministic rebuilds
  • tables whose contract is explicitly replace-all

Do not use overwrite simply because it is easier than implementing incremental logic.

Full overwrite may:

  • rewrite large amounts of data
  • increase runtime
  • create more obsolete files
  • increase conflict risk
  • affect concurrent readers/writers depending on the operation

8. Overwrite schema only when the schema change is deliberate

(
    rebuilt_table.write
    .format("delta")
    .mode("overwrite")
    .option(
        "overwriteSchema",
        "true",
    )
    .saveAsTable("sales_summary")
)

Use overwriteSchema only when replacing the table schema is part of the migration or rebuild.

Do not enable it globally.

Schema changes should be intentional and validated against downstream consumers.


9. Use mergeSchema carefully for additive schema evolution

(
    new_data.write
    .format("delta")
    .mode("append")
    .option(
        "mergeSchema",
        "true",
    )
    .saveAsTable("sales")
)

This can allow compatible new columns to be added.

Do not use schema evolution to hide upstream schema drift.

Before enabling it in production, decide:

Which columns are allowed to appear?
Who owns schema changes?
How will downstream consumers be notified?

10. Upsert with Delta MERGE

from delta.tables import DeltaTable

target = DeltaTable.forName(
    spark,
    "sales",
)

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Use MERGE when the business key can already exist and incoming data may:

  • update existing rows
  • insert new rows

The merge condition must represent the true business key.


11. Prefer explicit update mappings for stable production contracts

Instead of:

.whenMatchedUpdateAll()

use explicit mappings when schema control matters:

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdate(
        set={
            "merchant_id": "s.merchant_id",
            "sale_date": "s.sale_date",
            "amount": "s.amount",
            "updated_at": "s.updated_at",
        }
    )
    .whenNotMatchedInsert(
        values={
            "sale_id": "s.sale_id",
            "merchant_id": "s.merchant_id",
            "sale_date": "s.sale_date",
            "amount": "s.amount",
            "updated_at": "s.updated_at",
        }
    )
    .execute()
)

Explicit mappings reduce accidental coupling between source and target schema.


12. Deduplicate the source before MERGE

If the source contains multiple rows for the same target key, decide which one should win.

Example:

from pyspark.sql import Window
from pyspark.sql import functions as F

w = (
    Window
    .partitionBy("sale_id")
    .orderBy(
        F.col("updated_at").desc(),
        F.col("source_sequence").desc(),
    )
)

source_deduped = (
    source
    .withColumn(
        "_rn",
        F.row_number().over(w),
    )
    .filter(
        F.col("_rn") == 1
    )
    .drop("_rn")
)

Then merge source_deduped.

Do not rely on arbitrary source row order.


13. Validate source uniqueness before a merge

duplicate_keys = (
    source
    .groupBy("sale_id")
    .count()
    .filter(
        F.col("count") > 1
    )
)

duplicate_keys.show(
    50,
    truncate=False,
)

If the source should be unique and this returns rows, stop and investigate.

Duplicate source keys can make merge behavior ambiguous or fail depending on the match pattern.


14. Use a compound business key when one column is not enough

merge_condition = """
    t.member_id = s.member_id
    AND t.merchant_id = s.merchant_id
    AND t.site_id = s.site_id
"""

Then:

(
    target.alias("t")
    .merge(
        source.alias("s"),
        merge_condition,
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Do not simplify a real composite key merely to make merge code shorter.


15. Update only when the incoming row is newer

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedUpdateAll(
        condition="""
            s.updated_at > t.updated_at
        """
    )
    .whenNotMatchedInsertAll()
    .execute()
)

This protects against an older replay overwriting newer state.

Use this only when updated_at has reliable ordering semantics.


16. Insert only new keys

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenNotMatchedInsertAll()
    .execute()
)

This pattern can make append-like ingestion idempotent by business key.

It is more expensive than blind append because the target must participate in matching.

Use it when duplicate prevention is worth that cost.


17. Delete via MERGE only with a deliberate source contract

Example:

(
    target.alias("t")
    .merge(
        source.alias("s"),
        "t.sale_id = s.sale_id",
    )
    .whenMatchedDelete(
        condition="s.is_deleted = true"
    )
    .whenMatchedUpdateAll(
        condition="s.is_deleted = false"
    )
    .whenNotMatchedInsertAll(
        condition="s.is_deleted = false"
    )
    .execute()
)

Use source-driven deletion only when the delete flag has trustworthy business semantics.

Do not infer deletion merely because a row is absent from an incremental source batch.


18. Use SQL MERGE when SQL is clearer

MERGE INTO sales AS t
USING sales_stage AS s
ON t.sale_id = s.sale_id

WHEN MATCHED
    AND s.updated_at > t.updated_at
THEN UPDATE SET
    t.merchant_id = s.merchant_id,
    t.sale_date = s.sale_date,
    t.amount = s.amount,
    t.updated_at = s.updated_at

WHEN NOT MATCHED
THEN INSERT
(
    sale_id,
    merchant_id,
    sale_date,
    amount,
    updated_at
)
VALUES
(
    s.sale_id,
    s.merchant_id,
    s.sale_date,
    s.amount,
    s.updated_at
);

Spark SQL and the Delta API both work well.

Choose the form that makes the transformation easiest to review.


19. Use staging + merge for concurrent ingestion

A safer high-concurrency architecture is:

    • Producer A
    • Producer B
    • Producer C
  1. append
    Staging Delta
  2. Controlled MERGE
  3. Target Delta
Staging and merge for concurrent ingestion

Append-only staging reduces conflict between independent producers.

A controlled merge then performs the conflict-prone target reconciliation.

This is especially useful when multiple notebook or pipeline activities can arrive at the same time.


20. Design retry logic for concurrent Delta conflicts

Delta provides transactional commits, so a failed transaction does not leave a partially committed table state.

For workloads where logical write conflicts are expected, retry only the known concurrency failure cases.

Conceptual pattern:

import time

max_retries = 3

for attempt in range(max_retries):
    try:
        run_merge()
        break
    except Exception as exc:
        if not is_expected_delta_conflict(exc):
            raise

        if attempt == max_retries - 1:
            raise

        time.sleep(2 ** attempt)

Do not catch every exception and retry blindly.

Retry:

  • transient/conflict conditions you understand

Do not automatically retry:

  • bad schema
  • malformed source data
  • invalid merge logic
  • permission failures

21. Understand Fabric Low Shuffle Merge

Fabric Spark includes Low Shuffle Merge optimization for Delta MERGE.

It is enabled by default in Fabric Spark runtimes.

Check the current session setting:

print(
    spark.conf.get(
        "spark.microsoft.delta.merge.lowShuffle.enabled"
    )
)

Set explicitly if needed for controlled testing:

spark.conf.set(
    "spark.microsoft.delta.merge.lowShuffle.enabled",
    True,
)

Do not treat this optimization as a substitute for:

  • selective merge conditions
  • correct business keys
  • good file layout
  • reducing source volume

22. Partition only on useful low/moderate-cardinality dimensions

Example:

(
    sales.write
    .format("delta")
    .mode("append")
    .partitionBy(
        "sale_year",
        "sale_month",
    )
    .saveAsTable("sales")
)

Good partition candidates often align with:

  • common pruning filters
  • ingestion boundaries
  • writer-isolation boundaries

Bad candidates commonly include:

  • transaction IDs
  • exact timestamps
  • customer IDs with millions of values

High-cardinality partitioning creates excessive directories/files.


23. Do not over-partition small tables

A table does not need physical partitioning merely because it has a date column.

For small or moderate tables, unpartitioned Delta may be simpler and perform well.

Partitioning should solve a concrete problem such as:

  • data pruning
  • write isolation
  • lifecycle management

24. Inspect partition distribution before changing layout

(
    sales
    .groupBy(
        "sale_year",
        "sale_month",
    )
    .count()
    .orderBy(
        F.desc("count")
    )
    .show(
        100,
        truncate=False,
    )
)

Use actual data distribution to evaluate whether proposed partitions would be balanced and useful.


25. Avoid repartition(1) before Delta writes

Risky:

(
    df.repartition(1)
    .write
    .format("delta")
    .mode("append")
    .saveAsTable("sales")
)

This creates a single-partition write bottleneck.

Delta tables are designed to contain multiple Parquet files.

If a downstream process requires one export file, solve that as a separate export requirement.


26. Reduce excessive write partitions when output is small

If the final result is small but still has many Spark partitions:

smaller = df.coalesce(20)

Then write:

(
    smaller.write
    .format("delta")
    .mode("append")
    .saveAsTable("sales_summary")
)

Do this only after measuring.

Too few partitions can also create large/slow tasks.


27. Compact small files with OPTIMIZE

Spark SQL:

OPTIMIZE sales;

Use table maintenance when repeated writes/merges have created many small files and read performance is affected.

OPTIMIZE rewrites data files.

Schedule it intentionally rather than after every write.


28. Use Z-Order only for a real access pattern

Example:

OPTIMIZE sales
ZORDER BY
(
    merchant_id,
    sale_date
);

Use Z-Order when queries repeatedly filter on selected columns and the table is large enough for file skipping/layout improvements to matter.

Do not Z-Order every column.

Maintenance has compute and write cost.


29. Understand V-Order in Fabric

V-Order is a Fabric write-time/file-layout optimization for Parquet.

It can improve reads across Fabric engines, especially for read-heavy analytical workloads, at the cost of additional write work.

Current Fabric behavior depends on workspace/runtime configuration; newer workspaces do not universally have V-Order enabled by default.

Check your actual workload and runtime rather than assuming it is on.

Use V-Order when read performance across Fabric consumers justifies the write tradeoff.


30. Inspect Fabric optimization settings before changing them

Examples:

keys = [
    "spark.sql.parquet.vorder.enabled",
    "spark.sql.parquet.vorder.default",
    "spark.databricks.delta.optimizeWrite.enabled",
]

for key in keys:
    try:
        print(
            key,
            spark.conf.get(key),
        )
    except Exception:
        print(
            key,
            "<not available>",
        )

Configuration names/availability can depend on Fabric runtime.

Do not copy old tuning recipes without checking the active runtime.


31. Use VACUUM for storage cleanup, not query tuning

Standard:

VACUUM sales;

Explicit retention:

VACUUM sales RETAIN 168 HOURS;

VACUUM permanently removes old unreferenced data files that are older than the retention threshold.

It is primarily a storage cleanup operation.

It is not the same as OPTIMIZE.


32. Use VACUUM LITE only on supported Fabric runtimes

Fabric Spark runtime 2.0 / Delta 4.1 and later supports VACUUM LITE.

Example:

VACUUM sales LITE RETAIN 168 HOURS;

or:

from delta.tables import DeltaTable

table = DeltaTable.forName(
    spark,
    "sales",
)

table.vacuum(
    168,
    mode="LITE",
)

VACUUM LITE uses Delta-log information instead of a full directory listing.

If sufficient Delta-log history is not available, it can fail and require a standard/full vacuum.

Verify runtime support before using it.


33. Do not reduce VACUUM retention casually

Short retention can remove files needed for:

  • time travel
  • long-running readers
  • operational recovery
  • rollback investigation

Do not disable retention safety checks in routine production code.

If shorter retention is truly required, treat it as an operational decision with known consequences.


34. Read an earlier Delta version for investigation

Version-based time travel:

previous = (
    spark.read
    .format("delta")
    .option(
        "versionAsOf",
        42,
    )
    .table("sales")
)

Timestamp-based:

previous = (
    spark.read
    .format("delta")
    .option(
        "timestampAsOf",
        "2026-10-03T12:00:00Z",
    )
    .table("sales")
)

Use time travel for:

  • debugging
  • comparison
  • recovery analysis

Availability depends on retained transaction history and data files.

VACUUM can permanently remove files needed by older versions.


35. Inspect Delta history

DESCRIBE HISTORY sales;

Use history to investigate:

  • writes
  • merges
  • overwrites
  • maintenance operations
  • timestamps
  • operation metadata

This is often the first place to look when a table changed unexpectedly.


36. Compare counts before and after writes

Before:

source_count = source.count()

After:

target_count = (
    spark.table("sales")
    .count()
)

Raw row count alone rarely proves correctness, but it is a useful reconciliation signal.

For production loads, also validate:

  • distinct business keys
  • null counts
  • batch IDs
  • aggregate amounts
  • affected date range

37. Reconcile business keys after an upsert

duplicate_target_keys = (
    spark.table("sales")
    .groupBy("sale_id")
    .count()
    .filter(
        F.col("count") > 1
    )
)

duplicate_target_keys.show(
    50,
    truncate=False,
)

If the target contract says sale_id is unique, duplicate rows are a correctness problem regardless of whether the Delta transaction itself succeeded.

Delta provides file/table transactionality, not automatic business-key uniqueness constraints for arbitrary Spark writes.


38. Store execution metadata for incremental loads

Example columns:

output = (
    source
    .withColumn(
        "pipeline_run_id",
        F.lit(run_id),
    )
    .withColumn(
        "ingested_at_utc",
        F.current_timestamp(),
    )
)

This improves:

  • traceability
  • duplicate investigation
  • replay analysis
  • rollback decisions

Avoid putting secrets or sensitive operational payloads in metadata columns.


39. Avoid reading and rewriting the entire target for a small incremental change

Poor pattern:

target = spark.table("sales")
combined = target.unionByName(source)
# dedupe everything
# overwrite whole table

Better:

small incremental source
→ dedupe source
→ MERGE into target

Use targeted incremental operations where practical.

Full-table rewrites create unnecessary:

  • I/O
  • shuffle
  • stale files
  • maintenance work
  • concurrency exposure

40. Keep raw ingestion append-only when possible

A robust architecture is:

Bronze
  append-only source events/files

Silver
  MERGE / dedupe / correction logic

Gold
  curated serving tables

This keeps source history available while allowing controlled stateful transformations downstream.

Do not repeatedly mutate raw source-aligned tables unless the source contract genuinely requires it.

What the result means

Pattern Best fit
append New immutable rows
overwrite Intentional full rebuild
MERGE Upsert / incremental reconciliation
staging + MERGE Concurrent ingestion into shared target
partitioning Pruning, lifecycle, or writer isolation
OPTIMIZE Compact/reorganize files for read efficiency
Z-Order Improve skipping for repeated selective filters
V-Order Fabric Parquet layout optimization for read-heavy consumers
VACUUM Permanently remove old unreferenced files
time travel Investigate/read historical table versions
DESCRIBE HISTORY Audit Delta operations

The important distinction is:

transactionally successful write
!=
business-correct write

You still need to validate:

  • business keys
  • row counts
  • duplicates
  • aggregates
  • load window

What to look for

Target row count grows on every rerun

Your load is probably not idempotent.

Check:

  • append replay
  • missing business-key matching
  • unstable batch identifiers

MERGE touches much more data than expected

Check:

  • merge condition selectivity
  • partition pruning
  • data layout
  • source size
  • target file organization

A correct but broad merge can still be expensive.

Concurrent jobs fail during writes

Check whether writers:

  • modify overlapping target files/partitions
  • run conflicting merges
  • perform maintenance during ingestion

Consider:

  • staging append
  • writer isolation
  • serialized reconciliation
  • targeted retries

Table has many small files

Likely causes:

  • frequent small appends
  • overly many write partitions
  • low-shuffle merge side effects
  • micro-batch patterns

Measure the impact, then consider table maintenance.

Reads slow down after many updates/merges

Inspect:

  • file count
  • file sizes
  • layout
  • deletion vectors/runtime behavior
  • need for OPTIMIZE
  • frequently filtered columns

Old table versions stop working

Check whether VACUUM removed required historical files.

Delta log metadata alone cannot restore files that were permanently deleted.

Production notes

Delta gives ACID table transactions, not arbitrary business constraints

Delta protects table commits from partial writes.

It does not automatically enforce:

  • uniqueness
  • referential integrity
  • one-row-per-business-key semantics

Validate those yourself.

Fabric Low Shuffle Merge is already enabled by default

Do not spend time “optimizing” by enabling it unless you are diagnosing a configuration difference or testing behavior.

Focus first on source size, merge keys, touched files, and skew.

Concurrent writes require layout-aware design

Delta uses optimistic concurrency control.

Independent appends are less conflict-prone than overlapping updates/merges.

For high concurrency:

append staging
→ reconcile centrally

is often easier to operate.

OPTIMIZE and VACUUM solve different problems

OPTIMIZE
→ rewrites/compacts/reorganizes current files

VACUUM
→ removes old unreferenced files

Do not use one as a substitute for the other.

VACUUM affects recoverability

Once obsolete files are deleted, historical versions requiring those files are no longer readable.

Retention is an operational recovery choice, not just a storage-cost setting.

V-Order defaults have changed over time

Fabric workspace/runtime defaults have changed.

Do not assume guidance written for older Fabric workspaces matches a current runtime.

Inspect actual configuration.

Keep table maintenance out of peak write windows when practical

OPTIMIZE, VACUUM, and heavy merges consume resources and can interact with concurrent write workloads.

Schedule maintenance when it minimizes operational contention.

Partitioning is not mandatory

Do not copy Hive-style partitioning patterns automatically.

Delta can perform well without physical partitioning for many workloads.

Partition only when the data size and access/write pattern justify it.

Common mistakes

1. Using append for a replayable mutable feed

A rerun can create duplicates.

Use a durable idempotency strategy.

2. Using overwrite for every rerun

This turns small incremental changes into expensive full-table rewrites.

3. Using whenMatchedUpdateAll() without thinking about schema

Convenient source columns can unintentionally become part of the target contract.

Use explicit mappings when stability matters.

4. Merging duplicate source keys

The incoming source must have deterministic business-key semantics.

Deduplicate intentionally before the merge.

5. Partitioning by a high-cardinality identifier

This creates too many tiny directories/files and poor operational behavior.

6. Calling repartition(1) before every write

One output partition is a bottleneck, not an optimization.

7. Running VACUUM to make queries faster

VACUUM is mainly storage cleanup.

Use OPTIMIZE/layout tuning for read performance.

8. Lowering VACUUM retention without understanding time travel

Deleted files cannot be used for historical versions afterward.

9. Treating a successful Delta transaction as proof of correct metrics

A transaction can be perfectly valid while the business key or source logic is wrong.

Reconcile the data.

10. Retrying every write exception

Only retry errors that are genuinely transient/conflict-related.

Bad schema or bad merge logic should fail visibly.

Next step

Use the write pattern to choose the next action.

  • New immutable events only → append and add replay/batch controls.
  • Reruns create duplicates → implement key-based idempotency or MERGE.
  • Existing rows can change → use MERGE with a true business key.
  • Source contains duplicate keys → deduplicate with deterministic ordering before writing.
  • Concurrent writers conflict → append to staging and reconcile with controlled merge.
  • Merge scans too much target data → improve merge selectivity, partition/layout strategy, and source filtering.
  • Table has many small files → inspect write partitioning, then consider OPTIMIZE.
  • Storage keeps growing after updates/merges → review safe VACUUM retention.
  • Old versions are needed for recovery → protect retention before vacuuming.
  • Reads remain slow after correct write patterns → move to Fabric Notebook Performance and table-layout analysis.

← All cheat sheets