with uuid_generation AS
(select uuid_string() as uuid)
select metadata$filename,
to_timestamp_ntz(current_timestamp()),
md.$1
from @S3/table/prod/ (file_format => STG.XML, pattern =>'^((?!archive).)*$') md cross join uuid_generation
This snippet works perfectly fine in snowflake (dbeaver). However, when I put it in a dbt model, I get this error:
Missing column specification
I also tried this:
with uuid_generation AS
(select uuid_string() as uuid)
select metadata$filename,
to_timestamp_ntz(current_timestamp()),
md.$1 as "$1"
from @S3/table/prod/ (file_format => STG.XML, pattern =>'^((?!archive).)*$') md cross join uuid_generation
but I get the same error
CodePudding user response:
You need to provide a column name for aggregated or derived columns due to dbt. Also unquoted column names need to start with letters or underscore: https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html
with uuid_generation AS
(select uuid_string() as uuid)
select metadata$filename,
to_timestamp_ntz(current_timestamp()) as your_timestamp,
md.$1 as "$1"
from @S3/ivw_competitors/prod/ (file_format => STG.XML, pattern
=>'^((?!archive).)*$') md cross join uuid_generation