Week 120 – Basic

Synthetic Data!

Often, for testing purposes, you want to be able to generate synthetic data based on real data. But that’s hard!

Well, now Snowflake supports synthetic data generation: Using synthetic data in Snowflake

Your task is to run the below (you may need to replace SAMPLE_DATA with whatever you named the Snowflake sample data share):

CREATE OR REPLACE VIEW WEB_SALES as (
    SELECT * from SAMPLE_DATA.TPCDS_SF10TCL.WEB_SALES
    LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_RETURNS as (
    SELECT * from SAMPLE_DATA.TPCDS_SF10TCL.WEB_RETURNS
    LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_SITE as (
    SELECT * from SAMPLE_DATA.TPCDS_SF10TCL.WEB_SITE
    LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_PAGE as (
    SELECT * from SAMPLE_DATA.TPCDS_SF10TCL.WEB_PAGE
    LIMIT 5000
);
CREATE OR REPLACE VIEW WEB_ITEM as (
    SELECT * from SAMPLE_DATA.TPCDS_SF10TCL.ITEM
    LIMIT 5000
);

And then create a set of fake tables on top of the above views using GENERATE_SYNTHETIC_DATA() – if you’re successful you should get a result that looks something like:

And the below query should work:

select 
    ws.*,
    wr.*,
    wp.*,
    wsi.*,
    wi.*
from frosty_db.week_120.web_sales ws
left join frosty_db.week_120.web_returns wr
    on ws.ws_web_page_sk = wr.wr_web_page_sk
    and ws.ws_item_sk = wr.wr_item_sk
left join frosty_db.week_120.web_page wp
    on ws.ws_web_page_sk = wp.wp_web_page_sk
left join frosty_db.week_120.web_site wsi
    on ws.ws_web_site_sk = wsi.web_site_sk
left join frosty_db.week_120.web_item wi
    on ws.ws_item_sk = wi.i_item_sk;

Happy generating!