feature: support AWS_SNS_TOPIC field in external table yml definition for Snowflake data warehouses

[rkittens]

Describe the feature

The ability to specify the AWS_SNS_TOPIC for automatic table refreshes for s3 external tables in Snowflake. Snowflake allows the addition of an SNS topic so that it can update its catalog of S3 data but the package does not.

Describe alternatives you've considered

I edited the package to include the argument and ran it manually and then commented out when bringing it to production.

Additional context

Specifically for a Snowflake/S3 combo. Obviously there are other setups but not all have to be done at the same time.

Who will this benefit?

Anyone and everyone who doesnt want to manually refresh their external tables or setup a separate job to do so

[eliot1785]

+1

[jtmcn]

+1 More. External tables have limited usefulness if they don't get updated along with the underlying data

[jrdonovan]

+1

[dataders]

Please correct me if I'm wrong, but I think the following statements are true

1. aws_sns_topic is already supported in this package for use with Snowpipe
2. aws_sns_topic is only used with a CREATE OR REPLACE PIPE statement, and isn't useful/possible to define with a CREATE OR REPLACE EXTERNAL TABLE statement

existing Snowpipe support for aws_sns_topic

Here's where things are currently defined

dbt-external-tables/macros/plugins/snowflake/snowpipe/create_snowpipe.sql

Line 9 in c7ae743

{% if snowpipe.aws_sns_topic -%} aws_sns_topic = '{{snowpipe.aws_sns_topic}}' {%- endif %}

Perhaps this is a documentation miss more than anything, but my understanding looking at the code and sample_sources/snowflake.yml, it appears that this is possible already.

dbt-external-tables/sample_sources/snowflake.yml

Lines 40 to 53 in c7ae743

	- name: event_snowpipe
	description: "Table of Snowplow events, stored as JSON files, loaded in near-real time via Snowpipe"
	loader: S3 + snowpipe # this is just for your reference
	external:
	location: "@raw.snowplow.snowplow"
	file_format: "{{ target.schema }}.my_json_file_format"
	pattern: ".*[.]json" # Optional object key pattern
	# Instead of an external tables, create an empty table, backfill it, and pipe new data
	snowpipe:
	auto_ingest: true # requires either `aws_sns_topic` or `integration`
	aws_sns_topic: # Amazon S3
	integration: # Google Cloud or Azure
	copy_options: "on_error = continue, enforce_length = false" # e.g.

CREATE OR REPLACE TABLE support for aws_sns_topic

When I started writing this, I thought the syntax was not supported as I couldn't find it on the CREATE EXTERNAL TABLE SQL reference docs page. But it's there!

Docs

what we really need are some better docs on what is and isn't supported here