Upsert-Merge for Firebolt

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

Firebolt views design

The table names you define in your rivers will be represented as a views in your Firebolt database.
This design was chosen to ensure maximum data availability at all times.
The way it is done is by alternating between two tables after every successful river run, the name of the tables are the table name + "_flip" or "_flop".
e.g. table name is "facebook_insights" 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 wish to make changes such as adding additional AGGREGATION or JOIN indexes to your tables you can do it by querying the views information_schema and extract the DDL and then adding the new index to that table and also to the other table.

Do note that every other change such as partitions or primary index should be done only VIA the Rivery console.

Flip/Flop flow

Below you can find additional details on the Flip/Flop flow

1. New data arrives
2. If there is no current target view
   1. Create flip table with the new data
   2. Create a view over the flip table
3. If there is only flip table
   1. Create flop table
   2. Drop the current view
   3. Create a new view over the flop table
4. If both flip/flop tables exists
   1. Check what is the current table the view is based upon
   2. If flip
      1. Drop flop
      2. Recreate flop with new data
      3. Drop current view
      4. Create view over flop
   3. If flop
      1. Drop flip
      2. Recreate flip with new data
      3. Drop current view
      4. Create view over flip

Flip/Flop diagram