offdataai Learn · Beginner

Foundations: concepts, terms and best practices

The vocabulary and the rules of thumb behind 3NF, Kimball, Data Vault, semantic layers and ontologies: what every term means, with examples, and how the approaches fit together.

Start learning for $1/month

6 lessons · about 170 minutes · included in Explorer

Beginner6 lessons · about 170 minutes

Foundations: concepts, terms and best practices

The vocabulary and the rules of thumb behind 3NF, Kimball, Data Vault, semantic layers and ontologies: what every term means, with examples, and how the approaches fit together.

Outcome: You can explain every core term in data modeling, semantics and ontology, and apply the best practices that go with them.

1
Relational modeling (3NF): terms and best practices
Entities, keys, relationships and the normal forms: the vocabulary of operational data models and how to apply it.
Knowledge check · 25 min · instant checks
2
Dimensional modeling (Kimball): terms and best practices
Facts, dimensions, grain and slowly changing dimensions: the vocabulary of analytical models and how to apply it.
Knowledge check · 25 min · instant checks
3
Data Vault 2.0: terms and best practices
Hubs, links and satellites, the specialized satellites and links, the business vault and how a vault is loaded: every Data Vault 2.0 term with an example, plus the rules that keep a vault auditable and easy to extend.
Knowledge check · 30 min · instant checks
4
Semantic modeling: terms and best practices
Entities, measures, metrics and dimensions; metric types; additivity and the fan-out and chasm traps; time spines; governance; and how a semantic layer serves BI tools and AI assistants.
Knowledge check · 30 min · instant checks
5
Ontology: terms and best practices
Concepts, instances, properties and relationships; taxonomies, is-a and part-of; definitions, labels and synonyms; RDF, OWL and SKOS in practice; mapping to tables; and how ontologies ground AI agents.
Knowledge check · 30 min · instant checks
6
How it fits together: one architecture from source to AI agent
The capstone of Foundations: how 3NF sources, staging, Data Vault, Kimball marts, a semantic layer and an ontology-backed context layer form one stack, with the terms for moving, defining and governing data, and one question traced from source tables to an agent's answer.
Knowledge check · 30 min · instant checks

What you will be able to do

✓Define entities, keys and relationships, and read cardinality on an ERD
✓Identify 1NF, 2NF and 3NF violations from functional dependencies
✓Apply constraints and physical design choices that keep operational data consistent
✓Declare a fact table's grain and classify its measures by additivity
✓Choose the right dimension pattern and slowly changing dimension type for a requirement
✓Explain how conformed dimensions and the bus matrix keep data marts integrated
✓Define hubs, links, satellites, hash keys and hash diffs and say what each one stores
✓Choose the right satellite, link or business vault structure for a modeling problem
✓Separate hard rules from soft rules and explain how insert-only loading keeps a vault auditable
✓Explain what a semantic layer is and how it sits on the physical model
✓Choose the right metric type and aggregation, and recognize additivity and double-counting traps
✓Apply governance practices: one definition, an owner, certification and versioning
✓Explain what an ontology is and how it maps onto the physical model
✓Distinguish concepts, properties, relationships, subclass and part-of, and state cardinality
✓Write definitions and labels that remove ambiguity, and know when the formal standards matter
✓Place each modeling approach in its layer and explain the job that layer does
✓Use the cross-cutting terms for moving, defining and governing data correctly
✓Trace a business question from operational sources to a grounded AI agent answer

Free reading on these topics

Data Modeling Best Practices: Naming, Keys, Nullability, PII and Documentation

A practical checklist of data modeling best practices for warehouses and operational databases: naming conventions, keys, data types, nullability, PII handling, testing and documentation.

8 min read

Data Vault vs Kimball: When to Pick Which, and How Hybrid Architectures Work

Data Vault 2.0 and Kimball dimensional modeling solve different problems. A practical comparison of structure, change handling, auditability and effort, and how to combine them.

7 min read

Database Normalization Explained: 1NF, 2NF and 3NF With a Worked Example

A step-by-step worked example of database normalization from an unnormalized spreadsheet to third normal form, with functional dependencies, DDL and the mistakes to avoid.

8 min read

What Is a Semantic Layer? (And Why AI Agents Need One)

A semantic layer defines what your data means and turns business questions into correct SQL. How it works, and why AI agents depend on one.

6 min read

How to Build an Ontology for Your Data: Concepts, Definitions and Relationships

A step-by-step guide to building a business ontology for a data platform. Choosing concepts, writing definitions, naming relationships as verb phrases, and mapping them to tables and columns.

8 min read

How to Learn Data Modeling: A Roadmap From Beginner to Data Architect

A structured learning path for data modeling, from relational basics and SQL to Kimball, Data Vault, semantic layers and ontologies, with what to practise at each stage and how to know you are ready to move on.

8 min read

Included in Explorer

Every course and every exercise for $1 a month.

Explorer also includes the Model Builder, all 174 templates and SQL for every warehouse. Cancel anytime.

Start learning for $1/month

More courses

Relational modeling (3NF)

Beginner · 4 lessons

Dimensional modeling (Kimball)

Beginner · 8 lessons

Data Vault 2.0

Intermediate · 5 lessons

Build a semantic layer

Intermediate · 5 lessons

Build an ontology

Intermediate · 5 lessons

Context layer for AI agents

Advanced · 5 lessons

Project: Brewly, end to end

Intermediate · 9 lessons