Free lesson
Your first pipeline: raw to clean
Real data arrives dirty. Find the mess, clean it, and load a table people can trust.
In this lesson you will
- Look at raw data before trusting it
- Clean text, fix types and remove duplicates
- Build a clean table and test it
Words you'll meet
- Raw
- Data kept exactly as it arrived.
- Clean
- Data that has been checked and fixed.
- NULL
- A value that is unknown.
- Data quality check
- A query that counts rows that are still broken.
The lesson, step by step
Each step teaches one idea, then hands you a real database to try it on. The exercises run in your browser.
Step 1 of 7
Look first
So far the data has been spotless. Real data never is. This is
raw_orders, exactly as the shop tills exported it. Every column arrived as plain text.Rule one of data engineering: look at the data before you trust it.
A raw table keeps data exactly as it arrived, mess included. That is on purpose. If your cleaning turns out to have a mistake, you fix the cleaning and rebuild from raw. If you had cleaned the original in place, it would be gone.
Everything arrives as text because text can hold anything, so no row is rejected at the door.
Your turn: Select everything from raw_orders and read it carefully.
Step 2 of 7
Spot the bomb
Quantity should be a number so we can add it up. One value in this table will blow up any attempt to do math on it.
Your turn: Click the cell that cannot be turned into a number.
Step 3 of 7
Duplicates
Order 2 was sent twice. Duplicates are the most common defect in pipelines, and they silently inflate every total.
SELECT DISTINCTreturns each unique row once.Your turn: Select every column from raw_orders, with exact duplicate rows removed.
Step 4 of 7
Standardize text
To a database,
'austin','AUSTIN'and'Austin'are three different cities, and' Ada 'is not'Ada'.TRIM(x)strips spaces from both ends.LOWER(x)makes text lower case.Your turn: Show the trimmed customer and the lower-cased city for every raw order.
Step 5 of 7
Fix the types
CAST(quantity AS INTEGER)turns text into a number, but it stops the whole query with an error when it meets'three'.TRY_CASTis the pipeline-friendly version: values it cannot convert becomeNULL(meaning "unknown") and the job carries on.NULLis not zero and not empty text. It means "unknown".SUMskips NULLs, andNULL = NULLis not true, because two unknowns are not known to be equal.TRY_CASTswaps a loud failure for a quiet NULL. That is only safe if you then count the NULLs, which is what the last step of this lesson does.Your turn: Show order_id, and quantity converted to an INTEGER with TRY_CAST.
Step 6 of 7
Build the clean table
Time to put it together.
CREATE TABLE name AS SELECT ...saves a query's result as a new table. Raw stays untouched, clean is built from it. If you get it wrong you just rebuild.Your table needs these columns, in this order:
order_id: cast to INTEGERcustomer: trimmeditem: lower casequantity: TRY_CAST to INTEGERcity: lower case
And no duplicate rows.
Keeping raw and clean as separate layers is standard practice. You will hear the layers called bronze, silver and gold, or staging and marts. The idea is the same.
dbt, one of the most widely used tools in the field, is at heart an organized collection of
CREATE TABLE AS SELECTstatements like the one you are about to write.Your turn: Create a table called clean_orders from raw_orders.
Step 7 of 7
Test your work
You just built a pipeline: raw → transform → clean. Professionals finish with a data quality check, a query that counts the rows that are still broken, so problems get caught before a customer sees them.
Test for unknown values with
IS NULL(never= NULL).Real pipelines run checks like this automatically after every load, and raise an alert or stop when the count is not zero.
The common ones: a column is never NULL, a key is never repeated, a column only contains expected values, and today's row count is not wildly different from yesterday's.
Your turn: Count the rows in clean_orders where quantity is NULL or customer is NULL.
Reading is the easy part.
Every step above has an exercise. Do it for real, free, no account needed.
Start the lesson