The Analyze Direct data model includes two types of identifier fields: Surrogate Key fields (for relationships) and Guaranteed Unique ID fields (for persistent references). This article explains how to use each field type to keep joins, filters, saved reports, and change tracking reliable over time.

Key Fields and ID Fields in Analyze Direct

This section defines Surrogate Key fields and Guaranteed Unique ID fields, and explains when to use each.

Identifier Field Types

Analyze Direct uses the following identifier field types:

• Surrogate Key fields (for example, COURSE_KEY).
• Guaranteed Unique ID fields (for example, COURSE_ID).

What Is a Surrogate Key?

A Surrogate Key is a system-generated internal identifier used to maintain relationships between tables in the data warehouse.

Examples

The following are common Surrogate Key fields:

• COURSE_KEY
• USER_KEY
• SESSION_KEY

Characteristics

Surrogate Keys are:

• Numeric.
• Optimized for joins and warehouse performance.
• Used to relate fact and dimension tables.
• Not guaranteed to remain constant over time.
• May change during data rebuilds, migrations, or model updates.

Surrogate Keys are designed for internal relational mechanics and not long-term filtering.

What Is a Guaranteed Unique Identifier?

A Unique ID field represents the true, immutable business identifier of the object.

Examples

The following are common Unique ID fields:

• COURSE_ID
• USER_ID
• SESSION_ID

Characteristics

Unique ID fields are:

• Guaranteed unique.
• Immutable for the life of the object.
• Safe for saved filters, integrations, and persistent reporting.

If a report needs to consistently reference the same course over time, the filter must use COURSE_ID and not COURSE_KEY.

Key Takeaway

Use Surrogate Keys for joins and Unique IDs for filters and persistent references.

• Surrogate Keys maintain relationships.
• Unique IDs provide an immutable reference.

Surrogate Key values are subject to change. If a filter must remain correct over time, it must use the immutable *_ID field and not the *_KEY field.

Best Practices in SQL

Use Surrogate Keys to join related tables, and use Unique IDs when filtering for specific records.

Correct: Using Surrogate Keys in JOIN Statements

Surrogate Keys are appropriate when joining tables within your Snowflake warehouse.

In this example, COURSE_KEY maintains the table relationship and COURSE_ID is returned as the stable reference.

SELECT
  c.COURSE_ID,
  c.COURSE_NAME,
  e.USER_ID,
  e.COMPLETION_DATE
FROM FACT_ENROLLMENT e
JOIN DIM_COURSE c
  ON e.COURSE_KEY = c.COURSE_KEY;

Correct: Using Unique IDs in WHERE Clauses

When filtering for specific records, always use immutable ID fields.

This helps ensure filters continue to work correctly and reports remain accurate after warehouse updates.

SELECT
  USER_ID,
  COMPLETION_DATE
FROM FACT_ENROLLMENT
WHERE COURSE_ID = '964e6eee-56dd-4d4c-9f77-10b89a52a0fe';

Incorrect: Filtering with Surrogate Keys

Filtering with Surrogate Keys is risky because key values can be regenerated or remapped. This can cause saved reports to silently return incorrect results or filters to break without warning.

SELECT
  USER_ID,
  COMPLETION_DATE
FROM FACT_ENROLLMENT
WHERE COURSE_KEY = 12345;

Best Practice Pattern

Use the following pattern in your SQL and reporting tools:

• JOIN using Surrogate Keys.
• FILTER using immutable Unique Identifiers.

Example Combining Both

This example joins using COURSE_KEY and filters using COURSE_ID.

SELECT
  c.COURSE_ID,
  COUNT(*) AS Total_Completions
FROM FACT_ENROLLMENT e
JOIN DIM_COURSE c
  ON e.COURSE_KEY = c.COURSE_KEY
WHERE c.COURSE_ID IN (
  '964e6eee-56dd-4d4c-9f77-10b89a52a0fe',
  'b85a49c4-8368-4223-8596-a643d2145ed4'
)
GROUP BY c.COURSE_ID;

Field Use Case Summary

The following table summarizes when to use each field type.

Proper Use in Upserts 

An upsert operation updates an existing row, or inserts a new row if one does not exist. This requires a stable way to identify whether a record already exists. 

Analyze Direct provides a read-only version of your LMS data in your Snowflake warehouse.  Actions described in this section can be performed on copies of your LMS data, for example, COPY_OF_COURSE is a copy of DIM_COURSE.

Why Surrogate Keys Must Not Be Used in Upserts

Surrogate Keys are generated after insertion, may change across data loads, and do not represent real-world identity. Using them in upserts causes duplicate records, failed matches, and data corruption over time. 

Incorrect: Upsert Using a Surrogate Key

The following example shows an incorrect merge statement that uses COURSE_KEY (a Surrogate Key) as the match condition. The source system typically does not control COURSE_KEY, keys may not align between staging and target, and the result is duplicate or missed records.

MERGE INTO COPY_OF_COURSE target
USING STAGING_COURSE source
ON target.COURSE_KEY = source.COURSE_KEY  -- Incorrect: surrogate key
WHEN MATCHED THEN
    UPDATE SET target.COURSE_NAME = source.COURSE_NAME
WHEN NOT MATCHED THEN
    INSERT (COURSE_KEY, COURSE_NAME)
    VALUES (source.COURSE_KEY, source.COURSE_NAME);

Correct: Upsert Using a Unique Identifier

The following example shows a correct merge statement that uses COURSE_ID (a Unique Identifier) as the match condition. Since COURSE_ID is stable and meaningful, this approach ensures correct matching across loads and prevents record duplication.

MERGE INTO COPY_OF_COURSE target
USING STAGING_COURSE source
ON target.COURSE_ID = source.COURSE_ID  -- Correct: unique identifier
WHEN MATCHED THEN
    UPDATE SET
        target.COURSE_NAME = source.COURSE_NAME,
        target.LAST_UPDATED_DATE = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (COURSE_ID, COURSE_NAME, CREATED_DATE)
    VALUES (source.COURSE_ID, source.COURSE_NAME, CURRENT_TIMESTAMP);

Common Upsert Errors

The following table describes common errors made when writing upsert statements and the impact of each.

Key Takeaway

Surrogate Keys maintain relationships between tables. Unique IDs define the identity of a record. Any process that depends on stability over time, such as filters, reports, integrations, or upserts, must use the immutable *_ID field, never the *_KEY.

List of Analyze Direct Key Fields and Corresponding Unique ID Fields

The following table maps Surrogate Key fields to their guaranteed Unique Identifier equivalents.