Onboarding the Flipside shares is a simple three or four step process.
Get the listing
Mount the shared data
Run the provided script that will finish the setup
OPTIONAL: Create an alert to notify if the task fails
Flipside data shares contain the underlying tables and the script in step three will ensure the final presentation layer is recreated within the consumer’s snowflake account.
Step 1: Get and mount the share
In the Snowflake account you shared with Flipside, navigate to: Data > Data Products > then click on the tab Shared With Me. Find the listing titled Flipside_[Chain Name] and click the blue button labeled 'Get'
Step 2: Mount the share
Mount the data and make sure the database is named Flipside_[Chain name] (if Trial remove _Trial at the end)
Example: Flipside_Ethereum
Step 3: Run the SQL script to recreate the presentation layer as ACCOUNTADMIN
Open a new worksheet in Snowflake and run the script linked here.
(Optional) Step 4: Modify and execute the sample code to set up email alerting
SQL Script
--Create utility dbcreatedatabaseifnotexists _flipside;-- create base proc that uses the db and updated_sinceCREATE OR REPLACE PROCEDURE _flipside.public.sp_ddl_refresh(db string ,UPDATED_SINCE TIMESTAMP_NTZ(9))RETURNS VARCHAR NOT NULLLANGUAGE SQLASDECLARE full_table varchar DEFAULT :db ||'._datashare._create_gold'; destination_db varchardefault REPLACE(:db,'FLIPSIDE_'); QUERY varchardefault'';DDL_HASH STRING;BEGINselectreplace(replace(replace(ddl, '\\$\\$', '$$'),'__SOURCE__', UPPER(:db) ),'__NEW__', UPPER(:destination_db) ) , ddl_hash into :QUERY, :DDL_HASHfrom IDENTIFIER(:full_table) WHERE DDL_CREATED_AT >= :UPDATED_SINCEorder by ddl_created_at desclimit1; IF (:QUERY is not null) then EXECUTE IMMEDIATE :QUERY;endif; RETURN:DDL_HASH;END;-- create proc that uses just the dbCREATE OR REPLACE PROCEDURE _FLIPSIDE.PUBLIC.SP_DDL_REFRESH(DB string)RETURNS TABLE (ddl_hash STRING)LANGUAGE SQLAS DECLARE results resultset;BEGIN results := (call _flipside.public.sp_ddl_refresh(:db, '2000-01-01'::TIMESTAMP_NTZ) ); RETURN table(results);END;-- create proc that uses the updated since and will run for all flipside dbsCREATE OR REPLACE PROCEDURE _flipside.public.sp_ddl_refresh("UPDATED_SINCE" TIMESTAMP_NTZ(9))RETURNS TABLE (dbs string)LANGUAGE SQL-- EXECUTE AS OWNERAS DECLARE cur CURSOR FOR selectdatabase_namefrom SNOWFLAKE.INFORMATION_SCHEMA.DATABASES wheredatabase_namelike'FLIPSIDE_%';BEGINcreateorreplace temporary table results as select''::string as dbfrom dual limit0; FOR cur_row IN cur DO let db varchar:= cur_row.database_name;call _flipside.public.sp_ddl_refresh(:db, :updated_since);insert into results (db) values (:db); END FOR; let rs resultset := (select*from results order by db); RETURN TABLE(rs);END;-- create proc that will recreated all dbs regardless of updated since dateCREATE OR REPLACE PROCEDURE _flipside.public.sp_ddl_refresh()RETURNS TABLE (dbs string)LANGUAGE SQLASDECLARE results resultset;BEGIN results := (call _flipside.public.sp_ddl_refresh( '2000-01-01'::TIMESTAMP_NTZ) ); RETURN table(results);END;-- create task that runs every 10 minutes (procs only run if there's a change -- in the last 10 mins)createorreplace task _flipside.public.TK_ddl_refreshschedule='10 MINUTE'as DECLARErs resultset;output string;BEGIN rs := (call _flipside.public.sp_ddl_refresh(sysdate() - interval '10 mins'));select listagg($1, ';') into :outputfromtable(result_scan(last_query_id())) limit1;call SYSTEM$SET_RETURN_VALUE( :output );END;--set task to resume. By default they are suspendedalter task _flipside.public.TK_ddl_refresh resume;--Call the proc for the first time to created the new dbs manuallycall _flipside.public.sp_ddl_refresh();--query to see that the task is successfully createdselect*fromtable(_flipside.information_schema.task_history())wherename='TK_DDL_REFRESH'order by scheduled_time desc;// Some code
Task alerting
Alerting/Monitoring for failed tasks can be done in many ways. One option is to use the built in snowflake email alerting. Snowflake docs: alerting documentation | email notification
Below is a sample example of how we can check the task history every 10 minutes to look for failures
--create email integrationsCREATE NOTIFICATION INTEGRATION my_email_int TYPE=EMAIL ENABLED=TRUE ALLOWED_RECIPIENTS=('user@flipsidecrypto.com');--create alertCREATE OR REPLACE ALERT myalert WAREHOUSE = [warehouse name] SCHEDULE ='10 MINUTE' IF (EXISTS ( SELECT *fromtable(_flipside.information_schema.task_history())wherename='TK_DDL_REFRESH'and STATE ='FAILED'and COMPLETED_TIME BETWEEN SNOWFLAKE.ALERT.LAST_SUCCESSFUL_SCHEDULED_TIME() AND SNOWFLAKE.ALERT.SCHEDULED_TIME() )) THEN CALL SYSTEM$SEND_EMAIL('my_email_int','user@flipsidecrypto.com','Email Alert: The data share ddl refresh failed','Task A has ...');--make sure to resume the alert. By default it's suspendedALTER ALERT myalert RESUME;// Some code
Granting permissions
If another role outside of accountadmin needs access to the data, the below scripts should allow for granting access to the current views and will also ensure any new schemas/views are accessible.
GRANT usage ondatabase [blockchain] torole [your_role];
GRANT usage on all schemas indatabase [blockchain] torole [your_role];
GRANT selecton all views indatabase [blockchain] torole [your_role];
GRANT usage on future schemas indatabase [blockchain] torole [your_role];
GRANT selecton future views indatabase [blockchain] torole [your_role];