ETL concepts in SQL

ETL means Extract the rows you need, Transform them (clean, total), and Load the result into a table for reports.

About 5–10 minutes · no sign-up needed

Example

Extract, transform, load: a reporting table in three steps

CREATE TABLE daily_sales (order_date DATE, total NUMERIC);
INSERT INTO daily_sales
SELECT order_date, SUM(revenue) FROM orders WHERE revenue > 0 GROUP BY order_date;
SELECT * FROM daily_sales ORDER BY order_date

Result:

order_date	total
2024-03-01	349.94
2024-03-02	134.0
2024-03-03	22.5

Step by step

Load needs somewhere to go: a table shaped like the report.
```
CREATE TABLE daily_sales (order_date DATE, total NUMERIC);
```

SELECT the rows (extract), leave out bad revenue and total by day (transform), INSERT the result (load).

CREATE TABLE daily_sales (order_date DATE, total NUMERIC);
INSERT INTO daily_sales
SELECT order_date, SUM(revenue) FROM orders WHERE revenue > 0 GROUP BY order_date;

Always look at the loaded table before anyone reports from it.

CREATE TABLE daily_sales (order_date DATE, total NUMERIC);
INSERT INTO daily_sales
SELECT order_date, SUM(revenue) FROM orders WHERE revenue > 0 GROUP BY order_date;
SELECT * FROM daily_sales ORDER BY order_date;

Practice

Try each one in your head first, then open the answer.

1. Extract and transform: show each order_date's total revenue as total, leaving out negative revenue.

SELECT order_date, SUM(revenue) AS total FROM orders WHERE revenue > 0 GROUP BY order_date

2. orders_clean already exists, with the same columns as orders. Load into it every order with revenue above 0.

INSERT INTO orders_clean SELECT * FROM orders WHERE revenue > 0

3. daily_sales (order_date, total) already exists. Load each day's total revenue into it, leaving out negative revenue. Then show daily_sales ordered by order_date.

INSERT INTO daily_sales SELECT order_date, SUM(revenue) FROM orders WHERE revenue > 0 GROUP BY order_date; SELECT * FROM daily_sales ORDER BY order_date

4. category_sales (category, total) already exists. Load each category's total revenue into it, leaving out negative revenue. Then show it ordered by category.

INSERT INTO category_sales SELECT category, SUM(revenue) FROM orders WHERE revenue > 0 GROUP BY category; SELECT * FROM category_sales ORDER BY category

5. Create daily_sales (order_date DATE, total NUMERIC), load each day's total revenue leaving out negative revenue, then show it ordered by order_date.

CREATE TABLE daily_sales (order_date DATE, total NUMERIC); INSERT INTO daily_sales SELECT order_date, SUM(revenue) FROM orders WHERE revenue > 0 GROUP BY order_date; SELECT * FROM daily_sales ORDER BY order_date

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed