Limitation in snowplow__user_identifiers and user_sql Customization in Snowplow Unified Model

If user stitching is required, it is recommended to rely on the default behavior of the Snowplow Unified Model, which assigns identifiers correctly under stitched_userid rather than modifying user_sql or snowplow__user_identifiers.

We do recognize there are specific use cases where using a custom user_id may be desired. This article aims to address such scenarios.

Summary

Users who modify the snowplow__user_identifiers variable or customize snowplow__user_sql should be aware that these changes may not work as expected in specific scenarios.

If any of the identifiers in the list do not persist throughout the session, there is no guarantee that the highest-prestige identifier (first in the list) will take precedence. This is particularly relevant for those adding user_id as the first element in their list, as it may be NULL for some events within a session.

Important Note:
The default Snowplow Unified Model behavior does not exhibit this issue because it only coalesces between domain_userid (for web) and device_user_id (for mobile).

• user_id is not included in user_identifier by default.
• Instead, user_id is used for user stitching via stitched_userid, ensuring identity resolution across sessions.
• Users modifying snowplow__user_identifiers to include user_id risk disrupting the intended user stitching logic unless a workaround is applied.

Root Cause

The issue stems from a limitation of the snowplow__user_identifiers variable, which may not work as expected for users who diverge from the package defaults by modifying either snowplow__user_identifiers or snowplow__user_sql.

By default, the Snowplow Unified Model coalesces between domain_userid (for web) and device_user_id (for mobile) to determine user_identifier. However, users who customize the model—particularly by adding user_id as the first element in the list—should be aware that this may disrupt the expected user stitching logic unless a workaround is applied

Why This Happens

• If any of the identifiers in the user-defined list do not persist throughout the session, there is no guarantee that the highest-prestige identifier (the first one in the list) will actually take precedence.
• This is particularly problematic when user_id is added as the first element in the list, as it may be NULL for some events in the session.
• Since the model applies MAX() at the session level, it may randomly favor another identifier (e.g., domain_userid) over user_id, even if user_id is available in other events in the session.
• The default behavior of the package does not rely on user_id in user_identifier; instead, user_id is used as the default user_stitching_id, which resolves identity separately under stitched_userid.

Users should be mindful that modifying snowplow__user_identifiers to include user_id can lead to inconsistent results in user resolution, unless additional logic is implemented

Affected Models

• Model:snowplow_unified_sessions, snowplow_unified_views
• Variables Involved:
  • snowplow__user_sql
  • snowplow__user_identifiers

Example Query Demonstrating the Issue

SELECT session_identifier, 
       MAX(COALESCE(user_id, domain_userid)) AS user_identifier
FROM (
  SELECT 'a' AS session_identifier, NULL AS user_id, '1' AS domain_userid
  UNION ALL
  SELECT 'a' AS session_identifier, '2' AS user_id, '1' AS domain_userid
  UNION ALL
  SELECT 'b' AS session_identifier, NULL AS user_id, '3' AS domain_userid
  UNION ALL
  SELECT 'b' AS session_identifier, '2' AS user_id, '3' AS domain_userid
) 
GROUP BY session_identifier;

Expected Behavior:

session_identifier | user_identifier  
------------------|----------------  
a                 | 2  
b                 | 2  

Actual Behavior:

session_identifier | user_identifier  
------------------|----------------  
a                 | 2  
b                 | 3  

Session "b" incorrectly selects domain_userid instead of user_id.

Workaround

To ensure a deterministic user selection, modify snowplow__user_sql to:

COALESCE(
    MAX(user_id),
    MAX(domain_userid),
    MAX(network_userid)
) OVER (PARTITION BY session_identifier)

This ensures that MAX(user_id) is computed separately before applying COALESCE(), preventing domain_userid from taking precedence.

Performance Considerations

• This workaround introduces window functions, which may impact query performance in large datasets.
• The Snowplow engineering team is evaluating a more efficient fix and identity stitching approach in a future release.

Related Resources

• Snowplow dbt Identity Stitching