Stage tables are used to transform the data to a star schema or third normal form model. A stage table can be a fact or an EDW 3NF table that only contains change data or a work table. In star schema data warehouses, the stage table brings all the dimensional joins together in preparation for publishing into the fact table.
A stage table is built from the Data Warehouse connection. Unless you are retrofitting an existing system, stage tables are typically built from one or more load or stage tables. They can utilize the surrogate keys from a number of dimension tables.
The normal steps for creating a stage table are defined below and are covered in this chapter. The steps are:
- Identify the source transactional data that will ultimately constitute the fact or EDW 3NF table. If the data is sourced from multiple tables ascertain if a join between the source tables is possible, or if a series of passes will be required to populate the stage table. If the latter option is chosen, then bespoke code is needed.
- Using the 'drag and drop' functionality drag the table with the lowest granular data into a stage target. Refer to Building the Stage Table for details.
- Add columns from other source tables. Refer to Building the Stage Table for details.
- Add any relevant dimension table or EDW 3NF table keys. Refer to Building the Stage Table for details.
- Create the stage table in the database. Refer to Building the Stage Table for details.
- Build the update procedure. Refer to Generating the Staging Update Procedure for details.
- Test the update procedure and analyze the results. Refer to Tuning the Staging Update Process for details.
- Modify the update procedure as required. Refer to Tuning the Staging Update Process for details.
If you are building a Data Vault system, a Stage table with a type of Data Vault Stage can be created to generate hash keys that are used in building Data Vault objects (Hub, Link or Satellite tables). Refer to Data Vaults for details.