Data Productivity Cloud Pipeline
Author: Matillion
Date Posted: Jun 3, 2025
Last Modified: Jun 5, 2025

Prepare Target Table

As part of a larger ELT process, use this pipeline to handle schema drift between the source and target tables.

The “Prepare target table” pipeline can be used to compare the columns between a stage and target table. If differences are found, sometimes known as schema drift, the target table can be automatically updated to accept the latest data.

Image ofExample pipeline with instructions - included in download
Example pipeline with instructions - included in download

Scenario

You have a pipeline that first loads data from a source into a stage table, then you move the new data from the stage table into a target table.

The columns of the source could change over time. The stage table is recreated with each load, so it always has the latest columns from the source.

Description of the “Prepare target table” pipeline

The pipeline will first check that the target table exists. If it does not, it will be created with the same columns as the current stage table. Note that the columns in the target table will be created to allow NULL values, so that missing source columns will not cause issues in future loads.

Next, the pipeline will compare the columns between the stage and target table:

  • If no differences are found, the pipeline will complete with success.
  • If differences are found, it will categorize the differences to determine the next action.

When categorizing the differences, the key principles are

  1. No data must be lost, including presicion and scale.
  2. If changes are allowed, the target table will be altered to keep the pipeline running.
  3. If changes are not allowed, the pipeline will end with failure, indicating that an incompatible difference has been found.

For specific examples of how the columns are categorized, please see the example pipeline included in the download.

Installation

  1. Open a branch on your Data Productivity Cloud project.
  2. Click “Add > Browse Exchange”
Image ofBrowse the Matillion Exchange to find pipelines to import
Browse the Matillion Exchange to find pipelines to import
  1. Search for “Prepare target table” and select the pipeline to import it into your project.
  2. You should now have a folder named “Imported from Exchange > Prepare target table” containing the latest version of this pipeline.

Usage

Open the orchestration pipeline “Imported from Exchange > Prepare target table > Example”.

Follow the instructions on the notes in this pipeline to copy the Run Orchestration component into your own orchestration pipeline, and configure it to prepare your target tables.

Downloads

Licensed under: Matillion Free Subscription License

Installation Instructions

How to Install a Data Productivity Cloud Pipeline