TapCub is live — analytics, insights and live chat, free on one platform
Analytics

ETL with SQL: A Runnable Extract-Transform-Load Example

ETL with SQL means extracting with a SELECT, transforming with expressions and window functions, and loading with INSERT ... SELECT and upserts. A complete example you can run, with row counts and money reconciled and a safe rerun.

Xu An Published Nov 20, 2026 13 min read Analytics
Sample data: 12 raw rows become 11 after removing the duplicate E003 and 9 clean rows after rejecting E007 (month 13) and E008 (no user). Rejects are kept in their own table with a reason.Sample data

ETL with SQL means extracting rows with a SELECT, transforming them with expressions, joins and window functions, and loading them with INSERT ... SELECT, using an upsert so a rerun is safe. Below is one complete example you can run in SQLite: a 12-row events table cleaned, deduplicated, loaded into three tables and reconciled to the last row and cent.

What you will learn

  • Put every cleaning rule in one view so you can inspect the result before anything is loaded
  • Make the load idempotent with a primary key and ON CONFLICT, then prove it by running it twice
  • Reconcile counts and money: 12 extracted = 1 duplicate + 2 rejected + 9 loaded, and purchases 466.00 − 45.00 rejected = 421.00

What ETL means when you do it in SQL

ETL stands for extract, transform, load. Amazon's description is the plainest: pull the relevant records from the source database, reshape them into a form that is more useful for analysis, and write the prepared data into the target database. SQL fits each step: extract is a SELECT (or an export), transform is expressions, joins and window functions inside a SELECT, and load is INSERT ... SELECT into a target table.

Doing ETL in SQL alone is realistic when the source and target are tables you can reach from one database session and the volume is in the thousands to low millions of rows. It stops being enough when you need scheduling, many sources or file formats, or alerts; the last section gives rules for that.

If the load comes before the transform, the order is called ELT. ETL vs ELT compares them, and the ETL glossary page has the short definition. This article is one concrete, runnable example of the SQL itself. Everything below runs unchanged in the SQLite command line, DB Browser for SQLite or Python's sqlite3 module (SQLite 3.25 or newer, because it uses window functions and upserts). The counts shown are the actual output of a run on SQLite 3.51.0.

The source table and what is wrong with it

Sample data: twelve rows exported from a shop's event log. They contain the problems real exports have.

CREATE TABLE raw_events (
  event_id TEXT, occurred_at TEXT, user_email TEXT,
  event_name TEXT, amount_cents INTEGER, page TEXT
);
INSERT INTO raw_events VALUES
 ('E001','2026-11-01 09:15:00','Ann@Example.com ','Purchase', 4500,'/checkout/done'),
 ('E002','2026-11-01T10:02:30','bob@example.com','purchase',12900,'/checkout/done'),
 ('E003','2026-11-01 11:40:00','ann@example.com','page_view',NULL,'/pricing'),
 ('E003','2026-11-01 11:40:00','ann@example.com','page_view',NULL,'/pricing'),
 ('E004','2026-11-01 23:59:59','cy@example.com','PURCHASE ', 2500,'/checkout/done'),
 ('E005','2026-11-02 08:00:00','bob@example.com','page_view',NULL,'/blog'),
 ('E006','2026-11-02 08:05:00','dee@example.com','purchase', 7800,'/checkout/done'),
 ('E007','2026-13-02 09:00:00','eve@example.com','purchase', 3000,'/checkout/done'),
 ('E008','2026-11-02 14:30:00',NULL,'purchase', 1500,'/checkout/done'),
 ('E009','2026-11-03 10:00:00','ann@example.com','purchase', 4500,'/checkout/done'),
 ('E010','2026-11-03 12:12:00','bob@example.com','page_view',NULL,'/pricing'),
 ('E011','2026-11-03 18:45:00','cy@example.com','purchase', 9900,'/checkout/done');
ProblemRowsRule to fix it
Same event_id twiceE003 (2 copies)Keep one row per event_id
Mixed timestamp separators (space and T)E002Normalise to YYYY-MM-DD HH:MM:SS
Impossible date (month 13)E007Reject with a reason, do not load
Missing userE008Reject with a reason, do not load
Mixed case, trailing spacesE001, E004lower(trim(...))
Money as integer centsall purchasesDivide by 100

The targets are three tables: events_clean for good rows, events_rejected for rows that failed a rule, and daily_summary for one row per day.

CREATE TABLE events_clean (
  event_id TEXT PRIMARY KEY, occurred_at TEXT NOT NULL, user_id TEXT NOT NULL,
  event_name TEXT NOT NULL, amount REAL, page TEXT);
CREATE TABLE events_rejected (event_id TEXT, reason TEXT);
CREATE TABLE daily_summary (
  day TEXT PRIMARY KEY, events INTEGER, buyers INTEGER, revenue REAL);

Extract: pull only what you need

In a real pipeline the extract step is a query against another system or a file export. Two habits keep it cheap. Select only the columns the target needs, and extract incrementally: store a watermark, the largest timestamp you have already loaded, and fetch only newer rows.

SELECT event_id, occurred_at, user_email, event_name, amount_cents, page
FROM raw_events
WHERE occurred_at > :last_loaded_at;

On the first run the watermark is an empty string and everything comes through. Two cautions. Compare on a column the source sets reliably, such as an insert timestamp, and rerun the last few minutes again to catch late rows: that is safe only because the load step below is idempotent. And do not use SELECT * into a table you do not control; a column added at the source will silently change your target.

In this example the raw rows are already in raw_events, so extract is the view's FROM clause, and the watermark is left as a comment for your own pipeline.

Transform: clean, deduplicate and normalise in one view

Put every rule in one view so that you can inspect the result before anything is loaded.

CREATE VIEW v_stage AS
WITH norm AS (
  SELECT event_id,
         datetime(replace(occurred_at,'T',' '))          AS occurred_at,
         lower(trim(user_email))                         AS user_id,
         lower(trim(event_name))                         AS event_name,
         amount_cents / 100.0                            AS amount,
         page,
         row_number() OVER (PARTITION BY event_id ORDER BY rowid) AS rn
  FROM raw_events)
SELECT * FROM norm WHERE rn = 1;
  • replace(occurred_at,'T',' ') turns 2026-11-01T10:02:30 into the space-separated form. SQLite's datetime() accepts both, and per its documentation returns NULL for input it cannot parse. That is what makes E007 detectable: datetime('2026-13-02 09:00:00') is NULL, so you can test for it instead of crashing the load.
  • lower(trim(...)) makes Ann@Example.com and ann@example.com the same user. Without it, one buyer becomes two in every count.
  • row_number() OVER (PARTITION BY event_id ORDER BY rowid) numbers the copies of each event; keeping rn = 1 removes the duplicate E003. Window functions arrived in SQLite 3.25.0 (2018-09-15).
  • Dividing by 100.0, not 100, avoids integer division.

Query the view and check it before loading: SELECT count(*) FROM v_stage returns 11 (12 raw rows minus the one duplicate), and the two bad rows show NULL in the timestamp or user column.

Load: rejects, clean rows and an idempotent summary

Three statements, in this order.

-- 1. rejects, with a reason you can read later
INSERT INTO events_rejected
SELECT event_id,
       CASE WHEN occurred_at IS NULL THEN 'bad timestamp' ELSE 'missing user' END
FROM v_stage
WHERE occurred_at IS NULL OR user_id IS NULL;

-- 2. clean rows
INSERT INTO events_clean
SELECT event_id, occurred_at, user_id, event_name, amount, page
FROM v_stage
WHERE occurred_at IS NOT NULL AND user_id IS NOT NULL
ON CONFLICT(event_id) DO NOTHING;

-- 3. daily summary, safe to run again
INSERT INTO daily_summary (day, events, buyers, revenue)
SELECT date(occurred_at), count(*),
       count(DISTINCT CASE WHEN event_name='purchase' THEN user_id END),
       coalesce(sum(CASE WHEN event_name='purchase' THEN amount END),0)
FROM events_clean
WHERE true
GROUP BY date(occurred_at)
ON CONFLICT(day) DO UPDATE SET
  events=excluded.events, buyers=excluded.buyers, revenue=excluded.revenue;

Two details matter. ON CONFLICT ... DO NOTHING on the primary key means a rerun cannot insert an event_id twice. In step 3, DO UPDATE with the excluded. prefix overwrites a day's row with the newly computed values, so running the job twice leaves one row per day with the same numbers. The upsert syntax arrived in SQLite 3.24.0 (2018-06-04); PostgreSQL documents the same ON CONFLICT form. When an upsert takes its values from a SELECT, SQLite's documentation says the SELECT should always have a WHERE clause, even WHERE true, so that the parser does not confuse ON with a join; that is the reason for WHERE true in step 3.

Result of the real run:

dayeventsbuyersrevenue
2026-11-0143199.00
2026-11-022178.00
2026-11-0332144.00
app.tapcub.com

Rows from extract to load

12-row sample

Extracted

12

After dedupe

11

Loaded clean

9

One duplicate is removed; two rows are rejected, one for an impossible date and one for a missing user.
Sample data
Sample data: 12 raw rows become 11 after removing the duplicate E003 and 9 clean rows after rejecting E007 (month 13) and E008 (no user). Rejects are kept in their own table with a reason.

Reconcile the counts and prove it can be rerun

A pipeline that cannot show where every row went is not finished. Check two identities.

Row count: extracted 12 = duplicates removed 1 + rejected 2 + loaded 9. The query:

SELECT (SELECT count(*) FROM raw_events) AS extracted,
       (SELECT count(*) FROM raw_events) - (SELECT count(*) FROM v_stage) AS dup,
       (SELECT count(*) FROM events_rejected) AS rej,
       (SELECT count(*) FROM events_clean) AS loaded;

It returns 12, 1, 2, 9, and 1 + 2 + 9 = 12.

Money: purchases in the raw table sum to 466.00 once amounts are divided by 100 (45 + 129 + 25 + 78 + 30 + 15 + 45 + 99). The rejected purchases are E007 (30.00) and E008 (15.00), a total of 45.00. So the clean purchases should sum to 466.00 − 45.00 = 421.00, and SELECT sum(amount) FROM events_clean WHERE event_name='purchase' returns exactly 421.0.

Rerun test: clear events_rejected, then run the three load statements a second time. Still 9 rows in events_clean and the same three daily_summary rows, with 2 rejects, because the rejects table was emptied first. If a rerun changes a count, the load is not idempotent and a failed run would corrupt the target.

CheckExpectedResult
Rows: extracted = duplicates + rejected + loaded12 = 1 + 2 + 912, 1, 2, 9
Purchase total after rejects466.00 − 45.00 = 421.00421.0
Rerun: rows in events_clean99
Rerun: rows in daily_summary33
Rerun: unexplained differences00

Sample data. Every check passed on a real run; if a rerun changes any count, the load is not idempotent.

When SQL alone stops being enough

SQL is the transform language of most data pipelines, but the glue around it is not SQL. Match the signal to the response:

SignalWhat it meansWhat to add
The job must run every night at 02:00You need a schedulercron, a workflow tool or your database's own job feature
Source and target are different systemsA single session cannot read bothAn export file or a connector for extract and load
The raw table has hundreds of millions of rowsA full rescan each run is too slowIncremental loads on the watermark, partitions, a warehouse
Rules change oftenHand-edited view definitions driftKeep the SQL in version control and review changes
Someone must be told when it breaksNothing is watching the jobRow-count checks like the one above, wired to an alert

If you run the transform inside the warehouse after loading raw data, you are doing ELT, which is covered in ETL vs ELT. The SQL in this article moves to that setup unchanged; only the order of the steps differs.

How to send the result to TapCub

Take events_clean from above and bring it into TapCub as events.

  1. Export the load-ready shape. Select the columns TapCub's CSV wizard maps, with the time as ISO text: SELECT occurred_at AS time, event_name AS event, user_id, page FROM events_clean. Use an internal user ID, not an email, as user_id; the demo uses emails only to show lower-casing. Keep personal data out explains what to leave out.
  2. Import the file once. In All-platform analytics › Data › Data import, choose Import file. The wizard detects the type, shows the first 20 rows and guesses the mapping from column names such as time, event_name and user_id. A file holds at most 100,000 rows, so split larger exports. Imported events carry the platform import, which you can filter on.
  3. Keep the loading ongoing with the server events API. The import page is for migrations, not continuous collection; for a scheduled job, send events from your pipeline through the server events API; see send server-side events.
  4. Query it back. The SQL tab in All-platform analytics › Behavior › Behavior analysis accepts one SELECT or WITH over the project's v_events, v_sessions, v_persons and v_autocapture views, returns at most 50,000 rows and times out after 30 seconds. Writes are rejected, so TapCub's SQL is for reading what you loaded, not for the load itself. See query data with SQL.

TapCub is not an ETL tool: it does not schedule or run your transformations. It receives the cleaned events and lets you analyse them with the same reports as events from the script.

FAQ

What is ETL in SQL?

It is extract, transform and load done with SQL statements. Extract is a SELECT or an export, transform is the cleaning and reshaping inside the SELECT, and load is INSERT ... SELECT into the target table. It works well for modest volumes where source and target are in reach of one database session, and needs a scheduler for repeated runs.

Can you do ETL with SQL only?

For one-off or modest jobs, yes: the example above extracts, cleans, deduplicates, loads and reconciles with SQL alone. You still need something outside SQL to run it on a schedule, to move data between separate systems and to alert you when a check fails. Those parts are small compared with the transformations.

How do you make a SQL load safe to rerun?

Give the target a primary key and use an upsert: ON CONFLICT ... DO NOTHING for facts that never change and DO UPDATE SET col = excluded.col for summaries. Then a rerun produces the same rows. Test it by running the load twice and comparing row counts, as in the reconciliation section.

What is the difference between ETL and ELT in SQL?

ETL transforms the data before loading it; ELT loads raw data first and transforms it with SQL inside the target. The SQL is the same. ELT keeps the raw table, so you can fix a rule and rebuild, which is easier when the target is a warehouse with plenty of compute.

Which SQL features matter most for ETL?

Cleaning functions such as trim, lower and date parsing; CASE for rules; GROUP BY for summaries; window functions such as row_number() for deduplication; and upserts for safe reloads. These cover most transformations; the rest of the work is checking counts.

Sources

  • Amazon Web Services, What is ETL?: definition of extract, transform and load, and how ELT reverses the order. Checked 2026-11-20.
  • SQLite, UPSERT: ON CONFLICT syntax, the excluded. qualifier, the 3.24.0 version and the WHERE clause note for INSERT ... SELECT. Checked 2026-11-20.
  • SQLite, Date and time functions: accepted time formats and NULL for unparseable input. Checked 2026-11-20.
  • PostgreSQL, INSERT: the ON CONFLICT DO NOTHING and DO UPDATE clauses. Checked 2026-11-20.
  • SQLite, Window functions: added in version 3.25.0 (2018-09-15), including row_number(). Checked 2026-11-20.
Filed underAnalytics

Was this article helpful?

We use your answers to fix and expand this page.

Xu An

Customer Experience Consultant

Writes about live chat, visitor conversations, and self-service, sharing practical ways to make customer communication clearer and more effective.

Load your cleaned events into one place

Import a CSV of events, send a continuing feed from a script, and query the project's events with read-only SQL.

See your data clearly. Find your growth.

Every click, backed by data. Install one line of code and see your first numbers in a minute.

No credit card · Free plans for analytics and chat · Cookieless analytics