Lesson 6 of 6
Automate the Whole Pipeline with a Script
Put the import, clean, join, and export steps into one shell script that you can run end to end.
6 minData Engineering
After this lesson, you will be able to put the whole pipeline into one shell script. You will run the script to load raw CSV files into staging tables, clean and join them into the orders table, export a monthly summary to the data lake, and see the result in the raw-data folder.
Why automate with a script
So far you typed each SQL command by hand in the SQLite tool. That works for a small pipeline, but it is slow and easy to get wrong. A shell script is a plain text file that holds a list of commands. You run the script once and it runs every command in order. This makes your pipeline repeatable: you can run it again after you add new raw files, and you get the same result every time.
You will write a script called run_pipeline.sh in your pipeline folder. It will use the same SQLite database and raw files you already have from the earlier lessons. The SQL commands will live in a separate file called pipeline.sql. This keeps the shell script short and easy to read.
Create pipeline.sql
Open your text editor and create a new file called pipeline.sql inside the pipeline folder. This file will contain all the SQL statements that load, clean, join, and export the data. The staging tables already exist in the database from lesson 3, and the orders table exists from lesson 2. Both may contain rows from earlier manual work. Before each IMPORT, you must delete the old rows from the staging table. Otherwise, running the script again would append the same rows a second time, and the later insert into orders would fail with a duplicate primary key error.
Add the following lines to pipeline.sql:
.mode csv
DELETE FROM staging_customers;
DELETE FROM staging_products;
DELETE FROM staging_orders;
DELETE FROM staging_returns;
.import raw-data/customers.csv staging_customers --skip 1
.import raw-data/products.csv staging_products --skip 1
.import raw-data/orders.csv staging_orders --skip 1
.import raw-data/returns.csv staging_returns --skip 1
DELETE FROM orders;
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 <> '';
.headers on
.mode csv
.output raw-data/monthly_sales_summary.csv
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS order_count,
SUM(quantity) AS total_quantity
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;
.output stdout
.quitThe first four DELETE statements clear the staging tables. The DELETE FROM orders; line clears the final table so that every run starts from an empty table and avoids primary key conflicts. The IMPORT lines then load the latest raw files. The INSERT INTO orders statement cleans and joins the data exactly as you did in lesson 4. Finally, the export statements write the monthly summary to the data lake as in lesson 5.
Create run_pipeline.sh
Create another new file called run_pipeline.sh in the pipeline folder. The first line of a shell script tells the computer which shell to use. For this course, use bash.
#!/bin/bash
sqlite3 warehouse.db < pipeline.sqlThe #! line is called a shebang. It must be the very first line, with no spaces before it. The second line runs SQLite and sends the contents of pipeline.sql into it. The < symbol tells the shell to read that file as standard input. SQLite opens warehouse.db, runs every statement in the file, and exits because the file ends with .quit.
Make the script executable and run it
Before you can run the script, mark it as executable. Open a terminal in the pipeline folder and run:
chmod +x run_pipeline.shThe chmod command changes file permissions. The +x adds the execute permission, so the shell will let you run the file as a program.
Now run the script:
./run_pipeline.shThe ./ tells the shell to look for the script in the current folder. The script runs SQLite, which imports the raw files, cleans and joins the data, and writes the summary file. You will not see any output on the screen unless there is an error.
Check the output and repeat runs
List the raw-data folder and view the summary file to confirm the pipeline worked:
ls raw-datacat raw-data/monthly_sales_summary.csvYou should see the same summary as in lesson 5: a header row and one row for each month that has orders.
Because the script deletes old rows from the staging tables and the orders table before loading, you can run ./run_pipeline.sh again as many times as you like. Each run starts from the current raw files and produces the same summary. This is what makes your pipeline repeatable. You can add new raw files to raw-data, update the import and insert lines in pipeline.sql if needed, and run the script again to refresh the warehouse and the summary.
Key takeaways
- A shell script runs a list of commands in order, making the pipeline repeatable.
- Put SQL statements in a separate file and run them with
sqlite3 warehouse.db < pipeline.sql. - Use
chmod +xto make a script executable, then run it with./run_pipeline.sh. - Before each import, delete old rows from the staging table so repeated runs do not append duplicates.
- Delete old rows from the
orderstable before inserting so primary keys stay unique.
Try it
Add the extra_orders.csv file to the pipeline, and if you have not already done so, create a staging table staging_extra_orders with all TEXT columns. In pipeline.sql, add DELETE FROM staging_extra_orders; before the import lines, then add .import raw-data/extra_orders.csv staging_extra_orders --skip 1 with the other imports. After the first INSERT INTO orders statement, add a second INSERT INTO orders statement that copies clean rows from staging_extra_orders into orders, using the same rules: remove rows with any blank value, cast the first four columns to INTEGER, and keep order_date as TEXT. Run ./run_pipeline.sh twice and then view raw-data/monthly_sales_summary.csv; a good result is that the summary includes the extra orders from March 2025 with the correct order count and total quantity, and both runs complete without errors.
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.