Background: I have a few models that are materialized as ‘Table’. This table is populated by wipe (truncate) and load. Now I want to protect the table’s existing data if the query used to populate it returns an empty result set. How can I make sure an empty result set is not replacing my existing data in the table? My table lies in Databricks UC, and I am using dbt to model the output table.In a nutshell, only commit the transaction only if the SQL statement used returns a non-empty result set.
You could change from a table materialization to an incremental model, which will not drop and reload the table every time; instead it will just insert or merge new rows returned by the queries