Issue Staging Data Dynamically

[dylan_hornstei1]

We have a large base_table in our Snowflake database that contains row-by-row transactions from all of our subscribers.

I’m building a dashboard with 10 tiles that aggregates data from this base_table for a single subscriber. Ideally, I would stage the data for that subscriber one time, and then have various tiles/queries utilize the staged data to avoid querying the large base_table over and over with each tile. 

Here is a simple SQL example.

CREATE TABLE stage_data AS(
   SELECT
      subscriber_id,
      country,
      transaction_id,
      ...
   FROM base_table
   WHERE subscriber_id = <subscriber_id>
     AND date >= <start_date>
     AND date <  <end_date> 
);

--query1
SELECT <query1 cols> FROM stage_data;

--query2
SELECT <query2 cols> FROM stage_data;

...

--query10
SELECT <query10 cols> FROM stage_data;

Or, in LookML...

view: aggregate_1 {
  sql_table_name: stage_data.SQL_TABLE_NAME
  dimension: country {}
  measure: txn_cnt {type: count_distinct sql: ${tracking_id}}
  ...
}

view: aggregate_2 {
  sql_table_name: stage_data.SQL_TABLE_NAME
  ...
}

view: aggregate_3 {
  sql_table_name: stage_data.SQL_TABLE_NAME
  ...
}

view: aggregate_10 {
  sql_table_name: stage_data.SQL_TABLE_NAME
  ...
}


explore: stage_data {}

view: stage_data {
  derived_table: {
    persist_for: "10 minutes"
      sql:
          SELECT
            subscriber_id,
            msg_day,
            tracking_id,
            country,
            ...
          FROM base_table
          WHERE {% condition stage_data.subscriber_id %} subscriber_id {% endcondition %}
            AND {% condition stage_data.msg_day_date %} msg_day {% endcondition %}
       ;;
  } 
  dimension: subscriber_id {}
  dimension_group: msg_day {type: time}
  dimension: tracking_id {}
  dimension: country {}
  ...
}

The problem: The user needs to be able to select the subscriber_id and date range for the report, but I cannot dynamically pass a liquid variable into the CREATE TABLE process. It seems that liquid variable reference simply isn’t supported for PDTs. 

We don’t want to stage data for every customer individually because this report will only be used for new customers in a POC. Staging data for every customer is not necessary, and costly in terms of storage and query time.

I’ve tried many ways to get this to work, with no success. I’ve tried both SQL-derived-tables and NDTs. NDTs produce a LookML error “Cannot persist NDTs with dynamic filters”. SQL-derived-tables do not produce any LookML errors, but it leave the liquid syntax in the query that gets sent to Snowflake, which results in a syntax error 

Has anyone figured out how to stage data using dynamic filters???