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
297 stars 119 forks source link

add flag `refresh_on_create` for snowflake external table #151

Closed pei0804 closed 1 year ago

pei0804 commented 2 years ago

Description & motivation

Added refresh_on_create flag for snowflake external tables.

The data lake we're having is We have a very large number of objects, and if we do not run refresh_on_create = false, we will run into a limit on the number of objects.

see: https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html

If the specified location contains close to 1 million files or more, we recommend that you set REFRESH_ON_CREATE = FALSE. After creating the external table, refresh the metadata incrementally by executing ALTER EXTERNAL TABLE … REFRESH statements that specify subpaths in the location (i.e. subsets of files to include in the refresh) until the metadata includes all of the files in the location

Checklist

image

pei0804 commented 2 years ago

Please let me know if there are any changes that need to be made.

pei0804 commented 2 years ago

integration-databricks does not seem to be relevant to this PR. It is down in other PRs.

ghost commented 1 year ago

Hi, will this be merged? This is basically a necessity for any tables with large amounts of files

neal-edwards commented 1 year ago

Any update on this PR? This is essential to support external Delta Tables in snowflake.

pei0804 commented 1 year ago

This problem has been resolved. I have a table created in Terraform that is referenced in dbt.

jtinspire commented 1 year ago

Any update on this PR? This is essential to support external Delta Tables in snowflake.

echoing this. This doesn't seem to be be added. We need the flag refresh_on_create

Currently getting the error : 

001425 (22023): SQL compilation error:
  invalid property combination 'AUTO_REFRESH'='true' and 'TABLE_FORMAT'='DELTA'
jtinspire commented 1 year ago

@pei0804 what was the alternative solution you referenced? For me, I am not able to create external delta table. I can create it after adding the change in the PR manually in file "macros/plugins/snowflake/create_external_table.sql".