How to Build a Reproducible ETL Pipeline with Python, SQL, and Mage
TLDR
The first ETL run is the easy part. This tutorial builds a three-block Mage pipeline, runs the same orders batch twice, and corrects one record to prove that retries update the warehouse without creating duplicates.
The first run is not the hard part
A pipeline that works once is a demo. A pipeline that works twice is starting to become infrastructure.
Many ETL walkthroughs stop as soon as data reaches a table. Production is less polite. Jobs time out. Workers restart. Someone fixes a source record and reruns yesterday's batch. That is when a clean-looking pipeline starts duplicating orders or replacing data it was never supposed to touch.
The second run is the real architecture test. If the source has not changed, neither should the warehouse. If one order is corrected, the pipeline should update that order and leave everything else alone.
We will prove that behavior in Mage with three Python blocks:
- A Data Loader returns a fixed response from an example orders API.
- A Transformer validates types, required fields, values, and unique
order_idkeys. - A Data Exporter creates a warehouse table, upserts each order by
order_id, and reads the rows back.
SQLite is here for one reason: it lets you prove the complete behavior without cloud credentials or a pile of setup. Once the contract works, you can carry the same design to a production warehouse using its native MERGE or equivalent syntax.
The second run is the architecture test
Safe reruns are not a scheduler feature. They come from the decisions inside the pipeline: how records are identified, what gets rejected, how writes commit, and whether the destination is checked afterward.
This example uses four safeguards:
- A stable key:
order_ididentifies one business record across runs. - Validation before loading: the pipeline stops on missing fields, duplicate keys, invalid timestamps, or nonpositive values.
- A transactional upsert: each key is inserted once, then updated when a later batch contains a changed version.
- Warehouse readback: the exporter queries the destination after writing and checks the loaded keys and row count.
None of those safeguards is flashy. Together, they are the difference between a retry and a second copy of the same data.
This tutorial assumes pipeline runs are serialized and the incoming batch is authoritative for each order_id. If runs can overlap or older batches can arrive late, add a source version such as source_updated_at and update a destination row only when the incoming version is newer.
Prerequisites
You need a running Mage project and Python 3. That is it. SQLite ships with Python, so there is no database server to configure before you can test the idea. Create a standard batch pipeline in Mage, then add the three blocks below in order.
Mage passes each block's output to the next block. The decorators and structure below follow Mage's documented generic block pattern. If you want to explore the building blocks first, see the Mage block documentation.
Step 1: Extract a reproducible source batch
Create a Data Loader named load_orders. We are deliberately starting with boring data: three orders in a fixed list. That removes API drift from the experiment, so when the warehouse changes, you know the pipeline changed it. In a real pipeline, replace only SOURCE_RESPONSE with your API client or source query and keep the same record contract.
from copy import deepcopy
if 'data_loader' not in globals():
from mage_ai.data_preparation.decorators import data_loader
SOURCE_RESPONSE = [
{
'order_id': 'ord_1001',
'customer_id': 'cus_101',
'ordered_at': '2026-10-05T09:15:00Z',
'quantity': 2,
'unit_price_cents': 2500,
},
{
'order_id': 'ord_1002',
'customer_id': 'cus_102',
'ordered_at': '2026-10-05T09:18:00Z',
'quantity': 1,
'unit_price_cents': 7200,
},
{
'order_id': 'ord_1003',
'customer_id': 'cus_101',
'ordered_at': '2026-10-05T09:23:00Z',
'quantity': 4,
'unit_price_cents': 1250,
},
]
@data_loader
def load_orders(**kwargs):
return deepcopy(SOURCE_RESPONSE)
The deep copy is a small detail worth keeping. It protects the fixture when you deliberately mutate a record later to test a correction.
Step 2: Transform and validate the records
The warehouse should not be the first place a bad record announces itself. Add a Transformer named validate_orders downstream from load_orders. It normalizes each order, calculates order_total_cents, and stops the run before any write when the source contract is broken.
from datetime import datetime, timezone
if 'transformer' not in globals():
from mage_ai.data_preparation.decorators import transformer
REQUIRED_FIELDS = {
'order_id',
'customer_id',
'ordered_at',
'quantity',
'unit_price_cents',
}
def parse_utc_timestamp(value):
if not isinstance(value, str):
raise ValueError('ordered_at must be an ISO 8601 string')
try:
parsed = datetime.fromisoformat(value.replace('Z', '+00:00'))
except ValueError as error:
raise ValueError('ordered_at must be an ISO 8601 timestamp') from error
if parsed.utcoffset() is None:
raise ValueError('ordered_at must include a timezone')
return parsed.astimezone(timezone.utc).isoformat().replace('+00:00', 'Z')
def require_positive_integer(record, field, position):
value = record[field]
if isinstance(value, bool) or not isinstance(value, int):
raise ValueError(f'Record {position} field {field} must be an integer')
if value <= 0:
raise ValueError(f'Record {position} field {field} must be positive')
return value
@transformer
def validate_orders(records, *args, **kwargs):
if not isinstance(records, list) or not records:
raise ValueError('Expected a nonempty list of order records')
validated = []
seen_order_ids = set()
for position, record in enumerate(records):
missing = REQUIRED_FIELDS - record.keys()
if missing:
raise ValueError(f'Record {position} is missing: {sorted(missing)}')
if not isinstance(record['order_id'], str):
raise ValueError(f'Record {position} order_id must be a string')
if not isinstance(record['customer_id'], str):
raise ValueError(f'Record {position} customer_id must be a string')
order_id = record['order_id'].strip()
customer_id = record['customer_id'].strip()
quantity = require_positive_integer(record, 'quantity', position)
unit_price_cents = require_positive_integer(
record, 'unit_price_cents', position
)
if not order_id or not customer_id:
raise ValueError(f'Record {position} has a blank identifier')
if order_id in seen_order_ids:
raise ValueError(f'Duplicate order_id in batch: {order_id}')
seen_order_ids.add(order_id)
validated.append({
'order_id': order_id,
'customer_id': customer_id,
'ordered_at': parse_utc_timestamp(record['ordered_at']),
'quantity': quantity,
'unit_price_cents': unit_price_cents,
'order_total_cents': quantity * unit_price_cents,
})
return validated
This validation stays close to the load on purpose. You can add a richer contract framework later. The rule is simple: if the pipeline already knows a batch is invalid, it should not write that batch and hope someone notices tomorrow.
Step 3: Write like the job will run again
Add a Data Exporter named upsert_orders downstream from validate_orders. This is where rerun safety becomes real. Set database_path to a persistent path available to your Mage process. The default below creates orders_warehouse.db in the current project directory.
import sqlite3
if 'data_exporter' not in globals():
from mage_ai.data_preparation.decorators import data_exporter
CREATE_TABLE_SQL = '''
CREATE TABLE IF NOT EXISTS fact_orders (
order_id TEXT PRIMARY KEY,
customer_id TEXT NOT NULL,
ordered_at TEXT NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents > 0),
order_total_cents INTEGER NOT NULL CHECK (order_total_cents > 0)
)
'''
UPSERT_SQL = '''
INSERT INTO fact_orders (
order_id,
customer_id,
ordered_at,
quantity,
unit_price_cents,
order_total_cents
) VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(order_id) DO UPDATE SET
customer_id = excluded.customer_id,
ordered_at = excluded.ordered_at,
quantity = excluded.quantity,
unit_price_cents = excluded.unit_price_cents,
order_total_cents = excluded.order_total_cents
'''
@data_exporter
def upsert_orders(records, *args, **kwargs):
database_path = kwargs.get('database_path', 'orders_warehouse.db')
values = [
(
record['order_id'],
record['customer_id'],
record['ordered_at'],
record['quantity'],
record['unit_price_cents'],
record['order_total_cents'],
)
for record in records
]
with sqlite3.connect(database_path) as connection:
connection.execute(CREATE_TABLE_SQL)
connection.executemany(UPSERT_SQL, values)
rows = connection.execute(
'''
SELECT
order_id,
customer_id,
ordered_at,
quantity,
unit_price_cents,
order_total_cents
FROM fact_orders
WHERE order_id IN ({})
ORDER BY order_id
'''.format(', '.join('?' for _ in records)),
[record['order_id'] for record in records],
).fetchall()
expected_rows = sorted(
[
(
record['order_id'],
record['customer_id'],
record['ordered_at'],
record['quantity'],
record['unit_price_cents'],
record['order_total_cents'],
)
for record in records
],
key=lambda row: row[0],
)
if rows != expected_rows:
raise RuntimeError('Warehouse readback did not match the validated batch')
return rows
The connection context commits the table creation and upsert together when the block succeeds. If a database operation raises an exception, SQLite rolls the transaction back. The primary key blocks duplicate order_id values, and the conflict clause updates one existing row without replacing the table.
Prove it: run the pipeline again
The second run should be boring. Boring is the goal. Use this expected-outcome summary while you test the workflow:
- First run: three new orders create three rows.
- Identical rerun: no source changes leave three rows with no duplicates.
- Corrected rerun: changing
ord_1002quantity from1to3updates the existing row; the total remains three.
Run all three blocks once. The exporter should return three rows ordered by order_id. Ask the warehouse directly too. This query should return 3:
SELECT COUNT(*) AS order_count
FROM fact_orders;
Now run the pipeline again without changing a thing. The count should remain 3, not 6. If that feels uneventful, good. Reliable retries are supposed to be uneventful.
Next, give the pipeline one legitimate reason to change the warehouse. Update ord_1002 from quantity 1 to 3 in the Data Loader, run it again, and inspect that row:
SELECT order_id, quantity, unit_price_cents, order_total_cents
FROM fact_orders
WHERE order_id = 'ord_1002';
The expected result is one row with quantity = 3 and order_total_cents = 21600. The total table count should still be 3.
That short sequence proves the two behaviors a retry needs:
- Identical input does not create duplicates.
- Corrected input updates the record with the same business key.
Adapt the load for a cloud warehouse
SQLite made the proof cheap. It is not the point of the architecture. Keep the Data Loader and Transformer contract, then replace the exporter with your warehouse connector and native upsert statement. The exact SQL differs by destination, but the responsibilities do not:
- Stage or bind the validated batch.
- Match destination rows on
order_id. - Update matched rows and insert unmatched rows in one atomic operation when the warehouse supports it.
- Query the destination for the keys you just loaded.
- Compare the readback with the expected key count and important values.
Resist the tempting shortcut of translating this into DROP TABLE, CREATE OR REPLACE TABLE, or a replace-style dataframe write. Unless full replacement is the explicit dataset contract, those operations can erase records the retry batch never contained.
For scheduling, dependency management, retry policies, and backfills around this load pattern, read Data Pipeline Orchestration: The Ultimate Guide for Data Engineers.
Production checklist
Before putting this pattern on a schedule, make the uncomfortable decisions explicit:
- What field is the stable business key?
- Can one source batch contain the same key twice?
- Which fields are required, and which values are invalid?
- Does the destination load commit atomically?
- What should a corrected source record update?
- Can late data overwrite a newer record, and what source version prevents that?
- How will you verify the destination after each run?
- What evidence will distinguish a successful retry from a duplicated load?
If those answers are explicit, a retry becomes routine work instead of a small act of faith.
Build for the rerun
A safe ETL pipeline is not defined by how many services appear in its architecture diagram. It is defined by a clear source contract, deterministic transformations, explicit keys, guarded writes, and proof from the destination.
Start with the three-block pipeline in this tutorial. Run it twice. Change one source record. Inspect the warehouse. Once identical input leaves the warehouse unchanged and a correction updates exactly one row, replace the fixture and SQLite database with your real source and warehouse while preserving the same contract.
Ready to run and rerun this workflow in a managed environment? Build this ETL pipeline in Mage.



%2Fhero.png&w=3840&q=75&dpl=dpl_CzFdPjQoWHdkzMU6GFythmfNqgzG)