I'm introducing a Redshift for my team and it becomes a challenge to make a solid development process, so I need some info how to deal with it.

First, of all some info about current layout. We are using 3 envs for development (LOCAL env of every developer, TEST env, PROD) and via are using Postgres as the main database. For DDL changes, we are creating sql scripts for an incremental update and they applied in automatic mode for every env during deploy. It solid and straightforward.

Now, we need to make some analytic reports and Postgres not good with it at all (performance problems). So we are moving to make regular upload of data to Redshift. As data transfer instrument at the current moment, we are using AWS DataPipeline, but it is a challenge to create a solid process. So I'm looking for an advice:

  1. Data to redshift is coming from Postgres via data transfer jobs (AWS DataPipeline), next stage of jobs - jobs that filter and repack data suitable for reports. It is very difficult to maintain data the same on the TEST and PROD envs: you need to create all the pipelines in 2 copies (or more in case you have dev Redshift in a cloud) and be sure that you run them all in the same order in a proper time to get consistent data. Maybe AWS Data pipeline is not the best tool for it? Or there is a way to track data transfer job in some solid way, to be sure they are working against two envs in the same way?

  2. There is also a problem with data transfer jobs - they are cannot be run locally (AWS DatapipeLine is cloud-based), so you spend lot of time starting it directly against TEST env in a cloud, which also could lead to broken data on a TEST env, so you have to recreate database from a dump (also waste of time). Are there any solutions to develop and debug such kind of jobs (it can be related not only to DataPipeline tool) locally?

  3. AWS Data pipeline are created via GUI, so there is a chance to create 2 different jobs for different envs, but there is an opportunity to create a job from JSON config file - so it can be stored as a code. Any best practices how to organise versions of for creating data pipelines during CI process? Recreate jobs on every deploy? Any suitable tool for this or maybe bash script?

  1. You could use AWS Glue to do the ETL. Experiences I have heard with AWS ETL are that Glue is for ETL, Data Pipelines for simple tasks. Glue will run natively with Redshift if you want an Amazon solution. I believe you could then just replicate the Glue process in both test & prod, changing just the ID/endpoints. They also have some pre-built ETL in GitHub, perhaps you could use/modify some of that to speed up the process. And Glue has crawlers to populate the data catalog for you. You can create your code in Python, so you don't need anything proprietary. If you want more on Redshift capabilities, you can go here.

  2. There's also Matillion ETL for Redshift. It has solid ETL capabilities, but is another product to buy.

  3. Consider a general ETL tool like Informatica, Alooma, Talend, etc.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.