Integration ETL / Batch Processing (Internal)
Problem
One internal system needs bulk data from another on a regular schedule, and that data changes rarely. Both ends sit in the same organisation's Private Subnet (Workloads), so the transfer never touches the public internet, and the integration service reaches each store with a least-privilege managed identity or service account. Ad hoc or row-by-row movement overloads both systems, breaks consistency and fails to scale as volumes grow.
Solution
Build a pipeline on a Managed Data Integration Service that extracts, transforms and loads (ETL) bulk datasets between internal workloads or data stores. It runs on a schedule or when new data arrives, keeping data consistent across systems. Transformation steps run as serverless functions or containerised workloads in the Private Subnet (Workloads), which scales with volume and keeps cost down.
Cloud Paradigm
- Data Pipelining
- Batch Processing
- Serverless Compute (for transformation tasks)
- Event-Driven Architectures (for triggering)
- Data Lake / Data Mesh integration readiness
- Idempotent Data Ingestion
- Cost-Optimised Data Movement
Solution Flow
Data Ingestion Flow:
- Source Backend Workload/Data Store: The source system (e.g., a database, object storage, file share) provides the data through secure data interfaces (e.g., database connections, API endpoints, secure file transfer protocols) in a Private Subnet (Workloads).
- Managed Data Integration Service: A schedule or event (e.g., a new file arriving, a time-based schedule) starts a job in the Managed Data Integration Service, which also runs in a Private Subnet (Workloads).
- Data Extraction: The service connects securely to the source, extracts the bulk data and usually stages it in an intermediate store (e.g., object storage or a temporary database).
- Data Transformation (Optional/Conditional): The data is transformed as needed: cleansing, enrichment, format conversion, aggregation or joins with other datasets. Serverless functions or containerised compute instances can run these steps.
- Data Loading: The transformed data is loaded securely into the target workload or data store in a Private Subnet (Workloads), by direct database inserts or updates, bulk file uploads to object storage, or message queue publication.
- Target Backend Workload/Data Store: The target system stores the ingested data and makes it available for consumption.
When to Use
- Bulk datasets must move periodically between internal backend systems, and the source changes rarely enough that continuous streaming would be wasteful.
- The workload can accept batch latency (minutes to hours) and does not need near-real-time propagation.
- The data needs real transformation (cleansing, enrichment, format conversion or joins) before the target store can use it.
- Source and target both sit in a Private Subnet (Workloads) and can be reached over secure data interfaces with no public exposure.
- Volume varies a lot between runs, so elastic serverless or containerised compute costs less than always-on infrastructure.
When NOT to Use
- Consumers need sub-second or event-by-event freshness. Use a streaming or change-data-capture pipeline instead.
- The interaction is a request/response exchange between services, not bulk movement. An API or synchronous integration pattern fits better.
- Volumes are small and change often, so per-record eventing is simpler and cheaper than scheduled batches.
- The transfer crosses a trust boundary to an external partner. An externally facing, gateway-mediated integration pattern is more appropriate.
- Source and target share the same database engine, and a native replication feature already meets the need.
Trade-offs
- Efficient, low-cost bulk movement vs batch latency that leaves the target stale between scheduled runs.
- Elastic serverless or containerised compute vs the operational complexity of orchestration, checkpointing and dead-letter handling.
- Central transformation and data quality enforcement vs more pipeline logic and metadata to maintain as code.
- Decoupled source and target schemas vs the risk of silent drift when either side changes and the pipeline does not.
- Strong observability and lineage vs the overhead of instrumenting metrics, logs and catalogue entries at every stage.
Illustrative Scenario
A hypothetical scenario showing how the pattern applies. Not a client case study.
A hypothetical pharmaceutical manufacturer consolidates batch-release records each night from a manufacturing execution database that barely changes during a production shift. A time-based trigger starts a job in the Managed Data Integration Service in the Private Subnet (Workloads). The job connects to the source database over TLS, extracts the shift's batch genealogy and quality-test results, and stages them as Parquet files in object storage for processing. A containerised transformation step then standardises lot identifiers, enriches the records with reference master data on materials and equipment, and aggregates yield metrics. Finally, it bulk-loads the transformed output into the quality-analytics warehouse. Checkpointing and a dead-letter queue hold malformed batch records so they can be reprocessed and audited. Job duration and row counts flow into the central observability platform. Together these give validation and operations teams the lineage they need for regulatory traceability, and no part of the transfer leaves the private network.
Additional Details
- Orchestration & Scheduling: Use cloud-native schedulers or workflow orchestrators to run the pipelines. Jobs can be time-based, event-driven or started manually.
- Resilience & Error Handling: Build in fault tolerance: checkpointing, retries and dead-letter queues for failed records. Log and alert on operational issues.
- Scalability: Run transformation steps on serverless or containerised compute that scales with data volume and processing needs.
- Data Governance: Keep a data catalogue and metadata that track lineage, check data quality and support compliance for the ingested datasets.
- Data Formats & Protocols: Use formats suited to bulk processing (e.g., Parquet, ORC, Avro, CSV, JSON lines). TLS 1.2+ is mandatory for all data transfer.
- Observability: Send pipeline logs, metrics (e.g., data volume processed, job duration, error counts) and lineage to a central observability platform to watch performance and health.
- Infrastructure as Code (IaC): Manage the whole pipeline (connectivity, compute, orchestration logic) as code for version control, automation and consistent deployments.
Security Controls
- Network Segmentation: Deploy data integration components and data stores in isolated Private Subnets (Workloads) with no direct Public Internet access, and open only the ports and protocols needed between source, integration service and target.
- Transport Security: Enforce Transport Layer Security (TLS) 1.2 or higher for all data in transit between source, processing components and target.
- Data Encryption at Rest: Encrypt all data stores, including transient staging areas, with platform-managed or customer-managed keys.
- Authentication & Authorisation:
- Give the data integration service a managed identity or service account with least privilege on the source and target stores.
- Use role-based access control (RBAC) for pipeline operators and data consumers.
- Credential Management: Never store credentials in cleartext. Keep database credentials, API keys and other access tokens in a Managed Secrets Management Service.
- Data Validation & Integrity: Validate data during transformation, so corrupted or malicious data does not reach target systems.
- Audit Logging: Log all pipeline activity (data access, transformations, load events) to a central logging and monitoring service.
- Data Loss Prevention (DLP): For highly sensitive data, run DLP scans in the transformation pipeline to stop unauthorised egress or inappropriate storage.
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-internal), licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/).