Version: Latest (0.18)0.170.160.150.140.130.120.110.100.9.40.9.30.9.10.9.00.8.0

Identity Stitching

This guide provides a detailed walkthrough on how to use a PB project and create output tables in a warehouse for a custom identity stitching model.

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.

Output

After running the project, you can view the generated material tables:

A sample output containing the results in Snowflake:

Profiles project includes an ID stitcher model (default_id_stitcher) by default even if you do not define any specs for creating one. It takes all the input sources and ID types defined in the file inputs.yaml file. Also, it creates a custom ID stitcher when you define an ID stitcher model explicitly along with the specs.

Sample project for Custom ID Stitcher

This sample project considers multiple user identifiers in different warehouse tables to ties them together to create a unified user profile. 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.

There can be different ID types for an entity. You can include all such identifiers in the id_types field under entities.

In case of id_stitcher model, the main_id for the entity is rudder_id (predefined ID type) by default. For other models, any other ID type can be the main_id, for example session_id. Hence, if you want to specify the ID type of a column as a primary identifier, you can specify main_id.

# Project name
name: sample_id_stitching
# Project's yaml schema version
schema_version: 80
# Warehouse connection
connection: test
# Folder containing models
model_folders:
  - models
# Entities in this project and their ids.
entities:
  - name: user
    id_stitcher: models/user_id_stitcher # modelRef of custom ID stitcher model
    id_types:
      - user_id # one of the identifier from your data source.
      - email
id_types:
  - name: user_id
  - name: email
    filters:
      - type: include
        regex: ".+@.+"
      - type: exclude
        value: "test@company.com"

Input

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

inputs:
- name: rsIdentifies
  contract: # constraints that a model adheres to
    is_optional: false
    is_event_stream: true
    with_entity_ids:
      - user
    with_columns:
      - name: timestamp
      - name: user_id
      - name: anonymous_id
      - name: email
  app_defaults:
    table: rudder_events_production.web.identifies # one of the WH table RudderStack generates when processing identify or track events.
    occurred_at_col: timestamp
    ids:
      - select: "user_id" # kind of identity sql to pick this column from above table.
        type: user_id
        entity: user # as defined in project file
        to_default_stitcher: true # default value
      - select: "anonymous_id"
        type: anonymous_id
        entity: user
        to_default_stitcher: true
      - select: "lower(email)" # can use sql.
        type: email
        entity: user
        to_default_stitcher: true
- name: rsTracks
  contract:
    is_optional: false
    is_event_stream: true
    with_entity_ids:
      - user
    with_columns:
      - name: timestamp
      - name: user_id
      - name: anonymous_id
  app_defaults:
    table: rudder_events_production.web.tracks # another table in WH maintained by RudderStack processing track events.
    occurred_at_col: timestamp
    ids:
      - select: "user_id"
        type: user_id
        entity: user
        to_default_stitcher: true
      - select: "anonymous_id"
        type: anonymous_id
        entity: user
        to_default_stitcher: true

Columns specified under ids field are automatically sent for identity stitching unless you specify to_default_stitcher as false.

Model

Profiles Identity stitching model maps and unifies all the specified identifiers (in pb_project.yaml file) across different platforms. It tracks the user journey uniquely across all the data sources and stitches them together to a rudder_id.

A sample profiles.yaml file specifying an identity stitching model (user_id_stitcher) with relevant inputs:

models:
  - name: user_id_stitcher
    model_type: id_stitcher
    model_spec:
      entity_key: user
      materialization:
        run_type: incremental # default value is `discrete` for a custom ID stitcher and `incremental` for the default ID stitcher.
      incremental_timedelta: 12h
      edge_sources:
        - from: inputs/rsIdentifies
        - from: inputs/rsTracks

Model specification fields

Use cases

This section describes some common identity stitching use cases:

• Identifiers from multiple data sources: You can consider multiple identifiers and tables by:

  • Adding entities in pb_project.yaml representing identifiers.
  • Adding references to table and corresponding sql in models/inputs.yaml
  • Adding table reference names defined in models/inputs.yaml as edge_sources in your model definition.

• Leverage Sql Support: You can use SQL in your models/inputs.yaml to achieve different scenarios. For example, you want to tag all the internal users in your organization as one entity. You can use the email domain as the identifier by adding a SQL query to extract the email domain as the identifier value: lower(split_part({{email_col}}, '@', 2))

• Custom ID Stitcher: You can define a custom ID stitcher by defining the required id stitching model in models/profiles.yaml.

Questions? Contact us by email or on Slack