Week 52 - Dynamic Tables & Streams

At the last Snowflake Summit , the Dynamic Table was one of the big announcements. This table type adds another option to the list of Streams and Materialized Views.

We'd like you to use our startup function to hit this challenge at speed! The function creates a table if doesn't already exist and fills it with data if it does exist! Perfect for our challenge (and a decent function to get 'inspired by')

CREATE OR REPLACE PROCEDURE check_and_generate_data(tableName STRING)RETURNS STRINGLANGUAGE JAVASCRIPTEXECUTE AS CALLERAS$$try {var tableName = TABLENAME;var command1 = `SELECT count(*) as count FROM information_schema.tables WHERE table_schema = CURRENT_SCHEMA() AND table_name = '${tableName.toUpperCase()}'`;var statement1 = snowflake.createStatement({sqlText: command1});var result_set1 = statement1.execute();result_set1.next();var count = result_set1.getColumnValue('COUNT');if (count == 0) {var command2 = `CREATE TABLE ${tableName} (payload VARIANT, ingested_at TIMESTAMP_NTZ default CURRENT_TIMESTAMP())`;var statement2 = snowflake.createStatement({sqlText: command2});statement2.execute();return `Table ${tableName} has been created.`;} else {for(var i=0; i<40; i++) {var jsonObject = {"id": i,"name": "Name_" + i,"address": "Address_" + i,"email": "email_" + i + "@example.com","transactionValue": Math.floor(Math.random() * 10000) + 1};var jsonString = JSON.stringify(jsonObject);var command3 = `INSERT INTO ${tableName} (payload) SELECT PARSE_JSON(column1) FROM VALUES ('${jsonString}')`;var statement3 = snowflake.createStatement({sqlText: command3});statement3.execute();}return `40 records have been inserted into the ${tableName} table.`;}} catch (err) {return "Failed: " + err;}$$;

The challenge for this week is to use a Dynamic Table to create the following results: