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.dbYou 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:
.tablesSQLite 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 customersSQLite 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
sqlite3command-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
Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.