Delta Lake Optimalisatie in Microsoft Fabric: OPTIMIZE, Z-ORDER en VACUUM Gids

Delta tables zijn geen magie. Ze worden na verloop van tijd trager als je ze niet onderhoudt.

Elke schrijfactie creëert nieuwe bestanden. Updates creëren meer bestanden. Verwijderingen creëren zelfs nog meer bestanden. Uiteindelijk heb je duizenden kleine bestanden en queries die vroeger 10 seconden duurden, duren nu 2 minuten.

Ik had een table die in 3 maanden van 50 bestanden naar 4000 bestanden ging. De query performance daalde met een factor 10. Ik voerde optimize en z-order uit, en binnen 15 minuten was alles weer normaal.

Delta onderhoud is niet optioneel. Het is vereist. Dit is wat je moet weten.

Het kleine bestanden probleem

Delta tables slaan data op als Parquet bestanden. Elke write operatie creëert nieuwe bestanden.

# first write: creates 5 files
df.write.format("delta").mode("overwrite").saveAsTable("sales")

# append data: adds 3 more files
df2.write.format("delta").mode("append").saveAsTable("sales")

# another append: adds 2 more files
df3.write.format("delta").mode("append").saveAsTable("sales")

# now you have 10 files total

Bij frequente appends eindig je met honderden of duizenden kleine bestanden.

Waarom dit slecht is:

• Elk bestand vereist een aparte read operatie
• List operaties vertragen met meer bestanden
• Query planning overhead neemt toe
• Metadata wordt enorm
• Storage kosten zijn hoger (minimum block size)

Een echt voorbeeld: een table met 2000 bestanden versus een geoptimaliseerde table met 20 bestanden, dezelfde data. De versie met 2000 bestanden was 7 keer trager om te query'en.

Wat optimize doet

Het optimize-commando leest kleine bestanden en herschrijft ze tot grotere bestanden.

# optimize a table
spark.sql("OPTIMIZE sales")

# optimize with a filter (only optimize recent partitions)
spark.sql("OPTIMIZE sales WHERE date >= '2024-01-01'")

Wat er eigenlijk gebeurt:

1. Leest alle kleine bestanden in de table (of partition)
2. Combineert ze tot grotere bestanden (standaarddoel: 1GB per bestand)
3. Schrijft de nieuwe bestanden
4. Werkt de transaction log bij
5. Oude bestanden worden gemarkeerd voor verwijdering

Na optimize worden je 2000 kleine bestanden 20 grote bestanden. Queries zijn sneller, metadata is kleiner.

Wanneer optimize uit te voeren

Voer niet na elke write optimize uit. Dat is verspilling. Maar wacht ook niet tot de performance verschrikkelijk is.

Goede triggers:

Na bulk appends:

# load a bunch of data
df.write.format("delta").mode("append").saveAsTable("sales")

# optimize immediately
spark.sql("OPTIMIZE sales")

Op een schema:

# in a notebook run weekly
from datetime import datetime

# optimize tables older than 7 days
spark.sql("""
  OPTIMIZE sales 
  WHERE date < current_date() - interval 7 days
""")

Wanneer het aantal bestanden groeit:

# check file count
file_count = spark.sql("""
  DESCRIBE DETAIL sales
""").select("numFiles").collect()[0][0]

if file_count > 1000:
    spark.sql("OPTIMIZE sales")

Voor tables met frequente writes, optimize wekelijks. Voor tables met af en toe writes, optimize maandelijks of na grote loads.

Auto optimize

Fabric ondersteunt auto optimize. Het voert optimize automatisch uit tijdens writes.

# enable auto optimize for a table
spark.sql("""
  ALTER TABLE sales SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true'
  )
""")

Wat dit doet:

• optimizeWrite: schrijft minder, grotere bestanden tijdens de write operatie
• autoCompact: compacteert automatisch kleine bestanden na writes

Klinkt geweldig, maar heeft afwegingen:

Voordelen:

• Geen handmatige optimize nodig
• Tables blijven automatisch performant
• Minder onderhoudswerk

Nadelen:

• Write operaties duren langer
• Gebruikt meer compute tijdens writes
• Niet zo geoptimaliseerd als handmatige optimize met z-order

Mijn aanbeveling: gebruik auto optimize voor tables met zeer frequente kleine writes. Gebruik handmatige optimize met z-order voor tables met batch loads.

Z-order clustering

Dit is het geheime wapen. Z-order organiseert data fysiek binnen bestanden op basis van kolomwaarden.

# optimize with z-order on frequently filtered columns
spark.sql("OPTIMIZE sales ZORDER BY (customer_id, product_category)")

Z-order maakt gefilterde queries veel sneller.

Zonder z-order:

• Query filtert op customer_id
• Spark leest alle bestanden
• Filtert data na het lezen
• Traag voor grote tables

Met z-order op customer_id:

• Query filtert op customer_id
• Data voor dezelfde klant is geclusterd
• Spark slaat bestanden over die die klant niet bevatten
• Veel minder data gelezen

Echte cijfers: query filtering op customer_id ging van 45 seconden naar 6 seconden na z-order. Dezelfde query, dezelfde data, 7,5x sneller.

Z-order kolommen kiezen

Kies kolommen waarop je het vaakst filtert. Meestal:

Goede z-order kandidaten:

• ID kolommen (customer_id, order_id)
• Categorie kolommen (product_category, region)
• Status kolommen (order_status, user_type)

Slechte z-order kandidaten:

• Timestamp kolommen (te hoge cardinality)
• Kolommen waarop je nooit filtert
• Kolommen met zeer weinig onderscheidende waarden

Je kunt z-order uitvoeren op meerdere kolommen (tot 4-5 is redelijk):

# z-order on multiple columns
spark.sql("""
  OPTIMIZE sales 
  ZORDER BY (region, product_category, customer_id)
""")

De volgorde is belangrijk. Zet de meest gefilterde kolom eerst.

Z-order kosten

Z-order is duurder dan een reguliere optimize. Het leest en herschrijft alle data, gesorteerd op de z-order kolommen.

Voor een 100GB table:

• Reguliere optimize: 2-5 minuten
• Optimize met z-order: 10-20 minuten

Het is het waard als je queries filteren op die kolommen. Het is het niet waard voor tables die je alleen volledig scant.

Vacuum: oude bestanden opschonen

Optimize creëert nieuwe bestanden, maar verwijdert oude niet onmiddellijk. Ze worden bewaard voor time travel.

Vacuum verwijdert oude bestanden die niet langer nodig zijn.

# delete files older than retention period (default 7 days)
spark.sql("VACUUM sales")

# vacuum with custom retention
spark.sql("VACUUM sales RETAIN 168 HOURS")  # 7 days in hours

Wat vacuum doet:

1. Lijst alle bestanden in de table
2. Controleert de transaction log welke bestanden actueel zijn
3. Verwijdert bestanden die niet worden gerefereerd door versies binnen de retention period

Na vacuum zijn je oude versies verdwenen. Time travel werkt alleen terug tot de retention period.

Vacuum retention period

De standaard retention is 7 dagen. Je kunt geen bestanden van minder dan 7 dagen oud vacuum'en (veiligheidsfunctie).

# this errors unless you override safety
spark.sql("VACUUM sales RETAIN 0 HOURS")

# need to disable check (dangerous)
spark.conf.set("spark.databricks.delta.retentionDurationCheck.enabled", "false")
spark.sql("VACUUM sales RETAIN 0 HOURS")

Vervang de veiligheidsfunctie niet tenzij je precies weet wat je doet. Als je te agressief vacuum't, kun je concurrerende queries die oude bestandsversies lezen beschadigen.

Richtlijnen voor de retention period:

• 7 dagen: veilige standaard
• 30 dagen: als je langere time travel nodig hebt
• 90 dagen: voor compliance of audit vereisten
• 24 uur: alleen als je zeker weet dat geen enkele query langer dan dat duurt

Storage is goedkoop. Houd de retention op 7 dagen, tenzij je een specifieke reden hebt om dit te wijzigen.

Wanneer vacuum uit te voeren

Vacuum periodiek om storage terug te winnen. Het is niet nodig om na elke optimize te vacuum'en.

# good pattern: optimize weekly, vacuum monthly
from datetime import datetime

# weekly maintenance notebook
if datetime.now().day <= 7:
    # first week of month: vacuum
    spark.sql("VACUUM sales RETAIN 168 HOURS")
else:
    # other weeks: just optimize
    spark.sql("OPTIMIZE sales")

Vacuum is minder urgent dan optimize. Optimize helpt de performance. Vacuum helpt de storage kosten.

Partitioning strategie

Hoe je tables partitioneert, beïnvloedt de optimalisatiestrategie.

# partition by date
df.write.format("delta") \
    .partitionBy("date") \
    .saveAsTable("sales")

Met gepartitioneerde tables kun je specifieke partitions optimaliseren:

# only optimize recent partition
spark.sql("OPTIMIZE sales WHERE date = '2024-01-15'")

# optimize last 7 days
spark.sql("OPTIMIZE sales WHERE date >= current_date() - interval 7 days")

Dit is veel sneller dan het optimaliseren van de gehele table.

Over-partitioning probleem

Te veel partitions is erger dan geen partitions.

Slecht voorbeeld:

# partitioned by customer_id (millions of values)
df.write.format("delta") \
    .partitionBy("customer_id") \
    .saveAsTable("sales")

Dit creëert miljoenen directories. Listing operaties worden traag. Queries die meerdere klanten scannen zijn trager dan niet-gepartitioneerde queries.

Goede partitioning:

• Date kolommen (dag, maand, jaar)
• Lage cardinality categorieën (10-1000 onderscheidende waarden)
• Kolommen waarop je in de meeste queries filtert

Niet partitioneren als:

• Table minder dan 10GB is
• Je geen duidelijke partition kolom hebt
• De meeste queries de volledige table toch scannen

Voor kleine tables voegt partitioning overhead toe zonder voordeel.

Belangrijke table properties

Delta tables hebben properties die het gedrag regelen.

# view current properties
spark.sql("SHOW TBLPROPERTIES sales")

# set useful properties
spark.sql("""
  ALTER TABLE sales SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true',
    'delta.targetFileSize' = '134217728',
    'delta.deletedFileRetentionDuration' = 'interval 7 days',
    'delta.logRetentionDuration' = 'interval 30 days'
  )
""")

Belangrijke properties:

delta.targetFileSize: doel bestandsgrootte na optimize (standaard 1GB)

• Verhoog voor zeer grote tables
• Verlaag voor tables met selectieve queries

delta.autoOptimize.optimizeWrite: schrijf minder, grotere bestanden

• Goed voor frequente appends
• Tragere writes, maar betere read performance

delta.autoOptimize.autoCompact: automatisch compact kleine bestanden

• Voert compaction uit na writes
• Goed voor streaming of micro-batch patronen

delta.deletedFileRetentionDuration: hoe lang oude bestanden te bewaren

• Standaard 7 dagen
• Verhoog als je langere time travel nodig hebt

delta.logRetentionDuration: hoe lang de transaction log te bewaren

• Standaard 30 dagen
• Bepaalt hoe ver terug time travel werkt

Verander deze niet tenzij je een reden hebt. De defaults werken voor de meeste gevallen.

Table gezondheid monitoren

Controleer regelmatig de statistieken van je table.

# get table details
spark.sql("DESCRIBE DETAIL sales").show()

# see version history
spark.sql("DESCRIBE HISTORY sales").show()

# check file count and size
details = spark.sql("DESCRIBE DETAIL sales").collect()[0]
print(f"Files: {details.numFiles}")
print(f"Size: {details.sizeInBytes / 1e9:.2f} GB")

Tekenen dat je table optimalisatie nodig heeft:

• Aantal bestanden boven de 1000
• Gemiddelde bestandsgrootte onder de 100MB
• Query tijden nemen toe na verloop van tijd
• Storage groeit sneller dan de data size

Stel monitoring in:

# weekly health check notebook
def check_table_health(table_name):
    details = spark.sql(f"DESCRIBE DETAIL {table_name}").collect()[0]
    
    num_files = details.numFiles
    size_gb = details.sizeInBytes / 1e9
    avg_file_size_mb = details.sizeInBytes / num_files / 1e6
    
    print(f"Table: {table_name}")
    print(f"Files: {num_files}")
    print(f"Size: {size_gb:.2f} GB")
    print(f"Avg file size: {avg_file_size_mb:.2f} MB")
    
    if num_files > 1000:
        print("WARNING: Too many files, consider optimize")
    if avg_file_size_mb < 100:
        print("WARNING: Files too small, consider optimize")

check_table_health("sales")

Gescheduleerd onderhoudspatroon

Voeg alles samen in een maintenance notebook.

# maintenance notebook run weekly
from datetime import datetime

tables_to_maintain = ["sales", "customers", "orders", "products"]

for table in tables_to_maintain:
    print(f"Maintaining {table}...")
    
    # check if table needs optimize
    details = spark.sql(f"DESCRIBE DETAIL {table}").collect()[0]
    num_files = details.numFiles
    
    if num_files > 500:
        print(f"  Optimizing ({num_files} files)...")
        
        # optimize with z-order on key columns
        if table == "sales":
            spark.sql(f"OPTIMIZE {table} ZORDER BY (customer_id, date)")
        elif table == "customers":
            spark.sql(f"OPTIMIZE {table} ZORDER BY (customer_id)")
        else:
            spark.sql(f"OPTIMIZE {table}")
    
    # vacuum monthly (first week)
    if datetime.now().day <= 7:
        print(f"  Vacuuming...")
        spark.sql(f"VACUUM {table} RETAIN 168 HOURS")

print("Maintenance complete")

Plan dit via een Fabric pipeline om wekelijks uit te voeren.

Performance voor en na

Echt voorbeeld van een productie table:

Voor optimalisatie:

• Bestanden: 3500
• Totale grootte: 45GB
• Gemiddelde bestandsgrootte: 13MB
• Query tijd (filter op customer_id): 38 seconden

Na optimize + z-order:

• Bestanden: 45
• Totale grootte: 45GB (zelfde data)
• Gemiddelde bestandsgrootte: 1GB
• Query tijd (zelfde query): 5 seconden

7,6x performance verbetering alleen al door bestandsorganisatie. Dezelfde data, dezelfde query, andere fysica.

Storage impact na vacuum:

• Voor vacuum: 67GB (oude en nieuwe bestanden)
• Na vacuum: 45GB (22GB teruggewonnen)

Bestandsorganisatie is belangrijker dan de meeste code optimalisaties.

Veelvoorkomende fouten

Fout 1: Nooit optimize uitvoeren

De ergste fout. Je tables worden elke maand trager en je vraagt je af waarom Fabric traag is. Het is niet Fabric, het is bestandsfragmentatie.

Fout 2: Te vaak optimaliseren

Optimize uitvoeren na elke kleine write verspilt compute. Batch je writes of gebruik in plaats daarvan auto optimize.

Fout 3: Te agressief vacuum'en

Retention instellen op 0 uur en je afvragen waarom queries willekeurig mislukken. Oude bestandsversies zijn nodig voor concurrerende lezers.

Fout 4: Z-order op verkeerde kolommen

Z-order op timestamp kolommen met miljoenen onderscheidende waarden. Helpt niet en duurt eeuwig. Kies kolommen met lage tot gemiddelde cardinality waarop je filtert.

Fout 5: Over-partitioning

Partitioneren op user_id als je miljoenen gebruikers hebt. Creëert een directory-nachtmerrie en vertraagt alles.

Fout 6: Onderhoud volledig negeren

Delta behandelen als een normale database die zichzelf onderhoudt. Dat doet het niet. Je moet optimize en vacuum uitvoeren, anders degradeert de performance.

Integratie met data pipelines

Optimize en vacuum passen natuurlijk in data pipelines.

# pipeline pattern
# step 1: load new data
df = extract_from_source()
df.write.format("delta").mode("append").saveAsTable("sales")

# step 2: optimize new partition
from datetime import date
today = date.today()
spark.sql(f"OPTIMIZE sales WHERE date = '{today}'")

# step 3: continue with downstream processing
transformed = spark.table("sales").filter(...)

Optimaliseer niet de hele table bij elke uitvoering. Optimaliseer alleen wat veranderd is.

# weekly full maintenance in separate pipeline
spark.sql("OPTIMIZE sales ZORDER BY (customer_id)")
spark.sql("VACUUM sales RETAIN 168 HOURS")

Scheid incrementele optimalisatie (dagelijks) van volledig onderhoud (wekelijks).

Fabric specifieke overwegingen

Fabric beheert Spark clusters anders dan Databricks.

Wat dit betekent voor onderhoud:

• Optimize/vacuum runs gebruiken je capacity units
• Jobs concurreren met andere Fabric workloads
• Plan onderhoud tijdens perioden met laag gebruik
• Overweeg je capacity SKU bij het plannen van onderhoudsvensters

Op F16 capacity duurt het optimaliseren van een 100GB table 10-15 minuten. Op F8 kan het 30-40 minuten duren. Budgetteer dienovereenkomstig.

Werken met lakehouses

In Fabric leven je Delta tables in Lakehouses.

Onderhoudscommando's werken hetzelfde:

# reference table in lakehouse
spark.sql("USE my_lakehouse")
spark.sql("OPTIMIZE sales")

# or use full path
spark.sql("OPTIMIZE my_lakehouse.sales")

Lakehouse bestanden staan in OneLake. Na vacuum worden de oude bestanden uit OneLake verwijderd en win je storage terug.

Laatste onderhoudschecklist

Voor elke Delta table:

Wekelijks:

• Controleer aantal bestanden
• Voer optimize uit als bestanden > 500
• Gebruik z-order bij optimize voor belangrijke tables

Maandelijks:

• Voer vacuum uit om storage terug te winnen
• Controleer table properties
• Controleer query performance trends

Na bulk loads:

• Altijd onmiddellijk optimize uitvoeren
• Overweeg z-order als table gefilterde queries ondersteunt

Voortdurend:

• Monitor het aantal bestanden in kritieke tables
• Volg de query performance in de loop van de tijd
• Pas de onderhoudsfrequentie aan op basis van write patronen

Werkelijke impact

Had een Lakehouse met 15 tables. Gedurende 6 maanden geen onderhoud uitgevoerd. Query tijden degradeerden 3-10x. Storage was 2x wat het zou moeten zijn.

Een onderhoudsbeurt uitgevoerd:

• Optimize + z-order op alle tables: 2 uur
• Alles vacuum'en: 30 minuten
• Queries weer op normale snelheid
• 200GB storage teruggewonnen

Totale kosten van onderhoud: één keer 2,5 uur capacity. Voortdurend voordeel: snellere queries en lagere storage kosten voor altijd.

Onderhoud is geen optionele overhead. Het zijn vereiste operaties voor Delta tables.

Meer leren

Delta onderhoud sluit aan bij algehele Spark optimalisatie. Bestandsorganisatie is een onderdeel van de fysica die queries snel maakt.

Als je nieuw bent met Fabric, begin dan eerst met de introductiegids voor Power BI developers. Leer vervolgens over Lakehouses vs Warehouses. Kom dan terug naar optimalisatie.

De concepten hier zijn van toepassing op elke Delta Lake implementatie. Werkt hetzelfde in Databricks, Fabric of standalone Delta. Je leert overdraagbare vaardigheden.

Delta tables zijn krachtig, maar ze zijn niet zelfonderhoudend. Voer regelmatig optimize uit, gebruik z-order voor gefilterde queries, en vacuum periodiek. Je tables blijven snel en je storage blijft schoon.

Bouw onderhoud vanaf dag één in je pipelines. Dat is veel gemakkelijker dan later performance problemen proberen op te lossen.