PostgreSQL_DB_for_DWH_and_ETL_Additional_materials

1. PostgreSQL DB for DWH and ETL Building

Source triplet

2. Definition

We use source triplet columns to identify the whole path of current row from Sources to Target table.
Here are the main columns of source triplet:
1. Source_system – describes the system (source) this row is loaded from.
Examples: ‘SA_CASH_SALES’, ‘SA_ONLINE_SALES’, ‘BL_3NF’.
It could also be ‘MANUAL’ if the values were inserted manually, like default row.
2. Source_entity (could be source_table as well) – describes the entity this row is loaded from. Usually,
entity is a table.
Examples: ‘SRC_SALES_EUROPE’, ‘CE_EMPLOYEES_SCD’.
It could also be ‘MANUAL’ if the values were inserted manually, like default row.
3. Source_id (could be xxx_src_id as well) – natural (business) key. Describe how the row can be
identified in source entity.
Examples: ‘111’, ‘Qwerty’, ‘123_qwerty’.
It could also be ‘n. a.’ for default row.
2

3. Data type and possible values

All source columns should be in VARCHAR data type only. It is a rule.
Even if your SOURCE_ID column fills by Numbers only – you could not be 100% sure that in the future your
source will not generate value which will contain Letters.
Column
Data type
Examples
Source_system
VARCHAR only
‘MANUAL’, ‘BL_3NF’,
‘SA_EUROPE_SALES’
Source_entity
VARCHAR only
‘MANUAL’, ‘CE_SHOPS’,
‘SRC_EU_SALES’
Source_id
VARCHAR only
‘123’, ‘qwerty’, ‘123qwerty’,
‘123-qwe-123’, ‘n. a.’
3

4. How to define column values

Your source table SRC_SALES
on SA_SOURCE_NAME schema
shop_id
shop_name
other columns
123
Shop 1
…
123MSQ
Shop Minsk
…
GML
Shop Gomel
…
Your BL_3NF table CE_SHOPS
shop_id (generated by sequence)
shop_name
other columns
source_system
source_entity
source_id
1
Shop 1
…
SA_SOURCE_NAME
SRC_SALES
123
2
Shop Minsk
…
SA_SOURCE_NAME
SRC_SALES
123MSQ
3
Shop Unnown
…
SA_SOURCE_NAME
SRC_SALES
GML
4

5. How to define column values

Your BL_3NF table CE_SHOPS
shop_id (generated by sequence)
shop_name
other columns
source_system
source_entity
source_id
1
Shop 1
…
SA_SOURCE_NAME
SRC_SALES
123
2
Shop Minsk
…
SA_SOURCE_NAME
SRC_SALES
123MSQ
3
Shop Unnown
…
SA_SOURCE_NAME
SRC_SALES
GML
Your BL_DM table DIM_SHOPS
shop_surr_id (generated by sequence)
shop_name
other columns
source_system
source_entity
source_id
1
Shop 1
…
BL_3NF
CE_SHOPS
1
2
Shop Minsk
…
BL_3NF
CE_SHOPS
2
3
Shop Unnown
…
BL_3NF
CE_SHOPS
3
5

6. Usage of SOURCE TRIPLET

Imagine the situation: our customer came to us with the report which was created based on our BL_DM
dimensions and mentioned incorrect shop names. We see ‘Minsk Interna’ in the report, but our customer
sure that it should be ‘Minsk International’.
First what we should check – if this value came from sources in such short format.
The path will be the following:
1.
Check the BL_DM.DIM_SHOPS table and find the row with mentioned shop.
SELECT shop_surr_id, shop_name, source_id, source_entity, source_system
FROM dim_shops
WHERE shop_name = ‘Minsk Interna’;
shop_surr_id
127
shop_name
Minsk Interna
source_system
source_entity
BL_3NF
CE_SHOPS
source_id
13
6

7. Usage of SOURCE TRIPLET

2. We should check BL_3NF table using SOURCE_ID value from dimension and find the source of
mentioned row:
SELECT shop_id, shop_name, source_id, source_entity, source_system
FROM ce_shops
WHERE shop_id = 13;
shop_id
13
shop_name
Minsk Interna
source_system
source_entity
SA_EU_SALES
SRC_SALES
source_id
MSQ_1
7

8. Usage of SOURCE TRIPLET

3. We got source values, so now we can check SOURCE data which came from our customer:
SELECT shop_id, shop_name
FROM SA_EU_SALES.src_sales
WHERE shop_id = ‘MSQ_1’;
shop_id
MSQ_1
shop_name
Minsk International
Result 1: We see that in our sources we have a full name of the shop. We need to check our ETL.
shop_id
MSQ_1
shop_name
Minsk Interna
Result 2: We see that in our sources we have same short name of the shop. We should ask customer to
check his source.
8

9. Usage of SOURCE TRIPLET

We also could use only 1 query to get source values.
Here is an example:
SELECT shop_id, shop_name
FROM SA_EU_SALES.src_sales src
JOIN BL_3NF.ce_shops nf
ON to_char(src.shop_id) = nf.source_id
-- we use TO_CHAR() because our source_id is VARCHAR
JOIN BL_DM.dim_shops dim
ON to_char(nf.shop_id) = dim.source_id
WHERE dim.shop_name = ‘Minsk Interna’;
9

10. Usage of SOURCE TRIPLET in your project

1. In JOINS (example: when you insert data into 3nf tables)
SELECT shop_id (generated by sequence)
, src.shop_name
, shc. shop_category_id
, src.shop_id AS source_id
, ‘SRC_ASIA’ AS source_entity
, ‘SA_ASIA’ AS source_system
…………
FROM SA_ASIA.src_asia src
JOIN BL_3NF. ce_shop_category shc
ON src.shop_category_id = shc.source_id
AND shc.source_entity = ‘SRC_ASIA’
AND shc.source_system = ‘SA_ASIA’
10

11. Usage of SOURCE TRIPLET in your project

1.
In NOT EXISTS or NOT IN
INSERT INTO BL_3NF. ce_shops …
SELECT …
FROM SA_ASIA.src_asia src
…
WHERE NOT EXISTS
( SELECT 1
FROM BL_3NF. ce_shops sh
WHERE src.shop_id = sh.source_id
AND sh.source_entity = ‘SRC_ASIA’
AND sh.source_system = ‘SA_ASIA’)
…
11

12. Thank you!