See related
No related articles

Snowflake data sharing

Last Update: Oct 2024 • Est. Read Time: 2 MIN
To check plan availability, see the pricing page.

If you are using or considering Snowflake as your Business Intelligence (BI) solution, then you can utilize Kustomer's Snowflake data warehouse to access your data.

Kustomer currently supports sharing with AWS users in the following regions: 

  • us-east-1
  • us-west-2
  • eu-west-1
  • eu-central-1

June 2023 update: This feature is no longer available to new customers.

In this article

Overview

If you want access to your Kustomer data to run your own analytics/reports, then Snowflake is a great solution as we can give you your data with minimal setup, using Snowflake's data sharing features.

Currently, data is pulled from Kustomer every 10-15 minutes and copied to our Snowflake data warehouse. As soon as the data is in Snowflake, it should be available to you and ready to query. This service does not impact your API rate limits.

Data

All of Kustomer's standard object data is available as well as additional lookup tables. The tables all have the same structure, although the data field holds specific semi-structured JSON data for the respective object.

Snowflake Kustomer tables

Each table has the same structure - event data is stored as JSON in the DATA column. Here, you will find all the standard attributes of your data as well as any custom fields you may have set.

Companies - standard object company data
REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Customers - standard object customer data

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Conversations - standard object conversation data

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Messages - standard object message data

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Custom Objects (KObjects) - Data of any custom klasses you may have set up in Kustomer

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Teams - lookup data for teams

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Users - lookup data for users

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Shortcuts - lookup data for any shortcuts you may have set up in Kustomer

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


 Metadata - lookup data for any multi level lists or custom fields you have set up in Kustomer

REV {"type":"FIXED","precision":38,"scale":0,"nullable":true}

DATA {"type":"VARIANT","nullable":true}

UPDATED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}

ID {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

ORG {"type":"TEXT","length":25,"byteLength":100,"nullable":false,"fixed":false}

IMPORTED_AT {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}


Table definitions

The REV field holds the revision of the record. It is here to ensure that we do not overwrite a record with older data as we sometimes cannot ensure that data comes in through in order.

The DATA field holds the bulk of the data in a semi structured JSON format. A nice advantage of holding semi structured data this way is that if you add an additional custom field to an object it will automatically become available without having to update any schemas. The same is true for any custom objects you may have.

The UPDATED_AT is the date the record was last updated.

The ID field is the internal ID of the record.

The ORG field is the internal organization ID within Kustomer. You will only see your org ID.

The IMPORTED_AT field is when the record was copied to Snowflake and is for Kustomer reference.

Example queries

Top 10 total messages sent by user:

SELECT count(*) AS sent, u.data:name::string As name 

FROM messages m 

   INNER JOIN users u ON m.data:createdBy = u.id

WHERE m.data:createdBy IS NOT NULL 

GROUP BY u.data:name

ORDER BY sent DESC

LIMIT 10;