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

This legacy feature is removed in Looker 6.6.

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 Looker instances that are updating to Looker 5.22 or Looker 5.24, this legacy feature is ON by default. Existing NDTs will continue to output string format for yesno, date, and date_time fields (the pre-Looker 5.22 behavior).


• For Looker instances updating to Looker 6.0, Looker 6.2, or Looker 6.4, this legacy feature will be OFF by default, but instance administrators can temporarily reenable the legacy behavior if necessary to complete the transition to the new behavior.


• New Looker installations that start on Looker 5.22 or newer will not have the legacy string 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 in the IDE until Looker 6.4; it is removed in Looker 6.6.

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!