Normalize

Translation reference to convert Teradata Normalize functionality to Snowflake

Description

NORMALIZE specifies that period values in the first-period column that meet or overlap are combined to form a period that encompasses the individual period values. For more information about Normalize clause, see the Teradata documentation.

Sample Source Patterns

Sample data

Teradata

CREATE TABLE project
     (    emp_id        INTEGER,
          project_name  VARCHAR(20),
          dept_id       INTEGER,
          duration      PERIOD(DATE)
     );

INSERT INTO project VALUES (10, 'First Phase', 1000,  PERIOD(DATE '2010-01-10', DATE '2010-03-20') );

INSERT INTO project VALUES (10, 'First Phase', 2000,  PERIOD(DATE '2010-03-20', DATE '2010-07-15') );

INSERT INTO project VALUES (10, 'Second Phase', 2000,  PERIOD(DATE '2010-06-15', DATE '2010-08-18') );

INSERT INTO project VALUES (20, 'First Phase', 2000,  PERIOD(DATE '2010-03-10', DATE '2010-07-20') );

INSERT INTO project VALUES (20, 'Second Phase', 1000,  PERIOD(DATE '2020-05-10', DATE '2020-09-20') );

Snowflake

CREATE TABLE PUBLIC.project
(
     emp_id INTEGER,
     project_name VARCHAR(20),
     dept_id INTEGER,
     duration VARCHAR(24) /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/
);

INSERT INTO PUBLIC.project VALUES (10, 'First Phase', 1000, PUBLIC.PERIOD_UDF(DATE '2010-01-10', DATE '2010-03-20') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/);

INSERT INTO PUBLIC.project VALUES (10, 'First Phase', 2000, PUBLIC.PERIOD_UDF(DATE '2010-03-20', DATE '2010-07-15') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/);

INSERT INTO PUBLIC.project VALUES (10, 'Second Phase', 2000, PUBLIC.PERIOD_UDF(DATE '2010-06-15', DATE '2010-08-18') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/);

INSERT INTO PUBLIC.project VALUES (20, 'First Phase', 2000, PUBLIC.PERIOD_UDF(DATE '2010-03-10', DATE '2010-07-20') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/);

INSERT INTO PUBLIC.project VALUES (20, 'Second Phase', 1000, PUBLIC.PERIOD_UDF(DATE '2020-05-10', DATE '2020-09-20') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/);

Normalize Clause

Suppose you want to use Normalize clause with the employee id.

Teradata

SELECT NORMALIZE emp_id, duration FROM project

Result

EMP_ID	DURATION
20	(2010-03-10, 2010-07-20)
10	(2010-01-10, 2010-08-18)
20	(2020-05-10, 2010-09-20)

Snowflake

WITH NormalizeCTE AS
(
  SELECT
    T1.*,
    SUM(GroupStartFlag)
    OVER (
    PARTITION BY
      emp_id
    ORDER BY
      duration_begin
    ROWS UNBOUNDED PRECEDING) GroupID
  FROM
    (
      SELECT  
        emp_id,
        duration,
        /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_BEGIN_UDF' INSERTED. ***/
        PUBLIC.PERIOD_BEGIN_UDF(duration) duration_begin,
        /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_END_UDF' INSERTED. ***/
        PUBLIC.PERIOD_END_UDF(duration) duration_end,
        (CASE
          WHEN duration_begin <= LAG(duration_end)
          OVER (
          PARTITION BY
            emp_id
          ORDER BY
            duration_begin,
            duration_end)
            THEN 0
          ELSE 1
        END) GroupStartFlag
      FROM  
        PUBLIC.project
    ) T1
)
SELECT
  emp_id,
  /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/
  PUBLIC.PERIOD_UDF(MIN(duration_begin), MAX(duration_end))
FROM
  NormalizeCTE
GROUP BY
  emp_id,
  GroupID;

Result

EMP_ID	PUBLIC.PERIOD_UDF(MIN(START_DATE), MAX(END_DATE))
20	2020-05-10*2010-09-20
20	2010-03-10*2010-07-20
10	2010-01-10*2010-08-18

Known Issues

Normalize clause can use ON MEETS OR OVERLAPS, ON OVERLAPS or ON OVERLAPS OR MEETS, for these cases SnowConvert will add an error that this translation is planned for the future.

Teradata

SELECT NORMALIZE ON MEETS OR OVERLAPS emp_id, duration FROM table1;```sql

Snowflake

SELECT
--       --** MSC-ERROR - MSCEWI1073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'NORMALIZE SET QUANTIFIER' NODE **
--       NORMALIZE ON MEETS OR OVERLAPS
                                      emp_id,
duration FROM
table1;

Related EWIs

• MSCEWI1073: Pending Functional Equivalence Review.

• MSCEWI2079: The required period type column was not found.