An ontology provides a formal definition of business concepts, their properties, and the relationships between them. In a modern data platform, it helps connect technical data assets to business meaning, making information easier for analysts, applications, and AI systems to interpret.
In Microsoft Fabric, it is important to distinguish between building an ontology as a conceptual design and implementing it using Fabric’s data and semantic capabilities. A Power BI semantic model, for example, can define business entities, relationships, measures, and terminology, but it is not automatically a formal ontology.
Fabric provides the data engineering, analytics, and semantic modeling capabilities that can serve as the foundation for an ontology-driven solution.
Steps to Create an Ontology in Microsoft Fabric
Microsoft Fabric now includes an Ontology (preview) item that lets you define business entities, properties, relationships, and bindings to underlying data. You can create one from an existing semantic model or build it directly from data in OneLake.
(from Microsoft Learn)
1. Prepare your data
Organize relevant business data in a Fabric lakehouse or create a Power BI semantic model with appropriate tables, columns, relationships, and measures.
(from Microsoft Learn)
2. Create the ontology
To reuse an existing semantic model, open its overview page and select Generate Ontology. Alternatively, select + New item in your workspace and choose Ontology (preview) to build one from scratch.
(from Microsoft Learn)
3. Define entities and relationships
Create business entities such as Customer, Product, Store, and Sale. Define their properties, select appropriate entity keys, and establish relationships such as a Customer placing an Order.
(from Microsoft Learn)
4. Bind the ontology to your data
Map entity properties and relationships to the appropriate source tables and columns. This connects business definitions to actual enterprise data without requiring the ontology to duplicate that data.
(from Microsoft Learn)
5. Validate and use the ontology
Verify entity keys, property mappings, and relationships. You can then explore connected data through supported ontology experiences, including graph visualization and natural-language queries using Fabric data agent capabilities.
Example: A Retail Ontology
A retailer could define the following business relationships:
Customer
places
Sale / Order
contains
Product
belongs to
Product Category
With the relationships and data bindings configured, an AI-powered experience could use this shared business context to help answer questions such as, “Which customers purchased products in our premium category?”
Important Considerations
Preview status: As of October 2026, Fabric’s Ontology item is in preview, so capabilities and user interfaces may change.
Permissions and capacity: Check that your tenant has ontology functionality enabled, your workspace uses a supported Fabric capacity, and you have the required workspace and semantic model permissions.
Quality matters: Review generated entities and relationships rather than assuming the automatically created ontology is complete.
Start with business meaning: Agree on entity definitions, identifiers, and relationships before expanding the ontology across multiple data domains.
Conclusion
Creating an ontology in Microsoft Fabric helps organizations move beyond storing and reporting data toward representing the meaning of their business information. By building on existing semantic models or binding directly to OneLake data, organizations can create a reusable business context that supports connected analytics and AI applications.
This is a quick article that describes what an ontology is and why ontologies are important.
What is an Ontology?
In data management, an ontology is a formal representation of knowledge that defines the concepts within a particular domain, their properties, and the relationships between them. It provides a shared understanding of what data means, how different entities relate to one another, and how systems can interpret those relationships consistently.
While a database schema defines how data is structured and stored, an ontology goes a step further by describing the meaning of the data and the rules governing its relationships.
What does an Ontology consist of?
An ontology typically consists of four key elements:
Entities or concepts: The things being described, such as Customers, Products, Orders, and Employees.
Properties: The characteristics of those concepts, such as a customer’s name, a product’s price, or an employee’s department.
Relationships: How concepts connect to one another, such as a Customer placing an Order or an Employee managing a Department.
Rules and constraints: Conditions that define how concepts behave or relate, such as an Order containing at least one Product.
Consider a retail business. Its ontology might define that a Customer places an Order, an Order contains Products, and each Product belongs to a Product Category. These relationships help systems understand the business beyond individual database tables.
Ontology vs. Data Model vs. Semantic Model vs. Knowledge Graph
Although these concepts are related, they serve different purposes.
Concept
Primary Purpose
Example
Data Model
Defines the structure and organization of data.
Customer and Order tables connected by a foreign key.
Ontology
Defines concepts, meanings, relationships, and rules within a domain.
A customer places an order, which contains products.
Semantic Model
Organizes data around business concepts, relationships, measures, and definitions to support consistent analysis and reporting.
A Power BI model connecting Customers, Products, and Sales, with a standardized Total Revenue measure.
Knowledge Graph
Represents entities and their relationships as connected data.
A graph showing customers, orders, products, and their connections.
An ontology can provide the semantic foundation for a knowledge graph, while a data model defines how information is organized in a particular system. A semantic model builds on data structures and business definitions to make information easier to query, analyze, and interpret consistently.
For example, a Power BI semantic model may define a standardized Revenue measure, establish relationships between sales and product tables, and organize fields into user-friendly business categories. An ontology could extend this understanding by formally defining what a Customer or Product represents and how those concepts relate across multiple business systems.
Why are Ontologies important?
Ontologies are increasingly valuable in modern data environments because they help organizations:
Improve data integration: Connect information across systems that use different structures or terminology.
Establish consistent business definitions: Ensure that concepts such as Customer, Revenue, or Active Account have a shared meaning.
Enable semantic search: Allow users and applications to find information based on meaning and relationships rather than just matching keywords.
Support artificial intelligence: Give AI systems structured domain knowledge that can help them interpret questions, identify relationships, and produce more contextually relevant responses.
Improve data discovery: Help analysts and business users understand how datasets and business concepts relate to one another.
For example, an AI assistant answering the question, “Which customers purchased products in our premium category?” could use an ontology to understand the relationships between customers, purchases, products, and product categories—even when the underlying information is distributed across multiple systems.
Ontologies and the future of data
As organizations adopt knowledge graphs, semantic models, and AI-powered applications, ontologies are becoming an important component of data architecture. They help bridge the gap between raw data and business meaning, allowing both people and machines to interpret information more consistently.
The key takeaway: A data model explains how data is organized, an ontology explains what the data represents and how its concepts relate, a semantic model makes data meaningful and usable for business analysis, and a knowledge graph represents entities and their relationships as connected information. Together, these concepts help organizations build more connected, intelligent, and AI-ready data environments.
If there is one data modeling concept that every data analyst, data engineer, and Power BI developer should understand, it is the star schema.
Although modern analytics platforms provide powerful capabilities for importing, transforming, and analyzing data, the quality of your reports ultimately depends on the quality of your data model. A well-designed star schema makes reports easier to build, improves query performance, reduces complexity, and helps ensure accurate calculations.
Whether you’re creating a Power BI semantic model, designing a Microsoft Fabric Warehouse, building an Azure SQL data warehouse, or developing a traditional enterprise data warehouse, the star schema remains the industry standard for organizing analytical data.
What Is a Star Schema?
A star schema is a data modeling technique that organizes data into two primary types of tables:
Fact tables
Dimension tables
The fact table sits in the center of the model, while the dimension tables surround it, creating a shape that resembles a star.
This simple design makes it easy for reporting tools to aggregate data while allowing users to filter information by various business attributes. Star Schema models are sometimes called Dimensional Models.
Fact Tables
A fact table stores measurable business events.
Each row typically represents a single business transaction or event.
Examples include:
Sales transactions
Orders
Shipments
Inventory movements
Website visits
Financial journal entries
Fact tables usually contain:
Numeric values (sales amount, quantity, cost)
Foreign keys pointing to dimensions
Sometimes transaction identifiers
Example:
DateKey
ProductKey
CustomerKey
Quantity
SalesAmount
20260701
125
845
3
$149.97
20260701
450
210
1
$89.99
Notice that the fact table stores very little descriptive information. Instead, it references other tables.
Dimension Tables
Dimension tables describe the facts.
Rather than storing numbers, they store descriptive attributes used for filtering, grouping, and reporting.
Examples include:
Customer
Product
Date
Employee
Store
Vendor
Geography
A Product dimension might contain:
ProductKey
ProductName
Category
Brand
Color
125
Wireless Mouse
Accessories
Contoso
Black
A Customer dimension may include:
Customer Name
City
State
Country
Customer Segment
Industry
These tables provide the business context needed to interpret the facts.
Why Is It Called a Star Schema?
When diagrammed visually, the fact table appears in the middle while dimension tables radiate outward.
Product
Customer Sales Date
Geography
Salesperson
Unlike more complicated database designs, each dimension typically connects directly to the fact table.
This simplicity is one of the reasons star schemas are so effective.
Relationships
In a star schema:
One dimension row relates to many fact rows.
Fact tables contain foreign keys.
Dimension tables contain primary keys.
For example:
Product
---------
ProductKey (Primary Key)
|
|
Sales Fact
----------
ProductKey (Foreign Key)
This creates a one-to-many relationship, which is ideal for analytical workloads.
What Is Granularity?
Granularity refers to the level of detail stored in the fact table.
Examples:
Daily Sales: One row per day.
Transaction Sales: One row per individual purchase.
Order Line Sales: One row for every item sold on an order.
The more detailed the granularity, the more flexible the reporting becomes.
Choosing the correct grain is one of the most important design decisions in a star schema.
Common Dimension Tables
Nearly every warehouse includes some common dimensions.
Date Dimension
Perhaps the most important dimension.
Contains attributes like:
Date
Year
Quarter
Month
Month Name
Week
Fiscal Year
Holiday Indicator
Instead of calculating these repeatedly, reports simply reference the Date dimension.
Product Dimension
Contains:
Product Name
Category
Subcategory
Brand
Color
Size
SKU
Customer Dimension
Contains:
Customer Name
Region
Industry
Segment
Customer Type
Geography Dimension
Contains:
Country
State
Province
City
Postal Code
Sales Territory
Measures vs Attributes
Understanding the difference between measures (stored in Fact tables) and attributes (stored in Dimension tables) is essential.
Measures (Fact Table)
Sales Amount
Cost
Quantity
Profit
Hours Worked
Attributes (Dimension Tables)
Product Name
Customer Name
State
Department
Month
Category
A simple way to think about it:
Numbers that are aggregated belong in fact tables. Descriptive information belongs in dimension tables.
Benefits of Star Schemas
1. Better Performance
Reporting engines such as Power BI are optimized for star schemas.
Fewer joins result in:
Faster queries
Better compression
Reduced memory usage
2. Simpler Reports
Users can easily understand:
Sales by Month
Sales by Customer
Sales by Product
Sales by Region
Instead of navigating dozens of interconnected tables, report authors work with a clean, intuitive model.
3. Easier Maintenance
Changes to one dimension rarely affect other dimensions.
Adding a new Product Category only requires updating the Product dimension.
The fact table usually remains unchanged.
4. Improved Data Quality
Because descriptive information is stored once, duplication is minimized.
For example, “Florida” exists once in a Geography dimension instead of appearing in millions of sales rows.
5. Scalability
Star schemas scale exceptionally well.
Many enterprise warehouses contain:
Billions of fact rows
Millions of customers
Hundreds of thousands of products
The design continues to perform efficiently.
Star Schema vs Flat Tables
Some beginners attempt to place every column into one enormous table.
While this may seem easier initially, it creates several problems:
Duplicate data
Larger storage requirements
Slower refreshes
Poor compression
Difficult maintenance
A star schema separates repeated descriptive information from transactional data, making the model both smaller and faster.
Star Schema vs Snowflake Schema
A related design is the snowflake schema.
Instead of storing all descriptive information in a single dimension, dimensions are normalized into multiple tables.
Example:
Product
|
Category
|
Department
Advantages:
Less duplicated data
Smaller dimension tables
Disadvantages:
More joins
More complicated reports
Slower query performance
Harder for business users to understand
For Power BI and most analytics solutions, a star schema is generally preferred.
Best Practices
When designing a star schema:
Determine the grain of each fact table before loading data.
Use surrogate keys for dimension relationships when appropriate.
Keep dimensions descriptive and facts numeric.
Avoid storing repeated descriptive data in fact tables.
Use conformed dimensions (such as Date or Customer) across multiple fact tables.
Create one-to-many relationships from dimensions to facts.
Using bidirectional filtering without a clear need.
Mixing transaction-level and summary-level data in the same fact table.
Including calculated totals in fact tables instead of calculating them in reports.
Storing descriptive text in fact tables.
Ignoring the importance of a proper Date dimension.
Avoiding these pitfalls leads to cleaner, more maintainable models.
Star Schemas in Power BI
Power BI is designed to work exceptionally well with star schemas.
Benefits include:
Faster report performance
Better DAX calculation behavior
Simpler filter propagation
Easier report development
Improved semantic model organization
Microsoft recommends using star schemas whenever possible when designing Power BI semantic models.
Real-World Example
Imagine a retail company tracking sales.
Sales Fact
Sales Amount
Quantity
Discount
Cost
Connected to:
Date
Customer (contains Customer Segment)
Product
Store (contains Region)
Employee
Promotion
Business users can easily answer questions such as:
Which products sold the most this month?
Which region generated the highest profit?
What is the average order value by customer segment?
Which promotions increased sales?
How did sales compare year over year?
This flexibility is one of the major reasons star schemas have become the standard for business intelligence.
Frequently Asked Questions
Can a star schema have multiple fact tables?
Yes. Many enterprise data warehouses include multiple fact tables, such as Sales, Inventory, Budget, and Returns, all sharing common dimensions like Date, Product, and Customer.
Why shouldn’t descriptive columns be stored in fact tables?
Doing so increases duplication, wastes storage, and makes updates more difficult. Dimension tables provide a single source of truth for descriptive information.
Are star schemas only used in Power BI?
No. Star schemas are widely used in Microsoft Fabric, Azure Synapse Analytics, SQL Server, Oracle, Snowflake, Amazon Redshift, Google BigQuery, and many other analytics platforms.
Is a star schema required?
Not always, but it is considered the best practice for most analytical reporting and business intelligence solutions because it balances simplicity, performance, and scalability.
Conclusion
The star schema is one of the most important concepts in modern analytics and data warehousing. By separating measurable business events into fact tables and descriptive business information into dimension tables, it creates a model that is easy to understand, efficient to query, and scalable for organizations of any size.
Whether you’re building dashboards in Power BI, designing a Microsoft Fabric Warehouse, or developing an enterprise data warehouse, mastering star schema fundamentals will help you create faster reports, more reliable analytics, and data models that are easier to maintain over time. A solid star schema is not just a design choice—it is the foundation of effective business intelligence.
One of the most important concepts in Microsoft Power BI is the Semantic Model. While reports and dashboards are what users see, the semantic model is the intelligence that sits behind them. It organizes data, defines business logic, and ensures that reports produce consistent, accurate results.
A well-designed semantic model makes report development faster, simplifies maintenance, improves performance, and creates a single version of the truth for an organization.
What Is a Power BI Semantic Model?
A Power BI Semantic Model is a structured collection of data, relationships, calculations, and business rules that provides a business-friendly view of your data.
Think of it as the translation layer between your organization’s raw data and the reports your users consume.
Instead of report developers needing to understand dozens of database tables and SQL queries, they simply connect to a semantic model that already contains:
Imported or connected data
Relationships between tables
Measures
Calculated columns
Hierarchies
Data formatting
Security rules
Business definitions
The semantic model allows users to analyze data without needing to understand where the data originally came from.
Why Is the Semantic Model Important?
The semantic model serves as the foundation for nearly every Power BI report.
Some of its biggest benefits include:
Creates a single source of truth
Eliminates duplicated business logic
Improves report consistency
Simplifies report development
Improves report performance
Makes security easier to manage
Enables report reuse across teams
Without a semantic model, every report developer would need to create their own calculations for example, resulting in inconsistent numbers across reports.
What Makes Up a Semantic Model?
A semantic model typically contains several key components.
Tables
The business data that users analyze.
Examples include:
Sales
Customers
Products
Employees
Dates
Relationships
Relationships connect tables together so Power BI understands how information relates.
For example:
Sales → Customer
Sales → Product
Sales → Date
Proper relationships eliminate the need for complicated report calculations.
Measures
Measures perform calculations at query time.
Examples:
Total Sales
Average Order Value
Profit Margin
Year-to-Date Sales
Measures are generally preferred over calculated columns for aggregations because they are more flexible and consume less storage.
Calculated Columns
Calculated columns create new values that become part of the data model.
Examples include:
Full Name
Profit Category
Fiscal Quarter
Hierarchies
Hierarchies make navigation easier.
Example:
Year → Quarter → Month → Day
Data Formatting
Semantic models define:
Currency formats
Percentages
Decimal places
Date formats
This ensures reports display information consistently.
Row-Level Security (RLS)
Security rules determine which data each user is allowed to see.
For example:
Regional managers only see their own region.
Sales representatives only see their own customers.
How Is a Semantic Model Created?
The typical process looks like this:
Connect to one or more data sources.
Clean and transform data using Power Query.
Load the data into Power BI.
Create relationships.
Create measures using DAX.
Configure formatting.
Build hierarchies.
Configure security.
Publish the semantic model to the Power BI Service.
Once published, reports can connect directly to the semantic model rather than importing data again.
How Is a Semantic Model Maintained?
Like any business asset, semantic models require ongoing maintenance.
Common maintenance activities include:
Refreshing data
Adding new tables
Creating or updating relationships
Updating business calculations
Optimizing model performance
Reviewing and updating security
Creating new columns or removing unused columns
Documenting business definitions
Monitoring refresh failures
and more
A well-maintained semantic model becomes increasingly valuable over time.
Shared Semantic Models
One of the greatest strengths of Power BI is the ability to share a semantic model across many reports.
Instead of creating ten separate datasets containing the same sales data:
Build one high-quality semantic model.
Allow many reports to connect to it.
Benefits include:
Consistent calculations
Less duplicated work
Smaller storage footprint
Easier maintenance
Better governance
Faster report development
This approach is sometimes called the “build once, report many” strategy.
Best Practices
When designing semantic models, consider the following recommendations.
Use a Star Schema
Organize data into:
Fact tables
Dimension tables
This improves both performance and usability.
Hide Technical Columns
Hide columns that report authors should not use.
Examples:
Primary keys
Foreign keys
Internal IDs
This creates a cleaner report authoring experience.
Create Measures Instead of Repeating Calculations
Store business calculations centrally.
Instead of recreating “Total Sales” in every report, define it once inside the semantic model.
Use Meaningful Names
Instead of:
SalesAmt
Use:
Total Sales
Business-friendly names improve usability.
Remove Unnecessary Data
Only import:
Needed tables
Needed columns
Needed rows
Smaller models perform better.
Document Business Logic
Describe:
Measures
KPIs
Calculations
Business rules
Future developers will appreciate the documentation.
Optimize Relationships
Avoid unnecessary many-to-many relationships when possible.
Keep relationships simple and easy to understand.
Securing a Semantic Model
Security should be considered from the beginning rather than added later.
Important security practices include:
Use Row-Level Security (RLS) when different users should see different data.
Apply workspace permissions using the principle of least privilege.
Secure the underlying data source.
Protect sensitive information using sensitivity labels when appropriate.
Limit who can modify the semantic model.
Review permissions regularly.
Good security protects both the data and the business.
Common Mistakes to Avoid
Many new Power BI developers make similar mistakes.
Building a Separate Model for Every Report
Instead, reuse a shared semantic model whenever possible.
Importing Every Column
Extra columns increase model size and reduce performance.
Creating Duplicate Measures
One calculation should exist only once.
Poor Naming
Names like:
Measure1
Calc2
Table3
make models difficult to maintain.
Ignoring Relationships
Incorrect relationships often produce incorrect totals.
Always validate relationship directions and cardinality.
Excessive Calculated Columns
Use measures whenever practical for aggregations.
Skipping Documentation
Undocumented models become difficult to maintain as teams grow.
How to Make Your Semantic Model More Valuable
Organizations receive the greatest value when they treat the semantic model as a shared enterprise asset.
Some ways to maximize its value include:
Develop reusable measures.
Standardize business definitions.
Encourage report developers to connect to existing semantic models.
Validate and certify trusted semantic models for organization-wide use.
Monitor usage to identify opportunities for improvement.
Regularly review performance and security.
Keep the model simple, clean, and well documented.
As adoption grows, the semantic model becomes the central foundation for business reporting.
Frequently Asked Questions
Can multiple reports use the same semantic model?
Yes. In fact, this is one of the primary design goals of Power BI. A single semantic model can support dozens—or even hundreds—of reports while ensuring consistent calculations and business definitions.
What is the difference between a semantic model and a report?
The semantic model contains the data, relationships, measures, and business logic. A report is the visual presentation that connects to and displays information from the semantic model.
Can a semantic model connect to multiple data sources?
Yes. A semantic model can combine information from databases, spreadsheets, cloud services, data warehouses, data lakes, and many other supported data sources.
Who should create semantic models?
Ideally, semantic models are created and maintained by BI developers, data engineers, analytics engineers, or Power BI developers who understand both the organization’s data and its business rules.
When should a new semantic model be created?
A new semantic model should generally be created only when the data serves a different business domain or has substantially different security, refresh, or performance requirements. Otherwise, extending an existing shared semantic model is often the better choice.
Can security be applied inside the semantic model?
Yes. Row-Level Security (RLS) can restrict which rows users see, and Object-Level Security (OLS) can hide specific tables or columns from certain users when supported. These features help enforce data access policies consistently across all reports that use the model.
Summary
The Power BI semantic model is the foundation of effective business intelligence. It transforms raw data into a reusable, business-friendly resource by defining relationships, calculations, security, and business logic in one central location.
Organizations that invest in well-designed, shared semantic models benefit from more consistent reporting, faster report development, improved performance, stronger governance, and easier maintenance. By following best practices—such as using a star schema, creating reusable measures, documenting business logic, securing data appropriately, and encouraging report reuse—you can build semantic models that deliver lasting value across the organization.
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. This topic falls under these sections: Describe an analytics workload (25–30%) --> Describe data visualization in Microsoft Power BI --> Describe features of data models in Power BI
Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.
A data model is the foundation of any effective report in Microsoft Power BI. It defines how data is structured, related, and calculated, enabling efficient analysis and meaningful visualizations.
For the DP-900 exam, you should understand how data models work, their key components, and best practices.
What Is a Data Model in Power BI?
A data model is a logical representation of data that includes:
Tables
Relationships
Calculations
It allows Power BI to:
Combine data from multiple sources
Enable filtering and aggregation
Support interactive reporting
Key Features of Power BI Data Models
1. Tables
Data models consist of one or more tables, which can come from:
Databases
Files (Excel, CSV)
Cloud sources
✔ Tables contain rows (records) and columns (fields)
2. Relationships
Relationships define how tables are connected.
Types of Relationships
One-to-many (1:*) → Most common
Many-to-one (*:1)
Many-to-many (:)
Key Concepts
Primary key → Unique identifier in one table
Foreign key → Reference in another table
✔ Relationships enable filtering across tables
3. Schema Design (Star Schema)
Power BI models commonly follow a star schema:
Fact tables → Contain measurable data (e.g., sales)
Dimension tables → Contain descriptive data (e.g., customer, product)
✔ This structure improves performance and usability
4. Measures and Calculated Columns
Power BI uses DAX (Data Analysis Expressions) for calculations.
Measures
Calculated at query time
Used in aggregations (e.g., SUM, AVERAGE)
Calculated Columns
Computed during data load
Stored in the model
✔ Measures are preferred for performance
5. Data Types
Each column has a defined data type:
Text
Number
Date/Time
Boolean
✔ Correct data types ensure accurate calculations and visuals
6. Hierarchies
Hierarchies allow users to drill down into data.
Example
Year → Quarter → Month → Day
✔ Used for interactive reporting and exploration
7. Filtering and Cross-Filtering
Relationships enable:
Filter propagation between tables
Cross-filtering in visuals
✔ Example: Selecting a product filters related sales data
8. Data Granularity
Granularity refers to the level of detail in data.
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. This topic falls under these sections: Identify considerations for relational data on Azure (20–25%) --> Describe relational concepts --> Identify common Structured Query Language (SQL) statements
Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.
Understanding basic SQL statements is essential for working with relational data and is a key requirement for the DP-900 exam. You are not expected to be an advanced SQL developer, but you should recognize common SQL commands, their purpose, and when they are used.
What Is SQL?
Structured Query Language (SQL) is the standard language used to:
Query data
Insert new data
Update existing data
Delete data
Define database structures
SQL is used across relational database systems, including Azure services like:
Azure SQL Database
Azure Database for PostgreSQL
Azure Database for MySQL
Categories of SQL Statements
SQL statements are typically grouped into categories:
Category
Purpose
DDL (Data Definition Language)
Define and modify database structures
DML (Data Manipulation Language)
Work with data in tables
DQL (Data Query Language)
Retrieve data
DCL (Data Control Language)
Manage permissions
For DP-900, focus primarily on DDL, DML, and DQL.
1. Data Query Language (DQL)
SELECT
Used to retrieve data from a table.
SELECT Name, City
FROM Customers;
You can filter results:
SELECT Name
FROM Customers
WHERE City = 'Seattle';
💡 Key Points:
Most commonly used SQL statement
Can include filtering, sorting, and grouping
2. Data Manipulation Language (DML)
INSERT
Adds new rows to a table.
INSERT INTO Customers (Name, City)
VALUES ('John', 'Seattle');
UPDATE
Modifies existing data.
UPDATE Customers
SET City = 'Austin'
WHERE Name = 'John';
DELETE
Removes rows from a table.
DELETE FROM Customers
WHERE Name = 'John';
💡 Important: Always use a WHERE clause with UPDATE and DELETE to avoid affecting all rows.
3. Data Definition Language (DDL)
CREATE
Creates new database objects such as tables.
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
City VARCHAR(50)
);
ALTER
Modifies an existing table.
ALTER TABLE Customers
ADD Email VARCHAR(100);
DROP
Deletes a table or database object.
DROP TABLE Customers;
💡 Warning: DROP permanently removes the object and its data.
4. Additional Common SQL Clauses
WHERE
Filters rows:
SELECT * FROM Orders
WHERE Amount > 100;
ORDER BY
Sorts results:
SELECT * FROM Orders
ORDER BY Amount DESC;
GROUP BY
Aggregates data:
SELECT City, COUNT(*)
FROM Customers
GROUP BY City;
JOIN
Combines data from multiple tables:
SELECT Orders.OrderID, Customers.Name
FROM Orders
JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
💡 DP-900 Tip: You don’t need deep JOIN knowledge — just understand that JOINs combine related tables.
SQL in Azure
SQL is used across many Azure services:
Azure SQL Database
Fully managed relational database
Uses T-SQL (Microsoft’s SQL variant)
Azure Synapse Analytics
Used for analytical queries on large datasets
Azure Database for PostgreSQL
Uses PostgreSQL SQL dialect
Why This Matters for DP-900
On the exam, you may be asked to:
Identify what a SQL statement does
Match commands to their purpose (SELECT, INSERT, etc.)
Recognize DDL vs DML
Understand basic query concepts like filtering and sorting
Summary — Exam-Relevant Takeaways
✔ SELECT → Retrieve data ✔ INSERT → Add new data ✔ UPDATE → Modify existing data ✔ DELETE → Remove data
✔ CREATE / ALTER / DROP → Define and modify structures ✔ WHERE → Filter results ✔ ORDER BY → Sort data ✔ GROUP BY → Aggregate data ✔ JOIN → Combine tables
✔ SQL is the standard language for relational databases
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. This topic falls under these sections: Identify considerations for relational data on Azure (20–25%) --> Describe relational concepts --> Describe normalization and why it is used
Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.
Normalization is a foundational concept in relational database design. For the DP-900 exam, you are expected to understand what normalization is, why it is important, and how it improves data quality and efficiency.
What Is Normalization?
Normalization is the process of organizing data in a relational database to:
Reduce data redundancy (duplicate data)
Improve data integrity
Ensure logical data relationships
This is done by splitting data into multiple related tables and defining relationships between them using keys.
Why Normalization Is Used
Normalization is used to solve common data problems in poorly designed tables.
1. Reduce Data Redundancy
Without normalization, the same data may be repeated across multiple rows.
Example (Unnormalized Table):
OrderID
CustomerName
CustomerCity
Product
1
John
Seattle
Laptop
2
John
Seattle
Mouse
Here, customer information is duplicated.
After Normalization:
Customers Table
CustomerID
Name
City
1
John
Seattle
Orders Table
OrderID
CustomerID
Product
1
1
Laptop
2
1
Mouse
Now, customer data is stored once and referenced using a key.
2. Improve Data Integrity
Normalization ensures that data remains accurate and consistent.
Without normalization:
Updating a customer’s city requires changing multiple rows
Missing one update leads to inconsistent data
With normalization:
Data is updated in one place only
Consistency is maintained automatically
3. Prevent Data Anomalies
Normalization helps prevent common issues:
Insert anomaly: Cannot add data without unrelated data
Update anomaly: Inconsistent updates across rows
Delete anomaly: Deleting one record removes important data
Normalized designs eliminate these problems.
4. Improve Data Organization
Normalized databases:
Clearly separate different entities (customers, orders, products)
Use relationships to connect data logically
Make databases easier to maintain and scale
Understanding Normal Forms (Simplified for DP-900)
Normalization is often described in stages called normal forms. For DP-900, you only need a basic understanding:
First Normal Form (1NF)
No repeating groups or multi-valued fields
Each column contains atomic (single) values
Second Normal Form (2NF)
Meets 1NF
All non-key columns depend on the entire primary key
Third Normal Form (3NF)
Meets 2NF
No dependency between non-key columns
💡 DP-900 Tip: You do NOT need to memorize formal definitions — just understand that normalization reduces redundancy and improves integrity.
Trade-Offs of Normalization
While normalization has many benefits, there are trade-offs:
Advantages
✔ Reduces duplicate data ✔ Improves consistency ✔ Simplifies updates ✔ Enhances data integrity
Disadvantages
✖ Requires more tables ✖ Queries may require joins ✖ Can slightly impact performance for complex queries
Normalization vs Denormalization
Understanding this comparison is important for the exam:
Feature
Normalization
Denormalization
Data Redundancy
Reduced
Increased
Data Integrity
High
Lower
Query Complexity
Higher (joins required)
Lower
Performance
Slower for reads
Faster for analytics
Use Case
Transactional systems (OLTP)
Analytical systems (OLAP)
Where Normalization Is Used in Azure
Normalization is commonly applied in relational database services such as:
Azure SQL Database
Azure Database for PostgreSQL
Azure Database for MySQL
These services are typically used for transactional workloads, where data integrity is critical.
Why This Matters for DP-900
On the exam, you may be asked to:
Identify why normalization is used
Recognize normalized vs unnormalized structures
Understand how normalization affects data integrity
Distinguish normalization from denormalization
Summary — Exam-Relevant Takeaways
✔ Normalization organizes data into multiple related tables ✔ It reduces data redundancy ✔ It improves data integrity and consistency ✔ It prevents insert, update, and delete anomalies ✔ It is commonly used in transactional (OLTP) systems ✔ It may require joins when querying data
What is the primary purpose of normalization in a relational database?
A. Increase data duplication B. Improve query speed for analytics C. Reduce data redundancy and improve integrity D. Store unstructured data
✅ Answer: C
Explanation: Normalization reduces duplicate data and improves consistency and integrity.
Question 2
Which issue is MOST likely to occur in a non-normalized table?
A. Faster query performance B. Data redundancy C. Reduced storage requirements D. Simpler queries
✅ Answer: B
Explanation: Without normalization, the same data is often stored multiple times.
Question 3
A database stores customer details repeatedly in every order record. What problem does normalization solve in this scenario?
A. Improves indexing B. Reduces redundancy C. Enhances encryption D. Increases data size
✅ Answer: B
Explanation: Normalization separates customer data into its own table to avoid duplication.
Question 4
Which anomaly occurs when updating the same data in multiple rows leads to inconsistent values?
A. Insert anomaly B. Delete anomaly C. Update anomaly D. Query anomaly
✅ Answer: C
Explanation: Update anomalies happen when duplicate data is not consistently updated.
Question 5
Which approach is commonly used in normalized database design?
A. Storing all data in a single table B. Splitting data into related tables C. Removing all relationships between data D. Using only unstructured formats
✅ Answer: B
Explanation: Normalization divides data into multiple related tables.
Question 6
Which statement best describes First Normal Form (1NF)?
A. Data must be encrypted B. Tables must include foreign keys C. Each column contains atomic (single) values D. Tables must be denormalized
✅ Answer: C
Explanation: 1NF requires that each field contains indivisible values and no repeating groups.
Question 7
What is a trade-off of normalization?
A. Increased data redundancy B. Simpler queries with no joins C. More complex queries due to joins D. Reduced data integrity
✅ Answer: C
Explanation: Normalized databases often require joins, making queries more complex.
Question 8
Which type of workload benefits MOST from normalized data?
A. Analytical workloads B. Transactional workloads C. Streaming workloads D. Machine learning workloads
✅ Answer: B
Explanation: Transactional systems prioritize data integrity, which normalization supports.
Question 9
Which action is an example of normalization?
A. Combining multiple tables into one B. Storing duplicate data for faster reads C. Separating customer and order data into different tables D. Removing relationships between tables
✅ Answer: C
Explanation: Normalization separates data into logical entities and links them via keys.
Question 10
Why might a system choose denormalization instead of normalization?
A. To improve data integrity B. To reduce redundancy C. To improve read performance for analytics D. To enforce strict schemas
✅ Answer: C
Explanation: Denormalization is often used in analytical systems to improve query performance.
✅ Quick Exam Takeaways
For DP-900, remember:
✔ Normalization = reduce redundancy + improve integrity ✔ Involves splitting data into related tables ✔ Prevents:
Insert anomalies
Update anomalies
Delete anomalies
✔ Used primarily in transactional (OLTP) systems ✔ Trade-off = more joins / more complex queries ✔ Opposite concept = denormalization (used in analytics)
This post is a part of the DP-900: Microsoft Azure Data Fundamentals Exam Prep Hub. This topic falls under these sections: Identify considerations for relational data on Azure (20–25%) --> Describe relational concepts --> Identify features of relational data
Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.
Relational data is one of the most fundamental concepts in data management and a core focus area for the DP-900 exam. Understanding how relational data is structured, stored, and accessed will help you confidently answer questions related to databases, querying, and Azure data services.
What Is Relational Data?
Relational data is data that is organized into tables (relations) consisting of:
Rows (records)
Columns (attributes or fields)
Each table represents a specific entity, such as customers, orders, or products. Relationships between tables are defined using keys.
Core Features of Relational Data
1. Tabular Structure (Rows and Columns)
Relational data is stored in a structured, tabular format:
Each row represents a single record
Each column represents a specific attribute
Example:
CustomerID
Name
City
1
John
Seattle
2
Maria
Austin
This structure makes relational data easy to query and understand.
2. Predefined Schema
Relational databases enforce a fixed schema, which defines:
Table structure
Column names
Data types (e.g., INT, VARCHAR, DATE)
This ensures:
Data consistency
Data validation
Predictable structure
3. Use of Keys
Keys are essential for uniquely identifying records and linking tables.
Primary Key
Uniquely identifies each row in a table
Cannot contain duplicate or null values
Example: CustomerID
Foreign Key
Links one table to another
Establishes relationships between tables
Example: Order.CustomerID → Customer.CustomerID
4. Relationships Between Tables
Relational data supports relationships such as:
One-to-One
One-to-Many
Many-to-Many
Example:
One customer can have many orders (one-to-many)
These relationships allow complex data models to be built efficiently.
5. Structured Query Language (SQL)
Relational data is accessed and manipulated using Structured Query Language (SQL).
SQL is used to:
Query data (SELECT)
Insert data (INSERT)
Update data (UPDATE)
Delete data (DELETE)
Example:
SELECT Name FROM Customers WHERE City = 'Seattle';
6. Data Integrity and Constraints
Relational databases enforce data integrity through constraints such as:
PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
CHECK
These rules ensure that:
Data is accurate
Relationships remain valid
Invalid data is prevented
7. Normalization
Relational data is often normalized to reduce redundancy and improve consistency.
Normalization involves:
Splitting data into multiple related tables
Eliminating duplicate data
Ensuring dependencies are logical
Example:
Instead of storing customer details in every order row, store them in a separate Customers table.
8. ACID Transactions
Relational databases support ACID properties, ensuring reliable transactions:
Atomicity → All or nothing
Consistency → Valid state maintained
Isolation → Transactions don’t interfere
Durability → Changes persist
This is especially important for transactional workloads.
Relational Data in Azure
Azure provides several services for working with relational data:
Azure SQL Database
Fully managed relational database
Supports SQL queries
High availability and scalability
Ideal for OLTP applications
Azure Database for PostgreSQL
Managed open-source relational database
Supports PostgreSQL features and extensions
Azure Database for MySQL
Managed MySQL database service
Suitable for web and application workloads
These services support structured data, relationships, and SQL-based querying.
Why This Matters for DP-900
On the exam, you may be asked to:
Identify characteristics of relational data
Recognize table-based structures
Understand keys and relationships
Distinguish relational data from non-relational data
Match relational workloads to Azure services
Summary — Exam-Relevant Takeaways
✔ Relational data is stored in tables (rows and columns) ✔ It uses a fixed schema with defined data types ✔ Primary and foreign keys define relationships ✔ Data is accessed using SQL ✔ Supports data integrity constraints ✔ Often normalized to reduce redundancy ✔ Ensures reliability with ACID transactions
