Replicating Foreign Keys - Copying the Source Data Model
Hi Qlick community! I'm interested in applying (but not enforcing) the foreign keys from my source data model as part of the DDL on a given target, assuming FK's are supported. Since QDI only replicates primary keys, I'm looking at building/buying some external tools to identify and apply the foreign keys, has anyone else run into this challenge? If so, what methods have you used to create the foreign keys on your targets?
Hi @Matthew_Christian Can you elaborate? Do you mean to update table definitions that are being replicated with PK-FK relations? Also, I am not sure what we mean by applying vs enforcing constraints? my understanding is that if you define a constraint on a table, it's automatically enforced.
Hi JR, thanks for responding. It might help to explain the use case. I'm using a data cataloging tool that can reference foreign key definitions to define relationships between data. The catalog doesn't care if the referential integrity is maintained but it really does benefit from having the constraint defined. A second use case is for data discovery. We have a team of data scientists combing through Snowflake and the data lake and having the foreign keys available helps with linking data across tables.
We're using Attunity Replicate for our DDL on the targets (Postgres and Snowflake), and it can create the primary keys but it doesn't have any mechanism for creating foreign keys; the DDL doesn't pick these up. I'm looking at some workaround to identify the foreign keys and create them after Attunity runs its DDL, but it'd be much more convenient to have that as part of the product.
Some data platforms, such as Snowflake, will define a foreign key relationship but will not actually enforce it. PostgreSQL can also be configured to not enforce foreign keys by disabling triggers. This fits my use case perfectly, where I'm replicating data for a data lake use case and I only really need the foreign keys for data discovery.
Hi @Matthew_Christian Thank You for the detailed explanation. My understanding is that, replicate generated DDL do not capture FK relationships between tables and if we want this to be part of the product, you will need to submit it as an Idea via the ideation channel of the community. From a workaround standpoint, you can create the target tables first and then in FL settings, set the condition 'if target table already exists' to 'Do Nothing' OR ' Truncate and Load' based on your use case.