In Looker version 5.12, persistent derived table (PDT) jobs can connect using separate JDBC parameters (host, port, user, database, etc). This is valuable for a number of reasons:
- PDT jobs can authenticate through a separate database user, with a higher query queue (a.k.a. workload management) priority. This way the database can prioritize the PDT jobs over less-critical user queries.
- Write access can be revoked for the standard Looker database connection, and only granted to a special user that PDT jobs will authenticate through. This is a better security posture for most organizations.
- For databases like Snowflake, PDT jobs can be routed altogether to more powerful hardware that is not shared with the rest of the Looker users. This way PDTs can build quickly without incurring the cost of running expensive hardware 24/7.
How It Works
On the edit connection screen, if you have PDTs enabled you may override any of the existing connection properties, like host, port, username, password, and whatever else is being used to specify the database connection.
In this example, I've overridden the "Additional Params" property for a Snowflake connection with a separate warehouse called "my_sweet_cluster".
Can I now use PDTs on my parameterized connection?
Yes! You can, but only if you're parameterizing the connection with user attributes (as opposed to directly through LDAP).
Can the override be parameterized as well?
Can I write my PDTs to a separate database server software/dialect of SQL?
No. For example, you cannot write PDTs to a PostgreSQL database if your default connection goes to a MySQL database.
Can I write my PDTs to a separate database (superset of schemas) within the same dialect of SQL?
For systems that support "databases" as the superset namespace of schemas, no—we do not yet support scoping for the scratch schema ("Temp Database"). Note that wherever the PDTs are located, they must remain accessible and readable by the default (non-PDT) connection.