The Use Case

Some use cases call for the table you are querying from to be dynamic, based on what the user selects on the front end. For example, you could have multiple schemas that are identical in structure. We often encounter this when working with GA Premium data, where we can see tables such as myproject:00001.ga_sessions_, myproject:00002.ga_sessions_, and myproject:00003.ga_sessions_. The 00001, 00002, and 00003 prefixes could represent data for different countries, traffic sources, etc.

Using a parameter that we plug in to the sql_table_name of our view (read about the actual parameter parameter on the parameter documentation page), we can apply a filter to Looks and dashboards that allows users to select the table they will query from (selecting a specific country table for a countries view) — with a user-friendly name!

The Code

This is how we define the parameter that becomes the front-end filter for end users to select the table to query. We will apply it to our sql_table_name parameter:

view: countries {
  sql_table_name: {% parameter tablename %};;

  parameter: tablename {
    type: unquoted
    allowed_value: {
      label: "UK"
      value: "00001.ga_sessions"
    }
    allowed_value: {
      label: "Germany"
      value: "00002.ga_sessions"
    }
  }
}

Referencing Derived Tables

We can use a similar approach to dynamically reference derived tables in our sql_table_name parameter. Since we use the LookML syntax ${view_name.SQL_TABLE_NAME} to reference derived tables in SQL parameters, we need to use slightly different Liquid syntax:

view: my_view {
  sql_table_name:
    {% if tablename._parameter_value == 'derived table name' %}   #If the parameter value = 'derived table name'
      ${derived_table_name.SQL_TABLE_NAME}                        #Then reference it with ${view_name.SQL_TABLE_NAME} syntax
    {% elsif tablename._parameter_value == 'another table' %}     #Otherwise 'another table' option is selected, reference this table instead
      schema.table_name
    -- Add as many of these elsif clauses as you want, one for each parameter option.
    {% else %}
      schema.table_name                                           #Use schema.table_name as the default if no selection is made
    {% endif %} ;;

  parameter: tablename {
    type: unquoted
    allowed_value: {
      label: "derived table"
      value: "derived table name"
    }
    allowed_value: {
      label: "another table"
      value: "schema.table_name"
    }
    # add as many allowed_values as you have tables
  }
}

Here we are using the Liquid variable _parameter_value, which allows us to reference the selected value of a parameter and ensures the passing of special characters like brackets. This is necessary for referencing PDTs with the ${view_name.SQL_TABLE_NAME} syntax, while normal, non-derived tables will use the schema.table_name format.