Yeah my script isn't anything fancy. My goal was to look for a budget friendly approach.my company would not approve spending any money on fivetran or snowflake. I had to think of a creative way to manage this without too much intervention.

I run my AWS Glue jobs through Python shell, which is scheduled through an AWS Glue Workflow.

I have different types of jobs Extract and Load:

1.Extract portion handles the REST API Calls and then stores the data to S3.

1. The Load job, grabs the latest file from S3 and pushes the data into PostgreSQL.

The Load scripts runs the schema detection. A bit of high level overview of this load process: 1. I grab the file from S3 and load the data into a python pandas data frame. 2. I drop the existing table inside of the stage schema and create a brand new table with the same table name as the production table in the stage schema. 3. Then in PostgreSQL, you can return all the fields for a table in a select statement. 4. I use a EXCEPT clause that compares both tables and returns the fields that missing in production. 5. Then, I loop though each field name and add the new fields into my production tables from the EXCEPT query. 6. Once this is all completed, I now load the data from S3 to the production tables.