ID mapping table analysis rule

In AWS Clean Rooms, an ID mapping table analysis rule isn't a standalone analysis rule. This type of analysis rule is managed by AWS Clean Rooms and is used to join disparate identity data to facilitate querying. It's automatically added to ID mapping tables and can't be edited. It inherits the behaviors of the other analysis rules in the collaboration – as long as those analysis rules are homogeneous.

The ID mapping table analysis rule enforces security on an ID mapping table. It restricts a collaboration member from directly selecting or inspecting the non-overlap population between the two members' datasets using the ID mapping table. The ID mapping table analysis rule is used to protect the sensitive data in the ID mapping table when used in queries with other analysis rules implicitly.

With the ID mapping table analysis rule, AWS Clean Rooms enforces an overlap on both sides of the ID mapping table in expanded SQL. This enables you to do the following tasks:

The supported query structure and syntax are defined in ID mapping table query structure and syntax.

The parameters of the analysis rule, defined in ID mapping table analysis rule query controls, include query controls and query results controls. Its query controls include the ability to require overlap of the ID mapping table in JOIN statements (that is, requireOverlap).

ID mapping table query structure and syntax

Queries on tables that have an ID mapping table analysis rule must adhere to the following syntax.

--select_list_expression
SELECT 
provider.data_col, consumer.data_col 

--table_expression
FROM provider

JOIN idMappingTable idmt ON provider.id = idmt.sourceId

JOIN consumer ON consumer.id = idmt.targetId

                        

Collaboration tables

The following tables represent configured tables that exist in an AWS Clean Rooms collaboration. The id column in both the cr_drivers_license and cr_insurance tables represents a column to match with the ID mapping table.

cr_drivers_license

cr_insurance

ID mapping table

The following table represents an existing ID mapping table that matches on the cr_drivers_license and cr_insurance tables. Not all entries will have IDs for both collaboration tables.

The ID mapping table analysis rule only allows for queries to run on the set of overlapping data, which would look as follows:

Example queries

The following examples show valid locations for the ID mapping table joins:

-- Single ID mapping table
SELECT
    [ select_items ]
FROM
    cr_drivers_license cr_dl
    [ INNER | LEFT | RIGHT ] JOIN cr_identity_mapping_table idmt ON idmt.cr_drivers_license_id = cr_dl.id
    [ INNER | LEFT | RIGHT ] JOIN cr_insurance cr_in             ON idmt.cr_insurance_id       = cr_in.id
;

-- Single ID mapping table (Subquery)
SELECT
    [ select_items ]
FROM (
    SELECT
        [ select_items ]
    FROM
        cr_drivers_license cr_dl
        [ INNER | LEFT | RIGHT ] JOIN cr_identity_mapping_table idmt ON idmt.cr_drivers_license_id = cr_dl.id
        [ INNER | LEFT | RIGHT ] JOIN cr_insurance cr_in             ON idmt.cr_insurance_id       = cr_in.id
)
;

-- Single ID mapping table (CTE)
WITH
    matched_ids AS (
        SELECT
            [ select_items ]
        FROM
            cr_drivers_license cr_dl
            [ INNER | LEFT | RIGHT ] JOIN cr_identity_mapping_table idmt ON idmt.cr_drivers_license_id = cr_dl.id
            [ INNER | LEFT | RIGHT ] JOIN cr_insurance cr_in             ON idmt.cr_insurance_id       = cr_in.id
    )
SELECT
    [ select_items ]
FROM
    matched_ids
;

Considerations

For ID mapping table query structure and syntax, be aware of the following:

ID mapping table analysis rule query controls

With ID mapping table query controls, AWS Clean Rooms controls how the columns in your table are used to query the table. For example, it controls which columns are used for joining, and which columns require overlap. The ID mapping table analysis rule also includes functionality that enables you to allow the sourceID, the targetID, or both, to be projected without requiring a JOIN.

The following table explains each control.

ID mapping table analysis rule predefined structure

The predefined structure for an ID mapping table analysis rule comes with default protections that are applied to the sourceID and targetID. This means that the column with protections applied must be used in queries.

You can configure the ID mapping table analysis rule in the following ways:

The following example shows a predefined structure for an ID mapping table analysis rule with the default protections applied to the sourceID and targetID. In this example, the ID mapping table analysis rule only allows an INNER JOIN on both the sourceID column and the targetID column.

{
  "joinColumns": [
    "source_id",
    "target_id"
  ],
  "queryConstraints": [
    {
      "requireOverlap": {
        "columns": [
          "source_id",
          "target_id"
        ]
      }
    }
  ],
  "dimensionColumns": [] // columns that can be used in SELECT and JOIN
}

The following example shows a predefined structure for an ID mapping table analysis rule with protections applied to the targetID. In this example, the ID mapping table analysis rule only allows an INNER JOIN on the sourceID column.

{
  "joinColumns": [
    "source_id",
    "target_id"
  ],
  "queryConstraints": [
    {
      "requireOverlap": {
        "columns": [
          "target_id"
        ]
      }
    }
  ],
  "dimensionColumns": [
    "source_id"
  ]
}

The following example shows a predefined structure for an ID mapping table analysis rule with protections applied to the sourceID. In this example, the ID mapping table analysis rule only allows an INNER JOIN on the targetID column.

{
  "joinColumns": [
    "source_id",
    "target_id"
  ],
  "queryConstraints": [
    {
      "requireOverlap": {
        "columns": [
          "source_id"
        ]
      }
    }
  ],
  "dimensionColumns": [
    "target_id"
  ]
}

The following example shows a predefined structure for an ID mapping table analysis rule without protections applied to the sourceID or targetID. In this example, the ID mapping table analysis rule allows an INNER JOIN on both the sourceID column and the targetID column.

{
  "joinColumns": [
    "source_id",
    "target_id"
  ],
  "queryConstraints": [
    {
      "requireOverlap": {
        "columns": []
      }
    }
  ],
  "dimensionColumns": [
    "source_id",
    "target_id"
  ]
}

ID mapping table analysis rule – example

Rather than writing a long waterfall statement that references Personally Identifiable Information (PII), for example, companies can use the ID mapping table analysis rule to use multi-party LiveRamp transcoding. The following example demonstrates how you can collaborate in AWS Clean Rooms using the ID mapping table analysis rule.

Company A is an advertiser who has customer and sales data, which will be used as a source. Company A also performs transcoding on behalf of the parties in the collaboration, and brings the LiveRamp credentials.

Company B is a publisher who has event data, which will be used as a target.

To create a collaboration that enables the ID mapping table analysis in collaboration, the companies do the following: