dbt: Missing column specification

October 12, 2022

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

>Solution :

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