search

dbt-labs / dbt-external-tables

dbt macros to stage external sources
https://hub.getdbt.com/dbt-labs/dbt_external_tables/latest/
Apache License 2.0
314 stars 123 forks source link

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

Closed rkittens closed 7 months ago

rkittens commented 1 year ago

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 commented 11 months ago

+1

jtmcn commented 11 months ago

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

jrdonovan commented 10 months ago

+1

dataders commented 7 months ago

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 https://github.com/dbt-labs/dbt-external-tables/blob/c7ae7433b2b14ab4377aac75e2cb2b72e5010bf1/macros/plugins/snowflake/snowpipe/create_snowpipe.sql#L9

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.

https://github.com/dbt-labs/dbt-external-tables/blob/c7ae7433b2b14ab4377aac75e2cb2b72e5010bf1/sample_sources/snowflake.yml#L40-L53

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