Peliqan allows you to replicate any table from an external database, into the Peliqan data warehouse or any other target that is connected in Peliqan (e.g. Snowflake, SQL Server on Azure or Fabric, Redshift, Bigquery, Postgres etc). The same feature can be used to materialize a query into a physical table.

Replicate a table

Select “Replicate table” from the Settings in the top menu (gear icon):

Configure the Replication:

Materialize a query

Different options for SQL queries in Peliqan

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. By default queries only exist in Peliqan. You can enable “Create as view” to make the query visible as a view in the data warehouse. Or you can Materialize a query (turn it into a physical table), which is the opposite of ephemeral.

For each SQL query in Peliqan you have 3 options:

SQL Query exists in Peliqan (default)
SQL Query is a view in the data warehouse (and visible by BI tools)
SQL Query is materialized into a physical table

Create a view for your 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.

Enable “Create as view” under Settings (gear icon) in the top menu:

Enable materialize on a query in Peliqan

You can also enable “Materialize” for an SQL query by enabling the “Replicate table” feature on the query, under Settings in the top menu above the query editor:

When you activate Replicate, you can configure the schedule at which your query will be executed and written to a phyiscal (target) table

When to use materialize on a query

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, but only in case the option “Create as view” on your SQL query is not available

Replicate from a script (custom pipeline)

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:

replicate_settings = {
  'schedule': {
    'interval': 3600,
    'weekdays': [ 0, 1, 2, 3, 4, 5, 6],
    'start_time': None
  },
  'table_name': 'mytable',
  'schema_name': 'myschema',
  'incremental_field': None,
  'target_connection': 'Postgres',
  'target_application': 123     # Connection id of the target DWH (e.g. Peliqan DWH)
}

pq.replicate(table_id = 456, enabled = True, settings = replicate_settings)

You can find the id of the table (or query) to replicate/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" Replicate option on the query or table.