ARTIST (Media AI) - High Level Architectural Design

I created an architectural specification document complete with process, sequence, and ERD diagrams for the ARTIST AI Media Service.   This is compressing 5 years of Startup effort I had at ResonanceAI (a.k.a Transform, Inc) and compressing it into a design document..     

LINK: ARTIST AI Media Service

How To: Segment High Volume Events into 15 mins Ticks in Snowflake

Segmenting your events results in exploding your data.   When you have billions of event records needing to be segmented into 15 minute ticks, this could end up being slow and expensive to process.  The smaller the segmentation requirement the more explosion.   

Word to the Wise:  Don't join a big event table to another big table ,or in this case, a large segmentation dataset.   You want the segmentation dataset to be as small as possible to help the sql optimizer to be able to hold at least one side of the join in memory and no spill to disk.

Here is my solution that worked very well in Snowflake.   

Please Note: it uses my simple GENERATE_TIMELINE  function that I posted earlier.  And the SEGMENT_TICK CTE dates don't have to change ever.  

The below example is a 15 min example(900 seconds).  Just change the $GRAIN_SECONDS to what you need for segment size.    Your event table can be as large as you require and cover as may months as you need.  The SEGMENT_TICK CTE remains as a tiny dataset.

SET GRAIN_SECONDS = 900;

WITH SEGMENT_TICK AS (

    -- Get epoch seconds starts in 1970  

    SELECT DATE_PART(EPOCH_SECONDS, TIME_START) AS SECONDS 

      FROM TABLE(GENERATE_TIMELINE(

                      '1970-01-01'

                    , '1970-01-02 03:00:00'  

                    , $GRAIN_SECONDS)

                )

)

--------------------------------------------------------------------

-- Set up test cases

--------------------------------------------------------------------

, TEST_DATA AS (SELECT    COLUMN1 AS TEST_CASE

                        , COLUMN2 AS EVENT_START_TIMESTAMP_LOCAL

                        , COLUMN3 AS EVENT_END_TIMESTAMP_LOCAL

                        , DATE_PART(EPOCH_SECONDS, "EVENT_START_TIMESTAMP_LOCAL") % 86400 AS EVENT_START_SECONDS

                        , DATEDIFF(SECONDS, "EVENT_START_TIMESTAMP_LOCAL", "EVENT_END_TIMESTAMP_LOCAL") AS EVENT_DURATION

                  FROM (VALUES    (   'EVENT AFTER MIDNIGHT'                         

                                    , '2021-11-12 01:00:00'::TIMESTAMP_NTZ

                                    , '2021-11-12 02:00:00'::TIMESTAMP_NTZ)

                                , (   'EVENT BEFORE MIDNIGHT'                         

                                    , '2021-11-11 20:00:00'::TIMESTAMP_NTZ

                                    , '2021-11-11 21:00:00'::TIMESTAMP_NTZ) 

                                , (   'EVENT STRADDLES MIDNIGHT'                       

                                    , '2021-11-11 23:00:00'::TIMESTAMP_NTZ

                                    , '2021-11-12 01:00:00'::TIMESTAMP_NTZ)

                                , (   'NOT ON GRAIN EVENT LATE'                       

                                    , '2021-11-11 13:05:00'::TIMESTAMP_NTZ

                                    , '2021-11-11 14:05:00'::TIMESTAMP_NTZ)

                                , (   'NOT ON GRAIN EVENT EARLY'                      

                                    , '2021-11-11 16:55:00'::TIMESTAMP_NTZ

                                    , '2021-11-11 17:55:00'::TIMESTAMP_NTZ)

                                , (   'NOT ON GRAIN EVENT EARLY & LATE'                      

                                    , '2021-11-11 16:55:00'::TIMESTAMP_NTZ

                                    , '2021-11-11 18:05:00'::TIMESTAMP_NTZ)

                                , (   'CROSSING THE 3 AM BOUNDRY'                    

                                    , '2021-11-11 02:55:00'::TIMESTAMP_NTZ

                                    , '2021-11-11 03:55:00'::TIMESTAMP_NTZ)

                       )

                )

--------------------------------------------------------------------

-- Calc Event Start seconds and Event Duration

--------------------------------------------------------------------

, EVENT_GRAIN AS (SELECT  T.EVENT_START_TIMESTAMP_LOCAL

                        , T.EVENT_END_TIMESTAMP_LOCAL

                        , T.EVENT_START_SECONDS

                        , T.EVENT_DURATION

                       

                        -- Calc Start Time

                        , DATEADD(SECONDS, S.SECONDS, T.EVENT_START_TIMESTAMP_LOCAL::DATE) AS SEGMENT_TIME_START

                        -- Calc End Time

                        , DATEADD(SECONDS, $GRAIN_SECONDS, "SEGMENT_TIME_START") SEGMENT_TIME_END

                    FROM SEGMENT_TICK S

                    JOIN TEST_DATA T

                        -- Overlap Window logic

                        -- T.Start < S.End

                        -- S.Start < T.End

                     ON T.EVENT_START_SECONDS < S.SECONDS + $GRAIN_SECONDS              

                    AND S.SECONDS < T.EVENT_START_SECONDS + T.EVENT_DURATION   

                )    

-- Return Results

SELECT * 

  FROM EVENT_GRAIN;

 

 

How To: Pattern for Pipeline Stored Procedure Created in Snowflake

I created a typical pattern for my stored procedures within the pipeline part of the system.   Here is my "GoTo" pattern to start a stored procedure in Snowflake.  This is a JScript structured stored procedure of course and uses the cloning technique for isolating processes from access interruption for down stream users and systems.  Other types of stored procedures may need more control flow logic in JSCRIPT.    

CREATE OR REPLACE PROCEDURE {Schema}.{Pipeline Process Name}()

  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
     sqlStatements = 
    `
----------------------------------------------- 
-- Process: {Place Process Name Here}   
-- Typical Run: {X} minutes on {Y} warehouse
-- Typical Output: {Z} Records
----------------------------------------------- 
CREATE OR REPLACE TABLE {Staging Schema}.{Table Name} 
AS
SELECT * FROM {Staging Schema}.{Process Name};
GRANT OWNERSHIP ON {Staging Schema}.{Table Name} TO ROLE {Security Role};
CREATE OR REPLACE TABLE {Publishing Schema}.{Table Name} CLONE {Staging Schema}.{Table Name};
GRANT OWNERSHIP ON VIEW {Publishing Schema}.{Table Name} TO ROLE {Security Role};

----------------------------------------------- 
-- Process: {Place Process Name Here}   
-- Typical Run: {X} minutes on {Y} warehouse

-- Typical Output: {Z} Records

----------------------------------------------- 
CREATE OR REPLACE TABLE {Staging Schema}.{Table Name} 
AS
SELECT * FROM {Staging Schema}.{Process Name};
GRANT OWNERSHIP ON {Staging Schema}.{Table Name} TO ROLE {Security Role};
CREATE OR REPLACE TABLE {Publishing Schema}.{Table Name} CLONE {Staging Schema}.{Table Name};
GRANT OWNERSHIP ON VIEW {Publishing Schema}.{Table Name} TO ROLE {Security Role};

`;

    
        statementList = sqlStatements.split(";") ;
        index = 0;
        
      try {
        for (index in statementList) {
            // don't execute empty strings.
            if (statementList[index].trim()) {
                rs = snowflake.execute({sqlText: statementList[index]});
                }
            }          
        return "Succeeded: " + statementList.length + " Statements Executed.";   // Return a success/error indicator.
        }
    catch (err)  {
        throw "Failed: " + err + ".  SQL Executed(" + statementList[index] +  ")";   // Return a success/error indicator.
        }
  $$;
GRANT OWNERSHIP ON PROCEDURE {Staging Schema}.{Pipeline Process Name}() TO ROLE {Security Role}; 

  /*

  CALL {Staging Schema}.{Pipeline Process Name}();

  */

How To: Generating Simple Timeline Records in Snowflake

When you need to segment (a.k.a. fanning out) your events into 10 second grain (Or whatever grain you need), you will need to generate new records representing that grain across time.   This function enables you to do just that and join again your event table.   In the join it is best to use a form of overlapping window join.   Please look at my recent post on what that logic looks like.  

CREATE OR REPLACE FUNCTION GENERATE_TIMELINE(ARG_START_TIME STRING, ARG_END_TIME STRING, ARG_GRAIN INTEGER)
  RETURNS TABLE ( TIME TIMESTAMP_NTZ, TIME_START TIMESTAMP_NTZ, TIME_END TIMESTAMP_NTZ )
  AS 
 $$
            SELECT * 
              FROM (
                  SELECT DATEADD(SECOND, ((ROW_NUMBER() OVER(ORDER BY 1)) - 1) * ARG_GRAIN, ARG_START_TIME::TIMESTAMP_NTZ) AS TIME
                         , TIME AS TIME_START
                         , DATEADD(SECOND, ARG_GRAIN-1, TIME) AS TIME_END
                     FROM TABLE(generator(rowcount => 1000000))
                    )
              WHERE TIME BETWEEN ARG_START_TIME AND ARG_END_TIME  
 $$;
 

SELECT *
  FROM TABLE(GENERATE_TIMELINE('2021-06-01 05:55:03','2021-06-01 07:05:15',10))

How To: Generate Dates from a CHRON Pattern in Snowflake

I created this logic to generate dates based on a Chron Pattern.  Useful for when you have specific date patterns you want to pull events from to process on a continual basis in a pipeline.

CREATE OR REPLACE FUNCTION GENERATE_CHRON_PATTERNED_TIMESTAMPS (CHRON_PATTERN  STRING, DURATION NUMBER)
 RETURNS TABLE (  CHRON_START_TIMESTAMP TIMESTAMP
                , CHRON_END_TIMESTAMP TIMESTAMP
                , CHRON_PATTERN STRING
                , MIN_PATTERN STRING
                , HOUR_PATTERN STRING
                , DAY_PATTERN STRING
                , MON_PATTERN STRING
                , WDAY_PATTERN STRING
                , YEAR_PATTERN STRING
                , MIN STRING
                , HOUR STRING
                , DAY STRING
                , MON STRING
                , WDAY STRING
                , YEAR STRING
               )
  AS
$$
WITH PARSE_CHRON_PATTERN AS (SELECT CHRON_PATTERN as chron_pattern

                       , '[0-9*-\.\/]+' as parse_rule
                       ,  REGEXP_SUBSTR(chron_pattern,parse_rule,1,1) min_pattern
                       ,  REGEXP_SUBSTR(chron_pattern,parse_rule,1,2) hour_pattern
                       ,  REGEXP_SUBSTR(chron_pattern,parse_rule,1,3) day_pattern
                       ,  REGEXP_SUBSTR(chron_pattern,parse_rule,1,4) mon_pattern
                       ,  REGEXP_SUBSTR(chron_pattern,parse_rule,1,5) wday_pattern
                       ,  COALESCE(REGEXP_SUBSTR(chron_pattern,parse_rule,1,6), '*') year_pattern

                 )
,
EXPLODE_LIST AS (
            SELECT P.*
                   , MIN_LIST.VALUE AS MIN
                   , HOUR_LIST.VALUE AS HOUR
                   , DAY_LIST.VALUE AS DAY
                   , MON_LIST.VALUE AS MON
                   , WDAY_LIST.VALUE AS WDAY
                   , YEAR_LIST.VALUE AS YEAR
                    
              FROM PARSE_CHRON_PATTERN P,
              lateral split_to_table(P.min_pattern, ',') MIN_LIST,
              lateral split_to_table(P.hour_pattern, ',') HOUR_LIST,
              lateral split_to_table(P.day_pattern, ',') DAY_LIST,
              lateral split_to_table(P.mon_pattern, ',') MON_LIST,
              lateral split_to_table(P.wday_pattern, ',') WDAY_LIST,
              lateral split_to_table(P.year_pattern, ',') YEAR_LIST
)
, WILD_CARD_RANGE AS (
                         SELECT CHRON_PATTERN
                              , PARSE_RULE
                              , MIN_PATTERN
                              , HOUR_PATTERN
                              , DAY_PATTERN
                              , MON_PATTERN
                              , WDAY_PATTERN
                              , YEAR_PATTERN
                              , IFF(MIN = '*', '0-59', MIN) AS MIN
                              , IFF(HOUR = '*', '0-23', HOUR) AS HOUR
                              , IFF(DAY = '*', '1-31', DAY) AS DAY
                              , IFF(MON = '*', '1-12', MON) AS MON
                              , IFF(WDAY = '*', '0-6', WDAY) AS WDAY
                              , IFF(YEAR = '*' or YEAR IS NULL, '2019-' || DATE_PART(YEAR, CURRENT_DATE())::STRING, YEAR) AS YEAR
                          FROM EXPLODE_LIST
 )
, EXPLODE_RANGE AS (SELECT  E.CHRON_PATTERN
                          --, E.PARSE_RULE
                          , E.MIN_PATTERN
                          , E.HOUR_PATTERN
                          , E.DAY_PATTERN
                          , E.MON_PATTERN
                          , E.WDAY_PATTERN
                          , E.YEAR_PATTERN
                          , COALESCE(MIN_LIST.NUMBER::STRING, E.MIN) AS MIN
                          , COALESCE(HOUR_LIST.NUMBER::STRING, E.HOUR) AS HOUR
                          , COALESCE(DAY_LIST.NUMBER::STRING, E.DAY) AS DAY
                          , COALESCE(MON_LIST.NUMBER::STRING, E.MON) AS MON
                          , COALESCE(WDAY_LIST.NUMBER::STRING, E.WDAY) AS WDAY
                          , COALESCE(YEAR_LIST.NUMBER::STRING, E.YEAR) AS YEAR
                      FROM WILD_CARD_RANGE E
                      LEFT OUTER JOIN MEDIA_CONFIG.CHRON_PATTERN_SEQ_NUMBER MIN_LIST
                        ON MIN_LIST.NUMBER BETWEEN TRY_TO_NUMBER(SPLIT_PART(E.MIN,'-',1)) AND TRY_TO_NUMBER(SPLIT_PART(E.MIN,'-',2))
                      LEFT OUTER JOIN MEDIA_CONFIG.CHRON_PATTERN_SEQ_NUMBER HOUR_LIST
                        ON HOUR_LIST.NUMBER BETWEEN TRY_TO_NUMBER(SPLIT_PART(E.HOUR,'-',1)) AND TRY_TO_NUMBER(SPLIT_PART(E.HOUR,'-',2))
                      LEFT OUTER JOIN MEDIA_CONFIG.CHRON_PATTERN_SEQ_NUMBER  DAY_LIST
                        ON DAY_LIST.NUMBER BETWEEN TRY_TO_NUMBER(SPLIT_PART(E.DAY,'-',1)) AND TRY_TO_NUMBER(SPLIT_PART(E.DAY,'-',2))
                      LEFT OUTER JOIN MEDIA_CONFIG.CHRON_PATTERN_SEQ_NUMBER MON_LIST
                        ON MON_LIST.NUMBER BETWEEN TRY_TO_NUMBER(SPLIT_PART(E.MON,'-',1)) AND TRY_TO_NUMBER(SPLIT_PART(E.MON,'-',2))
                      LEFT OUTER JOIN MEDIA_CONFIG.CHRON_PATTERN_SEQ_NUMBER WDAY_LIST
                        ON WDAY_LIST.NUMBER BETWEEN TRY_TO_NUMBER(SPLIT_PART(E.WDAY,'-',1)) AND TRY_TO_NUMBER(SPLIT_PART(E.WDAY,'-',2))
                      LEFT OUTER JOIN MEDIA_CONFIG.CHRON_PATTERN_SEQ_NUMBER YEAR_LIST
                        ON YEAR_LIST.NUMBER BETWEEN TRY_TO_NUMBER(SPLIT_PART(E.YEAR,'-',1)) AND TRY_TO_NUMBER(SPLIT_PART(E.YEAR,'-',2))
                     
                    )
 
 SELECT   try_to_timestamp(YEAR || '-' || MON || '-' || DAY || ' ' || HOUR || ':' || MIN || ':00', 'yyyy-mm-dd hh24:mi:ss') AS CHRON_START_TIMESTAMP
        , DATEADD(SECONDS, DURATION, CHRON_START_TIMESTAMP) AS CHRON_END_TIMSTAMP
        , *
   FROM EXPLODE_RANGE
 HAVING CHRON_START_TIMESTAMP IS NOT NULL
    AND DATE_PART(WEEKDAY, CHRON_START_TIMESTAMP) = WDAY
    AND CHRON_START_TIMESTAMP <= CURRENT_TIMESTAMP()
  ORDER BY CHRON_START_TIMESTAMP DESC               
 $$
 ;
 
 
SELECT * FROM TABLE (GENERATE_CHRON_PATTERNED_TIMESTAMPS('0 17,19-23 1,15 * 1,2,4-6', 3600));

How To: Detect Daylight Savings Time in Snowflake

 I created this function to easily determine if a date is in Daylight Savings Time or Standard Time.    

CREATE OR REPLACE FUNCTION IS_DAYLIGHT_SAVINGS(DATE TIMESTAMP)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
AS
$$
// STANDARD TIME IS Calc SECOND Sunday in MARCH
var standardMonth = new Date(DATE.getFullYear(), 2);
var standardMonthFirstDayType = standardMonth.getDay();
var standardDaysToAdd = 7 + (8 - (standardMonthFirstDayType % 6))   ; // Its +7 and +1 to Account for day one in month
var standardDate = new Date(DATE.getFullYear(), 2, standardDaysToAdd, 2);  
// DAYLIGHT SAVINGS TIME IS Calc FIRST Sunday in NOVEMBER
var dayLightMonth = new Date(DATE.getFullYear(), 10);
var dayLightMonthFirstDayType = dayLightMonth.getDay();
var daylightDaysToAdd = (8 - (standardMonthFirstDayType % 6)) ; //  Its +7 and +1 to to Account for day one in month
var daylightDate = new Date(DATE.getFullYear(), 10, daylightDaysToAdd, 2);  
//DST Test
if (DATE >= daylightDate || DATE < standardDate) {
    return 0;
}
else {
    return 1;
}
$$;

SELECT IS_DAYLIGHT_SAVINGS('2021-11-07 02:00') AS NOV, 
       IS_DAYLIGHT_SAVINGS('2021-03-14 02:00') AS MARCH  ;
SELECT IS_DAYLIGHT_SAVINGS('2021-11-07 01:59') AS NOV, 
       IS_DAYLIGHT_SAVINGS('2021-03-14 01:59') AS MARCH  ;

How To: Simple String Similarity Function in Snowflake

I created this function to help with performing a simple title matching logic for tv show meta data sourced from two different data providers.    I found it satisfactory for my needs at the time.  Obviously it may need a human in the loop to deal with titles that are so different that this process doesn't work.

CREATE OR REPLACE FUNCTION STRING_SIMILARITY (STR1 string, STR2 string, SUB_STRING_LENGTH variant, CASE_SENSITIVE boolean) 

  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
AS
$$
if (CASE_SENSITIVE == false) {
STR1 = STR1.toLowerCase();
STR2 = STR2.toLowerCase();
}
if (STR1.length < SUB_STRING_LENGTH || STR2.length < SUB_STRING_LENGTH)
return 0;
const map = new Map();
for (let i = 0; i < STR1.length - (SUB_STRING_LENGTH - 1); i++) {
const substr1 = STR1.substr(i, SUB_STRING_LENGTH);
map.set(substr1, map.has(substr1) ? map.get(substr1) + 1 : 1);
}
let match = 0;
for (let j = 0; j < STR2.length - (SUB_STRING_LENGTH - 1); j++) {
const substr2 = STR2.substr(j, SUB_STRING_LENGTH);
const count = map.has(substr2) ? map.get(substr2) : 0;
if (count > 0) {
map.set(substr2, count - 1);
match++;
}
}
return (match * 2) / (STR1.length + STR2.length - ((SUB_STRING_LENGTH - 1) * 2));
$$
;
SELECT STRING_SIMILARITY ('Startrek', 'Star Trek', 2, FALSE) ;

How To: Search for overlapping time windows

This logic is particularly useful when looking for all the events that overlap a specific time window.   For example:  You want to know all the TV watch sessions in your DVRs that you have distributed across the world which overlap a range of time.

Here is a Snowflake SQL example of performing this operation complete with test cases as proof it works.

/* This proof tests the logic for Overlapping window filters */
SET FILTER_START = '2020-02-01 00:00:00 +0000';
SET FILTER_END = '2020-02-20 23:59:59 +0000';

CREATE OR REPLACE TEMPORARY TABLE TEST (TEST_TYPE STRING, START_TIMESTAMP TIMESTAMP_NTZ, END_TIMESTAMP TIMESTAMP_NTZ);

INSERT INTO TEST
SELECT COLUMN1 AS TEST_TYPE, COLUMN2::TIMESTAMP_NTZ AS START_TIMESTAMP, COLUMN3::TIMESTAMP_NTZ AS END_TIMESTAMP
 FROM (VALUES('OUTSIDE LEFT', '2020-01-01 00:00:00 +0000', '2020-01-02 00:00:00 +0000'),  
             ('OUTSIDE RIGHT', '2020-02-24 00:00:00 +0000', '2020-02-25 00:00:00 +0000'),  
             ('STRADDLE LEFT', '2020-01-15 00:00:00 +0000', '2020-02-02 00:00:00 +0000'),  
             ('STRADDLE RIGHT', '2020-02-10 00:00:00 +0000', '2020-02-25 00:00:00 +0000'),  
             ('INSIDE', '2020-02-10 00:00:00 +0000', '2020-02-11 00:00:00 +0000'),  
             ('STRADDLE BOTH SIDES', '2020-01-01 00:00:00 +0000', '2020-03-25 00:00:00 +0000')
      );
      
SELECT *
  FROM TEST
 WHERE (END_TIMESTAMP > $FILTER_START AND START_TIMESTAMP < COALESCE($FILTER_END,DATEADD(DAY, 1, $FILTER_START)));

How To: Linear Regression for Big Data in Snowflake

BI tools like Looker will do the linear regression on small datasets and are performed in memory.   As awesome as these tools like this are, they are limited to small datasets.    And when you need to feed that data into a gradient boosting tool like XG Boost, you will most likely want to perform a linear regression in SQL.   

Below are two examples.  One that can be ran in any DB that supports CTEs and the other can be ran in Snowflake.  I have also projected out the trend line beyond the sample data to show you how to do simple projections.  Also I throw in R2 so you can evaluate the model fit. 

Sample Data

CREATE OR REPLACE TEMPORARY TABLE DATA_SAMPLE (X INTEGER,Y INTEGER);

INSERT INTO DATA_SAMPLE (X,Y)

VALUES (1,41),

       (2,963),

       (3,4506),

       (4,13557),

       (5,40422),

       (6,61484),

       (7,85075),

       (8,118436),

       (9,134457),

       (10,180253);

Generic SQL Logic

WITH X_Y_AVG AS (

    SELECT   X

           , Y

           , AVG(X) OVER() AS X_AVG

           , AVG(Y) OVER() AS Y_AVG

      FROM DATA_SAMPLE

)

, SLOPE AS (

    SELECT   SUM((X - X_AVG) * (Y - Y_AVG)) / SUM((X - X_AVG) * (X - X_AVG)) AS SLOPE

           , MAX(X_AVG) AS X_AVG_MAX

           , MAX(Y_AVG) AS Y_AVG_MAX

      FROM X_Y_AVG

)

, CALC AS (

    SELECT   SLOPE

           , Y_AVG_MAX - X_AVG_MAX * SLOPE AS INTERCEPT

      FROM SLOPE

)

, TREND_LINE AS (

        SELECT   X_Y_AVG.X

               , X_Y_AVG.Y

               , X_Y_AVG.X_AVG

               , X_Y_AVG.Y_AVG

               , CALC.SLOPE

               , CALC.INTERCEPT

               , (X_Y_AVG.X * CALC.SLOPE) + CALC.INTERCEPT AS TREND_LINE_Y

          FROM X_Y_AVG

          CROSS JOIN CALC

         UNION ALL

        SELECT   PROJECTION_X.X

               , NULL

               , PROJECTION_X.X

               , NULL

               , CALC.SLOPE

               , CALC.INTERCEPT

               , (PROJECTION_X.X * CALC.SLOPE) + CALC.INTERCEPT AS TREND_LINE_Y

          FROM (SELECT COLUMN1 AS X

                  FROM VALUES  (11),

                               (12),

                               (13),

                               (14),

                               (15),

                               (16)) AS PROJECTION_X 

          CROSS JOIN CALC

         ORDER BY X

  )

, RSQUARED AS (

    SELECT 1-(SUM(POWER(Y - TREND_LINE_Y,2)))/(SUM(POWER(Y - Y_AVG,2))) AS R2

      FROM TREND_LINE

)

SELECT   TREND_LINE.X

       , TREND_LINE.Y

       , TREND_LINE.SLOPE

       , TREND_LINE.INTERCEPT

       , TREND_LINE.TREND_LINE_Y

       , IFF(TREND_LINE.Y IS NOT NULL, (SELECT R2 FROM RSQUARED), NULL) AS R2

  FROM TREND_LINE

;    

Snowflake Specific SQL Logic Using REGR_SLOPE & REGR_INTERCEPT Functions

WITH CALC AS (

    SELECT   REGR_SLOPE(Y, X) AS SLOPE

           , REGR_INTERCEPT(Y, X) AS INTERCEPT

     FROM DATA_SAMPLE

)

SELECT   DATA_SAMPLE.X

       , DATA_SAMPLE.Y

       , CALC.SLOPE

       , CALC.INTERCEPT

       , (DATA_SAMPLE.X * CALC.SLOPE) + CALC.INTERCEPT AS TREND_LINE_Y

       , REGR_R2(DATA_SAMPLE.Y, "TREND_LINE_Y") OVER() AS R2

  FROM DATA_SAMPLE

  CROSS JOIN CALC

 UNION ALL

SELECT   PROJECTION_X.X

       , NULL

       , CALC.SLOPE

       , CALC.INTERCEPT

       , (PROJECTION_X.X * CALC.SLOPE) + CALC.INTERCEPT AS TREND_LINE_Y

       , NULL

  FROM (SELECT COLUMN1 AS X

          FROM VALUES  (11),

                       (12),

                       (13),

                       (14),

                       (15),

                       (16)) AS PROJECTION_X 

  CROSS JOIN CALC

  ORDER BY X;  

How To: Snowflake Data Ingestion Techniques

There are many ways to ingest data.  The below list of techniques are not an exhaustive list, but more of a list of some simple "Goto" techniques for your "Toolbox".    Without belaboring the subject, your ingestion technique will depend on wither your data is CSV, JSON, XML, Fix String, or Parquet files and wither it is a high or low Volume, Variety, Velocity dataset, and wither your reporting is "Real-Time", Hourly, Daily, Weekly, or Monthly cadence.  Below, I have provided three basic techniques that I have in my "Toolbox" that have evolved over the last 5 years using Snowflake on various ingestion challenges.

Technique A: I typically use this technique for ingesting data from an ERP, CRM, or Data Mastering Databases in which the volume or velocity are not extreme.   

1, 2, 3:  You would create a pull script (2) that will bulk pull the data from a table from a database (1) using some change control timestamp in the table (If available) or change control log in the database system itself. Depending on volume and change control available for the table, you can choose to pull the whole table each time or only pull new and changed records and place into a Parquet or CSV file (3).  Change control methods vary depending on the database or table design.   

4:  You then COPY INTO the file into Snowflake (4).  I like ingesting Parquet files, because they are compressed and ingest extremely fast into Snowflake.     

5:  Dedup the table (5).  This is where you need to know the Primary Key of the table.   If you need, you can treat the table like a Slowly Changing Dimension and perform a non-destructive method for identifying the most current records and deprecating the duplicates.   I typically use a soft delete timestamp.  This allows me a window into historical changes.   If its a high volume table and the changes are high volume you may wish to perform a hard delete.

6:  Publish (6) the table in the Store when all other supporting tables are also ready to be published.  Publish tables a group were any dependent tables published at the same time.  Snowflake's cloning process happens extremely fast, so any read blocking that you might incur will not be noticeable.   Make sure you use Snowflake's ability to keep the security grants when using the CREATE OR REPLACE statement on an existing table.

7, 8:  You will then create a VIEW(7) that transforms the data into a shape that fits your target table for the warehouse.   Selecting against that  VIEW you can either do an incremental Insert or do a FULL replacement of the table via CREATE TABLE (8).  For small tables you can just replace the whole table and save yourself the complexity of change control and incremental processing. 

9: Clone the Staging Table into the published schema area (8).    

Technique B:  This technique I typically use when I run across JSON, XML, or Fix String files to ingest.   Its like technique A, but it has an extra transformation and table staging step to flatten the semi-structured data and place into a staging table(s).

5.2, 5.4:  This is where you would create a VIEW (5.2) that would flatten the JSON or XML, or perform some special handling on the Fixed Length String file.    And then you would incrementally insert or do a full replaced of the staging table (5.4).  Then proceed on to the other steps to further transform and publish.

Technique C:  This technique is especially useful when you ingest a high volume table into Snowflake.   Its like technique A from step 1-6, but no transformations are performed.  You go straight to publishing via CLONING.  This technique assumes that you can trust the upstream data source for data quality & that the shape is exactly what you will be using for reporting.  For really fast ingesting I would suggest ingesting Parquet files.    

Please note that when dealing with High Volume, High Velocity data, that there are several other techniques that can help you perform your intense operational or business reporting cadence.  The above lists of techniques is not an exhaustive list, but more of a list of some "Goto" techniques for your "Toolbox".  

Proven Snowflake Data Mesh Design Suggestions

 

I have found that Snowflake is an extremely fast and easy way to get your enterprise data warehouse up in the cloud in a secure way and supports futuristic design ideas like "Data Meshing".   Snowflake has been extremely fast and reliable for my projects, even for high volume projects with a daily ingestion of 275 million records a day or more.   

1. I highly suggest you divide your warehouse database into separate schemas.   This will enable you to keep the noise down for analysts and developers maintaining the warehouse and speed up delivery.    Each schema representing a specific state of the data.  There are two types of schemas:  Staging and Published.   And then there are 3 different schemas I would recommend:  Config (or you a name it Reference), Store,  and Insight schemas.   Staging schema type represents the RAW state of the data as well as it represents the data possibly being in a flux.  Note that Staging schema type is applied across all the schemas:  Config,  Store,  and Insight.   Each of these schemas will have a state of the data that is in flux as its being transformed.  The Store schema represents the published cleaned up, deduplicated, json-> table flattened, and the data is safe to be consumed by down stream services or analysts.  
2. The Insight schema is the highly modeled design that is shaped and aggregated in a way that is ready to be used for reporting.   Notice that is also has its own Staging.
3. The Config or Reference schema is a place to warehouse your configuration meta data and data mastery data for domains & hierarchies.
4. Each domain would export out of their transactional database into an S3 Bucket path.  Data ingestion into Snowflake via AWS S3 is extremely simple to do.  Snowflake processes many types of data formats like Parquet, JSON, or CSV files with ease.   
5. In the spirit of supporting "Data Mesh" concepts, you may find it more convenient all around to create separate warehouse databases for each business domain which are separated from the main enterprise reporting warehouse.   This reduces what the main enterprise reporting warehouse needs to publish to only the necessary aggregates.  Deeper analysis can be done by accessing the specific domain database.   Using the Data Mesh approach reduces bottlenecks by enabling you to scale out your teams independently for development, maintenance, OPS, and subject matter expertise for each business domain without introducing in heavy dependencies.
6. Most warehouses need some kind of data mastery in which data is created by the company and change controlled.  I have explored all kinds of means to master data.   But I find it the most simple to create an AWS RDS db, model the data you need, and place the necessary fields for change control.  As for UI needs, if the changes are not high volume, I wouldn't bother.   Make a change order process and have a DBA make the necessary changes.  But if it is high volume, then you should create a UI purpose built for mastering data by a team of people.   
7. Complex Warehouses will have some kind of configurations that drive reporting to the end customer.   This is like Master Data except this is more for pipeline control and setting arguments like thresholds, percentage ranges, etc...
8. You may need to process unstructured data or make predictions based on data in the warehouse. Such tools do not run within the Snowflake Environment.
• If you have non-structured data like video or audio or images, you will probably be using some kind of Machine Learning for image and audio processing and then import the results into your warehouse.
• If you have a need to make predictions, you will probably be using some kind of gradient boosting tools like XG-BOOST.   You'll be exporting data from the warehouse and importing the inference model back into your warehouse that then can be used to make predictions.
9. Business Intelligence Reporting tools are essential within the enterprise.   I personally have found that Looker is an excellent match with Snowflake and I have been able to deliver some extremely complex reporting.   Creating new reports using tools like Looker, is orders of magnitude faster than building reports using a web dev.
10. Data Quality is critical for a data warehouse.  I would start with validating all results for the basics (G.O.L.D.) then work on other tolerance checks:
• Gaps in the data
• Orphaned dimensions
• Late datasets
• Duplicate Records
11. Snowflake makes it extremely easy to do cloud sharing.  You can share internal or external to the company.   This makes it easy for analysts, systems, or partners across the company that need to import data to go directly to the warehouse and export what is needed based on their security rights.

As for processing pipelines and creating job schedules, I have found it most useful to build as much of the processes as close to the data as possible.  Creating Stored Procedures and executing them using Snowflake's Task Scheduler makes the data warehouse self contained when not dependent on having to pull data from API's,  other non Snowflake DBs, or other Non-Snowflake accessible file locations.   When creating pulls from other data sources like an API or a DB, I would suggest using Python and a Cron Scheduler and place the logic package in an a cheap AWS EC2 instance.   Most data services have example Python Logic for pulling data from their API that you can copy and paste.  You might also find that Databricks can be of great value here as well and also adds an additional value by enabling you to apply other complex logic like XG BOOST, Neural Networks, NLP and the like.   Over the years, I found that complex ETL services and tools are not required and add a level of unnecessary expense.  Personally, I would rather spend that money on more Snowflake and Databrick processing time.  But saying that, having a modern DAG job manager can be useful if you keep the design elegant.

Baseline Conceptual ERP Data Model

Baseline ERP Conceptual Model by Scott Thornton.pdf

This model is a pdf file that is more detailed (down to attributes) that this website already lays out.   It defines the information and informational relationships a business will have to function as a business.   Anyone working with data will like this model, as it will help you to understand how business data ties together into a relational data model.  

How To: Sequence SQL Tables based on Dependency

There are many times in which you may need to know the sequence of tables based on dependencies in order to process data without foreign constraint issues and without turning off those constraints.  You'll need a list of tables from the most dependent table to the least dependent table.   The process below provides an example in how to provide such a list.


CREATE FUNCTION dbo.GetTableSequence ()
RETURNS @Temp2 Table
(  Sequence int Primary key Identity
 , ObjectID int
 , SchemaName sysname
 , TableName sysname)
AS
BEGIN
DECLARE @Temp1 Table
(  ForeignObjectID int
 , ForeignSchemaName sysname
 , ForeignTableName sysname
 , ObjectID int
 , SchemaName sysname
 , TableName sysname)

-----------------------------------------------------
-- Identify Tables with Depedency on another table
-----------------------------------------------------
Insert into @Temp1 (ForeignObjectID, ForeignSchemaName, ForeignTableName, ObjectID, SchemaName, TableName)
Select Distinct O.Object_ID as ForeignObjectID, Cast(schema_Name (o.Schema_ID) as sysname) as ForeignSchemaName, cast(o.name as sysname) as ForeignTableName,
                o2.Object_ID as ObjectID, Cast(schema_Name (o2.Schema_ID) as sysname) as SchemaName, cast(o2.Name as sysname) as TableName
From sys.foreign_keys f
Join sys.objects o
 on f.referenced_object_id = o.object_id
Join sys.objects o2
    on f.parent_object_id = o2.object_id
Where O.Name <> O2.Name -- Exclude Child-Parent Relationships (These causes infinite loops)
---------------------------------------------------------------------------------------
-- Recursively Identify Tables from Most Dependent to least Dependent
---------------------------------------------------------------------------------------
;
with RecursionCTE (ObjectId, SchemaName, TableName)
as
(-- First Get all tables that are not dependent on any other table. (Table can depend on them but not the other way a round)
SELECT T.ObjectID, T.SchemaName, T.TableName
FROM (Select   Distinct O.Object_ID as ObjectID
    , Cast(schema_Name (o.Schema_ID) as sysname) as SchemaName
    , Cast(o.name as sysname) as TableName
  From sys.objects o
   Join sys.columns c
     on c.object_id = o.object_id
  Where o.type = 'u') as T
LEFT OUTER JOIN sys.foreign_key_columns sfk
   ON sfk.referenced_object_id = T.ObjectID
Where sfk.referenced_object_id is null
union all
--  Now recursively work through all dependencies
select R1.ForeignObjectID, R1.ForeignSchemaName, R1.ForeignTableName
FROM @Temp1 as R1
join RecursionCTE as R2 on R1.TableName = R2.TableName
)
Insert Into @Temp2(ObjectId, SchemaName, TableName)
 select ObjectID, SchemaName, TableName
  from RecursionCTE
;
----------------------------------------------------
-- Delete Duplicates but keep the very last entry
----------------------------------------------------
WITH Dubs(ROWID, RecordID, SchemaName, TableName) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY TableName ORDER BY Sequence DESC) as ROWID, Sequence, SchemaName, TableName
FROM @Temp2
)
DELETE FROM Dubs
WHERE ROWID > 1;
RETURN
END

How To: Naturalize (Flatten) Child/Parent Hierarchies for use as Dimensions

I keep getting the need to quickly Naturalize hierachies for analysis. So here is a sample script that accomplishes this:


set nocount on
declare @Temp1 table
(
RecordID int Primary key NOT NULL ,
ParentRecordID int,
Description nvarchar(1000)
)
/* Start loading of test data */
insert into @Temp1 values(1,null,'CEO')
insert into @Temp1 values(2,1,'VP Marketing')
insert into @Temp1 values(3,1,'VP Operations')
insert into @Temp1 values(4,2,'Marketing Director - Direct Mail')
insert into @Temp1 values(5,2,'Marketing Director - TV')
insert into @Temp1 values(6,1,'VP Research')
insert into @Temp1 values(7,4,'Human Resources Director')
insert into @Temp1 values(8,4,'Program Manager')
insert into @Temp1 values(9,6,'Research Analyst')
set nocount off;
with RecursionCTE (RecordID,ParentRecordID, Description, TOC, L1ID, Level1, L2ID, Level2, L3ID, Level3, L4ID, Level4, LevelCount)
as
(
select RecordID,ParentRecordID, Description, convert(varchar(1000),Description) TOC, RecordID as L1ID, Description as Level1, CONVERT(INT, NULL) as L2ID, Convert(nvarchar(1000), '') as Level2, CONVERT(INT, NULL) as L3ID, Convert(nvarchar(1000), '') as Level3, CONVERT(INT, NULL) as L4ID, Convert(nvarchar(1000), '') as Level4, Convert(int, 1) as LevelCount
from @Temp1
where ParentRecordID is null
union all
select R1.RecordID,
R1.ParentRecordID,
R1.Description,
case when DataLength(R2.TOC) > 0
then convert(varchar(1000),R2.TOC + '->'
+ cast(R1.Description as varchar(100)))
else convert(varchar(1000),
cast(R1.Description as varchar(100)))
end as TOC,
L1ID = R2.L1ID,
Level1 = R2.Level1,
L2ID = convert(INT, CASE WHEN R2.LevelCount = 1 THEN R1.RecordID ELSE R2.L2ID END),
Level2 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 1 THEN R1.Description ELSE R2.LEVEL2 END),
L3ID = convert(INT, CASE WHEN R2.LevelCount = 2 THEN R1.RecordID ELSE R2.L3ID END),
Level3 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 2 THEN R1.Description ELSE R2.LEVEL3 END),
L4ID = convert(INT, CASE WHEN R2.LevelCount = 3 THEN R1.RecordID ELSE R2.L4ID END),
Level4 = convert(nvarchar(1000), CASE WHEN R2.LevelCount = 3 THEN R1.Description ELSE R2.LEVEL4 END),
LevelCount = R2.LevelCount + 1
from @Temp1 as R1
join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
)
select * from RecursionCTE

How To: Delete Duplicate Data (DeDup)

ETL processes typically will run across the need to delete duplicate records. The fastest and simplist way I know of is to create a Primary Key (or unique index) on the table with the instruction to IGNORE_DUP_KEY=ON . Then when you bulk copy or insert data into the table, SQL quickly ingores the duplicates.

Example:

CREATE TABLE #Temp (ID INT NOT NULL)

ALTER TABLE #Temp
ADD PRIMARY KEY (ID ASC)
WITH (IGNORE_DUP_KEY = ON)
GO

INSERT INTO #Temp (ID)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 1

SELECT * FROM #Temp

DROP TABLE #Temp

But sometimes you need to delete duplicate data in a more controlled and auditable way. Here is an approach that does it in a single set statement.

Example:

CREATE TABLE #Temp(ID INT, Name VARCHAR(255))
INSERT INTO #Temp (ID, Name)
SELECT 1, 'Name 1'
UNION ALL
SELECT 2, 'Name 2'
UNION ALL
SELECT 3, 'Name 3'
UNION ALL
SELECT 4, 'Name 4'
UNION ALL
SELECT 5, 'Name 2'
UNION ALL
SELECT 6, 'Name 1'
UNION ALL
SELECT 7, 'Name 3'
UNION ALL
SELECT 8, 'Name 5'
UNION ALL
SELECT 9, 'Name 4'
UNION ALL
SELECT 10, 'Name 4'

SELECT 'Deleted Data Set'
DELETE FROM #TEMP
OUTPUT DELETED.* -- Display the Deleted Rows
FROM #TEMP T
JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY #TEMP.Name ORDER BY #TEMP.ID) as ROWID, #TEMP.*
FROM #TEMP
) AS DupRows -- Find Dubs and assign rowID which resets for each new name
ON DupRows.ID = T.ID
WHERE DupRows.ROWID > 1


-- OR Here is an even more eligant solution --


;
WITH Dubs(ROWID, ID, Name) AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY #TEMP.Name ORDER BY #TEMP.ID) as ROWID, #TEMP.*
FROM #TEMP
)
DELETE FROM Dubs
OUTPUT DELETED.* -- Display the Deleted Rows
WHERE ROWID > 1 ;
SELECT 'Clean Data Set'
SELECT * FROM #TEMP
DROP TABLE #TEMP
GO

How To: Sessionize Events

This is a how to do sessionizing using SQL with just a set of events. We use alot of self-joins to accomplish this task.

Steps:

• Determine your session timeout length.
• Order events by date.
• Identify the Exit Point Event and the duration between events. The Exit Point is the event that has a duration greater than the session timeout length.
• Finally you can identify the sessionID and assign a sequence number for each event within a session.

-- HOW TO: SESSIONIZING EVENTS
-- This is an example of how to implicitly determine sessions with just a set of event date/times.

--------------------------------------------------------------------
-- Setup test scenerio.
--------------------------------------------------------------------
-- DROP TABLE WebLog CREATE TABLE WebLog(UserID INT NOT NULL, EventDate DATETIME NOT NULL)
DECLARE @SessionTimeOut INT
SET @SessionTimeOut = 1800 -- 60 seconds * 30 minutes
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:01:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:02:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:03:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:06:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:10:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:20:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 01:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:30:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 00:31:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:32:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:33:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:10');
INSERT INTO WebLog VALUES (123,'01-jan-2005 03:34:30');
INSERT INTO WebLog VALUES (123,'01-jan-2005 04:00:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:01:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:02:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:03:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:04:00');
INSERT INTO WebLog VALUES (123,'01-jan-2005 05:05:00');

INSERT INTO WebLog VALUES (121,'01-jan-2005 00:01:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:02:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:03:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:06:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:10:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:20:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 01:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:30:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 00:31:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:32:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:33:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:10');
INSERT INTO WebLog VALUES (121,'01-jan-2005 03:34:30');
INSERT INTO WebLog VALUES (121,'01-jan-2005 04:00:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:01:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:02:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:03:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:04:00');
INSERT INTO WebLog VALUES (121,'01-jan-2005 05:05:00');
DECLARE @WebLogWithRowID TABLE (UserID INT NOT NULL,
EventDate DATETIME NOT NULL,
RowID BIGINT NOT NULL)

DECLARE @WebLogExitPoint TABLE (UserID INT NOT NULL,
EventDate DATETIME NOT NULL,
RowID BIGINT NOT NULL,
Duration BIGINT NOT NULL,
ExitPointFlag BIT NOT NULL)
--------------------------------------------------------------------
-- Assign Record ID
--------------------------------------------------------------------
INSERT INTO @WebLogWithRowID(UserID, EventDate, RowID)
SELECT UserID, EventDate, ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY EventDate) RowID
FROM WebLog
--------------------------------------------------------------------
-- Determine Duration and ExitPoint
--------------------------------------------------------------------
INSERT INTO @WebLogExitPoint(UserID, EventDate, RowID, Duration, ExitPointFlag)
SELECT a.UserID, a.EventDate, a.RowID,
ISNULL(DATEDIFF(SECOND, a.EventDate, b.EventDate), @SessionTimeOut) AS Duration,
CASE WHEN b.UserID IS NULL THEN 1 -- Last record
WHEN DATEDIFF(SECOND, a.EventDate, b.EventDate) > @SessionTimeOut THEN 1
ELSE 0
END AS ExitPointFlag
FROM @WebLogWithRowID a
LEFT OUTER JOIN @WebLogWithRowID b
ON b.RowID = a.RowID + 1
AND b.UserID = a.UserID

--------------------------------------------------------------------
-- Determine SeqNumber within Session and SessionID
--------------------------------------------------------------------
SELECT a.UserID, a.EventDate, a.RowID,
a.RowID - IsNULL(b.RowID, 0) AS SeqNumber,
a.Duration,
a.ExitPointFlag,
ISNULL(b.RowID, 1) AS SessionID
FROM @WebLogExitPoint a
LEFT OUTER JOIN @WebLogExitPoint b
ON b.RowID = (SELECT MAX(c.RowID)
FROM @WebLogExitPoint c
WHERE c.RowID < a.RowID
AND c.ExitPointFlag = 1 )
AND b.UserID = a.UserID

How To: Hierarchal Lookups Without a Cursor

Reporting on hierarchal tables (Child/Parent relationship) tables can be a huge time bottleneck when using an iterative cursor process. Here is a speedy way to look up hierarchal information.

Original Hierarchal Table
Traditionally hierarchal tables self reference its own primary key and giving it a parent role name. Example:

Person
PersonID (PK) , Name (Attributes) , ParentPersonID (Self Ref. Person.PersonID FK)
1 , John Doe , NULL
2 , Tom Doe , 1
3 , Jill Doe , 1
4 , Harry Doe , 2
5 , Jim Smith , NULL

Hierarchal Index Table
The key is building an index table that maps a parent to every child, grand child, great grand child, etc... and assign the generation it belongs to in relationship. Example:

PersonHierarchyIndex
AncestoryID (Person.PersonID PK, FK), ChildID (Person.PersonID PK, FK), GenerationLevel1 (John Doe) , 1 (John Doe), 0
1 (John (Doe), 2 (Tom Doe), 1
1 (John Doe), 3 (Jill Doe), 1
1 (John Doe), 4 (Harry Doe), 2
2 (Tom Doe), 2 (Tom Doe), 0
2 (Tom Doe), 4 (Harry Doe), 1
3 (Jill Doe),2 (Jill Doe), 0
4 (Harry Doe), 4 (Harry Doe), 0
5 (Jim Smith), 5 (Jim Smith), 0

Index Usage
With the aid of the hierarchal index you will not need a cursor for your reports. Example:

-- Get all Progeny (Children, Grand Children, etc…)
Select Person.Name, Parent.Name, Index.GenerationLevel
From PersonHierarchyIndex
Join Person
On Person.PersonID = PersonHierarchyIndex.ChildID
And ParentHierarchyIndex.AncestoryID = 1 -- (John Doe)
Join Person Parent
On Parent.PersonID = PersonHierarchyIndex.AncestoryID

Index Population
The following example uses the new feature in 2005 SQL Server. We populate the Index using a recursive query using a CTE (Common Table Expression). It is used to build the index at the time of creating or modifying the hierarchy. Most hierarchies are slowly changing domain data. With that in mind it makes sense to take the cost of building the index at the time when the domain changes rather then at the time of selecting from the hierarchy to create your reports.

Use this function to then insert into the PersonHierarchyIndex table:

-- Recursive Query using Common Table Expression
CREATE Function dbo.BuildPersonHierarchyIndex () RETURNS Table
ASRETURN(
WITH AncestoryTree (PPID, PID, PersonID, ParentPersonID, DirectChild, GenerationLevel)
AS (SELECT PPID = Person.ParentPersonID, PID = Person.PersonID, Person.PersonID, Person.ParentPersonID , DirectChild = 1, GenerationLevel = 1
FROM Person
UNION ALL
SELECT AncestoryTree.PPID, AncestoryTree.PID, Person.PersonID, Person.ParentPersonID, DirectChild = 0, GenerationLevel = AncestoryTree.GenerationLevel + 1
FROM Person
JOIN AncestoryTree
ON AncestoryTree.ParentPersonID = Person.PersonID
WHERE Person.PersonID <> Person.ParentPersonID)
SELECT PersonID = AncestoryTree.PID, AncestoryTree.ParentPersonID, AncestoryTree.DirectChild, AncestoryTree.GenerationLevel
FROM AncestoryTree
WHERE AncestoryTree.PPID IS NOT NULL
UNION ALL
SELECT PersonID, PersonID, 0, 0
FROM Person

--OPTION (MAXRECURSION 200) -- Default is 100: Make sure the recursion limit is set high enough
);