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.