How long will this data modeling project take?

 

I was working with a Data Engineering executive yesterday on estimating the size of a data modeling effort. Calling her an "engineering manager" doesn't get to her depth, so "executive" will have to do. She's a serious software engineer who knows her stuff, and we've both worked on data warehouse projects for years at the same company, and we've worked with the same people off and on for at least six years. But she's still a little mystified at how to estimate a data modeling effort, and even though I've written thousands upon thousands of complex sentences, that Gemini can thankfully distill down to 25 words... well she just hasn't had time to read them all.

So I thought I'd walk through here in very concise terms the heuristics I use to estimate modeling effort. I learned these with and from other people over the last 30 years, and also via my own trial and error. Over the last seven years though I've worked with a lot of data modelers, more than I ever worked with, and I learned way more than I did in the prior ~23. I'll try to draft our collective wisdom. 

Data modelers and architects are vague about modeling effort on purpose, and also embarassed about the lack of precision. The vague aura of mystery isn't an act. Data modeler managers can often be just about helpless when it comes to estimates of their team's time. It happens to me personally all the time. But when I don't know how to find the answer, I look for someone with a different perspective. That's partly what informs these thoughts: Collecting as many different conceptual, logical or physical models as I can to use as a base for assumptions.

Here's the plan of attack. I'll name the heuristic in bold, and then describe some experiences and add some thoughts.

Heuristics

The client thinks all they need is A, but to use A you must also have B and C

Suppose a client asks you to estimate the FTEs required to build data models to master data to some level of accuracy for their Customer, Product and Subscription domains. These three data models will be the sources for three other data models, supporting analytics for Attribution, Acquisition, and Personalization programs, their terms for their use-cases.

These three source domains are foundational. A Subscription is just an Order. Theoretically we've got all the data about and between the domains in the basic business "tripod" of Orders, Customer and Product, at least from the standpoint of the revenue transaction. This is a good foundation for a Finance team. But when we look at what the client wants to do with that data, namely the three analytical models, it's obvious we're missing a fourth source model: Campaign.

Why do we need Campaign? Because the tripod isn't enough data for Marketing. The Tripod is necessary data. We must have it to analyze Attribution etc. But its not sufficient for Marketing, or Logistics/Supply Chain, or a whole bunch of other groups within the company. Finance is often quite happy with just the tripod. What Marketing needs is a way to sift Orders by conversion patterns, so they can make more of the patterns that work. That all happens in Campaign, and its very messy. 

This is typical, by the way. Marketing runs on Campaigns. Much of the data model for a Campaign is actually stored within the values of the various NAME strings. There are taxonomies instantiated in the CAMPAIGN_NAME field that are well-governed, but only when the people are seriously disciplined.

Already we've identified a discrepancy between the client's ask and the reality of the modeling effort. Feel good? This isn't a small discrepancy, and in the example case of Campaign its often the hardest to model because there usually isn't anything like a validated source of truth. Its spreadsheets all the way down. That's why the engineers tend to avoid worrying about it, because in some ways its not an 80% problem. But the Campaign domain is also almost always missing in a Marketing assessment. Thus our first heuristic: The client believes a data model for entity A is logically sufficient to accomplish their goals, but their goals require more than entity A.

I can assert that this is true, though, even if I don't know anything about the client. The data in the Tripod is useful to a soulless PE firm or product company, but not enough for Marketing to do a good job. The Data Modeling project as spec'd will be perceived as a failure without also wrangling Campaign into some trellis. This is a simple but also Quantum leap observation.

Every source has more than one data model

When we look at the system architecture from a data modeling standpoint, we assume every system on the system architecture diagram has at least one unique data model, and probably more than one.

In an easy case, the logical steps for the data modeling effort for Customer are:

1. Identify all the Customer-specific fields across all the data models on the system architecture diagram
2. Prove a map between fields that mean the same thing across those different data models 
3. Document the transformations as records move through the system architecture 

So far, pretty straightforward. In the case of Customer and Product domains we generally have a simple 80/20 problem. For each view of the real/core Customer record across the system architecture, it's likely that nearly all of the relevant fields will be sourced exclusively from one source, and copied exactly or someone gets mad. Just for Customer, specifically. This is almost always because of Conway's Law. A larger organization might have reason to allow the creation of Customer.DATE_OF_BIRTH values in multiple systems, maybe because of corporate acquisitions that haven't been integrated. But most CTOs know enough about data to realize multiple DOB fields is a bad idea, so for the Tripod domains we can err on the side of optimism that there's been some discipline in field copies. That includes documentation, CRUD analyses, etc.

The fields required to master a Customer domain will be pretty local to the sponsor, though, plus mostly some very natural and easy-to-handle necessary fields required to identify the individual. This means that on the whole there are a small handful of data models that must be explored to integrate Customer data. Customer is thus an "easy" model. 

Except when its corporate hierarchies! And then we want to hire Moody's. Some companies have done contract work for a wide variety of companies for decades. Office furniture sales and design comes to mind; a largely still artisanal operation with infinite tiers of quality and customization services and BOMs that ... well you need some pretty sophisticated math to analyze that. An Office furniture company like that collects corporate hierarchies over time in a succession of CRM-like data models: The ABC station in Des Moines, the Disney offices in Burbank, the Production office for the Jimmy Kimmel show, etc. They have a hard enough time keeping the hierarchies of their actual productive business relationships accurate, never mind figuring out that all three of those trees are part of the same company.  

The boredom we experience with Customer is generally even more true of Product and Subscription/Order, with some qualifications (e.g. do they have a million skus all in GSheets with deep hierarchical structures, or Toyota-level-item counts? Three CommerceCloud instances? etc.).

Now, my calm and optimistic estimate above will only ever be true of most clients, where most is ... 70-80% in a good quarter? Because every source has more than one data model, we can still expect that any one source domain will explode in complexity. The likelihood that one of those Tripod domains has some quirk that makes it explode in complexity is ... 1 in 4? We can point to this risk as live because they've come to us asking for help. Corporate hierarchies are one mindfield.

Complexity is inevitable in Campaign, though. So we will have to tackle that at the client, along with the Tripod. There will be more than one undocumented data model for Campaign. It's so much more fun to model virtually anything but Campaign. It's the semantic challenge of the Age.

In our example case, I happen to know they don't have very many source systems for Campaign. The logic within the Customer might still be complex in terms of ad-hoc or weird segments or types or whatever, but that part of the Tripod doesn't look scary to me. Same with Product: I know they have fewer than 100 products, and I could sort out the naming convention mapping problems between layers with ~4-32 hours of manual "curatorial" mapping work in a GSheet.

System architecture data flow documentation will tell you a lot

All the required mapping and transformation work on Customer or Product fields has gotten much easier to do in 2026. Inferring provenance, transformations, etc., used to be some combination of brute-force effort and guesswork and luck. The learned helplessness of modelers is partly due to that work. If however you can find and/or generate some kind of spiderweb of copy steps and blobs of business logic out of the source code, and even better identify the blobs with the fields you need for e.g. Marketing's view of Customer, then you'll have an easier time finding what you need. So consider that another scale of measurement: The degree to which the system architecture data flow is documented tells you a lot about the modeling effort. This can vary in a few dimensions too: Total coverage of the ecosystem, level of detail or resolution in those boxes, age of the local coverage, etc. 

Our example client had excellent documentation of their Marketing data ecosystem. And while it was an organic mess, with arrows going everywhere, I was pretty confident I could find my transformation blobs in low-finite time, through brute force search-and-analysis and review with the devs. 

Foundation field count, plus dispersion

Let's suppose we're told there's "50 sources." When we consider all of the 50+ data models across those sources, we know that for the Tripod domains there's only ever a handful of sources. (Unless we're explicitly told, for example, that the Customer can type their name into like 50 different apps, in which case we have a different kind of problem.) So that means a lot of those sources are probably specific to one group, like Marketing or ... whoever. 

Across those 50+ source data models, only a very small set of fields are actually relevant as sources to our Tripod domains. This is our foundation field set. There will however be many target fields in many other systems that reference subsets of those Tripod sources, including the organic reporting systems. There are also many GSheets where CAMPAIGN_NAME is constructed referencing out of out-of-date Tripod domain data, but that sheet is the source-of-truth for that particular foresighted but random slice of the Marketing team, and then pasted into another GSheet. And so on. And then almost certainly transformed to fit field validation in the target ad platform. 

For the most part in early data modeling we're not concerned with those target fields, except for, as was mentioned in the scope, those related to Acquisition, Attribution and Personalization. Those business processes are the ones paying us to look for relevant fields in the ENTITY layer, and we'll represent those proceses (at least internally) as PIVOT objects. We've got three target PIVOT models, and three ENTITY models plus the missing Campaign. We have a strong argument for focusing all of our effort on the generalizability of those four ENTITY models, so we can maximize productivity in PIVOT. If we can support Finance with a subset of the Tripod domain we've got in ENTITY, for example, we can generate a whole new set of combinations. Add in Telemetry data (i.e. "product-user behavior") and you can generate a brand new set of deeply-enriched PIVOT objects. Etc.    

However it is likely with CAMPAIGN_NAME that there is serious business-critical transformation between the source and a whole lot of targets, from Meta Ads to GA4 to AEM to ... that must be accounted for in the Campaign model the client needs to build. This is what I mean by Dispersion: the number of potential objects across the relevant client team's RAW, ENT, PIV, RPT and EGR layers with unique transformations. Tripod domains generally have strong governance on their RAW and ENT layers, as records move through systems. We don't get much actual attribute data about those domains, though, downstream, except the various types and categories associated with counts.

The CAMPAIGN_NAME string however lives in the "Trafficking" GSheet, and helps Client Services people at the media agency organize the upload and optimization parts of the campaign. It must be modified - in fact, optimized - to fit into one of ~200 different media publisher platform specs, which is the next step in the Campaign execution business process. The Trafficking GSheet "inspires" the names in the next step, more or less, but it's the "manifest" for that part of the process too. Trafficking.CAMPAIGN_NAME itself is a version of similar strings in a set of upstream docs, which were also an "inspiration."

In our example case, I knew the client had an artisanally-coded organic MarTech ecosystem, meticulously documented by the Director who was waiting for the day they'd hire an Architect who'd give him some advice. I also knew they managed all of their experiments in an AirTable instance, which included performance, so there was a "source of truth" with leverage on the business. 

The point of this heuristic: If you know or can identify quickly a source-of-truth for a domain, some local evidence of discipline, for each of the Tripod + 1 domains, there's less data modeling effort. You generally have a problem finding a source of truth for the Campaign domain, and getting a simple governance solution like Airtable started is usually a 3-month project.   

Consistency of data modeling practice

There's a consideration at play that we very rarely get visibility on until we start the modeling effort, but which would really help with estimation. That's the consistency of data modeling practice across the source systems. 

For example, it may be that the team of software engineers who built a particular source system were fond of using enums instead of externalizing or at least decoupling "lookup" lists from the source code that interprets them. A modeler has to search the entire code base for these different enum blocks to integrate their values into a reporting data model. But at least they know what to search for, if its consistent. With a less disciplined engineering team, or cobbled-together product data models, there will be lookup-table equivalents in JSON files or fields, code-based enums, database tables, GSheets and blockchain entries. 

When I think of a scale to quantify this, the range is not a linear progression. Even within the "well at least they're consistent" set of models there's the possibility the org still has 2+ standards. 

A friend of mine manages the data for an oncology software company, with a core transaction data model of 500+ tables that covers products, product experiments and acquisitions over the last 25+ years. Five different billing systems integrated into this thing, across users from all over the globe. Multiple radiology machine, chemotherapy regimes, and similar models specifically designed to be the source of truth for an automated system. Transaction tables that have clearly just glued together other transaction tables with the fields they need, so they can move customer data from one order management model to another. Some lookups in enums, some in JSON, some in tables, some that can't ever never be changed but have become one variable in a global metric definition.  

For me the signpost of this consistency is what I'll call the field-category problem. When you look at records in the source systems, you see a couple of features. First, stuff like "5th normal form vs. 2nd vs. 3rd" and "foreign keys are natural keys instead of artificial keys." But you also start to notice whether the fields are used to store values from decoupled lookup lists, or are themselves the values of those lookup lists. You see fields you know you can't decode without talking to product owners at least, especially in Salesforce sources.

The "three CommerceCloud instances" example above is a relevant example: The Software Architect responsible for ecommerce at a 10-brand company created three separate brand-specific instances of the platform in four years, a great success for his "consolidated platform" strategy. Every new instance learned from the past, and every instance's model was partly to wildly inconsistent across the other two, in most but not all dimensions. Very thorough. As you'd expect from a disciplined architect. Except that the prior instances were still taking orders, and sure the data models were like Picassos but there was ultimately no logical overlap between most of the models. Neither the category fields nor the hierarchies he put in those fields to track business process - both of those plus maybe a couple more are "the Data Model" - matched each other. Some of the lookup values became fields and vice versa. He was working on his fourth system and he promised he'd learn from the prior ones again and this time be good about ... being consistent, with the best from all three prior instances. The 4th version would be another Picasso, and this time he'd get it right. 

Another example to show how quickly this can go asymptotic. I worked for a healthcare agency onboarding a group of epidemiologists and public health people to a software app for public health report collection, designed to replace manual tally sheets sent via email across schools etc. and public health nurses. I was brought in after they'd created unique 1000+-field formsets for 25+ diseases. They could do this very quickly because the app's backend was just two EAVs, one to store form elements for each "disease," and the other to store form values for each disease report-instance. The latter pointed to the former to supply meaning to the value, and some complicated logic in the app layer kept them in synch over updates etc. The Software Architects designed 25 unique systems in ~9 months, and were collecting data in 12 of them with hopes to roll out the cross-disease reporting everyone just knew was the inevitable consequence of this digital transformation. 

There were at least four different field names for "date of birth," depending on which architect did which disease. And even within a given architect's work they changed their minds sometimes. Across 1000+ fields? It was a months-long MDM project just to master Disease fields across 25X1000 fields. 

So the Architects had eliminated the possibility of doing automated cross-disease monitoring for 25 diseases. They'd (1) have to do better going forward after they picked a standard and actually stuck to it no exceptions, which is just so hard and unacceptable friction if you're a Software Architect, and (2) either manually maintain a rickety semantic layer for 25 diseases, or admit they made a mistake and replace each of the old diseases with the new standard. I doubt very many Software Architects would even now see those choices as entailing that risk to their project.

Back to the client: Again, based on the MarTech documentation, the likelihood that there's just a mess of unaccountable downstream reuse but the intent was a rebuild, a true Neurath's Boat, that even then we were looking at most three or four different devs. At this client they were unlikely to think of themselves as Artists.

Conclusion

That's enough heuristics. You could probably build at least a Star Schema to track evaluation criteria for each of those heuristics, if you wanted to somehow quantify. They are mostly different kinds of measurement evaluations.

What do they look for, in common? Discipline across the system architecture: OK you have a box here. Can you point me to code or am I guessing? The active assumption is that there is some logical rats-nest you can spot if you look at the system architecture from a couple of different perspectives, that they can't untangle, and more than likely the Campaign entity is the cause.

Where do they differ? Not sure. In practice, they may actually overlap, because we rarely get the insight into the client that I had with our example. What we had before the request for a modeling estimate was a full view of their timelines, MarTech and culture, and so as I ran through my mental list I spotted problems for later.

In practice, we rarely get anywhere near that depth of insight. So, we watch for a way to apply at least those five heuristics to the estimation process, filtering any small bit of data through five or more analytical paths.

What helps?

Number one, a full view of the MarTech, Marketing strategy timelines (e.g. Black Friday?), and the Culture. 

Number two: Visibility. Long ago if you asked a DBA for access to the system tables, what we usually call the INFORMATION_SCHEMA, they acted like you asked for their spouse's phone number. Microsoft used to strongly advise against any query of the INFORMATION_SCHEMA-like tables in SQL Server, because it'd reveal secrets of database management that no mere mortal - well, look, shudder. But we all get access now, and the tuning scripts the wizards used to run against and with those control tables are now automated. Snapshots of all of the client's INFORMATION_SCHEMAs over a couple of weeks would help across all five heuristics. That helps you map provenance and identify a bunch of other useful stuff that you can apply heuristics on.

Teams that use dbt or equivalent will grow just as organically given enough time as if all they had was stored procedures. But the newer tools usually create some graphs of model provenance, which means the modeler doesn't have to keep all that in their head and refer to their hunches. If you can get those, even if the coverage of the whole giant thing is spotty, they can provide some views.

Data catalog access is helpful. But generally without some way to programmatically query the data in the back end of the catalog, the schema the catalog itself uses to store data, you have limited confidence you're seeing "all the data" when you're estimating how big the problem is. Here you probably will get the "you want what?" look I mentioned above from the Data catalog's admin dba, because they believe deeply in role-based security and there is no "data god" role. Its just unheard of.

What we're looking for then is scans in different wavelengths of the whole ecosystem. We want to positiveIy know which parts of the map are "oh don't worry, that's out of scope" and why. The sore spots are where the pain is, and that's the semantic knots we need to untangle.

Not knowing where those are leans the estimate closer to the "helpless" part of the scale. 

How many people/how long will this take?

The estimate needs to account for risk:

1. You run into an explosion of complexity in a domain you didn't plan for
2. You discover you also need at least the basics from B and C domains because just A adds no value
3. You pursue work on integration with a source that turns out not to be doable
4. The client demands you reconcile and collapse vast constellations of business logic across dozens of custom dashboards and do a lift-and-shift layer so everyone keeps their current cozy views
5. The rest of the team has completed their tasks - platform tuned to run at high speed, dashboards QA'd and confirmed with the business using sample data, the business worked to a fever pitch by the analytics people - but you still haven't gotten access to key source data, and the project is 8 weeks in

Those are all viable versions of some basic themes the heuristics are designed to catch. In the first four risks the solution is more people, even after you've run into the minefield. Best to have a "free safety" on the data modeling team who can offer 50% of their time to a specific domain as needed to ramp up early.

In general the free safety resource is critical, and we try to overestimate risk.

I assume one competent Senior Data Analyst can do a useful foundational (by which I mean "reusable for at least a year") data model for one or two domains in three months. By "useful" we generally understand this to mean, on my team, "a data model with a six-month shelf life that can be integrated with other data models or on its own to output useful facts and figures and such." We assume a bare minimum of six weeks of actual contact with the data, at least three or four weeks of which should precede dashboard development on any PIVOT tables dependent on those ENTITY tables.

What we're really estimating then is risk to that six-week MVP. Risk to that six-week MVP can happen at any point in the first three months - you discover the source model you were using is out of date two weeks before the end of the project, for example. Or a late-added custom Salesforce field requires some rethinking.   

Generally for Marketing engagements we begin data modeling Campaign and Order, because that gives us good and bad paths through the funnel for optimization. What's needed from Order is minimal, and so we can focus most of our efforts on building out Campaign, including the performance data or "vanity metrics" used to measure campaigns inflight, which is nearly always disconnected. Next priority would be either Customer or Product, depending on strategy. 

For our client example, important piece of info: Most of their business is Black Friday-dependent.

Given the size of the scope - Tripod + 1 and Attribution, Personalization and the other one - we want to move fast if we can use anything by Black Friday. Depending on local priorities we will need to have Campaign, Order and Customer (just to pick one) in the first three months, plus reasonably one or more of Attribution etc. That's four domains, with one at least tightly coupled to two of the three ENTITY tables. You can do that build thinking longitudinally in terms of a DAG, having your modelers and engineers parse out work across say the three ENTITY models plus one or two of the PIVOT models using a target POC as a guide. Another option is fill out all of e.g. Attribution with Tripod + Campaign data in the first three months, because you need that to support Personalization. (Because whatever.)  

But either way, they need 2+1 ENTITY models and 1-2 PIVOT models in three months to use that before Black Friday. In three month chunks that gives us one of those two above options for the first quarter. 

That puts the estimate at three modelers plus a free-safety modeler for three months to hit the first chunk of utility, and then the same four modelers for another three months to get the remainder of the original scope done. After that there will be inevitable additions, logically and physically - new sources, new business processes in PIVOT, etc. You won't need four modelers then. But you will need at least two for the remainder of the first year, I think, especially in the PIVOT layer.

That estimate has a low risk of failure. I expect three of the modelers to solve three of my domain problems. The fourth modeler is insurance, and an accelerant: Either another domain or more utility from on or more one domains.

Pull the free-safety out, and you can see where the risk over six months starts to grow.