Create Reltio object type staging tables in Snowflake
Learn how to create a staging table for each Reltio object type in Snowflake.
Before you create Reltio object type staging tables, you must have created a database schema. For more information, see topic Create a Snowflake schema. Reltio uses Snowflake staging tables to temporarily hold entity, relationship, interaction, or activity data exported from your tenant. These tables must follow a strict schema so that Snowpipe and downstream SQL tasks can process the data correctly. Mismatches in structure or column names will cause validation or task failures.
All data in Snowflake is stored in database tables. Create a staging table for each Reltio object type: entities, relationships, interactions, matches, merges, and links.If you're migrating from Reltio Connected Data for Snowflake to the Reltio Data Pipeline for Snowflake, you might want to use legacy-compatible table formats for Entity types Otherwise, we recommend you use standard tables.
To create Reltio object type staging tables in your Snowflake environment:
- In the Snowflake application worksheet area, create a new SQL worksheet.
- In the SQL worksheet, run these commands to create a staging table for each Reltio object type. Run each command individually and then check the result in step 3 before creating a staging table for the next object type:
Entities staging table Standard
Legacy (optional)CREATE TABLE "<database_name>"."<schema_name>"."entities" ("uri"VARCHAR,"version"NUMBER,"timestamp"NUMBER (13,0 ), "type" VARCHAR, "attributes" VARIANT, "crosswalks" VARIANT, "analyticsAttributes" VARIANT, "createdBy" VARCHAR, "createdTime" NUMBER, "updatedBy" VARCHAR, "updatedTime" NUMBER, "commitTime" NUMBER, "startDate" NUMBER, "endDate" NUMBER, "active" BOOLEAN, "tags" VARIANT)CLUSTER BY ("type","active" );CREATE TABLE "<database_name>"."<schema_name>"."entity_<entityType>_ov" ("uri" VARCHAR, "timestamp" NUMBER(13,0), "deleted" BOOLEAN, "linked" BOOLEAN, "version" NUMBER, "json" VARIANT, "endDate" NUMBER);Relations staging table CREATE TABLE "<database_name>"."<schema_name>"."relations" ("uri" VARCHAR, "version" NUMBER, "timestamp" NUMBER(13,0), "type" VARCHAR, "attributes" VARIANT, "crosswalks" VARIANT, "startObject" VARIANT, "endObject" VARIANT, "startRefPinned" BOOLEAN, "startRefIgnored" BOOLEAN, "endRefPinned" BOOLEAN, "endRefIgnored" BOOLEAN, "createdBy" VARCHAR, "createdTime" NUMBER, "updatedBy" VARCHAR, "updatedTime" NUMBER, "commitTime" NUMBER, "startDate" NUMBER, "endDate" NUMBER, "active" BOOLEAN) CLUSTER BY ("type", "active");Interactions staging table CREATE TABLE "<database_name>"."<schema_name>"."interactions" ("uri" VARCHAR, "version" NUMBER, "type" VARCHAR, "attributes" VARIANT, "crosswalks" VARIANT, "members" VARIANT, "createdBy" VARCHAR, "createdTime" NUMBER, "updatedBy" VARCHAR, "updatedTime" NUMBER, "commitTime" NUMBER, "timestamp" NUMBER(13,0), "active" BOOLEAN) CLUSTER BY ("type", "active");Matches staging table CREATE TABLE "<database_name>"."<schema_name>"."matches" ("entityId" VARCHAR, "potential_matches" VARIANT, "not_matches" VARIANT, "manual_matches" VARIANT, "timestamp" NUMBER(13,0), "version" NUMBER, "active" BOOLEAN) CLUSTER BY ("active");Merges staging table CREATE TABLE "<database_name>"."<schema_name>"."merges" ("mergeKey" VARCHAR, "winnerId" VARCHAR, "loserId" VARCHAR, "timestamp" NUMBER(13,0), "matchRules" VARIANT, "mergeRulesUris" VARIANT, "type" VARCHAR, "active" BOOLEAN) CLUSTER BY ("type", "active");Links staging table CREATE TABLE "<database_name>"."<schema_name>"."links" ("winnerId" VARCHAR, "loserId" VARCHAR, "timestamp" NUMBER(13,0), "active" BOOLEAN) CLUSTER BY ("active");Note: When you reference storage or notification integrations in Snowflake, always use the UPPERCASE version of the name (for example:RELTIO_SNOWFLAKE_STORAGE_INT).Snowflake object names are case-sensitive unless quoted, and mismatched casing will cause integration or COPY INTO errors.where:-
<database_name>: Is the name of the database you created in Create a Snowflake database.
-
<schema_name>: Is the name of the schema you created in Create a Snowflake schema.
-
<entityType>: Is the Reltio entity type you want to create staging tables for.
-
- In the SQL worksheet, view the Successfully created notification message.
For general information, see Create table in Snowflake SQL Command Reference.