distribution

Usage

view: my_view {
  derived_table: {
    distribution: "customer_id"
    ...
  }
}

Definition

distribution lets you specify the column from a persistent derived table (PDT) or an aggregate table on which to apply a distribution key to spread the data around a cluster. When two tables are joined by the column specified in the distribution parameter, the database can find the joined data on the same node to minimize internode I/O. Currently, distribution works only with Redshift and Aster databases. For other SQL dialects (such as MySQL and Postgres), use indexes instead.

The distribution parameter works only with tables that are persistent, such as PDTs and aggregate tables. distribution is not supported for derived tables without a persistence strategy.

In addition, the distribution parameter is not supported for derived tables that are defined using create_process or sql_create.

Generally speaking, a distribution key should be applied to the column that will be acting as a foreign key (the column you use to join your table to others). Refer to the documentation for your dialect for best practices.

Examples

Create a customer_order_facts persistent derived table with a distribution key on customer_id:

view: customer_order_facts {
  derived_table: {
    explore_source: order {
      column: customer_id { field: order.customer_id }
      column: lifetime_orders { field: order.lifetime_orders }
    }
    datagroup_trigger: order_datagroup
    distribution: "customer_id"
  }
}

Create a customer_order_facts derived table based on a SQL query with a distribution key on customer_id:

view: customer_order_facts {
  derived_table: {
    sql:
      SELECT
        customer_id,
        COUNT(*) AS lifetime_orders
      FROM
        order ;;
    persist_for: "24 hours"
    distribution: "customer_id"
  }
}