STORAGE.CHANGE_TRACKING

STORAGE.CHANGE_TRACKING physical option:

• In Snowflake

  • for a TABLE

    • enables change tracking

• In Databricks SQL

  • for a TABLE

    • enables deletion vectors

    • enables row tracking

    • enables change datafeed

  • for a MATERIALIZED VIEW

    • enables deletion vectors

Change tracking allows querying table changes within a given time window which can reduce the needed data processing amount to a fraction of the full data content. This is at least recommended to be used for Databricks SQL Materialized Views to function cost-efficiently.

See also:

• Changes (Snowflake documentation)

• Change tracking using streams (Snowflake documentation)

• Use Delta Lake change data feed on Databricks (Databricks documentation)

• Deletion vectors in Databricks (Databricks documentation)

• Row tracking in Databricks (Databricks documentation)

• Incremental refresh for materialized views (Databricks documentation)

• CONFIG_ENTITY_DEFAULTS

Usage

STORAGE.CHANGE_TRACKING: Boolean

Default: false

Notes

Change tracking can be enabled for tables (Snowflake & Databricks SQL) and materialized views (Databricks SQL).

When change tracking is enabled, ADE prevents entity recreation during deployment to protect the change history:

CHANGE CASE: ENTITY_RECREATION_PREVENTED_BY_CHANGE_TRACKING

To deploy changes requiring entity recreation, one can either:

• force entity recreation from entity summary details

• disable change tracking

Examples

Enabling change tracking for a table

Snowflake

Set STORAGE.CHANGE_TRACKING to true for a table (e.g. F_TRIP):

STORAGE.CHANGE_TRACKING: true

DDL generated by Agile Data Engine:

ALTER TABLE publish.F_TRIP SET CHANGE_TRACKING = true;

Databricks SQL

Set STORAGE.CHANGE_TRACKING to true for a table (e.g. F_TRIP):

STORAGE.CHANGE_TRACKING: true

DDL generated by Agile Data Engine:

ALTER TABLE publish.F_TRIP SET 
  TBLPROPERTIES(delta.enableChangeDataFeed = true,
                delta.enableRowTracking = true,
                delta.enableDeletionVectors = true);

Enabling change tracking for a materialized view

Databricks SQL

Set STORAGE.CHANGE_TRACKING to true for a materialized view (e.g. F_TRIP_MATERIALIZED):

STORAGE.CHANGE_TRACKING: true

DDL generated by Agile Data Engine:

CREATE MATERIALIZED VIEW publish.F_TRIP_MATERIALIZED (
  ...
)
TBLPROPERTIES (
    'delta.enableDeletionVectors' = true
) AS
SELECT
  ...