Using temporary destination tables
Once the data is in temporary tables, we can begin performing the simple conversions to start getting it into shape. The next step is to create a set of tables identical to the destination tables in the data warehouse. For example, if there exists a highly partitioned sales transactions table in the data warehouse, we should create one of those partitions at this point. This allows us to start populating the database in a form that fits the data warehouse requirement. Because we are about to execute a substantial number of consistency checks at this point, data should not be loaded into the warehouse until it has been cleaned up. If a consistency check fails, then that particular item of data needs to be set aside in a temporary table till later. Although all relational databases provide some form of rollback, in practice it is easier to load data into a temporary area, clean it up, and then publish it to the data warehouse.
On the basis that we have used temporary tables identical to the destination tables, it becomes a simple exercise to publish data to the data warehouse. This can be achieved either by a simple copy operation or, even better, by reassigning the temporary table to become a partition of the destination table. Once source data is in the temporary data store and has been cleaned up, the warehouse manager can then transform it into a form suitable for decision support queries. The data is transformed into a form in which the bulk of the factual data lies in the center, surrounded by the reference (dimension) data. Three variations on this theme are commonly used: star schemas, snowflake schemas, and starflake schemas.