Skip to content

Lesson 2 of 6

Set Up a Warehouse with SQLite Tables

Create a SQLite database file and define tables for customers, products, and orders with primary keys and foreign keys.

7 minData Engineering

After this lesson, you will be able to create a SQLite database file and define tables for customers, products, and orders with primary keys and foreign keys.

What is a warehouse?

A warehouse is a place to store data in a structured way so you can ask questions about it. In this course, the warehouse is a SQLite database file. SQLite is a small database engine that stores everything in one file on your computer. You do not need to install a server; the SQLite command-line tool can create and manage the file.

You already have a pipeline folder with a raw-data subfolder from the earlier lesson. The raw CSV files are your data lake. Now you will create a database file inside the pipeline folder and define tables that will hold clean, related data.

Create the database file

Open a command line and go into your pipeline folder. Run this command to start the SQLite command-line tool with a new database file called warehouse.db:

sqlite3 warehouse.db

You will see a prompt that looks like sqlite>. This means you are inside the SQLite tool. The file warehouse.db is created as soon as the tool opens. If you already have a file with that name, the tool opens it instead of creating a new one.

To leave the SQLite tool at any time, type .quit and press Enter. For now, stay inside the tool to create the tables.

Define the customers table

A table holds data in rows and columns, like a spreadsheet. Each column has a name and a data type. The data type tells SQLite what kind of values the column will store. Common types are INTEGER for whole numbers, TEXT for words, and REAL for numbers with a decimal point.

The customers table will store the same information as the customers.csv file from the data lake: a customer ID, a name, and a city. The customer ID is a whole number, and the name and city are text. We also want to make sure each customer ID is unique, so we mark it as the primary key. A primary key is a column or set of columns that uniquely identifies each row in the table.

Type this SQL statement at the sqlite> prompt and press Enter:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL
);

The statement creates a table named customers with three columns. customer_id is an integer and the primary key. name and city are text columns. The words NOT NULL mean that every row must have a value in those columns; a blank value is not allowed.

SQLite does not require a semicolon at the end of a statement, but it is good practice to include one. If you make a typing mistake, SQLite will show an error message. You can retype the whole statement.

Define the products table

The products table comes from products.csv. It has a product ID, a product name, and a price. The product ID is an integer primary key. The price is a number with a decimal point, so we use REAL.

Type this statement:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    price REAL NOT NULL
);

Define the orders table

The orders table will hold clean, joined data later in the course. For now, we define its structure. It has an order ID, a customer ID, a product ID, a quantity, and an order date. The order ID is the primary key. The customer ID and product ID refer to rows in the customers and products tables. We mark them as foreign keys. A foreign key is a column that points to a primary key in another table. This helps keep the data consistent: you cannot put a customer ID in orders that does not exist in customers.

Type this statement:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    FOREIGN KEY (product_id) REFERENCES products (product_id)
);

The order_date column is TEXT because the dates in the CSV file are written as 2025-01-15. Storing dates as text in that format keeps them easy to read and sort.

Check your tables

To see the tables you have created, type:

.tables

SQLite will print a list of table names. You should see customers, products, and orders. If you do not see a table, check the CREATE TABLE statement for that table and run it again.

To see the structure of one table, use the .schema command followed by the table name. For example:

.schema customers

SQLite will show the CREATE TABLE statement for that table. This is useful to check your work.

Leave the SQLite tool

When you are done, type .quit and press Enter to leave the SQLite tool. The database file warehouse.db stays in your pipeline folder. You can open it again later with the same sqlite3 warehouse.db command.

Key takeaways

  • A SQLite database is a single file that stores tables.
  • Use the sqlite3 command-line tool to create and manage the database.
  • Define a table with CREATE TABLE, giving each column a name and a data type.
  • A primary key uniquely identifies each row in a table.
  • A foreign key links a column to a primary key in another table.

Try it

Open the SQLite tool for warehouse.db and create a new table called returns that matches the returns.csv file you may have created in the previous lesson. The table should have columns return_id (integer primary key), order_id (integer not null), and return_date (text not null). Add a foreign key on order_id that references the order_id column in the orders table. After creating the table, use .tables to confirm it appears, and use .schema returns to check the definition. A good result is that the returns table has the three columns with correct types, the primary key on return_id, and the foreign key on order_id.

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.

    Set Up a Warehouse with SQLite Tables | Build a Small Data Pipeline with a Warehouse and Lake | Rolebound