Improved Behavior for Native Derived Tables in Looker 5.22


(erin.alexander) #1

In Looker 5.20 and earlier, native derived tables (NDTs) with yesno, date, and date_time fields created string format output for these data types.

Starting in Looker 5.22, an NDT can preserve the data types of all of its input fields, including yesno, date, and date_time fields.

New Legacy Feature

To help migrate to the new behavior, Looker 5.22 introduces a new legacy feature called Native Derived Tables convert dates and yesnos to strings by default. When this legacy feature is ON, NDTs on your Looker instance will convert fields with yesno, date, and date_time data types into string format, which is the pre-5.22 behavior.

  • For existing Looker instances that are upgrading to 5.22, this legacy feature is ON by default, so that existing NDTs will continue to output string format for yesno, date, and date_time fields (pre-5.22 behavior).
  • For new Looker installations in 5.22 and later, the legacy feature is OFF by default, so that the output of NDTs will preserve the data types of the input fields (new, 5.22 behavior).

New preserve_types Parameter

Looker 5.22 also introduces a new preserve_types parameter that developers can use to test existing NDTs before moving to the new NDT behavior. The preserve_types parameter is supported only when the Native Derived Tables convert dates and yesnos to strings by default legacy feature is ON. The preserve_types parameter will be supported only for a few releases; it is a short-lived parameter used only for migrating to the new NDT behavior.

When you specify preserve_types: yes for an NDT, the NDT uses the 5.22 behavior where yesno, date, and date_time data types are preserved in the output. You can add preserve_types: yes to each NDT to test that the NDT functions correctly when data types are preserved. If all NDTs work as expected, you can safely turn off the Native Derived Tables convert dates and yesnos to strings by default legacy feature and use the new NDT behavior.

Here is an example derived table with the preserve_types parameter:

explore: my_ndt
view: my_ndt {
  derived_table: {
    explore_source: users {
      preserve_types: yes
      column: month { field: users.created_at_month }
    }
  }
  dimension: month { type: string }
  dimension: month_2 { type: date_month  datatype: date }
  dimension: month_math {
    datatype: date
    type: date_month
    sql: DATE_ADD(${month}, INTERVAL 2 MONTHS);;
  }
}

How to Migrate Your NDTs

Here are the steps for migrating your NDTs from pre-5.22 behavior to the new 5.22 behavior:

  1. Turn on Development Mode.
  2. Navigate to your LookML project files and add preserve_types: yes to all of your NDTs.
  3. Use the LookML Validator to verify your changes and resolve any LookML warnings. Fix any NDTs that don’t work correctly when data types are preserved.
  4. Make sure that any critical content based on your NDTs is also functioning properly.
  5. Once you have verified your LookML and the content based on your NDTs, push your changes to production.
  6. Go to the Legacy Features page of the Admin panel and turn OFF the legacy feature Native Derived Tables convert dates and yesnos to strings by default.
  7. Go back to your LookML project files and remove all instances of the preserve_types parameter from your NDTs. (Once you turn off the legacy feature, the preserve_types parameter will generate LookML warnings.)
  8. Validate your LookML, then push your changes to production.

Now you can move forward with the new NDT behavior. Enjoy!


Looker 5.22 Release Notes
Legacy Features End-of-life Schedule