Skip to content

Materialization

Open Lakehouse Contract - the standard

LakeLogic implements the Open Lakehouse Contract (OLC), the open standard for lakehouse data contracts. The authoritative field-level spec for materialization is the OLC reference: materialization reference →. This page shows how the LakeLogic runtime applies that spec (engine behavior, examples, and runtime-specific notes).

Materialization controls how validated data is written to the target Delta table.

Think of it like choosing a filing strategy. You can append new pages to the end of a folder (append), update existing pages in-place (merge), keep every version of every page with dates (SCD2), or throw everything out and start fresh each day (overwrite).

Write Strategies

Strategy Use Case Typical Layer Business Value
append Immutable event logs Bronze, Gold (facts) Complete audit trail, nothing is ever lost
merge Upsert by primary key (SCD1) Silver, Gold (dims) Always current, deduplication built in
scd2 Track every change with history Silver, Gold (dims) Point-in-time analytics, regulatory compliance
overwrite Daily snapshots, full refreshes Gold (aggregates) Clean slate, simple to reason about

Append

Adds new rows without checking for duplicates. Ideal for event logs and transaction records.

Example: Append with date partitioning

materialization:
  strategy: "append"
  partition_by: ["ingestion_date"]

Merge (Upsert)

Matches incoming rows against existing rows using primary_key, updates matches, inserts new rows.

Example: Merge with deduplication guard

primary_key: ["customer_id"]

materialization:
  strategy: "merge"
  merge_dedup_guard: true        # Dedup incoming batch by PK before merge

Key behaviours:

  • Matched rows → UPDATE all non-key columns
  • Unmatched incoming → INSERT
  • Unmatched existing → kept as-is (no deletes)
  • _lakelogic_processed_at → updated on every merge (last modified)
  • _lakelogic_created_at → immutable (first-insert time)

SCD2 (History Tracking)

See Dimensional Modeling for full SCD2 configuration.

Overwrite

Replaces all data on each run. Use for pre-aggregated Gold tables that are recomputed daily.

Example: Daily overwrite with monthly partitions

materialization:
  strategy: "overwrite"
  partition_by: ["month"]

Partitioning & Clustering

Partitioning organises data into physical directories. Clustering sorts data within partitions for faster queries.

Example: Partition by country, cluster by customer

materialization:
  partition_by: ["country", "created_date"]
  cluster_by: ["customer_id"]

Target Path & Format

Example: Explicit target path

materialization:
  target_path: "{data_root}/{silver_layer}_{system}_customers"
  format: "delta"                # delta | iceberg | ducklake | parquet | csv
  location: "abfss://container@account.dfs.core.windows.net/silver/customers/"

For warehouses and databases — and any destination dlt supports — see Relational Databases via dlt.

DuckLake

DuckLake is DuckDB's open lakehouse format: a SQL catalog plus Parquet data, with snapshots and time travel. It runs on plain DuckDB — no cloud service or JVM needed.

Example: a DuckLake table

metadata:
  ducklake_metadata: "./lakehouse/ducklake_catalog.ducklake"   # or md:my_lake for MotherDuck
  ducklake_data_path: "./lakehouse/ducklake_data/"             # or s3:// gs:// abfss://
materialization:
  strategy: merge                 # append | merge | overwrite
  format: "ducklake"
  target_path: "table:rideflow_lake.marketplace.silver_trips"  # catalog.schema.table
  • Target: table:<catalog>.<schema>.<table>; the first part is the DuckLake catalog's name.
  • Where it lives: ducklake_metadata / ducklake_data_path in the contract's metadata, or the DUCKLAKE_METADATA / DUCKLAKE_DATA_PATH environment variables. Without either, a local catalog under ./lakehouse/ is used.
  • MotherDuck: a metadata path starting md: writes a native DuckLake database in MotherDuck.
  • Cloud storage: data paths on s3://, gs:// or abfss:// are supported.
  • Strategies: overwrite replaces the table; merge and scd2 work as on every other target — scd2 keeps history rows (1.84+; earlier versions wrote it like merge); anything else appends. Each merge or scd2 write is one transaction, so one DuckLake snapshot.

Soft Deletes (CDC)

When load_mode: cdc, LakeLogic automatically handles soft deletes — marking rows as deleted rather than physically removing them. This preserves audit trails and enables "as-of" queries.

Example: Soft delete column configuration

materialization:
  soft_delete_column: "_lakelogic_is_deleted"
  soft_delete_value: true
  soft_delete_time_column: "_lakelogic_deleted_at"
  soft_delete_reason_column: "_lakelogic_delete_reason"

Deletes From Full Snapshots

A source that sends its complete current records every time (a full daily file over SFTP, a nightly full extract) never says what was deleted. deletion: {strategy: snapshot_reconcile} compares each load with the table: a key the table holds but the load lacks was deleted at source.

Example: a bank's full daily settlement file

primary_key: [settlement_id]
materialization:
  strategy: merge
  soft_delete_column: is_deleted        # omit to remove missing records instead
  soft_delete_time_column: deleted_at
  soft_delete_reason_column: delete_reason
  deletion:
    strategy: snapshot_reconcile
    max_delete_ratio: 0.2               # stop the run rather than delete more than 20%
    scope: [country]                    # optional: each file covers one country
  • A missing record is flagged is_deleted: true with delete_reason: missing_from_snapshot and keeps its last values. Without soft_delete_column it is removed.
  • A deleted record that appears again is restored (is_deleted: false).
  • If more than max_delete_ratio of the current records would be deleted, the run fails and deletes nothing: a truncated or empty file must never wipe a table.
  • Same behaviour on Polars, DuckDB, pandas and Spark: the missing records become the delete signal the CDC merge already applies.

Full loads only

On an incremental feed every record not in that batch would be deleted. Use CDC deletes for a source that sends delete events.

Table Properties

Example: Auto-optimize Delta tables

materialization:
  table_properties:
    "delta.autoOptimize.optimizeWrite": "true"
    "delta.autoOptimize.autoCompact": "true"

  compaction:
    auto: true
    vacuum_retention_hours: 168

Fact Table Configuration

See Dimensional Modeling for the fact: block (transaction, periodic snapshot, accumulating snapshot, factless, aggregate).

Relational Databases (Postgres, SQL Server, Azure SQL) via dlt

LakeLogic writes to any dlt destination, including Postgres, SQL Server and Azure SQL, Snowflake and BigQuery. Use it in one of two ways:

  • As the main target. Set format: dlt and the table lands in the database instead of the lake. The table is named after the contract's dataset.
  • As a secondary target. Write to the lake as usual, and also copy the same rows into a database (secondary_targets).

Install the destination's extras, for example pip install "dlt[postgres]" or pip install "dlt[mssql]". SQL Server and Azure SQL also need ODBC Driver 18.

Example: Postgres as the main target, with merge

primary_key: ["customer_id"]

materialization:
  strategy: merge                       # append | overwrite | merge (merge needs primary_key)
  format: dlt
  dlt_destination: postgres
  dlt_credentials: env:SALES_DB_URL     # postgresql://user:pass@host:5432/db?sslmode=require
  dlt_dataset_name: sales               # the database schema

Example: Delta in the lake, plus a copy in Azure SQL

materialization:
  strategy: append
  format: delta
  secondary_targets:
    - format: dlt
      dlt_destination: mssql
      dlt_credentials: keyvault://my-vault/reporting-sql-url
      dlt_dataset_name: reporting
      table_name: customers
      fail_on_error: true               # fail the run if the copy fails

Credentials never need to be in the contract. dlt_credentials accepts:

Value Reads the secret from
env:VAR or ${ENV:VAR} an environment variable
keyvault://vault/secret Azure Key Vault (DefaultAzureCredential)
databricks://scope/key a Databricks secret scope
(omitted) dlt's DESTINATION__<NAME>__CREDENTIALS variable or secrets.toml

A literal connection string still works but logs a warning. Error messages never contain the password.

Strategies: append appends, overwrite replaces the table, and merge updates and inserts on primary_key. scd2 and fact loads are refused, because they need a lakehouse format such as Delta.

Secondary targets run after every main format: Delta, Parquet, CSV, Iceberg or dlt. A secondary target with fail_on_error: false logs its error and lets the run continue. It also tries dlt's own configuration when no credentials are set.