Integration / ETL / Batch Processing
ETLCloudData PipelineServerless

Integration ETL / Batch Processing (Cloud)

David TirabassiUpdated
Enterprise/Solution Architect · Almost 30 years' experience, specialising in integration · TOGAF 9.1 certifiedLinkedInGitHub

Problem

Data teams building a cloud analytics platform must pull data from systems they do not run: on-premises databases, SaaS applications and third-party APIs. It arrives through a perimeter ingestion gateway or a private VPN/Direct Connect-equivalent link, and the pipelines reach cloud data services over private endpoints with managed service identities. Loading that does not scale stalls throughput, raises costs, and lets lapses in data integrity and governance reach downstream analytics.

Solution

Build cloud-native Extract, Transform, Load (ETL) or Extract, Load, Transform (ELT) pipelines on serverless or containerised compute. Distributed processing frameworks scale out to ingest structured, semi-structured and unstructured data, run complex transformations and load the results into target data stores, with high throughput, fault tolerance and data quality checks.

Cloud Paradigm

  • Serverless Data Processing: Use fully managed, auto-scaling compute, so teams focus on transformation logic rather than infrastructure.
  • Elastic Scalability: Adjust processing capacity to data volume and complexity, keeping resource use and cost in line.
  • Data Lakehouse Architecture: Integrate ETL/ELT pipelines with unified data platforms that support both data warehousing and data lake paradigms.
  • Data Mesh Principles: Enable decentralised data ownership and consumption through domain-oriented data products.
  • Infrastructure as Code (IaC): Define and deploy data pipelines and associated infrastructure using declarative configuration.

Solution Flow

Data Ingestion Flow (Source to Staging):

  1. Source System: Data comes from on-premises databases, external SaaS applications, streaming platforms or third-party APIs.
  2. Secure Ingestion Gateway: External or on-premises data enters through a designated Egress Gateway (from external systems) or a dedicated ingestion service (e.g., VPN/Direct Connect equivalent) in a Public Subnet (Perimeter).
  3. Data Ingestion Service: A cloud-native ingestion service (e.g., managed message queue, stream processing service or file transfer service) pulls or receives the data and may infer and validate its schema.
  4. Raw Data Landing (Object Storage): Raw data lands in a secure, versioned, immutable Object Storage bucket (e.g., a "raw zone" in a data lake) in a Private Subnet (Workloads), which also gives a recovery point.

Data Processing & Storage Flow:

  1. ETL/ELT Orchestrator: A managed workflow orchestrator (e.g., serverless workflow engine) triggers and sequences the data processing jobs.
  2. Data Transformation Engine: Serverless or containerised compute (e.g., managed data processing service, distributed compute cluster) reads the raw landing zone and cleanses, enriches and aggregates the data by business logic, possibly in stages (e.g., raw -> curated -> conformed).
  3. Curated Data Storage: Validated data is loaded into a curated layer: another Object Storage zone (e.g., "curated zone"), a managed data warehouse or an analytical database, depending on the use case.
  4. Data Consumption: Downstream applications, business intelligence tools, machine learning models and data scientists read directly from the curated layer.

When to Use

  • You need to consolidate structured, semi-structured and unstructured data from on-premises, SaaS and streaming sources into a cloud data lake or warehouse.
  • Volumes are variable or unpredictable, so serverless auto-scaling costs less than fixed-capacity clusters.
  • Consumers need a curated, quality-checked layer separate from immutable raw data, with clear lineage and governance.
  • Batch historical loads and near-real-time stream processing must coexist on one governed platform.
  • Regulators or auditors require traceable transformations, schema versioning and recoverable raw landing zones.

When NOT to Use

  • Data moves point to point with no transformation. A direct replication or CDC tool is lighter.
  • Integration must be sub-second and event-driven, where an ETL orchestrator adds too much latency. Use event streaming or messaging instead.
  • Volumes are low and steady, so a scheduled script or a single managed database job is enough.
  • The workload is purely transactional and needs immediate consistency, not analytical batch or stream processing.
  • Source and target share the same engine, and native federated queries avoid moving data at all.

Trade-offs

  • Elastic, pay-per-use scaling vs the complexity of orchestrating serverless jobs, retries and dead-letter handling across stages.
  • Immutable raw landing plus curated layers vs extra storage cost and duplication across several data zones.
  • Strong governance and lineage vs the upkeep of a schema registry and metadata catalogue as pipelines change.
  • Support for both batch and streaming vs the effort of reconciling two processing models in one framework.
  • Fault tolerance through checkpointing and queues vs more latency and design effort than naive direct loads.

Illustrative Scenario

A hypothetical scenario showing how the pattern applies. Not a client case study.

Imagine a large public university that brings together three kinds of data: student information system records held in on-premises Oracle databases, learning-management activity from a SaaS platform, and admissions feeds from third-party APIs. Nightly exports and streaming events pass through a secure ingestion gateway and land, unaltered, in a versioned raw zone in object storage. A serverless workflow orchestrator then starts the distributed transformation jobs. They cleanse enrolment records, remove duplicate applicants and validate each record against a schema registry, then promote the data from the raw zone to the curated zone. The curated warehouse feeds institutional-research dashboards on retention and degree progression, and machine-learning models use it to flag at-risk students. Lineage metadata supports FERPA governance and the university's accreditation audits. Dead-letter queues catch malformed transcript records so that they can be reprocessed later. Because the jobs run on serverless compute, the platform absorbs enrolment-period spikes without fixed-capacity clusters.

Additional Details

  • Data Modalities: Covers both batch loads of historical data and near-real-time stream processing for continuous ingestion and transformation.
  • Schema Management: Use a schema registry to manage schema changes across data layers, so downstream consumers do not break.
  • Monitoring & Alerting: Monitor pipeline health, data quality, processing latency and resource use, and alert on failures, anomalies and bottlenecks.
  • Data Lineage & Governance: Record each dataset's origin, transformations and path through the pipeline, and connect to cloud-native governance services for cataloguing and policy enforcement.
  • Cost Optimisation: Serverless and auto-scaling services charge only for the compute used during processing. Match storage tiers to access patterns.
  • Fault Tolerance & Resiliency: Build in retries, dead-letter queues and checkpointing, so pipelines recover from transient failures and data stays consistent.

Security Controls

  • Network Segmentation: Keep ETL/ELT infrastructure, sources and targets in Private Subnets (Workloads), away from public access, and connect data services through private or service endpoints.
  • Identity and Access Management (IAM): Give pipelines granular, least-privilege roles and managed service identities for sources, targets and processing resources. Require strong authentication.
  • Data Encryption: Encrypt data at rest in data lakes, warehouses and intermediate storage (e.g., Object Storage, managed databases). Require Transport Layer Security (TLS) 1.2 or higher for data in transit, across network boundaries and inside the workflow.
  • Data Governance & Masking: Run data quality checks, schema validation and lineage tracking. Mask, tokenise or anonymise sensitive data (e.g., PII, PHI) before it reaches lower environments or specific analytical uses.
  • Audit Logging & Monitoring: Log all pipeline activity, data access and transformation steps, and send the logs to a central security information and event management (SIEM) system for anomaly detection and compliance auditing.

Related Patterns