Preparing Clean Data for a DWH

"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)

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:

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)

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

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)

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

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:

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:

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:

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:

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:

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:

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:

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:

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:

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_dataSource：https://dev.to/dyacc11/preparing-clean-data-for-a-dwh-lhc
My favorites

Related posts

Goravel feature testing for a REST API

Part 13 of the Goravel series . The original on DevChalk has the colour-matched code, the figures and the full diffs. Co

0dev.to Testing

How to find and fix slow PostgreSQL queries

Originally published on the Hubfly space blog . By Octave, drafted with AI, tested on Hubfly space. TL;DR What you get:

0dev.to Database

Goravel models and migrations with Postgres

Part 4 of the Goravel series . The original on DevChalk has the colour-matched code, the figures and the full diffs. Cod

0dev.to Database

Goravel belongs to relationships and foreign keys

Part 5 of the Goravel series . The original on DevChalk has the colour-matched code, the figures and the full diffs. Cod

0dev.to Database

Goravel has many relationships and N+1 queries

Part 6 of the Goravel series . The original on DevChalk has the colour-matched code, the figures and the full diffs. Cod

0dev.to Database

Goravel factories and seeders with fake data

Part 7 of the Goravel series . The original on DevChalk has the colour-matched code, the figures and the full diffs. Cod

0dev.to Database