Files Required for ID Unification Workflow

To set up a Unification Workflow, the following two files are essential:

1. Dig file : Used to invoke the Unification Workflow.
2. YAML file : Defines the data sources and stitching keys used for ID unification.

This document provides detailed instructions on how to write these files. It includes all available options, so refer to this guide if you're uncertain about specific configurations.

Writing a dig File

The following example serves as a template for creating a dig file. The file primarily sets options for running the Unification Workflow (WF). You can use this as a basic template for your implementation.

+call_unification:
  http_call>: https://api-cdp.treasuredata.com/unifications/workflow_call
  headers:
    - authorization: ${secret:td.apikey}
  method: POST
  retry: true
  content_format: json
  content:
    run_canonical_ids: true
    run_enrichments: true
    run_master_tables: true

    full_refresh: true
    keep_debug_tables: true

    unification:
      !include : unification_ex1.yml

http_call Operator

This operator triggers the Unification Workflow. The endpoint URL depends on your account's region:

Headers

Authentication is performed using the td.apikey secret, which contains the Master API Key for your account. No manual setup of the secret is required because it is auto-generated for ID Unification WF.

Content Options

The content section defines execution options for the ID Unification Workflow and includes a reference to a yml file.

Writing a yml File

The example below demonstrates how to write the referenced yml file (unification_ex1.yml) from the dig file example.

name: production
keys:
  - name: td_client_id
    valid_regexp: "[0-9a-fA-F]{8}-..."
    invalid_texts: ['']

  - name: td_global_id
    valid_regexp: "[0-9a-fA-F]{8}-..."
    invalid_texts: ['', '0000000000-...']

  - name: email
    valid_regexp: ".*@.*"

tables:
  - database: prod
    table: pageviews
    incremental_columns: [updated_at, id]
    key_columns:
      - {column: td_client_id, key: td_client_id}

 - database: brand2
    table: pageviews
    as: brand2_pageviews
    key_columns:
      - {column: td_client_id, key: td_client_id}
      - {column: td_global_id, key: td_global_id}
      - {column: email, key: email}

  - database: prod
    table: contacts
    key_columns:
      - {column: email, key: email}

canonical_ids:
  - name: browser_id
    merge_by_keys: [td_client_id, td_global_id]

master_tables:
  - name: marketing_master
    canonical_id: browser_id

    attributes:
      - name: browser_id
        source_canonical_id: browser_id

      - name: email
        source_columns:
          - {table: contacts, column: email}

Names

Specifies the name of this Unification. This name will be entered as the database name (cdp_unification_${name}) that will store the output of the ID Unification. In the above example, the cdp_unification_production database is created and the output is stored.

Keys

Define the keys used for stitching IDs across all tables.

Tips: Regular Expression for td_client_id / td_global_id

The values for td_client_id and td_global_id are similar to:

• 307423ab-9cbc-4bca-9cae-05c3700cc8f2
• fc6422e1-48bb-4396-b7aa-bdd2075c000d

These can be uniformly expressed as the following regular expression:

• [0-9a-fA-F]{8}-[0-9a-fA-F]{4}[0-9a-fA-F]{4}[0-9a-fA-F]{4}[0-9a-fA-F]{12}

Tables

Configure the tables used in the unification process.

Tips: When the column and key differ in key_columns

In most cases, the values of column and key may match. However, there are instances where the identifier recorded under the column name cookie_id in one table needs to be stitched together with the td_client_id column from another table (registered as td_client_id in keys).

For such cases, it would be written as:

• {column: cookie_id, key: td_client_id}

Tips: Cases where as: should be used

This option is necessary in situations like the current example, where there are tables with the same name, such as pageviews, in different databases, and both are used as source tables. When creating an enriched table in the destination database, the table names would conflict. To avoid this collision, the as: option is used.

canonical_ids

Defines how IDs are unified and assigns canonical identifiers.

Example:

canonical_ids:
  - name: browser_id
    merge_by_keys: [td_client_id, td_global_id, td_ssc_id]

  - name: marketing_id
    merge_by_canonical_ids: [browser_id]
    merge_by_keys: [email]
    source_tables: [pageviews, contacts]

  - name: contact_id
    merge_by_canonical_ids: [browser_id]
    merge_by_keys: [membership_id, email]
    merge_iterations: 3
    incremental_merge_iterations: 2

Options:

master_tables

Specifies the master output tables and attributes for unified profiles.

Example:

master_tables:
  - name: marketing_master
    canonical_id: browser_id
    attributes:
      - name: email
        source_columns:
          - {table: contacts, column: email}

Options for canonical_ids

(Reference) Naming a canonical_id

When naming a canonical_id, it is important to make the name clear enough to indicate what the unified ID represents. Additionally, keep the name concise, as it will appear in the output table name. For example:

• browser_id, unified_cookie_id
  • Used when integrating identifiers such as td_client_id , td_global_id , td_ssc_id , and cookie_id .
• person_id, customer_id, cust_id, member_id
  • Used when integrating keys containing personal information such as email or member_id .

Options for master_tables

master_tables:
  - name: marketing_master
    canonical_id: marketing_id
    attributes:
      - name: browser_id
        source_canonical_id: browser_id

      - name: firstname
        source_columns:
          - {table: pageviews}
          - {table: form_submits, column: first_name}

      - name: birthdate
        valid_regexp: "[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}"
        invalid_texts: ['']
        source_columns:
          - {table: contacts, priority: 1}
          - {table: pageviews, priority: 2}
          - {table: form_submits, priority: 3}

      - name: all_emails
        array_elements: 5
        source_columns:
          - {table: contacts, order: last, order_by: time, priority: 1}
          - {table: pageviews, order: first, order_by: time, priority: 2}
          - {table: form_submits, order: first, order_by: time, priority: 2}

Options for attributes:

Options for source_columns:

Notes on Setting the Same priority:

If multiple tables are assigned the same priority:, they must share the same sorting order. If different sorting orders are specified, an error will occur. For tables with the same priority level, the specified sorting is uniformly applied, and values are selected sequentially from top to bottom.