# Extract, Transform, Load

Extract, Transform, Load is a data integration process that retrieves data from source systems, prepares it for a target use case, and writes it to a destination.

Cloud concept. Updated 2026-10-29. Page: https://blowstack.com/tools/cloud-concepts-hub/extract-transform-load

> ETL turns data from databases, files, APIs, and document repositories into consistent, usable datasets. In the cloud, it supports analytics, operational integration, and generative AI retrieval pipelines by separating source access, transformation logic, and destination loading.

Practice exams covering Extract, Transform, Load: [AIP-C01 AWS Certified Generative AI Developer - Professional](https://blowstack.com/practice-exams/aws-certified-generative-ai-developer-professional), [SAA-C03 AWS Certified Solutions Architect - Associate](https://blowstack.com/practice-exams/aws-certified-solutions-architect-associate), [SAP-C03 AWS Certified Solutions Architect - Professional](https://blowstack.com/practice-exams/aws-certified-solutions-architect-professional).

## 6 key aspects of Extract, Transform, Load

Effective ETL combines reliable extraction, use-case-specific transformation, compatible loading, recoverability, performance tuning, and end-to-end governance.

1. **Source-aware extraction**: Connectors must handle authentication, pagination, source limits, and incremental change tracking. Include a strategy for detecting deletions and permission changes, especially when ingesting enterprise documents.
2. **Purpose-driven transformation**: Prepare data for the destination rather than merely changing its format. Analytics may require joins and normalized types, while retrieval pipelines require parsing, chunking, metadata enrichment, and embeddings.
3. **Destination-compatible loading**: Match the destination's schema, indexing rules, and write capacity. For vector stores, validate embedding dimensions and use stable identifiers so updates replace the correct records.
4. **Recoverable, repeatable processing**: Use checkpoints, bounded retries, and idempotent writes so failed work can be replayed safely. Quarantine invalid records with enough context to investigate without exposing sensitive data.
5. **Throughput and freshness trade-offs**: Choose batch or continuous processing based on acceptable data lag and workload characteristics. Tune parallelism, file sizes, and write batches while respecting source and destination limits.
6. **Security and data lineage**: Preserve provenance and access-control information throughout processing. Enforce least-privilege access, protect intermediate datasets, and propagate deletion requirements to derived records.

## Cloud services for Extract, Transform, Load

A typical implementation uses Lambda to read document APIs, S3 to retain extracted content, and Step Functions to coordinate processing and recovery. Glue handles large-scale cleansing, joins, and format conversion when distributed processing is needed. For retrieval applications, a supported Knowledge Bases for Amazon Bedrock workflow can manage document preparation and embedding, with a compatible vector destination such as OpenSearch Service. These are composable options, not mandatory stages: select services according to data volume, connector support, freshness, and transformation complexity. Preserve identifiers, authorization metadata, and deletion tracking across whichever components the pipeline uses.

- **[AWS Glue](https://blowstack.com/tools/cloud-services-hub/aws-glue)**: AWS Glue provides managed data integration and transformation capabilities, including distributed Spark ETL jobs for large datasets. Crawlers and the Data Catalog help discover and describe data, but cataloging alone does not transform it; supported job bookmarks track processing progress rather than guarantee end-to-end exactly-once delivery.
- **[AWS Lambda](https://blowstack.com/tools/cloud-services-hub/aws-lambda)**: AWS Lambda can implement lightweight source connectors, document preprocessing, and event-driven loading logic. It suits bounded tasks; long-running extraction or large distributed transformations require a different compute approach.
- **[AWS Step Functions](https://blowstack.com/tools/cloud-services-hub/aws-step-functions)**: AWS Step Functions orchestrates extraction, transformation, and loading tasks with dependencies, retries, and failure handling. It coordinates processing services rather than acting as a distributed transformation engine.
- **[Amazon Bedrock](https://blowstack.com/tools/cloud-services-hub/amazon-bedrock)**: Knowledge Bases for Amazon Bedrock provides managed ingestion workflows that can parse, chunk, embed, and index supported content for retrieval. Connector capabilities vary, so validate source support, synchronization behavior, metadata handling, and authorization requirements.
- **[Amazon S3](https://blowstack.com/tools/cloud-services-hub/amazon-s3)**: Amazon S3 commonly stores extracted source snapshots, intermediate results, and curated ETL outputs. Durable landing data supports replay and separates source availability from downstream processing, but S3 itself does not execute transformation logic.
- **[Amazon OpenSearch Service](https://blowstack.com/tools/cloud-services-hub/amazon-opensearch-service)**: Amazon OpenSearch Service can serve as a destination for transformed documents and vector embeddings. ETL writers must account for index mappings, vector configuration, indexing throughput, and consistent update and deletion behavior.

## Extract, Transform, Load exam questions

Real practice exam questions on Extract, Transform, Load, with the correct answer and its explanation.

### Question 1 (SAA-C03)

**Question:**

A financial services company operates an Amazon EKS cluster with established namespace isolation, pod admission policies, and automated worker-node scaling. The company needs to run Apache Spark jobs that deduplicate transaction records, join them with reference data, and write curated datasets to Amazon S3. Security policy requires all transformation compute to run as pods in a designated namespace of the existing EKS cluster so that the cluster's admission policies apply. The company wants to minimize the effort required to configure and maintain the Spark runtime.

Which solution best meets these requirements?

**Correct answer:** Create an Amazon EMR virtual cluster associated with the designated EKS namespace. Submit the Spark jobs through Amazon EMR on EKS and configure their execution role for access to the S3 datasets.

**Explanation:** Amazon EMR on EKS runs Spark workloads as pods in an existing EKS cluster. An EMR virtual cluster maps to an EKS namespace, allowing the company to use its existing Kubernetes controls while using an AWS-managed Spark runtime.

### Question 2 (SAP-C03)

**Question:**

A multinational company stores immutable invoice files in Amazon S3, partitioned by invoice date. An AWS Glue Spark job uses job bookmarks to process newly arrived files, joins invoices with an exchange-rate reference dataset, and writes a curated Parquet dataset partitioned by invoice date. Some invoices arrive up to 30 days after their invoice date.

The finance team occasionally corrects exchange rates for specified invoice dates. A correction must update every previously processed invoice for those dates, including invoices in source files that have not changed. The company retains all source files. During correction processing, the normal ingestion job is paused. The correction process must read only the affected invoice-date partitions, preserve unaffected curated partitions, and produce no duplicate invoices if operators rerun it. Each affected partition can contain hundreds of gigabytes of data.

Which solution meets these requirements?

**Correct answer:** Use a separate Glue Spark correction job with bookmarks disabled. Select all raw files in the affected invoice-date partitions, join them with the corrected exchange rates, and replace only the corresponding curated partitions with the complete recalculated results.

**Explanation:** The correction must re-extract previously processed invoices because their derived values depend on reference data that changed. Disabling bookmarks for this separate job allows all files in the selected partitions to be read without disturbing the normal job's bookmark state. Distributed processing supports the data volume, and replacing the complete affected output partitions preserves unrelated data and prevents duplicates on reruns.

### Question 3 (AIP-C01)

**Question:**

A manufacturer exports approved maintenance-wiki pages to Amazon S3 for ingestion into an Amazon Bedrock knowledge base. Each page describes several equipment models. An existing flat chunking configuration reliably produces passages that each contain exactly one equipment code. An internal registry API maps each equipment code to its authoritative service region; a page can contain equipment assigned to different regions.

The assistant must filter retrieved passages by service region before supplying them to a foundation model. The region must be obtained from the registry during ingestion and attached only to the applicable passages. The company wants to retain managed chunking, embedding generation, and vector-store updates. An AWS Lambda function can access the registry, and each transformation batch fits within Lambda execution limits.

Which solution best meets these requirements?

**Correct answer:** Configure a POST_CHUNKING custom transformation Lambda function on the knowledge base data source with an intermediate S3 location. Have the function read the referenced chunk batches, resolve each chunk's equipment code through the registry, write transformed batches with chunk-level service-region metadata to S3, and return their references.

**Explanation:** A custom transformation runs after chunking and before embedding generation. Its Lambda function can read chunk batches from the intermediate S3 location, enrich individual chunks using an external API, and return references to transformed batches. Bedrock then generates embeddings and updates the vector store. The service-region metadata can be used in retrieval filters.

## Practice Extract, Transform, Load in a full exam

Questions on Extract, Transform, Load in each practice exam, with every answer option and an explanation for each:

- [SAA-C03](https://blowstack.com/practice-exams/aws-certified-solutions-architect-associate): 5 questions
- [SAP-C03](https://blowstack.com/practice-exams/aws-certified-solutions-architect-professional): 3 questions
- [AIP-C01](https://blowstack.com/practice-exams/aws-certified-generative-ai-developer-professional): 3 questions

## What is Extract, Transform, Load

Extract, Transform, Load, usually abbreviated ETL, describes three logical stages of data integration. Extraction reads data from sources such as transactional databases, object storage, document management systems, and internal wikis. Transformation cleans, validates, standardizes, joins, enriches, or restructures that data. Loading writes the prepared results into a destination such as a data warehouse, curated data lake, search index, or vector store. The stages describe responsibilities, not necessarily three separate services or programs.

A cloud ETL pipeline commonly uses durable object storage as a landing area, managed compute for transformation, and an orchestrator to coordinate dependencies and recovery. For example, a nightly sales pipeline can extract database changes, normalize timestamps and currencies, join transactions with customer records, and load partitioned Parquet files into a curated dataset. Distributed processing is appropriate for large joins and aggregations, while short API-driven transformations may fit event-driven functions. Adding more workers does not resolve every bottleneck: source throughput, network transfer, data skew, and destination write limits can dominate execution time.

For generative AI, ETL also prepares unstructured content for retrieval-augmented generation. An integration component authenticates to a document repository or wiki, follows pagination, respects API rate limits, and tracks changed documents. Transformations can parse documents, remove navigation text, redact sensitive information, attach source metadata, divide text into chunks, and generate embeddings. The load stage writes vectors and associated text or references into a compatible vector store. The embedding output dimensions must match the target index configuration, and queries must use a compatible embedding model.

Document integration must preserve more than text. Retain stable document identifiers, versions, provenance, and access-control metadata. Apply authorization during retrieval so that indexing a restricted document does not make it available to unauthorized users. Synchronize deletions and permission changes as well as additions. If chunking rules or embedding models change, plan for reprocessing and potentially rebuilding the index; updating source documents alone does not automatically update stored vectors.

Choose batch or continuous processing according to freshness requirements and operating cost. Batch jobs often improve throughput through grouped reads and writes, but introduce scheduling delay. Streaming transformations can reduce latency while adding complexity around ordering, late events, checkpoints, and recovery. Incremental extraction reduces repeated work, but its correctness depends on reliable change tracking. A modification timestamp alone may miss deletions or changes that occur near a checkpoint boundary.

Reliable ETL uses validation rules, rejected-record handling, retries with backoff, and idempotent writes. Stable keys and upserts prevent a retried load from creating duplicate records. Record counts, processing lag, failure rates, and data-quality checks help detect silent data loss. Encrypt data in transit and at rest, limit connector permissions, and keep credentials out of transformation code and logs.

ETL is not synonymous with copying data, and it does not have to run only as a nightly batch. It also differs from ELT, where data is loaded into the analytical destination before transformations execute there. Landing raw files in temporary storage does not necessarily make a pipeline ELT; the important distinction is where transformation occurs relative to loading the intended target. Choose the pattern according to transformation complexity, governance requirements, destination capabilities, and cost rather than treating either ordering as universally better.

## Learn Extract, Transform, Load in lessons

4 short lessons on Extract, Transform, Load in 3 certification courses.

- SAA-C03: Extract, Transform, Load: transformation processing models (Premium), https://app.blowstack.com/lessons/extract-transform-load-transformation-processing-models-saa-c03
- SAP-C03: Extract, Transform, Load: serverless pipeline design (Premium), https://app.blowstack.com/lessons/extract-transform-load-serverless-pipeline-design-sap-c03
- AIP-C01: Extract, Transform, Load: incremental document ingestion for vector stores (Premium), https://app.blowstack.com/lessons/extract-transform-load-incremental-document-ingestion-for-vector-stores-aip-c01
- SAP-C03: Extract, Transform, Load vs. Event Streaming (Premium), https://app.blowstack.com/lessons/extract-transform-load-vs-event-streaming-sap-c03
