Cookbook

Working with Delta Tables

Delta tables give you ACID transactions, incremental upserts (deduplication), schema evolution, and time-travel directly on object storage—without provisioning or paying for an always-on database server.

← Back to Documentation Hub

1. What is a Delta Table — and When to Use It

A Delta table stores data as standard Apache Parquet files alongside a transaction log (_delta_log) that tracks every insert, update, delete, and schema change atomically. In Automat BI, DuckDB queries these tables directly with partition pruning and zero-copy reads.

Comparing Storage Options in Automat BI

Automat BI supports Delta tables, BigQuery, and ClickHouse natively, as well as customer-managed Snowflake (or traditional Postgres DWH setups). Choose the right engine based on workload shape:

Approach Best When Why Not / Trade-offs
Delta Table (Built-in) Incremental ingestion, append-heavy event logs, multi-year history (10M–500M+ rows), and zero-ops cost efficiency. Not designed for high-concurrency OLTP point-lookups or sub-100ms external SaaS app queries; requires periodic OPTIMIZE maintenance.
ClickHouse (Native) Sub-second interactive dashboards over massive raw event streams (1B+ rows) and high-concurrency analytical queries. Higher baseline compute/memory footprint; updates/deletes are heavier than Delta's key-based upserts.
BigQuery (Native) You already centralize enterprise data in GCP and need serverless petabyte-scale SQL federation. Per-query/slot billing and external cloud IAM management; overkill for self-contained operational BI pipelines.
Snowflake (Customer-managed) Existing enterprise Snowflake standard where governance and BI models already live. Requires separate Snowflake account, warehouse sizing, and credit management outside Automat BI.
Traditional Postgres DWH Small datasets (< 10M rows) requiring strict relational foreign keys or transactional row locking. Poor analytical compression, expensive storage scaling, painful ALTER TABLE locks, and slow full-table scans on wide analytical tables.

Rule of thumb: Start with Delta tables for 90% of incremental ingestion and reporting pipelines. Move to ClickHouse, BigQuery, or Snowflake when query concurrency or multi-terabyte scale demands dedicated distributed compute.


2. Bootstrapping a Delta Table

Before running incremental loads, bootstrap the table with a clupc, strongly-typed schema and a single dummy seed row. Defining explicit types upfront prevents VARCHAR vs. DATE/DOUBLE inference mismatches when the first real batch arrives.

Core Concepts: Partitioning, Deduping & Tombstoning

  • Partitioning (partition): Splits physical Parquet files into directory hierarchies (e.g., year=2026/month=11/). Queries filtering on partition columns skip irrelevant folders entirely. Pick coarse, low-cardinality columns ([year, month] or [year])—never high-cardinality keys like upc or invoice_id, which cause the "small file problem."
  • Deduplication / Upserts (key + version): When key and version are set, Automat BI merges incoming rows against existing records matching the composite key. If a duplicate key arrives, the row with the higher version (_ver, typically an epoch timestamp or monotonic sequence) wins.
  • Tombstoning (Soft Deletes): Object storage files are immutable, so hard-deleting single rows rewrites whole Parquet files. Instead, include a boolupc flag (such as _is_deleted or status = 'VOID') in your upstream source, upsert the updated _ver so the tombstone wins, and filter WHERE NOT _is_deleted in downstream views.

Bootstrap Pipeline Example

Run this once to initialize invoice_history.delta with explicit column types, composite deduplication keys, and [year, month] partitioning:

- name: ReadInvoices
  kind: transform
  query: >
    SELECT
      0::BIGINT AS _ver,
      '' AS item_name,
      '' AS upc,
      0::DOUBLE AS qty,
      '' AS uom,
      '' AS buyer_name,
      '' AS seller_name,
      '1970-01-01'::DATE AS trx_date,
      '' AS pos_name,
      '' AS buyer_gln,
      '' AS seller_gln,
      '' AS pos_gln,
      '1970' AS year,
      '01' AS month,
      '01' AS day,
      '' AS dow_no,
      '' AS dow_name;

- name: SinkInvoices
  kind: sink
  connection:
    kind: Deltatable
    locator: invoice_history.delta
    version: _ver
    key: [trx_date, upc, buyer_gln, pos_gln]
    partition: [year, month]

(Note: The 1970-01-01 seed row lives in its own isolated year=1970/month=01 partition, so it never pollutes production date ranges and can be filtered out with WHERE trx_date > DATE '1970-01-01'.)


3. Day-2 Operations

3.1 Evolving the Schema Safely in SQL

When adding a new column to an existing Delta table, always cast it explicitly and provide a typed fallback in your transform step so nulls or malformed source strings don't break schema validation:

SELECT
  _ver,
  item_name,
  upc,
  qty,
  /* Added doc_date field for reason Y 2026-09-17 */
  coalesce(try_cast(doc_date AS DATE), DATE '1970-01-01') AS doc_date,
  trx_date,
  buyer_gln,
  pos_gln,
  year,
  month
FROM ReadInvoices

Tip: Using 1900-01-01 (or 1899-12-30, the classic Excel/COM epoch) as your cast fallback keeps 1970-01-01 strictly reserved for the bootstrap seed partition—making it trivial to build a data-quality alert report (WHERE doc_date = DATE '1900-01-01' AND trx_date > DATE '1970-01-01') whenever an upstream source sends unparseable dates.

3.2 Write Modes & Schema Evolution Flags

Control how new batches interact with existing data and columns using data_mode and schema_mode on the Deltatable sink:

- name: SaveData
  kind: sink
  connection:
    kind: Deltatable
    locator: order_history.delta
    version: _ver
    key: [trx_date, upc, buyer_gln, pos_gln]
    partition: [year, month]
    data_mode: append      # error (default) | append | overwrite
    schema_mode: merge     # null (default) | merge | overwrite
Parameter Values Behavior
data_mode error (default) Fails if the target table already exists (prevents accidental overwrites).
append Adds new rows—or merges/deduplicates by key & version when configured.
overwrite Replaces existing table data with the incoming batch.
schema_mode null (default) Enforces strict schema matching; rejects batches with unexpected columns.
merge Automatically adds new columns (such as doc_date) to the Delta table schema while preserving existing columns.
overwrite Replaces the table schema entirely with the incoming batch's schema (use only with data_mode: overwrite).

Note: By default, Delta sinks use data_mode: error and schema_mode: null as safety guardrails against silent data loss or unintended schema drift:

  • data_mode (error | append | overwrite): Defaults to error so a misconfigured pipeline fails loudly instead of accidentally overwriting or duplicating an existing table. Switch to append for ongoing incremental loads/upserts, or overwrite for full-table refreshes.
  • schema_mode (null | merge | overwrite): Defaults to null (strict validation) to block upstream API changes or typos from polluting your table with unexpected columns. Set to merge when intentionally adding new fields (like doc_date), or overwrite when replacing the schema alongside data_mode: overwrite.

3.3 Compaction, Vacuuming & Maintenance (OPTIMIZE)

Frequent incremental runs create many small Parquet files and retain historical versions of updated files. Schedule a maintenance step (for example, weekly inside your maintenance job) to compact small files and purge expired history:

- name: DeltaOptimize
  kind: sql
  connection:
    kind: Deltatable
    locator: invoice_history.delta
  query: OPTIMIZE Deltatable COMPACT VACUUM CLEANUP

The Deltatable SQL connection supports the following optimization syntax:

OPTIMIZE Deltatable [COMPACT] [VACUUM] [AGE=<int>[d|h]] [DRY_RUN] [CLEANUP]
Clause Purpose
COMPACT Bin-packs small Parquet files within each partition into larger, query-optimal files.
VACUUM Removes unreferenced data files older than the retention threshold (AGE, default 7d if omitted).
**`AGE=[d h]`**
DRY_RUN Previews which files would be compacted or vacuumed without modifying storage.
CLEANUP Prunes expired transaction log checkpoints and metadata artifacts after vacuuming.

Best Practice: Add DeltaOptimize to the end of your daily maintenance job and gate it with skip_if: <<'yes' if API.dt(None).weekday() != 6 else ''>> so compaction and vacuum run automatically every Sunday night.

3.4 Deleting Records (Tombstone Pattern)

Delta tables in Automat BI support logical deletes without expensive file rewrites by inserting a tombstone record that matches the target row's composite key with a newer version (_ver).

Convention: The _admin Column

Include an _admin status column in your table schema (initialized as '' during bootstrap). To delete a record:

  1. Emit a tombstone row containing the exact composite key values, a higher _ver timestamp/sequence, and _admin = 'deleted'.
  2. Upsert into the Delta table (data_mode: append with key and version configured) so the tombstone supersedes the active row.
  3. Exclude deleted rows in downstream queries and Report Designer models using WHERE _admin <> 'deleted' (or COALESCE(_admin, '') <> 'deleted').

Example: Tombstoning Voided Invoices

SELECT
  epoch_ms(now())::BIGINT AS _ver,
  'deleted' AS _admin,
  trx_date,
  upc,
  buyer_gln,
  pos_gln,
  year,
  month
FROM VoidedInvoices

Tip: Make sure your tombstone batch includes the partition columns (year, month) alongside the composite key (trx_date, upc, buyer_gln, pos_gln) so the merge targets the exact partition where the original record lives.

← Back to Documentation Hub