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

Tables

When you configure your transformation, our pre-built data models create staging tables with the prefix stg_ in your destination. These tables act as intermediate steps that prepare your data for the final, analytics-ready tables. To learn more, see our documentation on how pre-built data models work.

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.

Schemas

We use separate schemas to organize staging and final tables into distinct layers. Staging schemas contain temporary or intermediate data used during processing, while final schemas contain the tables intended for analysis. This separation helps prevent confusion and ensures you query only finalized data.