Interesting read, thanks! Regarding generating missing ids (quote from the blogpost: "If you know how one would generate the missing ids between the gaps (which could be of variable size)": I don't have access to snowflake instance, but the following works in Postgres (if I understood the problem correctly):

   with lead as (
      select id, lead(id, 1) over (order by id) as lead_by_one 
      from gaps_table),
   gaps as (
      select id + 1 as start_gap, 
      lead_by_one - 1 as end_gap
      from lead where lead_by_one - id > 1)
   select generate_series(start_gap, end_gap) as missing_ids from gaps

So if Snowflake has a generating function similar to generate_series, it should do the trick.