Upsert-merge for Firebolt

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

Firebolt views design

The table names you define in your rivers are represented as views in your Firebolt database. This design is chosen to ensure maximum data availability at all times by alternating between two tables after every successful river run; the names of the tables are the table name + "_flip" or "_flop". For example, the table name is "facebook_insights", and the underlying tables would be "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.

Every other change, such as partitions or primary indexes, should be made 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 is based upon.
   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