Merging Data from Data Warehouse Staging Tables to Production
CloudConnect is a legacy tool and will be discontinued. We recommend that to prepare your data you use the GoodData data pipeline as described in Data Preparation and Distribution. For data modeling, see Data Modeling in GoodData to learn how to work with Logical Data Modeler.
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:
In the graph, add a DBExecute component.
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:
After this graph is executed, you can truncate the staging tables.