Upsert-merge for Firebolt

Using Data Integration, Firebolt users can perform an Upsert-Merge data using views and the flip-flop method.

Firebolt views design

Firebolt represents the table names you define in your rivers as views in your database. To ensure maximum availability, this design alternates between two tables—suffixed with _flip and _flop after every successful river run. For example, the table name is facebook_insights, and the underlying tables are facebook_insights_flip and facebook_insights_flop.

You can find the name of the current underlying table from the views schema by exploring the DDL of the view. If you want to make changes, such as adding additional AGGREGATION or JOIN indexes to your tables, you can do so by querying the information_schema views and extracting the DDL. Then, add the new index to both the original table and the other table.

You should make all other changes, such as partitions or primary indexes, only via the Data Integration console.

Flip/Flop Flow

You can find additional details on the Flip/Flop Flow.

1. New data arrives:
2. If there is no current target view
   1. Create a flip table with the new data.
   2. Create a view over the flip table.
3. If there is only a flip table:
   1. Create a flop table.
   2. Drop the current view.
   3. Create a new view over the flop table. 
4. If both flip/flop tables exist:
   1. Check the current table that the view depends on.
   2. If flip
      1. Drop flop
      2. Recreate flop with new data
      3. Drop current view
      4. Create a view over flop
   3. If flop
      1. Drop flip
      2. Recreate the flip with new data
      3. Drop current view
      4. Create a view over flip

Flip/Flop diagram