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.
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 likeupcorinvoice_id, which cause the "small file problem." - Deduplication / Upserts (
key+version): Whenkeyandversionare set, Automat BI merges incoming rows against existing records matching the compositekey. If a duplicate key arrives, the row with the higherversion(_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_deletedorstatus = 'VOID') in your upstream source, upsert the updated_verso the tombstone wins, and filterWHERE NOT _is_deletedin 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(or1899-12-30, the classic Excel/COM epoch) as your cast fallback keeps1970-01-01strictly 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: errorandschema_mode: nullas safety guardrails against silent data loss or unintended schema drift:
data_mode(error|append|overwrite): Defaults toerrorso a misconfigured pipeline fails loudly instead of accidentally overwriting or duplicating an existing table. Switch toappendfor ongoing incremental loads/upserts, oroverwritefor full-table refreshes.schema_mode(null|merge|overwrite): Defaults tonull(strict validation) to block upstream API changes or typos from polluting your table with unexpected columns. Set tomergewhen intentionally adding new fields (likedoc_date), oroverwritewhen replacing the schema alongsidedata_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= |
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
DeltaOptimizeto the end of your daily maintenance job and gate it withskip_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:
- Emit a tombstone row containing the exact composite
keyvalues, a higher_vertimestamp/sequence, and_admin = 'deleted'. - Upsert into the Delta table (
data_mode: appendwithkeyandversionconfigured) so the tombstone supersedes the active row. - Exclude deleted rows in downstream queries and Report Designer models using
WHERE _admin <> 'deleted'(orCOALESCE(_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 compositekey(trx_date,upc,buyer_gln,pos_gln) so the merge targets the exact partition where the original record lives.