Types
of Data Loading:
1.
Full
Data Loading:
2.
Incremental
or Delta or Partial Loading
Solutions
for Incremental Loading:
1.
Use Lookup
2.
Use Slowly Changing Dimension Transformation(SCD):
o
This
is used to capture history of the row.
o
This
is used in Data Warehouse (OLAP)
o
There are 3 types of SCD
§
Type 0 SCD: Changes treated as errors (Nobody
can change the data, like Name, Gender, and DOB. If it is changed by anybody it will give error)
§
Type 1 SCD: It will over write the row (If any
changes will happen in the source, the changes will be overwrite in destination.)
§
Type 2 SCD: It will capture history of a row (If
any changes happen in source, the same row get copied as new row in destination.)
o
Note:
o
OLTP system cannot store
history of a row.
o SCD will support Insert and update but it will not
support Delete.
o
Surrogate Key: A sequence number in
the DWH system.
o
SQL Server 2012 has Change Data Capture (CDC) to store history
of OLTP data.
3.
Use Merge Statements in SQL(Recommended):
·
To
perform incremental loading
·
It
is one of the DML statement placed in SQL
Server 2008.
·
Merge statement is a combination
statement that can perform INSERT, UPDATE , DELETE statements based on whether
rows that match the selection criteria exists in the target table or not.
Syntax:
MERGE Target_Table as
target
USING Source_Table as
source
ON condition
WHEN MATCHED THEN
UPDATE
SET columns=source.column,....
WHEN TARGET NOT MATCHED THEN
INSERT VALUES
(source.column1, source.column2,)
4.
Use Customized SQL code/SSIS Code
5.
Use Set Operators (Except, Intersect, Union etc.)
Q. What is a pre-requisite to perform an
incremental loading?
=> Source system should have a primary key.