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.