PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Can DBT source identifier names begin with integers?

I’m trying to select from a snowflake source as shown below called "s_2020_09_history_logs"

extract from staging model:

with unioned_archived_history_logs as (

    select * from {{ source('emspdb_archive', s_2020_09_history_logs) }}

extract from _sources.yml:

  - name: emspdb_archive
    database: lake
    schema: emspdb_archiveschema
    tables:
      - name: s_2020_09_history_logs
        identifier: "2020_09_history_logs"

the names used are correct and I have been trying for hours to find out why i am getting the error, but am coming up with nothing.

the error:

Database Error
001003 (42000): SQL compilation error: syntax error line 4 at position 43 unexpected ‘.2020’. syntax error line 157 at position 33 unexpected ‘from’. syntax error line 159 at position 4 unexpected ‘from’.

I think this might be to do with the snowflake table in the identifier beginning with numbers i.e. ‘2020’ as this error is happening on all such cases but none of the cases without numbers. I cannot find however anything to suggest why this would not be allowed? I have tried using double quotation marks in all combinations, but this has had no effect.

Tags:

2

Answers


  1. 0

    Try this once; please reply to me if it worked or not.

    with unioned_archived_history_logs as (
    select * from {{ source('emspdb_archive', `"s_2020_09_history_logs"`) }}
)
    Login or Signup to reply.
  2. 0

    The issue here is that because your table identifier starts with a number, it must be quoted. dbt offers a configuration option to do exactly this.

    See the dbt source quoting config docs for more detail.

      - name: emspdb_archive
    database: lake
    schema: emspdb_archiveschema
    tables:
      - name: s_2020_09_history_logs
        identifier: "2020_09_history_logs"
        quoting:
          identifier: true
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search