Version: Latest (0.11)0.100.9.40.9.30.9.10.9.00.8.0

SQL Models

This guide provides a detailed walkthrough on how to use a PB project and create SQL Template models using custom SQL queries.

Prerequisites

• Familiarize yourself with:

  • A basic Profile Builder project by following the Profile Builder CLI steps.
  • Structure of a Profile Builder project and the parameters used in different files.

Sample project

The following sections describe how to define your PB project files:

Project detail

The pb_project.yaml file defines the project details such as name, schema version, connection name and the entities which represent different identifiers.

You can define all the identifiers from different input sources you want to stitch together as a single ID (main_id in this example):

name: sample_test
schema_version: 54
connection: test
model_folders:
  - models
entities:
  - name: user
    id_stitcher: models/test_id__
    id_types:
      - test_id
      - exclude_id
include_untimed: true
id_types:
  - name: test_id
    filters:
      - type: include
        regex: "([0-9a-z])*"
      - type: exclude
        value: ""
  - name: exclude_id

Input

The input file (models/inputs.yaml) file includes the input table references and corresponding SQL for the above-mentioned entities:

inputs:
  - name: tbl_a
    app_defaults:
      table: Temp_tbl_a
    occurred_at_col: insert_ts
    ids:
      - select: TRIM(COALESCE(NULL, id1))
        type: test_id
        entity: user
        to_default_stitcher: true
      - select: "id2"
        type: test_id
        entity: user
        to_default_stitcher: true
      - select: "id3"
        type: exclude_id
        entity: user
        to_default_stitcher: true
  - name: tbl_b
    app_defaults:
      view: Temp_view_b
    occurred_at_col: timestamp
    ids:
      - select: "id1"
        type: test_id
        entity: user
        to_default_stitcher: true
      - select: "id2"
        type: test_id
        entity: user
        to_default_stitcher: true
      - select: "id3"
        type: test_id
        entity: user
        to_default_stitcher: true
  - name: tbl_c
    app_defaults:
      table: Temp_tbl_c
    ids:
      - select: "id1"
        type: test_id
        entity: user
        to_default_stitcher: true
      - select: "id2"
        type: test_id
        entity: user
        to_default_stitcher: true

Model

Profiles SQL model lets you write custom SQL queries to achieve advanced use-cases to create desired output tables.

A sample profiles.yaml file specifying a SQL model (test_sql):

models:
- name: test_sql
  model_type: sql_template
  model_spec:
    validity_time: 24h# 1 day
    materialization:                 // optional
      run_type: discrete             // optional [discrete, incremental]
    single_sql: |
        {%- with input1 = this.DeRef("inputs/tbl_a") -%}
          select id1 as new_id1, id2 as new_id2, {{input1}}.*
            from {{input1}}
        {%- endwith -%}        
    occurred_at_col: insert_ts        // optional
    ids:
      - select: "new_id1"
        type: test_id
        entity: user
        to_default_stitcher: true
      - select: "new_id2"
        type: test_id
        entity: user
        to_default_stitcher: true
      - select: "id3"
        type: test_id
        entity: user
        to_default_stitcher: true

Model specification fields

SQL template

You can pass custom SQL queries to the single_sql or multi_sql fields, which is also known as a SQL template. It provides the flexibility to write custom SQL by refering to any of the input sources listed in the inputs.yaml or any model listed in models/profiles.yaml.

The SQL templates follow a set query syntax which serves the purpose of creating a model. Follow the below rules to write SQL templates:

• Write SQL templates in the pongo2 template engine syntax.

• Avoid circular referencing while referencing the models. For example, sql_model_a references sql_model_b and sql_model_b references sql_model_a.

• Use timestamp variable (refers to the start time of the current run) to filter new events.

• this refers to the current model’s material. You can use the following methods to access the material properties available for this:

  • DeRef("path/to/model"): Use this syntax {{ this.DeRef("path/to/model") }} to refer to any model and return a database object corresponding to that model. The database object, in return, gives the actual name of the table/view in the warehouse. Then, generate the output, for example:

  {% with input_table = this.DeRef("inputs/tbl_a") %}
      select a as new_a, b as new_b, {{input_table}}.*
        from {{input_table}}
  {% endwith %}
  

  • GetMaterialization(): Returns a structure with two fields: MaterializationSpec{OutputType, RunType}.
    • OutputType: You must use OutputType with ToSQL() method:
      For example, CREATE OR REPLACE {{this.GetMaterialization().OutputType.ToSQL()}} {{this.GetSelectTargetSQL()}} AS ...
    • RunType: For example, this.GetMaterialization().RunType

Questions? Contact us by email or on Slack