We always talk about STG and ODS in BI archi, so what is the role of these two phases in a BI archi?
- STG (Staging Area): is a set of tables which represent a true copy of the data source and which are purged each time the ETL is run: it is a waiting area, a “boarding room” before the ODS phase; it is in these tables that we can find data in bizarre formats.
- ODS (Operating Data Store): is the place where the transformations, the crossings,… etc; before the power supply of the data warehouse and which uses the STG as a source.
Staging Area and ODS have only a passage and transformation in a decision-making architecture.
In no case should they be used as a source for reporting, for example! These are not the places where the consolidated and historized data are located!
STG is not necessarily essential: it can possibly be deleted; in this case we insert the data directly into ODS. But for reasons of exploitation, it can serve us to supervise the last data inserted before the next execution of ETL and resume processing quickly if a data quality problem appears.
The STG and ODS tables can be grouped together in the same database, as well as in different databases.