Lesson 5 of 6
Export a Summary to the Lake
Query the warehouse to create a monthly sales summary and export it as a CSV file back into the data lake.
8 minData Engineering
After this lesson you will be able to query the warehouse for a monthly sales summary and export the result as a CSV file into the data lake.
Query a Monthly Sales Summary
The warehouse now has clean rows in the orders table. Each row has an order_date in the format YYYY-MM-DD. You want a summary that shows, for each month, how many orders were placed and the total quantity sold.
SQLite has a function called strftime that extracts parts of a date. The format string %Y-%m gives the year and month, for example 2025-01. You can group rows by this value and count the orders and sum the quantities.
Open the SQLite tool for your warehouse:
sqlite3 warehouse.dbRun this query to see the monthly summary:
SELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS order_count,
SUM(quantity) AS total_quantity
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;The AS keywords give names to the output columns. COUNT(*) counts the rows in each group, and SUM(quantity) adds up the quantities. The GROUP BY clause groups rows that have the same year and month. The ORDER BY month sorts the result from the earliest month to the latest.
The output shows one row per month. For the orders you have loaded, you will see two rows: one for 2025-01 and one for 2025-02. The counts and sums depend on the rows in your orders table.
Export the Summary to a CSV File
You can write the result of a query to a file using the SQLite tool. First turn on column headers so the first row of the file contains the output column names. Then set the output mode to CSV, then set the output file, then run the query.
Still inside the SQLite tool, run these commands:
.headers on.mode csv.output raw-data/monthly_sales_summary.csvSELECT strftime('%Y-%m', order_date) AS month,
COUNT(*) AS order_count,
SUM(quantity) AS total_quantity
FROM orders
GROUP BY strftime('%Y-%m', order_date)
ORDER BY month;.output stdoutThe .headers on command makes the CSV file include a first row with the column names. The .output command with a file name sends the query result to that file instead of the screen. The file is created inside the raw-data folder, which is your data lake. The final .output stdout returns output to the screen.
Exit the SQLite tool:
.quitNow check that the file exists and look at its contents:
ls raw-datacat raw-data/monthly_sales_summary.csvThe CSV file has a header row with month, order_count, and total_quantity, followed by one row for each month. The values are separated by commas.
Why Export to the Lake
The data lake holds raw files and now also holds a summary file. A summary is useful for reporting or for other tools that cannot read the SQLite database directly. By exporting the summary as a CSV file, you create a simple, portable result that anyone can open in a spreadsheet.
The summary is a derived file: it is created from the warehouse data. If the warehouse changes, you can run the query again to create a new summary file. This keeps the lake up to date without changing the raw files.
You have now completed the main steps of the pipeline: load raw files, clean and join data, and export a summary. The next lesson will show you how to automate all these steps with a script.
Key takeaways
- Use
strftime('%Y-%m', order_date)to group dates by month in SQLite. - Use
COUNT(*)andSUM(quantity)to aggregate rows in a group. - Use
.headers on,.mode csv, and.output filenamein the SQLite tool to write query results to a CSV file with a header row. - Exporting a summary to the data lake creates a portable file for reporting.
- The summary file can be regenerated any time by running the query again.
Try it
Open sqlite3 warehouse.db and write a query that creates a summary of total quantity sold per product. The output columns should be product_id, product_name, and total_quantity. Join the orders table with the products table on product_id, group by product_id and product_name, and order by product_id. Set .headers on, then set .mode csv, then set .output raw-data/product_sales_summary.csv. Run the query, then set .output stdout and exit SQLite. Use cat to view the file. A good result is a CSV file with a header row and one row per product, showing the product ID, product name, and total quantity sold across all orders.
Official documentation
Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.