continuous integration and continuous deployment in DW/BI

Deployment method

In Redshift

1, Deploy process: Drop and Refresh the view, Drop table, Create an empty table using the DDL

2, Build data: Insert the data to table from the view

In Netezza

1, Deploy process: Drop all the existing tables, Refresh the view and create the an empty table for that view
2, Build data: Create the table with data(CTAS from view), replace the empty table with the new table

When building data, the scripts will first create an table with an incoming suffix, then do some data validation, if all passed, then replace the empty target table with the incoming table.

Automation method

Basically there are have two process in the DW stage: 

1, load data from DB2 to Redshift

2, Refresh objects in Redshift(DMA job)

Currenlty, Use Jenkins as the automation tool to call all the shell scripts, and use Jenkins pipeline to get Jenkins jobs automated.