The patterns of Focal Data Modeling and Identity Resolution with Patrik Lager

Welcome to the Agile Data Podcast, where we talk about the merging of agile Product and Data Patterns in a Simply Magical Way. My first book, An Agile Data Guide to Information Product Canvas, is published on Amazon. If you hear your stakeholders say, "Hey, that's not what I wanted," and then you hear your team reply with, but that's what you asked for, then you probably have a problem capturing data requirements. The Information Product Canvas is a pattern template that is a quick and simple way to solve that problem. Head over to agiledataguides.com and click on the green book link to buy a copy now. Welcome to the Agile Data Podcast. I'm Shane Gibson.

This is Patrick Loyer.

Hey Patrick, thank you for coming on the show. Looking forward to this one today. Today we're gonna be talking about Focal, data modeling, and identity resolution. But before we rip into that, why don't you give the audience a bit of background about yourself?

Yeah, I've been within the data area for the last 25 years, working with data warehousing mostly, but more or less, yes. Integration is a lot of work. I worked in SOA projects and other kinds of integration areas as well. I've been around and seen a lot of things on the way. I also have been one of the main developers of something that is called the Focal Framework and the Focal Modeling Method. So I've been working with that since 2004, more or less. The Focal came along in 1998 by a man who invented it, more or less. But then he and I plus a lot of other people worked a lot with developing it and moving it forward, so to speak. So that's me.

Focal's been around for a long time, but it's not well known. In some European countries, it is relatively well known, but in the rest of the world, it's not. Why do you think that is?

The Focal framework has an Achilles heel, if you want to call it. It's an extremely abstract way of using data. But what it makes happen is it can create an extremely non-destructive architecture that is very easy to change and add data and change data without affecting anything else in your architecture, which makes it very agile and very efficient when building things. But as I said, it's an engineering dream, so to speak, with a lot of smart people working with it. So it's not easy to take in, to learn. and that has been its Achilles heel, so to speak.

And when you talk about Focal being a framework, can you just describe what you mean by that? Because a lot of times we see data modeling patterns that are described as frameworks or methodologies, and really what they are is a good way of structuring data and not really much around it. Or sometimes we see methodologies that are agnostic around the data modeling patterns that you can use. Whereas from what I understand, Focal kind of binds those two things together closely, the framework and the modeling patterns are coexistent. Is that right?

Yeah. The, the Focal framework, as it all started with the Focal modeling technique, but then it became a Focal framework. It was more for documenting really what we ran into in all of these projects. The work was the documentation always got out of line from what you implemented. It doesn't connect it after a while. So the idea was that we would capture all the metadata, models, mappings, rules, and stuff like that into a framework that then would generate the code for us and generate the things and hold lineage and everything in it. So that became the framework, so to speak. You could call it an automation engine, but it's much larger because it also works as a vocabulary and description of models, holds all kinds of lineage business lineage, technical lineage, operational lineage. It's all over the place, so to speak. The metadata model is pretty huge. It's like 80 tables containing all kinds of metadata around what you're doing. So it, that's the framework, so to speak.

So if I play that back, it's a technical framework on how you can implement and automate the Focal patterns. So it doesn't take care of people and process. It doesn't talk about standups or the way teams are structured. So it's more in that how you build a technical framework to implement and execute Focal without having to handcraft code on a regular basis to make it work. Is that right?

Yeah, exactly. The thing was that we, at one point, we came to the conclusion that the value of data warehousing, or the value is the understanding of data. The value was not in the code. The code was a secondary value in the architecture. So, we wanted to make the focus on understanding what you're doing, understanding data, understanding the business. So that was the focus, and then could use that knowledge to generate the code instead of writing it. And then also always be in sync. The documentation would always be in sync with the code because the documentation creates the code.

And there's been a few of us that have been working on this idea of metadata-driven data tooling or active metadata or config-driven. Now the new buzzword is context-driven. And I always saw three patterns in the world. There were the, let me just write a blob of code. The blob of code executes, it's, it's on its own. It has no relationship with anything else. It's pretty much going to do all the heavy dating work from the left-hand side, the raw data history, all the way through to consume. And then the next one is tooling that allows you to write blobs of code, but then produces the documentation after the fact. Escape was, probably old enough to remember that. That was one where a lot of the value was when you generated your code, it created the documentation for you. And if I look at people using dbt, a lot of it was around that documentation, that lineage that you got out of it. And then there's a smaller group like us where we said, create the documentation first and then let the documentation effectively generate the code. So we define the metadata, the context, and then that code will get hydrated based on that core. And we end up protecting or caring about that metadata, that config, because the code becomes disposable. The metadata is our pet, the code is cattle in DevOps language. And we see tooling come out of that metadata-driven pattern over the last 30 years, but it never really gets traction. Why do you think that is? Why do— and we still keep doing it because we know it's much faster for us. But why do you think that this middle ground where people can write code and then document it after the fact always seems to be more popular than defining the metadata, the logic, and letting it hydrate the code for us?

The love of the code, that's the thing. Everyone who is a developer loves their code. They are proud of it. They want to do it. They want to show how good they are at it. And then they come a tooling, a framework that you do not need to be good at writing code. You need to be good to understanding the business and describe the business and describe models and create things like that. And it's hard to get organizations to accept that because you get often a lot of fightback from the development side that says, you can't write as good code as me, and I'm better at that. And then always it comes, one of the biggest things is often that they say, but can it do this? And you say, no, it can't do that. Okay, then it's useless. Even if you can do 90% of the optimization, because you can't do the 10%, it's useless. So they drive that line up again to their own management side. And then it becomes hard to come into companies doing that. They probably feel threatened as well. The interesting point is now with AI, there is no way out anymore. This is what's going to happen. Your coding skills are not that valuable anymore. Your ability to design and understand systems, how a good design and a good architecture becomes much more important than being very skilled at programming.

Also, writing code by hand takes time.

Yeah.

It's busy work. So you can be busy writing that code and you're not having to engage with the stakeholder side to understand the value, deliver that value incrementally. It's not unusual for a data team to be able to do busy work for a month and actually deliver nothing to a stakeholder. We somehow have this expectation and I'm with you. I think the LLMs and GenAI are going to reduce that expectation timeframe that a month doing busy work is, is not going to happen. 5-code an LLM or use a metadata config-driven framework, pick your choice, but I'd rather leverage an architecture that somebody else has created for me that I know works than let the LLM guess at an architecture that hasn't been proven before. Okay. So Framework is the technical implementation of Focal. Focal started out in 1998, I think you said, as a data modeling paradigm. Do you want to talk me through the Focal modeling, just for people that are more familiar with other modeling techniques and how it's the same and how it's different?

Yeah, the Focal has been from the first point, really metadata-driven, but it came up with the thought of 3 parts within the model where you control the key, and that was its own structure. And then you had a table that represented the description of data. And then you have their own structures for relationships. So you never had foreign keys between tables. And this was not my idea. This was the one who invented it, that this is how it should work. Because He had seen that complexity grows and that changes always happen. And when changes affect the model and when it affects the code, it becomes much slower to change. And a data warehouse after 5 or 10 years becomes very complex. So he was trying to find a way to lessen that effect. And the thing is, one of the things that is making it abstract from a point of view is that The tables that are holding the descriptions are what we call typed tables. You don't have normal business attributes columns in the tables. They have generic columns like start timestamp, end timestamp, value string, then numeric string, unit of measure. And then you have a key or an identifier of the, information that lies on the row. So it's what we call a type key or an atomic context key. But what it really says is it's like a name-value pair, if you know what that is. But it's— the inventor, he said it's modeled name-value pair because it's not one value and one key. It is one key and you can have up to 5 different attributes if those attributes belong together. So this is one of the problems with Focal. Everyone you talk about it is it has a lot of underlying philosophical ideas and theories that doesn't match how we do normal data models. So it's always an abstract step to take in your mind. Why do you do that and how does it work? But if you want to simplify it very much, you would say that the tables that hold the description are name-value pairs, you could say. So you have an ID or a key, or what you normally name, and that says what is in the values string. And then you can add new attributes. They are just new names and they hold their own values. So you never change the table. You just add new types of data into that. So that is one of the biggest differences from normal data modeling, really. So it's, it's built for agility, more or less change. Do non-destructive change. You can add attributes, remove attributes, and you never change the code. You don't change the tables. You just do the change, so to speak.

So let me play that one back. So you talked about first object is a key. Second object is a table, which describes the values effectively in the key. And there's some unique technical architecture around that, or modeling architecture compared to most patterns. And then the third object is relationships. Keys that have a relationship at a point in time. My understanding is that is a form of ensemble modeling. You are decomposing the key from the descriptors of the key and you're decomposing the relationships and holding those separately. So that's true, right? Focal is a form of ensemble modeling?

Yes, that it is.

Excellent. And then there's two other ones that I know of. There's Data Vault as one form and anchor modeling as another. So what I might do is just, actually, we've never had anybody on to talk about anchor. I need to do that, but let me try and just play back what you told me around Focal and how I think it's different to Data Vault and anchor modeling. And I'll get the anchor one wrong, cuz like I said, I haven't had anybody on to explain it properly. So the first thing is both anchor and Data Vault hold the key in a separate object like Focal does. So, so that's the same, correct?

Yes.

Okay. And then in Data Vault, we'll hold one or many satellites that hold the descriptors or the attributes against those keys. But we will hold a table that has, or a view that has many columns. So if I have person's name and person's age or date of birth, I will hold those as columns in the satellite. In Anchor, my understanding is I'd hold each of those attributes as a separate table.

Yes, that's correct.

And what you are saying is in Focal, what we do is we use a named peer key. So we're making those columns rows. However, there's an extra little bit of complexity or value in there that you can hold multiple values against one pair. So I could decide to have name and date of birth in what sounds like a JSON string. Is it a way of typing an object and having a subset of values against it? Is that right?

No, the underlying theory, you can do that, but that's not why it was designed for. It is designed for what we call an atomic context. And the atomic context is— so the key, what we hold is we call it the atomic context key, is a group of attributes, one-to-many, but 5 more or less is what we have in the tables. And it is supposed to answer one atomic question. So if you put a shoe number and first name, you don't have an atomic question for that, right? Because you want to ask, what's your first name? What's your shoe number? That's two different questions. So they would be two different atomic contexts. But on the other hand, if I would say, what is the loan amount I have on my loan, that my sign, my original loan amount, I might say it's €1,000, because you can't answer the question of a monetary value without its currency. So you need to have the value and the currency on the same row. Therefore, we have, as an example, we have a unit of measure that we call unit of measure column. Then you say, then, so you use the val_num column and you say that's 1,000, and then in the unit measure it says euro. So then you answer the atomic question. And as another example, more complex one, is like a balance, that you have a balance. And to understand the balance, which is a point in time data, is only true at a certain point in time, you need the balance date. When was this balance value true? And what was the unit of measure of the balance. So all of a sudden you have 3 attributes within the same atomic context to be able to answer the atomic query. So this is more like from a human perspective. Think, how does human ask questions? You can ask, who are you? And you will get a lot of things. But if you ask an atomic question, the idea is that all the data should be on the same row to answer that in a physical table.

So let's go back to that example then. So if I'm holding balance on my bank account, then I need to know what the point in time, the date, time of that balance is. And I probably also need to know what currency it is. So what you're saying is you hold those attributes together because actually they need to be together to answer that business question or to provide the context of one of those numbers. And therefore, rather than holding it outside of the physical structure, in some form of physical data model where I know I've got to go join that with that, or query that row and grab these 3 columns. You're actually saying by bounding them together in a row, the answer's already there. You have to grab them all because we went to the effort of putting them there because that's the information that you need to actually answer a question around what is my balance.

Yeah. So you can almost see it like a normalization of data because if you would have a, If you had a satellite in Data Vault, you could have these 3 columns that we talked about, the date, the value, and the currency. And then you could have what kind of classification of balance is this and what is— so you just add it up. But as we do that, we break it out. So these, this answer one question and these columns answer these questions and break it out in rows then. And these are in, in, in Anchor, they go all the way. They do not care about how data from a human perspective is represented. They just break it out totally mechanically. One attribute is one table. So you have the value in one table and the currency in another table and the date in another table. And then they generate views that joins everything together. So it's 3 different approaches. Sometimes when I talk to Lars Rønnbeck, who is the inventor of Anchor, We talk about it. He says Anchor is in 6th normal form, and not from a truly formalized perspective, but he says it feels like Focal is 5th normal form, somewhere around there. And then we have Data Vault, which is more 3rd normal form. So you could see it like that. It's more normalizationist.

And it would be interesting when you start putting LLMs across it, because with Data Vault, we're gonna have to provide the context of which columns, which attributes in the satellite actually have value given the business question. In anchor, we're gonna have to say which tables, right, effectively need to be put together to answer that question. So that context is held outside the data. But in focal, what I think I'm hearing is actually the data has context as well. So if I give it that name, peer key, and it can see that there is a balance, a currency, and a datetime, it's going to be pretty good at saying those three things are important to ask the business question of what was my balance in New Zealand dollars? Okay. I can see doing it. One of the things that people often say against any of the ensemble modeling is, oh, storage is too expensive. It's too hard. And so I can see that, let's take currency. We're going to store currency again and again, right? Where it's bound to the context of one of the other attributes. In my view, storage is cheap now. And actually with columnar databases, actually querying across tables is more expensive than querying within a table. But what have you found that's one of the pushbacks with Focal is data storage and duplication is bad because it was 30 years ago when we had mainframes?

No, never ran into the issue of, of storage really. And more when we move really far back, early 2000, when we had the normal relational databases. The easy thing with the Focal was that it was so easy to set up good indexes because the tables look exactly the same all the time. And you could say the logic for true delta always looks the same and always how you query it looks the same. It's always the same. So it was very easy to set up good indexes. So even then the tables became very deep. It still worked very well because the indexes were so well-defined and the pattern was reusable all the time. So, it just worked, so to speak. What I've seen on the other side is that when we moved up into the cloud area where indexes are not so important anymore, or important, but they don't exist, so to speak, true indexes, but they are columnar, right? Often Snowflake, and if you look at BigQuery, so on and so forth, it's really efficient because it doesn't need to store everything, right? It just stores one unique value. It is a really good— what do you call it when you press data together?

Compression.

Yeah. So, Focal works very well with those formulas for doing that. So, it becomes very small in storage instead because it doesn't need to store each and every value in the engine, so to speak.

And then from what you've said, the Focal Framework and the modeling pattern has basically described the types that sit in those name-peer keys. So there's a dictionary or a shopping list of types that, that you need because they've been proven over the last, what is it, 20, 30 years. So you've probably gone through and figured out all the types that are needed pretty much in most examples. And you get those kind of free when you're using the methodology or the patterns.

Yeah, you always have to keep in mind that the Focal today is the data layer, so to speak, where the data resides, is very machine-driven. It's not for the human eye. It's been very specialized to be fully metadata-oriented. So there you can't really look at the table in the Focal data model and understand what kind of data is there. You need to add the metadata layer or knowledge layer upon it that is directly related then, but everything describes what is there, how it is stored, and everything is up in that layer. So that layer is the heart of the solution, so to speak. The physical layer is more just how do we make this reusable? How do we not care about what the human think, care about good engineering sort of principles. And that has been very effective. And now when AI comes along, it's all of a sudden, it's shit. The machine understands it without a problem. What a human thought was hard for a machine, it seems like the AI goes like this, okay, that's not hard.

Because it's pattern-based, right?

Yeah, everything is described. So it just reads the metadata layer and then he understands what's in the data layer. And then you can just ask him questions.

And there's no exceptions. Because it's metadata-driven. There's no, oh, Bob came and did that one and he cheated. And yeah, it's an exception that you've got to deal with.

No, so it's, yeah, it's fully pattern. So it's, as you say, there is no exceptions that he has to take into consideration.

So one of the negatives that you often hear around modeling patterns like Data Vault is consuming the data is hard. There's a lot more tables, a lot more joins, even though it's pattern-based, people that try and query it directly, have a little bit more work. And what we always say is your consume layer should be automatically generated. It's a pattern-based design pattern. So creating the consumption patterns can be automated and you can use One Big Table with dimensional modeling or whatever, anything that flavor that you prefer to query the data. But those patterns for querying it should be automated. I'm gonna assume that for Focal, it's the same, that you wouldn't expect a human with Tableau to go into the core Focal data model and try and figure out how to query it.

No, certainly not. No, it's the Focal Framework itself creates, automatically creates views more or less like Anchor does. So you as a human never reads the physical tables. You go through the views, which will pivot the data into, this is the customer view, this is a customer table. It looks like a normal table when you look at it with all the column names and stuff like that. Underneath it's a totally different thing, but for a human, you just go in and access the views and it looks like a normal table. So that's how we have abstracted the complexity. So you don't work against the physical, the machine does that. You just read the views and you don't have to care how complex or how strange or abstract it's underneath really. So that's more or less how we work with it. You don't go in and hand code a dimension from the physical tables, no.

And that's part of the framework. When you talk about your metadata repository having 80 tables, that's where you're gonna define these keys, these name-pair keys, the relationships, and then it's gonna generate the physical structure under the covers using the Focal Modeling Technique. And then it's gonna also generate the views that you need to make that data useful and usable when you query it. Yeah. And that's the value of the, framework over and above the modeling pattern, correct?

Yeah, I think I agree that most, both from Anchor and DataWorld and Focal, the ensemble models, it is harder to write queries against it. You can't really dodge it. That's how it is. But there is a reason for it. And that's what people forget. So you can't say it's just as easy as a dimension model. No, it isn't. But there is a reason why the ensemble models looks and works like they do, which is from an architectural perspective and longevity of the system and changeability and stuff like that. This layering ideas of have layers is that you have a layer which is specialized on a specific area and use the modeling technique that support what you need to do in that layer. Have another layer that makes it easier to do other things, but don't sub-optimize the layer by trying to do everything in one layer. You have to have sort of a system design mind to understand when and where to use things.

Yeah, that idea of a layered data architecture and use the modeling techniques that have the most value for the problem you're trying to solve and then automate them and standardize them so they're part of your architecture, not little things bolted on the end that people forget that for this one, we somehow used something that was slightly different. Last question around that though, the Focal framework itself, it's open source, correct?

At this point, there is a Focal CLI that we are about to release it. It's a lot of testing that goes around. The Focal framework itself is not in GitHub at this point because more or less it's just so hard to understand it. So we need to abstract it. Give you tools to use it, so to speak. So you don't have to be a Focal expert to use it because that just takes too much time, more or less.

So the other thing we were going to talk about is how Focal helps us with identity resolution. So this idea that, you know, one of the largest problems always is we see the same person in multiple different places and systems and we want to stitch them together or the same organization. And that is a big problem that most people ignore, right? They focus on the simple problems when they bring out new patterns, frameworks, methodologies. They do the nice customer orders product, simple ones. So let's deep dive into entity resolution and how Focal helps us with that.

Okay. Let's, uh, let me set some concepts first around it and why we're doing it just for the audience. So when I'm talking about entity resolution or key integrations, I also used to call it. Is that we have two things, two concepts. And that's one that you have, something is called an identity. And that is something that is stable, immutable key that represents an instance of data. And that key should be, it doesn't have to be, but it should be unique throughout the whole analytical system. And it's identifies a thing or an event uniquely over its whole lifecycle. So it's immutable. So that's an identity when I call it identity. The other one is called an identifier, which is a way of identifying a thing in an event. It has to be unique within its data space, might not identify the instance as its full lifecycle, but we should strive to find one. So these are two different concepts. The identity often is called surrogate keys, is normally represented as a surrogate key in your system. And the identifier is what people sometimes call business identifier or something like that. But it's good to understand that you should keep those two apart, that they are two different things and they are there for two handling things differently. The other thing is what are we trying to do and what do we not want to happen when we do the sort of integration? Key integration is that one thing we don't want to happen is key collision. The idea is that we have two different systems, both holds accounts and they have an account number series on them. And we send them into our account, Focal or Hub or whatever. And we use the account number as the identification because it's the business identifier. But for some reason, these numbers are overlapping. So we can start getting key collision. That means that two different accounts that are two different instances in the real world start writing their data on each other and changing data back and forth because they are not truly the same account. That's a key collision area that we want to avoid. The other thing is that we want to achieve key integration, and, and that is the thing that we want to, if they are the same thing in two different systems, we want them to integrate on the identifier or the key, so to speak. And this is the hard part when working with this is to understand how unique should I do something and make something and still make it integrate. So let's say you could have this account thing and you go, okay, but I set source system ID in front of it. And then The account numbers will be unique. Then you have two different customer systems and the same customer lives in both systems, but you just go, "Oh, but we set the same system ID in front of the customer ID and then it's unique." But then we all of a sudden don't get the key integration that we want to happen because we have made it too unique for how it works in the IT landscape. This work, as you said, this work with integration and entity resolution is is really hard work. It's not something that just comes along freely. You have to put time and effort to understand the IT landscape, the business processes, how does data move, where is the unique instances of things and where do they live, and how do they move in the IT landscape to be able to understand how should I build my identifier, because this is what we talk about. So We need to create an identifier. Some people talk about business identifiers. For me, that doesn't ring correctly because I talk about— I normally want to say integration identifier because for me, business identifier is something that the business can use to identify things, but it doesn't have to be unique for their work in their daily work. So you could have something that is true in one point in time that it's unique, but it can change and still the business uses it because it works from an operational perspective. But from an analytical system where we historize things and keep track over time how things changes, we need identifiers that stay true throughout the lifecycle as much as possible. So there is always that kind of work we need to do to understand how do we create the identifiers so they will truly integrate and not end up with a key collision. So it's hard work, more or less.

There was a lot in there, so let me unpack it and try and map it to some patterns that I understand, and I'm probably going to get them wrong, so I'll pause after each one to make sure that I've got it. So the idea of key collision, so let's use, let's use accounts that in system 1, We have an account which the account sequencing numbering is 1, 2, 3, 4. So it's a number. And then in system 2, we have a separate series where it's ABC. And based on that, if both of those are held true forever, we know there is no key collision. We can trust that as a unique account record and we'd never collide. But if system 1 is 1, 2, 3, 4, and system 2 is 1, 2, 3, 4, and they're not integrated anywhere else. We know that potentially we'll see that 1234 come through from each system, but for a different account, account for Bob versus account for Shane or account for savings versus an account for investment. And so our natural way of dealing with that is to put source identifiers at the front. Yeah, so system 1, 1234, system 2, 1234, and now we know we will never get a collision. So that's the first pattern that we typically do is that Correct?

Yes.

And that source and account combination, is that what you call an identifier?

Yeah, it could be, but it could be multiple. You could use multiple columns to create an identifier. So it's depending on your physical realization of how you want to do it. You could keep the columns apart, but in the Focal, we at least, we concatenate all the different columns we need to create a unique string of data.

Okay. Yeah. But that's the identifier because we can identify that was true at that time. It's a fact. We can see the data there. We didn't make it up. We didn't infer it. It's a fact. Okay. And then we know that if we want to say this account in System 1 is the same as this account in System 2, and now we don't have an identifier that is exactly the same identifier, we have to infer that they are the same. And it's like a surrogate key in my head. We're trying to find this unique identity. I'm just trying to get my language right. The unique identity to say that actually is the one thing that looks differently elsewhere and it's the same. Is that right? So we're creating this unique identity ID that then everything's bound to if we can match it. Is that right?

Yeah. I mean, it's the same for DataWorld people that you have the business key and then you have the SID, right? It's the same. Principle, you have an identification identifier, which is related to a surrogate key. So that's just to keep them apart, so to speak. That's what they have two different sort of workings in the system.

And so obviously creating that identity ID, because that becomes this golden ID, it's this immutable thing that we bind everything to when we find something that's related in the future, and we can see changes over time. So if my account number goes from 1234 to 56789 a year later, and we bind that back to the identity ID, you still know it was my account. Its identifier changed.

Yeah.

Sometimes happens, especially when we're using email, when we use email addresses, the identifier or name, we know that those identifier IDs will change over time and they cause us a major problem. So what we're trying to do is find that, that golden identity ID. For the thing, the event, the person, people, place, those kinds of concepts, who does what, all those kinds of concepts that we want to manage. Is that right?

Yeah. Yeah. Okay.

Lots of people talk about that and then they leave it to you to figure out how to do it. So take me through the next bit.

Yeah. So since we're talking about patterns, let's talk about how, first, how Data Vault does it and Anchor does it, just to make it See, and then we go down to how Focal does it. So in the data world, we already touched on it, right?

You—

we have the hub, and in the hub we have a business key and we have a SID and a surrogate key. Now I know a lot of people have discontinued the use of surrogate keys in, in the data world area because you're using hash keys, and there is a reason for that. And I agree that data world should do that because they do not have the ability to relate multiple identifiers to one identity because it's hardwired in the hub. So if it is a one-to-one relationship between the one identifier and its key, there is no really architectural functionality for the surrogate key. You can just as well just hash the identifier and then you have a key that always works through. Instead, when the reality hits us in the face, we all of a sudden understand that, oh, this one can be identified multiple different ways. So, you could have a customer ID and you have a social security number, and both are used to be identifying you in different systems. And in DataWorld, if you use the customer ID and it has a surrogate key, or you just hash it, that becomes one instance of the customer. You do the social security number, it becomes another instance of the customer, or then they use a same-as link that says this ID is the same as this ID from an instance perspective. So that's how they do it in their solution. In Anchor, they point at an attribute table. So you have in the Anchor, you only have the surrogate key, and then they point at the table and one of the attribute tables, and you'll say, this one is also used as an identifier. So their code uses that for lookups. Okay, I have my identifier, I check there, and then I can Pick up from the anchor, I can pick up the surrogate key, I get the surrogate key. And then you have the pickup and they have the ability that they can choose, okay, we want another attribute as well as part, we could be able to identify with social security number as well. And then they can point in the table that holds the social security number. The issue is that they have to generate the code or change the code because it has no generic solution around it. So, the code is changing. It's a destructive change in the architecture when you need to add a new way of identifying things. Then you get a destructive change in your code. So, you need to change your code. In Focal, we have what we call an IDF for table, which is to identify your table, which sole purpose is to integrate, to be used to create integration and hold the relationship between the identifier and the key that we have created. And how this works is no magic. In the Focal framework as of today, there is no fuzzy logic solution or things like that. Its only purpose and only functionality is that you can connect multiple identifiers to one key if you have them in the same table, more or less. So let's say you have a table where you have your customer ID in the table and you also have the Social Security number in the table, probably from the source. You can then point out and say the customer ID identifies a customer and the Social Security number identifies a customer. How do we know it's true? They are on the same row in the table. So we know by how they're built that they belong together. There's no magic really. It's just, we can just point out multiple things and those different columns that we use to identify becomes their own identifier string. Now in the identifier table, there is, I'm not going to talk about the whole because there's a lot of technical things in it as well. But from a conceptual point of view, you have one column that is called identifier and you have one column that is called key. And the thing is that each row represents the relationship between an identifier and a key. Now, in the code, if you point out Social Security number and you point out the customer ID as identifying, the identifier code will then put both of those into the same SQL, and then he will check for each of those He will do multiple lookups, one for each. He will look up with the string for the customer ID, will check against the identifier table. Do I have a key for this? Yes or no? Okay. And then he checks for the Social Security number. Do I have a key or not in my identifier table? If there is no key, new key is created. Both of these becomes pivot to its own rows. So The Social Security number becomes related to customer key 1, the identity, the customer ID key 1, and the Social Security number will also be aligned to the customer key 1, so to speak, the surrogate key. So now they are aligned. One of the interesting points is, let's say we also have something else. I don't come up with a good example, but some kind of other identification of a customer. Maybe in the same table, but it could also be in another table. So in another table, in another system, we have the, let's say, the Social Security number, and they have a local identification of the customer as well in another column. And they want, they need that because they have other systems that works with that identifier in the IT landscape and not the Social Security number. So the thing is that when they map that system and say, It's a Social Security number and this local ID that identifies a customer from this source. When that goes in and checks the data in the identifier, he will check the Social Security number and he will pick up a key, number 1, right? And then he will go with the local and he will find no key. But since he already found the key, he will propagate that key to the local ID and that will become a new row in the identifier table. The row that represents the Social Security number's relationship to the key is not multiplied. It just exists only one instance, independently how many systems that uses it. So, it just becomes one instance, one row per unique string, so to speak. So, all of a sudden, then you have 3 different identifiers related to the same key. Now, the fascinating thing about this is that since the table is just Identifier and key. And when you instantiate this pattern, you do it for the concept. So you have customer identifier, and then the table will be called customer identifier. And in the column, there will be a customer_idfr column and customer key. And do you do it for product? Same thing, product. So from a pattern base, there is identifier key, idfr key. That's it. And then you just add what kind of entity you want to use this identification for. And the code is exactly the same for independently of which entity. You could also say, independently of what industry you do it for. It doesn't change. It is the same. So, the table and the code works the same way independently of who you want to do identification on. So, this pattern is For me, it's not really a Focal pattern. It is part of the Focal solution. But from my point of view, you can use this for any modeling technique, really. If you want to be able to create a surrogate key, you just create this lookup table, so to speak. And you can have it for dimension modeling. You can have it for Data Vault if you dare to break the hub thingy. And you can do it, you could really do it for anchor as well. Or yeah, whatever modeling method you want, as long as you do like some sort of, I need to be able to have multiple ways of identifying things, but I don't want duplicates in my data. I just want one surrogate key for it. It's totally reusable for anyone to use really.

So let me play it back to make sure I got it right. And then we'll jump into a couple of questions. So what we're doing is we're holding a table, let's call it table. And in that table, there is a golden unique, a identity, something we're generating that we, that never changes to say we have an instance of a thing. And then we're binding that to identifiers we see. So in your example, I might bind it to an email address and a social security number and a passport number and a driver's license number, because each one of those are identifiers of a person. So what we end up with is we end up in that scenario, we would end up with the golden key that we've generated 4 times, 4 rows, and then each of the related identifiers, social security number, email address, license, passport number. And the benefit of Focal is the framework does that automatically for you. So if you go and say that social security number is the same, is related to that driver's license number, because you have a table that tells you that, then the Focal framework is going to go and do an upsert. Into that golden table. If it hasn't seen it before, or say we've seen it before, I know that relationship exists, we're okay. Am I right so far?

Yes.

And so the benefit of that is if I look at something like Data Vault, where we have same as links, I've got to codify the code. I've got to build my old framework to go, how do I do these relationships? And for Ancrum, I'm not so sure, but probably the same. And then, so that's the first part of the problem. How do we codify or patternize the identifications of these keys without having to manually do it? And then the second one is, how do we then use that in all our queries to be able to let me query any data with any of those identifiers that is valid at the point in time? And that's the bit that you're saying in the Focal Framework, you still, you haven't patternized or solved that automagically yet. That still requires some human to do that query side of using that data or Is that something you have solved?

It's depending on what, because from a Focal perspective, we more or less never use the identifiers for anything else than creating the relationship to the key, so to speak, if there is multiple ways of doing it, multiple identifiers. I will talk about that a little bit later. But so the identifier table is a technical table. It's not about using as a business representation of things. You have your key that is a unique instance of something. If you want to search or do a query on the social security number, then the social security number also is stored in the descriptive data as its own, it's its own representation. So you search there for your social security number. And we have said that there is a separation of concern here. We should have, the identifiers should be able to live and develop however they need to be to achieve integration. While the business identifiers should not be contaminated by that. So the business identifiers is held on their own in its free form. It's clear. So if we do a concatenation in the identifier table, it's not done on the business identifier. Social Security number is Social Security number. If we need to say Sweden Social Security number and Russia Social Security number, for some reason, whatever, the ID in the scripted table, it's just the Social Security number, clean, so to speak, as a business, from a business perspective, which means you could get duplicates. When you search for that, you can get two Rogues, one Russian and one Swedish guy. But yeah, that's how it works. Then you just have to choose whatever you want to have it, what you want to see. That's a separation of concern in the architecture. The identifier table is a technical table, should not be accessed by people, so to speak, because the key itself is really what binds it all together.

And then again, because it's pattern-based, you'd assume that LLMs would understand the role that technical identifier table plays because it'll see it being used the same way every time, and therefore it should use it more effectively than we've got bespoke tables or other things that are used in different ways. And then I think you said it doesn't solve the whole fuzzy matching problem, but again, there's patterns and code bases out there that allow you to do it. So all you'd be doing is injecting the fuzzy matching logic to determine what rows you need to insert or check for in the identifier table, right?

No, there is no, you can do it, but the code as it is today is just, you have it on the same row, they mean the same thing, two different columns. Yeah. So there is no magic. I usually say that there is no magic.

It's just, we talked about 3 object types. We talked about a thing that holds the keys, a table that holds the descriptions or the attributes as name, pair values, and then something that holds the relationships of the keys. This identifier table that holds this unique identifier and the system identifiers, is that a different object type or is that? Reusing one of the, just to get, I've got a funny feeling I know where you're gonna go with it. But yeah, is it a different object type or is it just reusing one of those, a couple of those three?

No, it's a different object type. In the Focal, we have the Focal. There is a Focal table that holds the surrogate key exactly as anchor key, anchor does. It's only a surrogate key. As I said, the identify table is a technical table to achieve integration on the key. So to speak. So it's its own type. But normally when I talk to Hans Hultgren, we talk about, he says, yeah, but the concept is the key, even if it's two tables.

So like we have with Data Vault, where we have HALs and SALs and these other things that we need to do from a technical point of view that break the, there's only three simple type of tables you need. I'm assuming within Focal, there are other instances of that where there are other technical tables or objects that we need for certain things, or is this the only one?

This is the only one. I mean, from a physical data modeling perspective, but then you have the whole metadata layer, but that's another animal, so to speak.

Then we get to the problem of point-in-time reporting. And so if I changed my email address as one of my identifiers at a certain point in time, it's gonna solve that problem anyway because I'm now going to automatically get another row in the identifier table to say against this golden key, I've got another identifier. And then in my queries, I'll be able to use that to say, was it this or was it that? Okay.

Yeah.

I'm just trying to think around the whole changing of identifiers and how that goes back.

I mean, there's always that. There's, yeah, there's no magic. So if you don't have a stable identifier and you change your email address, you only have email addresses as an identifier, and then you change the email address, and you don't have a column that says this was the old email address or something. It will create a new key because it doesn't know about the previous email address. But if you have a Social Security number and an email address, the Social Security number will be stable, and you change the email address, it will add the new email address to the same key.

But if I had a table that held a change of email address as an event, and so that table held previous email and new email, then it should pick it up against that golden key that the email address had changed. And so it's just another identifier against that key, correct?

Yes, then it would do that. The identifier table doesn't care which column you use. So it's just a generic column of identifier. You just have a string of values. That one is one you check. There is, of course, the ability to going up to the metadata layer and see what attributes in the source was used to concatenate this string. So it exists up there, but in the physical, from a code perspective, it's just, here's a string. Does that string exist? When he checks, when they do a lookup, he doesn't care if it's what column you use to create that string.

And again, it goes back to that idea that the metadata or the context is held outside of the physical data. So if you're using an LLM. You need to make sure that both are exposed because if you give a physical Focal data model, whether it's physical data to an LLM, it's going to struggle to understand the context of how that works.

Yeah. Yes, very much. Yeah.

Okay. That makes sense.

I can just do another thing around this because when Focal started and for the, up to, yeah, 2014 or something like that, and '15, the, or even later, but anyway, the identifier table was not optional. It was always, you always have a surrogate key. That was how it worked. But when we moved into the cloud area, with the cost of doing a lookup, when you have a lot of rows, costs every time. So if you have an entity that does not have multiple identifiers, why should you use a lookup? Why then you can just as well hash the thing or use the natural key, right? So In the Focal Framework today, the multi-identifier functionality is optional. You always start with a single identifier point of view. So you set up your entities. And if you don't have any multi-identifier at this point, you just run it as like Data Vault or something. It's just natural or hash keys. But the thing is then that when you come to a point in your development that you also know, okay, we need both social security number and email address to make this work. Then you can, in the Focal Framework, then just point at the entity and say, now this entity is multi-identifier entity. And then that gives you the ability to map multiple identifiers to that as you do your mapping and say, this identifies and this identifies. And then the framework will also migrate your data for that entity. So it will propagate the new surrogate key that is generated when you deploy before you load. It deploys and takes all the old data, integrated to the surrogate key that you have created and populates all the tables that are involved. This is also, when you think about it from an ensemble modeling perspective, is you have an entity and it has 3 relationships, right? And you point at that entity and says, this one is now multi-identifier, so it has to run on a surrogate key. It also has to migrate the data in all the relationships. And once again, since everything is metadata-driven and pattern-based. Once we figured out the script on how to migrate from the normal key or natural key to surrogate key, it works for every entities. It was just a one-go. You needed to figure out how do you do this? How do we move? What kind of things do we need to do? Once that is done, it was repeatable. So it became repeatable. So you don't have to think about it either. Oh, how should I migrate them? No, it's just point at it, say, this is multi-identifier now, and then it will migrate itself.

And this is one of the values of these frameworks that are put on top of these modeling patterns, is that they have been built out over so many years, decades. They've seen lots of edge cases, and the framework then gets updated with those edge cases. And then that code gets tested, that pattern gets tested time and time again by many people, many organizations, and many industries. And it pretty much becomes bulletproof. Versus a senior engineer writing it for the first time, or go and find a blob of code from somebody. And then obviously you've got all the Git repos, you go and harvest, and now the new one is your vibe code on the LLM. But none of that is proven versus you go to a framework that's been tested over decades and you know that those edge cases where they've been dealt with have been proven. One of the questions I always have is, when do I ever trust an organization who tells me they don't have a secondary identifier? And my answer is never, because they'll tell you they've only got one, and then 3 to 6 months later, another one will turn up in the data, and they'll be like, oh yeah, sorry, I forgot about that. And so for me, yeah, I probably would just go with multi-identifier in the beginning and wear the cost of the surrogate lookup.

At the same time, you don't need to when you have the migration script. So you just, you run it as single until you need multi. It just automatically migrates your data over to multi-identifier. and then it's just for that entity also. And if I've got billions of rows, this is something we have seen when we worked in BigQuery and Snowflake. This is really important from a cost perspective, really, because when it's huge datasets, it costs a lot to do those lookups, even if they are really efficient. So still costs data, especially since there are no true indexes and stuff like that on these platforms. So you, Even if you do a very good setup for the order of data and partitioning of data, which you can do in these areas, it still costs money. So that was the decision why we said, we know that a lot of entities will get multi-identifier over time, but until they do not need it, we can run it on single identifier code, so to speak.

Yeah. And it's that idea of optimizing where it makes sense. You know, one of the things we do is we will, for smaller volumes of data, when we see an event, we will just do an upsert. Where we see larger volumes of data and we know some things are going to happen, we might do a partitioning replace, but the system takes care of it because you optimize it for, okay, if it looks like this, do that because we know over time that's going to save us money. And if it looks like that, do this and we can switch between if we need to. But it's that baking in, optimize at the right time, at the right place, and don't be afraid to change it later if you can change it safely. Whereas in the past, we would never touch anything like that. You say to somebody, oh, can you go into your dimensional data warehouse and just rekey all the surrogates for me? Yeah, there's going to be a dangerous change, right? Because it hasn't been designed to allow you to do that.

Oh, exactly. Oh, that's true.

Excellent. All right. If people want to get hold of you, if they want to understand more about Focal, if they want to learn more about the modeling patterns and the framework, where do they go?

I would say you can either go to LinkedIn, to my profile, or you can go to dana.db, which is the project where we're working with, with the Focal framework, creating CLIs around it so we can, you can easily test it.

And give me an example what you mean by building a CLI out for this. What are you looking to achieve?

Well, it's more like doing an abstraction layer of the Focal framework since it is complex in its sort of design to make it easy for people to use. So the Dana CLI works with YAML files where you set up your models and your mappings and then that get processed into the framework, and then the framework creates the code and stuff like that. So, it's more like just ease of use. So, instead of trying to figure out how you're gonna use the Focal Framework, there is already a command line interface where you can do all the things you want to do with it, so to speak.

And the metadata tables and the physical tables, they're technology agnostic, right? So, you can run those on any of the data platforms or data technologies.

Yeah, and not. There's always these small quirks in SQL that the different platforms use. The interesting point is that Focal Framework has had Oracle, SQL Server, Snowflake, BigQuery, Postgres, but we are now in the middle of sort of revisiting the architecture. So now we have pulled us down to Postgres and BigQuery, where we start to build up all the different platforms again. But it's still certain parts are platform-specific. But since it is metadata-driven, it's not that hard once you get it to work. It's just shift. And now with AI, LLMs also, they can easily translate your patterns. So you can go, now I want this to work in Snowflake. and you just show them all the patterns and then they just transform it over. You have to test it, of course, because it's LLMs, you don't know what they're gonna do, but it goes very quickly to move to a new platform at this point.

Okay. But if somebody was starting out and they wanted to reduce the uncertainty of things they need to learn, then you're saying Postgres and BigQuery is probably the safest way to start out to learn on it.

Now you can go in. For the homepage, that's dana.dev and sign up for be a beta tester and try it out. And then we have building up a community around it because people want to be able to contribute and stuff like that. So yeah, we'll see where it goes. For me, it's really fun because all of a sudden the Focal Framework becomes accessible for people that doesn't want to put the energy or effort trying to understand the underneath under the hood, and that's really fascinating, I would say. So it's really fun.

And even if we look at something like Data Vault, which is probably a lot more well-known across the world, even then, it's still, you've got to learn some stuff. So anything we can do to remove that complexity and make it for people to simple for them to use the patterns.

Yeah.

That's a good thing in my view, right? But it takes time and effort to make something that's complex under the covers, simple to use and execute and get that value of automation and optimization out of it.

Yeah, yeah, exactly.

Excellent. All right, thank you for that. It's been a great chat. I now know a lot more around Focal than I did a while ago, so thank you for that. And I hope everybody has a simply magical day.

Same. Have a nice day, everyone.

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.