Create Reltio object type staging tables in Snowflake
Learn how to create a staging table for each Reltio object type in Snowflake.
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.
- 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");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.