Case study · Data pipeline optimisation

Simplifying an AWS data pipeline

We replaced a pipeline that repeatedly processed the full dataset with DMS change data capture and SQL transformations in Redshift. Airflow coordinated the workflow, and Terraform managed the infrastructure.

Focus: Data engineering · ETL to ELT
Platform: AWS · Redshift · Amazon MWAA
Infrastructure: Terraform

Before / Full dataset each run Transform across S3 layers

RDS → DMS → S3 CSV files

EventBridge → Lambda & Glue / Spark

S3 Bronze → Silver → Gold

Split by company_id → Redshift

Each run took around two hours.

After / Full load, then CDC Transform inside Redshift

RDS → DMS → Redshift

Amazon MWAA / Airflow → SQL transformations

Business-ready datasets per company

S3 exports where needed

Main workflow scheduled every hour.

The architectural shift

DMS and SQL transformations in Redshift

Preserve business rules and company-specific datasets while reducing processing stages and repeated transfers.

Incremental ingestion

DMS initial full load followed by ongoing change data capture

Central orchestration

Airflow tasks coordinate SQL transformations inside Redshift

Managed infrastructure

Terraform defines and manages the AWS resources

Conceptual architecture · From layered S3 processing to transformations inside Redshift

01 The context

Why the pipeline needed to change

The client’s pipeline was slow and expensive. They needed a lower-cost solution without increasing processing time or disrupting the data already delivered to Redshift.

The original pipeline used AWS DMS to copy data from an RDS database into CSV files in S3. EventBridge triggered Lambda functions and AWS Glue jobs to process the data with Spark.

Data moved through Bronze, Silver, and Gold layers in separate S3 buckets. Further jobs split the results by company_id and loaded them into Redshift. Each run processed the full dataset and took around two hours.

The challenge was to preserve the existing business rules and company-specific datasets, keep delivery reliable, and manage the infrastructure with Terraform.

02 The approach

Moving the work into Redshift

01 / Ingest

Full load, then changes

We changed the DMS target to Redshift and configured an initial full load followed by ongoing change data capture (CDC). Later updates could use database changes instead of repeatedly copying the full dataset.

02 / Orchestrate

Orchestration with Airflow

We set up Amazon MWAA, AWS’s managed Apache Airflow service, and moved orchestration into Airflow tasks. The main workflow runs every hour to apply the business rules and prepare separate datasets for each company.

03 / Transform

SQL inside Redshift

We rewrote the Glue and Spark transformations as SQL that runs inside Redshift. The new pipeline produces the business-ready datasets previously built through the Gold layer, with the main transformations running in the warehouse.

03 Infrastructure & continuity

Keeping the existing outputs

Datasets for each company

The workflow continues to apply the existing business rules and prepare separate datasets for each company. Moving the transformations into Redshift changed where processing happens while retaining the required business-ready outputs.

Infrastructure defined in code

We used Terraform to define and manage the AWS infrastructure, supporting infrastructure automation alongside the pipeline changes.

Exports where they are needed

S3 exports remain available where needed, while the main transformations run inside Redshift rather than moving data repeatedly between S3 layers.

04 The outcome

What changed

The new pipeline produces the datasets previously built through the Gold layer, with the main transformations running inside Redshift.

The new architecture reduced the need for Glue and Spark jobs and repeated transfers between S3 layers, helping lower processing costs and simplify maintenance.

Ongoing CDC supports updates from database changes, and Airflow coordinates the main transformations on an hourly schedule.

05 Technology & delivery

Technologies used

Architecture: Data pipeline design, ETL/ELT, Change Data Capture (CDC)
Data engineering: Data transformation, SQL, company-specific datasets
Infrastructure as Code: Terraform
DevOps: CI/CD, Semaphore CI, infrastructure automation
AWS: RDS, DMS, Redshift, S3, MWAA, IAM, Lambda, CloudWatch
Programming: Python, SQL
Orchestration: Apache Airflow, Amazon MWAA
Testing: Pytest, data validation, end-to-end testing

← Back to work