Okay, so now that you've got a hint at BigQuery architecture behind the scenes, let's cover a little bit more on the Schema discussion. Where we talked about possibly the fact that joins are not a necessity and potentially we can actually store parent, child related data, almost like two tables in one through the concept of Nested Fields. And this is where your SQL knowledge is going to peak with the very, very advanced concepts that we're going to have here. So let's dive right in. Okay, this is the last part of this, my favorite lecture to go over. And put your seat belts on. Let's just jump right into it. Okay, so here we go. BigQuery Architecture introduces the concept of repeated Fields. So if you haven't seen repeated fields before, it's kind of a magical concept. So where we had normalized data before, all of the way on the left, we have two different tables, right? So you have people and then the cities that they've lived in, again, keeping kind of one fact in one place and having a common key that link them. That's traditional relationship model of databases. And that's, again, you're sacrificing performance because you necessitate the use of joins. But it's better for what we like to call better schemas for those relational databases. In the middle, you have the opposite. If you want to optimize for reads but potentially just have redundancy in the amount of data that you're storing, that's the denormalized view. And we call this kind of like the mother of all tables, right? And then the third, the new one that you probably haven't seen before, is you see the name, the age, the gender, the cities lived, and something strange that says repeated. And then this is the first time you might have seen a schema actually as a tab in it. Where cities and years lived seems to be like directly underneath where that cities lived is. And that's because that is a nested child field within that schema. So that concept of repeating these rows is exactly what we're going to spend the next pieces of these slides going over. Okay, review, requires us expensive joins. Now here's the real crux. So BigQuery can actually use the nested schemas to get at the high scale, right? Imagine if you had all your data in one table and you didn't have to do a join, it's much, much faster to refer to it that way. So this might look like I have purposely obfuscated some rows with that grey box. But that's actually not the case, that is actually what your query results will look like inside of BigQuery. So for this company named New York Association Inc, those three expenses lobbying, legal, and insurance are actually child nested records underneath that singular record. See where that vertical blue bar is? All of that is one row. So I'm only showing you two rows here. Their first row is New York Association Inc, and the second row is ACNE Co, It's not four individual rows. That's because you have this collapsible nesting of these child rows into that first one. We'll walk through a few more examples and see how that actually looks like inside of BigQuery. But just conceptually, work that around in your head. And lets talk about some of the performance benefits. So, again, you're not joining across two different tables because you almost have like an implicit built-in join because of the way this is actually set up. And the great news about this is that gray space right there, if you're doing something like a select distinct under company names. You actually not iterating through four rows. Again, here you're iterating just through those two. So it's kind of like having your cake and eating it, too. You can get all the performance benefits of just counting quickly the parent records. Think of orders table with nested order details inside of it. If you want to get the kind of orders really quickly, you can get that benefit. Or if you want to expand an individual order and look at some of the items that are in there. So it's 1,000 items, right? You can unpack or do what's called unnest those rows and access those as well.
