All projects E-commerce marketplace · 400 employees

Oracle Data Warehouse to AWS Redshift

AbeBooks (an Amazon subsidiary)

Migrating a marketplace off Oracle, PL/SQL and Crystal Reports onto Redshift and Tableau.

Amazon Redshift Matillion ETL Tableau AWS EMR Apache Spark DynamoDB Streams Amazon Kinesis Amazon S3

The team

  • 2 Oracle DBAs (also supporting the back-end OLTP)
  • 1 Software Engineering Manager
  • 1 Data Engineer

Timeline

~8 months for the warehouse and ETL pipelines with a 3-person team, plus another 4 months to move reporting onto Tableau Server.

Starting state

  • Oracle data warehouse
  • PL/SQL transformations scheduled with cron
  • Excel and Crystal Reports for reporting

Use cases

  • Sales and financial reporting
  • Financial reconciliation
  • Marketing analytics — attribution model, channel performance, user segmentation
  • Product and category analytics

Architecture

AWS architecture: Postgres and social media APIs into Matillion on EC2, Redshift, and Tableau Server behind a load balancer
The migration target: Redshift, Matillion ETL and Tableau Server on AWS.
Extended AWS architecture adding Kinesis Firehose, Elastic MapReduce, Spark, Redshift Spectrum, SageMaker and Athena
The later build-out: EMR and Spark for web logs, DynamoDB Streams for inventory CDC, and Redshift Spectrum over the S3 data lake.

What was built

  • Amazon Redshift as the data warehouse
  • Matillion ETL running on EC2 for transformations
  • Tableau Server for reporting, behind an application load balancer
  • SQS, SNS and Python 3 in the service layer
  • Later: EMR and Spark to clear a Redshift ETL performance bottleneck
  • Later: DynamoDB Streams into Kinesis to deliver inventory CDC to S3

What changed

  • Reporting moved off Excel and Crystal Reports onto a governed Tableau Server deployment.
  • ETL moved off PL/SQL and cron onto a managed tool with scheduling and monitoring.
  • Adding EMR and Spark removed the Redshift ETL bottleneck rather than paying for a larger cluster.
  • Inventory changes reached the lake continuously through DynamoDB Streams instead of batch extracts.
Related service

Cloud Data Platforms

Architect and build cloud data platforms that scale to petabytes.

Explore service

Let's talk about your data platform

Tell us what you are working on. We reply within one business day, and we will tell you plainly if we are not the right fit.

Start a conversation Email us

Other projects

Consumer technology · internal data products

Product Analytics for a Voice Assistant

Amazon Alexa — Natural Language Understanding

Replacing manual CSV extracts with an automated Redshift and Tableau product analytics stack.

Amazon Redshift Redshift Spectrum Tableau Amazon SageMaker +1 more
Read the project E-commerce · machine learning

Feature Engineering for Machine Learning

Amazon Retail

Building ML feature pipelines from a core data lake — with no warehouse, no data model and no dashboards.

Apache Spark Scala AWS EMR Amazon S3 +3 more
Read the project Video games · player analytics

Delta Lake for Gaming Analytics

Microsoft Xbox — The Coalition

Moving a game studio off on-premise SQL Server and Hive onto an Azure Databricks lakehouse.

Azure Databricks Delta Lake Power BI Azure Data Factory +3 more
Read the project