DEMO: Error Handling while moving data from staging table to base table | Snowflake| VCKLY Tech скачать в хорошем качестве

DEMO: Error Handling while moving data from staging table to base table | Snowflake| VCKLY Tech

In this video, I am going to show you how to handle errors while moving/loading data from stage table to base table in snowflake cloud data warehouse using Multi Table Insert Command and Error handling conversion functions. Steps: 1: Create a staging table. 2: Create a base table. 3: Create an error table. 4: Preparing/Inserting sample records into staging table. 5: Loading data from stage table to base table using multi-table insert command. 6: Verify Data 7: Cleanup Tables --Step 1: Create a staging table CREATE OR REPLACE TABLE STG_FIN_SALES ( SALE_TIMESTAMP string, -- timestamp ITEM_SKU string, -- number PRICE string, -- number(10,2) IS_TAXABLE string, -- boolean COMMENTS string -- string ); --Step 2: Create a base table CREATE OR REPLACE TABLE FIN_SALES ( SALE_TIMESTAMP timestamp NOT NULL, ITEM_SKU number NOT NULL, PRICE number(10,2) NOT NULL, IS_TAXABLE boolean NOT NULL, COMMENTS string NOT NULL ); --Step 3: Create an error table CREATE OR REPLACE TABLE FIN_SALES_ERR( SALE_TIMESTAMP string, ITEM_SKU string, PRICE string, IS_TAXABLE string, ERROR_MSG string, -- extra column to capture error details COMMENTS string ); --Step: 4 Preparing/Inserting sample records into staging table insert into stg_fin_sales (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS) values ('2020-17-17 18:21:34', '2S3289', '$3.42' , 'Foo' , 'Bad row --all columns.'), ('2020-17-03 18:21:56', '9O832' , '1.41' , 'FALSE', 'Bad row: SALE_TIMESTAMP has the month and day transposed, ITEM_SKU has a capital "O" instead of a zero.'), ('2020-03-17 18:22:03', '7O242' , '2.99' , 'T' , 'Bad row: ITEM_SKU has a capital "O" instead of a zero.'), ('2020-03-17 18:22:10', '53921' , '$6.25', 'F' , 'Bad row: PRICE should not have a dollar sign.'), ('2020-03-17 18:22:17', '90210' , '2.49' , 'Foo' , 'Bad row: IS_TAXABLE cannot be converted to true or false'), ('2020-03-17 18:22:24', '80386' , '1.89' , '1' , 'Good row.'); --Step 5: Loading data from stage table to base table using multi-table inserts --Error-handling Conversion Functions INSERT FIRST WHEN row_status = 'Bad Row' THEN into FIN_SALES_ERR (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, ERROR_MSG,COMMENTS) values (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, NVL(SALE_TIMESTAMP_X_ERR,' ')||NVL(ITEM_SKU_X_err,' ')||NVL(PRICE_X_err,' ')||NVL(IS_TAXABLE_X_err,' '),COMMENTS) ELSE into FIN_SALES (SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, COMMENTS) values (SALE_TIMESTAMP_X, ITEM_SKU_X, PRICE_X, IS_TAXABLE_X, COMMENTS) select case when SALE_TIMESTAMP_X is NULL THEN ' {SALE_TIMESTAMP: Invlaid date} 'else NULL end as SALE_TIMESTAMP_X_err, case when ITEM_SKU_X is NULL THEN ' {ITEM_SKU: Invlaid Number} ' else NULL end as ITEM_SKU_X_err, case when PRICE_X is NULL THEN ' {PRICE: Invlaid Price} ' else NULL end as PRICE_X_err, case when IS_TAXABLE_X is NULL THEN ' {IS_TAXABLE: Invlaid Flag} ' else NULL end as IS_TAXABLE_X_err, SALE_TIMESTAMP_X, ITEM_SKU_X, PRICE_X,IS_TAXABLE_X, COMMENTS, SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE, CASE WHEN (SALE_TIMESTAMP_X is null and SALE_TIMESTAMP is not null) or (ITEM_SKU_X is null and SALE_TIMESTAMP is not null) or (PRICE_X is null and PRICE is not null) or (IS_TAXABLE_X is null and IS_TAXABLE is not null) THEN 'Bad Row' ELSE 'Good Row' END row_status from ( select try_to_timestamp (SALE_TIMESTAMP) as SALE_TIMESTAMP_X, try_to_number (ITEM_SKU, 10, 0) as ITEM_SKU_X, try_to_number (PRICE, 10, 2) as PRICE_X, try_to_boolean (IS_TAXABLE) as IS_TAXABLE_X, COMMENTS, SALE_TIMESTAMP, ITEM_SKU, PRICE, IS_TAXABLE from STG_FIN_SALES ); ​ ​-- Step 6: check bad/error records. Verify the comments columns ​ select * from fin_sales_err; --check good records select * from fin_sales; select error_msg from fin_sales_err; -- Step 7: Cleanup tables drop table stg_fin_slaes; drop table fin_base; drop table fin_base_err; -- For more details, https://docs.snowflake.net/manuals/sq... https://docs.snowflake.com/en/sql-ref... #snowflake#vcklytech#dwh#datacloud#sql