"Active member" should mean the same thing in every report you publish.
When finance and operations bring different customer counts to the same meeting, the problem is usually the model. We design dimensional models, with Data Vault 2.0 where history matters. Semantic layers then define each metric once, inside tools your team already licenses.
CRMaccounts · contactsCIAM and ERPlogins · customers · invoices
02 · Integration layer
Data Vault 2.0hubs · links · satellitesConformed keysone person · one key
03 · Presentation layer
Dimensional modelfacts at a grain · SCD dimensions
04 · Semantic layer
Metric definitionsdbt · semantic views · Power BIReports and AI toolsdashboards · natural-language queries
SnowflakeDatabricksdbtMicrosoft Fabric
in short·15-second read
Data modeling defines how data is structured so it can be stored, joined and analyzed consistently.
Cohash designs conceptual, logical and physical models for Snowflake, Databricks and Microsoft Fabric. We build dimensional star schemas for reporting, Data Vault 2.0 for auditable history across many sources, and semantic layers that define metrics once. We work in erwin, ER/Studio, SqlDBM or dbt.
you probably need this if
"Active member" or "customer" means something different in each team's report.
Every new report needs a custom join because there are no conformed dimensions.
Source systems change often and you need history that holds up in an audit.
From the first call to a setup your own team can run.
Here's what usually prompts a call about your Data Modeling, what we do about it and in which order, and where you end up.
01 · where it hurts02 · what we change03 · how it runs04 · where you end up
incident board · data modeling3 open
open"Active member" or "customer" means something different in each team's report.
recurringEvery new report needs a custom join because there are no conformed dimensions.
blockedSource systems change often and you need history that holds up in an audit.
exposure
Conceptual and logical modelsDimensional modelingData Vault 2.0Semantic layersModeling toolsCRM and identity entities
startrehearsehandover
Discover
Model
Implement
Maintain
we are here
ready to hand over
Analysts query star schemas with a handful of joins now.
Ask finance and sales how many customers there are and you get one answer, from a…
Conceptual and logical data models
Physical models in your modeling tool and in code
chapter 01 · where it hurts
Why teams reach out
"Active member" or "customer" means something different in each team's report.
Every new report needs a custom join because there are no conformed dimensions.
Source systems change often and you need history that holds up in an audit.
chapter 02 · what we change
How we solve it
Conceptual and logical models
Dimensional modeling
Data Vault 2.0
Semantic layers
Modeling tools
CRM and identity entities
chapter 03 · how it runs
Our approach
Discover
Model
Implement
Maintain
chapter 04 · where you end up
What changes
Analysts query star schemas with a handful of joins now.
Ask finance and sales how many customers there are and you get one answer, from a definition with an owner.
Conceptual and logical data models
Physical models in your modeling tool and in code
what we do
Where we spend the hours
6 areas of work, each one done by an engineer who has run it in production.
Before anyone writes a table, we agree the business entities and relationships with domain owners. That way the model reflects how the business actually talks.
Star schemas with conformed dimensions, slowly changing dimension handling and fact tables at a declared grain.
Hubs, links and satellites bring many sources together with full history. We only add that layer where auditability or frequent source changes make it worth it.
Metrics and dimensions defined once in dbt's semantic layer, Snowflake semantic views or Power BI semantic models, then reused by reports and AI tools.
Models maintained in erwin Data Modeler, ER/Studio or SqlDBM, with forward and reverse engineering to the target platform.
We reconcile customer, account, member and user entities across CRM, CIAM and ERP. Otherwise one person ends up with several keys.
capability 01 / 06in production
Conceptual and logical models
Before anyone writes a table, we agree the business entities and relationships with domain owners. That way the model reflects how the business actually talks.
Hubs, links and satellites bring many sources together with full history. We only add that layer where auditability or frequent source changes make it worth it.
Metrics and dimensions defined once in dbt's semantic layer, Snowflake semantic views or Power BI semantic models, then reused by reports and AI tools.
We pieced these together from Data Modeling situations that come up a lot. They're patterns. You won't find a specific client in them.
case file 01
A Data Vault that only its builder can query
composite · not a client story
Beforethe situation
An earlier team built a Data Vault 2.0 layer of hundreds of hubs, links and satellites on Snowflake. It loads reliably, but analysts query it directly, each report needs a dozen joins, and point-in-time logic is copied from report to report.
What we dothe work
We keep the vault as the integration layer and add point-in-time and bridge tables where queries need them. Dimensional marts go on top for the main subject areas, with conformed customer and product dimensions, and analyst access moves to the marts.
Afterwhere you end up
Analysts query star schemas with a handful of joins now. The vault still holds history and the audit trail. Point-in-time logic lives in one place.
case file 02
Salesforce accounts that are sometimes people and sometimes companies
composite · not a client story
Beforethe situation
The CRM holds Person Accounts for individuals and business accounts for employers, and some records have been converted from one to the other. Finance and sales report different customer counts, because each team has quietly decided for itself what a customer is.
What we dothe work
First comes a conceptual model the business signs, which separates party, relationship and role. The logical model treats Person Accounts and business accounts as subtypes of one party. Then a customer dimension with tracked history, and one definition of 'active customer' in the semantic layer.
Afterwhere you end up
Ask finance and sales how many customers there are and you get one answer, from a definition with an owner. Converting a record between account types doesn't double-count it anymore.
How source data becomes a model with metrics defined once
Source tables are integrated with full history in a Data Vault where auditability matters, then shaped into star schemas with conformed dimensions. A semantic layer defines each metric once for every report and AI tool.
01 · Sources
CRMaccounts · contacts → Data Vault 2.0 · load with history→ Dimensional model · direct load
CIAM and ERPlogins · customers · invoices → Data Vault 2.0 · load with history
02 · Integration layer
Data Vault 2.0hubs · links · satellites → Dimensional model · derive
Conformed keysone person · one key → Data Vault 2.0 · business keys
03 · Presentation layer
Dimensional modelfacts at a grain · SCD dimensions → Metric definitions · measures and dimensions
04 · Semantic layer
Metric definitionsdbt · semantic views · Power BI → Reports and AI tools · one metric definition
Reports and AI toolsdashboards · natural-language queries
Read the diagram as text
CRM → Data Vault 2.0: load with history
CIAM and ERP → Data Vault 2.0: load with history
Conformed keys → Data Vault 2.0: business keys
Data Vault 2.0 → Dimensional model: derive
CRM → Dimensional model: direct load
Dimensional model → Metric definitions: measures and dimensions
Metric definitions → Reports and AI tools: one metric definition
how it runs
A typical engagement
We don't start a step until the last one has produced something you can check.
step 01
Discover
Source structures get profiled. Business definitions come from the people who actually use the data.
step 02
Model
Conceptual, logical and physical models drafted and reviewed with domain owners.
step 03
Implement
Physical models built in dbt or DDL, loaded with real data and checked against known totals.
step 04
Maintain
Model change process, naming standards and documentation handed to your team.
you keep
What you're left with
We write these for your Data Modeling setup, and they're working documents. Your team should be able to keep things running from them long after we've gone.
design01
Conceptual and logical data models
yours to keep
design02
Physical models in your modeling tool and in code
yours to keep
document03
Metric and dimension definitions for the semantic layer
Some clients want a project team, some want a couple of engineers on theirs. Either works. Onsite, nearshore or offshore, with handovers across time zones that keep things moving.
Put these to anyone bidding on your Data Modeling work. The ones who've done it answer differently from the ones who've read about it.
Q1
Which customer attributes need full history, and which can simply be overwritten?
Type 2 history on every column bloats tables and slows queries. Type 1 everywhere loses the history auditors and analysts ask for. Decide per attribute.
Q2
Can you state the grain of each fact table in one sentence?
Fact tables with unclear grain cause double counting. A modeler should write the grain down before any SQL, in words you can check.
Q3
Will the modeling tool or the dbt code be the source of truth? And what keeps the other in sync?
erwin diagrams that drift from the dbt project mislead the next team. Agree which one is authoritative and how the other is kept in sync.
Q4
How will you get business owners to agree on a definition like 'active customer'?
Most metric fights are about meaning, not SQL. How you settle the argument matters more than the tool that stores the answer.
Q5
How will model changes be reviewed before they break downstream reports?
Renaming a column in a shared dimension can break dozens of reports. Ask about change review, deprecation periods and dbt model contracts or versions.
01Dimensional model or Data Vault 2.0?
Dimensional models are simpler to query and suit most reporting. Data Vault 2.0 adds a layer that handles many changing sources and full history well, at the cost of more tables. Many platforms use Data Vault for integration and dimensional marts for reporting. We recommend based on source count, change rate and audit needs.
02Do we need a separate semantic layer?
If more than one tool or team calculates the same metrics, yes. Snowflake semantic views, dbt's semantic layer and Power BI semantic models each work. Pick by where most queries come from.
03Which modeling tool should we use?
Usually the one you already license. erwin and ER/Studio are common in larger enterprises with established data architecture teams. SqlDBM is browser-based and popular with cloud data teams. We work in all three.
04Can you model data from Salesforce or Facets?
Yes, and often. CRM and payer data come up a lot, especially the account, contact, member and provider entities that get duplicated across systems.
05Can you fix our existing model instead of starting over?
Usually, yes. We look at which tables are actually queried, where joins are slow and where definitions disagree, then refactor the parts causing trouble. A full redesign is rarely worth the disruption to reports that already work.
related
Often paired with
Projects on Data Modeling usually end up touching one of these, so they're worth a look.
