Load Files into Tables

In the last lesson you read a file in place with FROM 'orders.csv': DuckDB reads the file again on every query. A warehouse keeps its data in tables instead. CREATE TABLE name AS SELECT … runs the query once and stores its rows in the database under that name. Later queries read them with FROM name, without quotes.

The shop sends one export a day into the folder orders/, from 2026-05-01.csv to 2026-05-07.csv. read_csv can read them all at once: in the path 'orders/*.csv' the * matches any text, so the path names every CSV file of the folder, and the rows of all the files are stacked into one result. Two of its options make a good raw table. filename = true adds a column filename with the file each row came from. all_varchar = true keeps every value as text (VARCHAR), exactly as it arrived, instead of guessing a type for each column.

con.execute(
    "CREATE TABLE customers AS "
    "SELECT * FROM read_csv('customers.csv', all_varchar = true)"
)
# the table customers: 300 rows, every column text

con.execute(query) sends a statement to DuckDB. A CREATE TABLE prints nothing: it makes the table. Why a data engineer loads raw files this way: when a number looks wrong next week, filename tells you which day's file to open, and text columns mean one bad value (05-03's file has the quantity 'two') can't stop the load or change a column's type. Types come later, in a clean table built from this one. And the first check after any load is a count per file: did every day arrive, with the rows you expected?

The check prints your count per file, then the columns of raw_orders and their types:

filename | row_count
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)
column_name | column_type
order_id | VARCHAR
customer_id | VARCHAR
product | VARCHAR
quantity | VARCHAR
unit_price | VARCHAR
status | VARCHAR
ordered_at | VARCHAR
filename | VARCHAR
(8 rows)

Your task: in the first query, make raw_orders from every file of orders/, with the file names and every column as text, and run it with con.execute. In the second query, select each filename with its number of rows, named row_count, from raw_orders: one group per file, sorted by file name.

Assignment
Follow the Coding Tutorial to load the week's files into raw_orders and count the rows each file brought.

Hint
The first query has three pieces: CREATE TABLE raw_orders AS, SELECT * and a FROM read_csv(…) line with both options. The second is a GROUP BY query like the ones you know, read FROM raw_orders without quotes.