After copying all our SQL, NoSQL data into Snowflake, is there a way to detect "relationships" across the hundreds of Tables, Jsons, other data?

user_15096826 0
Viewed 98

Apologies for this somewhat bizarre question...

Data from multiple transactional, operational, and event sources have successfully been ingested into Snowflake. However, most of our analytics and data science use cases involve:

  • denormalizing data
  • building models across multiple structured and semi-structured sources i.e. understand how data "joins" within and across sources, esp where there is no consistent naming convention naming convention across sources

Is there a way in Snowflake (either directly or via other tools) to automatically detect relationships across the data without requiring us to write multiple joins? Do any of the other cloud data warehouses offer this (directly or via 3rd party add-ons)?

snowflake-cloud-data-platform data-warehouse data-modeling sql
1 Answers

This is a great question.

Snowflake does maintain metadata about tables, columns, etc - but nothing that infers a relationship. That said, you could use the metadata to see how many overlaps exist between two objects. For example:

-- Find all tables that share n column names
select c1.table_catalog || '.' || c1.table_schema || '.' || c1.table_name object1, 
       c2.table_catalog || '.' || c2.table_schema || '.' || c2.table_name object2,
       count(1) overlapping_column_names
from snowflake.account_usage.columns c1,
     snowflake.account_usage.columns c2
where upper(c1.column_name) = upper(c2.column_name)
group by 1, 2
order by 3 desc;

For semi-structured, it's a bit more complex but there are functions in Snowflake to extract the different keys in the variant data. The SQL below will identify the unique keys observed in a 10% sample of the variant data:

with c1 as (select distinct object_keys(json_data) keys 
             from customer_interactions sample(10)
           )
select distinct 'TABLE1' table_name, upper(value::string) keyname
from c1, lateral flatten(c1.keys)
;

You could compare this to other variant data sets to see how many keys overlap between the two.

user_152794910
Related