The data engineering community has spent years debating ETL vs ELT, batch vs streaming, and Airflow vs everything else. Most of these debates produce conference talks and blog posts that look great on paper but collapse under the weight of production reality.
After building and maintaining pipelines that process millions of records daily across multiple client environments, we’ve settled on patterns that survive contact with real data. This article documents what works, what fails silently, and what every team learns the hard way.
ELT Over ETL: The Structural Argument
The traditional ETL (Extract, Transform, Load) pattern transforms data before it reaches the warehouse. The modern ELT (Extract, Load, Transform) pattern loads raw data first, then transforms it inside the warehouse.
We default to ELT for one structural reason: raw data preservation.
When you transform before loading, you lose the original data. If your transformation logic has a bug—and it will—you need to re-extract from the source system. Depending on the source (APIs with rate limits, legacy databases with no change tracking, third-party SaaS platforms), re-extraction ranges from “annoying” to “impossible.”
With ELT, the raw data sits in a staging layer. When transformation logic changes, you reprocess from staging. No re-extraction needed. The source system is never touched twice.
The Practical Exception
ELT breaks down when the raw data volume is genuinely enormous and the warehouse cost model makes storing raw data prohibitive. If you’re ingesting 500GB/day of raw IoT telemetry and only need 5GB of aggregated output, transforming before loading makes economic sense.
The decision framework:
| Factor | Favor ELT | Favor ETL |
|---|---|---|
| Source re-extraction cost | High | Low |
| Raw data volume vs output | Similar | 10x+ difference |
| Transformation logic stability | Evolving | Stable |
| Warehouse storage cost | Low (ClickHouse, S3) | High (BigQuery on-demand) |
| Regulatory audit requirements | Yes | No |
Orchestration: Mage.ai in Production
We use Mage.ai as our primary orchestrator for client-facing production pipelines. The decision was deliberate and came after evaluating Airflow, Prefect, and Dagster across multiple dimensions.
Why Not Airflow
Apache Airflow is the industry standard, and for good reason. It’s battle-tested, has massive community support, and every data engineer knows it. But Airflow has three operational costs that compound in consulting environments:
- Infrastructure overhead: Airflow requires a metadata database, a web server, a scheduler, and workers. For a single client deployment, that’s 4+ containers before you’ve run a single pipeline.
- DAG debugging: When a task fails, debugging requires navigating logs across multiple components. The feedback loop between “code change” and “see result” is measured in minutes, not seconds.
- Python-only: Every transformation must be Python. For SQL-heavy transformations (which most analytical pipelines are), you’re wrapping SQL in Python strings—an ergonomic disaster.
What Mage.ai Gets Right
Mage.ai addresses these friction points:
- Blocks as first-class units: Each pipeline step (data loader, transformer, exporter) is an isolated block with its own execution context. SQL blocks execute SQL natively. Python blocks execute Python. No wrapping.
- Interactive development: Blocks can be executed individually during development with immediate output preview. The feedback loop drops from minutes to seconds.
- Lighter footprint: A single Mage instance serves as UI, scheduler, and executor. For client deployments where infrastructure budget is constrained, this matters.
Pipeline Structure Pattern
Every production pipeline follows a consistent block architecture:
Pipeline: client_sales_ingestion
├── [Loader] api_extract_sales # Paginated API extraction
├── [Transformer] validate_schema # Schema enforcement + null handling
├── [Transformer] enrich_dimensions # Join reference data (regions, categories)
├── [Transformer] compute_metrics # Business calculations
├── [Exporter] load_clickhouse # Batch insert to analytical store
└── [Exporter] notify_status # Slack/webhook on success or failureThe key discipline: loaders never transform, transformers never load. Each block has exactly one responsibility. When a pipeline fails at enrich_dimensions, you know the extraction succeeded and the problem is in the enrichment logic. No guessing.
The Silent Failure Problem
The most dangerous pipeline failures are the ones that don’t raise exceptions. The pipeline completes successfully, reports green status, but the output data is wrong.
Common Silent Failures
1. Schema drift without detection
The source API adds a new field or changes a field type. Your loader succeeds because it’s reading JSON. Your transformer succeeds because it only accesses known fields. Your exporter succeeds because ClickHouse accepts the insert. But the new field—which contains critical business data—is silently dropped.
Fix: Explicit schema validation at the loader boundary:
from pydantic import BaseModel, ConfigDict
class SalesRecord(BaseModel):
model_config = ConfigDict(extra='forbid') # Reject unknown fields
transaction_id: str
amount: Decimal
currency: str
customer_id: int
created_at: datetime
def validate_batch(raw_records: list[dict]) -> list[SalesRecord]:
valid, rejected = [], []
for record in raw_records:
try:
valid.append(SalesRecord(**record))
except ValidationError as e:
rejected.append({'record': record, 'error': str(e)})
if rejected:
log_rejected_records(rejected) # Don't lose visibility
if len(rejected) / len(raw_records) > 0.05:
raise SchemaViolationThreshold(
f"{len(rejected)}/{len(raw_records)} records failed validation"
)
return validThe extra='forbid' configuration is critical. It forces the pipeline to fail loudly when the source schema changes, rather than silently ignoring new fields.
2. Duplicate records from idempotency failures
API pagination with cursor-based offsets can produce duplicates when records are inserted into the source during extraction. Your pipeline runs daily, extracts page 1-100, but page 50 shifts because 200 new records were added mid-extraction.
Fix: Deduplication at the exporter with deterministic keys:
-- ClickHouse ReplacingMergeTree handles this natively
CREATE TABLE analytics.sales_events
(
transaction_id String,
amount Decimal(18, 2),
-- ... other fields
_loaded_at DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(_loaded_at)
ORDER BY transaction_id;ReplacingMergeTree keeps only the latest version of each row (by transaction_id). Duplicates are automatically collapsed during background merges.
3. Timezone mismatches
The source API returns timestamps in UTC. Your transformation assumes local time. Revenue attributed to “Monday” is actually Sunday night. Your weekly reports are consistently wrong by 8-16 hours depending on the client’s timezone.
Fix: All internal timestamps are UTC. Timezone conversion happens exclusively at the presentation layer (Superset/Flutter). Never in the pipeline.
Retry Patterns and Dead Letter Queues
Production pipelines fail. Networks timeout. APIs rate-limit. Databases lock. The question isn’t whether failures happen—it’s how the system recovers.
Exponential Backoff with Jitter
For transient failures (HTTP 429, connection timeouts), we retry with exponential backoff plus random jitter:
import random
import time
def retry_with_backoff(func, max_retries=5, base_delay=1.0):
for attempt in range(max_retries):
try:
return func()
except TransientError:
if attempt == max_retries - 1:
raise
delay = base_delay * (2 ** attempt) + random.uniform(0, 1)
time.sleep(delay)The jitter prevents thundering herd problems when multiple pipelines retry against the same API simultaneously.
Dead Letter Pattern
For records that fail validation or transformation, we never drop them silently. Failed records are routed to a dead letter table:
CREATE TABLE staging.dead_letters
(
pipeline_name String,
block_name String,
record String, -- JSON serialized original record
error_message String,
failed_at DateTime DEFAULT now(),
resolved Boolean DEFAULT false
)
ENGINE = MergeTree()
ORDER BY (pipeline_name, failed_at);A daily monitoring query surfaces unresolved dead letters. Engineers review, fix the root cause, and reprocess. No data is lost.
Monitoring: The Three Metrics That Matter
Pipeline monitoring typically generates noise. Hundreds of metrics, dashboards with 30 panels, alerts firing every hour. We’ve narrowed operational monitoring to three core metrics:
1. Freshness
How old is the most recent record in the target table? If the pipeline runs hourly and the newest record is 3 hours old, something is wrong.
SELECT
dateDiff('minute', max(created_at), now()) AS freshness_minutes
FROM analytics.sales_events
WHERE tenant_id = {tenant_id}Alert threshold: 2x the expected pipeline interval.
2. Volume Anomaly
Did today’s pipeline process a reasonable number of records compared to the rolling average?
SELECT
today_count,
avg_7d,
abs(today_count - avg_7d) / avg_7d AS deviation_pct
FROM (
SELECT
countIf(event_date = today()) AS today_count,
countIf(event_date >= today() - 7) / 7 AS avg_7d
FROM analytics.sales_events
)If volume drops by >50% or spikes by >200%, investigate. Either the source changed or the pipeline is filtering incorrectly.
3. Dead Letter Rate
What percentage of incoming records failed processing?
If dead letter rate exceeds 5%, the pipeline is broken. If it’s between 1-5%, there’s a data quality issue at the source. Below 1% is normal operational noise.
The Operational Reality
Pipeline engineering is 20% building and 80% maintaining. The initial pipeline takes a day to write. The monitoring, retry logic, schema validation, dead letter handling, and timezone management take a week. But that week of defensive engineering prevents months of “why don’t the numbers match?” investigations that consume analyst time, erode stakeholder trust, and eventually lead to the pipeline being replaced by manual Excel uploads.
Build the boring infrastructure first. The exciting data science work becomes possible only when the plumbing is reliable.