Difference between ephemeral and materialize
SQL queries that you write in Peliqan are ephemeral, this means that they are executed each time you view the data or use the query.
SQL queries are “views” on the underlying data. By default they only live inside Peliqan. You can enable “Create as view” on an SQL query in Peliqan. By doing so, the view is created in the data warehouse and it will be visible when you connect to the data warehouse using e.g. a BI tool such as Microsoft Power BI or Metabase.
Materialize is the opposite of ephemeral.
Enable materialize on a query or table in Peliqan
You can enable Materialize for an SQL query or table under the Settings icon of the query above the SQL editor or Grid view. The option is called “Materialize” or “Sync to data warehouse”:
When you activate Materialize, the query will be executed (refreshed) once every hour and the result will be stored in a table in the data warehouse, under the schema named “Materialize”.
Materialize from a script
You can also materialize a query from a low-code Python script in Peliqan, e.g. as part of a custom pipeline. In order to run materialize from a script, you can use pq.materialize(). Examples:
result = pq.materialize([(table_id, 'target_table_name')], 'target_schema_name')
# Materialize one query
result = pq.materialize([(1234, 'materialized_query')], 'my_materialized_data')
# Materialize multiple queries
result = pq.materialize([(1234, 'materialized_query1'), (5678, 'materialized_query2')], 'my_materialized_data')You can find the id of the table (or query) to materialize by opening it in the grid view or query editor, the id is the last number in the URL.
When you materialize from a script, please make sure to disable the above "built-in" materialize on the query or table.
When to use materialize
You should materialize a query for one of two reasons:
- Speed up complex queries
- Make tables available to BI tools and other applications outside of Peliqan (in case the option “Create as view” on your SQL query is not available)