Define a view to create a separate PDT that gets generated each day with a title like “pdt_20190219”

Knowledge Drop

Last tested: Feb 19, 2019
 

Turns out we can use liquid in the create_process part of the derived table for this!
We use this in the first sql_step to define the name of the PDT to generate.

view: yesterday_data {

derived_table: {

explore_source: order_items {

column: pk2_created_date { field: order_items.created_date}

column: pk2_brand { field: products.brand }

column: total_orders {field: order_items.count }

filters: {

field: order_items.created_date

value: "1 days ago for 1 day"

}

}

}

}

explore: table_date {}

view: table_date {

derived_table: {

datagroup_trigger: incremental_pdt_default_datagroup

create_process: {

sql_step:

CREATE TABLE db_scratch.table_{{'now' | date: "%s" | minus : 86400 | date: "%Y%m%d" }}

AS ${yesterday_data.SQL_TABLE_NAME} ;;

sql_step: CREATE TABLE ${SQL_TABLE_NAME} AS SELECT 1 as one ;;

This content is subject to limited support.                

Version history
Last update:
‎05-07-2021 09:48 AM
Updated by: