How to create and use a Delta table with daily history in a Lakehouse

João Barros 06 de August de 2026 5 min read

This tutorial shows how to create and use a Delta table in a Lakehouse that records daily change history (daily snapshot), enabling temporal analysis and auditing. It is useful to retain versions by day without relying on continuous time travel and to generate consistent daily reports.

Prerequisites

Access to a Lakehouse environment (for example Microsoft Fabric Lakehouse) with write permissions.
Notebook with PySpark or SQL endpoint configured for the Lakehouse.
A sample CSV file or streaming source with product/stock data to import.

Step 1: Design the daily history model

Before creating tables, define how to store the history: a Delta 'base' table with the current data and a Delta 'snapshot_diario' table that stores a copy of the rows with a snapshot_date column. This avoids rewriting the base and makes per-day consumption easier.

Step 2: Create the base Delta table

Create a Delta table to hold the current records (this table will be updated by ETL/ingest). Keep the modeling simple: id, nome, stock, price, updated_at.

# PySpark example
spark.sql("CREATE TABLE IF NOT EXISTS lakehouse.produtos_base (
  id STRING,
  nome STRING,
  stock INT,
  price DOUBLE,
  updated_at TIMESTAMP
) USING DELTA LOCATION 'abfss://@.dfs.core.windows.net/lakehouse/produtos_base'")

Step 3: Populate the base table with initial data

Load a sample CSV into the base table. Alternatively, do an INSERT INTO or write a DataFrame.

# PySpark example to load CSV and write to Delta
df = spark.read.option('header', 'true').csv('/mnt/data/produtos.csv')
from pyspark.sql.functions import col, current_timestamp
df2 = df.select(col('id'), col('nome'), col('stock').cast('int'), col('price').cast('double'))
  .withColumn('updated_at', current_timestamp())
df2.write.format('delta').mode('overwrite').saveAsTable('lakehouse.produtos_base')

Step 4: Create the daily snapshots table

Create a separate Delta table that will have an additional snapshot_date column of type date. This table will accumulate a copy of the base table records each day.

spark.sql("CREATE TABLE IF NOT EXISTS lakehouse.produtos_snapshot_diario (
  id STRING,
  nome STRING,
  stock INT,
  price DOUBLE,
  updated_at TIMESTAMP,
  snapshot_date DATE
) USING DELTA LOCATION 'abfss://@.dfs.core.windows.net/lakehouse/produtos_snapshot_diario'")

Step 5: Schedule the daily snapshot process (manual example)

To test, run a job manually that inserts the base table data into the snapshot_diario table with the current date. In production, schedule this with the service scheduler (e.g., pipeline/flow).

# PySpark/SQL example to append daily snapshot
from pyspark.sql.functions import current_date
snapshot = spark.sql("SELECT id, nome, stock, price, updated_at FROM lakehouse.produtos_base")
snapshot.withColumn('snapshot_date', current_date()) \
  .write.format('delta').mode('append').saveAsTable('lakehouse.produtos_snapshot_diario')

Step 6: Handling deduplication and only changed records

To avoid unnecessary duplicates and reduce storage, capture only rows that changed since the last snapshot. Do a left-anti join between the base and the latest snapshot by id and relevant values.

# Example: identify changes by comparing with the last snapshot
last_snapshot = spark.sql("SELECT * FROM lakehouse.produtos_snapshot_diario WHERE snapshot_date = (SELECT max(snapshot_date) FROM lakehouse.produtos_snapshot_diario)")
base = spark.sql("SELECT id, nome, stock, price, updated_at FROM lakehouse.produtos_base")
changed = base.alias('b').join(last_snapshot.alias('s'), on='id', how='left') \
  .filter("s.id IS NULL OR b.nome <> s.nome OR b.stock <> s.stock OR b.price <> s.price") \
  .select('b.*')
changed.withColumn('snapshot_date', current_date()).write.format('delta').mode('append').saveAsTable('lakehouse.produtos_snapshot_diario')

Step 7: Compacting and managing growth

With daily accumulations the table can grow. Periodically run OPTIMIZE (if available) and consider retention policies (e.g., keep 365 days). Use VACUUM carefully according to the service policy.

# SQL example to clean old snapshots (keep 365 days)
-- Check the dates first
SELECT DISTINCT snapshot_date FROM lakehouse.produtos_snapshot_diario ORDER BY snapshot_date DESC;

-- Delete older rows (example with WHERE condition)
DELETE FROM lakehouse.produtos_snapshot_diario WHERE snapshot_date < date_sub(current_date(), 365);

Verify the result

Confirm there are entries in the snapshot_diario table for today and that the base remains intact. Example queries to validate content and detect duplicates.

-- Count per day
SELECT snapshot_date, count(*) AS total FROM lakehouse.produtos_snapshot_diario GROUP BY snapshot_date ORDER BY snapshot_date DESC;

-- View today's data
SELECT * FROM lakehouse.produtos_snapshot_diario WHERE snapshot_date = current_date() LIMIT 100;

-- Check for duplicates by id and day
SELECT id, snapshot_date, count(*) FROM lakehouse.produtos_snapshot_diario GROUP BY id, snapshot_date HAVING count(*) > 1;

Conclusion

You now have a simple strategy to create and maintain a daily history in Delta on the Lakehouse: a base table for current data and a snapshot_diario table for per-day analyses. Suggested next steps: automate the daily job with a pipeline, add compression/OPTIMIZE and implement retention. Tip: start by testing the deduplication logic on small datasets to avoid uncontrolled growth — do you intend to store everything or only significant changes?