0

Currently we are in process of migration from legacy on premise Data warehousing solution [based on IBM datastage ] to cloud based solution . We have files with incremental information from heterogeneous sources which we require to load into our target tables while maintaining history of each and every transaction [because we need to report on the basis of PIT values] . We are planning to use AWS GLUE and AWS PostGre for the above mentioned approach .

Is it correct approach as I need to do a lot of custom transformations in Glue Jobs ? Also in order to check/test the glue jobs is there any way to create a development environment without incurring additional costs [creation of dev end points is chargeable as per AWS standard documentation ]

dashsidd1
  • 16
  • 1

1 Answers1

0

Are you expecting to use glue for the migration, or for ongoing operation?
If for migration you should consider DMS instead.
If for ongoing operation, Glue is probably not the best solution for what you are looking to achieve, but it could be used. More below.

I assume that by AWS PostGre you are referring to an RDS hosted PostgreSQL instance. Is that database to be your warehouse store? Your operational store? Both?

Depending on the nature of your current solution, and the warehousing needs, you may want to consider one of these alternate approaches to actually use the cloud (instead of just moving hosting of on-prem solutions to the cloud):

  1. You could use an AWS data lake to store the transaction files, and then use Athena to query the data and provide insights. For most scenarios this is fairly low cost and performance is quite good. The bonus here is that all your historical data would be maintained clearly as individual files. Storage can however become a mess if you have high throughput on your files.
  2. You could commit fully to a data warehouse with Red Shift, this is a fairly costly option but has great performance and will support true warehousing if that is the need.

If you do want to stay with PostGreSQL entirely (source and target are PostGreSQL), you could use a CDC approach to warehouse the data.

If despite all of this you still want to use glue, you would need a mechanism to trigger glue, either files, timing, a streaming job, or even a lambda, and have glue take the data and push it into the target database, likely with either an insert only with change date approach, or with an audit table approach. This is likely more complicated than any of the other solutions above.

jonlegend
  • 361
  • 2
  • 6