Data modeling services where every report uses one definition per metric

Data modeling services for database, warehouse and BI schemas, not machine-learning models, with a data dictionary for every field.

Illustration of a data model: database tables linked by keys and relationships, with report charts built on topIllustration of a data model: database tables linked by keys and relationships, with report charts built on top

DATA MODELING

What does data modeling do when reports disagree?

A data model decides what your data means before anyone queries it: which entities exist, how they relate and what each field holds. Revenue gets one definition, and finance and sales stop arguing over whose number is right. A new source is mapped onto entities you already have, instead of arriving as another set of tables.

Data modeling is one part of data engineering at Octalance, and it has a boundary. The model decides which entities exist, how they relate and what each one is called. Where the data is stored and which platform runs it belong to the architecture around the model, a separate service we also offer.

A model nobody wrote down still exists. It lives in the heads of two analysts and in a dozen dashboard filters, and it changes when one of them leaves. Written down, it survives that analyst leaving, and analytics on the data model starts from documented fields.

Downstream, a definition holds when the marts share their dimensions and each metric is defined once, in a semantic layer such as a Power BI semantic model that BI tools and AI assistants read from. An assistant writing SQL picks tables and columns by name and description, so a documented model gives it a fair chance of picking the right ones.

Diagram of a data model: customer, order and product tables joined by keys, with one shared revenue definitionDiagram of a data model: customer, order and product tables joined by keys, with one shared revenue definition

METHODOLOGY

Kimball, Inmon or Data Vault: how do we choose?

The method comes down to how the data will be queried, how often the sources change and how much history you must keep. A team that reads the same monthly reports and an auditor who traces one number back to its source need different models.

We pick the method with you during model design, after discovery has shown us the sources and the reports. Plenty of warehouses mix them, and that works when the mix is chosen on purpose: Data Vault underneath for history, star schemas on top for the people who read reports.

Kimball: bottom-up marts

Dimensional data marts, as star schemas, built one business area at a time and joined through shared dimensions.

Good when the business wants reports early. The marts drift apart unless the shared, or conformed, dimensions are agreed before the second one is built.

Inmon: top-down warehouse

A normalized enterprise warehouse comes first, and department marts are built from it.

Fits when many teams need one integrated source and the scope can be planned up front. First reports arrive later, which some sponsors find hard to wait for.

Data Vault: hybrid approach

Hubs, links and satellites keep the raw history of every source, so any past value can be traced to its source.

Worth it when sources change often or auditors ask where a number came from. It means more tables and joins, so nobody should point a dashboard straight at it.

OLTP or OLAP: decide first

An OLTP model sits under an application. It is normalized so writes stay fast and consistent.

An OLAP model is shaped for reads and aggregation. One schema asked to do both usually does neither well, so we settle which one you need before picking a method.

DELIVERABLES

What our data modeling services deliver

Data model diagrams

Every entity, key and relationship, drawn by our data engineers at the levels your project needs. The diagrams are made in the modeling tool that fits your platform and team. Where the model lives in transformation code, the dbt project is the model's source.

Scripts

Scripts that implement the agreed model, handed over with the diagrams, so whoever builds it works from the design instead of retyping it.

A data dictionary

Every table and field in plain language, with what it means, its data type and the source it comes from. It is the document people open when two reports disagree, and the one a new analyst reads in the first week.

Source-to-target mapping

A field-by-field record of where each value comes from and how it changes on the way in. Whoever builds the pipelines works from it, your team or ours. Where transformation runs in dbt, the mapping is what those transformations follow.

Illustration of four modeling deliverables: a data model diagram, script files, a data dictionary and a mapping tableIllustration of four modeling deliverables: a data model diagram, script files, a data dictionary and a mapping table

MODEL LEVELS

Conceptual, logical, physical and dimensional models

Most data models are worked out at three levels, from business terms down to platform detail, and dimensional modeling is usually counted as the fourth type.

Conceptual model

The business view. Core entities such as customer, order or policy, how they relate, and the words people use for them. There are no columns or data types yet. Business owners and engineers agree on it before anyone designs a table.

Logical model

Entities get attributes, keys and relationships, still independent of any database product. This is where ER modeling and normalization happen, usually drawn as an ER diagram, and where most schema design decisions are made.

Physical model

The logical model fitted to your platform, for example Snowflake, BigQuery or Amazon Redshift, with tables, data types and the storage choices that platform offers, such as partitions, clustering, or sort and distribution keys. This is where modeling stops. Creating the tables and loading data into them is warehouse implementation, which we deliver as its own service.

Dimensional model

Built for reporting and BI. Measurable events sit in fact tables, and their context, such as date, customer or product, sits in dimension tables, usually arranged as a star schema. The method comes from Ralph Kimball's bottom-up approach. Splitting dimensions further, into a snowflake schema, is worth the extra joins mainly when a dimension is very large or changes in parts.

Two decisions shape it. The first is the grain, meaning what one fact row stands for, such as one order line rather than one order. The second is how to handle slowly changing dimensions. Type 1 overwrites an attribute, and Type 2 keeps the old value in a new row so last year's reports still add up.

Conceptual data model diagram showing core business entities and the relationships between themConceptual data model diagram showing core business entities and the relationships between themLogical data model diagram showing entities with attributes, primary keys and foreign-key relationshipsLogical data model diagram showing entities with attributes, primary keys and foreign-key relationshipsPhysical data model diagram showing tables with column data types and storage settings for a target platformPhysical data model diagram showing tables with column data types and storage settings for a target platform

EXAMPLES

Data modeling examples

Every industry arranges its core entities differently. These four example schemas show common analytical models. They are illustrations, not client projects.

Retail

Customers, orders, products, stores, promotions and inventory. Sales are usually stored at order-line grain, so discounts and returns can be read per product.

Finance

Customers, accounts, transactions, balances, loans and financial products. Transactions and balances go in separate fact tables, because a balance is a snapshot, and summing it over a month gives a number nobody owns.

Healthcare

Patients, encounters, diagnoses, treatments, providers and billing records. One encounter can carry several diagnoses, so the model needs a bridge table between them. Skip it, and a count of encounters by diagnosis counts some visits twice.

SaaS

Customers, subscriptions, plans, usage events and invoices. Plans change mid-subscription, so the model keeps subscription history. Without it, last quarter's recurring revenue changes every time a customer switches plans.

Example retail data model: an order-line fact table linked to customer, product, store and promotion tablesExample retail data model: an order-line fact table linked to customer, product, store and promotion tablesExample finance data model: customer, product and account tables, with transactions and daily balances kept in separate fact tablesExample finance data model: customer, product and account tables, with transactions and daily balances kept in separate fact tablesExample healthcare data model: patients, providers and encounters, with a bridge table between encounters and diagnosesExample healthcare data model: patients, providers and encounters, with a bridge table between encounters and diagnosesExample SaaS data model: customers, subscriptions, plans and invoices, with subscription history that records plan changesExample SaaS data model: customers, subscriptions, plans and invoices, with subscription history that records plan changes

CASE STUDIES

Warehouse and modeling work in our case studies

Transformation and modeling for an education company

A professional education company had a warehouse on Amazon Redshift and needed pipelines to fill it. We analyzed its sources, reporting needs and architecture, then chose Fivetran for ingestion and dbt for transformation and modeling. Data from Salesforce, Shopify and SAP went through dbt into analytics-ready datasets, with validation and monitoring. Read the dbt transformation and modeling case.

Contributor data for a Web3 platform

Repeated third-party API calls were driving up costs for a Web3 platform that evaluates DAO contributors. We chose an external data warehouse so the platform would stop calling the same third-party APIs repeatedly, and designed and built it with Python, Airflow, AWS Athena, dbt, Amazon S3 and AWS Glue Studio. Data updates moved from weekly to daily. Read about our data modeling for a contributor platform.

One view of guest reviews across a hotel chain

A hotel chain in Europe wanted reviews and ratings from 10 sources in one Power BI dashboard, per hotel and for the chain. We put one data model under all 10, so a review from any source is sorted by the same keywords and one hotel compares directly with another. Our client's guest rating went from 4.40 before the launch to 4.85 after. See the full review analytics for a hotel chain case.

Illustration of data projects: a contributor data warehouse, a hotel review dashboard and education reportingIllustration of data projects: a contributor data warehouse, a hotel review dashboard and education reporting

TOOLS

Data modeling tools and platforms we work with

dbt

IBM IDA

ER/Studio

ERwin Data Modeler

Oracle Data Modeler

SAP PowerDesigner

Toad Data Modeler

Snowflake

Databricks

Amazon Redshift

BigQuery

PROCESS

How a modeling project runs

Nothing gets built until step three is done. The model is settled on paper first, against your sources, your reports and the people who define each metric. Changing it on a diagram costs far less than changing it in a warehouse full of data.

  1. STEP 1

    Discovery phase

    We review your data sources, reporting requirements and existing architecture before anyone picks a tool or a method.

  2. STEP 2

    Model design

    We work the model from conceptual to physical, pick the methodology with you and write each field into the data dictionary as we go.

  3. STEP 3

    Review with your team

    Your business owners check the definitions and your engineers check the structure, and the model is revised before anything is built.

  4. STEP 4

    Handover

    We hand over the diagrams, scripts, dictionary and mapping. The build follows, by us, by your team or by your team with engineers to implement the data model.

HOW WE WORK

Engagement models and terms

Project delivery

We deliver an agreed scope end to end: plan, team, milestones, QA and release. You make the product decisions and accept each milestone.

Fixed price per milestone after discovery, or time and materials against an estimate.

  • Scope changes go through change requests.
  • On fixed-price projects, a warranty period agreed in the contract, usually 30–60 days.

Discovery phase

A short phase before a build: requirements and backlog, architecture, an estimate and a delivery plan.

Fixed price, usually 2–4 weeks.

  • The outputs stay with you whether or not the build continues with us.

Staff augmentation

Our engineers join your team and work in your process and tools. You manage them; we source, vet and contract them.

Hourly rate per engineer by role and seniority, invoiced monthly.

  • If an engineer isn't the right fit, we replace them within 14 days at no extra cost.
  • Knowledge transfer to the replacement engineer isn't billed.
  • There is no free trial period.
  • 30 days' notice to take an engineer off or end the engagement, with no minimum term beyond it.
  • Out-of-hours on-call only if agreed with you; we set its hours and billing together.
  • After two years, moving an engineer onto your own staff is something we can discuss.

Whichever model you choose

  • We reply to an inquiry from the contact form within 24 hours.
  • We sign an NDA before work starts, and IP passes to you on payment.

CASE STUDIES

Modeling work inside our data projects

A data model is often bought inside something bigger: a warehouse build, a pipeline project or a dashboard. It is still the part that decides whether the next source or report fits without rework. None of the four projects here was modeling alone.

Bought that way, the model is easy to lose, because it ends up as transformation code nobody reads. Ask for it in the same form a modeling project leaves behind, with diagrams, a dictionary and a mapping, even when the warehouse is the headline.

Not sure which model your data needs?

Tell us which reports disagree and where their data comes from, and we will suggest where the modeling work should start.

FAQ

Questions we get before a modeling project starts

How much does a data modeling project cost?

The amount is agreed with you once the scope is known, and a bigger model takes more time. The modeling project itself is a fixed price per milestone, or time and materials against an estimate. The full terms are under Engagement models and terms on this page.

What access do you need to our systems, and where does our data sit?

Our engineers work through named accounts you issue in your own IAM or SSO, with only the access each role needs, and that access is removed when the engagement ends. For a model that usually means the warehouse, the cloud account and the source systems. Where we handle your data, we sign a data processing agreement, and we work under GDPR as an EU company. Systems we control are hosted in the EU; data in your own systems stays where you keep it.

What do we keep when the project ends, and is there a warranty?

The diagrams, scripts, data dictionary and source-to-target mapping stay with you, and so do the discovery outputs, whether or not the build continues with us. IP passes to you on payment. A fixed-price modeling project carries a warranty period agreed in the contract, usually 30–60 days.

Can we buy only the model and build the warehouse ourselves?

Yes. The dictionary and the mapping are written for whoever builds next, so your engineers can start from them without us in the room. If they join the review step, the questions they would otherwise raise halfway through the build get asked before it starts. If you later want the build from us too, it runs as a warehouse project of its own.

What do you need from our team?

Access to the people who know how each metric is defined, and to the documents and systems you already have. In the review, the people who own the definitions matter more than anyone technical. A model can be clean on paper and still count customers in a way nobody in sales recognizes.

Which hours does your team work, and how much do they overlap with ours?

CET/CEST business hours by default, which gives EU and UK teams the full working day. For a US team we shift the schedule and can arrange 4+ hours of daily overlap with the West Coast, and more with the East Coast.

What should we ask a data modeling partner before we sign?

Ask who on your side reviews the definitions before any table is built, and what happens when two departments define a metric differently. Ask to see a data dictionary and a source-to-target mapping, or at least a template. Those two documents show whether a model gets written down or only drawn, and we can show you a sample or template of both. And ask what they would leave out of the first scope, because a partner who wants to model everything at once will take a long time to show you a report.