title: In Snowflake how to make NEW data available once all related objects have been loaded?
body: <p>Lets say we have dimensional data model in our Snowflake data warehouse that is loaded once a day. The requirement is that the data loaded today should be available to end users only when ALL objects have been loaded successfully.</p>
  <p>So if dimension d1 has been loaded successfully today, but fact f1 load fails for what ever reason, then the end users should stay with the data loaded yesterday (also for the dimension d1 then). The flip over to the new data should take place only when all dimensional model objects have loaded successfully.</p>
  <p>We have currently then done it the way that we stage the incremental set / new data to staging tables and once all successfully there then there is a huge SQL script doing DELETE/INSERT one dimensional model table at a time and then the single commit in the end. So the enda data load is not done in parallel.</p>
  <p>Is there a better way to achieve the requirement? We were thinking about materialized views (load all the underlying dimensional model tables and the refresh the &quot;select * from table&quot; mat. views ) but they are refreshed automatically in Snowflake. Also then DROP target table / CLONE from source to target table does not suite us as there is the chance that the target table is queried at the time when it does not exist.</p>