Meet the warehouse

Tables, rows, grain, and connections

A warehouse is a set of tables. A table has columns, which name what is stored, and rows, which are the individual records. This project has six tables: dates, stores, products, customers, orders, and order_items.

Before you query a table, decide what one row of it represents. Data teams call this the table's grain. Two grains carry this whole course, and they are easy to confuse. orders is one row per order. order_items is one row per line on an order, and orders average exactly 2.40 lines each. That number returns in the join lesson and again in the capstone, so keep it in mind.

Your queries reach the data through a connection, which is a named pointer to a database. This project's connection is called duckdb-default, and it is defined in the .bruin.yml file at the repo root. That file is listed in .gitignore on purpose, because in a real project it would hold passwords, and a password does not belong in version control. This particular project needs no credentials at all - the connection is just a path to a local file, already configured.

One house convention runs through every lesson: each bruin query carries a --description, which states what the query is meant to prove.

The data spans three years, 2023 to 2025, with 360 orders in 2023, 480 in 2024, and 360 in 2025. Volume grows from 2023 to 2024, then declines from 2024 to 2025. The decline is deliberate. If you explore 2025 and find categories shrinking, that is the data behaving as designed, not a mistake on your part.

One note on reading output. The terminal prints numbers plainly: 604065, not 604,065.00, and a very large number may appear in scientific notation, like 1.44977182e+06, which means "1.44977182 times 10 to the 6th" - move the decimal point six places right to get 1,449,771.82. The lessons write numbers with separators for readability; your terminal will not. Same values, different clothes.

Your task

Run three "look around" queries, then read docs/schema.md. The goal is to confirm the shape of the warehouse before you query it in earnest: six tables, and the row counts of the two order tables.

bruin query --connection duckdb-default --description "list every table in the project" --query "SHOW TABLES;"
bruin query --connection duckdb-default --description "preview the first five orders" --query "SELECT * FROM orders LIMIT 5;"
bruin query --connection duckdb-default --description "count rows in the two order tables" --query "SELECT (SELECT COUNT(*) FROM orders) AS orders, (SELECT COUNT(*) FROM order_items) AS order_items;"

SHOW TABLES returns all six because the tables have plain names like orders, not retail.orders. Expect 6 tables from the first query. The third query is the check: it should return 1,200 orders and 2,880 order lines.

Check your understanding

• What does one row of orders represent?
• What does one row of order_items represent?
• Which of the two has more rows, and why?

Do it with your agent

Say next lesson and your agent teaches this, asks you these questions, then sets the task above. Do it by hand, then say review my work - it runs your work, checks it against a rubric, and tells you what to fix or marks the lesson done.