Closed Beta: Custom Persistence DDL for AML Query Models

Product Releases
1

Hi folks,

As you may have known, Model Persistence persists the data of your Query Models as physical tables in your Data Warehouse.
Thus, it is a common need to configure and optimize those persisted physical tables using Data Warehouse table optimization techniques, such as:

By applying Data Warehouse optimizations to your persisted tables, your Holistics reports will gain even more performance benefits from Model Persistences.

Seeing such a need that is so vital to your BI/query performance, we have developed a new feature called Custom Persistence DDL :art: :writing_hand: . Using Custom Persistence DDL, you can now freely apply Data Warehouse optimization features and techniques to improve the performance of your persisted tables!

Here is an example of Custom Persistence DDL that creates database indexes for your persisted table:

Model orders_fact {
  type: 'query'
  query: @sql
    /* query model's sql */
  ;;

  persistence: FullPersistence {
    schema: 'persisted'

    // BEGIN custom_ddl declaration
    custom_ddl: @sql
      /* Run parsed_query and persist it into persisted_table */
      CREATE TABLE {{ persisted_table }}
      AS {{ parsed_query }};

      /* Add indexes to the persisted_table */
      CREATE INDEX {{ index_name }} ON {{ persisted_table }} (order_created_at);
      CREATE INDEX {{ index_name }} ON {{ persisted_table }} ((order_created_at::date));
      CREATE INDEX {{ index_name }} ON {{ persisted_table }} (user_id);
      CREATE INDEX {{ index_name }} ON {{ persisted_table }} (status);
      CREATE INDEX {{ index_name }} ON {{ persisted_table }} (product_id);
      CREATE INDEX {{ index_name }} ON {{ persisted_table }} (merchant_id);
      CREATE INDEX {{ index_name }} ON {{ persisted_table }} (order_id, product_id);
    ;;
    // END custom_ddl declaration
  }

And here is how you can define Custom Persistence DDL in your Query Model definition:
custom_ddl_template 2.gif

To use this feature now, please head to the Closed Beta form!
Kindly note that this feature is only available to Holistics 4.0 (that provides as-code Data Modeling).

For more details, please see our docs.

Cheers! :metal:

2 Likes