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
298 stars 120 forks source link

[Snowflake] Override column expression to pick metadata from file #294

Closed pierrick-c closed 5 months ago

pierrick-c commented 5 months ago

Describe the feature

Hello there, I've been looking for a way to expose metadata$file_last_modified on Snowflake (to load files from GCS buckets). I came across this MR https://github.com/dbt-labs/dbt-external-tables/pull/239 for snowpipe. I'd like to do it for auto-refreshed external tables, without snowpipe.

Describe alternatives you've considered

A way to do it would be to allow to define an "expression" key in the column definition to override the automatic generation of the column_expression in dbt_external_tables/macros/plugins/snowflake/create_external_table.sql. This seems less robust than what's done on snowpipe, but in this case we may want to stay flexible as the call to the metadata may depend on the source of the external table.

Additional context

This feature would be Snowflake specific

Who will this benefit?

This would allow us to specify sql code to load some columns directly using user-defined expressions.

example: Accessing metadata from the file, or whichever expression the user needs.

pierrick-c commented 5 months ago

Looks like it was added a few weeks ago in v0.9.0. Thanks everyone