Simplified UNNEST with STRUCT de-structuring

[pilosoposerio]

The following CTE block:

data-engineer-handbook/bootcamp/materials/1-dimensional-data-modeling/lecture-lab/incremental_scd_query.sql

Lines 67 to 75 in 954ac02

	unnested_changed_records AS (
	SELECT player_name,
	(records::scd_type).scoring_class,
	(records::scd_type).is_active,
	(records::scd_type).start_season,
	(records::scd_type).end_season
	FROM changed_records
	),

can be simplified to (using CROSS JOIN to the UNNESTed array):

unnested_changed_records AS ( 
  
     SELECT c.player_name, 
           r.*
            FROM changed_records c, UNNEST(c.records) r
     ), 

provided that you remove the UNNEST call in changed_records.records (i.e., just leave records as an ARRAY):

data-engineer-handbook/bootcamp/materials/1-dimensional-data-modeling/lecture-lab/incremental_scd_query.sql

Lines 46 to 60 in 954ac02

	UNNEST(ARRAY[
	ROW(
	ls.scoring_class,
	ls.is_active,
	ls.start_season,
	ls.end_season
	)::scd_type,
	ROW(
	ts.scoring_class,
	ts.is_active,
	ts.current_season,
	ts.current_season
	)::scd_type
	]) as records

This is especially helpful when your scd_type has a lot more fields and you need to de-structure them all.

[BUBLET]

Looks good to merge, but I’d recommend adding a check for when the records array is empty—just to avoid any issues with UNNEST.

[redpandaxl]

is this easier to read than using the deconstructor?

(unnest(ARRAY[
               (ls.scoring_class, ls.is_active, ls.start_season, ls.end_season)::scd_type,
               (ts.scoring_class, ts.is_active, ts.current_season, ts.current_season)::scd_type
           ])).*

[BUBLET]

CROSS JOIN and UNNEST is actually easier, especially if there're many fields
There's no need to explicity specify each field during deconstruction and thus no risk of manual errors