Hi Amazing folks,
I am new to this community and trying to understand the concepts of DV. I am not able to figure out a concept of why we should not join a SAT to SAT(BK to BK join) table and should always traverse through Sat1-Hub-link-Sat2. What can be the disadvantages of this approach? Also, when is it ok use a Sat to Sat join, if at all it is ok.
why not — you can do a sat-to-sat join — in fact you do that when you’re bringing sats together around a PIT
3 Likes
Hi @patrickcuba,
Thank you for the reply! Supporting question:
Then why is it recommended to traverse through multiple hops to connect 2 Sats available in diff hubs? Is it just a standard to maintain inner join consistency?
Beats me, I join things together whatever way works — only standards that apply are what these models tables look like. To help traverse these things in a repeatable manner we recommend PITs and Bridges but certainly thats not the only way to do it.
I have had models with hub-link-hub-link-hub, knowing that I did not need the business key from the second hub I omit that join from my query — or if you use RELY in Snowflake the SQL planner will omit that for you!
Sat-to-Sat joins are fine when those sats are connected to the same hub. Of course you need to account for the fact that those sats contain history so if you want the data for a specific point in time, a PIT will come in handy. If you only want the current data I suggest to create a view on top of every satellite with a window function to determine which records are active. You can easily join those sats (views) together on their hashkey (or natural key if you don’t use hashes) without the need of joining to the Hub.
You mention Sat1-Hub-link-Sat2 , I’m curious what Sat2 is? Is it a Sat on that link? If it is, you can’t join it directly on Sat1… they don’t share a common hashkey. That common hashkey is in the link so you would definitely need to traverse through that link.
Or if your example is what I think it should be, namely Sat1-Hub1-link-Hub2-Sat2 , then you can indeed skip the Hubs. You’d only join to the Hubs to get the business key, like what Patrick mentioned in his post.
2 Likes
Hi @FrenkLoonen and @patrickcuba ,
Thank you for the reply. Makes sense! Yes the example I was refeering to was Sat1-hub1-link-hub2-Sat2, thank you for raising my typo. Also, thank you for mentioning the point on creating views on top of Sat tables. We will definitely implement it.
1 Like