...continued from previous post
Generated SQL code for our custom PIT template will look like...
for the ETL code:
CREATE OR REPLACE PROCEDURE "VAULTSPEED_HANDSON_PROC"."BV_ETL_PIT_CUSTOMERS_PIT"()
RETURNS varchar
LANGUAGE JAVASCRIPT
AS $$
/*
__ __ _ _ _ __ ___ __ __
\ \ / /_ _ _ _| | |_ ___ ____ ___ ___ __| | \ \/ _ \/ / /_/
\ \ / / _` | | | | | __/ __| _ \ / _ \/ _ \/ _` | \/ / \ \/ /\
\ V / (_| | |_| | | |_\__ \ |_) | __/ __/ (_| | / / \/\ \/ /
\_/ \__,_|\__,_|_|\__|___/ .__/ \___|\___|\__,_| /_/ \/_/\__/
|_|
Vaultspeed version: 5.2.1.1, generation date: 2022/12/14 13:10:09
DV_NAME: VS_DEMO_DV_SNW - Release: DV_R1(1) - Comment: first data vault release - Release date: 2022/09/26 21:35:06,
BV release: BVR-3(3) - Comment: demo - Release date: 2022/09/27 14:33:59,
SRC_NAME: VISITS - Release: VISITS(1) - Comment: release 1 - Release date: 2022/09/26 21:30:06
*/
var POINT_IN_TIME = snowflake.createStatement( {sqlText: `
INSERT INTO "VS_DEMO_DV_SNW_BV"."PIT_CUSTOMERS"(
"PIT_HKEY"
,"CUSTOMERS_HKEY"
,"SNAPSHOT_DATE"
,"END_SNAPSHOT_DATE"
,"SAT_CUSTOMERS_HKEY_SAT_VST_CUSTOMERS"
,"SAT_CUSTOMERS_HKEY_SAT_SLS_CUSTOMERS_GDPR"
,"SAT_CUSTOMERS_HKEY_SAT_SLS_CUSTOMERS_GENERAL"
,"LOAD_DATE_SAT_VST_CUSTOMERS"
,"LOAD_DATE_SAT_SLS_CUSTOMERS_GDPR"
,"LOAD_DATE_SAT_SLS_CUSTOMERS_GENERAL"
)
WITH "SNAPSHOTDATES" AS
(
SELECT
DISTINCT
"SAT_SRC1"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "SAT_SRC1"."LOAD_DATE" AS "SNAPSHOT_DATE"
FROM "VS_DEMO_DV_SNW_FL"."SAT_VST_CUSTOMERS" "SAT_SRC1"
UNION
SELECT
DISTINCT
"SAT_SRC2"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "SAT_SRC2"."LOAD_DATE" AS "SNAPSHOT_DATE"
FROM "VS_DEMO_DV_SNW_FL"."SAT_SLS_CUSTOMERS_GDPR" "SAT_SRC2"
UNION
SELECT
DISTINCT
"SAT_SRC3"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "SAT_SRC3"."LOAD_DATE" AS "SNAPSHOT_DATE"
FROM "VS_DEMO_DV_SNW_FL"."SAT_SLS_CUSTOMERS_GENERAL" "SAT_SRC3"
)
, "SAT_TGT1" AS
(
SELECT
"SAT_SRC1"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "SAT_SRC1"."LOAD_DATE" AS "LOAD_DATE"
, COALESCE(LEAD("SAT_SRC1"."LOAD_DATE")OVER(PARTITION BY "SAT_SRC1"."CUSTOMERS_HKEY" ORDER BY "SAT_SRC1"."LOAD_DATE")
, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS')) AS "ENDING_LOAD_TIMESTAMP"
FROM "VS_DEMO_DV_SNW_FL"."SAT_VST_CUSTOMERS" "SAT_SRC1"
)
, "SAT_TGT2" AS
(
SELECT
"SAT_SRC2"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "SAT_SRC2"."LOAD_DATE" AS "LOAD_DATE"
, COALESCE(LEAD("SAT_SRC2"."LOAD_DATE")OVER(PARTITION BY "SAT_SRC2"."CUSTOMERS_HKEY" ORDER BY "SAT_SRC2"."LOAD_DATE")
, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS')) AS "ENDING_LOAD_TIMESTAMP"
FROM "VS_DEMO_DV_SNW_FL"."SAT_SLS_CUSTOMERS_GDPR" "SAT_SRC2"
)
, "SAT_TGT3" AS
(
SELECT
"SAT_SRC3"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "SAT_SRC3"."LOAD_DATE" AS "LOAD_DATE"
, COALESCE(LEAD("SAT_SRC3"."LOAD_DATE")OVER(PARTITION BY "SAT_SRC3"."CUSTOMERS_HKEY" ORDER BY "SAT_SRC3"."LOAD_DATE")
, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS')) AS "ENDING_LOAD_TIMESTAMP"
FROM "VS_DEMO_DV_SNW_FL"."SAT_SLS_CUSTOMERS_GENERAL" "SAT_SRC3"
)
, "PIT_CHECK" AS
(
SELECT
"PIT_TGT"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "PIT_TGT"."SNAPSHOT_DATE" AS "SNAPSHOT_DATE"
FROM "VS_DEMO_DV_SNW_BV"."PIT_CUSTOMERS" "PIT_TGT"
)
SELECT
UPPER(SHA1_HEX( "HUB_SRC"."CUSTOMERS_HKEY" || '\\#' || TO_CHAR("SNAPSHOTDATES"."SNAPSHOT_DATE",
'DD/MM/YYYY HH24:MI:SS.FF') )) AS "PIT_HKEY"
, "HUB_SRC"."CUSTOMERS_HKEY" AS "CUSTOMERS_HKEY"
, "SNAPSHOTDATES"."SNAPSHOT_DATE" AS "SNAPSHOT_DATE"
, COALESCE(LEAD("SNAPSHOTDATES"."SNAPSHOT_DATE")OVER(PARTITION BY "HUB_SRC"."CUSTOMERS_HKEY" ORDER BY "SNAPSHOTDATES"."SNAPSHOT_DATE")
, TO_TIMESTAMP('31/12/2999 23:59:59', 'DD/MM/YYYY HH24:MI:SS')) AS "END_SNAPSHOT_DATE"
, COALESCE("SAT_TGT1"."CUSTOMERS_HKEY","UNSAT_TGT1"."CUSTOMERS_HKEY") AS "SAT_CUSTOMERS_HKEY_SAT_VST_CUSTOMERS"
, COALESCE("SAT_TGT2"."CUSTOMERS_HKEY","UNSAT_TGT2"."CUSTOMERS_HKEY") AS "SAT_CUSTOMERS_HKEY_SAT_SLS_CUSTOMERS_GDPR"
, COALESCE("SAT_TGT3"."CUSTOMERS_HKEY","UNSAT_TGT3"."CUSTOMERS_HKEY") AS "SAT_CUSTOMERS_HKEY_SAT_SLS_CUSTOMERS_GENERAL"
, COALESCE("SAT_TGT1"."LOAD_DATE","UNSAT_TGT1"."LOAD_DATE") AS "LOAD_DATE_SAT_VST_CUSTOMERS"
, COALESCE("SAT_TGT2"."LOAD_DATE","UNSAT_TGT2"."LOAD_DATE") AS "LOAD_DATE_SAT_SLS_CUSTOMERS_GDPR"
, COALESCE("SAT_TGT3"."LOAD_DATE","UNSAT_TGT3"."LOAD_DATE") AS "LOAD_DATE_SAT_SLS_CUSTOMERS_GENERAL"
FROM "VS_DEMO_DV_SNW_FL"."HUB_CUSTOMERS" "HUB_SRC"
INNER JOIN "SNAPSHOTDATES" "SNAPSHOTDATES" ON "SNAPSHOTDATES"."CUSTOMERS_HKEY" = "HUB_SRC"."CUSTOMERS_HKEY"
LEFT OUTER JOIN "PIT_CHECK" "PIT_CHECK" ON "PIT_CHECK"."CUSTOMERS_HKEY" = "HUB_SRC"."CUSTOMERS_HKEY" AND "SNAPSHOTDATES"."SNAPSHOT_DATE" = "PIT_CHECK"."SNAPSHOT_DATE"
LEFT OUTER JOIN "SAT_TGT1" "SAT_TGT1" ON "HUB_SRC"."CUSTOMERS_HKEY" = "SAT_TGT1"."CUSTOMERS_HKEY" AND "SNAPSHOTDATES"."SNAPSHOT_DATE" >= "SAT_TGT1"."LOAD_DATE" AND
"SNAPSHOTDATES"."SNAPSHOT_DATE" < "SAT_TGT1"."ENDING_LOAD_TIMESTAMP"
LEFT OUTER JOIN "SAT_TGT2" "SAT_TGT2" ON "HUB_SRC"."CUSTOMERS_HKEY" = "SAT_TGT2"."CUSTOMERS_HKEY" AND "SNAPSHOTDATES"."SNAPSHOT_DATE" >= "SAT_TGT2"."LOAD_DATE" AND
"SNAPSHOTDATES"."SNAPSHOT_DATE" < "SAT_TGT2"."ENDING_LOAD_TIMESTAMP"
LEFT OUTER JOIN "SAT_TGT3" "SAT_TGT3" ON "HUB_SRC"."CUSTOMERS_HKEY" = "SAT_TGT3"."CUSTOMERS_HKEY" AND "SNAPSHOTDATES"."SNAPSHOT_DATE" >= "SAT_TGT3"."LOAD_DATE" AND
"SNAPSHOTDATES"."SNAPSHOT_DATE" < "SAT_TGT3"."ENDING_LOAD_TIMESTAMP"
INNER JOIN "VISITS_MTD"."MTD_EXCEPTION_RECORDS" "MEX_SRC" ON "MEX_SRC"."RECORD_TYPE" = 'U'
INNER JOIN "VS_DEMO_DV_SNW_FL"."SAT_VST_CUSTOMERS" "UNSAT_TGT1" ON "MEX_SRC"."LOAD_CYCLE_ID"::integer = "UNSAT_TGT1"."LOAD_CYCLE_ID"
INNER JOIN "VS_DEMO_DV_SNW_FL"."SAT_SLS_CUSTOMERS_GDPR" "UNSAT_TGT2" ON "MEX_SRC"."LOAD_CYCLE_ID"::integer = "UNSAT_TGT2"."LOAD_CYCLE_ID"
INNER JOIN "VS_DEMO_DV_SNW_FL"."SAT_SLS_CUSTOMERS_GENERAL" "UNSAT_TGT3" ON "MEX_SRC"."LOAD_CYCLE_ID"::integer = "UNSAT_TGT3"."LOAD_CYCLE_ID"
WHERE "PIT_CHECK"."CUSTOMERS_HKEY" is null
;
`} ).execute();
return "Done.";$$;for DDL code:
/*
__ __ _ _ _ __ ___ __ __
\ \ / /_ _ _ _| | |_ ___ ____ ___ ___ __| | \ \/ _ \/ / /_/
\ \ / / _` | | | | | __/ __| _ \ / _ \/ _ \/ _` | \/ / \ \/ /\
\ V / (_| | |_| | | |_\__ \ |_) | __/ __/ (_| | / / \/\ \/ /
\_/ \__,_|\__,_|_|\__|___/ .__/ \___|\___|\__,_| /_/ \/_/\__/
|_|
Vaultspeed version: 5.2.1.1, generation date: 2022/12/14 13:10:09
DV_NAME: VS_DEMO_DV_SNW - Release: DV_R1(1) - Comment: first data vault release - Release date: 2022/09/26 21:35:06,
BV release: BVR-3(3) - Comment: demo - Release date: 2022/09/27 14:33:59,
SRC_NAME: VISITS - Release: VISITS(1) - Comment: release 1 - Release date: 2022/09/26 21:30:06
*/
-- POINT_IN_TIME
CREATE TABLE "VS_DEMO_DV_SNW_BV"."PIT_CUSTOMERS"
(
"PIT_HKEY" VARCHAR
,"CUSTOMERS_HKEY" VARCHAR(40)
,"SAT_CUSTOMERS_HKEY_SAT_SLS_CUSTOMERS_GENERAL" VARCHAR(40)
,"SAT_CUSTOMERS_HKEY_SAT_SLS_CUSTOMERS_GDPR" VARCHAR(40)
,"SAT_CUSTOMERS_HKEY_SAT_VST_CUSTOMERS" VARCHAR(40)
,"SNAPSHOT_DATE" TIMESTAMP_TZ
,"END_SNAPSHOT_DATE" TIMESTAMP_TZ
,"LOAD_DATE_SAT_SLS_CUSTOMERS_GENERAL" TIMESTAMP_NTZ
,"LOAD_DATE_SAT_SLS_CUSTOMERS_GDPR" TIMESTAMP_NTZ
,"LOAD_DATE_SAT_VST_CUSTOMERS" TIMESTAMP_NTZ
)
;