INITIALIZING SYSTEMS

0%
Zalo: 0366 553 592[email protected]
ENVIKO
DATA ENGINEERING

ETL & Data Pipeline Design
Enterprise Data Engineering Guide

A comprehensive technical guide to designing, building, and operating production-grade data pipelines. Covering ETL vs ELT paradigms, pipeline architecture patterns, orchestration tools, transformation frameworks, ingestion strategies, storage targets, monitoring, error handling, cost optimization, and the modern data stack landscape for enterprise data engineering teams across APAC.

DATA ENGINEERING February 2026 35 min read Technical Depth: Advanced
Table of Contents
$42B
Global Data Integration Market (2026)
73%
Enterprises Using ELT Over ETL (Cloud)
4.2x
Growth in Data Pipeline Complexity (5yr)
68%
Data Engineers Citing Airflow as Primary Orchestrator

1. ETL vs ELT: Paradigm Shift to Cloud

1.1 Traditional ETL

Extract-Transform-Load (ETL) was the dominant data integration pattern for over three decades, rooted in an era when compute and storage were tightly coupled and expensive. In traditional ETL, data is extracted from source systems, transformed on a dedicated middleware server (Informatica PowerCenter, IBM DataStage, Talend, SSIS), and then loaded into a data warehouse in its final, cleaned, modeled form.

The transformation layer in ETL typically handled schema mapping, data type casting, business rule application, deduplication, surrogate key generation, and slowly changing dimension (SCD) management. This approach made sense when target warehouses like Teradata, Oracle Exadata, or Netezza had limited and expensive compute -- pre-processing data before loading reduced warehouse resource consumption and query costs.

ETL architectures rely on staging areas where intermediate data lands between extraction and transformation. A typical enterprise ETL pipeline might extract from 50-200 source systems nightly, stage data in a relational database, apply 500-2000 transformation mappings sequentially, and load the results into a dimensional model between 2:00 AM and 6:00 AM to meet the morning reporting SLA.

1.2 Modern ELT

Extract-Load-Transform (ELT) inverts the transformation sequence by loading raw data into the target system first, then transforming it in-place using the target system's own compute engine. This pattern became viable -- and then dominant -- with the rise of cloud data warehouses (Snowflake, BigQuery, Redshift) and lakehouses (Databricks) that provide elastic, pay-per-query compute that scales independently from storage.

In an ELT architecture, the extraction and loading phases are typically handled by managed connectors (Fivetran, Airbyte, Stitch) that replicate source data into raw landing tables with minimal transformation -- perhaps only data type normalization and basic schema flattening. The raw data is then transformed using SQL-based tools like dbt (data build tool) that execute transformation logic directly inside the warehouse.

The ELT approach offers several structural advantages: raw data preservation enables re-transformation when business logic changes, warehouse-native compute eliminates dedicated ETL server infrastructure, SQL-based transformations are more accessible to analysts than proprietary ETL tool languages, and the decoupling of ingestion from transformation enables independent scaling and scheduling of each concern.

1.3 When to Use Each

FactorETL (Traditional)ELT (Modern)
Target SystemOn-premise warehouse, RDBMSCloud warehouse, lakehouse
Transformation ComplexityComplex procedural logic, ML-based cleansingSQL-expressible business logic
Data VolumeModerate (GB-low TB per day)High (TB-PB scale)
Compliance RequirementsPII must be masked before landingRaw zone with access controls acceptable
Team SkillsETL tool specialists (Informatica, DataStage)SQL + analytics engineering (dbt)
Schema StabilityStable, well-defined schemasEvolving schemas, semi-structured data
Budget ModelCapEx (fixed infrastructure)OpEx (pay-per-query compute)
Latency ToleranceBatch windows acceptableNear-real-time to batch
The Hybrid Reality

Most enterprise environments in 2026 operate a hybrid of ETL and ELT. Legacy on-premise systems continue using traditional ETL tools like Informatica or SSIS that organizations have invested decades of business logic into, while new cloud-native workloads adopt ELT with dbt and managed connectors. The migration path is rarely a wholesale rip-and-replace. Instead, organizations typically adopt ELT for new sources while gradually migrating existing ETL pipelines as they undergo major revisions, spreading the transition over 2-4 years.

1.4 The Paradigm Shift

Three technological shifts drove the transition from ETL to ELT. First, the separation of storage and compute in cloud warehouses eliminated the cost penalty for storing raw data alongside transformed data. Snowflake, BigQuery, and Redshift Serverless charge pennies per GB per month for storage while allowing compute to scale elastically. Second, columnar storage formats and query optimization in modern warehouses made SQL transformations performant at scales that previously required Spark or MapReduce. Third, the rise of analytics engineering as a discipline -- championed by dbt Labs -- demonstrated that version-controlled, tested, documented SQL transformations could replace proprietary ETL tool logic with better maintainability and lower vendor lock-in.

2. Pipeline Architecture Patterns

2.1 Batch Processing

Batch processing remains the workhorse pattern for analytical data pipelines, processing accumulated data at scheduled intervals -- hourly, daily, or weekly. A batch pipeline extracts a bounded set of records (typically based on a timestamp watermark or change flag), applies transformations, and loads the results into the target system.

Batch pipelines are appropriate when data freshness requirements are measured in hours rather than seconds. Financial close processes, regulatory reporting, customer segmentation updates, and ML model retraining are classic batch use cases. The key advantage of batch processing is simplicity: bounded datasets are easier to reason about, test, and debug than continuous streams, and batch systems offer straightforward backfill and reprocessing semantics.

# Typical batch pipeline execution timeline # ======================================== # 00:00 UTC - Scheduler triggers extraction DAG # 00:05 - Extract from PostgreSQL (incremental, last 24h) # 00:15 - Extract from Salesforce API (modified records) # 00:25 - Extract from S3 file drops (new CSV files) # 00:30 - Raw data landed in warehouse staging schema # 00:35 - dbt run: staging models (clean + deduplicate) # 01:00 - dbt run: intermediate models (joins + business logic) # 01:30 - dbt run: marts models (dimensional facts + dims) # 01:45 - dbt test: data quality assertions # 02:00 - BI cache refresh (Looker PDTs, Tableau extracts) # 02:15 - SLA check: all dashboards showing current data # 02:20 - Slack notification: pipeline complete, 0 test failures

2.2 Micro-Batch Processing

Micro-batch processing strikes a balance between batch simplicity and streaming freshness by running small batch jobs at high frequency -- typically every 5 to 15 minutes. Apache Spark Structured Streaming uses micro-batch as its default execution model, processing accumulated data in small windows while maintaining exactly-once semantics through checkpoint management.

Micro-batch is well-suited for operational dashboards that need sub-hour freshness without the complexity of true streaming infrastructure. E-commerce inventory updates, logistics tracking, and marketing campaign monitoring are common micro-batch use cases. The pattern preserves batch semantics (bounded datasets, SQL-friendly) while delivering near-real-time freshness.

2.3 Streaming (Real-Time)

True streaming pipelines process events individually or in small windows as they arrive, achieving sub-second latency from event occurrence to downstream availability. Apache Kafka serves as the de facto event backbone, with stream processing handled by Apache Flink, Kafka Streams, or cloud-native services like Amazon Kinesis Data Analytics, Google Cloud Dataflow, or Azure Stream Analytics.

Streaming architectures are essential for fraud detection (where a 5-second delay can mean a completed fraudulent transaction), real-time recommendation engines, IoT sensor monitoring with threshold alerting, and financial market data processing. The complexity cost is significant: streaming systems require careful management of event ordering, late-arriving data, state management, and exactly-once processing guarantees.

2.4 Change Data Capture (CDC)

Change Data Capture reads the transaction log (WAL in PostgreSQL, binlog in MySQL, redo log in Oracle) of a source database to capture inserts, updates, and deletes as they occur, streaming them to downstream systems without impacting source database performance. Debezium is the leading open-source CDC platform, running as Kafka Connect connectors that translate database logs into Kafka events.

CDC provides several advantages over traditional batch extraction: it captures every change (not just the latest state at extraction time), imposes near-zero load on source databases, preserves the complete change history including intermediate states, and delivers changes within seconds of the source transaction commit. CDC has become the preferred ingestion method for replicating transactional databases into analytical systems.

# Debezium CDC configuration for PostgreSQL # ========================================== { "name": "inventory-connector", "config": { "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "database.hostname": "postgres-primary.internal", "database.port": "5432", "database.user": "debezium", "database.password": "${secrets:pg-cdc-password}", "database.dbname": "inventory_db", "database.server.name": "inventory", "schema.include.list": "public", "table.include.list": "public.orders,public.order_items,public.products", "plugin.name": "pgoutput", "slot.name": "debezium_inventory", "publication.name": "dbz_publication", "topic.prefix": "cdc.inventory", "snapshot.mode": "initial", "tombstones.on.delete": true, "transforms": "route", "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter", "transforms.route.regex": "([^.]+)\\.([^.]+)\\.([^.]+)", "transforms.route.replacement": "cdc.$1.$3" } }

2.5 Hybrid Approaches (Lambda & Kappa)

The Lambda Architecture runs parallel batch and streaming paths: a batch layer reprocesses the complete dataset periodically for accuracy, while a speed layer processes real-time events for low latency. A serving layer merges both views. Lambda provides the correctness of batch with the freshness of streaming, but at the cost of maintaining two codebases with potentially divergent logic.

The Kappa Architecture eliminates the batch layer entirely, processing everything as streams. Historical reprocessing is achieved by replaying the event log (Kafka topic) from an earlier offset. Kappa simplifies the codebase to a single processing path but requires that the streaming layer can handle both real-time and historical replay workloads at scale.

In practice, most modern architectures have converged toward a Lakehouse pattern that sidesteps the Lambda/Kappa debate: streaming events land in a lakehouse table format (Delta Lake, Apache Iceberg, Apache Hudi) that supports both streaming appends and batch reads from a single storage layer, eliminating the need for separate batch and speed paths.

PatternLatencyComplexityCostBest For
BatchHoursLowLowAnalytics, reporting, ML training
Micro-BatchMinutesMediumMediumOperational dashboards, inventory
StreamingSecondsHighHighFraud, IoT, real-time personalization
CDCSecondsMediumMediumDatabase replication, audit trails
LambdaSeconds + HoursVery HighHighSystems needing both speed and accuracy
KappaSecondsHighMedium-HighEvent-sourced systems, unified processing

3. Data Orchestration

3.1 Apache Airflow

Apache Airflow, originally developed at Airbnb in 2014 and now an Apache Software Foundation top-level project, remains the most widely deployed data orchestration platform. Airflow defines pipelines as Directed Acyclic Graphs (DAGs) written in Python, providing full programmatic control over task dependencies, scheduling, retries, and alerting. As of 2026, Airflow powers data pipelines at thousands of organizations from startups to Fortune 100 enterprises.

Airflow's strengths include its massive operator library (500+ pre-built integrations), active community (15,000+ GitHub stars, 2,500+ contributors), and the availability of managed services (Astronomer, Google Cloud Composer, Amazon MWAA). Airflow 2.x introduced the TaskFlow API for cleaner Python-native DAG definitions, dynamic task mapping for variable-width parallel execution, and dataset-aware scheduling that triggers DAGs based on data availability rather than fixed schedules.

# Apache Airflow DAG - ELT Pipeline Example from airflow.decorators import dag, task from airflow.providers.snowflake.operators.snowflake import SnowflakeOperator from airflow.providers.airbyte.operators.airbyte import AirbyteTriggerSyncOperator from cosmos import DbtTaskGroup, ProjectConfig, ProfileConfig from datetime import datetime, timedelta @dag( schedule="0 2 * * *", start_date=datetime(2026, 1, 1), catchup=False, default_args={ "retries": 3, "retry_delay": timedelta(minutes=5), "on_failure_callback": slack_alert, }, tags=["elt", "daily", "production"], ) def daily_elt_pipeline(): # Step 1: Ingest from sources via Airbyte ingest_salesforce = AirbyteTriggerSyncOperator( task_id="ingest_salesforce", airbyte_conn_id="airbyte_prod", connection_id="sf-connection-uuid", asynchronous=False, timeout=3600, ) ingest_postgres = AirbyteTriggerSyncOperator( task_id="ingest_postgres", airbyte_conn_id="airbyte_prod", connection_id="pg-connection-uuid", asynchronous=False, timeout=3600, ) # Step 2: Run dbt transformations dbt_transform = DbtTaskGroup( group_id="dbt_transform", project_config=ProjectConfig("/opt/airflow/dbt/analytics"), profile_config=ProfileConfig( profile_name="snowflake_prod", target_name="prod", ), default_args={"retries": 2}, ) # Step 3: Data quality checks @task def run_quality_checks(): # Execute Great Expectations suite pass # Step 4: Refresh BI layer refresh_looker = SnowflakeOperator( task_id="refresh_looker_pdts", sql="CALL analytics.refresh_looker_cache();", snowflake_conn_id="snowflake_prod", ) # Dependencies [ingest_salesforce, ingest_postgres] >> dbt_transform dbt_transform >> run_quality_checks() >> refresh_looker daily_elt_pipeline()

3.2 Dagster

Dagster takes a fundamentally different approach to orchestration with its "software-defined assets" model. Rather than defining pipelines as sequences of tasks (like Airflow DAGs), Dagster defines the data assets (tables, files, ML models) that the pipeline produces, and the framework infers the execution graph from asset dependencies. This asset-centric approach aligns pipeline definitions more closely with how data teams think about their work: in terms of the datasets they maintain rather than the jobs that produce them.

Dagster's developer experience is widely regarded as superior to Airflow's: local development with `dagster dev` provides a full-featured UI for testing individual assets, integrated type checking catches configuration errors before runtime, and built-in I/O managers abstract storage concerns from transformation logic. Dagster+ (the managed offering from Elementl) provides serverless execution, branch deployments for CI/CD, and built-in alerting.

3.3 Prefect

Prefect positions itself as the "second-generation" orchestrator, eliminating much of Airflow's operational burden through a hybrid execution model: the Prefect Cloud control plane manages scheduling, observability, and API access, while flows execute in your own infrastructure (Kubernetes, ECS, Cloud Run) via lightweight agents. This architecture eliminates the need to manage an orchestrator database, web server, and scheduler.

Prefect's Python-native API treats any Python function decorated with `@flow` or `@task` as an orchestratable unit, with no DAG definition boilerplate. Dynamic workflows, parameterized runs, and sub-flows are first-class concepts. Prefect is particularly strong for ML pipeline orchestration where workflow shapes are data-dependent and cannot be defined statically at parse time.

3.4 Other Orchestrators

3.5 Orchestrator Comparison

FeatureAirflowDagsterPrefectMage
Core ModelTask DAGsSoftware-Defined AssetsDecorated Python FunctionsNotebook Blocks
LanguagePythonPythonPythonPython / SQL
Managed OfferingAstronomer, MWAA, ComposerDagster+Prefect CloudMage Pro
Community SizeVery LargeGrowing FastMediumSmall-Medium
Local Dev ExperienceModerateExcellentGoodExcellent
Dynamic WorkflowsDynamic Task Mapping (2.3+)NativeNativeLimited
Data LineageVia pluginsBuilt-in (asset graph)Via MarvinBuilt-in
Best ForComplex enterprise pipelinesAsset-centric analyticsML pipelines, minimal opsSmall teams, rapid dev

4. Transformation Layer

4.1 dbt (data build tool)

dbt has become the de facto standard for SQL-based transformations in modern data pipelines. Created by dbt Labs (formerly Fishtown Analytics), dbt applies software engineering practices -- version control, testing, documentation, modularity -- to SQL transformation logic. Data engineers write SELECT statements that define how raw data should be transformed, and dbt handles materialization (creating tables, views, or incremental models), dependency resolution, and execution ordering.

A dbt project organizes models into layers following the medallion architecture: staging models (bronze) clean and rename raw source columns, intermediate models (silver) apply joins and business logic, and marts models (gold) produce the final dimensional tables consumed by BI tools and applications. Each model is a SQL file with a ref() function that declares dependencies on upstream models, enabling dbt to build and execute the transformation DAG automatically.

-- dbt model: marts/fct_daily_revenue.sql -- ======================================== -- Computes daily revenue by product category with incremental processing {{ config( materialized='incremental', unique_key='revenue_date || product_category', cluster_by=['revenue_date'], incremental_strategy='merge', on_schema_change='append_new_columns' ) }} WITH orders AS ( SELECT * FROM {{ ref('stg_orders') }} {% if is_incremental() %} WHERE order_date >= (SELECT MAX(revenue_date) - INTERVAL '3 days' FROM {{ this }}) {% endif %} ), order_items AS ( SELECT * FROM {{ ref('stg_order_items') }} ), products AS ( SELECT * FROM {{ ref('dim_products') }} ), daily_revenue AS ( SELECT o.order_date AS revenue_date, p.product_category, COUNT(DISTINCT o.order_id) AS order_count, COUNT(oi.item_id) AS items_sold, SUM(oi.quantity * oi.unit_price) AS gross_revenue, SUM(oi.discount_amount) AS total_discounts, SUM(oi.quantity * oi.unit_price) - SUM(oi.discount_amount) AS net_revenue, AVG(oi.quantity * oi.unit_price) AS avg_order_value, COUNT(DISTINCT o.customer_id) AS unique_customers FROM orders o INNER JOIN order_items oi ON o.order_id = oi.order_id INNER JOIN products p ON oi.product_id = p.product_id WHERE o.order_status NOT IN ('cancelled', 'refunded') GROUP BY 1, 2 ) SELECT * FROM daily_revenue

4.2 dbt Testing and Data Quality

dbt's built-in testing framework enables data quality assertions at the model level. Schema tests validate column properties (not null, unique, accepted values, relationships), while custom data tests execute arbitrary SQL that must return zero rows to pass. dbt packages like dbt-expectations extend the testing vocabulary with statistical distribution checks, recency tests, and cross-database comparisons.

# dbt schema.yml - Model tests and documentation # ================================================ version: 2 models: - name: fct_daily_revenue description: "Daily revenue aggregated by product category" columns: - name: revenue_date description: "Date of revenue calculation" tests: - not_null - dbt_expectations.expect_column_values_to_be_of_type: column_type: date - name: product_category tests: - not_null - accepted_values: values: ['electronics', 'apparel', 'food', 'home', 'industrial'] - name: net_revenue tests: - not_null - dbt_expectations.expect_column_values_to_be_between: min_value: 0 max_value: 10000000 - name: order_count tests: - not_null - dbt_utils.expression_is_true: expression: ">= 0"

4.3 Apache Spark Transformations

When transformations exceed what SQL can efficiently express -- complex ML feature engineering, graph algorithms, unstructured data processing, or transformations spanning multiple storage systems -- Apache Spark provides a distributed compute engine that scales across clusters of hundreds of nodes. Spark's DataFrame API (PySpark, Scala, Java) and Spark SQL interface handle both batch and streaming transformations with a unified programming model.

Databricks, the commercial platform built on Spark, has emerged as the leading lakehouse platform, running Spark workloads with optimized performance (Photon engine), managed infrastructure, and integrated governance (Unity Catalog). For organizations with complex transformation requirements that exceed dbt's SQL-only model, Spark on Databricks provides the most capable transformation layer.

4.4 SQL-Based Transformations Beyond dbt

5. Data Ingestion

5.1 Managed API Connectors

Managed connector platforms have transformed data ingestion from a custom engineering effort into a configuration task. These platforms maintain pre-built connectors to hundreds of SaaS applications, databases, and file systems, handling API pagination, rate limiting, schema detection, incremental extraction, and error recovery automatically.

PlatformConnectorsDeploymentPricing ModelKey Strength
Fivetran500+Fully managed SaaSMonthly Active Rows (MAR)Reliability, enterprise support, deepest connector catalog
Airbyte400+Self-hosted or CloudCredits (Cloud) or Free (OSS)Open-source, custom connector SDK, cost control
Stitch (Talend)200+Fully managed SaaSRow-basedSimple setup, Singer protocol compatibility
Hevo Data150+Fully managed SaaSEvent-basedNo-code transformations, APAC support presence
Matillion100+SaaS or Cloud-nativeCredit-basedBuilt-in transformation layer, ELT in one tool

5.2 Database Replication with CDC

For transactional database sources, Change Data Capture provides the most efficient and least intrusive replication method. Debezium, the leading open-source CDC platform, reads database transaction logs and streams changes as events to Apache Kafka. From Kafka, changes flow to any downstream system via Kafka Connect sink connectors.

CDC architecture for enterprise database replication typically follows this pattern:

5.3 File Ingestion

Despite the growth of API and streaming ingestion, file-based data exchange remains prevalent in enterprise environments. Partner data feeds, government regulatory filings, legacy system exports, and IoT batch uploads commonly arrive as CSV, JSON, Parquet, XML, or Excel files deposited in cloud storage (S3, GCS, Azure Blob) or SFTP servers.

Robust file ingestion pipelines implement: file arrival detection (S3 Event Notifications, GCS Pub/Sub, Azure Event Grid), schema validation against expected formats, malformed record quarantining to dead letter storage, deduplication based on file checksums, archive management after successful processing, and audit logging of every file processed with row counts and processing timestamps.

5.4 Streaming Ingestion

Streaming ingestion captures events from real-time sources and lands them in analytical storage with minimal latency. Common streaming ingestion patterns include:

6. Data Storage Targets

6.1 Cloud Data Warehouses

Cloud data warehouses serve as the primary analytical storage layer in modern data architectures. The three dominant platforms each offer distinct architectural advantages:

6.2 Data Lakes

Data lakes store raw, unprocessed data in cloud object storage (S3, GCS, Azure Blob) using open file formats (Parquet, ORC, Avro, JSON). The lake serves as the landing zone for all data -- structured, semi-structured, and unstructured -- before it is transformed and loaded into analytical systems. Data lakes provide the lowest cost per TB of any storage tier, typically $0.02-0.03/GB/month for standard storage classes.

The challenge with raw data lakes has historically been governance: without a metadata layer, lakes devolve into "data swamps" where nobody knows what data exists, its quality, or its lineage. This challenge drove the development of lakehouse table formats.

6.3 Lakehouses

The lakehouse architecture combines the low-cost, open-format storage of data lakes with the ACID transactions, schema enforcement, and query performance of data warehouses. Three open table formats enable the lakehouse pattern:

6.4 Feature Stores

Feature stores serve as the specialized storage layer for machine learning features, bridging the gap between data pipelines and ML model serving. A feature store provides consistent feature computation (ensuring training and serving use identical feature logic), feature reuse across models, point-in-time correct training data generation, and low-latency online serving for real-time inference.

Leading feature store platforms include Feast (open-source), Tecton (managed), Databricks Feature Store, Amazon SageMaker Feature Store, and Vertex AI Feature Store. Feature stores are becoming a critical component of the data platform for organizations with production ML workloads, preventing the "feature pipeline skew" problem where training and serving features diverge.

Storage TargetQuery LatencyCost/TB/MonthBest For
Cloud Warehouse (Snowflake)Seconds$23-40 (storage) + computeBI queries, ad-hoc analytics, data sharing
Cloud Warehouse (BigQuery)Seconds$20 (active) + $5/TB scannedServerless analytics, Google ecosystem
Data Lake (S3/GCS)Minutes (raw)$20-23 (standard tier)Raw data archive, ML training data
Lakehouse (Delta/Iceberg)Seconds-Minutes$20-23 (storage) + computeUnified batch + streaming, open format
Feature Store (Feast/Tecton)Milliseconds (online)Varies by backendML feature serving, real-time inference
Operational DB (PostgreSQL)Milliseconds$100-300+ (managed)Application serving, low-latency OLTP

7. Pipeline Monitoring & Observability

7.1 Data Freshness

Data freshness measures the time elapsed between when an event occurs in the source system and when it becomes available for query in the target system. Freshness SLAs are the most critical pipeline metric because stale data directly impacts business decisions. A freshness monitoring system should track: the timestamp of the most recent record in each target table, the time since the last successful pipeline run, and the delta between source system clock and target system data currency.

Tools like Monte Carlo, Elementary, and Soda provide automated freshness monitoring that alerts when a table's data falls behind its expected schedule. dbt's built-in `freshness` configuration in source YAML files checks source table freshness as part of the transformation pipeline.

7.2 Pipeline SLAs

Production data pipelines should define explicit SLAs for three dimensions:

7.3 Data Quality Metrics

# Data Quality Monitoring Dashboard Metrics # ========================================== # # Volume Metrics: # - Row count per table (daily delta, % change) # - Byte size per table (growth trend) # - Source-to-target row count reconciliation # # Schema Metrics: # - Column count changes (new columns, dropped columns) # - Data type changes (unexpected type coercion) # - Null percentage per column (trend over time) # # Distribution Metrics: # - Min / Max / Mean / Median / Std Dev per numeric column # - Cardinality per categorical column # - Value distribution histograms (detect drift) # # Referential Integrity: # - Foreign key violation counts # - Orphaned records per relationship # - Cross-table join hit rates # # Freshness Metrics: # - Max event timestamp per source table # - Pipeline completion time vs. SLA # - Time since last successful full refresh

7.4 Data Lineage Tracking

Data lineage traces the complete journey of a data element from source system through every transformation to its final consumption point. Lineage enables impact analysis (which dashboards break if this source table schema changes?), root cause analysis (why did this metric change unexpectedly?), and compliance documentation (where does customer PII flow through the pipeline?).

Lineage tracking tools fall into two categories: active lineage (extracted from pipeline code at build time, as dbt and Dagster provide natively) and passive lineage (observed from query logs at runtime, as tools like Atlan, DataHub, and Monte Carlo provide). The most comprehensive lineage implementations combine both approaches for end-to-end visibility from source system to BI dashboard.

7.5 Alerting

Effective pipeline alerting requires careful calibration to avoid alert fatigue while ensuring genuine issues are surfaced immediately. Best practices include:

8. Error Handling & Recovery

8.1 Idempotency

Idempotent pipelines produce the same output regardless of how many times they are executed for a given input. Idempotency is the single most important design principle for reliable data pipelines because it enables safe retries, backfills, and reprocessing without data duplication or corruption.

Implementing idempotency requires replacing INSERT operations with MERGE/UPSERT patterns using natural business keys, using date-partitioned overwrite strategies (delete-then-insert for a partition), or employing deduplication logic based on event IDs or composite keys. In dbt, incremental models with a properly defined `unique_key` and `merge` strategy provide idempotent behavior by default.

-- Idempotent merge pattern in Snowflake -- ======================================= MERGE INTO analytics.fct_orders AS target USING staging.stg_orders AS source ON target.order_id = source.order_id WHEN MATCHED AND source._loaded_at > target._loaded_at THEN UPDATE SET target.order_status = source.order_status, target.total_amount = source.total_amount, target.updated_at = source.updated_at, target._loaded_at = source._loaded_at WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_status, total_amount, created_at, updated_at, _loaded_at) VALUES (source.order_id, source.customer_id, source.order_status, source.total_amount, source.created_at, source.updated_at, source._loaded_at);

8.2 Exactly-Once Processing

Exactly-once semantics guarantee that each record is processed neither zero times (data loss) nor more than once (duplicates). True exactly-once requires coordination between the processing engine and the output sink:

8.3 Dead Letter Queues

A dead letter queue (DLQ) captures records that fail processing -- malformed data, schema mismatches, business rule violations -- without blocking the main pipeline. The DLQ pattern prevents a single bad record from halting the entire pipeline while preserving failed records for investigation and reprocessing.

DLQ implementation requires: routing failed records to a separate storage location (S3 prefix, Kafka topic, database table) with the original record payload plus error context (error message, timestamp, pipeline stage, retry count); monitoring DLQ depth as a quality metric; and providing a reprocessing mechanism to replay corrected records back through the pipeline.

8.4 Retry Strategies

8.5 Backfill Patterns

Backfilling reprocesses historical data to incorporate pipeline logic changes, recover from data quality issues, or onboard new data sources. Well-designed backfill patterns include:

9. Cost Optimization

9.1 Compute Right-Sizing

The most impactful cost optimization in data pipelines is right-sizing compute resources. Cloud data warehouses charge for compute time (Snowflake credits, BigQuery slots, Redshift node-hours), and many organizations over-provision by 2-5x due to defaulting to large warehouse sizes during development and never revisiting.

9.2 Scheduling Optimization

Not every pipeline needs to run hourly. Audit pipeline schedules against actual data consumption patterns. Common optimizations include:

9.3 Incremental Processing

Incremental processing is the single most effective cost reduction strategy for data pipelines. Instead of reprocessing the entire dataset on every run, incremental pipelines process only new or changed records since the last run. A well-implemented incremental pipeline processing 1 day of data instead of 365 days reduces compute cost by ~99.7%.

dbt incremental models, Spark's watermark-based incremental reads, and CDC-based ingestion all implement incremental processing patterns. The key requirement is a reliable change detection mechanism: a monotonically increasing timestamp, an auto-incrementing ID, or a CDC event stream that captures all changes.

9.4 Partition Strategies

Partitioning divides large tables into segments based on column values (typically date), enabling query engines to skip irrelevant data segments (partition pruning). Effective partitioning reduces both query cost and execution time:

60-90%
Cost Savings with Spot/Preemptible Compute
99%+
Compute Reduction via Incremental Processing
3-5x
Typical Over-Provisioning Factor
$0.02
Cost per GB/Month in Cloud Object Storage

10. The Modern Data Stack

10.1 Overview

The Modern Data Stack (MDS) is an architectural pattern that assembles best-of-breed cloud-native tools into a composable data platform. Unlike monolithic platforms (Informatica, IBM) that attempt to provide every capability in a single product, the MDS embraces specialization: dedicated tools for ingestion, storage, transformation, orchestration, BI, and data quality, connected via well-defined interfaces and a shared cloud warehouse as the central integration point.

The MDS pattern emerged around 2018-2020 as cloud warehouses eliminated the infrastructure bottleneck, dbt democratized transformation, and a wave of venture-funded startups built specialized tools for every layer of the data platform. By 2026, the MDS has matured from a startup phenomenon into an enterprise-grade architecture pattern adopted by companies from Series A startups to Fortune 500 enterprises.

10.2 MDS Component Landscape

# Modern Data Stack - Component Map (2026) # ========================================== # # INGESTION LAYER # ├── Managed Connectors: Fivetran, Airbyte, Stitch, Hevo # ├── CDC: Debezium, Arcion, Striim # ├── Event Collection: Segment, RudderStack, Snowplow # └── Streaming: Confluent (Kafka), Amazon MSK, Redpanda # # STORAGE LAYER # ├── Cloud Warehouse: Snowflake, BigQuery, Redshift # ├── Lakehouse: Databricks, Apache Iceberg + Trino # └── Object Storage: S3, GCS, Azure Blob # # TRANSFORMATION LAYER # ├── SQL-Based: dbt, SQLMesh, Dataform (Google) # ├── Distributed: Spark (Databricks), Snowpark # └── Feature Engineering: Feast, Tecton # # ORCHESTRATION LAYER # ├── Apache Airflow (Astronomer, Composer, MWAA) # ├── Dagster (Dagster+) # ├── Prefect (Prefect Cloud) # └── Cloud-Native: Step Functions, ADF, Workflows # # BI & ANALYTICS LAYER # ├── Enterprise BI: Tableau, Power BI, Looker # ├── Modern BI: Preset (Superset), Metabase, Lightdash # ├── Embedded Analytics: Sigma Computing, Cube # └── Notebooks: Hex, Deepnote, Observable # # DATA QUALITY & OBSERVABILITY # ├── Observability: Monte Carlo, Bigeye, Anomalo # ├── Testing: Great Expectations, Soda, Elementary # └── Contracts: dbt contracts, Schemata # # GOVERNANCE & CATALOG # ├── Catalog: Atlan, DataHub, Alation, Collibra # ├── Access Control: Immuta, Privacera # └── Lineage: OpenLineage, Marquez

10.3 How Components Fit Together

A reference MDS architecture flows data through these layers:

  1. Ingestion: Fivetran syncs 30 SaaS sources (Salesforce, HubSpot, Stripe, Zendesk) to Snowflake raw schema every 6 hours. Debezium streams PostgreSQL CDC events through Kafka to Snowflake in near-real-time for operational data.
  2. Orchestration: Airflow triggers dbt runs after Fivetran sync completion (using Fivetran sensor), manages Spark jobs for ML feature pipelines, and coordinates cross-system dependencies.
  3. Transformation: dbt transforms raw data through staging, intermediate, and marts layers, producing clean dimensional models. dbt tests validate quality at each layer. dbt docs generate a searchable data catalog.
  4. Storage: Snowflake serves as the central analytical warehouse. Databricks handles large-scale ML feature engineering on Delta Lake. S3 stores raw file archives and ML training datasets.
  5. Quality: Monte Carlo monitors data freshness, volume, schema, and distribution anomalies across all Snowflake tables. Elementary runs dbt-native data tests and publishes quality dashboards.
  6. Consumption: Looker connects to Snowflake marts for executive dashboards. Hex notebooks enable data science exploration. Reverse ETL (Census, Hightouch) syncs warehouse data back to Salesforce and HubSpot.

10.4 MDS Considerations for APAC Enterprises

APAC enterprises adopting the MDS pattern should consider several region-specific factors:

Avoiding Tool Sprawl

The MDS philosophy of best-of-breed tools can easily lead to an unmanageable tool landscape with 15-20 specialized products, each with its own pricing model, support contract, and learning curve. For teams under 10 data engineers, we recommend starting with a minimal stack: Airbyte (ingestion) + Snowflake or BigQuery (warehouse) + dbt (transformation) + Airflow or Dagster (orchestration) + one BI tool. Add specialized tools (observability, catalog, reverse ETL) only when the pain of not having them exceeds the operational cost of managing them.

11. Frequently Asked Questions

What is the difference between ETL and ELT?

ETL (Extract-Transform-Load) transforms data before loading it into the target system, typically using a dedicated transformation engine like Informatica or SSIS. ELT (Extract-Load-Transform) loads raw data first into a cloud data warehouse or lakehouse, then transforms it in-place using the target system's compute power. ELT has become the dominant pattern for cloud-native data pipelines because modern warehouses like Snowflake, BigQuery, and Databricks provide massive, elastic compute that makes in-warehouse transformation faster and more cost-effective than external transformation servers. The shift to ELT also preserves raw data for re-transformation when business logic changes.

When should I use batch processing vs. real-time streaming pipelines?

Use batch processing when data freshness requirements are measured in hours or days -- daily financial reporting, monthly analytics, periodic ML model training. Use real-time streaming when business value depends on sub-second or sub-minute latency -- fraud detection, live recommendation engines, IoT sensor monitoring, operational dashboards. Many enterprises use a hybrid approach with streaming for operational use cases and batch for analytical workloads, balancing cost and latency. Start with batch (simpler, cheaper) and add streaming only where the business case justifies the added complexity.

What is the best data orchestration tool in 2026?

Apache Airflow remains the most widely deployed orchestrator with the largest community and integrator ecosystem, making it the safest enterprise choice. However, Dagster is gaining rapid adoption for its software-defined assets model and superior developer experience, particularly among teams building new data platforms without Airflow legacy. Prefect offers the best managed cloud experience with minimal infrastructure overhead. For AWS-native teams, Step Functions provides tight integration with zero infrastructure management. The best choice depends on your team size, cloud provider, existing infrastructure, and whether you prefer code-first (Dagster) or DAG-based (Airflow) pipeline definitions.

How do I ensure data quality in my pipelines?

Implement data quality checks at multiple pipeline stages: schema validation at ingestion, row count and null checks after extraction, referential integrity checks during transformation, and statistical distribution checks before serving. Use dbt tests for transformation-layer quality assertions, Great Expectations or Soda for cross-layer validation, and Monte Carlo or Elementary for continuous observability. Define data contracts between producers and consumers. Implement circuit breakers that halt pipelines when quality thresholds are violated. Maintain data quality dashboards with freshness SLAs for each critical dataset. Treat data quality as a continuous process, not a one-time setup.

What does a modern data stack (MDS) consist of?

A modern data stack typically includes: (1) an ingestion layer using managed connectors like Fivetran or Airbyte for SaaS sources and Debezium for database CDC; (2) a storage layer using a cloud data warehouse (Snowflake, BigQuery, Redshift) or lakehouse (Databricks, Apache Iceberg); (3) a transformation layer using dbt for SQL-based modeling; (4) an orchestration layer using Airflow, Dagster, or Prefect; (5) a BI and analytics layer using Looker, Tableau, or Metabase; (6) a data quality layer using Monte Carlo, Soda, or Elementary; and (7) a governance layer using Atlan, DataHub, or Unity Catalog. The MDS philosophy favors composable, best-of-breed tools over monolithic platforms.

How do I handle pipeline failures and ensure exactly-once processing?

Design all pipeline operations for idempotency so re-running a failed task produces the same result without duplicates. Use MERGE/UPSERT operations with natural business keys instead of blind INSERT statements. Implement dead letter queues for records that fail validation so they do not block the main pipeline. Use checkpointing in streaming systems (Kafka consumer offsets, Flink savepoints) to resume from the exact failure point. For critical pipelines, implement automated retry with exponential backoff and circuit breakers that alert on-call teams after a configurable number of failures. Maintain runbooks for common failure modes so that incident response is systematic rather than ad-hoc.

Related Articles

Architecture

Data Lakehouse Architecture

Delta Lake, Apache Iceberg, and Apache Hudi compared. Building unified batch and streaming storage on cloud object stores.

 Streaming

Real-Time Streaming Analytics

Apache Kafka, Flink, and Spark Streaming for sub-second analytics. Event-driven architectures and stream processing patterns.

 Platform

Snowflake Data Platform Guide

Complete Snowflake architecture, virtual warehouses, data sharing, Snowpark, and cost optimization strategies.

 Governance

Data Governance & Quality Framework

Enterprise data governance policies, quality frameworks, catalog implementation, and compliance for APAC data regulations.

 Industry

Manufacturing Analytics & IoT

IoT data pipelines for manufacturing, predictive maintenance analytics, OEE dashboards, and Industry 4.0 data architecture.
Browse All Data Analytics Guides →

Get Your Data Pipeline Architecture Review

Receive a tailored assessment of your data pipeline architecture covering orchestration strategy, ingestion patterns, transformation approach, cost optimization opportunities, and a migration roadmap to the modern data stack.

© 2026 Seraphim Co., Ltd.