When you're moving a few thousand records, ETL is mostly a data-processing problem.
When you're moving 25+ million records across 120+ school districts, it becomes something else entirely:
A distributed-systems reliability problem where you need to prove that the data moved correctly.
In an education platform, this distinction matters. Student, enrollment, attendance, and course data isn't just another dataset. A partial or silently corrupted load can affect downstream analytics, interventions, reporting, and operational decisions.
This is the story of the architecture and reliability principles we used to approach that problem.
The Problem: Moving Millions of Records Without Losing Trust
Our platform needed to ingest data from 120+ school districts, each with its own data volume, timing characteristics, and potential failure modes.
Across a typical sync cycle, the pipeline could process 25M+ records spanning areas such as:
- Students
- Enrollments
- Attendance
- Courses
- Sections
- Staff and related relationships
The challenge wasn't simply:
"Can we process 25 million records?"
The harder questions were:
- Did we receive everything the source intended to send?
- Did every accepted record get processed?
- What happened when a job failed halfway through?
- Could we safely retry?
- How do we detect partial loads?
- Can we explain exactly what happened to a district's data days or weeks later?
That led us to four primary requirements.
1. Scale
The pipeline needed to handle 25M+ records per sync cycle, while accommodating significant differences between districts.
One district might have a relatively small dataset.
Another could generate millions of records.
That meant designing around variable workloads, rather than assuming every sync would behave the same way.
2. Correctness
Successful execution isn't the same thing as successful data ingestion.
A job can return 200 OK, complete without throwing an exception, and still produce an incomplete dataset.
For example:
Source: 1,250,000 records
Target: 1,247,831 records
From an infrastructure perspective, the job might appear healthy.
From a data perspective, something went wrong.
We therefore treated reconciliation as a first-class part of the pipeline, rather than something performed manually after an incident.
3. Auditability
Education data requires a strong operational audit trail.
We needed to answer questions such as:
When was this district synchronized?
How many records did we receive?
How many were validated?
How many were successfully processed?
Were any records rejected?
Did the job retry?
Did reconciliation pass?
If something failed, where did it fail?
This pushed us toward designing observability and auditability into the pipeline rather than bolting them on afterward.
4. Reliability
At this scale, failures are inevitable.
Networks fail.
External SIS systems become unavailable.
Workers restart.
Individual batches fail.
Dependencies time out.
A downstream service can become temporarily unavailable.
The architecture therefore had to assume that partial failure is normal.
The goal wasn't to eliminate failure.
It was to make failure safe, detectable, recoverable, and explainable.
The Architecture
At a high level, the pipeline looked like this:
┌─────────────────────┐
│ School Districts │
│ / SIS │
└──────────┬──────────┘
│
▼
┌─────────────────────┐
│ Ingestion │
│ Scheduled / Batch │
│ Processing │
└──────────┬──────────┘
│
▼
┌─────────────────────┐
│ Validation │
│ Schema + Integrity │
│ Checks │
└──────────┬──────────┘
│
▼
┌─────────────────────┐
│ Processing │
│ Transform + Load │
│ Idempotently │
└──────────┬──────────┘
│
▼
┌─────────────────────┐
│ Reconciliation │
│ Source vs. Target │
│ Counts │
└──────────┬──────────┘
│
▼
┌─────────────────────┐
│ Observability & │
│ Audit │
└─────────────────────┘
The important part isn't any individual technology.
It's the control points between the stages.
1. Idempotent Ingestion
The first principle we adopted was simple:
Assume every operation can be retried.
A network timeout doesn't necessarily mean that the server didn't process the request.
A worker can crash after writing data but before acknowledging completion.
A scheduler can trigger the same operation more than once.
If the pipeline isn't idempotent, retries can turn transient failures into permanent data corruption.
We therefore designed operations around stable identifiers and idempotency keys.
Conceptually:
record + operation + source version
│
▼
deterministic identity
│
▼
idempotent processing
A retry should result in:
First attempt → write
Second attempt → recognize existing operation
Third attempt → same final state
rather than:
First attempt → write
Second attempt → duplicate write
This became one of the most important design principles in the system.
2. Validate Before Processing
We didn't want malformed data to travel deep into the pipeline before being discovered.
Validation happened as early as practical.
Typical validation categories included:
- Schema validation
- Required-field validation
- Referential integrity
- Data-type validation
- Source-specific business rules
- Duplicate detection
This created a useful separation:
Raw input
│
▼
Validation
│
├── Invalid → rejected / dead-letter path
│
▼
Valid records
│
▼
Processing
That separation also improved troubleshooting.
Instead of asking:
"Why did this record disappear?"
we could ask:
"Was this record rejected during validation, or was it accepted and subsequently failed during processing?"
That distinction is extremely valuable in production.
3. Process in Batches, Not One Giant Transaction
With tens of millions of records, treating an entire sync as one atomic operation is usually impractical.
Instead, we divided workloads into manageable units.
Conceptually:
25M records
│
├── Batch 1
├── Batch 2
├── Batch 3
├── ...
└── Batch N
This provides several advantages:
Failure isolation
If one batch fails, we don't necessarily need to restart the entire sync.
Retryability
Failed batches can be retried independently.
Parallelism
Where appropriate, independent batches can be processed concurrently.
Operational visibility
Instead of:
Sync = FAILED
we can reason about:
1,248 batches completed
12 batches retried
2 batches failed
That is much more actionable.
4. Reconciliation: The Most Important Control
One of the biggest lessons from operating high-volume data pipelines is this:
A successful job does not prove a successful data load.
That's why reconciliation became an explicit stage.
At the end of a load, we compare source and target measurements.
For example:
Source Target
Students 1,250,000 1,250,000
Enrollments 3,840,000 3,840,000
Attendance 8,910,000 8,909,997
Courses 1,120,000 1,120,000
Even a small mismatch matters.
The goal isn't necessarily to prevent every mismatch.
The goal is to ensure that a mismatch cannot silently pass through the system.
A simplified control flow looks like:
Sync Complete
│
▼
Compare source/target
│
┌────────┴────────┐
│ │
Match Mismatch
│ │
▼ ▼
Success Alert
│
▼
Investigate /
retry / repair
This changed the operational question from:
"Did the job finish?"
to:
"Can we prove the expected data arrived?"
That is a much stronger guarantee.
5. Dead-Letter Paths Are Part of the Design
Not every record that enters the pipeline will successfully complete.
Instead of allowing problematic records to disappear into logs, we need a deliberate failure path.
Processing
│
┌────────┴────────┐
│ │
Success Failure
│ │
▼ ▼
Target Dead-letter
│
▼
Investigation
The dead-letter path provides two important properties:
- The primary pipeline can continue processing valid data.
- Failed records remain visible and recoverable.
This is particularly important when one malformed record shouldn't block millions of valid records.
6. Observability Must Follow the Data
Traditional application monitoring often focuses on infrastructure metrics:
- CPU
- Memory
- Latency
- Error rates
- Availability
Those are important, but they aren't enough for data pipelines.
We also needed data-level telemetry.
For each sync, useful metrics included concepts such as:
records_received
records_validated
records_processed
records_rejected
records_failed
records_retried
records_loaded
reconciliation_status
This lets operators move from:
"Something failed."
to:
"District X received 2.4M records.
2.39M were processed successfully.
4,812 were rejected during validation.
127 failed during transformation.
Reconciliation detected a 127-record mismatch."
That's the difference between monitoring infrastructure and observing a data pipeline.
7. Design for Partial Failure
Distributed systems fail partially.
A pipeline processing millions of records should assume that:
- Some batches succeed.
- Some batches fail.
- Some requests time out.
- Some retries succeed.
- Some retries fail again.
- External systems can disappear temporarily.
This leads to an important architectural principle:
Don't design around the assumption that the entire operation succeeds or fails together.
Instead, explicitly model intermediate states.
For example:
QUEUED
↓
PROCESSING
↓
VALIDATED
↓
LOADED
↓
RECONCILED
And failure states:
PROCESSING
↓
FAILED
↓
RETRYING
↓
PROCESSING
This state-oriented approach makes both automation and operational debugging significantly easier.
What We Learned
1. Idempotency is non-negotiable
Retries are inevitable.
Without idempotency:
Retry + partial success = duplicate or inconsistent data
With idempotency:
Retry + partial success = recoverable operation
Design for retries from the beginning rather than adding idempotency after the first production incident.
2. Reconciliation catches what application monitoring misses
A pipeline can have:
- No infrastructure alarms
- No application exceptions
- Successful job completion
…and still have incorrect data.
Count-based reconciliation and other data-quality controls provide a second line of defense.
3. Batch-level controls are as important as record-level controls
Record-level validation answers:
"Is this record valid?"
Batch-level reconciliation answers:
"Did we process everything we expected to process?"
You need both.
4. Failure should be observable, not invisible
A rejected record isn't necessarily a disaster.
A rejected record that nobody knows about is.
Dead-letter queues, explicit states, metrics, and alerts turn hidden failures into manageable operational work.
5. Auditability should be designed, not added later
If you wait until an audit or production incident to answer:
"What happened to this data?"
you'll discover that the information you need may no longer exist.
Capture the evidence while the pipeline is running.
The Bigger Lesson
Scaling ETL isn't primarily about making the pipeline faster.
At 25M+ records, throughput is only one dimension of the problem.
The more important questions become:
Can we retry safely?
Can we detect partial failure?
Can we reconcile source and target?
Can we explain what happened?
Can we recover without reprocessing everything?
Can we prove the final state?
That changes how you design the system.
The pipeline isn't simply:
Extract → Transform → Load
It becomes:
Extract
↓
Validate
↓
Process
↓
Verify
↓
Reconcile
↓
Audit
And that's probably the biggest lesson we learned:
Modern ETL isn't just about moving data. It's about being able to prove that the data moved correctly.
When you're processing tens of millions of records across hundreds of data sources, trust becomes a system feature.
Top comments (0)