Can we get the snapshot of the change data feed tables at a given time?

[manjeettanwar10]

We are trying to build the history snapshot for data that we are ingested in the delta tables. We are exploring different option like delta history, change data feed and dbt snapshot.

This Question is particularly about the change data feed. So I want to know that by CDF can we get the changes in table whether is delete, update or insert.

But we need to the get snapshot of table at given time not just the changes. Is there any thing that can be done to achieve this via CDF.

Also if CDF is the correct approach for getting snapshot of the table in the history.

What will be the recommendation from Databricks about how we can achieve functionality to get snapshot of the table in the history.

[raphaelblg]

Hello @manjeettanwar10 ,

The Delta History API allows you to view the logs of all SQL statements executed on your table, including UPDATE, DELETE, and INSERT commands. When used in conjunction with Delta's Time Travel features, it's possible to retrieve the Delta table and data in a previous state, as long as data retention periods are respected.

Alternatively, Change Data Feed can also be used to monitor the state of data and changes. One of the differences is that CDF captures data values, while Delta History displays the SQL statement history and predicates, but not the actual data values.

As described at Change Data Feed:

"Change data feed works in tandem with table history to provide change information. Because cloning a Delta table creates a separate history, the change data feed on cloned tables doesn’t match that of the original table."