Capstone: Bring the Files In in Python

Over the next six lessons you build the shop's small data warehouse in DuckDB, the way you would at work. A week of daily order files goes in, a report comes out, and each layer of tables has one job. This lesson builds the first layer, the raw layer: tables that hold exactly what arrived, every row and every value, mistakes included. Nothing is fixed here. When a number in a report looks wrong, the raw tables are where you check what the source really sent.

Reading a folder: read_csv('orders/*.csv') reads every file the pattern matches into one table. The * stands for any name. Two options make it a raw load. filename = true adds a filename column with the file each row came from, so you can trace a bad row back to its export. all_varchar = true reads every column as text (VARCHAR) instead of guessing each column's type. A reader that guesses sees the quantity two in 05-03's file and makes quantity text, but in a clean week it would make it a number. The table would change shape with whatever arrived.

CREATE OR REPLACE TABLE … AS SELECT … makes a table from a query and replaces the old one, so the script can rebuild every table each time it runs. If the shop also exported refunds to a refunds/ folder, their raw load would look like this:

query = (
    "CREATE OR REPLACE TABLE raw_refunds AS "
    "SELECT * "
    "FROM read_csv('refunds/*.csv', filename = true, all_varchar = true) "
)
con.execute(query)

After a load, first check how many rows came from each file. A missing or half-empty export shows up right away. Your script must print:

filename | rows
orders/2026-05-01.csv | 10
orders/2026-05-02.csv | 12
orders/2026-05-03.csv | 12
orders/2026-05-04.csv | 9
orders/2026-05-05.csv | 13
orders/2026-05-06.csv | 10
orders/2026-05-07.csv | 12
(7 rows)

That makes 78 rows in all. The second copy of order 5027, the repeats of 5040 and 5062, and the two are all still there. The check also prints the size of both raw tables (raw_customers: 300 rows and 11 columns, all text) and those problem rows with their file names.

Your task: in load_raw, finish the two queries. Read the seven daily files into raw_orders as text, with each row's file name, and read customers.csv into raw_customers as text. Then write the query at the bottom. It counts the rows of raw_orders per file, in file name order.

Assignment
Follow the Coding Tutorial to load the two raw tables and count the rows that arrived from each file.

Hint
'orders/*.csv' reads only the files inside the orders folder, not orders.csv beside it. customers.csv is a single file, so it needs no file name column. The count is COUNT(*) AS rows with GROUP BY filename, then ORDER BY filename.