Person: In Looker, derived tables can be ephemeral, meaning that Looker builds on app runtime or they can be persistent, meaning that they are written back to the connected database. The benefit of persistent derived tables, or PDTs, is that they are ready to go when business users need them. While the main downside is that they take up storage space in your database, which may correlate to cost. In this section we'll explore how PDTs are written back to and stored in the connected database in Looker. In Looker, ephemeral derived tables build at runtime as temporary tables or common table expressions, that are defined in the generated SQL with the following sin tax. With derived, underscore, table, underscore, name as open parenthesis, select, column one and so on. By contrast, a PDT will run an SQL create statement in the underlying database, to create a physical table. Both SQL-derived and native-derived tables can be saved as PDTs. When a business user selects a field or a filter from a PDT, the generated SQL will join to the PDT, just as it would to a regular table in the database. Another key point about PDTs is that Looker will build a version of the PDT specifically for your GitBranch in development mode. After you have deployed your code to production or if the PDT already existed in production and you are modifying it, business users clearing the PDT, in production mode, will be pointed to a separate version for production. Let's review the generated SQL for a persistent-derived table. First, notice the schema of the PDT, which is teach, underscore, scratch in this example. We recommend naming the schema for PDTs something like X, underscore, scratch, but you could call it anything you want. The important thing is to configure a schema dedicated to Looker PDTs in your database. Typically a database admin would create the schema, and then a Looker admin would specify the schema name in the connection settings of the Looker instance. Second, although the derived table is named user, underscore, order, underscore, facts, Looker will auto-generate the name for the PDT so it can keep track of different versions of the PDT in development or production modes. In the table name, a text string of a long jumble of letters and numbers will be prepinded to the drive table name. PDT names always start with this hash, which encodes information such as the database connection and SQL that it uses. To a persisted derived table, you must use at least one of the following parameters in the definition. First, data group, underscore, trigger uses a data group or cashing policy configured in the model. As a best practice, we recommend using data group, underscore, trigger if data groups are already defined in your model. Second, SQL, underscore, trigger, underscore, value uses a pre-written select statement that returns one value, such as the maximum value of the user ID column. Looker sends that select statement to the database repeatedly. And when it discovers the result has changed, it takes this as a queue to rebuild the PDT. Last, persist for instructs the PDT to be available for a set duration, such as 1 hour or 4 hours. There are a few things to consider before choosing this option, to persist your derived tables. First, because persist for does not contain any rebuild logic, the PDT does not get updated at any time within the specified duration. In addition, once time is up, the PDT is dropped and not recreated until the next business user needs it for a query. As a primary benefit of PDTs is having data readily available to minimize query run times, we recommend that you use persist, underscore, for in conjunction with sequel, underscore, trigger, underscore, value, to ensure that data updates within the duration or simply use data group, underscore, trigger or sequel, underscore, trigger, underscore, value on their own. If you have a use case for it, you can also make SQL-derived tables select from one or more other derived tables. This would create cascading derived tables, due to dependencies between the tables. Given that PDT names always start with a long hash, you can use dot, SQL, underscore, table, underscore, name, in capital letters, as a substitution operator, to refer to the view name. This enables Looker to plug in the physical name at run time. If you persist the cascading derived tables, then it's important to ensure they rebuild in the correct sequence. So the dependent PDTs rebuild with the most recent data. You can non-explicitly control the sequence that Looker uses to rebuild PDTs. Now one way you could try to make all your PDTs build in the right order is to stagger the SQL, underscore, trigger, underscore, value, based on the typical build time of the proceeding drive table. As you might imagine, this isn't full proof. This is another reason why data group, underscore, trigger is better than SQL, underscore, trigger, underscore, value for applying persistence. If all your cascading PDTs uses same data group, underscore, trigger, then Looker will actually be able to detect the dependencies and rebuild the PDTs in a guaranteed correct sequence. Last, we also recommend that you add indexing to your PDTs. Indexing is like a card catalog in a library, where you can look up the location of a book to quickly find it among the shelves. Similarly, indexes help database process queries more quickly. Looker accepts the indexing parameters for most database dialects. We suggest discussing indexing of PDTs with a data engineer or database administrator at your company, to determine the best option for your use case. For more information on these parameters, you can find them in derived table parameters section of Looker's view parameters documentation page. In summary, after PDTs are written back to your database, they are stored as physical tables that can be queried and joined, just like any other table in your database. Looker builds a separate version of the PDT, in development and production modes, to ensure that you can create and test PDTs without impacting the production environment until you're ready to merge your updates. Be sure to create a schema specifically for Looker PDTs. Within this schema, Looker auto-generates the name for the PDT so it can keep track of different versions and encode information, such as the database connection and SQL that is uses. With all of these features, Looker makes it very easy to create PDTs and leverage their great benefits. We hope that you will give them a try in your Looker instance.
