Integration ETL / Batch Processing (External)
Problem
A backend workload and a partner's system must swap large datasets on a schedule, and the partner owns its end of the exchange. Files and API calls cross the internet into a perimeter subnet, where a WAF inspects inbound traffic, the partner authenticates with mTLS, OAuth 2.0 client credentials or SSH keys, and outbound data leaves through an egress gateway. Real-time sync is not needed, but bulk loads that skip validation corrupt records and erode trust between the systems.
Solution
Run a cloud-native Extract, Transform, Load (ETL) or Extract, Load, Transform (ELT) pipeline to orchestrate the bulk transfer. It exchanges data with the partner through secure API endpoints or managed file transfer services in the Public Subnet (Perimeter), so the partner never connects to anything inside the Private Subnet.
Cloud Paradigm
- Batch Data Processing
- Serverless Data Integration
- Data Lake / Data Warehouse Architectures
- Managed File Transfer Services
- Data Pipeline Orchestration
- Perimeter Security for Data Exchange
Solution Flow
Data Ingestion Flow (External to Internal):
- External System: The partner system prepares a bulk dataset for transfer.
- Secure Ingress Point: The partner pushes data to an ingress point in the Public Subnet (Perimeter): an API Gateway for data ingestion, a managed file transfer endpoint, or an Object Storage bucket set up for secure external access.
- Web Application Firewall (WAF/NACLs): WAF rules or NACLs inspect incoming traffic for malicious patterns and unauthorised access.
- Data Ingestion Service / Object Storage: A cloud-native ingestion service receives the data, or it lands in a temporary Object Storage bucket, with initial validation and encryption at rest.
- ETL/ELT Pipeline: A serverless or containerised ETL/ELT pipeline starts on a schedule, file arrival or API call. It extracts the raw data, transforms it by business rules (e.g., schema enforcement, data cleansing, enrichment) and loads it into the target data store (e.g., Data Warehouse, Data Lake, database) in the Private Subnet (Workloads).
- Backend Workload: Internal workloads or analytics platforms consume the processed data.
Data Egress Flow (Internal to External):
- Backend Workload: An internal workload or data processing service selects a dataset to export.
- ETL/ELT Pipeline: A serverless or containerised pipeline extracts the data, transforms it if needed (e.g., anonymisation, format conversion) and stages it for export.
- Secure Egress Point: The data moves to an egress point in the Public Subnet (Perimeter): a managed file transfer service, an export API endpoint, or a temporary Object Storage bucket with controlled external access.
- Egress Gateway / Managed NAT: Outbound connections pass through a Managed NAT / Egress Gateway that enforces FQDN allow-listing, IP address restrictions and, where configured, Data Loss Prevention (DLP) policies.
- External System: The partner retrieves the data with pre-configured authentication and authorisation.
When to Use
- You exchange large datasets with external partners on a schedule or on events, and latency of minutes or more is acceptable.
- Integrity and consistency matter more than speed, and data can be validated, transformed and reconciled in discrete batches.
- Regulations or contracts require DLP, FQDN allow-listing, encryption at rest and auditable data lineage across an organisational boundary.
- Source and target schemas differ, so data needs cleansing, enrichment, anonymisation or format conversion before loading.
- Internal data stores must stay isolated in a Private Subnet while controlled data still flows through a perimeter.
When NOT to Use
- You need real-time or near-real-time synchronisation. Use an event-driven streaming or CDC pattern instead.
- The exchange is small, transactional and request-response, which suits a synchronous API integration pattern better.
- Both systems are internal and trusted, so perimeter WAF and NAT egress controls are unnecessary overhead.
- Data must stay in one consistent transaction across systems, where distributed transaction or saga patterns fit better.
- Continuous, low-latency micro-updates would make batch windows inefficient and leave data stale.
Trade-offs
- Strong data integrity and validation vs batch-window latency that rules out time-sensitive use cases.
- Hardened perimeter isolation with WAF, NAT and DLP vs the work of maintaining ingress and egress controls and allow-lists.
- Elastic serverless or containerised scaling to volume vs unpredictable cost spikes during large transfer runs.
- Internal databases shielded from external exposure vs more moving parts to operate and monitor: staging buckets, pipeline orchestration and reconciliation logic.
- Idempotent, recoverable jobs with checkpointing vs the effort of building retries, dead-letter handling and lineage tracking correctly.
Illustrative Scenario
A hypothetical scenario showing how the pattern applies. Not a client case study.
Suppose an agricultural supply cooperative has to reconcile daily grain intake and fertiliser inventory records with a regional commodity clearinghouse. Each evening the clearinghouse pushes an encrypted bulk dataset to a managed file transfer endpoint in the cooperative's Public Subnet (Perimeter). WAF rules and NACLs inspect the traffic before the file lands in a temporary Object Storage bucket for staging. The file-arrival event starts a serverless ETL pipeline. It enforces the schema, cleanses moisture and weight readings, and loads settlement figures into a Data Warehouse in the Private Subnet, where they feed member payout analysis. In the other direction, a scheduled job extracts member delivery volumes for the clearinghouse, anonymises farm identifiers and stages the output for export. The file leaves through a Managed NAT gateway that applies FQDN allow-listing and DLP. The clearinghouse then retrieves it with its pre-configured credentials, while the cooperative's internal databases stay isolated behind the perimeter.
Additional Details
- Data Volume & Frequency: Suits large datasets updated at low to medium frequency, where batch latency is acceptable. For real-time or near-real-time data, use event-driven streaming patterns.
- Data Governance & Quality: Apply data governance across the ETL/ELT process: data cataloguing, lineage tracking and automated data quality checks.
- Error Handling & Idempotency: Make ETL/ELT jobs idempotent, with error handling, logging and alerting. Add retry logic, dead-letter queues and checkpointing for fault tolerance and recovery.
- Scalability & Elasticity: Use cloud-native, serverless or containerised processing that scales automatically with volume and demand.
- Monitoring & Observability: Monitor ETL/ELT pipelines end to end (job run times, data volumes processed, error rates, resource use) and feed central logging and alerting systems.
- Interface Documentation: Document the data contract, schema definitions, transformation rules and access protocols for inbound and outbound exchanges.
- Avoid Direct Database Access: Never expose internal databases to external systems. All exchange goes through controlled API endpoints or secure file transfer, mediated by the pipeline and perimeter controls.
Security Controls
- Transport Security: Require Transport Layer Security (TLS) 1.2 or higher for all data in transit, by API or file transfer.
- Data at Rest Encryption: Encrypt all temporary and permanent storage for transferred data with industry-standard algorithms (e.g., AES-256).
- Authentication & Authorisation:
- For APIs, use OAuth 2.0 (Client Credentials Grant) or mTLS.
- For file transfers, use SFTP or FTPS with strong authentication (e.g., SSH keys, client certificates), restricted to specific identities.
- Perimeter Security: All inbound and outbound transfer endpoints sit in a Public Subnet (Perimeter), behind a Web Application Firewall (WAF) or Network Access Control Lists (NACLs), and are secured by an Egress Gateway or Managed File Transfer Service.
- Credential Management: Keep all credentials, API keys and certificates in a central secret management service, never in plain text in code or configuration.
- Data Validation & Integrity: Validate data at ingestion and egress points. Use checksums or digital signatures on file transfers to detect tampering or corruption.
- Outbound Controls: Allow outbound connections from the Egress Gateway or Managed NAT Gateway only to the allow-listed external IP addresses or fully qualified domain names (FQDNs) the transfer needs.
- Auditing & Logging: Log and audit every transfer (source, destination, volume and timestamps) for accountability and incident response.
Related Patterns
Use and cite this
Free to use and adapt under CC BY 4.0. Credit: David Tirabassi, IT Architecture Patterns.
Attribution: Source: David Tirabassi, IT Architecture Patterns (https://www.itarchitecturepatterns.net/patterns/int-etl-external), licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/).