We have a vendor application that is now out of our support. We need to extract the data from this source and move it to Snowflake via Fivetran (ETL Tool). Fivetran needs Change Tracking enabled on the source so that it can pick up only the changes since the last cycle. The issue we are facing is there are no PKs on the source.
That vendor database has a clustered indexes (not unique not designated as PK) on some of the tables and when I check they are unique. So I can make them PKs. But there are some tables that don't have a unique index nor a clustered key, forget about PK :-(. One option is for me to see all the indexes on the table if any are unique.
The other option I was thinking was to add a Identity column to all the tables and make that the PK. But if they wrote the SQL the following way then we have an issue.
declare @mytest table (col1 int identity, col2 varchar(10))
declare @mytest2 table (rowid int identity, name varchar(10))
insert into @mytest select * from @mytest2
I am lost. Any ideas or thoughts? I greatly appreciate your help and time.
- This topic was modified 10 months, 2 weeks ago by cbarus.