Facts and Dimensions

A data warehouse is a database built for reports, and its tables hold two kinds of columns. A fact is a number that happened, one you add up: in orders.csv, the quantity and the unit_price of each order line. A dimension is what you describe, group and filter the facts by: the product, its category, the customer, the date.

Each of the 1,497 lines of orders.csv repeats its product's name, category and price. A dimension table keeps each product once. A data engineer needs one because a product's details then live in a single row: a report on products reads 8 rows instead of 1,497, and a fix changes one row instead of every line that copied it.

The tools: DuckDB reads a CSV file in place when you put its name in single quotes where a table name goes: FROM 'orders.csv'. SELECT DISTINCT keeps each different row only once. CREATE TABLE name AS SELECT … saves the rows of the SELECT as a new table, and con.execute(query) runs the statement in the database con; it prints nothing. The same idea for categories:

query = (
    "CREATE TABLE dim_category AS "
    "SELECT DISTINCT category "
    "FROM 'orders.csv' "
)
con.execute(query)

That table gets 4 rows: gifts, home, kitchen and office. For your table, the hidden check prints dim_product sorted by product, then the rows of orders.csv next to the rows of dim_product:

product | category | unit_price
Gift card | gifts | 50
Lamp | home | 29
Mug | kitchen | 12
Notebook | office | 7
Pen | office | 2
Plant | home | 15
Stapler | office | 9
Teapot | kitchen | 22
(8 rows)
order_lines | dim_product_rows
1497 | 8
(1 row)

Your task: fill in query so that it creates the table dim_product from each different product, category and unit_price of orders.csv, then run it with con.execute.

Assignment
Follow the Coding Tutorial to build the dimension table dim_product from orders.csv.

Hint
Each piece of query is a string that ends with a space, one clause per line. A CREATE TABLE statement does nothing until con.execute(query) runs it.