AWS ELT Pipeline Patterns for Data Engineers

ELT — Extract, Load, Transform — flips the traditional ETL model on its head. Instead of transforming data before loading it into a warehouse, ELT loads raw data first and uses the warehouse's own compute power to transform it. On AWS, this pattern is especially powerful because services like Redshift, Athena, and Glue provide massive parallelism for in-database transformations.

This guide covers the core ELT concepts, walks through AWS-native ELT architectures, and prepares you for data engineering interviews.

ETL vs ELT: The Core Difference

Understanding the distinction between ETL and ELT is fundamental for any data engineer.

ETL (Extract, Transform, Load):

1. Extract data from sources
2. Transform it using an external engine (Spark, custom code)
3. Load the transformed result into the target warehouse

ELT (Extract, Load, Transform):

1. Extract data from sources
2. Load raw data directly into the warehouse or data lake
3. Transform it inside the warehouse using SQL

Why ELT is Gaining Popularity

ELT shines when you have powerful compute already sitting in your warehouse. Why shuttle data through an intermediate engine when the warehouse can do the heavy lifting?

SVG: ETL vs ELT Side-by-Side Comparison

The key insight: ELT eliminates the separate transform engine and pushes all transformation logic into the warehouse itself.

ELT Pipeline Architecture on AWS

An AWS-native ELT pipeline typically follows this flow:

1. Extract — Pull data from operational databases, APIs, or streaming sources
2. Load — Stage raw data in S3 or load directly into Redshift/Athena
3. Transform — Run SQL transformations inside the warehouse to produce curated datasets

Core AWS Services for ELT

The S3 Landing Zone Pattern

Most ELT pipelines on AWS begin with S3 as the landing zone. Data lands in S3 in its raw form (JSON, Parquet, CSV), and downstream services query or load from there.

s3://data-lake-raw/
├── orders/
│   └── dt=2026-01-15/
│       └── orders.json.gz
├── customers/
│   └── dt=2026-01-15/
│       └── customers.json.gz
└── products/
    └── dt=2026-01-15/
        └── products.json.gz

This partitioned structure enables efficient querying by date and entity.

SVG: ELT Pipeline Architecture (S3 → COPY → Redshift → SQL Transform)

The COPY Command: ELT's Best Friend

The COPY command is the backbone of Redshift-based ELT. It bulk-loads data from S3 in parallel across all nodes:

COPY staging.orders
FROM 's3://data-lake-raw/orders/dt=2026-01-15/'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftLoadRole'
FORMAT AS JSON 'auto'
GZIP
REGION 'us-east-1';

Key advantages of COPY:

• Parallel loading across all cluster nodes
• Compression detection with GZIP or ZSTD
• Manifest support for precise file control
• Error handling with MAXERROR and REJECTLIMIT

Redshift-Based ELT

Redshift is purpose-built for ELT workloads. Its massively parallel processing (MPP) architecture makes in-database transformations blazing fast.

Three-Layer Schema Pattern

The most common ELT pattern in Redshift uses a three-layer schema:

SVG: Redshift ELT Three-Layer Pattern

Staging Layer SQL

-- Staging: Load raw data with minimal transformation
CREATE TABLE staging.orders_stg (
    order_id VARCHAR(50),
    customer_id VARCHAR(50),
    order_total DECIMAL(12,2),
    order_date VARCHAR(30),
    raw_json TEXT
);

COPY staging.orders_stg
FROM 's3://data-lake-raw/orders/dt=2026-01-15/'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftRole'
FORMAT AS JSON 'auto'
GZIP;

Raw Layer SQL

-- Raw: Type cast, deduplicate, validate
INSERT INTO raw.orders
SELECT DISTINCT
    order_id::VARCHAR(50),
    customer_id::VARCHAR(50),
    order_total::DECIMAL(12,2),
    order_date::DATE,
    CURRENT_TIMESTAMP AS loaded_at
FROM staging.orders_stg
WHERE order_id IS NOT NULL
  AND order_total > 0;

Curated Layer SQL

-- Curated: Business logic and joins
INSERT INTO curated.order_summary
SELECT
    o.order_id,
    c.customer_name,
    c.segment,
    o.order_total,
    o.order_date,
    DATEDIFF(day, c.first_order_date, o.order_date) AS days_since_first_order,
    CASE
        WHEN o.order_total >= 1000 THEN 'Enterprise'
        WHEN o.order_total >= 200 THEN 'Mid-Market'
        ELSE 'SMB'
    END AS order_tier
FROM raw.orders o
JOIN raw.customers c ON o.customer_id = c.customer_id
WHERE o.order_date = CURRENT_DATE - 1;

Redshift ELT Best Practices

• Use SORTKEY and DISTKEY on frequently filtered/joined columns
• Vacuum and analyze after large loads to maintain query performance
• Use materialized views for commonly repeated transformations
• Leverage Redshift Spectrum to query S3 data directly without loading
• Monitor with SVV_TABLE_INFO to identify skew and optimize distribution

Athena-Based ELT

Athena brings ELT to the serverless world. Using CREATE TABLE AS SELECT (CTAS), you can transform data in S3 without provisioning any infrastructure.

The CTAS Pattern

Athena's CTAS statement is the foundation of serverless ELT:

CREATE TABLE curated.order_summary
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://data-lake-curated/order-summary/'
)
AS
SELECT
    o.order_id,
    c.customer_name,
    c.segment,
    o.order_total,
    o.order_date,
    CASE
        WHEN o.order_total >= 1000 THEN 'Enterprise'
        WHEN o.order_total >= 200 THEN 'Mid-Market'
        ELSE 'SMB'
    END AS order_tier
FROM raw.orders o
JOIN raw.customers c ON o.customer_id = c.customer_id;

Why Athena CTAS Works for ELT

SVG: Athena ELT with CTAS Pattern

Athena ELT Performance Tips

• Convert to Parquet/ORC as the first CTAS step — reduces scan costs by 90%+
• Partition output by date or entity for efficient downstream queries
• Use Athena workgroups to set query limits and cost controls
• Leverage federated queries to join S3 data with RDS/Redshift
• Catalog everything in Glue so other services can discover your curated data

Glue for ELT

AWS Glue bridges the gap between pure SQL ELT and code-based transformations. Glue ETL jobs can read from S3, transform using PySpark, and write back to S3 — all managed serverlessly.

When Glue Fits Into ELT

Glue is ideal when:

• Transformations are too complex for pure SQL (nested JSON flattening, complex aggregations)
• You need Python/Scala logic alongside SQL
• You want auto-scaling compute without managing clusters
• You need built-in data quality and catalog updates

Glue ELT Pattern

Glue ETL Job Example

import sys
from awsglue.transforms import *
from awsglue.context import GlueContext
from pyspark.context import SparkContext

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session

# Read raw orders from S3
orders_df = spark.read.parquet("s3://data-lake-raw/orders/")

# Transform: flatten, clean, aggregate
from pyspark.sql import functions as F

order_summary = (
    orders_df
    .withColumn("order_date", F.to_date("order_date"))
    .withColumn("order_tier",
        F.when(F.col("order_total") >= 1000, "Enterprise")
         .when(F.col("order_total") >= 200, "Mid-Market")
         .otherwise("SMB")
    )
    .groupBy("customer_id", "order_date", "order_tier")
    .agg(
        F.count("*").alias("order_count"),
        F.sum("order_total").alias("total_revenue"),
        F.avg("order_total").alias("avg_order_value")
    )
)

# Write curated output to S3
order_summary.write.mode("overwrite").parquet("s3://data-lake-curated/order-summary/")

# Update Glue Catalog
glueContext.create_dynamic_frame.from_options(
    connection_type="s3",
    connection_options={"paths": ["s3://data-lake-curated/order-summary/"}],
    format="parquet"
)

SVG: Glue ELT Patterns

When to Use ELT vs ETL

Choosing between ELT and ETL depends on your specific requirements. Here's a decision framework:

Choose ELT When:

• Warehouse has strong compute — Redshift, Snowflake, BigQuery can handle transforms efficiently
• Data volumes are large — Loading raw is faster than transforming in flight
• Schema flexibility needed — Keep all raw data, transform on demand
• Speed to insight matters — Load first, figure out transforms later
• Data history is important — Retain all historical snapshots in raw layer

Choose ETL When:

• Strict compliance — Data must be transformed/cleaned before entering warehouse
• Complex transformations — Multi-step pipelines with ML inference or geospatial processing
• Real-time requirements — Stream processing with Kinesis Data Analytics
• Legacy systems — Target warehouse lacks strong compute (e.g., small RDS instance)
• Data governance — Sensitive data must be masked/aggregated before storage

Hybrid Approach

Many production pipelines use both:

Source → S3 (raw) → Glue ETL (complex transforms) → S3 (processed) → Redshift COPY → SQL transforms → Curated

This combines the flexibility of ELT with the power of Glue for transformations too complex for SQL.

SVG: ELT vs ETL Decision Framework

Interview Q&A

Q1: What is the fundamental difference between ETL and ELT?

Answer: In ETL, data is transformed before loading into the target system using an external engine (like Spark). In ELT, raw data is loaded first into the target warehouse, and transformations happen inside the warehouse using its own compute power. ELT leverages the warehouse's MPP architecture for parallel transforms, while ETL relies on separate infrastructure.

Q2: Why would you choose ELT over ETL on AWS?

Answer: Choose ELT when:

• Your warehouse (Redshift) has sufficient compute for transforms
• You need to load data quickly without waiting for transform completion
• You want to retain raw data for historical reprocessing
• Schema flexibility is important (transform logic can change without re-extracting)
• You want to reduce infrastructure costs by eliminating a separate transform layer

Q3: Explain the three-layer schema pattern in Redshift ELT.

Answer: The three-layer pattern consists of:

1. Staging — Raw data loaded via COPY with no transformations. Schema matches the source exactly. Fastest load possible.
2. Raw — Data is type-cast, deduplicated, and validated. Basic cleaning applied. Still contains all original fields.
3. Curated — Business logic applied: joins, aggregations, calculated fields. This is the consumption layer for BI and ML.

Each layer is populated by SQL INSERT statements that read from the previous layer.

Q4: How does Athena CTAS enable ELT without any infrastructure?

Answer: Athena's CREATE TABLE AS SELECT (CTAS) reads data from S3, applies SQL transformations, and writes the output back to S3 in an optimized format (Parquet/ORC) with automatic compression. Since Athena is serverless, there are no clusters to provision. You pay only for the data scanned. The output is automatically registered in the Glue Data Catalog for other services to discover.

Q5: What are the performance considerations for Redshift COPY in ELT?

Answer:

• Use compressed formats (GZIP, ZSTD) to reduce data transfer and storage
• Set appropriate COPY options: REGION, IAM_ROLE, FORMAT
• Load in parallel by splitting large files into multiple smaller files (aim for 1MB-1GB per file)
• Use manifest files when loading specific files from S3
• Set MAXERROR appropriately to handle minor data quality issues
• Run VACUUM and ANALYZE after large loads to optimize query performance
• Monitor load errors with STL_LOAD_ERRORS

Q6: When would you use Glue ETL instead of pure SQL transforms in Redshift?

Answer: Use Glue ETL when:

• Transformations require Python/Scala logic (regex parsing, custom algorithms)
• You need to flatten deeply nested JSON structures
• Data quality rules are too complex for SQL CASE statements
• You need to call external APIs during transformation
• You want to leverage ML libraries (SparkML) for feature engineering
• You need auto-scaling compute without managing Redshift cluster capacity

Q7: How do you handle schema evolution in an ELT pipeline?

Answer:

• In staging layer: Use COPY with jsonpaths or auto to handle new fields gracefully
• In Redshift: Use ALTER TABLE ADD COLUMN for new fields, or CREATE OR REPLACE VIEW for flexible schemas
• In Athena: CTAS creates a new table, so schema changes are natural. Use MSCK REPAIR TABLE for partition discovery
• In Glue: Use ResolveChoice transform to handle ambiguous schemas, or ApplyMapping to enforce a target schema
• General: Always version your schemas and use the Glue Catalog to track schema history

Q8: How do you orchestrate an ELT pipeline on AWS?

Answer: Common orchestration patterns:

• AWS Step Functions — Visual workflow with state machines, built-in error handling
• MWAA (Managed Airflow) — Full DAG orchestration with rich scheduling
• EventBridge + Lambda — Event-driven triggers for simple pipelines
• Glue Workflows — Native orchestration for Glue jobs and crawlers

The typical flow: EventBridge triggers Step Functions → Lambda runs COPY commands → Redshift executes transform SQL → SNS notifies on completion.

Q9: What is the role of the Glue Data Catalog in ELT?

Answer: The Glue Data Catalog serves as a central metadata repository that:

• Stores table definitions, schemas, and partition information
• Enables Athena to query S3 data with SQL
• Allows Redshift Spectrum to access S3 data directly
• Provides data discovery for analysts via Lake Formation
• Tracks data lineage and quality metrics
• Auto-discovers schemas through crawlers

It's the glue (pun intended) that connects your raw, curated, and consumption layers.

Q10: Design an ELT pipeline for a real-time e-commerce platform.

Answer:

Order Events (Kinesis) → S3 (raw, partitioned by hour)
    ↓
Glue Crawler (auto-detect schema)
    ↓
Athena CTAS → S3 (curated, Parquet, partitioned by date)
    ↓
Redshift COPY (from curated S3)
    ↓
Redshift SQL transforms → curated tables
    ↓
QuickSight dashboards (near-real-time analytics)

Key decisions:

• Kinesis Firehose buffers streaming data to S3 (1-minute intervals)
• Glue Crawler auto-discovers new fields in the JSON events
• Athena CTAS converts JSON to Parquet and applies initial transforms
• Redshift COPY loads from curated Parquet for complex joins
• Total latency: ~5-10 minutes from event to dashboard

Summary

This topic covered the key concepts of AWS data engineering. Review the architecture diagrams, practice the interview questions, and understand the trade-offs between different service options.

Next Steps

Continue to the next topic to build on your AWS data engineering knowledge.

Knowledge Check

See Also

• AWS Redshift
• ETL Pipeline
• AWS Glue