This post is a part of the DP-600: Implementing Analytics Solutions Using Microsoft Fabric Exam Prep Hub; and this topic falls under these sections:
Prepare data
--> Transform data
--> Create views, functions, and stored procedures
Creating views, functions, and stored procedures is a core data transformation and modeling skill for analytics engineers working in Microsoft Fabric. These objects help abstract complexity, improve reusability, enforce business logic, and optimize downstream analytics and reporting.
This section of the DP-600 exam focuses on when, where, and how to use these objects effectively across Fabric components such as Lakehouses, Warehouses, and SQL analytics endpoints.
Views
What are Views?
A view is a virtual table defined by a SQL query. It does not store data itself but presents data dynamically from underlying tables.
Where Views Are Used in Fabric
- Fabric Data Warehouse
- Lakehouse SQL analytics endpoint
- Exposed to Power BI semantic models and other consumers
Common Use Cases
- Simplify complex joins and transformations
- Present curated, analytics-ready datasets
- Enforce column-level or row-level filtering logic
- Provide a stable schema over evolving raw data
Key Characteristics
- Always reflect the latest data
- Can be used like tables in SELECT statements
- Improve maintainability and readability
- Can support security patterns when combined with permissions
Exam Tip
Know that views are ideal for logical transformations, not heavy compute or data persistence.
Functions
What are Functions?
Functions encapsulate reusable logic and return a value or a table. They help standardize calculations and transformations across queries.
Types of Functions (SQL)
- Scalar functions: Return a single value (e.g., formatted date, calculated metric)
- Table-valued functions (TVFs): Return a result set that behaves like a table
Where Functions Are Used in Fabric
- Fabric Warehouses
- SQL analytics endpoints for Lakehouses
Common Use Cases
- Standardized business calculations
- Reusable transformation logic
- Parameterized filtering or calculations
- Cleaner and more modular SQL code
Key Characteristics
- Improve consistency across queries
- Can be referenced in views and stored procedures
- May impact performance if overused in large queries
Exam Tip
Functions promote reuse and consistency, but should be used thoughtfully to avoid performance overhead.
Stored Procedures
What are Stored Procedures?
Stored procedures are precompiled SQL code blocks that can accept parameters and perform multiple operations.
Where Stored Procedures Are Used in Fabric
- Fabric Data Warehouses
- SQL endpoints that support procedural logic
Common Use Cases
- Complex transformation workflows
- Batch processing logic
- Conditional logic and control-of-flow (IF/ELSE, loops)
- Data loading, validation, and orchestration steps
Key Characteristics
- Can perform multiple SQL statements
- Can accept input and output parameters
- Improve performance by reducing repeated compilation
- Support automation and operational workflows
Exam Tip
Stored procedures are best for procedural logic and orchestration, not ad-hoc analytics queries.
Choosing Between Views, Functions, and Stored Procedures
| Object | Best Used For |
| Views | Simplifying data access and shaping datasets |
| Functions | Reusable calculations and logic |
| Stored Procedures | Complex, parameter-driven workflows |
Understanding why you would choose one over another is frequently tested on the DP-600 exam.
Integration with Power BI and Analytics
- Views are commonly consumed by Power BI semantic models
- Functions help ensure consistent calculations across reports
- Stored procedures are typically part of data preparation or orchestration, not directly consumed by reports
Governance and Best Practices
- Use clear naming conventions (e.g., vw_, fn_, sp_)
- Document business logic embedded in SQL objects
- Minimize logic duplication across objects
- Apply permissions carefully to control access
- Balance reusability with performance considerations
What to Know for the DP-600 Exam
You should be comfortable with:
- When to use views vs. functions vs. stored procedures
- How these objects support data transformation
- Their role in analytics-ready data preparation
- How they integrate with Lakehouses, Warehouses, and Power BI
- Performance and governance implications
Practice Questions:
Here are 10 questions to test and help solidify your learning and knowledge. As you review these and other questions in your preparation, make sure to …
- Identifying and understand why an option is correct (or incorrect) — not just which one
- Look for and understand the usage scenario of keywords in exam questions to guide you
- Expect scenario-based questions rather than direct definitions
1. What is the primary purpose of creating a view in a Fabric lakehouse or warehouse?
A. To permanently store transformed data
B. To execute procedural logic with parameters
C. To provide a virtual, query-based representation of data
D. To orchestrate batch data loads
Correct Answer: C
Explanation:
A view is a virtual table defined by a SQL query. It does not store data but dynamically presents data from underlying tables, making it ideal for simplifying access and shaping analytics-ready datasets.
2. Which Fabric component commonly exposes views directly to Power BI semantic models?
A. Eventhouse
B. SQL analytics endpoint
C. Dataflow Gen2
D. Real-Time hub
Correct Answer: B
Explanation:
The SQL analytics endpoint (for lakehouses and warehouses) exposes tables and views that Power BI semantic models can consume using SQL-based connectivity.
3. When should you use a scalar function instead of a view?
A. When you need to return a dataset with multiple rows
B. When you need to encapsulate reusable calculation logic
C. When you need to perform batch updates
D. When you want to persist transformed data
Correct Answer: B
Explanation:
Scalar functions are designed to return a single value and are ideal for reusable calculations such as formatting, conditional logic, or standardized metrics.
4. Which object type can return a result set that behaves like a table?
A. Scalar function
B. Stored procedure
C. Table-valued function
D. View index
Correct Answer: C
Explanation:
A table-valued function (TVF) returns a table and can be used in FROM clauses, similar to a view but with parameterization support.
5. Which scenario is the best use case for a stored procedure?
A. Creating a simplified reporting dataset
B. Applying row-level filters for security
C. Running conditional logic with multiple SQL steps
D. Exposing data to Power BI reports
Correct Answer: C
Explanation:
Stored procedures are best suited for procedural logic, including conditional branching, looping, and executing multiple SQL statements as part of a workflow.
6. Why are views commonly preferred over duplicating transformation logic in reports?
A. Views improve report rendering speed automatically
B. Views centralize and standardize transformation logic
C. Views permanently store transformed data
D. Views replace semantic models
Correct Answer: B
Explanation:
Views allow transformation logic to be defined once and reused consistently across multiple reports and consumers, improving maintainability and governance.
7. What is a potential downside of overusing functions in large SQL queries?
A. Increased storage costs
B. Reduced data freshness
C. Potential performance degradation
D. Loss of security enforcement
Correct Answer: C
Explanation:
Functions, especially scalar functions, can negatively impact query performance when used extensively on large datasets due to repeated execution per row.
8. Which object is most appropriate for parameter-driven data preparation steps in a warehouse?
A. View
B. Scalar function
C. Table
D. Stored procedure
Correct Answer: D
Explanation:
Stored procedures support parameters, control-of-flow logic, and multiple statements, making them ideal for complex, repeatable data preparation tasks.
9. How do views support governance and security in Microsoft Fabric?
A. By encrypting data at rest
B. By defining workspace-level permissions
C. By exposing only selected columns or filtered rows
D. By controlling OneLake storage access
Correct Answer: C
Explanation:
Views can limit the columns and rows exposed to users, helping implement logical data access patterns when combined with permissions and security models.
10. Which statement best describes how these objects fit into Fabric’s analytics lifecycle?
A. They replace Power BI semantic models
B. They are primarily used for real-time streaming
C. They prepare and standardize data for downstream analytics
D. They manage infrastructure-level security
Correct Answer: C
Explanation:
Views, functions, and stored procedures play a key role in transforming, standardizing, and preparing data for consumption by semantic models, reports, and analytics tools.
The Data Community 