Cases data in the Connect Customer analytics data lake

This topic details the content in the Connect Customer data lake cases tables. The tables list the column, type, and description of the content.

There are two ways to access the analytics data lake and configure data to be shared:

If you are unable to access the scheduling tables by using Option 1, try using Option 2.

Case events

Table name: case_events

Description: Records case lifecycle events (created, updated, deleted), including case metadata, status changes, template information, and custom field values for Connect Cases.

Primary key: instance_id, event_id

Partition key: event_timestamp (daily)

Join keys:

Sample queries

The following example query in Athena creates a view that flattens custom fields from the case events table:

CREATE VIEW case_events_flattened AS
SELECT
    ce.event_id,
    ce.case_id,
    ce.event_timestamp,
    ce.event_type,
    ce.instance_id,
    -- ... other system fields
    -- Flattened custom fields
    cf.id AS custom_field_id,
    cf.field_name AS custom_field_name,
    CASE
        WHEN cf.string_value IS NOT NULL THEN 'string'
        WHEN cf.double_value IS NOT NULL THEN 'double'
        WHEN cf.timestamp_value IS NOT NULL THEN 'timestamp'
        WHEN cf.boolean_value IS NOT NULL THEN 'boolean'
        ELSE 'unknown'
    END AS custom_field_type,
    cf.string_value AS custom_field_string_value,
    cf.double_value AS custom_field_double_value,
    -- Cast milliseconds to timestamp
    CAST(from_unixtime(cf.timestamp_value / 1000.0) AS TIMESTAMP) AS custom_field_timestamp_value,
    cf.boolean_value AS custom_field_boolean_value
FROM "case_events resource link table name" ce
CROSS JOIN UNNEST(ce.custom_fields) AS t(cf)

The following example creates a view that returns the latest state for each case:

CREATE OR REPLACE VIEW latest_case_state AS
SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY case_id, instance_id
            ORDER BY event_timestamp DESC
        ) AS rn
    FROM "case_events resource link table name"
) t
WHERE rn = 1;

Case related item events

Table name: case_related_item_events

Description: Records events for items related to cases, including comments, contacts, file attachments, SLA tracking, and custom related items.

Primary key: instance_id, event_id

Partition key: event_timestamp (daily)

Join keys: