How to: add a new UUID (unique for each Sync) to all rows from all streams for a given sync?

[Liam Considine (iamliamc)]

Situation:
Airbyte + Terraform small changes (commonly cause delete/replace) Connections...
Causes reset of _airbyte_generation_id making full_refresh_append streams difficult to use...

Currently when I deploy changes to production using terraform... often change sets cause full delete/create of my Connections

This causes the _airbyte_generation_id to reset... previously on my full_refresh_append tables I had views...

SELECT * FROM `my_raw_table`
WHERE _airbyte_generation_id = (
  SELECT MAX(_airbyte_generation_id)
  FROM `my_raw_table`
)

Now when the connection is reset and runs again there may be two sets of rows in this table that both share a _airbyte_generation_id

Option 1:
This is why I want to add a unique UUID to all the rows from all the streams of a given sync... lets my view look like...

SELECT * FROM `my_raw_table`
WHERE _stable_globally_unique_identifier = (
  SELECT _stable_globally_unique_identifier
  FROM `my_raw_table`
  ORDER BY _airbyte_extracted_at DESC
  LIMIT 1
) 

But I don't know how to do this!
How do i pass the same UUID, unique to each sync, to all the streams and add it as a new field to all of them?
I can't set it as a input (cause it needs to be new for each run)... (unless I orchestrate some kind of patch of the source inputs before each run)

Option 2: sync_id
https://docs.airbyte.com/platform/understanding-airbyte/airbyte-metadata-fields#the-_airbyte_meta-field

• my main issue is that one sync_id seemed to have rows with different _airbyte_generation_ids
• my other issue is if i ever have to kubectl namespace delete airbyte reinstall and redeploy... my sync_id will get out of wack

SELECT
  JSON_VALUE(TO_JSON_STRING(_airbyte_meta), '$.sync_id') AS sync_id,
  ARRAY_AGG(DISTINCT _airbyte_generation_id) AS generation_ids,
  COUNT(DISTINCT _airbyte_generation_id) AS gen_count
FROM
  `my_raw_table`
GROUP BY sync_id
HAVING gen_count > 1
ORDER BY CAST(sync_id AS INT64) DESC

sync_id	generation_ids	gen_count
40	        "[3,4]"	                2

[Liam Considine (iamliamc)]

I could switch to:

WITH latest_date AS (
  SELECT DATE(MAX(_airbyte_extracted_at)) AS extracted_date
  FROM `my_raw_table`
),
max_generation AS (
  SELECT MAX(_airbyte_generation_id) AS max_gen
  FROM `my_raw_table`
  WHERE DATE(_airbyte_extracted_at) = (SELECT extracted_date FROM latest_date)
)

SELECT *
FROM `my_raw_table`
WHERE
  DATE(_airbyte_extracted_at) = (SELECT extracted_date FROM latest_date)
  AND _airbyte_generation_id = (SELECT max_gen FROM max_generation)

But this assumes no sync will ever create records on more than one day... refreshing at 10pm and now i have rows with _airbyte_extracted_at that cover two different dates.