Question on Snowflake replication and staging error for CS Tools

[BikeRiderJim]

Hi, have a question and wondering if anyone can help. We have CS tools running in our AWS environment with Snowflake and Matillion.
Our data engineer created a database replica from one account to another account inside Snowflake. When he executed the command to refresh that replication, we got an error. Below is the error we are receiving when running the refresh replica command, and it's only on the CSTOOLS data.

We're curious if there's a step missing in the staging process for CSTOOLS to eliminate this issue. Snowflake can't yet figure out why.

ERROR:
Dangling references in the snapshot. Correct the errors before refreshing again. The following references are missing (referred entity <- [referring entities]):
CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_GROUP.SYS_CONSTRAINT_604aa18d-65d8-4791-8390-b3c6515027ae' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_XREF_PRINCIPAL.SYS_CONSTRAINT_d5c3d601-cabf-43c5-879a-52f86a493c3e']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_GROUP.SYS_CONSTRAINT_919927a3-85af-4f36-b106-d9991b0e6896' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_SHARING_ACCESS.SYS_CONSTRAINT_ab816789-0375-4412-96ea-90cd23449d66']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_GROUP.SYS_CONSTRAINT_f39a4ec0-f2c1-41f4-a1b9-1e6ef8089205' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_GROUP_PRIVILEGE.SYS_CONSTRAINT_ac3a9dde-8c7c-4e3b-abf9-f0d352a86f3b']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_METADATA_COLUMN.SYS_CONSTRAINT_00ce0e8c-ac15-41bd-becd-04155e532486' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_DEPENDENT_OBJECT.SYS_CONSTRAINT_b71e6a26-6f75-4514-a69c-31fd94f025b2']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_METADATA_COLUMN.SYS_CONSTRAINT_36441f86-a058-4947-9d67-0b4948f92500' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_COLUMN_SYNONYM.SYS_CONSTRAINT_50c25eee-975d-46b0-8efe-f4aefc4202de']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_METADATA_OBJECT.SYS_CONSTRAINT_44566309-1a4d-4ad3-8232-e68d4d878218' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_METADATA_COLUMN.SYS_CONSTRAINT_d100d0cc-b596-40ec-88ca-a63018a051e3']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_METADATA_OBJECT.SYS_CONSTRAINT_c4af4ebb-3101-4dde-b01c-97c173fa3056' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_TAGGED_OBJECT.SYS_CONSTRAINT_ace4bca6-553a-4920-ab7a-707d6702ad44']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_METADATA_OBJECT.SYS_CONSTRAINT_c7361b0d-fe76-40d8-99fb-d5fb3c98163c' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_SHARING_ACCESS.SYS_CONSTRAINT_863fe8d0-5451-409b-ae88-30ba3319bf9d']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_TAG.SYS_CONSTRAINT_1541993a-b863-4860-be99-d4e560ebfd7a' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_TAGGED_OBJECT.SYS_CONSTRAINT_cc6cf78f-fa23-4491-9352-36a7e9e4eb61']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_USER.SYS_CONSTRAINT_00751c83-b0f0-4b58-96af-89b8a6226401' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_DEPENDENT_OBJECT.SYS_CONSTRAINT_044c2b7e-1547-40f3-a9bb-9b74c8a5a330']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_USER.SYS_CONSTRAINT_494c5426-6a96-4b75-8cfd-12332bc48284' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_XREF_PRINCIPAL.SYS_CONSTRAINT_57ce4c54-8ff2-4ea3-8d07-9d09963c604d']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_USER.SYS_CONSTRAINT_79696ae5-b082-4b67-bb52-f0287dece5aa' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_METADATA_OBJECT.SYS_CONSTRAINT_e5ed8f7d-9eff-4d49-95b5-a8a5f9c31464']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_USER.SYS_CONSTRAINT_935aac4b-0881-4a1f-8573-ede0b1664b37' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_TAG.SYS_CONSTRAINT_e037ea00-b277-40fb-b3f3-7022e7d5339c']. CONSTRAINT '.NP_STAGE_DB.CSTOOLS.TS_USER.SYS_CONSTRAINT_c3566ffe-34ab-4959-9202-4766b1728b5d' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_SHARING_ACCESS.SYS_CONSTRAINT_6e303175-b708-41be-b533-fa466627dde8']. TABLE '.NP_STAGE_DB.CSTOOLS.TS_GROUP' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_SHARING_ACCESS.SYS_CONSTRAINT_ab816789-0375-4412-96ea-90cd23449d66', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_GROUP_PRIVILEGE.SYS_CONSTRAINT_ac3a9dde-8c7c-4e3b-abf9-f0d352a86f3b', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_XREF_PRINCIPAL.SYS_CONSTRAINT_d5c3d601-cabf-43c5-879a-52f86a493c3e']. TABLE '.NP_STAGE_DB.CSTOOLS.TS_METADATA_COLUMN' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_COLUMN_SYNONYM.SYS_CONSTRAINT_50c25eee-975d-46b0-8efe-f4aefc4202de', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_DEPENDENT_OBJECT.SYS_CONSTRAINT_b71e6a26-6f75-4514-a69c-31fd94f025b2']. TABLE '.NP_STAGE_DB.CSTOOLS.TS_METADATA_OBJECT' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_SHARING_ACCESS.SYS_CONSTRAINT_863fe8d0-5451-409b-ae88-30ba3319bf9d', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_TAGGED_OBJECT.SYS_CONSTRAINT_ace4bca6-553a-4920-ab7a-707d6702ad44', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_METADATA_COLUMN.SYS_CONSTRAINT_d100d0cc-b596-40ec-88ca-a63018a051e3']. TABLE '.NP_STAGE_DB.CSTOOLS.TS_TAG' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_TAGGED_OBJECT.SYS_CONSTRAINT_cc6cf78f-fa23-4491-9352-36a7e9e4eb61']. TABLE '.NP_STAGE_DB.CSTOOLS.TS_USER' <- [CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_METADATA_OBJECT.SYS_CONSTRAINT_e5ed8f7d-9eff-4d49-95b5-a8a5f9c31464', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_SHARING_ACCESS.SYS_CONSTRAINT_6e303175-b708-41be-b533-fa466627dde8', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_DEPENDENT_OBJECT.SYS_CONSTRAINT_044c2b7e-1547-40f3-a9bb-9b74c8a5a330', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_TAG.SYS_CONSTRAINT_e037ea00-b277-40fb-b3f3-7022e7d5339c', CONSTRAINT '.PROD_STAGE_DB.CSTOOLS.TS_XREF_PRINCIPAL.SYS_CONSTRAINT_57ce4c54-8ff2-4ea3-8d07-9d09963c604d']
has context menu

From Snowflake support documents: This doesn't appear to help

Cause A table column can use a sequence to generate default values. Snowflake manages the column-sequence reference by sequence ID.
When the sequence is deleted, via DROP SEQUENCE or via CREATE OR REPLACE SEQUENCE, inserts into the table will fail because the column is referring to a deleted sequence.
Replication of a primary database that contains such a table fails with the error mentioned above in order to prevent the issue from extending to the secondary database. The user must assign an active sequence to the table column to fix this issue as described below.

[boonhapus]

@BikeRiderJim -- what version of CS Tools are you on?

The .SYS_CONSTRAINT stuff looks like it's from an older version to me. We had relationships explicitly called out in the schema DDL in the past and that caused a lot of issues once we started supporting more database dialects.

[BikeRiderJim]

Hi, thanks for the note; we're on version 1.3.4

[boonhapus]

That would definitely explain it @BikeRiderJim . The newer model is larger and more database agnostic as joins / relationships are only maintained within ThoughtSpot itself. You may consider creating a new Snowflake schema/database and try inserting the 1.5.10 version of the database schema in it to observe the differences.

This image shows the entire schema, however not all tables are included in the newer SpotApps. You can find examples of the Liveboards here and here.