Generating a compacted temporal table

compacted_temporal_table.sql

with input_data AS (

SELECT 1 AS id, 'a' AS value_text, 12 as value_int, DATE('2021-01-01') AS update_date

UNION ALL

SELECT 1 AS id, 'b' AS value_text, 30 AS value_int, DATE('2021-11-01') AS update_date

UNION ALL

SELECT 1 AS id, 'b' AS value_text, 25 AS value_int, DATE('2022-02-01') AS update_date

UNION ALL

SELECT 1 AS id, 'b' AS value_text, 25 AS value_int, DATE('2022-04-01') AS update_date

UNION ALL

SELECT 1 AS id, 'a' AS value_text, 11 AS value_int, DATE('2022-05-01') AS update_date

UNION ALL

SELECT 1 AS id, 'c' AS value_text, 11 AS value_int, DATE('2022-06-01') AS update_date

UNION ALL

SELECT 1 AS id, 'd' AS value_text, 20 AS value_int, DATE('2022-11-01') AS update_date

UNION ALL

SELECT 1 AS id, 'e' AS value_text, 20 AS value_int, DATE('2022-12-01') AS update_date

UNION ALL

SELECT 1 AS id, 'e' AS value_text, 20 AS value_int,  DATE('2023-03-01') AS update_date

)

,hashed AS (

SELECT 

  id, 
  value_text, 
  value_int, 
  update_date, 
  FARM_FINGERPRINT(CONCAT(IFNULL(value_text, 'N/A'),
                          IFNULL(value_int, -1))) AS row_hash 

FROM input_data


)


,compacted AS (


SELECT 

  id, 
  value_text, 
  value_int, 
  update_date, 
  row_hash, 
  LEAD(row_hash,1) OVER (PARTITION BY id ORDER BY update_date) AS next_hash

FROM hashed

QUALIFY row_hash <> IFNULL(next_hash, -1)





)
SELECT 


  id, 
  value_text, 
  value_int, 
  update_date AS valid_from, 
  IFNULL(DATE_SUB(LEAD(update_date,1) OVER(PARTITION BY id ORDER BY update_date), 
                  INTERVAL 1 DAY),
         DATE('9999-01-01')) AS valid_to 
  
FROM compacted