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 that represent a true copy of the data source and 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 odd formats.
- ODS (Operating Data Store): is the place where the transformations, the crossings, ... etc; before feeding the datawarehouse and using as source the STG.
Staging Area and ODS have only a passage that transformations 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.