Skip to content
Lucas Lessa
Back to projects

Case study · Data engineering · Finance

Financial comparison with Delta Lake Time Travel

A Databricks solution comparing the financial figures recorded at month-end close with the values loaded in the following days, tracking changes until the next close.

Using Delta Lake versioning, the solution retrieved historical snapshots and compared them with recent versions, revealing changes in financial values without duplicating the whole dataset.

  • Databricks
  • Delta Lake
  • Time Travel
  • PySpark
  • SQL
  • Databricks Jobs

How the solution worked

  1. Financial close day

    Identifying the reference historical snapshot.

  2. Retrieving the historical version

    With Delta Lake Time Travel: querying the transaction log with DESCRIBE HISTORY and finding the version that matches the close.

  3. Comparison with later loads

    Querying the reference version and the latest version of the table.

  4. Analyzing financial differences

    Identifying value changes between the close and subsequent updates.

The routine was designed to run daily, keeping the same close reference while new financial loads arrived. At the next close, a new reference could be set for the following cycle.

Technologies and features used

  • Databricks e Delta Lake

    Transactional storage and versioning of the financial tables.

  • DESCRIBE HISTORY

    Querying the history of operations, versions and timestamps recorded by Delta Lake.

  • PySpark e SQL

    Finding versions, reading snapshots and comparing the data.

  • Databricks Jobs

    Parameterizing the reference date and running the process automatically.

Technical logic

A key point was finding the exact table version that represented the financial close, instead of simply comparing data from two calendar dates. The flow was:

  1. Receive a reference date, for example data_referencia = 2026-03-05.

  2. Query the table history with DESCRIBE HISTORY.

  3. Find the timestamp and version that match the closing load.

  4. Retrieve the historical snapshot with VERSION AS OF.

  5. Compare that snapshot with the latest version of the table.

  6. Publish the results for analysis of the financial changes.

Main challenge: preserving history

The solution depended on old Delta Lake versions being available. So an important aspect was accounting for retention policies and the impact of the VACUUM command.

VACUUM removes old data files that are no longer needed under the retention rules. If the files a historical snapshot needs are removed, that version can't be recovered just because it still appears in the transaction history.

That's why the project required care with file retention and with how long each close needed to stay available for comparison.