Incremental PDTs not incrementing when referencing the same derived table more than once.

[jonf]

I came across this problem a month ago. I have a SQL-based derived table (not a PDT), which I reference twice when building an IPDT in another view. The first time the IPDT view is built, it runs just fine, but when it’s incremented the query fails because it uses the same alias for the source SQL-based derived table twice.

Here’s an example. This is a non-PDT view with the SQL-based derived table:

view: impressions {
  derived_table: {
    sql:
      SELECT 'video' as content_type, 10 as imp, DATE('2022-08-10') as date
      UNION ALL
      SELECT 'video' as content_type, 20 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 5 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 2 as imp, DATE('2022-08-12') as date
    ;;
  }
}

This is the IPDT that references the view above twice:

view: impressions_ipdt {
  #This  creates an IPDT based on the impressions view.
  derived_table: {
    sql_trigger_value: SELECT CURRENT_DATE() ;;
    increment_key: "date_dim"
    increment_offset: 3
    sql:
      SELECT content_type, date, imp
      FROM ${impressions.SQL_TABLE_NAME}
      WHERE content_type = 'video'
      AND {% incrementcondition %} date {% endincrementcondition %}
      UNION ALL
      SELECT content_type, date, imp
      FROM ${impressions.SQL_TABLE_NAME}
      WHERE content_type = 'audio'
      AND {% incrementcondition %} date {% endincrementcondition %}
    ;;
  }

  dimension: date_dim {
    type: date
    datatype: date
    sql: ${TABLE}.date ;;
  }

  measure: impressions {
    type: sum
    sql: ${TABLE}.imp ;;
  }
}

This is the query that’s generated for the first build of the IPDT. Notice how the “impresssions” table is only mentioned once:

-- generate derived table impressions_ipdt
-- Building persistent derived table ipdt_test::impressions_ipdt
WITH impressions AS (SELECT 'video' as content_type, 10 as imp, DATE('2022-08-10') as date
      UNION ALL
      SELECT 'video' as content_type, 20 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 5 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 2 as imp, DATE('2022-08-12') as date
    )

SELECT content_type, date, imp
      FROM impressions
      WHERE content_type = 'video'
      AND 1 = 1
      UNION ALL
      SELECT content_type, date, imp
      FROM impressions
      WHERE content_type = 'audio'
      AND 1 = 1

-- finished impressions_ipdt

But when the table is incremented, the impressions table is mentioned twice at the beginning of the query, causing a “duplicate alias” error in Big Query:

-- generate derived table impressions_ipdt
-- increment persistent derived table impressions_ipdt to generation 1
MERGE INTO `looker_scratch.LR_996OP1660319767266_impressions_ipdt` AS impressions_ipdt USING (

WITH impressions AS (SELECT 'video' as content_type, 10 as imp, DATE('2022-08-10') as date
      UNION ALL
      SELECT 'video' as content_type, 20 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 5 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 2 as imp, DATE('2022-08-12') as date
    )

  ,  impressions AS (SELECT 'video' as content_type, 10 as imp, DATE('2022-08-10') as date
      UNION ALL
      SELECT 'video' as content_type, 20 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 5 as imp, DATE('2022-08-11') as date
      UNION ALL
      SELECT 'audio' as content_type, 2 as imp, DATE('2022-08-12') as date
    )



SELECT content_type, date, imp
      FROM impressions
      WHERE content_type = 'video'
      AND ( date  >= DATE('2022-08-09'))
      UNION ALL
      SELECT content_type, date, imp
      FROM impressions
      WHERE content_type = 'audio'
      AND ( date  >= DATE('2022-08-09'))
    ) AS as2ae7d0303ed ON FALSE
WHEN NOT MATCHED BY SOURCE AND impressions_ipdt.date  >= TIMESTAMP('2022-08-09 00:00:00') THEN DELETE
WHEN NOT MATCHED THEN INSERT (content_type, date, imp) VALUES (content_type, date, imp)


-- finished impressions_ipdt