Best practices · Data and ML
Data Modeling Best Practices
Opens with creditsBest practices for modeling analytics data, checked against the Kimball Group's dimensional modeling techniques, Microsoft's dimensional modeling guidance and the dbt documentation. It covers the grain, fact and dimension tables, slowly changing dimensions, dbt marts and naming, tests and contracts, and defining metrics once in a semantic layer.
Written by Tauseef Fayyaz7 chapters, 26 practicesAbout 18 min25 quiz questionsReviewed 13 October 2026What you will learn
7 chapters · about 18 min
1Start from the business process and the grainEvery good dimensional model starts with two decisions: which business process you are modeling and what one row means. These practices make both explicit.4 practices · 3 min
2Fact tablesFact tables hold the measurements of a business process. These practices cover picking the right type of fact table, keeping measures summable and keeping keys clean.4 practices · 3 min
- Dimension tablesDimensions give facts their meaning: the who, what, where and when that people filter and group by. These practices keep them stable, shared and easy to use.4 practices · 3 minOpens with credits
- Slowly changing dimensionsCustomers move, products change category, salespeople change region. These practices decide which changes rewrite history and which keep it.4 practices · 3 minOpens with credits
- dbt layers and namingNames are the interface of a model. These practices, from dbt's structure and style guides, make models easy to find, read and join.3 practices · 2 minOpens with credits
- Tests and contractsA model other people depend on is an interface. These practices test its logic and lock its shape so changes do not break consumers by surprise.4 practices · 2 minOpens with credits
- Metrics and the semantic layerWhen every dashboard computes revenue its own way, the numbers drift apart. These practices define entities, dimensions and metrics once, next to the models.3 practices · 2 minOpens with credits
About this guide
A warehouse full of tables is not the same as a model people can use. Analysts lose hours when nobody knows what one row of a table means, when the same customer appears under three different keys, or when last year's sales move to a new region because someone overwrote a dimension. These data modeling best practices are about building tables whose meaning is clear and stays stable.
The guide is built on dimensional modeling, the approach Ralph Kimball and the Kimball Group made standard: business events go into fact tables, the context people filter and group by goes into dimension tables, and every fact table has one declared grain. Microsoft's dimensional modeling guidance for Fabric Warehouse and Power BI is used throughout, because it explains the same ideas with concrete table designs.
The implementation examples use dbt Core 1.12, which many teams use to build these models in SQL with tests, contracts, snapshots for history and a semantic layer for metrics. The modeling ideas do not depend on dbt; the same rules apply if you build tables with stored procedures, Spark or another tool.
This guide covers what the tables should look like. How data gets there reliably, with idempotent loads, orchestration and backfills, is covered in the data pipeline best practices guide in this library, and checking the data once it lands is in the data quality checklist.
Common questions
What is the grain of a fact table?
The grain is what one row of the fact table represents, such as one order line or one product per warehouse per day. The Kimball four-step process declares it right after choosing the business process, before dimensions and facts, and every row in the table must be at that grain.
What is the difference between a fact table and a dimension table?
A fact table stores measurements of events or states, such as quantities and amounts, with keys to dimensions. A dimension table describes the things involved, such as customers, products and dates, with attributes people filter and group by. Facts are usually narrow and very long; dimensions are wide and shorter.
When should I use a type 2 slowly changing dimension?
Use type 2 when reports must show history as it was, for example sales under the region a salesperson belonged to at the time. Use type 1 for corrections and for attributes where only the current value matters. Microsoft's guidance recommends type 1 for most attributes and type 2 only where history matters.
Should I use a star schema or a snowflake schema?
Prefer a star schema with denormalized dimensions for most analytics work: it is simpler to query and usually faster. Snowflaking makes sense for very large dimensions where storage matters more, or when other facts relate to a higher level of a hierarchy.
What does a dbt model contract do?
An enforced contract declares a model's column names and data types. dbt checks the model's query against it at build time and fails the build on a mismatch, and flags breaking changes such as removed or retyped columns. Constraint enforcement depends on the platform, so keep data tests for uniqueness.
Sources (8)
Checked against these, last on 13 October 2026.
- Four-Step Dimensional Design Process, Kimball Groupwww.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/four-4-step-design-process/
- Modeling Dimension Tables in Warehouse, Microsoft Learnlearn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-dimension-tables
- Modeling Fact Tables in Warehouse, Microsoft Learnlearn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-fact-tables
- Semantic models, dbt docsdocs.getdbt.com/docs/build/semantic-models
- Marts: Business-defined entities, dbt docsdocs.getdbt.com/best-practices/how-we-structure/4-marts
- How we style our dbt models, dbt docsdocs.getdbt.com/best-practices/how-we-style/1-how-we-style-our-dbt-models
- Snapshots, dbt docsdocs.getdbt.com/docs/build/snapshots
- Model contracts, dbt docsdocs.getdbt.com/docs/mesh/govern/model-contracts
Related on Sefism
Picked from the other libraries by shared topics.
Roadmaps
See allCheat sheets
See allBest practices
See all- Data Pipeline Best PracticesHow to build data pipelines you can re-run, test and trust: idempotent tasks, orchestrator hygiene, layered transformations, data tests, freshness, ownership and monitoring.
- Data Analysis Best PracticesAn analyst's workflow in SQL, pandas and Polars: framing the question, reading data correctly, joins and grain, aggregation pitfalls, reproducible notebooks, checking results and sharing findings.
- Data Visualization Best PracticesHow to make charts that are clear, accessible and honest: choosing the chart, encoding, color, axes and scales, annotation, dashboards and avoiding misleading charts.
Checklists
See all- Data Quality ChecklistA data quality checklist for warehouse tables: ownership, freshness, volume, schema, nulls, uniqueness, referential checks, anomaly monitoring and data incidents, with how to verify each.
- Analytics Dashboard ChecklistAn analytics dashboard checklist for launch day: purpose and audience, metric definitions, data freshness, correctness, performance, access, accessibility, ownership and adoption.
- ML Model Deployment ChecklistAn ML model deployment checklist: baselines, training and serving consistency, validation, model registry, canary rollout, rollback, drift monitoring and ownership, with how to verify each.
Project structures
See all- dbt project project structureA dbt project that turns raw tables into tested, documented models, using the staging, intermediate and marts layers from dbt's own structure guide. It runs on DuckDB on your laptop, so you can learn analytics engineering for free, and grows into a governed project with checks, contracts, slim CI and a hosted docs site that moves to BigQuery or Snowflake unchanged.
- Apache Airflow project structureFor data engineers scheduling real pipelines with Airflow 3. It starts from
astro dev init(dags, include, plugins, tests and a Runtime Dockerfile that runs Airflow locally in Docker) and grows into dags grouped by business area, logic in a tested include package, asset-driven scheduling, data quality checks, alerts, CI that parses and tests every dag, and dag-only deploys. A self-hosted docker compose and Helm route is shown alongside. - GenAI RAG app (FastAPI) project structureFor developers turning a "chat with your documents" demo into an app people can rely on. A FastAPI backend where every layer has a home: ingestion workers, retrieval, prompts as versioned files, model clients with fallbacks and cost caps, guards, evals that gate CI, and tracing, so a wrong answer can be traced to the step that caused it.
Career questions
See all- What is the difference between a data analyst, a data scientist and a machine learning engineer?Analysts answer business questions with SQL, spreadsheets, dashboards and reports. Data scientists add statistics, experiments and predictive models. ML engineers build and run models inside production software. Titles vary by company, so read the work in the job advert, and pick by the kind of problem you enjoy, not the title that sounds most senior.
- How do I switch from data analyst to data engineer?You already have the hardest part: SQL, business context and knowing what good data looks like. Add Python for pipelines, data modelling, an orchestrator, a cloud warehouse, Git and testing. Build one end-to-end pipeline, then move internally by taking on engineering work in your current team before applying outside.
- Can I get a remote data engineering job from Africa?Yes, it is realistic, and data engineering is one of the skills global employers most often hire remotely from Africa. You need solid SQL and Python, one cloud platform, a pipeline tool and a warehouse, two or three end-to-end projects, and a search aimed at remote-friendly companies and talent platforms. Most remote hires have some experience, so a local first job helps.
Stuck on something specific?
Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.
- 1:1 Mentorship
- Fresh Graduate Mentorship
- Career Guidance
Brand deals, promos and partnerships: tauseeffayyaz@gmail.com