Data Warehouse Loading Techniques

All dimension tables gets loaded first followed by Fact tables in the Data Warehousing world

  

As you can see in the above picture, the ETL tool process source data from different sources and load them in the dimension table with the natural key and surrogate key (A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database.)

The fact table read the same source data and it dose the look-up against the dimension tables to get the surrogate key for the particular and load them in the fact table as reference key

Here is a practical example

Row id	Emp First Name	Emp Last Name	EMP No	EMP Dep	EMP Sal
1	John	Beck	2001	A	1000
2	Mike	Morry	2002	A	2000
3	Kevin	Peter	2003	B	1000
4	Steve	Morry	2004	B	2000
5	Jim	Chen	2005	C	3000

The employee dimension will be loaded like this

Employee Dimension
Emp_Surrogate_Key	EMP No	Emp First Name	Emp Last Name	EMP Dep
100001	2001	John	Beck	A
100002	2002	Mike	Morry	A
100003	2003	Kevin	Peter	B
100004	2004	Steve	Morry	B
100005	2005	Jim	Chen	C

The employee Fact table will be like this

Employee Fact
Emp_Surrogate_Key	EMP Sal
100001	1000
100002	2000
100003	1000
100004	2000
100005	3000

The Employee fact joins to the employee dimension using the Emp_surrogate_key

Select emp_dim* from

Employee Dimension as emp_dim,

Employee Fact as emp_fact

Where

emp_dim. Emp_surrogate_key=emp_fact. Emp_surrogate_key