SeekWell's Snowflake integration lets you send data from Snowflake into Google Sheets, Salesforce, Intercom and another destination we support from your existing Snowflake Worksheets.

Create integration

CREATE OR REPLACE api integration seekwell
  api_provider = aws_api_gateway
  api_aws_role_arn = 'arn:aws:iam::145125969827:role/snowflake-external-lambda-functions'
  enabled = true
  api_allowed_prefixes = ('https://yjq9k9mqh3.execute-api.us-east-1.amazonaws.com/snowflake-external-function-stage')
;

Where

aws_api_gateway is the Snowflake api_provider
api_aws_role_arn is SeekWell's ARN for our API
api_allowed_prefixes is SeekWell's API endpoint

Please note, we'd prefer to use a custom domain here, but Snowflake requires the default domain.

Snowflake external functions and API integrations do not support AWS custom domains. To access an Amazon API Gateway from Snowflake, use the default URL generated by AWS, which looks similar to the following:

More information on External Functions can be found here.

Verify integration

DESCRIBE integration seekwell;

Create `seekwell` function

💡

Open the web app and hit command / ctrl + k -> “API” to generate an API key.

CREATE OR REPLACE external function public.seekwell(x object)
  returns variant
    headers = (
      'seekwell-api-key' = 'INSERT_YOUR_API_KEY'
  )
  api_integration = seekwell
  as 'https://yjq9k9mqh3.execute-api.us-east-1.amazonaws.com/snowflake-external-function-stage/seekwell'
;

Grant roles access to function

grant all privileges on function public.seekwell(object) to role MY_ROLE;

Send data to Google Sheets

Requried Parameters

type - 'sheets'
ss_id - The spreadsheet ID (long unique ID in the Sheet URL)
sheet - The sheet / tab name within the spreadsheet (e.g. "Sheet1")
cell - The cell to start rows in (e.g. "A1")

with to_sheets as (
	SELECT
	u.*,
	OBJECT_CONSTRUCT(
	    'type', 'sheets',
	    'ss_id', 'INSERT_YOUR_SPREADSHEET',
	    'sheet', 'users',
	    'cell', 'a1',
	    'title', 'User Report'
	) as seekwell
	FROM public.users AS u
	LIMIT 100
)
select 
to_sheets.*,
public.seekwell(
  OBJECT_CONSTRUCT(to_sheets.*)
)::string as seekwell_result
from to_sheets as to_sheets

💡

You can use SQL in the OBJECT_CONSTRUCT, e.g. OBJECT_CONSTRUCT( 'sheet', (select concat('run_on_', replace(current_date()::string, '-'))), 'code', current_statement(), 'columns', array_construct( 'ENGLISH_NAME', 'HEADLINE_CATEGORY', 'EMOJI', 'HEADLINE_DESCRIPTION' ) ) as seekwell

'sheet', (select concat('run_on_', replace(current_date()::string, '-'))),

Send data to Salesforce

Our Salesforce destination (type = 'sfdc') takes the following parameters:

type - 'sfdc'
object - e.g. Account, Contact, Opportunity, etc.
operation - e.g. insert, update, upsert, etc.

Simply alias your column name in Snowflake as the field name in Salesforce. Custom fields will end in __c. The field names can be found in the Object Manager in Setup.

with to_sfdc as (
    select 
    u.email as "Email", 
    sc.id as "Id", 
    u.channel as "Channel__c",
    OBJECT_CONSTRUCT(
        'type', 'sfdc',
        'object', 'Contact',
        'operation', 'update'
    ) as seekwell
    from public.users as u 
    join salesforce.contacts as sc on u.email = sc.email
)

select 
to_sfdc.*,
public.seekwell(
  OBJECT_CONSTRUCT(to_sfdc.*)
)::string as seekwell_result
from to_sfdc as to_sfdc

Schedule Tasks

CREATE TASK mytask_hour
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 9-17 * * SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
SELECT
u.*,
OBJECT_CONSTRUCT(
    'type', 'sheets',
    'ss_id', 'INSERT_YOUR_SPREADSHEET',
    'sheet', 'users',
    'cell', 'a1',
    'title', 'User Report',
) as seekwell
FROM public.users AS u
LIMIT 100;

💡

Make sure you alias your destination OBJECT_CONSTRUCT as seekwell

Ordering columns

If the order of your columns matters (e.g. in Sheets) you can use the columns parameter to specify the order

💡

Column names are case sensitive! By default, Snowflake uses upper case, so you should use upper case in the column array unless you are explictly lower casing your columns in the SELECT statement

OBJECT_CONSTRUCT(
	...
	'columns', array_construct(
        'DO', 'RE', 'MI'
)

Using SQL in your "seekwell" metadata

You can use SQL in the OBJECT_CONSTRUCT call to make the parameters dynamic, e.g. if you wanted to change the Sheet name based on the current date you could do something like this

OBJECT_CONSTRUCT(
		...
    'title', 'Weather Report',
		'sheet', (select concat('run_on_', replace(current_date()::string, '-'))),
    'code', current_statement(),
    ...
) as seekwell

Snowflake External Function