Skip to main content

Enable history tracking (SCD type 2)

Applies to: check marked yes API-based pipeline authoring

The history tracking setting, also known as the slowly changing dimensions (SCD) setting, determines how to handle changes in your data over time. Turn history tracking off (SCD type 1) to overwrite outdated records as they're updated and deleted in the source. Turn history tracking on (SCD type 2) to maintain a history of those changes. Deleting a table or column in the source does not delete that data from the destination, even when SCD type 1 is selected.

For example, let's say that you ingest the following table:

Example source table

Let's also say that Alice's favorite color changes to purple on January 2.

If history tracking is off (SCD type 1), the next run of the ingestion pipeline updates that row in the destination table.

SCD type 1 example

If history tracking is on (SCD type 2), the ingestion pipeline keeps the old row and adds the update as a new row. It marks the old row as inactive so that you know which row is up-to-date.

SCD type 2 example

Not all connectors support SCD type 2. For a list of supported connectors, see Feature compatibility.

Example: Google Analytics​

SCD type 2 is supported for the users and pseudonymous_users tables using last_updated_date as the cursor column. It's not supported for event-level tables, which are append-only.

By default, history tracking is off (SCD type 1). You can use the following example YAML file to change this setting in a bundle:

YAML
resources:
  pipelines:
    pipeline_ga4:
      name: <pipeline>
      catalog: <destination-catalog>
      schema: <destination-schema>
      ingestion_definition:
        connection_name: <connection>
        objects:
          - table:
              source_url: <project-id>
              source_schema: <property-name>
              destination_catalog: <destination-catalog>
              destination_schema: <destination-schema>
              table_configuration:
                scd_type: SCD_TYPE_2

Example: SQL Server​

The sequence column that you specify in the pipeline configuration (for example, last_updated, modified_at, or version_number) determines the time span that each row version was active (recorded in the __START_AT and __END_AT columns in the target table).

The following sequence_by column types are supported:

  • Timestamp
  • Date
  • Integer
  • Long
  • String

By default, history tracking is off (SCD type 1). To change this setting in a bundle, use the following example YAML file:

YAML
resources:
  pipelines:
    pipeline_sqlserver:
      name: <pipeline>
      catalog: <destination-catalog>
      schema: <destination-schema>
      ingestion_definition:
        connection_name: <connection>
        objects:
          - table:
              source_catalog: <source-catalog>
              source_schema: <source-schema>
              source_table: <source-table>
              destination_catalog: <destination-catalog>
              destination_schema: <destination-schema>
              table_configuration:
                scd_type: SCD_TYPE_2
                sequence_by: <sequence-column>

Example: Workday​

By default, history tracking is off (SCD type 1). The following example YAML changes this setting using Databricks Asset Bundles.

YAML
resources:
  pipelines:
    pipeline_workday:
      name: <pipeline>
      catalog: <destination-catalog>
      schema: <destination-schema>
      ingestion_definition:
        connection_name: <connection>
        objects:
          - report:
              source_url: <report-url>
              destination_catalog: <destination-catalog>
              destination_schema: <destination-schema>
              table_configuration:
                scd_type: SCD_TYPE_2

Limitations​

Running a full refresh replaces the entire table. This removes all previous row versions. New history is tracked starting from the refresh point.

Last updated on