Meet the warehouse

Tables, rows, columns, and grain

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 often 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.

What a connection is

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 project root - the bruin/ folder one level above the course folder, where bruin init put it. 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 a path to a local file, already configured, which is why the bruin init output about adding credentials did not apply.

Look around

Run these three commands. Each bruin query carries a --description, which is the house convention for saying what a query is meant to prove.

bruin query --connection duckdb-default \
  --description "list every table in the project" \
  --query "SHOW TABLES;"

SHOW TABLES returns all six. It works because the tables have plain names like orders, not retail.orders. That is deliberate: names inside a separate schema would make SHOW TABLES come back empty, which reads like a broken setup.

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;"

The counts should be 1,200 orders and 2,880 order lines.

One note on reading output before you go further. 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.

If you prefer a visual table browser, the Bruin VS Code extension has one, covered in Install the extension. It is optional. This course stays in the terminal, because reading query output is the skill you are here to build.

The shape of the data

The data spans three years, 2023 to 2025, with 360 orders in 2023, 480 in 2024, and 360 in 2025. So 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.

Ask your coding agent

AI Prompt

Profile this warehouse for me. For each table in the project, tell me: the row count, what one row represents, and which column or columns uniquely identify a row. Present it as a table.

Then tell me which two tables I would need to join to answer "how much did each customer spend", and warn me about anything that could go wrong in that join.

Use bruin query with --limit when you explore. Show me each query before you run it.

Checkpoint

Without looking at SQL, answer these:

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