Why Are Staging or Intermediate Tables and Schemas Created in the Destination?

Question

Why are staging or intermediate tables and schemas created in the destination?

Environment

• Projects using Fivetran data models
• Projects using Quickstart data models

Answer

When you configure your transformation, our source models create staging tables with the prefix stg_ in your destination. These staging tables are intermediate steps and prepare your data for our transform models. See How Fivetran Data Models Work to learn more.

We use staging tables for the following reasons:

• To load data efficiently: Loading data into staging or intermediate tables allows us to load data to the destination tables in bulk, which is more efficient than loading it row-by-row.
• To load data incrementally: We use incremental loading strategies to update only changed or new data since the last synchronization. To facilitate this process, we temporarily load incoming data in staging tables and compare it with existing data to determine what needs updating or adding.
• To map schemas consistently: Different source systems may have varying data structures. Adding staging tables allows us to normalize and transform data before loading it into the destination tables.

Examples of staging tables include:

• <CONNECTOR_NAME>_STG_CONNECTOR-TYPE: This is the staging schema for your connector transformation. We initially process and store data here before further transformation.
• <CONNECTOR_NAME>_CONNECTOR-TYPE: This schema is the final destination for the connector transformation. When we complete the processing steps, we load transformed data into this schema.