Pivot/Denormalize Over Effective Ranges

Question

I'm looking to pivot a transactional data set into SCD2 in such a way that captures the intervals in which each combination at the pivot grain were effective.

Snowflake is the actual DBMS I'm using, but tagging Oracle too because their dialects are nearly identical. I could probably finagle a solution provided for any DBMS though.

I have working sql, but this it's born out of trial&error and I feel like there has to be a more elegant way that I'm missing because it's very ugly and computationally expensive.

(Note: The second record in the input data "expires" the first. It can be assumed that every day of interest will occur as an add_dts at least once.) (added as image at the end until I can figure out why the markup isn't working)

Input:

Original_Grain	Pivot_Grain	Pivot_Column	Pivot_Attribute	ADD_TS
OG-1	PG-1	First_Col	A	2020-01-01
OG-1	PG-1	First_Col	B	2020-01-02
OG-2	PG-1	Second_Col	A	2020-01-01
OG-3	PG-1	Third_Col	C	2020-01-02
OG-3	PG-1	Third_Col	B	2020-01-03

Output:

Pivot_Grain	First_Col	Second_Col	Third_Col	From_Dt	To_Dt
PG-1	A	A	NULL	2020-01-01	2020-01-02
PG-1	B	A	C	2020-01-02	2020-01-03
PG-1	B	A	B	2020-01-03	9999-01-01

WITH INPUT AS 
  ( SELECT 'OG-1' AS Original_Grain,
           'PG-1' AS Pivot_Grain,
           'First_Col' AS Pivot_Column,
           'A' AS Pivot_Attribute,
           TO_DATE('2020-01-01','YYYY-MM-DD') AS Add_Dts
      FROM dual
     UNION
   SELECT 'OG-1' AS Original_Grain,
           'PG-1' AS Pivot_Grain,
           'First_Col' AS Pivot_Column,
           'B' AS Pivot_Attribute,
           TO_DATE('2020-01-02','YYYY-MM-DD')
      FROM dual
     UNION
   SELECT 'OG-2' AS Original_Grain,
           'PG-1' AS Pivot_Grain,
           'Second_Col' AS Pivot_Column,
           'A' AS Pivot_Attribute,
           TO_DATE('2020-01-01','YYYY-MM-DD')
      FROM dual
     UNION
   SELECT 'OG-3' AS Original_Grain,
           'PG-1' AS Pivot_Grain,
           'Third_Col' AS Pivot_Column,
           'C' AS Pivot_Attribute,
           TO_DATE('2020-01-02','YYYY-MM-DD')
      FROM dual
     UNION
   SELECT 'OG-3' AS Original_Grain,
           'PG-1' AS Pivot_Grain,
           'Third_Col' AS Pivot_Column,
           'B' AS Pivot_Attribute,
           TO_DATE('2020-01-03','YYYY-MM-DD')
      FROM dual     
  ),
  GET_NORMALIZED_RANGES AS 
  ( SELECT I.*,
           COALESCE(
             LEAD(Add_Dts) OVER (
             PARTITION BY I.Original_Grain
                 ORDER BY I.Add_Dts), TO_DATE('9000-01-01')
             ) AS Next_Add_Dts
      FROM INPUT I
  ),
  GET_DISTINCT_ADD_DATES AS 
  ( SELECT DISTINCT Add_Dts AS Driving_Date
      FROM Input  
  ),
  NORMALIZED_EFFECTIVE_AT_EACH_POINT AS 
  ( SELECT GNR.*,
           GDAD.Driving_Date
      FROM GET_NORMALIZED_RANGES GNR
     INNER
      JOIN GET_DISTINCT_ADD_DATES GDAD
        ON GDAD.driving_date >= GNR.add_dts
       AND GDAD.driving_Date < GNR.next_add_dts
  ),
  PIVOT_EACH_POINT AS 
  ( SELECT DISTINCT
           Pivot_Grain,
           Driving_Date,
           MAX("'First_Col'") OVER ( PARTITION BY Pivot_Grain, Driving_Date) AS First_Col,
           MAX("'Second_Col'") OVER ( PARTITION BY Pivot_Grain, Driving_Date) AS Second_Col,
           MAX("'Third_Col'") OVER ( PARTITION BY Pivot_Grain, Driving_Date) AS Third_Col
      FROM NORMALIZED_EFFECTIVE_AT_EACH_POINT NEP
     PIVOT (MAX(Pivot_Attribute) FOR PIVOT_COLUMN IN ('First_Col','Second_Col','Third_Col'))
  )
  SELECT Pivot_Grain,
         Driving_Date AS From_Dt,
         COALESCE(LEAD(Driving_Date) OVER ( PARTITION BY pivot_grain ORDER BY Driving_Date),TO_DATE('9999-01-01')) AS To_Dt,
         First_Col,
         Second_Col,
         Third_Col
    FROM PIVOT_EACH_POINT

The table mark-up worked in the preview, but not in the actual post — Error_2646, Mar 05 '21 at 19:22
we build our SCD1 & 2 (aka SCD6) tables by doing a hash on the columns that are "tracked" and thus each time we have a data pull if hash is the same then the to time left in the distance future, otherwise alter (the existing) and insert the new row. I am quite sure this is all done from the same MERGE instruction. — Simeon Pilgrim, Mar 06 '21 at 01:29
@SimeonPilgrim Yes, I'm familiar with that pattern. The crux of the problem though is handling the date intervals by combination across the pivot grain. — Error_2646, Mar 06 '21 at 02:27
often the markdown like there to be gap rows between different blocks of formatting. — Simeon Pilgrim, Mar 06 '21 at 04:27

score 1 · Accepted Answer · answered Mar 06 '21 at 04:25

so the input can be written with the VALUES operator, and the column names put into the CTE definition, making it take up less space.

WITH input(original_grain, pivot_grain, pivot_column, pivot_attribute, add_dts) AS ( 
    SELECT * FROM VALUES 
        ('OG-1', 'PG-1', 'First_Col', 'A', '2020-01-01'::date),
        ('OG-1', 'PG-1', 'First_Col', 'B', '2020-01-02'::date),
        ('OG-2', 'PG-1', 'Second_Col', 'A', '2020-01-01'::date),
        ('OG-3', 'PG-1', 'Third_Col', 'C', '2020-01-02'::date),
        ('OG-3', 'PG-1', 'Third_Col', 'B', '2020-01-03'::date) 
)

the LEAD in can be simplified by using the default value, which is an implicit COALESCE, but also some times if you have gaps in your data of this type IGNORE NULLS OVER is an awesome tool.

, get_normalized_ranges AS ( 
    SELECT 
        *
        ,LEAD(add_dts,1,'9000-01-01'::date) OVER (PARTITION BY original_grain ORDER BY add_dts) AS next_add_dts
    FROM input
)

get_distinct_add_dates seems fine.

, get_distinct_add_dates AS ( 
    SELECT DISTINCT add_dts AS driving_date
    FROM input  
)

depending on your data normalized_effective_at_each_point will do it's name sake and give you a value at every time/date point, which will over slice the values of that are unrelated (I assume pivot_grain is some global thing id that is distinct data thus this input backs it)

        ('OG-1', 'PG-1', 'First_Col', 'A', '2020-01-01'::date),
        ('OG-1', 'PG-1', 'First_Col', 'B', '2020-01-03'::date),
        ('OG-2', 'PG-1', 'Second_Col','A', '2020-01-01'::date),
        ('OG-3', 'PG-1', 'Third_Col', 'C', '2020-01-03'::date),
        ('OG-3', 'PG-1', 'Third_Col', 'B', '2020-01-05'::date),
        ('OG-4', 'PG-2', 'First_Col', 'D', '2020-02-02'::date),
        ('OG-4', 'PG-2', 'First_Col', 'E', '2020-02-04'::date),
        ('OG-5', 'PG-2', 'Second_Col','D', '2020-02-02'::date),
        ('OG-6', 'PG-2', 'Third_Col', 'F', '2020-02-04'::date),
        ('OG-6', 'PG-2', 'Third_Col', 'D', '2020-02-06'::date)

at which point get_distinct_add_dates should become:

, get_distinct_add_dates AS ( 
    SELECT DISTINCT pivot_grain, add_dts AS driving_date
    FROM input  
)

INNER JOIN is a JOIN so we can skip the unneeded INNER

, normalized_effective_at_each_point AS ( 
    SELECT gnr.*,
        gdad.driving_date
    FROM get_normalized_ranges AS gnr
    JOIN get_distinct_add_dates AS gdad
        ON gnr.pivot_grain = gdad.pivot_grain 
        AND gdad.driving_date >= gnr.add_dts
        AND gdad.driving_date < gnr.next_add_dts
  ),

really pivot_each_point is a three way JOIN, or a GROUP BY could be written, which the DISTINCT was really doing for us, and thus the PIVOT is gone.

  , pivot_each_point AS (
    SELECT Pivot_Grain
        ,Driving_Date
        ,MAX(IFF(pivot_column='First_Col', Pivot_Attribute, NULL)) as first_col
        ,MAX(IFF(pivot_column='Second_Col', Pivot_Attribute, NULL)) as second_col
        ,MAX(IFF(pivot_column='Third_Col', Pivot_Attribute, NULL)) as third_col
    FROM normalized_effective_at_each_point
    GROUP BY 1,2
  )

and lastly the final lead can drop the COALESCE and be moved into pivot_each_point.

WITH input(original_grain, pivot_grain, pivot_column, pivot_attribute, add_dts) AS ( 
    SELECT * FROM VALUES 
        ('OG-1', 'PG-1', 'First_Col', 'A', '2020-01-01'::date),
        ('OG-1', 'PG-1', 'First_Col', 'B', '2020-01-03'::date),
        ('OG-2', 'PG-1', 'Second_Col','A', '2020-01-01'::date),
        ('OG-3', 'PG-1', 'Third_Col', 'C', '2020-01-03'::date),
        ('OG-3', 'PG-1', 'Third_Col', 'B', '2020-01-05'::date),
        ('OG-4', 'PG-2', 'First_Col', 'D', '2020-02-02'::date),
        ('OG-4', 'PG-2', 'First_Col', 'E', '2020-02-04'::date),
        ('OG-5', 'PG-2', 'Second_Col','D', '2020-02-02'::date),
        ('OG-6', 'PG-2', 'Third_Col', 'F', '2020-02-04'::date),
        ('OG-6', 'PG-2', 'Third_Col', 'D', '2020-02-06'::date) 
), get_normalized_ranges AS (      
    SELECT 
        *
        ,LEAD(add_dts,1,'9000-01-01'::date) OVER (PARTITION BY original_grain ORDER BY add_dts) AS next_add_dts
    FROM input
), get_distinct_add_dates AS ( 
    SELECT DISTINCT pivot_grain, add_dts AS driving_date
    FROM input  
), normalized_effective_at_each_point AS ( 
    SELECT gnr.*,
        gdad.driving_date
    FROM get_normalized_ranges AS gnr
    JOIN get_distinct_add_dates AS gdad
        ON gnr.pivot_grain = gdad.pivot_grain 
        AND gdad.driving_date >= gnr.add_dts
        AND gdad.driving_date < gnr.next_add_dts 
)
SELECT pivot_grain
    ,driving_date
    ,LEAD(driving_date, 1, '9999-01-01'::date) OVER (PARTITION BY pivot_grain ORDER BY driving_date) AS to_dt
    ,MAX(IFF(pivot_column = 'First_Col', pivot_attribute, NULL)) AS first_col
    ,MAX(IFF(pivot_column = 'Second_Col', pivot_attribute, NULL)) AS second_col
    ,MAX(IFF(pivot_column = 'Third_Col', pivot_attribute, NULL)) AS third_col
FROM normalized_effective_at_each_point
GROUP BY pivot_grain, driving_date
ORDER BY pivot_grain, driving_date;

giving the result:

PIVOT_GRAIN  DRIVING_DATE   TO_DT       FIRST_COL   SECOND_COL  THIRD_COL
PG-1         2020-01-01     2020-01-03  A           A           null
PG-1         2020-01-03     2020-01-05  B           A           C
PG-1         2020-01-05     9999-01-01  B           A           B
PG-2         2020-02-02     2020-02-04  D           D           null
PG-2         2020-02-04     2020-02-06  E           D           F
PG-2         2020-02-06     9999-01-01  E           D           D

I cannot help but think I have over mapped how I process data onto your PIVOT_GRAIN. And I started to trying solve this again from first principles now that I understand the code, and I think the first three processing CTE is how I would do it, thus the GROUP BY is also how I would do the rest, many JOIN seems really gross, and into Snowflake, I am more of a fan of this explode the data, and then MERGE (or GROUP BY) the data, as it's all nice and parallizable.

score 0 · Answer 2 · answered Mar 05 '21 at 19:54

0

Not sure if this answers your question, but see https://jeffreyjacobs.wordpress.com/2021/03/03/pivoting-iiot-data-in-snowflake/

answered Mar 05 '21 at 19:54

Jeffrey Jacobs

302
1
4

Thanks, that was a very well put together article. It's definitely in the same vein as my question, but is not quite the "You're an idiot, just do this way easier thing" I was hoping for. – Error_2646 Mar 05 '21 at 20:08

Pivot/Denormalize Over Effective Ranges

2 Answers2