Alexis
Welcome to Talk Tech with Data Dave. I’m Alexis here from D3Clarity with my dear friend, Data Dave. 

Data Dave
Good afternoon, Alexis. This is Data Dave, Dave Wilkinson, CTO of D3Clarity with a career in data, data management, and all things data. 

Alexis
We are here to talk about just that all things data with Data Dave, hence the name Talk Tech with Data Dave 

Dave, the question today has a really cool background. Last month we talked about critical data elements, and then I had the pleasure of listening to you, during a presentation, talk about critical data elements. And we got a question from the audience that I thought would be awesome to explore here on the podcast. It’s kind of a two-part. Is time, time and date, a critical data element, and then if yes, when would it be considered a critical data element?  

So, let’s break that down into a couple of pieces. Is time a critical data element? 

Data Dave
Let me try and answer that first by going back to the definition of critical data elements that we used both in the presentation and last month. The critical data element is the minimum set of elements or attributes that is required to transact with an entity for a specific purpose. That’s the strict definition that we use that means that it’s any element that is needed to truly identify either the thing that you’re transacting with or the transaction itself.  

Often we break that down into master critical data elements, which are things that describe the entity a customer, a person, an object, a product, a thing, and the transaction itself. Is it a sale? Is it a new customer onboarding? Is it a customer service event? What is happening? What is the event?  

Those critical data elements, as we discussed last time, do change as you change for purpose. So, for a sale versus a purchase for a new customer versus a customer service event, et cetera. The question around time is an interesting element, right? Because time is not really identifying of a thing, but it is necessary if you’re going to pinpoint a transaction.  

If you’re going to pinpoint an event, then you need the time of that event. An event usually happens in a time and a place to an individual or to a thing, right? You attended a conference. Well, where was the conference? When was the conference? Time is definitely necessary to describe that event.  

I would usually say that time is a critical data element for a transaction or a transactional CDE. For transactions, it is an event that means that the event of selling occurred at a point in time. When was the credit card swiped? When was the new customer onboarded? When did this happen? Then it becomes critical. 

Now is it identifying the transaction? Not really. It’s an interesting one. It’s in that grey area, right, cause is it identifying the transaction? It’s not. Think, you sold this to me. It a sale. But it is pinpointing that event in the timeline. It is saying this occurred at this point in time. You sold this pen to me on Friday. Friday is a critical data element in identifying, but it doesn’t identify the pen. It doesn’t identify you or me, but it does identify when ownership of the pen changed.  

In that regard for that transaction, it would be considered a critical data element. It’s also critical when you start to say, “I’m going to roll this up and start to say how many pens did I sell in the month of June?” From a business perspective, it is a critical data element because it’s critical to the reporting and to the financial analysis and things that are going to go on past that, and so you do need to capture it as pinpointing that event. 

Alexis
That time is what’s going to help identify if, for example, I bought or I sold you two of those pens, the same transactions, the same people. [laughs] He’s got two pens. It’s one of them a flashlight? Important question. 

Data Dave
No, they’re not. Neither of them are flashlights. Loaded question. 

Alexis
Okay so, I sold you two pens. The critical data element in that fact is going to be the time because it’s the same people selling the same product and the same person buying, but the time is what’s going to differentiate the two. Is that correct? 

Data Dave
Right, exactly. And you want to know whether it happened in May or June? This one was sold to me in May, and this one was in June. And you need to know that because then it comes to revenue recognition and other things within that construct. So, you do need time to pinpoint that transaction.  

If you’re in a store, for example, if it’s a retail situation. Then you want to know what store and when, so you’re pinpointing in time and space for, “When did this transaction occur?” Does that make sense? Which Walmart were you in when you bought the pen, and when did you do it? 

Alexis
We talked about that earlier when we talked about data mining. The idea of this store sells winter coats during these months, and this is when they sell them and how many they sell because then we can figure out later. 

Data Dave
That’s right. For some particular things, like what you’re saying, then time is indeed a critical data element for that product because it’s the seasonality of this product is actually pertinent to that product in that aspect. Then there’s a seasonality where time is valuable.  

“This is a winter product. Winter is a season. Is it a definition of time?” Well, to a certain extent, yes, it is. That is time acting as a master CDC because it is actually identifying a pertinent piece of information about the product, right? A winter coat versus a summer jacket – is that time or is that a descriptor of the product? We have to think about it from that perspective as well when it’s used as a descriptor of the product. 

Alexis
In my brain, I think about time or dates in a sense of, like, how we format a date and time. What is used? Are we gonna use military time? Are we gonna use AM and PM? Are we gonna write in the morning? And the afternoon.  

So, is there a preferred method from, I don’t know what, the big picture governance or the big picture analytics standpoint, like is their preferred method that we should be using to track our dates and times in general? 

Data Dave
So that’s another very interesting question, right? “We’ve decided for this transaction that we do need to track time. We want to know when a transaction occurred, which is very valuable. How do we do that?” You’re not necessarily always in control of it, right? Because different applications will choose internally how they track time, and what format they use for time and date. Are they going to use the system time and date of the machine that they’re using? Are they going to use the database time and date type format where every major database has a timestamp format or a date time format? Are they going to use a string and force a format in it within that structure?  

You need to understand this to be able to track it precisely, right? Because when you bring all this data together and start saying, “I’ve got three different ERP systems in different regions of the world, when did my transactions occur? When is midnight on the 1st of January?” 

That’s actually an interesting question. When you talk about Europe and the US and different things, right? So, you need to understand that, let alone, is it stored as the number of seconds from 1970 or is it stored in a date time format or is it some proprietary format that can be rendered at extraction time into a string of some format.  So we need to think about that as we’re writing our ETL jobs or extraction jobs, or when we’re bringing the day together and expecting it to join together when we’re starting to say, “What transactions occurred in January or what transactions occurred in February, et cetera. How do we know how this is coming out?”  

So it is very good to define your date time structure, your date time format, as part of your data governance strategy so that as data is extracted from an application, it is always placed in the format that you want to see it so that it can be joined and collected and rolled up with other date time formats. 

Alexis
What I’m hearing you say is, it’s not always up to the user. Or the, you know, the person building or working in a system, what format we use. Because sometimes the system will define it for us. Otherwise, it doesn’t really matter that much as long as it’s consistent. 

Data Dave
It doesn’t really matter within context of the one application that does it in the same way it does matter when you get data from multiple different places, and you’re trying to bring it together. You need to make sure that it is consistent. For example, if you sold me a pen on 4/3/2023 and you sold me a pen. On 3/4/2023, which one is European? Right. Are they the same day or are they a different day? Which one is in March? Which one is in April?  

You don’t know, and so you have to understand this as you get the data out of the system to know what is going on. Otherwise, as you bring this data together and start to expect it to join, expecting it to roll up, expecting it to correlate, you’re going to see some interesting results. So, you have to be careful and if you get a number that is just. 1,323,000, whatever it is, then that’s probably the number of seconds since 1970. So exactly when was it? And there are standard functions to know that.  

Alexis
That’s what Excel does right? Whenever, all of a sudden, my dates become just a big long number. It’s the number of days since June 1st or January 1st, 1970. 

Data Dave
Yeah, it’s the. Number of seconds I believe. Let alone when you’re dealing with birth dates and things, when you’re starting to talk about, “Yeah. Is it the four-character year or two-character year?” We’re getting to the point where that’s starting to become relevant as we move further through the century, and we’ve got records, electronic records that do go back to birth dates in the 1960s. 

Alexis
Birthdays are something that I was thinking about when you were talking about master CDEs versus transactional CDEs. It kind of sounded like transactional CDEs would be the time when time would be important. But if we’re talking about me as a human, one of my identifying factors is the date of my birth. So, in that case date time would be a master CDE. 

Data Dave
Exactly date time becomes a descriptor, and this gets very interesting. We spent a lot of time dealing with healthcare records where the date of birth becomes relevant, and especially when you’re starting to talk about the birth of twins, when you’ve now got two people born at the same time. Then you got twin A and twin B. And they don’t have names yet, but they’ve got the same date of birth. They can be difficult to use that as a identifying factor, but it is still a descriptor for that date of birth, and that usually can be entered and can be stored.  But it’s knowing how it is put together.  

Alexis
Got you. So, I think I understand a lot more about time as a CDE now, and I think I actually understand a little bit more about critical data elements now. I felt really good at the end of the last podcast, but now I’m like, “Oh man! I think I learned something else!” 

Data Dave
Good, excellent. 

Alexis
I am Alexis, thank you so much for joining us. 

Data Dave
And I am Data Dave.  Thank you very much. And thank you, Alexis. 

Alexis
Have a great day everyone.