Lesson 4 of 6
Clean and Join Data into an Orders Table
Write SQL to remove blank values, join staging tables, and insert clean rows into a final orders table.
8 minData Engineering
After this lesson you will be able to clean raw data in SQLite, join staging tables, and insert clean rows into the final orders table.
Why clean and join?
The staging tables hold raw text from CSV files. Some values may be blank, and the tables are not linked. Before you can report on orders, you need a single table with clean, correct rows. In this lesson you will remove rows with blank values, join the staging tables, and insert the result into the final orders table you created in lesson 2.
Check the staging data
Open the SQLite tool for your warehouse database.
sqlite3 warehouse.dbLook at the staging tables you created in lesson 3.
SELECT * FROM staging_customers;SELECT * FROM staging_products;SELECT * FROM staging_orders;Each column is TEXT, even numbers and dates. Blank values appear as empty strings. To clean the data, you will write SQL that ignores rows with blanks and converts text to the correct types.
Remove blank values
A blank value in a CSV file becomes an empty string in a TEXT column. You can filter these out with a WHERE clause that checks each column is not an empty string. For example, to see only orders with no blanks in any column:
SELECT *
FROM staging_orders
WHERE order_id <> ''
AND customer_id <> ''
AND product_id <> ''
AND quantity <> ''
AND order_date <> '';The <> operator means not equal. This query returns rows where every column has a value. If your CSV files have no blanks, you will see all rows. If a row has a blank, it is left out.
Convert text to correct types
The final orders table expects INTEGER for IDs and quantity, and TEXT for the date. SQLite can convert text to numbers with the CAST function. For example:
SELECT CAST(order_id AS INTEGER),
CAST(customer_id AS INTEGER),
CAST(product_id AS INTEGER),
CAST(quantity AS INTEGER),
order_date
FROM staging_orders
WHERE order_id <> ''
AND customer_id <> ''
AND product_id <> ''
AND quantity <> ''
AND order_date <> '';CAST changes the text '1' to the integer 1. If a value cannot be converted, SQLite returns 0, but the blank check already removed empty strings.
Join staging tables
You need the customer name and product name in the final orders table, but those are in separate staging tables. Use JOIN to combine rows based on matching IDs.
SELECT so.order_id,
so.customer_id,
sc.name AS customer_name,
so.product_id,
sp.product_name,
so.quantity,
so.order_date
FROM staging_orders so
JOIN staging_customers sc ON so.customer_id = sc.customer_id
JOIN staging_products sp ON so.product_id = sp.product_id
WHERE so.order_id <> ''
AND so.customer_id <> ''
AND so.product_id <> ''
AND so.quantity <> ''
AND so.order_date <> '';Here so, sc, and sp are short aliases for the table names. The ON clause tells SQLite which columns must match. The AS keyword gives a column a new name in the result.
Insert clean rows into the final orders table
The final orders table from lesson 2 has columns order_id, customer_id, product_id, quantity, and order_date. It does not have customer name or product name. So you insert only the columns that exist in the table.
INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date)
SELECT CAST(so.order_id AS INTEGER),
CAST(so.customer_id AS INTEGER),
CAST(so.product_id AS INTEGER),
CAST(so.quantity AS INTEGER),
so.order_date
FROM staging_orders so
WHERE so.order_id <> ''
AND so.customer_id <> ''
AND so.product_id <> ''
AND so.quantity <> ''
AND so.order_date <> '';This statement inserts one row for each clean row in staging_orders. The WHERE clause removes rows with blanks. The CAST calls convert text to integers.
Verify the result
Check the final orders table.
SELECT * FROM orders;You should see the same rows as in staging_orders, but with integer types and no blank values. To confirm the types, look at the schema.
.schema ordersThe schema shows order_id INTEGER, customer_id INTEGER, and so on. If you run the insert twice, you will get duplicate rows because there is no check for existing rows. For this lesson, run it once. In a later lesson you will automate the whole pipeline and can clear the table first.
Clean up
Close the SQLite tool.
.quitKey takeaways
- Blank values in CSV files become empty strings in TEXT columns.
- Use a WHERE clause with
<> ''to remove rows with blanks. - Use CAST to convert text to the correct data type.
- Use JOIN to combine rows from different tables based on matching columns.
- Insert clean rows into the final table with INSERT INTO ... SELECT.
Try it
Open sqlite3 warehouse.db. Write a single SQL statement that inserts clean rows from staging_extra_orders into the orders table. The staging table has TEXT columns order_id, customer_id, product_id, quantity, and order_date. Remove rows with any blank value, cast the first four columns to INTEGER, and keep order_date as TEXT. After running the statement, use SELECT * FROM orders ORDER BY order_id; to see all rows. A good result shows six rows in order (1001, 1002, 1003, 1004, 1005, 1006), with no blank values and integer types for the ID and quantity columns.
Official documentation
Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.