Union two or more tables together automatically, AgileData Engineering Pattern #2 - Episode #68

Welcome to the Agile Data Podcast, where we talk about the merging of agile product and data patterns in a simply magical way. Welcome to the Agile Data Podcast. I'm Shane Gibson.

And I'm Nigel Vining.

Hey Nigel, we're on to our second agile data engineering pattern. So today we're going to talk about unioning two or more tables together automagically or automatically. Take me away with what it is and why I cared.

Cool, thanks, Jayne. This one was topical this week because Snowflake did an announcement around basically this very feature, unioning tables together and taking care of the complexity under the covers, which I thought was funny because it's something that crossed our paths going back about 4 years now. We needed the ability to union two tables together or more and have the complexity of this taken care of under the covers. Now the complexity is that when you union two tables together, you need to take into account the column names and the data types to basically line all the data up correctly. And usually this is handled in the SQL. But if you have an end user like Shane, working out that SQL's a step too far. It's quite a mission sometimes. Considering that some of the tables may have 100 columns in them and have some different data types. So we came up with this pattern called basically union tables, and it's a rule. So it lets Shane pick a driving table and then effectively pick another table and say union this, pick another table if he wants and say union this. Under the covers, we do a, basically a big dictionary lookup of the data types and the columns in those tables. And then we work out what the safe SQL is to union them together. Basically make sure all the columns match up and make sure the data types match up. And then we produce the SQL and it runs and it does all the normal stuff. It loads our downstream consumption tiles. So that's it in a nutshell.

And I was just looking at this, we actually built it 14th of June, 2022, we released it. So the interesting thing for me is we built that feature way back then because we needed it. Comes back to this, one of the overarching patterns of the data Ops platform is build it when you need it. When something is too hard or needs to be automated, then we build it, we use it. If we don't need it, we don't build it until we need it. So for me, what this allowed me to do was grab multiple tables, tiles in our language, and just slam them together. And then I could come up with one view automatically off that and just use it. And the reason we needed to do that was we were running data clean rooms. So we'd have data coming in from multiple publishers, each one going into their own private tenancy, and then once we cleaned all the data and hidden the stuff that we needed to hide from our customer, we then needed to slam all those tables together. So we'd bring in 2, 5, 10 tables, one from each publisher, and I needed to combine them to be a single table. And I didn't want to write a whole lot of really horrible SQL to do that. So this pattern effectively allowed me to say, pick this table and union it. And then all the complexity is done under the covers.

Yeah, exactly right. In the end, it turned out to be quite a versatile pattern for that scenario because yes, we have data arriving from different rooms, as you said, and then effectively you just want to put it all together because the end customer just wants one view across all that data. So they've already got a consistent format, so Basically, as long as all the data comes up from the clean rooms roughly similar, the union pattern takes care of it and safely puts them together. And incrementally loads that table every day because the second part of the pattern under the covers is it's keeping track of the, effectively, the load watermarks for each of those tables in the consume. And when new data turns up, basically a big append pattern but done in a safe way where we check we're never inserting duplicates. Yeah, it just works. It's, it's actually quite a magical one.

I was just reading our release notes. We actually iterated it to do that. So the first version of that pattern that we did, we actually created a view. Data was stored in the original tables and we created a view across the top. As we got to a large volume of data where BigQuery still ran it really fast, but the cost went up. Then we moved to physicalization, right? We changed it that it actually creates a table at the end and you incrementally load that. So again, we iterated the pattern as we found something that didn't quite work off version 1.

Yeah, yeah, that's exactly right. The cost went up and so we moved the engineering a little bit and the cost came way down now. And now that's quite a cheap pet to run 'cause we only incrementally add a few partitions into that table. Each day, which is really effective.

And the second thing we did is, as we changed some of our other patterns, we had to iterate that union pattern. So for example, when we started using shared tiles, so instead of data moving across tenancies, we shared data between tenancies in a safe way. You had to iterate that pattern. And then the other one was we had an automated field naming convention for our consume tiles, and we decided that actually the readability of those columns, those field names was not best. So we flipped it and actually changed the way we dynamically generated those field names. So again, when we did that, you had to add another set of logic to the union rule so that it could actually pick up both field formatting.

Yes, so there are two things that went on, as you said. The first one was we were unioning in tables from other projects, so we needed to extend the metadata lookup wider into the project that that table was coming from. So we had the most up-to-date list of column names and data types. So we would pull them straight off the source project. And then the second one is we changed some of our naming for the tiles going in. So which made our— the first version was a very simplistic match. It was basically if the end of the string matches, then it was the same column name. So we made that a lot more robust. We regexed that and we basically made sure that the name, the names could be a little bit inconsistent with mixed case. We could reverse the column names and it would still find the correct match, 'cause effectively we made it a whole lot smarter. And that's the version now we've been running probably about 18 months nearly, I believe. And that's still got legs, so we'll just keep running that as is.

And the other thing you mentioned is some of the input tables we use to union have over 100 columns because in our consume layer, we effectively use one big table as much as we can. So I'm literally grabbing 5 tables, each of 100 columns, saying to it, union it. It's working out where those columns are the same, rack and stacks that data. And then when the columns are different, it treats that as a separate column. Then I can choose which columns I don't want to bring through. So I may say, don't do these ones because effectively I don't want a table with 300 columns when I'm racking and stacking it. But I get, I get that choice when I use that rule. So that's really powerful for me. I don't have to actually go through and do a whole lot of column mapping. If the columns are the same, it picks it up, but I can actually then exclude the ones that I'm like, yeah, I don't need that this time. This one probably saves me hours actually, because I just don't need to care. It just does what I want. So that's the pattern that we use. When wouldn't you use this automated unioning pattern? I think for me, one of the ones would be, I know that the data that I'm about to union is similar. Because I know where it comes from. I know what we've done to it. I think if you grab two random tables that had no relationship of the data and try to union them without any human being involved to look at it. So just grab three random tables and union the hell out of it. It'll run. It'll create a consume table that's actually the union and all of it, but I'm not sure it'll meet the intent of what I would actually need to report off. So I think for me, that's probably an anti-pattern. That it just does it without a human being involved in that loop?

Yeah, it definitely, it allows you to do exactly that scenario. Put 3 things that aren't related together. It'll definitely union them. It'll run, it'll produce valid SQL. 90% of that produced table will be null because they don't match up.

I think that's the only one I can think of. The table versus view, that was performance. It's not really an anti-pattern, it's just something we need to do to that. And then the naming of the columns for helping with the automated matching, but they're just sub-patterns of the pattern itself. So that's the main one is the ability to use it to slam and rack and stack tables that actually aren't related at all and have no shared data. It'll run, it'll do the job, but probably not the job that you wanted to do. All right. I think we've got that one done. I hope everybody has a simply magical day. And that, Data Magicians, was another Agile Data Podcast episode. If you want to learn new patterns that you can apply to craft your own Agile Data way of working, head over to agiledata.io.