Skip to content

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

Run 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.csv
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;
.output stdout

The .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:

.quit

Now check that the file exists and look at its contents:

ls raw-data
cat raw-data/monthly_sales_summary.csv

The 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(*) and SUM(quantity) to aggregate rows in a group.
  • Use .headers on, .mode csv, and .output filename in 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

Ready to use this in a job?

Rolebound applies to matching roles for you.

Start now

Written with AI and checked automatically. Tools change, so a command may behave differently in the version you install. Spotted a mistake? Tell us.

    Cookies on Rolebound

    We use necessary cookies to sign you in and keep the site working — those cannot be turned off. We would also like to set analytics and advertising cookies so we can see which pages help people get interviews and which adverts brought them here. Nothing optional is set unless you say yes, and you can change your mind at any time from “Cookie settings” in the footer. Read our Privacy Policy.