Cleaning the Source and the Schema Contract

Goal

You complete one cycle of profiling a raw table that consists only of strings, normalizing the formats, and splitting the load into a clean table and a reject table.

Why it matters

The most dangerous incident in a data pipeline is not a failure but silent loss. If you simply skip the rows that failed to load, no error occurs and only the numbers get a little smaller. If it is discovered months later, there is no way to trace what was missing from what point.

So a cleansing pipeline must keep the conservation law. The sum of the clean count and the reject count must equal the raw count exactly, and there must be no row that belongs to neither. If you put this verification inside the pipeline, loss shows up the moment it happens.

You must also leave the reject reason. A row discarded without a reason cannot be recovered. And remember too that no value and 0 are different. The moment you fill an empty amount with 0, the very fact that "the information was missing" is erased.

Steps

The target is the staging.orders_raw table.

1. Create the t_raw_profile table. The columns are col and bad_count, and there are exactly 3 rows.
   • customer_email — the number of rows where the value is empty (NULL)
   • amount — the number of rows that are NULL or contain only whitespace
   • order_date — the number of rows that are not in the YYYY-MM-DD format
2. Create the v_raw_dates view. The columns are raw_id and order_date_parsed (date type), and every row must be parsed. The formats are the four YYYY-MM-DD, MM/DD/YYYY, YYYY.MM.DD, and YYYYMMDD.
3. Create the v_raw_amount view. The columns are raw_id and amount_num (numeric type); remove currency symbols and thousands separators, and keep an empty string as no value, not 0.
4. Create the v_raw_status view. The columns are raw_id and status_norm; trim leading and trailing whitespace and unify to lowercase. The result must be 3 kinds.
5. Create the orders_clean table. The columns are, in order, raw_id, order_ref, customer_email, order_date, amount, and status, and you load with proper types only the rows that have an email and whose amount is not empty.
6. Create the orders_reject table. The columns are raw_id and reason, and the rows excluded in step 5 go in with their reasons. The reason must not be empty.
7. Check that the sum of the orders_clean count and the orders_reject count equals the staging.orders_raw count, and that no row went into both.
8. Create the schema_contract table. The columns are column_name and data_type, and it holds the columns and types of orders_clean as they are.

Reference

• Regex match: order_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$'
• Parsing by format: to_date(order_date, 'MM/DD/YYYY')
• Turn an empty string into no value: nullif(btrim(값), '') (the placeholder stands for the value)
• Catalog query: SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'orders_clean'
• Common mistake 1: an empty string is not NULL, so IS NULL alone does not catch it.
• Common mistake 2: if you read MM/DD/YYYY as DD/MM/YYYY, up to the 12th it is quietly wrong without even an error.

Count the defects in the raw data

Create the t_raw_profile table. The columns are col and bad_count, and there are exactly 3 rows.

• customer_email — the number of rows where the value is empty (NULL)
• amount — the number of rows that are NULL or contain only whitespace
• order_date — the number of rows that are not in the YYYY-MM-DD format

What counts as a defect is defined differently for each column. Note that an empty string is not NULL.

Parse the four date formats

Create the v_raw_dates view. The columns are raw_id and order_date_parsed (date type), and every row must be parsed. The formats are the four YYYY-MM-DD, MM/DD/YYYY, YYYY.MM.DD, and YYYYMMDD.

After identifying the format with a regular expression, apply a different parsing rule to each. One format has the month and the day in a different order.

Remove symbols and commas from the amount

Create the v_raw_amount view. The columns are raw_id and amount_num (numeric type); remove currency symbols and thousands separators, and keep an empty string as no value, not 0.

Remove the characters and then convert to a number. An empty string must be kept as no value, not 0.

Normalize the status values

Create the v_raw_status view. The columns are raw_id and status_norm; trim leading and trailing whitespace and unify to lowercase. The result must be 3 kinds.

Check how many kinds remain after you trim leading and trailing whitespace and unify the case.

Load into the clean table

Create the orders_clean table. The columns are, in order, raw_id, order_ref, customer_email, order_date, amount, and status, and you load with proper types only the rows that have an email and whose amount is not empty.

Exclude rows with no email or an empty amount. The column types must be set up properly.

Leave rows with reasons in the reject table

Create the orders_reject table. The columns are raw_id and reason, and the rows excluded in step 5 go in with their reasons. The reason must not be empty.

Always write why you discarded it. If the reason is empty, nobody can recover it later.

Check the conservation law

Check that the sum of the orders_clean count and the orders_reject count equals the staging.orders_raw count, and that no row went into both.

The sum of clean and reject must equal the raw count, and no row may have gone into both.

Leave a schema contract

Create the schema_contract table. The columns are column_name and data_type, and it holds the columns and types of orders_clean as they are.

It is safer to read the column names and types as they are from the catalog and make them into a table.