Dimensional Modeling and ETL in Redshift

Question

I have been researching Amazon's Redshift database as a possible future replacement for our data warehouse. My experience has always been in using dimensional modeling and Ralph Kimball's methods, so it was a little weird to see that Redshift doesn't support features such as the serial data type for auto incrementing columns.

There is, however, this recent blog post from the AWS Big Data blog about how to optimize Redshift for a star schema: https://blogs.aws.amazon.com/bigdata/post/Tx1WZP38ERPGK5K/Optimizing-for-Star-Schemas-and-Interleaved-Sorting-on-Amazon-Redshift

The question I have is about what is the best practice for loading a star schema in Redshift? I cannot find this answered in any of Redshift's documentation.

I'm leaning toward importing my files from S3 into staging tables and then using SQL to do the transformations such as lookups and generating surrogate keys before inserting into the destination tables.

Is this what others are currently doing? Is there an ETL tool worth the money to make this easier?

Answer 1

You are definately on the right track with Kimball rather than inmon for Redshift.

There are a number of patterns for this, I have used them all in different use cases

1. "ELT" pattern - Load the source tables to redshift fully, do not do any significant transformations until the data has been loaded. For this you can either load to s3, then use redshift copy command or I would recommend using "AWS data migration services", which can sync a source (e.g.mysql or postgres) to a target (e.g. redshift) Then, on a regular basis run sql processes within redshift to populate dims then facts. You can use third part cloud based tools to "simplify" this process if you want to - such as Matillion (i do not recommend using a third party tool)
2. "ETL pattern" - Transform the data in flight, using apache spark. and load the dims and facts into redshift spark->s3->redshift. I have used EMR for this which is good. this is also the approach taken if you use AWS Glue
3. Do not transform ! - similar to 1) but just use the tables that have been loaded.

Note that Redshift sometimes works BETTER if you have a wide table with repeated values rather than a fact and dimensions. The reason for this is that the columnar approach lets Redshift compress the different values down to a level that is pretty efficient. I do not have a formula for when to use many Dimensions vs a flat wide table, the only way is to try it and see!

Some links

AWS DMS for Redshift taret

AWS Glue

Answer 2

For ETL there's AWS Glue. It is a managed, serverless ETL service that loads to Redshift (among other things).

https://aws.amazon.com/glue/

Answer 3

I am currently dealing with a similar task. It is to build ETL process and design dimensional model. I have researched a lot for the best way to deal with it and found an amazing helpful source of techniques we should definitely apply when working with MPP.

To answer the question

The question I have is about what is the best practice for loading a star schema in Redshift?

be sure to take a look into this resource. I bet you will find it incredibly helpful. It is a ~35 page document with powerful techniques to leverage the use of MPP columnar stores. It supports the comments you see like

Note that Redshift sometimes works BETTER if you have a wide table with repeated values rather than a fact and dimensions. The reason for this is that the columnar approach lets Redshift compress the different values down to a level that is pretty efficient. I do not have a formula for when to use many Dimensions vs a flat wide table, the only way is to try it and see!

comment by Jon Scott

Hope you find it as useful as I do

Answer 4

I think loading from S3 is a common pattern.

We needed to enforce uniqueness constraints so we chose to write to Postgres and then replicate new data to redshift every 10 minutes.

We use https://github.com/uswitch/blueshift to load into Redshift.

Answer 5

Since Redshift is a columnar database, storage and query performance will be different than RDBMS models. Optimizing for a columnar database is also different. Because there is usually less disk I/O and less data loaded from disk then queries are faster.

In terms of the AWS blog post you reference, I take it you have looked at those recommendations and considered which options work best for your data for distribution, keys, cursors, workload management, etc. and have at least a good idea of the approach you would use. I find it easier to work with a visual representation, you might consider a quick and dirty DB diagram showing how your existing tables would migrate to Redshift. Covering the major ones to get a feel for how much data is going where. And I would certainly use the ODBC/JDBC drivers from Amazon, loading large amounts of data can be troublesome in any case, much less moving to a different DB type.

As far as ETL/ELT, there is AWS Glue as other posters have mentioned. And yes, there are a number of tools, some of which are free. Amazon does have a DB Best Practices Guide, that might help you, too. One tip I've seen in other forums is to load your data as raw as possible and do the transformations in Redshift. That would lead you to an ELT process. With so many options, perhaps looking at a comparison of the 2 methods would help. Here's a blog article from Panopoly explaining the differences, it might help you decide on a path.

Answer 6

Amazon has recently published some best practices for ETL in Redshift

https://aws.amazon.com/blogs/big-data/top-8-best-practices-for-high-performance-etl-processing-using-amazon-redshift/

In a presentation on this topic Tony Gibbs, AWS Solution Architect recommends the following pattern for UPSERT style loads:

1. Load CSV data (from S3) in staging table
2. Delete matching rows from prd table

3. Insert data from stage

   BEGIN;
   CREATE TEMP TABLE staging(LIKE …); — copies dist keys
   copy staging from ’s3://… COMPUTE OFF;
   DELETE deep_dive d
   USING staging s WHERE d.aid = s.aid;
   INSERT INTO deep_dive SELECT * FROM staging
   DROP table staging;
   COMMIT;
   

When possible prefer DROP TABLE or TRUNCATE to DELETE to avoid ghost rows

See a video of his talk and the slides.

On our team, we typically load data into Redshift directly from S3 using the SQL COPY statement.

And manage all our ETL using the excellent Apache Airflow tool.

We also use integration services like Stich that write directly into Redshift, and then use CREATE TABLE LIKE and SELECT INTO to move the data into another schema.