Share via

Data warehouse : STG & ODS

  • Article

We always talk about STG and ODS in BI architectures, so what is the role of these two phases in a BI architecture?

                                           

  • STG (Staging Area): is a set of tables that represent a true copy of the data source and are purged each time the ETL is executed, it is a waiting area, a "boarding room" before the ODS phase, it is in these tables that we can find data in weird formats.
  • ODS (Operating Data Store): is the place where transformations, crossings, etc.. will be carried out, before the data warehouse is powered and which uses the STG as its source.

Staging area and ODS have only a role of passage and transformation in a decisions architecture.
In no case should they serve as a source for reporting for example! It is not in these places that the consolidated and historical data are found!

STG is not necessarily indispensable : it may possibly be deleted, in this case the data is inserted directly into the ODS. But for operational reasons, it can be used to monitor the latest data inserted before the next ETL execution and to resume processing quickly if a data quality problem occurs.

The STG and ODS tables can be grouped in the same database or in different databases.