Snowflake data sharing
Last Update: Oct 2024 • Est. Read Time: 2 MINIf 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;