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:
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?
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?
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?