Making a PDT unnecessarily

  • 23 June 2019
  • 1 reply

Userlevel 3

This is a bug report. Tl;dr version: Looker creates a PDT when the query calls for that PDT Liquid-conditionally, even if the Liquid condition is not met. This leads to the query’s not running.

We have MySQL and Snowflake almost-copies of the same table and sometimes want to query either one. I wrote a dimension like this —

dimension: foo {
sql: (select foo from t2 [...]) ;;

— but it leads to syntax errors in Snowflake dialect. So I instead created a PDT for Snowflake, a copy of the original table with foo appended —

view: t_copy { # Snowflake only
derived_table: {
sql_trigger_value: select count(*)from t ;;
, t.*
from t
left join t2
on [...]

— and changed the original t view to have —

sql_table_name: {% if _dialect._name == 'mysql' %} t
{% elsif _dialect._name == 'snowflake' %} ${t_copy.SQL_TABLE_NAME}
{% endif %} ;;
dimension: foo {
sql: {% if _dialect._name == 'mysql' %} (select foo from t2 [...])
{% elsif _dialect._name == 'snowflake' %} ${TABLE}.foo
{% endif %} ;;

This should, I’d think, work fine. In case the dialect is Snowflake, Looker should look at the PDT; in case the dialect is MySQL, it should completely ignore the PDT. Which is good, because Looker doesn’t have PDT-writing permissions on MySQL.

But when try to use this in MySQL, I get the standard Looker-doesn’t-have-PDT-writing-permissions error. That is, Looker is trying to write a PDT, even though there’s no reason to (the query doesn’t use it). Looking at the generated SQL, I see that Looker is trying to first create a table and then proceed to not use it at all in the subsequent query. It should not create the table; please fix this bug.

1 reply

Userlevel 7
Badge +1

Hey Menashe,

This has been a long-standing catch point when using Liquid, though people usually run into it with unnecessary joins due to fields being referenced in conditionals.

This is caused by the order in which we parse LookML substitution syntax (${}) and liquid blocks ({% %}). Changing the behavior would require a fairly big overhaul & a backwards compatibility rats nest, and we’re still thinking of the best way to approach it. Thanks for reporting!