Keymap.Customer
	Customer_dkey		CustomerId_bkey_CRM		CustomerNo_bkey_ERP	ValidFrom_meta	ValidTo_meta
	1		176		HA-65	1900-01-01	9999-12-31
	2		359		JW-11	1900-01-01	9999-12-31
	3		421		MB-01	1900-01-01	9999-12-31
	4		777			1900-01-01	9999-12-31
	5		778			1900-01-01	9999-12-31
	6		779			1900-01-01	9999-12-31
	7		901		SJ-08	1900-01-01	9999-12-31
A three days scenario for a ETL process from staging table to type 2 or type 7 dimension table with: Early arriving fact business key value becomes Unknown with IsInferred = 1, Late arriving dimension and Correction of data, Deletion of data become new row with IsDeleted = 1
Day 1
Staging.Customer
	Customer_dkey		CustomerId_bkey	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit			IsCorrection_meta	IsDeleted_meta	IsInferred_meta		InsertExecutionLogId_audit	UpdateExecutionLogId_audit
	1		176	Hans Andersen	77 Oxford Street, London	London	1997-04-05	CRM	1997-04-05			0	0	0		3
	2		359	Joe Watzon	33 Acorn Street, Boston	Boston	2000-01-12	CRM	2000-01-12			0	0	0		3
	3		421	Marie Beaulieur	55 Rue Saint-Jean, Lyon	Lyon	2005-12-01	CRM	2005-12-01			0	0	0		3
	4		777	Unknown 777		Unknown		CRM	2006-01-01			0	0	1		3
	5		778	Unknown 778		Unknown		CRM	2006-01-01			0	0	1		3
	6		779	Unknown 779		Unknown		CRM	2006-01-01			0	0	1		3
Dim.Customer
Customer_key	Customer_dkey	Customer_pkey	CustomerId_bkey	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit	ValidFrom_meta	ValidTo_meta	IsCurrent_meta	IsDeleted_meta	IsInferred_meta	CorrectionTime_audit	InsertExecutionLogId_audit	UpdateExecutionLogId_audit
-2	-2	0	-2	Unknown	Unknown	Unknown		DWH	1900-01-01	1900-01-01	9999-12-31	1	0	1		0
-1	-1	0	-1	Missing	Missing	Missing		DWH	1900-01-01	1900-01-01	9999-12-31	1	0	1		0
1	1	0	176	Hans Andersen	77 Oxford Street, London	London	1997-04-05	CRM	1997-04-05	1900-01-01	9999-12-31	1	0	0		4
2	2	0	359	Joe Watzon	33 Acorn Street, Boston	Boston	2000-01-12	CRM	2000-01-12	1900-01-01	9999-12-31	1	0	0		4
3	3	0	421	Marie Beaulieur	55 Rue Saint-Jean, Lyon	Lyon	2005-12-01	CRM	2005-12-01	1900-01-01	9999-12-31	1	0	0		4
4	4	0	777	Unknown 777		Unknown		CRM	2006-01-01	1900-01-01	9999-12-31	1	0	1		4
5	5	0	778	Unknown 778		Unknown		CRM	2006-01-01	1900-01-01	9999-12-31	1	0	1		4
6	6	0	779	Unknown 779		Unknown		CRM	2006-01-01	1900-01-01	9999-12-31	1	0	1		4
Day 2
Staging.Customer
	Customer_dkey		CustomerId_bkey	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit			IsCorrection_meta	IsDeleted_meta	IsInferred_meta		InsertExecutionLogId_audit	UpdateExecutionLogId_audit
	1		176	Hans Andersen	77 Oxford Street, London	London	2006-01-29	CRM	2006-01-29			0	1	0		5
	2		359	Joe Watson	33 Acorn Street, Boston	Boston	2006-01-30	CRM	2006-01-30			1	0	0		5
	3		421	Marie Lesauvage	16 Rue le Tasse, Paris	Paris	2007-11-16	CRM	2007-11-16			0	0	0		5
	4		777	Anna Jensen	Vesterbrogade 8, Copenhagen	Copenhagen	2006-01-31	CRM	2006-01-31			1	0	0		5
Dim.Customer
Customer_key	Customer_dkey	Customer_pkey	CustomerId_bkey	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit	ValidFrom_meta	ValidTo_meta	IsCurrent_meta	IsDeleted_meta	IsInferred_meta	CorrectionTime_audit	InsertExecutionLogId_audit	UpdateExecutionLogId_audit
-2	-2	0	-2	Unknown	Unknown	Unknown		DWH	1900-01-01	1900-01-01	9999-12-31	1	0	1		0
-1	-1	0	-1	Missing	Missing	Missing		DWH	1900-01-01	1900-01-01	9999-12-31	1	0	1		0
1	1	0	176	Hans Andersen	77 Oxford Street, London	London	1997-04-05	CRM	1997-04-05	1900-01-01	2006-01-29	0	0	0		4	6
2	2	0	359	Joe Watson	33 Acorn Street, Boston	Boston	2006-01-30	CRM	2006-01-30	1900-01-01	9999-12-31	1	0	0	2024-12-18 14:05:24.413	4	6
3	3	0	421	Marie Beaulieur	55 Rue Saint-Jean, Lyon	Lyon	2005-12-01	CRM	2005-12-01	1900-01-01	2007-11-16	0	0	0		4	6
4	4	0	777	Anna Jensen	Vesterbrogade 8, Copenhagen	Copenhagen	2006-01-31	CRM	2006-01-31	1900-01-01	9999-12-31	1	0	0	2024-12-18 14:05:24.413	4	6
5	5	0	778	Unknown 778		Unknown		CRM	2006-01-01	1900-01-01	9999-12-31	1	0	1		4
6	6	0	779	Unknown 779		Unknown		CRM	2006-01-01	1900-01-01	9999-12-31	1	0	1		4
7	1	1	176	Hans Andersen	77 Oxford Street, London	London	2006-01-29	CRM	2006-01-29	2006-01-29	9999-12-31	1	1	0		6
8	3	3	421	Marie Lesauvage	16 Rue le Tasse, Paris	Paris	2007-11-16	CRM	2007-11-16	2007-11-16	9999-12-31	1	0	0		6
Day 3
Staging.Customer
	Customer_dkey		CustomerId_bkey	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit			IsCorrection_meta	IsDeleted_meta	IsInferred_meta		InsertExecutionLogId_audit	UpdateExecutionLogId_audit
	2		359	Joe Watson	26 Branch Street, Boston	Boston	2014-07-01	CRM	2014-07-01			0	0	0		7
	3		421	Marie Sainte	13 La Prom, Nice	Nice	2014-07-15	CRM	2014-07-15			0	0	0		7
	5		778	Charlotte Nielsen	Vesterbrogade 8, Copenhagen	Copenhagen	2014-07-31	CRM	2014-07-31			1	0	0		7
	6		779	Unknown 779		Unknown	2014-08-30	CRM	2014-08-30			0	1	1		7
	7		901	Sara Taylor	2 Brick Lane, London	London	2014-07-31	CRM	2014-07-31			0	0	0		7
Dim.Customer
Customer_key	Customer_dkey	Customer_pkey	CustomerId_bkey	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit	ValidFrom_meta	ValidTo_meta	IsCurrent_meta	IsDeleted_meta	IsInferred_meta	CorrectionTime_audit	InsertExecutionLogId_audit	UpdateExecutionLogId_audit
-2	-2	0	-2	Unknown	Unknown	Unknown		DWH	1900-01-01	1900-01-01	9999-12-31	1	0	1		0
-1	-1	0	-1	Missing	Missing	Missing		DWH	1900-01-01	1900-01-01	9999-12-31	1	0	1		0
1	1	0	176	Hans Andersen	77 Oxford Street, London	London	1997-04-05	CRM	1997-04-05	1900-01-01	2006-01-29	0	0	0		4	6
2	2	0	359	Joe Watson	33 Acorn Street, Boston	Boston	2006-01-30	CRM	2006-01-30	1900-01-01	2014-07-01	0	0	0	2024-12-18 14:05:24.413	4	8
3	3	0	421	Marie Beaulieur	55 Rue Saint-Jean, Lyon	Lyon	2005-12-01	CRM	2005-12-01	1900-01-01	2007-11-16	0	0	0		4	6
4	4	0	777	Anna Jensen	Vesterbrogade 8, Copenhagen	Copenhagen	2006-01-31	CRM	2006-01-31	1900-01-01	9999-12-31	1	0	0	2024-12-18 14:05:24.413	4	6
5	5	0	778	Charlotte Nielsen	Vesterbrogade 8, Copenhagen	Copenhagen	2014-07-31	CRM	2014-07-31	1900-01-01	9999-12-31	1	0	0	2024-12-18 14:07:29.434	4	8
6	6	0	779	Unknown 779		Unknown		CRM	2006-01-01	1900-01-01	2014-08-30	0	0	1		4	8
7	1	1	176	Hans Andersen	77 Oxford Street, London	London	2006-01-29	CRM	2006-01-29	2006-01-29	9999-12-31	1	1	0		6
8	3	3	421	Marie Lesauvage	16 Rue le Tasse, Paris	Paris	2007-11-16	CRM	2007-11-16	2007-11-16	2014-07-15	0	0	0		6	8
9	2	2	359	Joe Watson	26 Branch Street, Boston	Boston	2014-07-01	CRM	2014-07-01	2014-07-01	9999-12-31	1	0	0		8
10	3	8	421	Marie Sainte	13 La Prom, Nice	Nice	2014-07-15	CRM	2014-07-15	2014-07-15	9999-12-31	1	0	0		8
11	6	6	779	Unknown 779		Unknown	2014-08-30	CRM	2014-08-30	2014-08-30	9999-12-31	1	1	1		8
12	7	0	901	Sara Taylor	2 Brick Lane, London	London	2014-07-31	CRM	2014-07-31	1900-01-01	9999-12-31	1	0	0		8
Three views with dimension data to join on a fact table or a fact view
Dims.Customer_Current as a current non-unique list of dimension data to join on fact table column Customer_key for type 2 and type 7														CREATE VIEW [Dims].[Customer_Current] AS
Customer_key	Customer_dkey		CustomerId	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit	IsDeleted_meta	IsInferred_meta	CorrectionTime_audit		SELECT
-2	-2		-2	Unknown	Unknown	Unknown		DWH	1900-01-01	0	1			d.[Customer_key]
-1	-1		-1	Missing	Missing	Missing		DWH	1900-01-01	0	1			,d.[Customer_dkey]
1	1		176	Hans Andersen	77 Oxford Street, London	London	2006-01-29	CRM	2006-01-29	1	0			,[CustomerId] = d.[CustomerId_bkey]
2	2		359	Joe Watson	26 Branch Street, Boston	Boston	2014-07-01	CRM	2014-07-01	0	0			,c.[CustomerName]
9	2		359	Joe Watson	26 Branch Street, Boston	Boston	2014-07-01	CRM	2014-07-01	0	0			,c.[Address]
3	3		421	Marie Sainte	13 La Prom, Nice	Nice	2014-07-15	CRM	2014-07-15	0	0			,c.[City]
8	3		421	Marie Sainte	13 La Prom, Nice	Nice	2014-07-15	CRM	2014-07-15	0	0			,c.[BusinessDate]
10	3		421	Marie Sainte	13 La Prom, Nice	Nice	2014-07-15	CRM	2014-07-15	0	0			FROM [Dim].[Customer] d
4	4		777	Anna Jensen	Vesterbrogade 8, Copenhagen	Copenhagen	2006-01-31	CRM	2006-01-31	0	0	2024-12-18 14:05:24.413		INNER JOIN
5	5		778	Charlotte Nielsen	Vesterbrogade 8, Copenhagen	Copenhagen	2014-07-31	CRM	2014-07-31	0	0	2024-12-18 14:07:29.434		(SELECT [Customer_dkey], [CustomerName], [Address], [City], [BusinessDate]
6	6		779	Unknown 779		Unknown	2014-08-30	CRM	2014-08-30	1	1			FROM [Dim].[Customer]
12	7		901	Sara Taylor	2 Brick Lane, London	London	2014-07-31	CRM	2014-07-31	0	0			WHERE IsCurrent_meta = 1
														) c ON c.Customer_dkey = d.Customer_dkey
														WHERE d.IsDeleted_meta = 0
Dims.Customer_Current_Unique as a current unique list of dimension data to join on fact table column Customer_dkey for type 7, good for a dropdown list box in Power BI with join path over durable key
	Customer_dkey		CustomerId	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit	IsDeleted_meta	IsInferred_meta	CorrectionTime_audit		CREATE VIEW [Dims].[Customer_Current_Unique] AS
	-2		-2	Unknown	Unknown	Unknown		DWH	1900-01-01	0	1			SELECT
	-1		-1	Missing	Missing	Missing		DWH	1900-01-01	0	1			[Customer_dkey]
	1		176	Hans Andersen	77 Oxford Street, London	London	2006-01-29	CRM	2006-01-29	1	0			,[CustomerId] = [CustomerId_bkey]
	2		359	Joe Watson	26 Branch Street, Boston	Boston	2014-07-01	CRM	2014-07-01	0	0			,[CustomerName]
	3		421	Marie Sainte	13 La Prom, Nice	Nice	2014-07-15	CRM	2014-07-15	0	0			,[Address]
	4		777	Anna Jensen	Vesterbrogade 8, Copenhagen	Copenhagen	2006-01-31	CRM	2006-01-31	0	0	2024-12-18 14:05:24.413		,[City]
	5		778	Charlotte Nielsen	Vesterbrogade 8, Copenhagen	Copenhagen	2014-07-31	CRM	2014-07-31	0	0	2024-12-18 14:07:29.434		,[BusinessDate]
	6		779	Unknown 779		Unknown	2014-08-30	CRM	2014-08-30	1	1			FROM [Dim].[Customer]
	7		901	Sara Taylor	2 Brick Lane, London	London	2014-07-31	CRM	2014-07-31	0	0			WHERE IsCurrent_meta = 1
Dims.Customer_Registered as a historical list of dimension data to join on fact table column Customer_key for type 2 and type 7
Customer_key	Customer_dkey		CustomerId	CustomerName	Address	City	BusinessDate	RecordSource_audit	DateOfChange_audit	IsDeleted_meta	IsInferred_meta	CorrectionTime_audit		CREATE VIEW [Dims].[Customer_Registered] AS
-2	-2		-2	Unknown	Unknown	Unknown		DWH	1900-01-01	0	1			SELECT
-1	-1		-1	Missing	Missing	Missing		DWH	1900-01-01	0	1			[Customer_key]
1	1		176	Hans Andersen	77 Oxford Street, London	London	1997-04-05	CRM	1997-04-05	0	0			,[Customer_dkey]
2	2		359	Joe Watson	33 Acorn Street, Boston	Boston	2006-01-30	CRM	2006-01-30	0	0	2024-12-18 14:05:24.413		,[CustomerId] = [CustomerId_bkey]
9	2		359	Joe Watson	26 Branch Street, Boston	Boston	2014-07-01	CRM	2014-07-01	0	0			,[CustomerName]
3	3		421	Marie Beaulieur	55 Rue Saint-Jean, Lyon	Lyon	2005-12-01	CRM	2005-12-01	0	0			,[Address]
8	3		421	Marie Lesauvage	16 Rue le Tasse, Paris	Paris	2007-11-16	CRM	2007-11-16	0	0			,[City]
10	3		421	Marie Sainte	13 La Prom, Nice	Nice	2014-07-15	CRM	2014-07-15	0	0			,[BusinessDate]
4	4		777	Anna Jensen	Vesterbrogade 8, Copenhagen	Copenhagen	2006-01-31	CRM	2006-01-31	0	0	2024-12-18 14:05:24.413		FROM [Dim].[Customer]
5	5		778	Charlotte Nielsen	Vesterbrogade 8, Copenhagen	Copenhagen	2014-07-31	CRM	2014-07-31	0	0	2024-12-18 14:07:29.434		WHERE IsDeleted_meta = 0
6	6		779	Unknown 779		Unknown		CRM	2006-01-01	0	1
12	7		901	Sara Taylor	2 Brick Lane, London	London	2014-07-31	CRM	2014-07-31	0	0
Please notice, that the dimension rows with marked IsDeleted_meta = 1, because the business key has been deleted in source system, is not included in the view because no fact data refer to them.