Skip to content

Lesson 3 of 6

Load Raw Files into Staging Tables

Use the SQLite command-line tool to import each CSV file into a staging table inside the warehouse.

10 minData Engineering

After this lesson, you will be able to load the raw CSV files from your data lake into temporary staging tables inside your SQLite warehouse, ready for cleaning and joining in the next lesson.

Why staging tables?

Your raw CSV files are in the raw-data folder. Before you can clean and join them, you need to bring them into SQLite. A staging table is a temporary holding area inside the database. It holds the raw data exactly as it came from the CSV file, including any blank values or inconsistencies. Later, you will clean and transform this data into the final orders table. Keeping raw and clean data separate makes it easier to trace problems and rerun the pipeline.

Create staging tables

You will create one staging table for each CSV file: staging_customers, staging_products, and staging_orders. These tables have the same columns as the CSV files, but all columns are TEXT for now. This avoids import errors when a value is missing or not the expected type. You will convert types later when you clean the data.

Start the SQLite command-line tool for your warehouse database:

sqlite3 warehouse.db

Create the staging table for customers:

CREATE TABLE staging_customers (
    customer_id TEXT,
    name TEXT,
    city TEXT
);

Create the staging table for products:

CREATE TABLE staging_products (
    product_id TEXT,
    product_name TEXT,
    price TEXT
);

Create the staging table for orders:

CREATE TABLE staging_orders (
    order_id TEXT,
    customer_id TEXT,
    product_id TEXT,
    quantity TEXT,
    order_date TEXT
);

Check that the tables exist:

.tables

You should see staging_customers, staging_products, and staging_orders in the list, along with the tables from the previous lesson.

Import CSV files into staging tables

SQLite can import CSV files directly using the .import command. The command reads the file and inserts each row into the specified table. The first row of the CSV file is treated as data unless you tell SQLite to skip it. Since your CSV files have a header row, you must set .mode csv and then use .import with the --skip 1 option to skip the header.

First, set the mode to CSV:

.mode csv

Now import each file. The path is relative to the directory where you started sqlite3. You are in the pipeline folder, so the files are in raw-data/.

Import customers:

.import raw-data/customers.csv staging_customers --skip 1

Import products:

.import raw-data/products.csv staging_products --skip 1

Import orders:

.import raw-data/orders.csv staging_orders --skip 1

If you created a returns.csv file in the first lesson, you can import it into a staging table as well. Create the table and import:

CREATE TABLE staging_returns (
    return_id TEXT,
    order_id TEXT,
    return_date TEXT
);
.import raw-data/returns.csv staging_returns --skip 1

If you do not have returns.csv, skip these two commands.

Check the imported data

Use a simple SELECT query to see the first few rows of each staging table. This confirms the import worked and shows the raw values.

SELECT * FROM staging_customers LIMIT 3;
SELECT * FROM staging_products LIMIT 3;
SELECT * FROM staging_orders LIMIT 3;

You should see the same data as in your CSV files. For example, the first row of staging_customers should be 1|Alice|London.

Exit SQLite

When you are done, exit the SQLite tool:

.quit

Your staging tables are now stored inside warehouse.db. In the next lesson, you will write SQL to clean these tables and insert the data into the final orders table.

Key takeaways

  • Staging tables hold raw CSV data inside SQLite before cleaning.
  • Create staging tables with all columns as TEXT to avoid import errors.
  • Use .mode csv and .import filename tablename --skip 1 to load a CSV file, skipping the header row.
  • Check imported data with SELECT * FROM staging_table LIMIT 3;.
  • Staging tables are separate from the final tables, so you can clean and transform data safely.

Try it

Create a new CSV file called extra_orders.csv in the raw-data folder with columns order_id, customer_id, product_id, quantity, and order_date. Add two rows: order 1005 for customer 2, product 102, quantity 3, date 2025-03-01; and order 1006 for customer 3, product 103, quantity 1, date 2025-03-05. Then open sqlite3 warehouse.db, set .mode csv, create a staging table staging_extra_orders with all TEXT columns, and import the file using --skip 1. Verify with SELECT * FROM staging_extra_orders;. A good result shows the two rows exactly as in the CSV file, with no header row in the table.

Official documentation

Ready to use this in a job?

Rolebound applies to matching roles for you.

Start now

Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.

    Cookies on Rolebound

    We use necessary cookies to sign you in and keep the site working — those cannot be turned off. We would also like to set analytics and advertising cookies so we can see which pages help people get interviews and which adverts brought them here. Nothing optional is set unless you say yes, and you can change your mind at any time from “Cookie settings” in the footer. Read our Privacy Policy.