DEV Community: Dmitry

Starting Small

Dmitry — Sun, 08 Nov 2026 20:42:00 +0000

"A problem well stated is a problem half solved."
- Charles Kettering

How Much Data Do We Need?

Earlier, we examined a small but fairly substantial dataset. It's substantial in the sense that visually verifying even 177 products is a tedious task.

When debugging data pipeline logic, less than two dozen elements is more than enough, and the fewer the better. However, we shouldn't go too far in reducing data at the expense of functionality testing.

Let's consider how much data might be required using the "Customer" dimension as an example.

This dimension is implemented as a Slowly Changing Dimension Type 1 (SCD 1): it doesn't store history but simply overwrites attributes, keeping only their current state. And we need to verify several things:

That data loads as such. For the most basic check, one record is enough.
That if there are multiple records for a single customer, the most recent one by modification date is selected.

So minimally, we need three records: one for the general check and a second for verifying the selection of the most recent record within a group with a single key. In practice, it's better to have an additional pair of records for the second check, so we can verify that in a dataset with multiple groups, the algorithm can identify groups and select the most recent record from each group.

In total, we'll need a minimum of five customer records across three unique customers.

Data Pipelines

The original DWH from which the data was taken is designed only for a single full load. This mode is typical for data analysts' approach. However, in real-world data pipelines for organizational DWHs, updated data is loaded continuously, and the requirements for organizing fault-tolerant pipelines are different. In general, two main modes are typically implemented:

In the first mode of initial load, the DWH, which at that moment contains no data, is populated with available data. The initial load is performed once.

In the second mode, data is loaded as it changes in the sources.

In a specific case, the data source can be the same for both modes: for example, we have a csv file with data that is stored in some shared location and is updated over time. For instance, some records in it may be updated, while new ones are added. Schematically, this can be represented as follows:

On one side, there is a data creator who updates the file over time. On the other side, there is a data department with a DWH that needs to account for changes in the warehouse. This is exactly the variant with a file in a shared location that we will consider for the "Customer" dimension.

However, much more often data updates are built according to a different scheme:

Here, only those changes that occurred over a certain period of time fall into the incremental changes.

These changes reach the DWH either directly upon its request or through an intermediary in the form of a message broker, which buffers and stores data, coordinating the mutual work.

We will use the incremental load variant for updates for the "Product" dimension and for the "Sales" fact.

Thus, our DWH, unlike the original one, will support a hybrid architecture for continuously receiving updated data from sources. This is precisely why we reduced the number of tables to three and removed extra elements for the "snowflake" schema to simplify the elements for implementing more complex data pipeline logic.

Let's draw another diagram:

Here we have indicated that customer data comes from a file, while product and sales data is taken from a database.

We have also designated the entry points for this data for each object as Endpoints.

This abstraction of the input layer will allow us to simplify the task of combining initial and incremental loads: if the data format remains the same for both cases, then we only need to switch the sources:

The selectors simply switch data flows, leaving their content as is. The solid line represents the data flow for incremental load, while the dashed line shows the data flows for initial load. This gives us the ability to switch between initial and incremental loads without changing the processing algorithms. These are exactly the algorithms we are going to implement and debug.

Small data is needed for debugging the logic functionality, as it allows us to verify the work visually.

Data Simulator

To be able to work with small datasets, let's modify the diagram above and slightly rearrange the functional blocks:

The Data Simulator contains selectors and controls their logic. It also provides data to the input endpoints and allows simulating both initial and incremental loads.

The simulator will help us debug the pipeline algorithms by providing small datasets for convenient visual debugging.

But to understand how to implement it, we first need to consider what data we need to simulate and how.

Data Simulation

The data loading process into DWH, as mentioned above, can be described with the following diagram:

First, there is an initial load, which is performed once.
Then, multiple incremental loads follow, which update data changes.

In general, the Data Manipulation Language (DML) provides three operations:

INSERT - inserting new data that is not present in the DWH.
UPDATE - updating data that already exists in the DWH.
DELETE - deleting data from the DWH.

The last operation, namely DELETE, is often not applied directly to the DWH used for analytics, as historical data is also of interest for analysis. We will not consider this operation in the context of our DWH, but note that to keep data in the database and have the ability to determine whether the data is current or not, an additional flag can be introduced for each record. This flag can be used to determine if the data has been deleted. Such a flag implements "soft" deletion, marking records as deleted without physically removing them.

The remaining two operations are often referred to by a single word: UPSERT. In the case of incremental loading of changing data, UPSERT is used specifically. During the initial load, when there is no data in the database yet, using INSERT will be much faster, but UPSERT will also correctly load the data.

Thus, test data in general should work with the UPSERT operation. This will be the same data format as for the import in the previous part from a csv file, and if there are already rows with the same key in the table, the new data should update them.

If parts of the test data are equivalent to files, then they can be placed in files and selected as needed, for example for a product:

However, with a dozen test scenarios and three tables, the number of files would amount to three dozen, and managing them would be difficult, as would checking what changed in the data from one test scenario to another.

It is much more convenient to place all of this in a table with an additional field for the test scenario number, organize the output point as a VIEW, and implement the selection logic as a WHERE condition:

Then, with a condition like WHERE id = 1, the view will return data for the initial load, and with WHERE id = 2 it will return data for the first incremental load.

To control data portions for multiple sources at once, a separate table simTestLoadScenario can be organized with the following columns:

field	description
tls_id	scenario sequence number
tls_name	scenario name
tls_description	scenario description
tls_Customer_load_id	load part number for customers
tls_Product_load_id	load part number for products
tls_Sales_load_id	load part number for sales

By specifying load_id for different sources, you can combine different loads in a scenario. The initial load is considered load_id = 1. With load_id = 0, nothing will be loaded for a specific source, as such a part will be absent from the data table.

To select the current scenario, a second table simCurrentLoadScenario can be created with a strict constraint that it can only contain one row:

CREATE TABLE simCurrentLoadScenario (
  cls_id INTEGER PRIMARY KEY CHECK(cls_id = 1),
  tls_id INTEGER 
);

INSERT INTO simCurrentLoadScenario (cls_id, tls_id) VALUES (1,0);

Then, by updating the tls_id field in this table, you can switch scenarios, and the views for sources will look like this:

CREATE VIEW simProduct
AS
SELECT 
  pd.prd_id,
  pd.prd_key,
  pd.prd_nm,
  pd.prd_cost,
  pd.prd_start_dt,
  pd.prd_end_dt
FROM productData AS pd
INNER JOIN simTestLoadScenario AS tls
  ON tls.tls_Product_load_id = pd.load_id
INNER JOIN simCurrentLoadScenario AS cls
  ON cls.tls_id = tls.tls_id;

Similarly, views are created for other data, where the load_id field needs to be added to select a particular load portion.

This will allow us to switch scenarios and data for loading with a simple query like: UPDATE CurrentLoadScenario SET tls_id = 2. The sim prefix is added to all objects to group them by functional attribute, as SQLite does not support schemas.

Now that we have determined the implementation of the data simulator, we can create a basic set of scenarios for testing:

First, we test the "Customer" dimension. First the initial load, and then one or two incremental loads. The other dimensions and facts do not return any data from the sources at this point. Next, we test the "Product" dimension in a similar way. After both dimensions have been tested and loaded, we test the "Sales" fact.

After completing the detailed testing by objects, we clear the target database and re-test the initial and incremental loads with data present in all sources to verify the integrity of such a load.

For the tls_id field, not numbers are specified, but some sequentially ordered labels, as each object needs to be considered using an example of what data can be created for it.

Let's consider this in detail.

The "Customer" Dimension (the `customerData` table)

As we determined above, this dimension will behave as if it resided in an external file that is periodically updated according to the assumed model, where a separate department is responsible for this. Therefore, its data arrives in full, and the incremental load is effectively a complete reload on top of the existing data, which must be able to detect changes and apply them on its own. This dimension is also a Slowly Changing Dimension Type 1 (SCD 1) and does not preserve history: a new version overwrites the attributes by key.

If we look at the data from the previous part, we will see that the table contains a total of 18,483 records (including duplicates by key), and the business key falls within the range AW00011000 - AW00029483. The surrogate key corresponds to the number in the business key and has a value range of 11000 - 29483.

Let's create five rows of data for the initial load for this table, using numbers starting from 1001 with the prefix TV, which stands for Test Valid, that is, valid test data. For the first and last names, we will also choose values that start with T for the last name and V for the first name in all records, so that there are no matching first and last names in the original data. We will take dates within January 2025.

cst_id	cst_key	cst_firstname	cst_lastname	cst_create_date	load_id	notes
1001	TV00001001	Verity	Terry	2025-01-01	1	simple single record
1002	TV00001002	NULL	NULL	2025-01-01	1	first pair of duplicates by key
1002	TV00001002	Vivian	Turner	2025-01-02	1	first pair of duplicates by key
1003	TV00001003	NULL	Tucker	2025-01-04	1	second pair of duplicates by key
1003	TV00001003	V	Tucker	2025-01-05	1	second pair of duplicates by key

From this data, the following should remain as the final result:

the first row with id=1001
the third row with id=1002 and the date 2025-01-02
the fifth row with id=1003 and the date 2025-01-05

For the next load, we are going to make the following changes:

remove row 4 with the date 2025-01-04
add the next refinement for customer 1003 and set their full first name to "Vishal"
add one more customer
at the same time, the rest of the data must remain, since we are reloading the data

How can this be implemented? This can be done using bitmasks and the bitwise AND operation. For example, load blocks can be implemented as follows:

Block number	Block bitmask (value)	Load bitmask (value)	Load by mask
1	001(1)	001(1)	will load block 1
2	010(2)	011(3)	will load blocks 1 and 2
3	100(4)	111(7)	will load blocks 1, 2 and 3

In the bitmask, the binary 1 shifts from block to block from right to left: in the first block it is in the least significant bit, in the second - in the middle bit, and in the third - in the most significant bit. Then, if we apply the mask 011, both the first and the second blocks will be included, and with the mask 111 - all three blocks.

Indeed: 011 & 001 = 001 > 0 and 011 & 010 = 010 > 0.

If we need a block to be loaded only for one specific load, we can introduce an additional field for_mask and specify in it a value for an exact comparison operation with the mask, when the value of this field is not zero:

row	load_id	for_mask
1	1	0
2	1	1
3	2	0
4	2	3
5	4	0

Then, when loading the first block, where the mask value used is 1, rows 1 and 2 will be loaded.

When loading the second block, where the mask value is 3, rows 1, 3, and 4 will be loaded. The second row will not be loaded, since 1 is not equal to 3, while the fourth row is loaded, since 3 equals 3.

When loading the third block, the fourth row will be skipped, since 3 is not equal to 7. The second row will also be skipped, and rows 1, 3, and 5 will be loaded.

The complete condition for both fields is: (load_id & mask) > 0 AND (for_mask = 0 OR for_mask = mask).

Now we can extend the scenario for the second load:

cst_id	cst_key	cst_firstname	cst_lastname	cst_create_date	load_id	for_mask	notes
1001	TV00001001	Verity	Terry	2025-01-01	1	0	simple single record
1002	TV00001002	NULL	NULL	2025-01-01	1	0	first pair of duplicates by key
1002	TV00001002	Vivian	Turner	2025-01-02	1	0	first pair of duplicates by key
1003	TV00001003	NULL	Tucker	2025-01-04	1	1	removed in the second load
1003	TV00001003	V	Tucker	2025-01-05	1	0	second pair of duplicates by key
1003	TV00001003	Vishal	Tucker	2025-01-13	2	0	second load update
1004	TV00001004	V	T	2025-01-14	2	0	new record of the second load

The data we will obtain in the second load will look like this:

cst_id	cst_key	cst_firstname	cst_lastname	cst_create_date
1001	TV00001001	Verity	Terry	2025-01-01
1002	TV00001002	NULL	NULL	2025-01-01
1002	TV00001002	Vivian	Turner	2025-01-02
1003	TV00001003	V	Tucker	2025-01-05
1003	TV00001003	Vishal	Tucker	2025-01-13
1004	TV00001004	V	T	2025-01-14

That is, customer 1003 with the date 2025-01-04 will be absent from it.

In the third load, we will additionally update customer 1004:

cst_id	cst_key	cst_firstname	cst_lastname	cst_create_date	load_id	for_mask	notes
1001	TV00001001	Verity	Terry	2025-01-01	1	0	simple single record
1002	TV00001002	NULL	NULL	2025-01-01	1	0	first pair of duplicates by key
1002	TV00001002	Vivian	Turner	2025-01-02	1	0	first pair of duplicates by key
1003	TV00001003	NULL	Tucker	2025-01-04	1	1	removed in the second and third loads
1003	TV00001003	V	Tucker	2025-01-05	1	0	second pair of duplicates by key
1003	TV00001003	Vishal	Tucker	2025-01-13	2	0	second load update
1004	TV00001004	V	T	2025-01-14	2	0	new record of the second load
1004	TV00001004	Vivek	T	2025-02-15	4	0	third load update
1004	TV00001004	Vivek	Tripp	2025-02-16	4	0	third load update

This is the complete content of the table for all three loads.

In the simTestLoadScenario table, the tls_Customer_load_id field will use exactly the load bitmask:

tls_id	tls_name	tls_description	tls_Customer_load_id
1	C-VT01	Customer valid test initial load	1
2	C-VT02	Customer valid test incremental load 1	3
3	C-VT03	Customer valid test incremental load 2	7

The "Product" Dimension (the `productData` table)

The surrogate key values in the product table start from 212. We will use values starting from 101. The product code is formed from the brand name, product type, some digits, and symbols indicating the size.

Let's come up with three brands: "SporticsDog", "Mike and Cat", and "JJ Run", and name the product "Track Vest" (using the prefix TV based on the first letters).

Then, for the first load, we will create one product with a closed validity interval, a second one - with an open interval, and a product with two intervals:

prd_id	prd_key	prd_nm	prd_cost	prd_start_dt	prd_end_dt	load_id
101	TV-SD10-L	Track Vest SporticsDog - L	10	2025-01-01	2025-03-01	1
102	TV-MC70-S	Track Vest Mike and Cat - S	5	2025-01-02	NULL	1
103	TV-JJ17-XS	Track Vest JJ Run - XS	7	2025-01-03	2025-02-05	1
104	TV-JJ17-XS	Track Vest JJ Run - XS	9	2025-02-05	NULL	1

In the product dimension, changes are incremental, so bitmasks are not required for incremental loads. For the first incremental load, we will make the following changes:

Add a new validity interval for the product TV-MC70-S.
Add one more product.

prd_id	prd_key	prd_nm	prd_cost	prd_start_dt	prd_end_dt	load_id
102	TV-MC70-S	Track Vest Mike and Cat - S	5	2025-01-02	2025-02-05	2
105	TV-MC70-S	Track Vest Mike and Cat - S	7	2025-02-05	NULL	2
106	TV-JJ17-XL	Track Vest JJ Run - XL	8	2025-02-03	NULL	2

As we can see, to close the previous interval, we use the existing surrogate key 102, setting a new end date for it, and for the next interval, we use a new surrogate key 105. For a completely new row, we use the next sequential surrogate key 106.

In the second incremental load, we will update the intervals for two products:

open a new interval for TV-JJ17-XS.
close the interval for TV-JJ17-XL.

prd_id	prd_key	prd_nm	prd_cost	prd_start_dt	prd_end_dt	load_id
104	TV-JJ17-XS	Track Vest JJ Run - XS	9	2025-02-05	2025-03-03	3
107	TV-JJ17-XS	Track Vest JJ Run - XS	11	2025-03-03	NULL	3
106	TV-JJ17-XL	Track Vest JJ Run - XL	8	2025-02-03	2025-04-03	3

Overall, this dataset is quite sufficient to test this dimension.

The "Sales" Fact Table (the `salesData` table)

The business key of an order in this table starts with SO (Sales Order) and falls within the range SO43697 - SO75123.
We will use the prefix TVSO and start from 101, for example, TVSO101.

This table is the simplest in terms of loading, since data is only appended to it. It is only necessary to take the product validity interval into account. For the customer identifier, we can use just a few values covering both records without duplicates and records with duplicates.

For the initial load, we will take two orders, each containing two products, from two customers:

sls_ord_num	sls_prd_key	sls_cust_id	sls_order_dt	sls_ship_dt	sls_due_dt	sls_sales	sls_quantity	sls_price	load_id
TVSO101	TV-SD10-L	1001	20250104	20250106	20250109	10	1	10	1
TVSO101	TV-JJ17-XS	1001	20250104	20250106	20250109	14	2	7	1
TVSO102	TV-SD10-L	1002	20250105	20250109	20250111	10	1	10	1
TVSO102	TV-MC70-S	1002	20250105	20250109	20250111	5	1	5	1

For the first incremental load, we will add orders for products whose prices changed in February, for the same customers:

sls_ord_num	sls_prd_key	sls_cust_id	sls_order_dt	sls_ship_dt	sls_due_dt	sls_sales	sls_quantity	sls_price	load_id
TVSO103	TV-JJ17-XL	1001	20250206	20250208	20250209	8	1	8	2
TVSO103	TV-MC70-S	1001	20250206	20250208	20250209	14	2	7	2
TVSO104	TV-JJ17-XL	1002	20250207	20250209	20250211	9	1	9	2
TVSO104	TV-MC70-S	1002	20250207	20250209	20250211	7	1	7	2

For the second incremental load, we will take products updated in March and use a couple of other customers:

sls_ord_num	sls_prd_key	sls_cust_id	sls_order_dt	sls_ship_dt	sls_due_dt	sls_sales	sls_quantity	sls_price	load_id
TVSO105	TV-JJ17-XL	1003	20250306	20250308	20250309	8	1	8	3
TVSO105	TV-JJ17-XS	1003	20250306	20250308	20250309	22	2	11	3
TVSO106	TV-JJ17-XL	1004	20250307	20250309	20250311	16	2	8	3
TVSO106	TV-JJ17-XS	1004	20250307	20250309	20250311	11	1	11	3

All Data Together

A small dataset is convenient because it allows for easy visual verification of data integrity: it can fit on a single spreadsheet page:

As clearly shown in the image, it is easy to highlight logically related cells with different colors and verify the correctness of the dataset. Additionally, three dates are highlighted in different colors (green, purple, blue) following the ETL loads marker.
These dates represent simulated ETL load dates:

During the first (initial) load, data up to 2025-01-08 is added (highlighted in green).
During the second (first incremental) load, data with dates 2025-01-09 - 2025-02-08 is added (highlighted in purple).
During the third (second incremental) load, data with dates 2025-02-09 - 2025-03-08 is added (highlighted in blue).

In this case, the sales dates follow the load dates for both the product and the customer. The dates are chosen purely for visual clarity, with a one-month interval.

The complete simTestLoadScenario table looks like this:

tls_id	tls_name	tls_description	tls_Customer_load_id	tls_Product_load_id	tls_Sales_load_id
1	C-VT01	Customer valid test initial load	1	0	0
2	C-VT02	Customer valid test incremental load 1	3	0	0
3	C-VT03	Customer valid test incremental load 2	7	0	0
4	P-VT01	Product valid test initial load	0	1	0
5	P-VT02	Product valid test incremental load 1	0	2	0
6	P-VT03	Product valid test incremental load 2	0	3	0
7	S-VT01	Sales valid test initial load	0	0	1
8	S-VT02	Sales valid test incremental load 1	0	0	2
9	S-VT03	Sales valid test incremental load 2	0	0	3
10	CLR	Clear loaded data	0	0	0
11	A-VT01	All data valid test initial load	1	1	1
12	A-VT02	All data valid test incremental load 1	3	2	2
13	A-VT03	All data valid test incremental load 2	7	3	3

Identifier 10 is reserved for clearing the loaded data between detailed single-table loads and simultaneous all-table loads, allowing the process to be restarted from the initial load.

Conclusions

We have explored the process of creating small test datasets and a data simulator. The simulator allows us to simulate both initial and incremental loads. Such simulation is possible if the data formats for the initial and incremental loads are identical. A small dataset allows for visual verification of data correctness during loading and is highly convenient for implementing and debugging data pipeline logic in real-world DWHs. For this type of debugging, SQLite is perfectly sufficient. The data cleaned in the previous article will be used later to test the fully built pipeline.

References

Source code for this article: DWH Step By Step Part 2
Part 1: Preparing Clean Data for a DWH

Preparing Clean Data for a DWH

Dmitry — Sun, 25 Oct 2026 18:18:13 +0000

"On two occasions I have been asked, 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question."
Charles Babbage, 1864

The "Product-Sales-Customer" model is a classic example of a star schema for a Data Warehouse (DWH) according to Ralph Kimball's methodology. The underlying business process is intuitively understandable to almost anyone.

When developing an ETL pipeline (data transformation pipeline), a data engineer needs reliable source data on which to safely design and debug the logic.

An excellent dataset for such tasks can be found in the GitHub repository prepared by Baraa Khatib Salkini: sql-data-warehouse-project.

The source data is stored in the 01_datasets/source_crm directory, and its main structure can be described by the following diagram:

As can be seen from the diagram, the model consists of one fact table and two dimension tables. The "Product" dimension is implemented as a Slowly Changing Dimension Type 2 (SCD 2), meaning it stores the full history of changes. The "Customer" dimension, on the contrary, is implemented as a Slowly Changing Dimension Type 1 (SCD 1): it does not store history, but simply overwrites attributes, keeping only their current state.

Overall, this is an excellent dataset containing 18,495 customers, 397 products, and 60,398 records in the sales fact table.

However, in its raw form, this data has quality and integrity issues. The purpose of this article is to describe how to clean up this data so that it can be correctly used for DWH modeling and configuring the ETL pipeline.

Data Structure Description

"Product" Dimension (`prd_info` table)

field	description
prd_id	surrogate product key
prd_key	product business key
prd_nm	product name
prd_cost	product cost
prd_line	product line
prd_start_dt	validity period start date
prd_end_dt	validity period end date

For different validity periods of the same product, the business key remains unchanged, while the surrogate key changes. The currently active record has a NULL value in the end date field.

Example of the first few records from the table:

prd_id	prd_key	prd_nm	prd_cost	prd_line	prd_start_dt	prd_end_dt
210	CO-RF-FR-R92B-58	HL Road Frame - Black- 58		R	2003-07-01	NULL
211	CO-RF-FR-R92R-58	HL Road Frame - Red- 58		R	2003-07-01	NULL
212	AC-HE-HL-U509-R	Sport-100 Helmet- Red	12	S	2011-07-01	2007-12-28
213	AC-HE-HL-U509-R	Sport-100 Helmet- Red	14	S	2012-07-01	2008-12-27
214	AC-HE-HL-U509-R	Sport-100 Helmet- Red	13	S	2013-07-01	NULL

Right here, we can already notice data quality issues:

The product cost is missing in the first two rows.
The validity period end date is earlier than its start date.
Even if we swap these dates, the validity periods will still overlap.

Let's note these errors as tasks that require fixing directly in the data.

"Customer" Dimension (`cust_info` table)

field	description
cst_id	surrogate customer key
cst_key	customer business key
cst_firstname	customer first name
cst_lastname	customer last name
cst_marital_status	customer marital status
cst_gndr	customer gender
cst_create_date	attribute creation or update date

Example of a few records from the table (at the beginning and in the middle of the sample):

cst_id	cst_key	cst_firstname	cst_lastname	cst_marital_status	cst_gndr	cst_create_date
11000	AW00011000	Jon	Yang	M	M	2025-10-06
11001	AW00011001	Eugene	Huang	S	M	2025-10-02
29466	AW00029466	NULL	NULL	M	NULL	2026-01-25
29466	AW00029466	Lance	Jimenez	M	NULL	2026-01-26
29466	AW00029466	Lance	Jimenez	M	M	2026-01-27

It is worth noting here that this is a Type 1 SCD dimension. For the same combination of surrogate and business keys, there are multiple records, but for analytics, only the latest one (with the maximum date) should be used.

"Sales" Fact Table (`sales_details` table)

field	description
sls_ord_num	order business key
sls_prd_key	product business key
sls_cust_id	surrogate customer key
sls_order_dt	order date
sls_ship_dt	shipping date
sls_due_dt	planned delivery date
sls_quantity	quantity
sls_price	price per unit
sls_sales	sales amount

Example of a few records from the table (at the beginning and in the middle of the sample):

sls_ord_num	sls_prd_key	sls_cust_id	sls_order_dt	sls_ship_dt	sls_due_dt	sls_sales	sls_quantity	sls_price
SO43697	BK-R93R-62	21768	20101229	20110105	20110110	3578	1	3578
SO43698	BK-M82S-44	28389	20101229	20110105	20110110	3400	1	3400
SO43699	BK-M82S-44	25863	20101229	20110105	20110110	3400	1	3400
SO51181	HL-U509-R	12132	20121228	20130104	20130109	35	1	35
SO51182	BK-R79Y-42	22998	20121228	20130104	20130109	1701	1	1701

The following features are noticeable here:

The product key in the fact table is shortened: HL-U509-R is used instead of the full key AC-HE-HL-U509-R.
Dates are presented in a different format: without using a hyphen as a separator.
The product cost does not match: the fact table shows a price of 35, while the maximum price for this product in the product dimension is 13.

The answer to the first observation lies in the data structure: the product category is encoded at the beginning of the product dimension's business key. This can be seen in the category reference file source_erp/PX_CAT_G1V2.csv, which contains the following information:

ID	CAT	SUBCAT	MAINTENANCE
AC_HE	Accessories	Helmets	Yes

Data Correction

So, we have reviewed the data structure and identified errors within it. Our task is to correct this data.

But first, we will simplify the structure to leave only three entities, as shown in the diagram above:

We will remove categories and the product line. With these additional attributes, the data schema would turn into a "Snowflake" schema, so for simplicity, we will omit them. To do this:
- Remove the prd_line field from the prd_info table.
- Cut the first 6 characters from the business key prd_key, leaving only the product code.
Remove the cst_marital_status and cst_gndr fields from the cust_info table for the same reason.

We will perform all operations in a SQLite database, as the data volume is relatively small and a single-user engine is more than enough for us. The correction process can be described by the following diagram using the prd_info table as an example:

The sequence of actions is as follows:

Import the prd_info table from the prd_info.csv file.
Copy the required fields into the Product table, where we also create several auxiliary fields. In doing so, some fields are populated immediately during the copy process.
Using a sequence of queries, we populate the remaining auxiliary fields by generating the necessary data in them.
Then, we update the main data, using the auxiliary fields as variables for the correction algorithms.
Upon completion of the correction, before writing to files, we delete the auxiliary fields. Since they may also be needed for correcting other tables (e.g., the fact table), they are deleted only after all tables have been processed.

It should be noted that the majority of the data that needs to be corrected consists of dates. We will regenerate them randomly, but in a way that keeps them consistent. The SQLite query language does not support procedural programming (variables, conditions, loops), so the initial constants for dates and their ranges can be set using a CTE (Common Table Expression):

WITH 
bounds AS (
    SELECT 
        '2025-01-01' AS A,  -- Start date
        365 AS B            -- Generation interval in days
)

Here we set the start date bounds.A (2025-01-01) and the generation range for the entire year of 2025 - 365 days from the start date (bounds.B).
Now, let's look at the data correction for each table separately.

Correction of `cust_info` Table Data

Analysis of `cust_info` Table Data

Let's load the data from the cust_info.csv file into a database table, for example data_fix.db (the name can be anything), specifying that missing values during import should be replaced with NULL.

After that, let's run a simple analytical query:

SELECT 
  COUNT(*) AS TotalRows,
  COUNT(DISTINCT cst_id) AS UniqueIds,
  COUNT(DISTINCT cst_key) AS UniqueKeys,
  COUNT(DISTINCT cst_id || '_' || cst_key) AS UniqueIdAndKey,
  COUNT(*) - COUNT(cst_id) AS IdNullCount,
  COUNT(*) - COUNT(cst_key) AS KeyNullCount,
  COUNT(*) - COUNT(cst_create_date) AS CreatedDateNullCount
FROM cust_info;

Results:

TotalRows	UniqueIds	UniqueKeys	UniqueIdAndKey	IdNullCount	KeyNullCount	CreatedDateNullCount
18494	18484	18488	18484	4	0	4

As you can see from the results, we have four rows with an empty surrogate key, while there are no rows with an empty business key.

These rows can be displayed separately by running the query SELECT * FROM cust_info WHERE cst_id IS NULL:

cst_id	cst_key	cst_firstname	cst_lastname	cst_marital_status	cst_gndr	cst_create_date
	SF566
	PO25
	13451235
	A01Ass

It is evident that, apart from business keys, there are no other attributes for these records. All empty values are NULL.

All these rows can be safely deleted from the table, as the fact table uses the surrogate key to link with customers, which is absent in these records. That is, these records will not affect the facts in any way.

Let's modify the query to exclude such records and count duplicates:

SELECT 
  COUNT(*) AS TotalRows,
  COUNT(DISTINCT cst_id) AS UniqueIds,
  COUNT(DISTINCT cst_key) AS UniqueKeys,
  COUNT(DISTINCT cst_id || '_' || cst_key) AS UniqueIdAndKey,
  COUNT(*) - COUNT(DISTINCT cst_id) AS IdDuplicatesCount,
  COUNT(*) - COUNT(DISTINCT cst_key) AS KeyDuplicatesCount,
  COUNT(*) - COUNT(DISTINCT cst_id || '_' || cst_key) AS IdAndKeyDuplicatesCount,
  COUNT(*) - COUNT(cst_create_date) AS CreatedDateNullCount
FROM cust_info
WHERE cst_id IS NOT NULL;

Result:

TotalRows	UniqueIds	UniqueKeys	UniqueIdAndKey	IdDuplicatesCount	KeyDuplicatesCount	IdAndKeyDuplicatesCount	CreatedDateNullCount
18490	18484	18484	18484	6	6	6	0

As you can see, there are six duplicate records based on the combination of surrogate and business keys. At the same time, when counting unique combinations, the duplicate rows themselves are also taken into account. The distribution of duplicates by their count for each surrogate key can be calculated as follows:

SELECT
  cst_id,
  COUNT(*) AS dupsCount,
  MIN(cst_create_date) AS minCreationDate,
  MAX(cst_create_date) AS maxCreationDate,
  julianday(MAX(cst_create_date)) - julianday(MIN(cst_create_date)) AS maxDateDiff
FROM cust_info
WHERE cst_id IS NOT NULL
GROUP BY cst_id
HAVING COUNT(*) > 1;

Result:

cst_id	dupsCount	minCreationDate	maxCreationDate	maxDateDiff
29433	2	2026-01-25	2026-01-27	2
29449	2	2026-01-25	2026-01-26	1
29466	3	2026-01-25	2026-01-27	2
29473	2	2026-01-25	2026-01-26	1
29483	2	2026-01-25	2026-01-27	2

As you can see, the maximum number of duplicates for a single surrogate key can be 3. There is only one such group, while the rest contain two records each. The interval between creation dates in these groups is a maximum of two days.

Thus, if we generate records randomly within the year 2025, we can safely use a difference of one day for duplicates. Additionally, we have the ability to use sorting by creation date for ranking records.

Now we have all the necessary information to generate new modification dates for customers.

Creating the `Customer` Table

Earlier, we decided to remove the cst_marital_status and cst_gndr fields. Instead, we will add three auxiliary fields:

Field	Description
dupsCnt	Number of duplicates for the surrogate key
dupNo	Duplicate number, sorted by creation date
newDate	New random creation date

We save the new date in an additional column so we can verify the data without overwriting the original dates.

The full query looks like this:

CREATE TABLE Customer AS
WITH
bounds AS (
    SELECT '2025-01-01' AS A, 365 AS B
)
SELECT 
  cst_id,
  cst_key,
  cst_firstname,
  cst_lastname,
  cst_create_date,
  COUNT(*) OVER(PARTITION BY cst_id) AS dupCnt,
  ROW_NUMBER() OVER(PARTITION BY cst_id ORDER BY cst_create_date ASC) AS dupNo,
  date(bounds.A, '+' || (ABS(RANDOM()) % bounds.B) || ' days') AS newDate
FROM cust_info, bounds
WHERE cst_id IS NOT NULL;

Now, all dates in the newDate column fall within the year 2025. However, for duplicates, they might not be sequential (i.e., not exactly one day apart), which is easy to see by running the query SELECT * FROM Customer WHERE dupCnt > 1;.

Since the number of duplicates does not exceed three, this situation can be fixed using two queries:

UPDATE Customer
SET
   newDate = date(prev.newDate, '+1 days')
FROM Customer AS prev
WHERE Customer.cst_id = prev.cst_id
AND Customer.dupNo = 2
AND prev.dupNo = 1
AND prev.dupCnt > 1;

UPDATE Customer
SET
   newDate = date(prev.newDate, '+1 days')
FROM Customer AS prev
WHERE Customer.cst_id = prev.cst_id
AND Customer.dupNo = 3
AND prev.dupNo = 2
AND prev.dupCnt > 2;

After executing these queries, you can verify that the dates for duplicates of the same customer are spaced exactly one day apart.

There are two steps left. The first step will transfer the new date from the newDate field to the original cst_create_date field:

UPDATE Customer
SET cst_create_date = newDate;

The following queries will remove the auxiliary fields that were used during the calculations:

ALTER TABLE Customer DROP COLUMN dupCnt;
ALTER TABLE Customer DROP COLUMN dupNo;
ALTER TABLE Customer DROP COLUMN newDate;

The final verification query requires the sales_details fact table to be loaded into the database as well:

SELECT * FROM sales_details
WHERE sls_cust_id NOT IN ( SELECT cst_id FROM Customer );

It returns no records. This means that all customers are used in the fact table, and nothing needs to be deleted from the "Customer" dimension.

Now, we can export the table to a Customer.csv file with column headers, keeping NULL values as empty strings.

This concludes the correction of the "Customer" dimension.

Data Correction for the `prd_info` Table

Analysis of `prd_info` Table Data

First, let's check the table using a query similar to the initial statistics query for the cust_info table:

TotalRows	UniqueIds	UniqueKeys	UniqueIdAndKey	IdNullCount	KeyNullCount	StartDateNullCount
397	397	295	397	0	0	0

As you can see from the results, the number of unique surrogate keys equals the total number of rows in the table. Furthermore, the combination of the surrogate key and the business key is also unique across the entire table. There are no records with empty values for either the surrogate key or the business key. Moreover, there are no NULL values in the interval start date.

Note: all statistical queries are available in the repository within the stat_queries.sql file.

As for the number of possible intervals and whether they are open or closed, this data can be obtained using another statistical query:

WITH group_stats AS (
    -- calc each group
    SELECT 
        prd_key,
        COUNT(*) AS TotalInGroup,
        MAX(prd_id) AS FinalPrdId
    FROM prd_info
    GROUP BY prd_key
),
final_null_stat AS
(
  -- add HasFinalNulls to check if last prd_end_dt is NULL
  SELECT 
     group_stats.prd_key,
     group_stats.TotalInGroup,
     CASE WHEN prd_info.prd_end_dt IS NULL THEN 1 ELSE 0 END AS HasFinalNulls 
  FROM group_stats
  INNER JOIN prd_info
    ON prd_info.prd_id = group_stats.FinalPrdId
)
SELECT 
    TotalInGroup,
    COUNT(*) AS groupsCount,
    COUNT(*) - SUM(HasFinalNulls) AS TotalNotNullEndDateCount,
    SUM(HasFinalNulls) AS TotalNullEndDateCount
FROM final_null_stat
GROUP BY TotalInGroup;

The query returns the following data:

TotalInGroup	groupsCount	TotalNotNullEndDateCount	TotalNullEndDateCount
1	218	80	138
2	52	18	34
3	25	0	25

As you can see, the maximum number of records in a group is three. At the same time, all groups with three records have an open interval: the last prd_end_dt date is NULL. Among the groups with two records, there are both open and closed intervals, meaning they have a specific finite end date for prd_end_dt.

Creating the `Product` Table

To correctly generate intervals for products given these input conditions, we will need three random dates, and they must be sequential so that SCD 2 is filled correctly.

For this, in the processing table, we will introduce not one, but three dates, and we will fill them in a more complex way.

We will add seven auxiliary fields:

field	description
recCnt	number of records in the group for the business key
id	sequential number of the record in the group, sorted by surrogate key
date1	new random date 1
date2	new random date 2, such that date2 > date1
date3	new random date 3, such that date3 > date2
min_dt	minimum new date among records in the group
max_dt	maximum new date among records in the group

Since we have no duplicates, slightly different names are used for the number of records in the group and the sequential number of the record.

The full query looks like this:

CREATE TABLE Product AS
SELECT 
  prd_id 
  , SUBSTR(prd_key, 7) AS prd_key
  , prd_nm
  , prd_cost
  , prd_start_dt
  , prd_end_dt
  , COUNT(*) OVER(PARTITION BY prd_key) AS recCnt
  , ROW_NUMBER() OVER ( PARTITION BY prd_key ORDER BY prd_id ASC) AS id
  , CAST(NULL AS TEXT) AS date1
  , CAST(NULL AS TEXT) AS date2
  , CAST(NULL AS TEXT) AS date3
  , CAST(NULL AS TEXT) AS min_dt
  , CAST(NULL AS TEXT) AS max_dt    
FROM prd_info;

We leave all new dates empty for now, we trim the business key to remove categories, and we do not include the product line.

We can immediately delete from the products those records that are not represented in the facts:

DELETE FROM Product
WHERE prd_key NOT IN (SELECT sls_prd_key FROM sales_details);

This will delete 220 rows, leaving 177 that are linked to facts. This will also delete records with empty prices.

The next step is to generate three dates. Since procedural capabilities are not used, we can generate them for each business key using a recursive CTE with a limit of three rows. The result will be in the form of "sequential number-date", and this needs to be transposed into additional columns. SQLite does not support PIVOT, so we transpose manually using FILTER and aggregate functions:

WITH RECURSIVE
-- 1. get unique business keys
target_list AS (
   SELECT DISTINCT prd_key
   FROM Product
),
-- 2. range for dates generation
bounds AS (
    SELECT '2025-01-01' AS A, 365 AS B
),
-- 3. recursive generator for each key
generator(prd_key, idx, date_result) AS (
    -- first date
    SELECT 
        t.prd_key, 
        1, 
        date(b.A, '+' || (ABS(RANDOM() + t.prd_key) % b.B) || ' days')
    FROM target_list t, bounds b
    UNION ALL
    -- next dates, up to 3 for each key
    SELECT 
        g.prd_key,
        g.idx + 1,
        date(b.A, '+' || (ABS(RANDOM() + g.idx) % b.B) || ' days')
    FROM generator g, bounds b
    WHERE g.idx < 3
),
-- 4. remove duplicates for each key
unique_dates AS (
    SELECT prd_key, date_result 
    FROM generator 
    GROUP BY prd_key, date_result
),
-- 5. final output with numbering (1, 2, 3) for each key
num_data AS ( 
 SELECT 
    prd_key,
    ROW_NUMBER() OVER (PARTITION BY prd_key ORDER BY date_result ASC) AS genId,
    date_result
 FROM unique_dates
)
,
final_data AS (
  SELECT prd_key,
    MAX(date_result) FILTER (WHERE genId = 1) AS [date1],
    MAX(date_result) FILTER (WHERE genId = 2) AS [date2],
    MAX(date_result) FILTER (WHERE genId = 3) AS [date3]
  FROM num_data
  GROUP BY prd_key
)
UPDATE Product
SET 
   date1 = final_data.date1
   , date2 = final_data.date2
   , date3 = final_data.date3
FROM final_data
WHERE Product.prd_key = final_data.prd_key;

Note: The random number generation algorithm used in the query does not guarantee the generation of all three sequential dates: there is a possibility that one or two dates may be NULL due to the random selection of the first date within the specified range. To verify this, use the following query:

SELECT * FROM Product
WHERE date1 IS NULL OR date2 IS NULL OR date3 IS NULL;

If it returns any rows, the date generation query should be executed again.

Now we have the date1, date2, date3 fields filled, and we need to fill prd_start_dt and prd_end_dt accordingly.

The following diagram shows how this is done for a group of three records with an open interval:

As you can see, we fill prd_start_dt from date1 for the first record. The value from date2 is used both for prd_start_dt of the second record and for prd_end_dt of the first. The value from date3 is used both for prd_start_dt of the third record and for prd_end_dt of the second.

If we have a closed interval for two records, the same algorithm applies.

In the general case, here are three queries that implement this algorithm, taking into account records with NULL:

UPDATE Product
SET
  prd_start_dt = date1
  ,prd_end_dt = CASE WHEN prd_end_dt IS NOT NULL THEN date2 ELSE NULL END
WHERE id = 1;

UPDATE Product
SET
  prd_start_dt = date2
  ,prd_end_dt = CASE WHEN prd_end_dt IS NOT NULL THEN date3 ELSE NULL END
WHERE id = 2;

UPDATE Product
SET
  prd_start_dt = date3
WHERE id = 3;

The presence of the auxiliary field recCnt, strictly speaking, is not required for the algorithm, but it allows you to quickly make slices by groups and verify its correctness.

The last fields min_dt and max_dt are not needed for correcting the Product table itself. They are used for correcting the sales table.

They are filled with a separate query, assuming that the maximum date for the generation range, if the interval is open - the last day of 2025:

WITH _rngs AS
(
SELECT prd_key,
   MIN ( prd_start_dt ) AS mn,
   MAX ( COALESCE( prd_end_dt, '2025-12-31')) AS mx
FROM Product
GROUP BY prd_key
)
UPDATE Product
SET 
   min_dt = _rngs.mn
   , max_dt = _rngs.mx
FROM _rngs
WHERE Product.prd_key = _rngs.prd_key;

The Product table is ready for export. But we will not delete the auxiliary fields in it and export it until we correct the Sales table.

Data Correction for `sales_details` Table

Analysis of `sales_details` Table Data

First, let's check the table using a query similar to the initial statistics query for the cust_info table:

SELECT 
  COUNT(*) AS TotalRows,
  COUNT(DISTINCT sls_ord_num) AS UniqueIds,
  COUNT(DISTINCT sls_prd_key) AS UniqueProductKeys,
  COUNT(DISTINCT sls_cust_id) AS UniqueCustKeys,
  COUNT(DISTINCT sls_prd_key || '_' || sls_cust_id) AS UniqueProdAndCust,
  COUNT(*) - COUNT(sls_ord_num) AS IdsNullCount,
  COUNT(*) - COUNT(sls_prd_key) AS ProductKeysNullCount,
  COUNT(*) - COUNT(sls_cust_id) AS CustKeysNullCount,
  COUNT(*) - COUNT(sls_order_dt) AS OrderDateNullCount,
  COUNT(*) - COUNT(sls_quantity) AS QuantityNullCount
FROM sales_details;

Result:

TotalRows	UniqueIds	UniqueProductKeys	UniqueCustKeys	UniqueProdAndCust	IdsNullCount	ProductKeysNullCount	CustKeysNullCount	OrderDateNullCount	QuantityNullCount
60398	27659	130	18484	58922	0	0	0	0	0

As you can see, with 60,398 rows in the table, there are only 27,659 unique business keys. There are no records with empty business keys or records without references to products or customers. There are also no NULL values in the quantity of purchased goods or the order date.

The fact that there are fewer business keys deserves separate consideration. Let's generate statistics on the number of records per business key:

WITH group_stats AS (
    -- calc each group
  SELECT 
    sls_ord_num,
    COUNT(*) AS groupCount
  FROM sales_details
  GROUP BY sls_ord_num
)
SELECT 
    groupCount AS TotalInGroup,
    COUNT(*) AS GroupsCount
FROM group_stats
GROUP BY groupCount;

Result:

TotalInGroup	GroupsCount
1	9668
2	8163
3	5870
4	3138
5	696
6	109
7	12
8	3

As you can see, there can be up to 8 records in a group with a single business key. An example of such a group:

sls_ord_num	sls_prd_key	sls_cust_id	sls_order_dt	sls_ship_dt	sls_due_dt	sls_sales	sls_quantity	sls_price
SO54115	TI-T723	27202	20130309	20130316	20130321	29	1	29
SO54115	TT-T092	27202	20130309	20130316	20130321	5	1	5
SO54115	HY-1023-70	27202	20130309	20130316	20130321	55	1	55
SO54115	CL-9009	27202	20130309	20130316	20130321	8	1	8

It is obvious that one order represents a sale of several different products to one customer at once.

And these data can create a problem for us: for all four of these products, the date interval in which they are valid must overlap. And it is precisely in the interval of intersection of these dates that we need to generate a new order date from 2025. The same should be true for all groups except the group of one product, since there we can generate the order date directly from the product's interval.

To check this, we will create an additional table sales_check, into which we will take part of the data from the sales table and part of the data from the products table:

CREATE TABLE sales_check
AS
SELECT 
 sls_ord_num
 ,sls_prd_key
 ,sls_cust_id
 , Product.min_dt
 , Product.max_dt
 , Product.recCnt AS recCntProduct
 , COUNT(*) OVER(PARTITION BY sls_ord_num) AS recCntSales
 , ROW_NUMBER() OVER ( PARTITION BY sls_ord_num ORDER BY sls_prd_key ASC) AS idSales
FROM sales_details
INNER JOIN Product 
  ON Product.prd_key = sales_details.sls_prd_key
  AND  Product.id = 1;

We take only one row from the products table by Product.id = 1, since the maximum and minimum dates are duplicated for all rows in the group by product change. Now we are only interested in the upper date for a group of more than one product:

SELECT DISTINCT max_dt
FROM sales_check
WHERE recCntSales > 1;

This query will return a single value 2025-12-31, which we set as the maximum for the current date of the open interval instead of NULL in the product table. Therefore, in order for all products in the group to be current, we need to take the maximum date from min_dt for generating a random purchase date and use the interval from it to 2025-12-31.

Hence the recommendation: if you need to generate similar data after generating random product validity intervals according to SCD2, it makes sense for such orders with the purchase of several products at the same time to use an open interval for such products, so as not to have to regenerate dates for them.

If you change the condition to WHERE recCntSales = 1, you can see that there will be many different dates, which indicates a closed interval for many products. If you check the number of intervals for the product dimension recCntProduct for groups of more than one product, you can see that it includes the entire possible set from one to three.

Creating the `Sales` Table

Since dates are identical within an order, it makes sense to create a separate table sales_groups_dates for new dates specifically by order.

We will also include the following columns in it:

field	description
recCntSales	to control the group size
startDate	start date of the new date generation interval
endDate	end date of the new date generation interval
newOrderDate	new random order creation date
newShipDate	new random shipping date
newDueDate	new random planned delivery date

Let's fill this table:

CREATE TABLE sales_groups_dates
AS
WITH _maxD AS
(
  SELECT 
    sls_ord_num
    , MAX(min_dt) AS startD
  FROM sales_check
  GROUP BY sls_ord_num
)
SELECT 
  _maxD.sls_ord_num
  , sales_check.recCntSales
  , _maxD.startD AS startDate
  , sales_check.max_dt AS endDate
  , CAST(NULL AS TEXT) AS newOrderDate
  , CAST(NULL AS TEXT) AS newShipDate
  , CAST(NULL AS TEXT) AS newDueDate
FROM _maxD
INNER JOIN sales_check
 ON sales_check.sls_ord_num = _maxD.sls_ord_num
 AND sales_check.idSales = 1;

Let's verify that the maximum start date of the interval startDate is calculated correctly for both the case of a group and the case of a single product with a closed date:

SELECT 
  sc.sls_ord_num
  ,sc.sls_prd_key
  ,sc.min_dt
  ,sc.max_dt 
  ,sd.recCntSales
  ,sd.startDate
  ,sd.endDate
FROM sales_check AS sc
INNER JOIN sales_groups_dates AS sd
  ON sd.sls_ord_num = sc.sls_ord_num
WHERE sc.sls_ord_num IN ( 'SO54115', 'SO43698');

Result:

sls_ord_num	sls_prd_key	min_dt	max_dt	recCntSales	startDate	endDate
SO43698	BK-M82S-44	2025-05-03	2025-06-14	1	2025-05-03	2025-06-14
SO54115	CL-9009	2025-03-14	2025-12-31	4	2025-05-10	2025-12-31
SO54115	HY-1023-70	2025-05-10	2025-12-31	4	2025-05-10	2025-12-31
SO54115	TI-T723	2025-02-18	2025-12-31	4	2025-05-10	2025-12-31
SO54115	TT-T092	2025-03-15	2025-12-31	4	2025-05-10	2025-12-31

As you can see, both for a group of 4 products and for a single product with a limited date, the start and end dates of the generation interval are calculated correctly.

Since we now have ranges for all groups, we can generate a random date from the range for the group:

UPDATE sales_groups_dates
SET newOrderDate = 
  date(
    julianday(startDate) + 
     (abs(random()) % (julianday(endDate) - julianday(startDate) ))
  );

This code works for a group of one product as well.

Note: the code should not generate the end date of the interval, so we do not add one more day after the remainder of the division.

After generating the order date, we can randomly generate the shipping date and delivery date sequentially within a 4-day interval:

UPDATE sales_groups_dates
SET newShipDate = date(newOrderDate, '+' || (1 + abs(random()) % 4) || ' days');

UPDATE sales_groups_dates
SET newDueDate = date(newShipDate, '+' || (1 + abs(random()) % 4) || ' days');

The following query will help verify the correctness of the newly generated dates:

SELECT *
FROM sales_check
EXCEPT
SELECT sc.*
FROM sales_check as sc
INNER JOIN sales_groups_dates AS gd
  ON gd.sls_ord_num = sc.sls_ord_num
INNER JOIN Product AS pd
  ON sc.sls_prd_key = pd.prd_key
  AND gd.newOrderDate >=  pd.prd_start_dt
  and ( gd.newOrderDate < pd.prd_end_dt OR pd.prd_end_dt IS NULL)
;

We subtract from the selection from the entire sales_check table the results of the interval join query. Both subqueries between EXCEPT should return the same number of rows - 60,398, and the full query should return no rows. This means that for all products, a match was found based on the validity ranges.

Now we are ready to create the Sales table with the same format and key fields, but with different values, except for the value of the sls_quantity field:

CREATE TABLE Sales
AS
SELECT
  sc.sls_ord_num,
  sc.sls_prd_key,
  sc.sls_cust_id,
  replace(gd.newOrderDate, '-', '') sls_order_dt,
  replace(gd.newShipDate, '-', '') AS sls_ship_dt,
  replace(gd.newDueDate, '-', '') AS sls_due_dt,
  CAST(pd.prd_cost AS INT) * CAST(sd.sls_quantity AS INT) AS sls_sales,
  CAST(sd.sls_quantity AS INT) AS sls_quantity,
  CAST(pd.prd_cost AS INT) AS sls_price

FROM sales_check as sc
INNER JOIN sales_groups_dates AS gd
  ON gd.sls_ord_num = sc.sls_ord_num
INNER JOIN sales_details AS sd
  ON  sd.sls_prd_key = sc.sls_prd_key
  AND  sd.sls_ord_num = sc.sls_ord_num
  AND  sd.sls_cust_id = sc.sls_cust_id
INNER JOIN Product AS pd
  ON sc.sls_prd_key = pd.prd_key
  AND gd.newOrderDate >=  pd.prd_start_dt
  and ( gd.newOrderDate < pd.prd_end_dt OR pd.prd_end_dt IS NULL)
;

We also recalculate both the price and the sales amount by quantity. We convert the dates to the same format as in the original table by removing the hyphen.

Using three queries, we can compare the old data with the new data:

SELECT *
FROM Sales
WHERE sls_ord_num IN ( 'SO54115', 'SO43698');

SELECT *
FROM sales_details
WHERE sls_ord_num IN ( 'SO54115', 'SO43698');

SELECT 
  prd_id,
  prd_key,
  prd_nm,
  prd_cost,
  prd_start_dt,
  prd_end_dt
FROM Product 
WHERE prd_key IN ( 'BK-M82S-44','TI-T723','TT-T092','HY-1023-70','CL-9009');

This is the result of the first query, that is, the new data:

sls_ord_num	sls_prd_key	sls_cust_id	sls_order_dt	sls_ship_dt	sls_due_dt	sls_sales	sls_quantity	sls_price
SO43698	BK-M82S-44	28389	20250520	20250522	20250525	1912	1	1912
SO54115	CL-9009	27202	20250923	20250924	20250927	3	1	3
SO54115	HY-1023-70	27202	20250923	20250924	20250927	21	1	21
SO54115	TI-T723	27202	20250923	20250924	20250927	11	1	11
SO54115	TT-T092	27202	20250923	20250924	20250927	2	1	2

This is the result of the second query, that is, the old data:

sls_ord_num	sls_prd_key	sls_cust_id	sls_order_dt	sls_ship_dt	sls_due_dt	sls_sales	sls_quantity	sls_price
SO43698	BK-M82S-44	28389	20101229	20110105	20110110	3400	1	3400
SO54115	CL-9009	27202	20130309	20130316	20130321	8	1	8
SO54115	HY-1023-70	27202	20130309	20130316	20130321	55	1	55
SO54115	TI-T723	27202	20130309	20130316	20130321	29	1	29
SO54115	TT-T092	27202	20130309	20130316	20130321	5	1	5

And the third query gives the prices for the products from the list and their validity range:

prd_id	prd_key	prd_nm	prd_cost	prd_start_dt	prd_end_dt
346	BK-M82S-44	Mountain-100 Silver- 44	1912	2025-05-03	2025-06-14
484	CL-9009	Bike Wash - Dissolver	3	2025-03-14
487	HY-1023-70	Hydration Pack - 70 oz.	21	2025-05-10
541	TI-T723	Touring Tire	11	2025-02-18
530	TT-T092	Touring Tire Tube	2	2025-03-15

As you can see, in the new version of the Product table, both dates and prices are consistent, unlike the old data.

We only need to delete the auxiliary columns used for calculations and the two additional tables:

ALTER TABLE Product DROP COLUMN recCnt;
ALTER TABLE Product DROP COLUMN id;
ALTER TABLE Product DROP COLUMN date1;
ALTER TABLE Product DROP COLUMN date2;
ALTER TABLE Product DROP COLUMN date3;
ALTER TABLE Product DROP COLUMN min_dt;
ALTER TABLE Product DROP COLUMN max_dt;

DROP TABLE sales_check;
DROP TABLE sales_groups_dates;

Now the Product and Sales tables can be exported. The data correction for the test dataset is complete.

Conclusion

We have explored the data correction process using a simple schema as an example. This approach is also applicable to other data models, provided the data volume is not excessively large. When building a DWH, it is best to start with a small volume of records, which makes it easier to debug the transformation logic. In such cases, data can be generated using the following methods:

Generating random dates within a specified range for dimensions. The interval, initially limited to the year 2025, can be extended to multiple years if necessary, while the algorithms remain the same.
Generating related data (e.g., sales records) in alignment with the generated dates in the dimensions.

Alternatively, the data could be generated from scratch: there are readily available scripts for randomly generating first and last names, product names, and business keys. Afterward, you can populate them with randomly generated yet mutually consistent dates, as described earlier in this article.

It is crucial to start working with clean, correct data from the very beginning: this will allow you to quickly identify any flaws in the transformation logic, if they exist.

References

Baraa Khatib Salkini: sql-data-warehouse-project
Source code for this article: DWH Step By Step Part 1

DEV Community: Dmitry

Starting Small

How Much Data Do We Need?

Data Pipelines

Data Simulator

Data Simulation

The "Customer" Dimension (the customerData table)

The "Product" Dimension (the productData table)

The "Sales" Fact Table (the salesData table)

All Data Together

Conclusions

References

Preparing Clean Data for a DWH

Data Structure Description

"Product" Dimension (prd_info table)

"Customer" Dimension (cust_info table)

"Sales" Fact Table (sales_details table)

Data Correction

Correction of cust_info Table Data

Analysis of cust_info Table Data

Creating the Customer Table

Data Correction for the prd_info Table

Analysis of prd_info Table Data

Creating the Product Table

Data Correction for sales_details Table

Analysis of sales_details Table Data

Creating the Sales Table

Conclusion

References

The "Customer" Dimension (the `customerData` table)

The "Product" Dimension (the `productData` table)

The "Sales" Fact Table (the `salesData` table)

"Product" Dimension (`prd_info` table)

"Customer" Dimension (`cust_info` table)

"Sales" Fact Table (`sales_details` table)

Correction of `cust_info` Table Data

Analysis of `cust_info` Table Data

Creating the `Customer` Table

Data Correction for the `prd_info` Table

Analysis of `prd_info` Table Data

Creating the `Product` Table

Data Correction for `sales_details` Table

Analysis of `sales_details` Table Data

Creating the `Sales` Table