Key Columns

Most fact tables in the data warehouse use the same key column structure, which consists of two types of internal identifiers. The first identifier is referred to as a WID value. The WID on a fact table is a foreign key reference to a dimension table’s ROW_WID column. For example, a PROD_WID column in a sales table is referring to the ROW_WID on W_PRODUCT_D (the product dimension table). Joining the WIDs on a fact and a dimension will allow you to look up user-facing descriptors for the dimensions, such as the product number.

The second identifier is known as SCD1_WID and refers to slowly changing dimensions, which is a common data warehousing concept. The IDs on the SCD1_WID columns are carried forward through reclassifications and other dimensional changes, allowing you to locate a single product throughout history, even if it has numerous records in the parent dimension table. For example, joining PROD_SCD1_WID from a sales table with SCD1_WID on W_PRODUCT_D will receive all instances of that product’s data throughout history, even if the product has several different ROW_WID entries due to reclassifications, which insert new records to the dimension for the same item.

The other core structure to understand is Date WIDs (key column DT_WID). These also join with the ROW_WID of the parent dimension (W_MCAL_DAY_D usually), but the format of the WID allows you to extract the date value directly if needed, without table joins. The standard DT_WID value used is a combination of 1 + date in YYYYMMDD + 000. For example, 120210815000 is the DT_WID value for “August 15, 2021”.