"Your Dashboard Isn't the Truth: Six Data Pipeline Architectures That Make Data Trustworthy" - Ahsan-Research/Dev GitHub Wiki
"Talk on why pipelines exist, how they work, and what breaks in production"
Your Dashboard Isn't the Truth
Six Data Pipeline Architectures That Make Data Trustworthy
** This article is written for data engineers, database administrators, architects, analytics leaders, and everyone who has ever received a terrifying Monday-morning dashboard question.
Good morning, everyone!
It is wonderful to see so many engineers, architects, DBAs, analysts, and people who have interest and chosen his carrier on Data Engineering and working on moving data from one place to another. That is either dedication to the profession—or proof that we all need better hobbies.
[Pause for a laugh.]
Let me take you to a familiar scene.
It's Monday, 8:45 a.m. You've just arrived at the office. Your laptop is open. Your coffee is still too hot to drink. For roughly eleven beautiful seconds, life is peaceful.
Then the CFO appears.
She holds up two screens. One says the business made $4.2 million over the weekend. The other says $3.8 million.
She asks, very calmly—which is somehow worse than shouting—“Which number is correct?”
Before you can answer, the AI team wants five years of clickstream history, the operations team wants fraud detection in milliseconds, and an alert tells you that somebody renamed a database column at 4:55 p.m. on Friday. Nineteen downstream jobs have been protesting ever since.
You look at your coffee.
Your coffee looks back at you.
Neither of you is prepared for this meeting.
Here's the uncomfortable truth: both dashboards may be technically correct and still give the business the wrong answer. Perhaps one uses transaction authorization time and the other uses settlement time. Perhaps a refund landed late. Perhaps the batch job hasn't caught up. Perhaps the streams count duplicates. Perhaps someone changed the definition of “revenue” without changing the dashboard label.
This is why data engineering matters.
A pipeline is not merely a pipe from Database A to Dashboard B. It is a set of promises: what information arrives, how quickly it arrives, what each field means, whether missing events can be recovered, and who is accountable when the result is wrong.
So today I want to unpack the six architectural building blocks on the screen:
- Batch ingestion — moving data reliably in scheduled groups.
- Stream processing — reacting to events as they happen.
- Change data capture (CDC) — following changes in operational databases.
- Data transformation (ETL/ELT) — turning raw records into trustworthy meaning.
- Orchestration and workflow — coordinating dependencies, recovery, and execution.
- Monitoring and governance — proving the entire system can be trusted.
One important clarification before we begin: these are not six competing products and not even six mutually exclusive pipeline types. Batch, streaming, and CDC are common ways to move or acquire changes. Transformation turns those changes into usable models. Orchestration coordinates work. Monitoring and governance cut across all of them. A real enterprise platform often uses all six together.
For each one, I'll answer three questions: Why do we need it? How does it really work? And what goes wrong at 2:00 a.m.? Then I'll give you one practical engineering rule worth taking home.
01 — Batch Ingestion: The Dependable Workhorse
Why use batch when the world is obsessed with “real time”?
Because fast and valuable are not the same thing.
Imagine a global retailer processing the previous day's sales from thousands of stores. Finance needs a dependable morning ledger. Operations needs a complete inventory snapshot. Legal needs a reproducible regulatory extract. None of those teams is necessarily improved by recalculating every three milliseconds.
In many systems, the right answer is complete, consistent, auditable, and ready by 7:00 a.m. Not “available instantly, but maybe missing the last 17%.”
Batch ingestion is attractive because it can process large volumes efficiently, schedule expensive compute deliberately, isolate workloads from operational systems, and make historical reprocessing straightforward if we design for it.
And please don't let anyone tell you batch is “legacy.” I have seen modern-looking architectures with six Kubernetes clusters and beautiful dashboards struggle with tasks a humble, well-engineered nightly batch could have performed quietly and reliably.
How it works
The basic pattern is simple:
Source systems → Extract or export → Landing/staging area → Validation → Raw data lake or warehouse.
Suppose we ingest yesterday's orders. A scheduler starts an export for a defined business interval. The pipeline writes files to a landing location, records the extraction window and source watermark, validates row counts and file integrity, then publishes the completed dataset for downstream processing.
A mature version adds a few pieces you won't necessarily see in a slide diagram:
- A watermark or high-water mark tells us how far the source has been ingested.
- A manifest tells us which files belong to the batch and how many records to expect.
- An idempotent load means re-running the same batch does not duplicate business records.
- A publish-after-validation step prevents half-loaded data from appearing “complete.”
- A backfill procedure lets us rerun a missed interval without corrupting later results.
Those additions are not glamorous. They are the difference between a dependable pipeline and a recurring Monday support ticket.
The difficulty: when one missed window becomes everybody's problem
Picture an ordinary nightly ingest. At 2:00 a.m., a partner's file endpoint goes down halfway through delivery. Our job retries three times, fails, and sends an alert. The ingestion window is marked unsuccessful, but downstream jobs begin anyway because somebody built the dependency on a clock rather than on successful data publication.
By breakfast, the warehouse contains yesterday's data for some regions and the day before yesterday's data for others. Every query works. Every dashboard loads. The numbers are silently incomplete.
That's the most dangerous kind of failure: the one that doesn't crash.
The real challenges are late source files, partial extracts, changing source schemas, growing data volumes, expensive backfills, and time-boundary mistakes. Midnight in New York is not midnight in Singapore; daylight-saving transitions make an innocent-looking date filter surprisingly exciting.
The field rule: design the rerun before the first run
When I review a batch design, I ask:
“If the 2:00 a.m. job fails after writing 80% of its output, what exact command or action restores a correct result?”
If the answer is “we'll manually delete some records and hope,” the pipeline is not production-ready.
Define immutable ingestion windows, use explicit UTC timestamps where appropriate, keep load manifests, validate completeness, and make the publish step atomic from the reader's perspective. Choose a freshness target that makes sense for the business—not a fashionable number borrowed from a conference slide.
Batch is not slow engineering. Badly designed batch is slow recovery.
02 — Stream Processing: Where the Clock Starts Winning
Why stream?
Let me ask the room a question: if a payment processor detects a suspicious transaction tomorrow morning, did it detect fraud—or did it write a very detailed apology?
Some decisions are valuable only when we make them quickly. Fraud signals, machine sensor alarms, live vehicle telemetry, stock availability, and interactive personalization are examples. The business wants to respond to events as they arrive, not wait for an overnight window.
Streaming is about reducing the time between something happening and the business being able to act on it.
But here's a nuance: streaming doesn't magically guarantee zero latency, exactly-once business outcomes, or instant analytical correctness. It introduces a different set of engineering trade-offs.
How it works
A common architecture looks like this:
Event producers → Durable event broker (such as Kafka) → Stream processor (such as Flink or Spark Structured Streaming) → Materialized views, serving stores, alerts, or downstream storage.
A customer places an order. An event is published. A processor checks the current pattern of activity, enriches the order with customer risk attributes, maintains a rolling window of recent transactions, and writes a result for a decision service or dashboard.
Now we have to think about concepts batch engineers can sometimes ignore:
- Event time is when something happened in the source; processing time is when the system handled it.
- Watermarks help decide how long to wait for delayed events before considering an event-time window sufficiently complete.
- State stores context, such as the last five minutes of transactions per card.
- Checkpoints and replay allow recovery after failures, subject to the framework and configuration.
- Delivery and sink semantics determine how duplicate deliveries, retries, and transactional writes affect business results.
A message broker is not the same as a stream processor, and a queue does not replace an event-time strategy.
The difficulty: the event arrives after the answer
Imagine a logistics company tracking deliveries. A truck enters a tunnel at 10:02, loses connectivity, and reconnects at 10:09. Its device uploads seven minutes of buffered events all at once.
If your system groups events by arrival time rather than event time, it may declare the truck stationary at the wrong location, report the wrong route, or trigger a false SLA violation.
Now add duplicates caused by retries. Add events that arrive out of order. Add a failed processing job that replays the last checkpoint. Suddenly “count all shipments” becomes an argument about which shipment event was counted, when it was counted, and whether a retry counted it again.
“Exactly once” is not a magic property you can purchase with a logo. Framework-level exactly-once state recovery doesn't automatically make an external database, notification API, or business workflow exactly once. End-to-end correctness depends on sources, checkpoints, transactions, sinks, and application design.
The field rule: assume events will arrive late, twice, and out of order
Before I call a stream production-ready, I want answers to these questions:
What identifies an event uniquely? What happens after a replay? When is an aggregate considered final? How do we repair a late correction? What happens when the downstream store is unavailable?
Use stable event IDs, explicit time semantics, documented late-arrival policies, idempotent consumers or transactional sinks when available, and observable consumer lag. Test by disconnecting a producer, delaying events, restarting processors, and replaying the same messages. A system that works only in the perfect network of a demo is not finished.
Streaming is exhilarating. So is standing on the roof during a thunderstorm. The excitement is not always a benefit.
03 — Change Data Capture: Let the Database Tell You What Changed
Why CDC?
Here is a scene every SQL Server DBA recognizes. The analytics team says, “We only need the orders that changed.” Then someone proposes running a full scan of a massive Orders table every five minutes.
On the production primary.
During business hours.
I can already feel the DBA in the front row reaching for the emergency shutdown button.
CDC exists because we often need incremental changes without repeatedly reading the entire operational dataset. We want to capture inserts, updates, and deletes and forward them to consumers with manageable latency and less unnecessary scanning.
But CDC does not make source overhead disappear, and it is not a complete business event stream by itself. A row update says what changed in storage; it does not always tell us why the business changed it.
How it works
In a log-based design, the database records transactions in its log. A capture process obtains eligible changes and exposes them through change tables or a connector, then downstream systems consume and apply those changes.
Operational DB → Database change capture → Connector or message bus → Lake, warehouse, search index, or service.
Examples include native SQL Server CDC and connectors such as Debezium. The exact capture mechanism differs by database and connector; for SQL Server, Debezium commonly consumes native CDC change tables, so don't assume every connector independently tails the raw transaction log.
We must track position—such as an LSN or offset—so a consumer can resume correctly. We must interpret deletes, transaction ordering, initial snapshots, schema versions, and replay behavior.
The difficulty: the Friday afternoon schema change
Suppose an application team changes CustomerStatus from an integer code into a text enum. The application passes its tests. The deployment succeeds.
The warehouse ingestion process, however, expects an integer. One downstream transformation fails. A retry fails again. The connector continues to produce data, the consumer falls behind, and the ingestion team learns on Monday that a tiny field change broke a chain of reporting models.
Or consider a different failure. A consumer has been offline longer than the retained change-data window. When it comes back, its last processed position is older than the earliest change available. There may be no safe way to “just continue.” You need a controlled resnapshot or another recovery strategy.
One technical distinction matters especially for SQL Server: native CDC has capture and cleanup processes with retention windows. CDC change-table growth and retention loss are important risks. Under some conditions, capture/log-scanning problems can also affect transaction-log truncation and space. A slow external consumer does not, by itself, mean the source transaction log necessarily keeps growing forever. Monitor each layer rather than combining them into one vague “log bloat” problem.
The field rule: treat schema and retention as contracts
My CDC design review begins with five questions:
How will we initialize? How do we identify a unique record? How do we handle deletes? What changes can the schema make without breaking consumers? What is the recovery plan if the consumer falls outside the retention window?
Define compatible schema evolution rules and alert on capture lag, consumer lag, connector failures, and available retention headroom. Store offset/checkpoint metadata durably. Give downstream owners advance warning when upstream tables change.
CDC is powerful because it makes the operational system's changes visible. It is dangerous when teams mistake visible changes for business-ready truth.
04 — Data Transformation (ETL/ELT): Where Numbers Become Meaning
Why transform?
Raw data is honest in a very unhelpful way.
One system calls a customer customer_id. Another calls that person account_no. A third stores an email address and calls it a customer key—which is a lovely way to turn a typo into a duplicate human being.
One source stores money in dollars, another in cents. One timestamp is UTC, another is local time without an offset. One refund appears as a negative sale; another is a separate transaction type.
Without transformation, you're not building an analytics platform. You're building a very expensive argument generator.
This is the stage that turns raw facts into consistent business definitions.
How it works
Two common patterns are worth separating:
- ETL — Extract, Transform, Load: Transform data before loading it into the target analytical store. This can be appropriate for certain integrations, privacy requirements, or target constraints.
- ELT — Extract, Load, Transform: Land raw or lightly processed data first, then transform it within a scalable warehouse or lakehouse. This is common with tools such as dbt and cloud analytical platforms.
Neither is universally superior. The right choice depends on governance, economics, workload, and where sensitive data may legally be processed.
A practical ELT flow might look like:
Raw orders → Cleaned orders → Deduplicated transactions → Standardized currencies → Conformed dimensions → Trusted revenue model → Finance dashboard.
I want readers to hear the word trusted very carefully. Trust doesn't come from naming a table final_revenue_v7_REALLY_FINAL. Trust comes from explicit business rules, tests, lineage, and sign-off from the people accountable for the metric.
The difficulty: silent semantic drift
Remember our CFO's $4.2 million versus $3.8 million?
Let's imagine the pipeline is technically healthy. No failed jobs. No duplicate records. No broken credentials. All the dashboards are green.
But the operations dashboard reports gross authorized orders, while the accounting report reports settled revenue net of refunds, perhaps using a different cutoff time. The engineering team says, “The pipelines are fine.” Finance says, “The business is not fine.”
Both sides have a point. The data is moving correctly; the meaning is not aligned.
This is semantic drift. It often appears when finance revises a definition, product adds a new order status, a regional team changes its reporting rules, or transformation logic gets copied into several models that evolve independently.
The most alarming part? Every query can return rows, every test can pass, and the executive meeting can still go terribly wrong if the tests validate the wrong definition.
The field rule: version the business definition, not just the SQL
For important metrics, capture a business contract:
- What is the metric's definition?
- What source fields and statuses are included or excluded?
- What event or accounting time determines the reporting period?
- How are refunds, corrections, currencies, nulls, and late arrivals handled?
- Who approves changes to the definition?
- What reference examples must continue to produce the same answer?
Use data tests for uniqueness, non-null keys, accepted values, referential integrity, and reconciliation totals. Version transformation code. Run impact checks on downstream dependencies. And publish the semantic definition alongside the dashboard, not in a private chat thread somebody will never find again.
The hardest part of transformation isn't writing a JOIN. It's getting six departments to agree what the result should mean.
05 — Orchestration and Workflow: The Air-Traffic Controller
Why orchestration?
Suppose we have a daily revenue pipeline.
The source export must complete before staging is validated. Validation must complete before transformation. Transformation must complete before the finance report is published. The report must not be published if a critical reconciliation fails.
That's not a sequence of independent jobs. That's a dependency graph.
Orchestration coordinates those dependencies so the system behaves coherently through successes, failures, retries, schedules, and recovery.
Think of it as air-traffic control. The controller doesn't build the airplane, but without coordination, the runway becomes a very expensive parking lot.
How it works
Workflow tools such as Apache Airflow define tasks and dependencies in a DAG — a directed acyclic graph. Depending on the platform, workflows may run on schedules, through events, or by explicit triggers.
A simplified daily workflow might be:
Wait for complete source data → Validate arrival → Load staging → Run transformations → Reconcile totals → Publish report → Notify consumers.
Notice the change from a naïve pipeline: we publish after validation and reconciliation. Not “at 6:00 a.m. whether the numbers are right or not.”
Production-grade orchestration also needs resource limits, retry policy, timeouts, scheduling semantics, clear data intervals, and auditability.
The difficulty: a retry turns one problem into three
Here's an incident pattern I love to ask about in design reviews.
A task writes 100,000 rows into a target table. It succeeds. Before the orchestrator receives the acknowledgment, the network connection drops. The orchestrator sees a failure and retries.
Now the target has 200,000 rows.
The scheduler did exactly what we asked: it retried a failed task. The data pipeline did exactly what we didn't want: it performed a successful side effect twice.
This is why retries without idempotency are not resilience; they're a duplication strategy.
Other headaches include backfills competing with daily workloads, cascading failures, insufficient worker capacity, hidden cross-DAG dependencies, and a job whose code assumes “yesterday” rather than receiving an explicit data interval. When you backfill Tuesday on Friday, that subtle assumption suddenly matters.
The field rule: every task needs a contract
For each important task, define:
Inputs. Outputs. Expected interval. Retry safety. Timeout. Failure severity. Owner. Reprocessing method.
A task should either be safe to rerun or expose a clear compensating action. Separate computation from publication when possible. Use concurrency controls to prevent backfills from overwhelming the cluster or warehouse. And test failures intentionally: kill a worker halfway through a write and observe what happens next.
An elegant DAG tells you what should happen. A resilient DAG tells you what happens when it doesn't.
06 — Monitoring and Governance: Because a Green Dashboard Can Still Be Wrong
Why monitor and govern?
I've spent the first five sections telling you how data moves, changes, and becomes useful. But none of that is enough unless we can answer a more important question:
Can we prove the data is complete, current, accurate enough for its purpose, appropriately protected, and owned by somebody who will respond when it isn't?
A pipeline that runs for 30 seconds and returns the wrong numbers is not healthy because it runs fast. A job that finishes successfully while quietly dropping 12% of records is not successful. And a perfectly accurate table exposed to the wrong identities is not a trustworthy platform.
Monitoring tells us what is happening. Governance defines what is allowed and who is accountable. They belong together, but they are not synonyms.
How it works
I divide the signals into four groups:
- Operational health: failures, duration, throughput, latency, retries, backlog, resource usage, and cost.
- Data health: freshness, completeness, duplicate rates, null rates, schema drift, distribution changes, and reconciliation results.
- Trust and traceability: lineage, source-to-target mapping, metric definitions, test evidence, and ownership.
- Security and compliance: access controls, sensitive-data classification, masking, retention, audit trails, and incident handling.
A good observability system connects these signals. If a finance table has a freshness problem, I don't want twenty unrelated red icons. I want to know which source was late, which task is blocked, who owns it, and which executive-facing metric is affected.
That last part—business impact—is what turns monitoring into an operational decision tool.
The difficulty: the alert that taught everyone to ignore alerts
Imagine an on-call engineer getting fifty warnings a night because a pipeline ran two minutes slower than its average. Most alerts self-resolve. None include the affected dataset or the data freshness objective.
After three weeks, the on-call channel becomes background noise.
Then a critical revenue model stops refreshing. Its alert looks exactly like the other forty-nine. Nobody notices for hours.
This isn't a failure of alerting volume. It's a failure of signal design.
There are also governance challenges that don't fit neatly into an uptime chart: who can access customer data, whether sensitive attributes have propagated into an unapproved sandbox, whether data can be deleted when required, and whether a correction can be traced to its source.
The field rule: alert on broken promises, not every unusual number
Establish explicit data service-level objectives, such as:
- Finance-ready revenue data must be published by the agreed business cutoff.
- A critical fraud feed should remain within its specified end-to-end freshness budget.
- Reconciled transaction counts must agree within documented tolerances.
- Sensitive columns must remain restricted to approved roles.
Then route actionable alerts with severity, owner, business impact, and a runbook link. Use anomaly detection carefully; an anomaly is a reason to investigate, not automatically proof that the data is wrong.
Above all, test whether the people receiving the alerts can do something useful with them.
Observability isn't how many dashboards you've purchased. It's how quickly your team can move from “Something is wrong” to “Here is what failed, what it affected, and how we recover safely.”
The Part Most Architecture Diagrams Leave Out
Now look at these six panels together.
Batch feeds the historical lake. Streams drive rapid decisions. CDC carries operational changes. Transformations define trusted business metrics. Orchestration coordinates dependencies. Monitoring and governance tell us whether we have kept our promises.
In a real company, all six are connected.
Here's a practical example:
An e-commerce order gets written to an operational database. A CDC process makes the change available to downstream systems. An order event also moves through a stream, where the fraud service produces a quick risk signal. Overnight, batch ingestion collects partner settlements. The warehouse transformation joins these sources under finance-approved business definitions. The orchestrator waits for required inputs and reconciliation checks. Monitoring warns the team if the settlement file is late or if totals unexpectedly diverge.
One order. Multiple paths. Different latencies. Different responsibilities.
The mistake is expecting all these paths to report the same number at the same moment without an agreed definition of sameness.
So let's return to our CFO's question.
Why $4.2 million and $3.8 million might both be correct
Instead of instantly declaring a broken dashboard, I would walk through five checks:
First: compare definitions. Does one screen show authorized orders while the other shows settled revenue? Gross or net? Inclusive or exclusive of refunds and taxes?
Second: compare time boundaries. Are both reports using the same timezone and reporting cutoff? A late settlement can belong to a different accounting period.
Third: compare freshness. How far have batch loads, streaming consumers, and CDC connectors progressed? Are the visible timestamps source event times or ingestion times?
Fourth: compare record integrity. Are we missing records, replaying duplicates, or applying corrections differently? Check stable IDs, offsets, reconciliation counts, and source totals.
Fifth: trace the transformation. Which business rule turned operational transactions into the metric labeled “revenue”? Who approved it and when did it change?
Only after those checks would I call the number wrong—or right. The goal isn't to defend a dashboard. It's to establish a reliable answer the business can act on.
The Engineer's Decision Guide: Which Architecture Should You Choose?
| Business need | Core pattern | What you gain | What you must engineer carefully |
|---|---|---|---|
| Daily finance reports or large historical loads | Batch ingestion | Efficient, reproducible scheduled movement | Windows, completeness, idempotent reruns, backfills |
| Immediate fraud signals or live telemetry | Stream processing | Low-latency event-driven decisions | Event time, late data, state, duplicates, recovery |
| Incremental replication of operational changes | CDC | Change-level movement without repeated full scans | Snapshots, offsets/LSNs, deletes, schema evolution, retention |
| Consistent analytical metrics and dimensional models | ETL/ELT transformation | Clean, governed, reusable data definitions | Semantic contracts, tests, lineage, cost, dependency impact |
| Complex multi-step pipelines | Orchestration | Coordinated scheduling, dependencies, recovery | Retry safety, data intervals, resource limits, backfill design |
| Dependable and compliant production operations | Monitoring and governance | Trust, accountability, controlled risk | Actionable alerts, ownership, SLOs, security, auditability |
Notice that the last three usually apply regardless of how data arrives. The right design is not “batch versus streaming.” The right design is the smallest reliable combination that meets the business promise.
And don't forget the economics. Faster processing has an infrastructure, operational, and human cost. If stakeholders only need a correct daily answer, delivering a constantly changing approximation every 300 milliseconds may be an expensive way to create confusion.
Five Questions I Want Every Data Engineer to Ask Before Going Live
If you remember nothing else from today, write these five questions on the first page of every pipeline design document.
1. What promise are we making to the business?
Fresh by when? Complete to what threshold? Correct according to whose definition?
2. What happens when data arrives late, twice, or not at all?
This single question exposes more architectural gaps than almost any vendor feature checklist.
3. How do we recover without making the damage worse?
Backfills, replay, snapshots, idempotent writes, and rollback are design features—not emergency wishes.
4. How do we know the data is trustworthy?
Do we have source reconciliation, data tests, ownership, lineage, and an auditable business definition?
5. Who gets called, and what can they actually do?
A pipeline without an owner and a runbook isn't automated. It's merely unsupervised.
Closing: Build Pipelines That Deserve to Be Trusted
Let me finish where we started—with that Monday morning coffee.
The CFO asks why two reports disagree. The AI team wants historical data. An alert is flashing. Someone has changed a source schema without telling the downstream owners.
A fragile data platform turns that moment into panic, blame, and a chain of emergency SQL scripts.
A resilient one gives us evidence.
We can say: “The operations screen shows authorized orders through 8:40 a.m. The finance report shows settled, net revenue through midnight. The partner settlement file arrived late, but its backfill is running safely. Here is the reconciled definition, the missing interval, and the expected publication time.”
See the difference?
We haven't eliminated every failure. That is not a realistic engineering goal. We've eliminated the mystery, reduced the recovery risk, and given the business a clear explanation it can trust.
That's the real purpose of a data pipeline.
Not to move bytes impressively. Not to assemble the longest list of tools on an architecture slide. And certainly not to make our GitHub profile look like the periodic table of cloud services.
The purpose is to move meaning safely from the moment something happens to the moment someone makes a decision.
Build for reality. Document the promises. Test the failures. Respect the people downstream.
And with a little luck, your Monday morning coffee will still be warm when you finally get to drink it.
Thank you, everyone.