Merging Data from Data Warehouse Staging Tables to Production

After data has been staged in Data Warehouse, merge it into your production environment. In this case, you create a DBExecute instance to MERGE INTO records from the staging tables.

Steps:

1. In the graph, add a DBExecute component.

2. Edit the component in the following way: 

   • DB Connection: select the connection that you created

   • SQL query: specify the MERGE INTO commands to merge from staging to production (see the section “Merging Data to Persistent Tables” in ELT Blueprint).

     MERGE INTO opportunities t
          USING in_opportunities s
             ON s.id = t.id
       WHEN MATCHED THEN
         UPDATE SET name      = s.name,
                    created   = s.created,
                    closed    = s.closed,
                    stage     = s.stage,
                    is_closed = s.is_closed,
                    is_won    = s.is_won,
                    amount    = s.amount
       WHEN NOT MATCHED THEN
         INSERT
           (id, name, created, closed, stage, is_closed, is_won, amount)
           VALUES
             (s.id, s.name, s.created, s.closed, s.stage, s.is_closed, s.is_won, s.amount)
     

   • Print statements: true

   • Transaction set: All statements

   Your DBExecute component looks like the following:  

   3. Save your graph.

After this graph is executed, you can truncate the staging tables.