Change Data Capture (3)

Here we will apply what we have seen before  to our Airline DWH example. The objective we want to achieve is:

1) set up Change Data Capture for the incoming data from AviationStack

2) use the Change Data Capture table to incrementally load our Data Warehouse.

The first thing we will do, is create the change data capture table :

Below this , a screenshot of the resulting table:

Next, we will create the PostgreSQL function aviationstack_capture_changes which will do just this: capture the changes made to the  aviationstack_flightdata_staging table.

Finally, we create the Trigger :

CREATE TRIGGER aviationstack_trigger

AFTER INSERT OR UPDATE OR DELETE

ON aviationstack_flightdata_staging

FOR EACH ROW

EXECUTE FUNCTION aviationstack_capture_changes();

On the next page we'll check if it all works....