DT4: Streams, big data and file formats
File formats and data lakes
🎯You'll be able to
Choose between CSV, JSON and Parquet, store large data in partitioned folders, and explain what table formats such as Delta Lake and Iceberg add.
🏠The everyday picture
A library shelves books by subject and then by author, so you walk to one shelf instead of searching every room. Partitioned, columnar files do the same for a query.
📘What it really is
- CSV: plain text, readable anywhere, no types, large, slow to scan. Fine for exchange; poor for storage at scale.
- JSON / JSON Lines: nested data, readable, also large.
- Parquet: columnar and compressed, with types and statistics stored inside. A query that needs 3 of 12 columns reads only those 3.
-
Partitioned folders:
orders/order_date=2026-10-05/data_0.parquet. A query filtered onorder_datereads only matching folders (partition pruning). Partition by a column that most queries filter on, with not too many small files (days, not seconds). -
A data lake is files like these in cheap object storage (Amazon S3, Google Cloud Storage, Azure Data Lake Storage).
- Table formats (Delta Lake, Apache Iceberg, Apache Hudi) add a log on top of Parquet files: safe concurrent writes, updates and deletes, schema changes and "time travel" to an earlier version. A lakehouse is a lake with a table format, queried like a warehouse.
📝A worked example
Input: export SabziSathi's cleaned order lines three ways with DuckDB:
copy (select * from clean.order_lines) to 'lines.csv' (header);
copy (select * from clean.order_lines) to 'lines.parquet' (format parquet);
copy (select * from clean.order_lines) to 'by_day' (format parquet, partition_by (order_date));
Then explain analyze select count(*) from read_parquet('by_day/*/*.parquet', hive_partitioning=true) where order_date = '2026-10-05'.
Expected output: the CSV is about 1.28 MB and the single Parquet file about 164 KB, roughly eight times smaller. The partitioned query reads 1 of 30 files ("Scanning Files: 1/30") and counts 302 lines. Measured on 1 November 2026 with DuckDB 2.0.0.
🌍Where it's used
- Data lakes: raw and cleaned data kept as Parquet in object storage.
- Warehouses: BigQuery and Snowflake load and export Parquet, and can query lake files directly.
- Lakehouses: Databricks with Delta Lake; Snowflake, BigQuery and Spark with Iceberg.
📝Case scenario
An analytics team paid for a query engine by the amount of data scanned. Their event data was one folder of CSV files. Converting to Parquet partitioned by day cut a typical query from scanning 2 TB to about 20 GB, and the monthly bill fell with it.
🖱️Try it
Run the worked example in the kit (inside python -c "import duckdb; ...", or the DuckDB command line). Then query one store's revenue
from the single Parquet file and look at the plan's "Data Read". Success: you have the two file sizes and can point to "1/30" in the plan.
⚠️Common mistakes
- Storing large data as CSV. Sign: slow, costly scans and type surprises. Fix: Parquet, with types.
- Partitioning too finely. Sign: millions of tiny files and slow listings. Fix: partition by day or month, not by second or id.
- Changing files under readers. Sign: a query reads half-written data. Fix: a table format, or write new files and switch over.
🛠️For your project
Not in the kit's tests. The capstone's warehouse design notes ask which tables you'd partition, and by what.
✅Quick check
Why is Parquet faster to query than CSV?
Show the answer
It's columnar and compressed, so a query reads only the columns it needs, with types already known.
What does partition pruning do?
Show the answer
It skips folders that can't match the filter.