Your first lakehouse table — create, query, measure, compact

At a glance

Learning objectives

• Create and query a first table on a real table format using the official docs
• Read a table's file health from the metadata table with one SQL query
• Run a first compaction and prove it with before/after numbers

What you need

• A lakehouse with Delta or Iceberg available (Spark, Trino, or a warehouse that reads the format)
• Permission to create one scratch table
• [object Object]

Steps

1. Create and query your first Delta table following the official docs — the definitive source for ACID transactions on object storage, time travel, OPTIMIZE and Z-ORDER clustering. (from ref-delta-docs )
2. Prefer Iceberg? The spec and docs cover the table format, snapshots and manifests, hidden partitioning, and maintenance procedures like compaction and snapshot expiry — read them before writing the first table. (from ref-iceberg-docs )
3. Before adding data, learn the one health query: SELECT count(*), avg(file_size_in_bytes)/1e6 FROM db.tbl.files; (from runbook-small-files-explosion )
4. Write like production does — streaming or high-frequency writes without compaction, over-partitioning (partition per hour, per user), or many small appends from a micro-batch job — and watch the health query report the damage. (from runbook-small-files-explosion )
5. Interpret the numbers with the skill's rule: avg_mb < 32 and a growing count means compact. (from skill-iceberg-maintenance )
6. Run your first maintenance call: OPTIMIZE db.tbl ZORDER BY (user_id); on Delta, or CALL catalog.system.rewrite_data_files('db.tbl'); on Iceberg. (from runbook-small-files-explosion )
7. Close the loop: compare numFiles and query bytes scanned before and after. If scanning didn't drop, the Z-ORDER was on the wrong columns. (from skill-delta-optimizer )

Check yourself

• The health query runs on your table and returns a count and an average file size you can write down.
• After OPTIMIZE or rewrite_data_files, numFiles drops and the next query reports fewer bytes scanned.
• You can state the compact trigger — avg_mb < 32 and a growing count — without looking it up.

Worked example

SELECT count(*), avg(file_size_in_bytes)/1e6 FROM db.tbl.files;

OPTIMIZE db.tbl ZORDER BY (user_id);  -- Delta
CALL catalog.system.rewrite_data_files('db.tbl');  -- Iceberg

Compare `numFiles` and query `bytes scanned` before and after. If
scanning didn't drop, the Z-ORDER was on the wrong columns.

Sourced from

• Delta Lake Documentation
• Apache Iceberg Documentation
• Small files explosion
• Iceberg Table Maintenance
• Delta Optimization Advisor

Security scan

Last sweep 2026-10-24: 0 finding(s), 0 marked as examples.

Common gotchas

• The compact trigger is avg_mb < 32 and a growing count — measure, do not guess.
• If bytes scanned did not drop after Z-ORDER, the Z-ORDER was on the wrong columns.
• One scratch table is the whole exercise — do not point OPTIMIZE at a shared table.

Production considerations

• Keep the health query standing — SELECT count(*), avg(file_size_in_bytes)/1e6 FROM db.tbl.files is the check on every table you own.
• Install the Iceberg maintenance skill once and reuse its plan on every table: npx de-agent-platform add iceberg-table-maintenance.

Diagram

first lakehouse table
   |
   |- create + query (Delta docs | Iceberg spec)
   |
   |- SELECT count(*), avg(file_size_in_bytes)/1e6 FROM db.tbl.files
   |
   |- avg_mb < 32 and growing --> compact
   |
   |- OPTIMIZE (Delta) | rewrite_data_files (Iceberg)
   |
   --> numFiles + bytes scanned before/after

Registry stats