Data Pipeline Architecture Explained (With Patterns That Scale)

A robust and scalable data pipeline is no longer a niche requirement for tech giants; it is the central nervous system of any data-driven organisation. It feeds everything from executive dashboards and operational analytics to the machine learning models that power product features. Yet, we frequently encounter architectures that are brittle, expensive, and opaque—accidental creations that grew without a coherent design.

Getting the architecture right from the outset, or course-correcting an existing one, is a high-leverage engineering activity. The right design choices determine your ability to scale, the cost of your data operations, and the speed at which your teams can deliver insights. This article explains the fundamental components, compares the dominant architectural patterns we see in 2026, and details the non-functional requirements like testing and observability that separate production-grade pipelines from science projects.

Anatomy of a Data Pipeline

At a high level, all data pipelines perform the same four logical functions. Understanding these stages is the first step to designing a coherent system.

Source

This is where data is born. Sources are diverse and each comes with its own constraints. Common examples we see in our engagements include:

• Production Databases (OLTP): PostgreSQL, MySQL, or SQL Server databases that back your company's applications. The main challenge here is accessing the data without impacting the performance of the production application.
• Event Streams: Real-time data from user activity, IoT devices, or application logs, often managed through a message broker like Apache Kafka or AWS Kinesis.
• Third-Party APIs: Data from SaaS tools like Salesforce, Stripe, or Google Analytics. Access is governed by rate limits, authentication schemes, and API-specific data structures.
• File Storage: Unstructured or semi-structured data like logs, images, or CSVs residing in cloud storage like Amazon S3 or Google Cloud Storage.

Ingestion & Transport

This is the "Extract" and "Load" phase, responsible for moving data from its source to a centralised storage layer, typically a data lake or data warehouse. The goal is reliable, efficient transport. For many standard sources, managed services like Fivetran or Airbyte offer a compelling out-of-the-box solution. For bespoke or high-volume sources, custom connectors using tools like Kafka Connect or even simple Python scripts are common. A crucial decision here, which we will explore next, is whether you transform the data before or after loading it.

Transformation

This is the core value-creation stage. Raw data is rarely useful on its own. The transformation step cleans, normalises, joins, enriches, and aggregates data to create clean, reliable datasets fit for specific business purposes. This is where business logic is encoded into the pipeline. SQL is the lingua franca of data transformation, and tools like dbt (Data Build Tool) have become the de facto standard for managing transformation logic as version-controlled, testable code. For more complex transformations or very large data volumes, frameworks like Apache Spark are used.

Serving & Destination

The final stage is making the processed data available to consumers. This destination depends entirely on the use case:

• Data Warehouse: The transformed data is often modelled into clean, queryable tables (marts) within a warehouse like Snowflake, BigQuery, or Redshift for BI and analytics.
• BI Tools: Tools like Tableau, Looker, or Power BI connect directly to the warehouse to power dashboards and reports.
• Reverse ETL: Data is pushed back out of the warehouse into operational systems. For example, sending a "propensity to churn" score from the warehouse back to the Salesforce contact record.
• ML Feature Stores: Curated data is served to machine learning models for training and inference.

ETL vs ELT: The Modern Default in 2026

The sequence of the "transform" and "load" steps is one of the most fundamental architectural decisions. For decades, ETL (Extract, Transform, Load) was the standard. Today, ELT (Extract, Load, Transform) is the dominant paradigm for analytical pipelines.

In the classic ETL pattern, data is extracted from the source, transformed in a separate, specialised processing engine (like Informatica, Talend, or a custom Spark application), and then the final, polished result is loaded into the destination data warehouse.

In the modern ELT pattern, data is extracted from the source and loaded into the data warehouse in its raw, unaltered state. All transformations then happen inside the warehouse, leveraging its powerful, scalable compute engine.

The shift to ELT was driven by a fundamental change in infrastructure economics. In the ETL era, storage was expensive and compute was relatively fixed. It made sense to minimise the amount of data stored in the costly warehouse by transforming it first. Today, cloud storage is incredibly cheap, and cloud data warehouses offer on-demand, pay-as-you-go compute.

This economic inversion makes ELT the superior choice for most modern use cases:

• Decoupling: Ingestion is decoupled from transformation. Ingestion tools can be simple, standardised, and robust. Transformation logic can be developed and evolved independently.
• Flexibility: Because you retain the raw data, you can always re-run or change your transformation logic without having to perform a costly and complex re-ingestion from the source systems. Need to fix a bug in a business logic calculation from two years ago? With ELT, you can simply re-run your transformation model on the raw historical data already in your warehouse.
• Analytics as Code: Tools like dbt allow teams to manage their SQL transformations as code in a Git repository. This brings all the benefits of modern software engineering to the analytics workflow: version control, code review, automated testing, and CI/CD.

Table: ETL vs ELT Architectural Comparison

There are still cases where we would not recommend ELT. For example, if you must scrub sensitive PII data before it ever lands in cloud storage for compliance reasons, an in-flight ETL transformation step might be necessary. But for the vast majority of analytical workloads, ELT provides a more flexible, scalable, and maintainable foundation.

Processing Paradigms: Batch, Streaming, and CDC

Another key architectural choice is the processing cadence: how frequently do you move and transform data?

• Batch Processing: This is the traditional approach where data is processed in large, discrete chunks on a schedule (e.g., once every 24 hours). It's simple to implement, cost-effective, and perfectly adequate for many BI use cases like daily sales reports or weekly financial summaries. Orchestration tools like Airflow or Dagster are commonly used to manage these scheduled jobs.

• Micro-Batch Processing: A hybrid approach that bridges the gap between batch and true streaming. The pipeline runs in small batches at high frequency, perhaps every 5 or 15 minutes. This provides lower latency than overnight batch jobs without incurring the full complexity and cost of a real-time streaming system. It’s a pragmatic choice for use cases like "near real-time" operational dashboards.

• Streaming Processing: Data is processed event-by-event as it is generated, with latencies in the seconds or milliseconds. This is essential for real-time use cases like algorithmic fraud detection, dynamic pricing, or real-time monitoring. Streaming systems are significantly more complex to build and operate, requiring specialised tools like Apache Flink, ksqlDB, or Materialize. Don't choose streaming unless the business case for sub-minute latency is explicit and valuable.

A powerful technique that often feeds streaming and micro-batch pipelines is Change Data Capture (CDC). Instead of repeatedly querying a production database (which can be inefficient and add load), CDC tools like Debezium tap into the database's internal transaction log. This allows the pipeline to capture every single row-level change (insert, update, delete) in real-time and stream those events into a system like Kafka. This is the most efficient and lowest-latency method for replicating an operational database into an analytical environment, forming the backbone of many modern data engineering platforms.

Table: Data Processing Paradigm Trade-offs

Six Reference Architectures and Their Trade-offs

In our work, we see several common patterns emerge. Choosing the right one depends on your team's skills, budget, latency requirements, and existing technology stack.

Pattern 1: The Orchestrator-Driven ELT Pipeline

The workhorse of modern data analytics. An orchestrator like Airflow or Dagster executes a Directed Acyclic Graph (DAG) of tasks. This typically involves running an ingestion script/tool, followed by a dbt run command to execute the SQL transformations in the warehouse.

• Use when: You need flexibility and control, have a team comfortable with Python and software engineering principles.
• Pros: Highly customisable, open-source-centric, powerful dependency management.
• Cons: Higher maintenance overhead, requires infrastructure to run the orchestrator.

Pattern 2: The Managed ELT Stack

This pattern outsources the "E" and "L" to a managed service like Fivetran or Airbyte Cloud, which handles ingestion from hundreds of common sources. The "T" is then managed by a tool like dbt Cloud, which schedules and runs transformations directly in the warehouse.

• Use when: Your sources are well-supported by the managed tool, and your priority is speed of delivery over customisability.
• Pros: Extremely fast time-to-value, low engineering overhead for ingestion.
• Cons: Can become very expensive as data volume grows, creates vendor lock-in, limited customisation for bespoke sources.

      ┌───────────────┐      ┌────────────────┐      ┌──────────────────┐
      │  SaaS API     │      │                │      │                  │
      │ (Salesforce)  ├─────►│   Fivetran /   ├─────►│    Snowflake /   │
      └───────────────┘      │    Airbyte     │      │     BigQuery     │
                             │   (Extract &   │      │ (Raw Data Layer) │
      ┌───────────────┐      │     Load)      │      └────────┬─────────┘
      │  PostgreSQL   │      │                │               │
      │   (App DB)    ├─────►│                │               │
      └───────────────┘      └────────────────┘               │ (Transform)
                                                              ▼
                                                        ┌───────────┐
                                                        │ dbt Cloud │
                                                        └─────┬─────┘
                                                              │
                                                              ▼
      ┌──────────────────┐      ┌───────────────┐       ┌──────────────────┐
      │  Looker /        │◄─────┤   BI /        │◄──────┤    Snowflake /   │
      │  Tableau         │      │ Analytics Marts│       │     BigQuery     │
      └──────────────────┘      └───────────────┘       │ (Transformed)    │
                                                        └──────────────────┘

Diagram: A typical managed ELT stack architecture.

Pattern 3: The Lakehouse Architecture

This pattern, popularised by platforms like Databricks and Snowflake's Unistore, seeks to unify data lakes and data warehouses. Data is stored in open formats (like Apache Parquet or Delta Lake) on cheap object storage, but the platform provides transactional guarantees and SQL query performance similar to a traditional warehouse.

• Use when: You have a mix of BI, data science, and AI workloads running on the same large datasets. This architecture is especially relevant when considering how to prepare data for Generative AI.
• Pros: Unifies data for diverse workloads, avoids data duplication, leverages open-source formats.
• Cons: Can be complex, platform ecosystem is still evolving.

Pattern 4: The Real-Time Streaming Pipeline

For sub-second latency needs. A message broker like Kafka is the backbone. Events are ingested into Kafka topics, processed in real-time by a stream processing engine like Apache Flink, and the results are landed in a low-latency database (like Apache Druid or ClickHouse) or pushed back into another Kafka topic.

• Use when: The business value of real-time insight is proven and high (e.g., fraud detection).
• Pros: Extremely low latency.
• Cons: Very high complexity and operational cost. A common mistake is to adopt this pattern for problems that could be solved with simpler micro-batching.

Pattern 5: The CDC-Powered Replication Pipeline

Uses a CDC tool like Debezium to stream changes from an operational database into Kafka. From there, a connector like Kafka Connect can sink this data into a data warehouse in near real-time.

• Use when: You need a low-latency, low-impact replica of a production database in your analytical environment.
• Pros: Highly efficient, minimal load on the source database, provides a full audit trail of changes.
• Cons: Can be complex to set up and manage the full stack (Debezium, Kafka, Kafka Connect).

Pattern 6: The Reverse ETL Pipeline

This isn't a pipeline into the warehouse, but out of it. It takes the curated, enriched data from the warehouse (e.g., customer health scores, lead scores) and pushes it back into operational tools like Salesforce or Marketo. Tools like Hightouch and Census specialise in this.

• Use when: You want to "activate" your data by putting insights directly into the hands of business teams within their daily tools.
• Pros: Closes the loop between analytics and operations, makes data actionable.
• Cons: Requires clean, reliable data in the warehouse to be effective.

Idempotency, Replay, and Backfill by Design

Production systems fail. Network connections drop, APIs return errors, and bugs are deployed. A robust data pipeline architecture must be designed for failure.

Idempotency is the property that allows an operation to be applied multiple times without changing the result beyond the initial application. In a data pipeline, this means if a task fails and is re-run, it won't create duplicate data or corrupt the state. This is critical. We achieve this by writing transformations to be idempotent. Instead of using INSERT, which creates duplicates on re-run, we use MERGE (or INSERT OVERWRITE in some systems) statements that atomically update existing records or insert new ones based on a unique key.

Replay and Backfill is the ability to re-process historical data to fix an error or enrich it with new logic. This is a non-negotiable for mature data platforms. ELT architectures make this significantly easier because the raw historical data is already present in the warehouse. To enable effective backfills, pipelines should be designed with this in mind:

• Partition data: Partition tables by the date the data was processed and, if available, the date the event occurred. This allows you to re-run a transformation for a specific historical time window without reprocessing the entire dataset.
• Parameterise jobs: Orchestration jobs should be parameterised with a date or date range, so you can trigger a run for any period in the past.

Worked Example: The Economics of a Backfill

A European insurer we worked with discovered a flaw in their risk scoring model for home insurance policies. They needed to recalculate the scores for all policies issued over the past three years.

• Data Volume: 15 TB of policy and claims data stored in Snowflake.
• Action: A dbt model containing the corrected logic needed to be re-run over the entire 3-year history.
• Execution: They provisioned a temporary 2X-LARGE Snowflake virtual warehouse (64 credits/hour) specifically for the backfill job to ensure it completed quickly without impacting other workloads. The job took 5 hours to run.
• Cost Calculation (assuming a 2026 credit price of €3.50):
  • 5 hours * 64 credits/hour * €3.50/credit = €1,120

This one-off compute cost of just over €1,000 was trivial compared to the alternative in their previous ETL system. A similar backfill there would have required weeks of engineering effort to orchestrate the re-ingestion of data from multiple legacy source systems, a project estimated at over €40,000 in engineering time. The ELT pattern, designed for replayability, turned a potential crisis into a routine operational task.

Testing and the Rise of Data Contracts

As data becomes a product, it must be subject to the same rigour as software. This means comprehensive testing and clear agreements between producers and consumers.

Testing in a data pipeline happens at multiple levels:

• Unit Tests: Test the business logic within a single transformation. dbt test provides a simple framework for this, allowing you to assert conditions like not_null, unique, or custom SQL queries on your model's output.
• Integration Tests: Test the full pipeline end-to-end, but with a small, controlled sample of data. This ensures all the components (ingestion, transformation, loading) work together correctly.
• Data Quality Monitors: These are tests that run on your production data to catch issues. Tools like Great Expectations or dbt's source freshness and dbt-expectations package allow you to define checks for things like: Is the data recent? Is the row count within an expected range? Are values in a column plausible?

A Data Contract is an API-like agreement for your data. It is a formal document, often stored in a schema registry and enforced by tooling, that defines the guarantees a dataset provides. This includes schema (field names, data types), data quality metrics (freshness, completeness), and semantics (what order_total actually means). By having producers validate their data against a contract before it is published, downstream breakages caused by unexpected upstream changes are dramatically reduced. Implementing data contracts is a sign of a mature organisation moving towards a truly robust modern data platform.

Observability, SLAs, and Data On-Call

If a critical dashboard is broken or a machine learning model is being fed garbage data, you need to know immediately—not when an executive complains. This requires investment in data observability. The key pillars are:

• Freshness: When was this data last updated? Is it stale?
• Volume: Did the expected number of rows arrive? A sudden drop often indicates an upstream problem.
• Distribution: Are the statistical properties of the data consistent? If the average value in a numeric column suddenly spikes, it could indicate a bug or data quality issue.
• Schema: Have any fields been added, removed, or changed type?
• Lineage: Where did this data come from, and what downstream assets depend on it? This is crucial for impact analysis.

Dedicated data observability tools like Monte Carlo or Databand can provide this out of the box, but you can also build a significant portion of it using open-source tools like OpenMetadata and the logging/testing features of your orchestrator and dbt.

This visibility allows you to define and monitor Service Level Agreements (SLAs) for your data products (e.g., "The daily_sales table will be updated by 08:00 CET with 99.5% reliability"). These SLAs drive engineering priorities and manage stakeholder expectations. And if an SLA is breached for a critical dataset, someone should be paged. Establishing a data on-call rotation is a major cultural step, but it's the ultimate mechanism for ensuring data pipelines are treated as the critical production infrastructure they are.

Worked Example: The Cost of Data Downtime

A Series A logistics platform in our portfolio uses a real-time pipeline to feed a dynamic pricing model for its delivery fleet. A bug in a deployment caused the pipeline to stop processing location updates.

• Incident: The data stream feeding the pricing model was stale for 90 minutes during a morning peak.
• Business Impact: Without real-time location data, the model reverted to less accurate, static pricing. The company estimated it lost approximately €15,000 in revenue from underpriced jobs and inefficient driver allocation during this window.
• Justification for Investment: The post-mortem concluded that basic freshness and volume monitoring would have caught the issue within minutes. The one-time cost of the incident easily justified the annual subscription for a data observability platform (approx. €30,000/year) plus the associated engineering time to implement it.

Frequently asked questions

What is the difference between ETL and ELT?

ETL stands for Extract, Transform, Load, while ELT stands for Extract, Load, Transform. The key difference is the sequence. In ETL, data is transformed by a separate processing engine before it is loaded into a data warehouse. In ELT, raw data is loaded directly into the warehouse first, and all transformations are then performed inside the warehouse using its own compute engine. ELT is the default for modern data architectures because cloud storage is inexpensive and performing transformations within the warehouse (often with tools like dbt) allows for more flexibility, versioning, and testability of the business logic.

Key takeaways

• ELT is the 2026 default. For most analytical use cases, loading raw data into a cloud data warehouse and transforming it in-place offers the most flexibility, scalability, and maintainability.
• Choose the right processing cadence for the job. Don't build a complex, expensive real-time streaming pipeline when a simple, reliable nightly batch job will suffice. Match the latency to the business need.
• Design for failure. Production pipelines will fail. Building for idempotency (re-runnability) and designing for backfills are not optional extras; they are fundamental to creating a resilient system.
• Treat data as a product. This means subjecting your pipelines and datasets to the same rigour as application code: automated testing, CI/CD, quality monitoring, and formal data contracts.
• Observability is non-negotiable. You cannot manage, troubleshoot, or guarantee the reliability of a pipeline you cannot see. Invest in monitoring freshness, volume, and quality.
• Architecture dictates cost and capability. The patterns you choose have direct and significant consequences on your cloud bill, your team's velocity, and what is ultimately possible with your data.

Choosing the right data pipeline architecture is a critical decision that impacts your entire organisation. It requires a thoughtful analysis of your use cases, technical capabilities, and business goals. If you are planning a new data platform or reassessing an existing one, a thorough architecture review can prevent costly missteps and ensure you are building on a foundation that will scale with your ambitions.