Wherescape Red and multi stage ETL

In Red, you associate dimensions with a fact in the Stage table. If your load table has data that needs to be transformed before it can be used to join to the appropriate dimension business keys then you may want a 2 step stage process to keep the transformations readable.

For instance if your load table has a column that contains data in the form ddmon (e.g. 01SEP) this can be converted to a date before joining to dim_date to get the dim_date_key. So stage_table_1 would have a date column derived from the ddmon column and stage_table_2 would have the date column plus a dim_date_key column using the relevant join criteria.

Of course stage_table_2 could be derived in one step but my preference is to keep the transformations simple.