automate-dv icon indicating copy to clipboard operation
automate-dv copied to clipboard

[BUG] get_period_boundaries macro does not use fully qualified table reference

Open NiallRees opened this issue 2 years ago • 3 comments

Describe the bug When running an insert_by_period materialized model in Snowflake for the second time (incremental load), I get this error:

Cannot perform SELECT. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.

I assume this is because the get_period_boundaries macro does not use a fully qualified table reference, see: https://github.com/Datavault-UK/dbtvault/blob/ca533eafff10978036d60545f109169074494125/macros/materialisations/period_mat_helpers/get_period_boundaries.sql#L24 Here is the query that is failing in Snowflake:

WITH period_data AS (
            SELECT
                COALESCE(MAX("RAW_TIMESTAMP"), '2022-01-01')::TIMESTAMP AS start_timestamp,
                COALESCE(
    dateadd(
        millisecond,
        86399999,
        NULLIF('2022-01-05','none')::TIMESTAMP
        )
,
    current_timestamp::
    timestamp_ntz
 ) AS stop_timestamp
            FROM <schema>.<table>
        )
        SELECT
            start_timestamp,
            stop_timestamp,
            datediff(
        day,
        start_timestamp,
        stop_timestamp
        ) + 1 AS num_periods
        FROM period_data

Versions dbt: dbt-snowflake==1.0.0 dbtvault: 0.8.3 To Reproduce Steps to reproduce the behavior:

  1. Go to '...'
  2. Click on '....'
  3. Scroll down to '....'
  4. See error Expected behavior A clear and concise description of what you expected to happen. Screenshots If applicable, add screenshots to help explain your problem. Log files If applicable, provide dbt log files which include the problem. Additional context Add any other context about the problem here.

NiallRees avatar May 26 '22 11:05 NiallRees