Category: SQL

Data Development, Databases, Microsoft Certification, SQL August 1, 2026

Write Common Table Expressions (CTEs) (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Write advanced T-SQL code
      --> Write common table expressions (CTEs)

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

A Common Table Expression (CTE) is a temporary, named result set that exists only for the duration of a single SQL statement. CTEs simplify complex queries by breaking them into logical, readable components. They can be referenced in SELECT, INSERT, UPDATE, DELETE, and MERGE statements and are particularly useful for hierarchical queries, recursive operations, and improving query readability.

Unlike temporary tables or table variables, CTEs are not physically stored in the database. They are defined using the WITH keyword and exist only during the execution of the immediately following statement.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

What CTEs are
How to create and use CTEs
Nonrecursive CTEs
Recursive CTEs
Multiple CTEs
Using CTEs with DML statements
Recursive query patterns
Performance considerations
CTE limitations
Best practices
AI-enabled database scenarios

Understanding CTEs is important because they are commonly used in enterprise SQL development to organize complex logic, traverse hierarchical data, and prepare datasets for reporting and AI workloads.

What Is a Common Table Expression?

A Common Table Expression is a temporary named query defined immediately before another SQL statement.

General syntax:

			
WITH CTE_Name AS
(
    SELECT ...
)
SELECT *
FROM CTE_Name;

		

The CTE is available only to the statement immediately following its definition.

Benefits of CTEs

CTEs offer several advantages:

Improve readability
Simplify complex queries
Replace deeply nested subqueries
Enable recursive queries
Make SQL easier to debug
Encourage modular query design
Improve maintainability
Support DML operations

Creating a Simple CTE

Example:

			
WITH HighValueOrders AS
(
    SELECT
        OrderID,
        CustomerID,
        TotalAmount
    FROM Sales.Orders
    WHERE TotalAmount > 5000
)
SELECT *
FROM HighValueOrders;

		

The CTE filters orders before the final query executes.

Referencing a CTE

A CTE behaves similarly to a temporary result set.

Example:

			
WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
)
SELECT *
FROM CustomerTotals
WHERE TotalSales > 10000;

		

CTEs and Query Readability

Without a CTE:

			
SELECT *
FROM
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS SalesTotals;

		

Using a CTE often makes the query easier to understand, particularly when multiple steps are involved.

Multiple CTEs

Multiple CTEs can be defined within a single WITH clause.

Example:

			
WITH CustomerTotals AS
(
    SELECT
        CustomerID,
        SUM(TotalAmount) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
),
TopCustomers AS
(
    SELECT *
    FROM CustomerTotals
    WHERE TotalSales > 10000
)
SELECT *
FROM TopCustomers;

		

Each CTE can reference earlier CTEs defined in the same WITH clause.

Recursive CTEs

A recursive CTE repeatedly references itself until a termination condition is met.

It consists of:

An anchor member
A recursive member

Example:

			
WITH Numbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT *
FROM Numbers;

		

Result:

Recursive CTE Structure

A recursive CTE has two parts:

Anchor member

Returns the initial result.

SELECT 1 AS Number

Recursive member

References the CTE itself.

			
SELECT Number + 1
FROM Numbers
WHERE Number < 10

The recursion ends when no additional rows are returned.

Hierarchical Queries

Recursive CTEs are ideal for hierarchical data such as:

Organizational charts
Employee-manager relationships
Bill of materials
Folder structures
Product categories

Example:

			
WITH EmployeeHierarchy AS
(
    SELECT
        EmployeeID,
        ManagerID,
        EmployeeName
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName
    FROM HumanResources.Employee e
        INNER JOIN EmployeeHierarchy h
            ON e.ManagerID = h.EmployeeID
)
SELECT *
FROM EmployeeHierarchy;

		

Using MAXRECURSION

SQL Server limits recursion to 100 levels by default.

To override the limit:

OPTION (MAXRECURSION 500);

Unlimited recursion:

OPTION (MAXRECURSION 0);

Using unlimited recursion should be done cautiously to avoid infinite loops.

CTEs with INSERT

Example:

			
WITH LargeOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE TotalAmount > 10000
)
INSERT INTO Sales.ArchiveOrders
SELECT *
FROM LargeOrders;

		

CTEs with UPDATE

Example:

			
WITH CustomerDiscounts AS
(
    SELECT
        CustomerID,
        Discount
    FROM Sales.Customers
    WHERE Discount < 0.05
)
UPDATE CustomerDiscounts
SET Discount = 0.05;

		

The CTE provides an updateable result set because it references a single base table without disqualifying constructs.

CTEs with DELETE

Example:

			
WITH OldOrders AS
(
    SELECT *
    FROM Sales.Orders
    WHERE OrderDate < '2023-01-01'
)
DELETE
FROM OldOrders;

		

CTEs with MERGE

CTEs can simplify complex merge operations.

Example:

			
WITH UpdatedCustomers AS
(
    SELECT *
    FROM Sales.CustomerImport
)
MERGE Sales.Customers AS Target
USING UpdatedCustomers AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
    UPDATE
    SET CustomerName = Source.CustomerName
WHEN NOT MATCHED THEN
    INSERT (CustomerID, CustomerName)
    VALUES (Source.CustomerID, Source.CustomerName);

		

CTEs vs. Subqueries

Feature	CTE	Subquery
Readability	High	Moderate
Supports recursion	Yes	No
Reusable within statement	Yes	Limited
Multiple logical steps	Excellent	Difficult
Hierarchical queries	Yes	No

CTEs vs. Temporary Tables

Feature	CTE	Temporary Table
Stored physically	No	Yes (tempdb)
Exists beyond one statement	No	Yes
Supports indexes	No	Yes
Good for complex multi-step processing	Sometimes	Yes
Good for readability	Excellent	Moderate

CTEs vs. Table Variables

Feature	CTE	Table Variable
Temporary object	Logical only	Physical object in tempdb
Exists after statement	No	Yes
Supports indexes	No (directly)	Limited (via constraints/indexes in newer versions)
Recursive queries	Yes	No

Performance Considerations

Although CTEs improve readability, they do not automatically improve performance.

Consider the following:

CTEs are expanded into the execution plan by the optimizer rather than materialized by default.
Large CTEs referenced multiple times may be re-evaluated.
Recursive CTEs can become expensive for deep hierarchies.
Temporary tables may outperform CTEs for large intermediate result sets reused across multiple statements.
Proper indexing on underlying tables remains critical.

Always review the execution plan when optimizing complex queries.

CTE Limitations

Developers should understand these limitations:

Scope is limited to one statement.
Cannot be referenced by subsequent statements.
Cannot include an ORDER BY clause unless used with TOP, OFFSET/FETCH, or FOR XML.
Cannot create indexes on a CTE.
Recursive CTEs require a termination condition.
Excessive recursion can impact performance or lead to errors if recursion limits are exceeded.

AI-Enabled Database Scenarios

CTEs are frequently used in AI-enabled database solutions to prepare data before AI processing.

Examples include:

Cleaning and filtering text before embedding generation
Building hierarchical product catalogs for Retrieval-Augmented Generation (RAG)
Preparing conversation histories for prompt construction
Identifying duplicate records before vectorization
Aggregating customer interactions for AI analysis
Transforming datasets before intelligent search indexing
Organizing graph-like relationships that feed AI models

CTEs provide a readable way to express complex transformations commonly required before AI workflows.

Best Practices

Give CTEs meaningful names.
Use CTEs to simplify complex queries.
Prefer CTEs over deeply nested subqueries.
Use recursive CTEs only when recursion is required.
Always include a termination condition in recursive CTEs.
Test recursive queries with realistic datasets.
Review execution plans for large queries.
Consider temporary tables for large reusable intermediate results.
Keep CTE definitions focused on a single logical task.
Avoid excessive nesting of multiple CTEs.

Common Exam Tips

For the DP-800 exam, remember these key points:

CTEs begin with the WITH keyword.
A CTE exists only for the immediately following statement.
Recursive CTEs consist of an anchor member and a recursive member.
Recursive CTEs are commonly used for hierarchical data.
SQL Server limits recursion to 100 levels by default.
OPTION (MAXRECURSION n) changes the recursion limit.
CTEs can be used with SELECT, INSERT, UPDATE, DELETE, and MERGE.
CTEs are not stored as database objects.
CTEs improve readability but do not guarantee better performance.
Recursive CTEs must include a termination condition.

Practice Exam Questions

Question 1

Which keyword is used to define a Common Table Expression?

A. WITH

B. TEMP

C. DEFINE

D. AS

Answer: A

Explanation: Every Common Table Expression begins with the WITH keyword followed by the CTE name and query definition.

Question 2

How long does a Common Table Expression exist?

A. Until the database connection closes

B. Until the current transaction completes

C. Only for the execution of the immediately following SQL statement

D. Until it is explicitly dropped

Answer: C

Explanation: A CTE exists only for the single statement immediately following its definition.

Question 3

Which capability distinguishes a recursive CTE from a nonrecursive CTE?

A. It can reference itself.

B. It creates a permanent table.

C. It automatically creates indexes.

D. It stores intermediate results in tempdb.

Answer: A

Explanation: Recursive CTEs reference themselves to repeatedly process data until a termination condition is reached.

Question 4

Which type of data is most appropriate for a recursive CTE?

A. Monthly sales totals

B. Customer invoices

C. Product pricing

D. Organizational hierarchies

Answer: D

Explanation: Recursive CTEs are commonly used for hierarchical data such as organizational charts, bill of materials, and category trees.

Question 5

Which statement about recursive CTEs is correct?

A. They do not require an anchor member.

B. They require both an anchor member and a recursive member.

C. They can only return numeric data.

D. They cannot use UNION ALL.

Answer: B

Explanation: Every recursive CTE contains an anchor member that produces the initial rows and a recursive member that references the CTE itself.

Question 6

What is the default maximum recursion level in SQL Server?

A. 10

B. 50

C. 100

D. Unlimited

Answer: C

Explanation: SQL Server limits recursive CTE execution to 100 levels by default unless the MAXRECURSION query hint is specified.

Question 7

Which statement correctly describes CTE performance?

A. CTEs always execute faster than temporary tables.

B. CTEs are always materialized into temporary storage.

C. CTEs automatically create indexes.

D. CTEs primarily improve query readability, while performance depends on the execution plan and underlying data.

Answer: D

Explanation: CTEs improve readability and maintainability, but the query optimizer determines how they are executed. They do not inherently improve performance.

Question 8

Which DML operation can use a Common Table Expression?

A. SELECT only

B. SELECT and INSERT only

C. SELECT, INSERT, UPDATE, DELETE, and MERGE

D. UPDATE only

Answer: C

Explanation: CTEs can precede and be referenced by SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Question 9

When should a temporary table typically be preferred over a CTE?

A. When a readable single-statement query is needed

B. When recursion is required

C. When the intermediate result set must be reused across multiple statements or indexed

D. When querying hierarchical data

Answer: C

Explanation: Temporary tables persist beyond a single statement, can be indexed, and are often more efficient when intermediate results are reused multiple times.

Question 10

How are CTEs commonly used in AI-enabled database solutions?

A. They directly generate vector embeddings.

B. They replace vector indexes.

C. They eliminate the need for application logic.

D. They simplify complex data preparation tasks such as filtering, aggregating, and organizing data before embedding generation, intelligent search, or Retrieval-Augmented Generation (RAG) workflows.

Answer: D

Explanation: CTEs are commonly used to prepare and transform datasets before downstream AI processing, improving readability and maintainability of complex SQL used in AI-enabled database solutions.

Go to the DP-800 Exam Prep Hub main page

Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Create triggers (DP-800 Exam Prep)

This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create triggers

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Triggers are special types of stored procedures that automatically execute (or “fire”) in response to specific database events. Unlike stored procedures, which must be executed explicitly by a user or application, triggers are invoked automatically by SQL Server when certain Data Manipulation Language (DML), Data Definition Language (DDL), or logon events occur.

Triggers are commonly used to enforce complex business rules, maintain audit trails, synchronize related data, validate changes, and perform automated actions that occur whenever data or database objects are modified. While triggers are powerful, they should be used judiciously because they can add complexity and affect database performance if not carefully designed.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

What triggers are
DML triggers
DDL triggers
AFTER and INSTEAD OF triggers
The inserted and deleted logical tables
Creating, altering, disabling, enabling, and dropping triggers
Nested and recursive triggers
Performance considerations
Best practices
AI-enabled database scenarios

Understanding triggers is important because they provide automatic execution of business logic while helping maintain data integrity and automate administrative tasks.

What Is a Trigger?

A trigger is a database object that automatically executes when a specified event occurs.

Triggers are associated with:

Tables
Views
Databases
SQL Server instances (for certain DDL and logon events)

Triggers cannot be executed directly using the EXEC statement.

Instead, SQL Server executes them automatically when the triggering event occurs.

Types of Triggers

SQL Server supports several types of triggers:

DML triggers
DDL triggers
Logon triggers

The DP-800 exam primarily focuses on DML and DDL triggers.

DML Triggers

Data Manipulation Language (DML) triggers fire when data is modified.

They respond to:

INSERT
UPDATE
DELETE

Typical uses include:

Auditing data changes
Enforcing business rules
Validating updates
Synchronizing tables
Recording historical information

AFTER Triggers

An AFTER trigger executes only after the triggering statement completes successfully.

Example:

			
CREATE TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
    INSERT INTO Sales.CustomerAudit
    (
        CustomerID,
        AuditDate
    )
    SELECT
        CustomerID,
        GETDATE()
    FROM inserted;
END;

		

The trigger records newly inserted customers after the insert operation succeeds.

INSTEAD OF Triggers

An INSTEAD OF trigger executes in place of the triggering action.

Example:

			
CREATE TRIGGER trgPreventDelete
ON Sales.Customers
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Deleting customers is not permitted.';
END;

		

The DELETE statement never executes because the trigger replaces it.

INSTEAD OF triggers are commonly used on:

Views
Complex update scenarios
Custom validation logic

DDL Triggers

DDL triggers respond to schema changes.

Common events include:

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE PROCEDURE
ALTER PROCEDURE
DROP PROCEDURE

Example:

			
CREATE TRIGGER trgAuditDDL
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    PRINT 'A table was created.';
END;

		

DDL triggers help monitor or prevent unauthorized schema modifications.

Logon Triggers

Logon triggers execute when a user establishes a SQL Server session.

Typical uses include:

Restricting connections
Recording login activity
Enforcing security policies

Logon triggers are created at the server level and are not supported in Azure SQL Database.

The inserted Logical Table

Whenever rows are inserted or updated, SQL Server creates a temporary logical table named inserted.

It contains the new version of affected rows.

Example:

			
SELECT *
FROM inserted;

The inserted table exists only during trigger execution.

The deleted Logical Table

Whenever rows are deleted or updated, SQL Server creates a logical table named deleted.

It contains the original version of affected rows.

Example:

			
SELECT *
FROM deleted;

For UPDATE operations:

deleted contains old values.
inserted contains new values.

Auditing Changes

Triggers are frequently used to create audit trails.

Example:

			
CREATE TRIGGER trgAuditSalary
ON HumanResources.Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO HumanResources.SalaryAudit
    (
        EmployeeID,
        OldSalary,
        NewSalary,
        ChangeDate
    )
    SELECT
        d.EmployeeID,
        d.Salary,
        i.Salary,
        GETDATE()
    FROM deleted d
    INNER JOIN inserted i
        ON d.EmployeeID = i.EmployeeID;
END;

		

This trigger records salary changes for auditing purposes.

Enforcing Business Rules

Triggers can prevent invalid operations.

Example:

			
CREATE TRIGGER trgNoNegativeInventory
ON Inventory.Products
AFTER UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT *
        FROM inserted
        WHERE Quantity < 0
    )
    BEGIN
        RAISERROR
        (
            'Inventory cannot be negative.',
            16,
            1
        );
        ROLLBACK TRANSACTION;
    END;
END;

		

The trigger rolls back the transaction if inventory becomes negative.

Multi-Row Operations

Triggers execute once per SQL statement, not once per affected row.

For example:

			
UPDATE Sales.Customers
SET City = 'Miami';

If 10,000 rows are updated, the trigger executes only once.

The inserted and deleted tables contain all affected rows.

Developers should always write triggers using set-based logic, not assumptions that only one row is affected.

Nested Triggers

A trigger can cause another trigger to fire.

Example:

Trigger A updates Table B.
Table B has Trigger B.
Trigger B executes automatically.

This behavior is called nested triggers.

SQL Server supports nested triggers up to a configurable limit.

Recursive Triggers

A recursive trigger fires itself either directly or indirectly.

Example:

Trigger updates its own table.
That update causes the same trigger to execute again.

Recursive triggers are disabled by default in many environments and should be used with caution to avoid infinite loops.

Enabling and Disabling Triggers

Disable a trigger:

			
DISABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Enable it:

			
ENABLE TRIGGER trgCustomerAudit
ON Sales.Customers;

Disabling a trigger preserves its definition while preventing it from firing.

Modifying a Trigger

Use ALTER TRIGGER.

Example:

			
ALTER TRIGGER trgCustomerAudit
ON Sales.Customers
AFTER INSERT
AS
BEGIN
    PRINT 'Customer inserted.';
END;

		

Deleting a Trigger

Use:

DROP TRIGGER trgCustomerAudit;

Viewing Trigger Definitions

Developers can inspect a trigger using:

sp_helptext 'trgCustomerAudit';

Or:

			
SELECT OBJECT_DEFINITION
(
    OBJECT_ID('trgCustomerAudit')
);

Triggers vs. Stored Procedures

Feature	Trigger	Stored Procedure
Executes automatically	Yes	No
Invoked by `EXEC`	No	Yes
Responds to database events	Yes	No
Accepts parameters	No	Yes
Returns result sets	Not intended for callers	Yes

Triggers vs. Constraints

Feature	Trigger	Constraint
Enforces simple rules	Possible	Yes
Enforces complex business logic	Yes	Limited
Can reference multiple tables	Yes	Limited
Executes automatically	Yes	Yes

Constraints should generally be preferred for simple validation rules because they are simpler and often more efficient.

Performance Considerations

Triggers execute within the same transaction as the triggering statement.

Poorly designed triggers can:

Increase transaction duration
Increase locking
Reduce concurrency
Consume additional CPU resources
Introduce blocking
Increase deadlock risk

Best practices include:

Keep trigger logic simple.
Use set-based operations.
Avoid unnecessary queries.
Avoid long-running operations.
Minimize external dependencies.
Do not assume only one row is affected.

Security Considerations

Triggers can:

Audit sensitive changes
Prevent unauthorized updates
Enforce compliance policies
Record administrative activity
Restrict schema modifications using DDL triggers

Proper permissions should be applied because trigger code executes in the database context.

AI-Enabled Database Scenarios

Triggers can support AI-enabled database solutions by automating actions whenever data changes.

Examples include:

Recording changes that require new embeddings to be generated
Logging modifications to AI training datasets
Flagging rows for downstream vectorization processes
Updating AI metadata tables after inserts or updates
Capturing prompt history for auditing
Initiating workflows that prepare data for intelligent search or Retrieval-Augmented Generation (RAG)

Although triggers cannot directly invoke external AI services, they can populate work queues or status tables that downstream applications or services process.

Best Practices

Prefer constraints for simple validation.
Use triggers only when automatic behavior is required.
Write triggers using set-based logic.
Minimize execution time.
Avoid recursive logic unless absolutely necessary.
Test triggers with multi-row operations.
Document business rules implemented by triggers.
Avoid unnecessary nested trigger chains.
Monitor trigger performance.
Audit only the information that is required.

Common Exam Tips

For the DP-800 exam, remember these key points:

Triggers execute automatically in response to events.
DML triggers respond to INSERT, UPDATE, and DELETE statements.
DDL triggers respond to schema changes.
AFTER triggers execute after the triggering statement completes successfully.
INSTEAD OF triggers replace the triggering action.
inserted contains new row values.
deleted contains original row values.
Triggers fire once per statement, not once per row.
Use ALTER TRIGGER to modify a trigger.
Use DISABLE TRIGGER, ENABLE TRIGGER, and DROP TRIGGER to manage trigger lifecycle.

Practice Exam Questions

Question 1

A developer wants database logic to execute automatically whenever rows are inserted into a table. Which database object should be used?

A. Stored procedure

B. Trigger

C. View

D. Scalar function

Answer: B

Explanation: Triggers automatically execute in response to specified database events such as INSERT, UPDATE, or DELETE operations.

Question 2

Which type of trigger executes only after the triggering statement has completed successfully?

A. BEFORE trigger

B. INSTEAD OF trigger

C. AFTER trigger

D. LOGON trigger

Answer: C

Explanation: An AFTER trigger fires only after the triggering DML statement has completed successfully and any associated constraints have been processed.

Question 3

During an UPDATE operation, which logical table contains the original values of the modified rows?

A. inserted

B. updated

C. original

D. deleted

Answer: D

Explanation: During an UPDATE, the deleted logical table contains the original row values, while the inserted table contains the new values.

Question 4

Which trigger type replaces the original INSERT, UPDATE, or DELETE operation?

A. AFTER trigger

B. DDL trigger

C. INSTEAD OF trigger

D. Recursive trigger

Answer: C

Explanation: An INSTEAD OF trigger executes instead of the triggering statement, allowing custom processing or validation.

Question 5

A trigger is written assuming that only one row is updated at a time. Why is this a problem?

A. SQL Server executes one trigger for every row.

B. Triggers always execute asynchronously.

C. Triggers execute once per SQL statement and may process many affected rows.

D. UPDATE statements cannot affect multiple rows.

Answer: C

Explanation: SQL Server fires DML triggers once per statement, so developers must use set-based logic to correctly process all affected rows.

Question 6

Which statement disables a trigger while preserving its definition?

A. REMOVE TRIGGER

B. DROP TRIGGER

C. ALTER TRIGGER

D. DISABLE TRIGGER

Answer: D

Explanation: DISABLE TRIGGER prevents a trigger from firing without deleting it, allowing it to be re-enabled later.

Question 7

Which statement best describes a DDL trigger?

A. It responds to changes in table data.

B. It responds to schema modification events such as CREATE, ALTER, or DROP statements.

C. It executes only during user logins.

D. It replaces the execution of stored procedures.

Answer: B

Explanation: DDL triggers respond to schema-related events, making them useful for auditing or preventing structural database changes.

Question 8

Which object is generally preferred for enforcing a simple rule such as ensuring a value is greater than zero?

A. AFTER trigger

B. CHECK constraint

C. DDL trigger

D. Stored procedure

Answer: B

Explanation: CHECK constraints are simpler, easier to maintain, and generally more efficient than triggers for straightforward validation rules.

Question 9

Which statement correctly describes nested triggers?

A. They occur only with DDL triggers.

B. They allow a trigger to execute dynamic SQL.

C. They occur when one trigger causes another trigger to fire.

D. They are required whenever inserted and deleted tables are referenced.

Answer: C

Explanation: Nested triggers occur when the actions performed by one trigger cause another trigger to execute.

Question 10

How can triggers support AI-enabled database solutions?

A. They automatically generate embeddings by calling AI models directly.

B. They replace vector indexes.

C. They eliminate the need for application code.

D. They automatically detect data changes and populate work queues, audit tables, or status records that downstream AI processes use to generate embeddings, update indexes, or prepare RAG data.

Answer: D

Explanation: Triggers are well suited for detecting data changes and initiating downstream workflows by recording changes or updating processing queues. External applications or services can then consume these queues to perform AI-related tasks such as embedding generation or intelligent indexing.

Go to the DP-800 Exam Prep Hub main page

DP-700, Python, SQL June 3, 2026

Transform data by using PySpark, SQL, and KQL (DP-700 Exam Prep)

This post is a part of the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric Exam Prep Hub.
This topic falls under these sections:
Ingest and transform data (30–35%)
   --> Ingest and transform batch data
      --> Transform data by using PySpark, SQL, and KQL

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available from the hub's main page below the exam topics section.

Introduction

One of the most important skills for the DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric certification exam is knowing how to transform data using the appropriate technology. Microsoft Fabric provides multiple transformation engines, each optimized for specific workloads:

PySpark for large-scale distributed data engineering and advanced transformations
SQL for relational data manipulation, warehousing, and analytics
KQL (Kusto Query Language) for high-volume log, telemetry, event, and time-series data analysis

A successful Fabric Data Engineer must understand not only how each technology works, but also when to choose one over another.

Understanding the Transformation Options in Microsoft Fabric

Microsoft Fabric supports several data processing experiences:

Technology	Primary Use Case	Common Fabric Components
PySpark	Big data processing and engineering	Lakehouse, Notebooks
SQL	Relational transformations and analytics	Warehouse, SQL Endpoint
KQL	Streaming, telemetry, logs, event analytics	Eventhouse, Real-Time Intelligence

While all three can transform data, they are designed for different scenarios.

Transforming Data with PySpark

What is PySpark?

PySpark is the Python API for Apache Spark.

Spark is a distributed processing engine that allows data engineers to process extremely large datasets across multiple nodes simultaneously.

Within Microsoft Fabric, PySpark is typically used in:

Notebooks
Lakehouses
Spark Job Definitions

When to Use PySpark

PySpark is ideal when:

Working with large-scale datasets
Performing complex transformations
Processing semi-structured data
Building data engineering pipelines
Performing machine learning preparation
Handling Delta Lake tables

Examples include:

Cleaning raw data
Parsing JSON files
Aggregating billions of records
Creating dimensional model tables
Performing data quality checks

Reading Data with PySpark

Example:

df = spark.read.format("delta").load("Tables/Sales")

Filtering Data

filtered_df = df.filter(df.Amount > 1000)

Creating New Columns

			
from pyspark.sql.functions import col
new_df = df.withColumn(
    "TaxAmount",
    col("Amount") * 0.07
)

		

Aggregating Data

			
from pyspark.sql.functions import sum
summary_df = (
    df.groupBy("Region")
      .agg(sum("Amount").alias("TotalSales"))
)

		

Writing Results

summary_df.write.mode("overwrite").saveAsTable("SalesSummary")

PySpark Advantages

Scalability

Handles terabytes and petabytes of data.

Distributed Processing

Automatically parallelizes workloads.

Flexibility

Supports:

Structured data
Semi-structured data
Unstructured data

Data Engineering Focus

Excellent for ETL and ELT processes.

PySpark Limitations

More complex than SQL
Requires programming skills
Less familiar to business analysts
Higher resource consumption for small workloads

Transforming Data with SQL

What is SQL in Fabric?

SQL remains one of the most commonly used languages in Fabric.

You can use SQL within:

Fabric Data Warehouse
Lakehouse SQL Endpoint
SQL Query Editor
Stored Procedures
Data Pipelines

When to Use SQL

SQL is ideal for:

Relational transformations
Data warehouse development
Reporting datasets
Aggregations
Joins
Dimensional modeling

Examples:

Creating fact tables
Loading dimensions
Building reporting views
Data validation

Filtering Records

			
SELECT *
FROM Sales
WHERE Amount > 1000;

Aggregations

			
SELECT
    Region,
    SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region;

		

Joining Tables

			
SELECT
    s.SaleID,
    c.CustomerName
FROM Sales s
INNER JOIN Customer c
    ON s.CustomerID = c.CustomerID;

		

Creating Transformation Tables

			
CREATE TABLE SalesSummary AS
SELECT
    Region,
    SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Region;

		

SQL Advantages

Familiarity

Most data professionals know SQL.

Readability

Easy to understand and maintain.

Relational Optimization

Optimized for joins and aggregations.

Warehousing Support

Ideal for star schemas and dimensional models.

SQL Limitations

Less effective for complex data engineering workflows
Not ideal for large-scale semi-structured data processing
Limited flexibility compared to PySpark

Transforming Data with KQL

What is KQL?

Kusto Query Language (KQL) is a read-optimized query language designed for:

Telemetry
Log analytics
Event processing
Streaming data
Time-series analysis

KQL is commonly used in:

Eventhouse
Real-Time Intelligence
KQL Databases

When to Use KQL

Use KQL when working with:

Sensor data
IoT events
Application logs
Security monitoring
Streaming datasets
Time-series analytics

Examples:

Monitoring manufacturing equipment
Detecting anomalies
Security event analysis
Operational dashboards

Filtering Data

			
Events
| where Temperature > 100

Summarization

			
Events
| summarize AvgTemp = avg(Temperature)
    by DeviceID

Time-Series Analysis

			
Events
| summarize Count=count()
    by bin(Timestamp, 1h)

Detecting Trends

			
Events
| make-series AvgTemp=avg(Temperature)
    on Timestamp
    step 1h

KQL Advantages

High Performance

Optimized for large event datasets.

Time-Series Analytics

Excellent for temporal analysis.

Streaming Support

Designed for real-time workloads.

Fast Query Execution

Ideal for operational dashboards.

KQL Limitations

Not intended for traditional data warehousing
Less suitable for dimensional modeling
Not commonly used for batch ETL

Comparing PySpark, SQL, and KQL

Requirement	Best Choice
Large-scale ETL	PySpark
Data warehouse transformations	SQL
Star schema creation	SQL
Streaming analytics	KQL
Time-series analysis	KQL
Semi-structured JSON processing	PySpark
Machine learning preparation	PySpark
Business reporting datasets	SQL
Eventhouse analytics	KQL
Massive Delta Lake processing	PySpark

Choosing the Right Transformation Tool

Choose PySpark When

Processing very large datasets
Working with Data Lake data
Building engineering pipelines
Handling JSON or Parquet files
Performing advanced transformations

Choose SQL When

Building warehouses
Creating dimensional models
Developing reporting datasets
Performing relational transformations
Creating views and stored procedures

Choose KQL When

Working with event streams
Analyzing telemetry
Investigating logs
Performing time-series analysis
Monitoring operational systems

Exam Tips

Know the Primary Use Cases

A common DP-700 exam question asks which technology is most appropriate for a scenario.

Remember:

PySpark = Big Data Engineering
SQL = Relational Analytics and Warehousing
KQL = Real-Time and Time-Series Analytics

Understand Fabric Components

Know where each technology is primarily used:

Technology	Fabric Experience
PySpark	Lakehouse, Notebook
SQL	Warehouse, SQL Endpoint
KQL	Eventhouse

Focus on Scenario-Based Questions

The exam frequently describes a business requirement and asks which technology should be used.

For example:

IoT sensors → KQL
Warehouse dimension tables → SQL
Processing billions of JSON records → PySpark

Practice Exam Questions

Question 1

A data engineer must transform 20 TB of semi-structured JSON data stored in OneLake. Which technology is the best choice?

A. SQL

B. PySpark

C. KQL

D. Power Query

Answer: B

Explanation: PySpark is designed for distributed processing of massive datasets and handles semi-structured formats such as JSON efficiently.

Question 2

A Fabric solution requires creation of a star schema consisting of fact and dimension tables. Which technology is most appropriate?

A. SQL

B. KQL

C. Power BI DAX

D. Data Activator

Answer: A

Explanation: SQL is optimized for relational transformations and dimensional modeling commonly used in data warehouses.

Question 3

A company wants to analyze millions of IoT events arriving continuously from factory equipment. Which technology should be used?

A. KQL

B. Power Query

C. SQL

D. Excel

Answer: A

Explanation: KQL is designed specifically for high-volume event, telemetry, and time-series analysis workloads.

Question 4

Which Fabric component is most closely associated with KQL transformations?

A. Warehouse

B. Notebook

C. SQL Endpoint

D. Eventhouse

Answer: D

Explanation: Eventhouse is the primary Fabric experience for KQL-based analytics and real-time intelligence workloads.

Question 5

A data engineer needs to process Delta Lake tables using distributed compute. Which technology should be selected?

A. KQL

B. SQL

C. PySpark

D. Power BI

Answer: C

Explanation: PySpark integrates directly with Delta Lake and supports scalable distributed processing.

Question 6

Which language is specifically optimized for time-series analysis?

A. SQL

B. KQL

C. Python

D. DAX

Answer: B

Explanation: KQL includes built-in capabilities for temporal aggregation, anomaly detection, and time-series analytics.

Question 7

A Fabric Warehouse team needs to build a reusable transformation layer consisting of joins, aggregations, and views. Which technology should they use?

A. SQL

B. KQL

C. Dataflows Gen2

D. Spark ML

Answer: A

Explanation: SQL is the preferred language for relational transformations and warehouse development.

Question 8

Which technology is generally the best choice for preparing large datasets for machine learning?

A. KQL

B. SQL

C. DAX

D. PySpark

Answer: D

Explanation: PySpark provides scalable data preparation capabilities and integrates well with machine learning workflows.

Question 9

An engineer needs to summarize application log events by hour and identify usage trends. Which technology is most appropriate?

A. PySpark

B. Power Query

C. KQL

D. SQL

Answer: C

Explanation: KQL excels at log analytics, event monitoring, and time-based aggregations.

Question 10

A team needs a transformation language that is familiar to most database developers and optimized for relational joins. Which should they choose?

A. PySpark

B. KQL

C. Power Query

D. SQL

Answer: D

Explanation: SQL remains the standard language for relational querying, joins, aggregations, and warehouse transformations.

Go to the DP-700 Exam Prep Hub main page.

Data Development, Data Education & Training, Data Modeling, Databases, DP-900, SQL, Uncategorized May 10, 2026

Identify common Structured Query Language (SQL) statements (DP-900 Exam Prep)

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

Go to the Practice Exam Questions for this topic.

Go to the Additional Practice Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

DP-900, Microsoft Certification, SQL May 10, 2026

Additional Practice Questions: Identify common Structured Query Language (SQL) statements – SQL JOIN Focused (DP-900 Exam Prep)

Practice Questions – SQL JOIN focused questions

Question 1

What is the purpose of a SQL JOIN?

A. To delete duplicate rows
B. To combine data from multiple tables
C. To sort query results
D. To filter columns

✅ Answer: B

Explanation:
JOIN is used to combine rows from two or more related tables.

Question 2

Which type of JOIN returns only matching rows from both tables?

A. LEFT JOIN
B. RIGHT JOIN
C. INNER JOIN
D. CROSS JOIN

✅ Answer: C

Explanation:
INNER JOIN returns only rows where there is a match in both tables.

Question 3

A LEFT JOIN returns:

A. Only matching rows
B. All rows from the right table only
C. All rows from the left table and matching rows from the right
D. Only non-matching rows

✅ Answer: C

Explanation:
LEFT JOIN keeps all rows from the left table, even if there is no match.

Question 4

What happens when there is no matching row in a RIGHT JOIN?

A. The row is removed
B. NULL values are returned for missing matches
C. The query fails
D. Only matched rows are shown

✅ Answer: B

Explanation:
Unmatched columns return NULL values.

Question 5

Which JOIN type returns all possible combinations of rows between two tables?

A. INNER JOIN
B. LEFT JOIN
C. CROSS JOIN
D. FULL JOIN

✅ Answer: C

Explanation:
CROSS JOIN produces a Cartesian product (all combinations).

Question 6

Which SQL clause is used to define how tables are related in a JOIN?

A. WHERE
B. GROUP BY
C. ON
D. ORDER BY

✅ Answer: C

Explanation:
The ON clause specifies the relationship between tables.

Question 7

Given two tables: Customers and Orders. Each customer may have multiple orders. Which JOIN is typically used to retrieve all customers and their orders?

A. INNER JOIN
B. LEFT JOIN
C. CROSS JOIN
D. SELF JOIN

✅ Answer: B

Explanation:
LEFT JOIN ensures all customers appear, even those without orders.

Question 8

What does an INNER JOIN exclude?

A. Duplicate rows
B. Non-matching rows
C. NULL values only
D. Primary keys

✅ Answer: B

Explanation:
INNER JOIN only returns rows with matching values in both tables.

Question 9

Which JOIN is MOST likely to return fewer rows than the original tables?

A. CROSS JOIN
B. INNER JOIN
C. LEFT JOIN
D. FULL OUTER JOIN

✅ Answer: B

Explanation:
INNER JOIN returns only matches, often reducing row count.

Question 10

Which statement best describes a FULL OUTER JOIN?

A. Returns only matching rows
B. Returns all rows from both tables, matching where possible
C. Returns only left table rows
D. Returns only right table rows

✅ Answer: B

Explanation:
FULL OUTER JOIN returns all rows from both tables, with NULLs where no match exists.

✅ Quick Exam Takeaways

For DP-900 JOINs, remember:

✔ JOIN = combine related tables
✔ INNER JOIN = only matches
✔ LEFT JOIN = all left + matches
✔ RIGHT JOIN = all right + matches
✔ CROSS JOIN = all combinations
✔ ON clause defines relationships
✔ Unmatched values become NULL

Go to the DP-900 Exam Prep Hub main page.

Data Development, DP-900, Microsoft Certification, SQL May 10, 2026

Practice Questions: Identify common Structured Query Language (SQL) statements (DP-900 Exam Prep)

Practice Questions

Question 1

Which SQL statement is used to retrieve data from a database?

A. INSERT
B. SELECT
C. UPDATE
D. DELETE

✅ Answer: B

Explanation:
The SELECT statement is used to query and retrieve data from tables.

Question 2

Which SQL statement adds new rows to a table?

A. INSERT
B. CREATE
C. ALTER
D. SELECT

✅ Answer: A

Explanation:
INSERT is used to add new records to a table.

Question 3

Which SQL statement modifies existing data in a table?

A. UPDATE
B. DELETE
C. SELECT
D. DROP

✅ Answer: A

Explanation:
UPDATE changes existing values in one or more rows.

Question 4

Which SQL statement removes rows from a table?

A. DROP
B. DELETE
C. ALTER
D. TRUNCATE

✅ Answer: B

Explanation:
DELETE removes specific rows based on a condition.

Question 5

Which SQL statement creates a new table?

A. ALTER
B. CREATE
C. INSERT
D. SELECT

✅ Answer: B

Explanation:
CREATE is used to define new database objects such as tables.

Question 6

Which clause is used to filter rows in a SQL query?

A. ORDER BY
B. GROUP BY
C. WHERE
D. HAVING

✅ Answer: C

Explanation:
WHERE filters rows based on conditions.

Question 7

Which SQL clause is used to sort query results?

A. ORDER BY
B. GROUP BY
C. WHERE
D. JOIN

✅ Answer: A

Explanation:
ORDER BY sorts results in ascending or descending order.

Question 8

Which SQL statement permanently removes a table and its structure?

A. DELETE
B. DROP
C. REMOVE
D. CLEAR

✅ Answer: B

Explanation:
DROP deletes the table and its structure completely.

Question 9

Which SQL operation is used to combine data from two related tables?

A. GROUP BY
B. JOIN
C. UNION
D. FILTER

✅ Answer: B

Explanation:
JOIN combines rows from multiple tables based on related columns.

Question 10

Which category of SQL statements is used to define or modify database structures?

A. DML
B. DQL
C. DDL
D. DCL

✅ Answer: C

Explanation:
DDL (Data Definition Language) includes CREATE, ALTER, and DROP.

✅ Quick Exam Takeaways

For DP-900, remember:

✔ SELECT → retrieve data
✔ INSERT → add data
✔ UPDATE → modify data
✔ DELETE → remove data
✔ CREATE / ALTER / DROP → manage structure
✔ WHERE → filter results
✔ ORDER BY → sort results
✔ JOIN → combine tables
✔ SQL categories: DDL, DML, DQL

Go to the DP-900 Exam Prep Hub main page.

Data Modeling, Databases, DP-900, Microsoft Certification, SQL May 10, 2026

Identify features of relational data (DP-900 Exam Prep)

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

✔ Common Azure services:

Azure SQL Database
Azure Database for PostgreSQL
Azure Database for MySQL

Go to the Practice Exam Questions for this topic.

Go to the DP-900 Exam Prep Hub main page.

AI, AI Strategy, Analytics, Artificial Intelligence (AI), Cloud computing, Computer Vision, Data Analysis, Data Careers, Data Education & Training, Data News, Data Science, Data Strategy, Data Visualization, Deep Learning, Generative AI, Large Language Models (LLMs), Machine Learning (ML), Natural Language Processing (NLP), Power BI, Power Query, Predictive Analytics, Python, SQL December 29, 2025December 29, 2025

AI Career Options for Early-Career Professionals and New Graduates

Artificial Intelligence is shaping nearly every industry, but breaking into AI right out of college can feel overwhelming. The good news is that you don’t need a PhD or years of experience to start a successful AI-related career. Many AI roles are designed specifically for early-career talent, blending technical skills with problem-solving, communication, and business understanding.

This article outlines excellent AI career options for people just entering the workforce, explaining what each role involves, why it’s a strong choice, and how to prepare with the right skills, tools, and learning resources.

1. AI / Machine Learning Engineer (Junior)

What It Is & What It Involves

Machine Learning Engineers build, train, test, and deploy machine learning models. Junior roles typically focus on:

Implementing existing models
Cleaning and preparing data
Running experiments
Supporting senior engineers

Why It’s a Good Option

High demand and strong salary growth
Clear career progression
Central role in AI development

Skills & Preparation Needed

Technical Skills

Python
SQL
Basic statistics & linear algebra
Machine learning fundamentals
Libraries: scikit-learn, TensorFlow, PyTorch

Where to Learn

Coursera (Andrew Ng ML specialization)
Fast.ai
Kaggle projects
University CS or data science coursework

Difficulty Level: ⭐⭐⭐⭐ (Moderate–High)

2. Data Analyst (AI-Enabled)

What It Is & What It Involves

Data Analysts use AI tools to analyze data, generate insights, and support decision-making. Tasks often include:

Data cleaning and visualization
Dashboard creation
Using AI tools to speed up analysis
Communicating insights to stakeholders

Why It’s a Good Option

Very accessible for new graduates
Excellent entry point into AI
Builds strong business and technical foundations

Skills & Preparation Needed

Technical Skills

SQL
Excel
Python (optional but helpful)
Power BI / Tableau
AI tools (ChatGPT, Copilot, AutoML)

Where to Learn

Microsoft Learn
Google Data Analytics Certificate
Kaggle datasets
Internships and entry-level analyst roles

Difficulty Level: ⭐⭐ (Low–Moderate)

3. Prompt Engineer / AI Specialist (Entry Level)

What It Is & What It Involves

Prompt Engineers design, test, and optimize instructions for AI systems to get reliable and accurate outputs. Entry-level roles focus on:

Writing prompts
Testing AI behavior
Improving outputs for business use cases
Supporting AI adoption across teams

Why It’s a Good Option

Low technical barrier
High demand across industries
Great for strong communicators and problem-solvers

Skills & Preparation Needed

Key Skills

Clear writing and communication
Understanding how LLMs work
Logical thinking
Domain knowledge (marketing, analytics, HR, etc.)

Where to Learn

OpenAI documentation
Prompt engineering guides
Hands-on practice with ChatGPT, Claude, Gemini
Real-world experimentation

Difficulty Level: ⭐⭐ (Low–Moderate)

4. AI Product Analyst / Associate Product Manager

What It Is & What It Involves

This role sits between business, engineering, and AI teams. Responsibilities include:

Defining AI features
Translating business needs into AI solutions
Analyzing product performance
Working with data and AI engineers

Why It’s a Good Option

Strong career growth
Less coding than engineering roles
Excellent mix of strategy and technology

Skills & Preparation Needed

Key Skills

Basic AI/ML concepts
Data analysis
Product thinking
Communication and stakeholder management

Where to Learn

Product management bootcamps
AI fundamentals courses
Internships or associate PM roles
Case studies and product simulations

Difficulty Level: ⭐⭐⭐ (Moderate)

5. AI Research Assistant / Junior Data Scientist

What It Is & What It Involves

These roles support AI research and experimentation, often in academic, healthcare, or enterprise environments. Tasks include:

Running experiments
Analyzing model performance
Data exploration
Writing reports and documentation

Why It’s a Good Option

Strong foundation for advanced AI careers
Exposure to real-world research
Great for analytical thinkers

Skills & Preparation Needed

Technical Skills

Python or R
Statistics and probability
Data visualization
ML basics

Where to Learn

University coursework
Research internships
Kaggle competitions
Online ML/statistics courses

Difficulty Level: ⭐⭐⭐⭐ (Moderate–High)

6. AI Operations (AIOps) / ML Operations (MLOps) Associate

What It Is & What It Involves

AIOps/MLOps professionals help deploy, monitor, and maintain AI systems. Entry-level work includes:

Model monitoring
Data pipeline support
Automation
Documentation

Why It’s a Good Option

Growing demand as AI systems scale
Strong alignment with data engineering
Less math-heavy than research roles

Skills & Preparation Needed

Technical Skills

Python
SQL
Cloud basics (Azure, AWS, GCP)
CI/CD concepts
ML lifecycle understanding

Where to Learn

Cloud provider learning paths
MLOps tutorials
GitHub projects
Entry-level data engineering roles

Difficulty Level: ⭐⭐⭐ (Moderate)

7. AI Consultant / AI Business Analyst (Entry Level)

What It Is & What It Involves

AI consultants help organizations understand and implement AI solutions. Entry-level roles focus on:

Use-case analysis
AI tool evaluation
Process improvement
Client communication

Why It’s a Good Option

Exposure to multiple industries
Strong soft-skill development
Fast career progression

Skills & Preparation Needed

Key Skills

Business analysis
AI fundamentals
Presentation and communication
Problem-solving

Where to Learn

Business analytics programs
AI fundamentals courses
Consulting internships
Case study practice

Difficulty Level: ⭐⭐⭐ (Moderate)

8. AI Content & Automation Specialist

What It Is & What It Involves

This role focuses on using AI to automate content, workflows, and internal processes. Tasks include:

Building automations
Creating AI-generated content
Managing tools like Zapier, Notion AI, Copilot

Why It’s a Good Option

Very accessible for non-technical graduates
High demand in marketing and operations
Rapid skill acquisition

Skills & Preparation Needed

Key Skills

Workflow automation
AI tools usage
Creativity and organization
Basic scripting (optional)

Where to Learn

Zapier and Make tutorials
Hands-on projects
YouTube and online courses
Real business use cases

Difficulty Level: ⭐⭐ (Low–Moderate)

How New Graduates Should Prepare for AI Careers

1. Build Foundations

Python or SQL
Data literacy
AI concepts (not just tools)

2. Practice with Real Projects

Personal projects
Internships
Freelance or volunteer work
Kaggle or GitHub portfolios

3. Learn AI Tools Early

ChatGPT, Copilot, Gemini
AutoML platforms
Visualization and automation tools

4. Focus on Communication

AI careers, and careers in general, reward those who can explain complex ideas simply.

Final Thoughts

AI careers are no longer limited to researchers or elite engineers. For early-career professionals, the best path is often a hybrid role that combines AI tools, data, and business understanding. Starting in these roles builds confidence, experience, and optionality—allowing you to grow into more specialized AI positions over time.
And the advice that many professionals give for gaining knowledge and breaking into the space is to “get your hands dirty”.

Good luck on your data journey!

Analytics, Artificial Intelligence (AI), Business Intelligence, Business Intelligence (BI) Development, Data Analysis, Data Cleaning, Data Development, Data Governance, Data Integration, Data Integration (ETL), Data Modeling, Data Security, Data Strategy, Data Visualization, Data Warehousing, Data Wrangling, Databases, DP-600, Microsoft Certification, Microsoft Fabric, Microsoft OneLake, Performance Tuning, Power BI, Power Query, Python, SQL December 28, 2025April 25, 2026

Exam Prep Hub for DP-600: Implementing Analytics Solutions Using Microsoft Fabric

This is your one-stop hub with information for preparing for the DP-600: Implementing Analytics Solutions Using Microsoft Fabric certification exam. Upon successful completion of the exam, you earn the Fabric Analytics Engineer Associate certification.

This hub provides information directly here, links to a number of external resources, tips for preparing for the exam, practice tests, and section questions to help you prepare. Bookmark this page and use it as a guide to ensure that you are fully covering all relevant topics for the exam and using as many of the resources available as possible. We hope you find it convenient and helpful.

Why do the DP-600: Implementing Analytics Solutions Using Microsoft Fabric exam to gain the Fabric Analytics Engineer Associate certification?

Most likely, you already know why you want to earn this certification, but in case you are seeking information on its benefits, here are a few:
(1) there is a possibility for career advancement because Microsoft Fabric is a leading data platform used by companies of all sizes, all over the world, and is likely to become even more popular
(2) greater job opportunities due to the edge provided by the certification
(3) higher earnings potential,
(4) you will expand your knowledge about the Fabric platform by going beyond what you would normally do on the job and
(5) it will provide immediate credibility about your knowledge, and
(6) it may, and it should, provide you with greater confidence about your knowledge and skills.

Important DP-600 resources:

In the section below this one, titled “DP-600: Skills measured as of October 31, 2025“, you will find the “skills measured” topics from the official study guide with links to exam preparation content for each topic. Bookmark this page and use that section as a structured topic-by-topic guide for your prep.
Link to the Microsoft Fabric Analytics Engineer Associate Certification page
Link to the Microsoft DP-600 study guide page.
- This page provides information for preparing for, practicing for, and registering for the exam. The skills measured content in the guide is also what is used to form the “Skills Measured as of …” outline below.
About the exam:
- Cost: US $165
- Number of questions: approximately 60
- Time to do exam: 120 minutes (2 hours)
To Do’s:
- Schedule time to learn, study, perform labs, and do practice exams and questions
- Schedule the exam based on when you think you will be ready; scheduling the exam gives you a target and drives you to keep working on it
- Use the various resources above and below to learn
- Take the free Microsoft Learn practice test, any other available practice tests, and do the practice questions in each section and the two practice tests available in this hub.
Link to the free, comprehensive, self-paced course: Microsoft Learn course for a Microsoft Fabric Analytics Engineer. It contains 4 Learning Paths, each with multiple Modules, and each module has multiple Units. It will take some time to do it, but we recommend that you complete this entire course, including the exercises/labs. To help you work through your preparation in a structured manner, we will point you to the relevant sections in the training material corresponding to each of the sections in the skills measured section below.
YouTube videos that you will find useful:
- DP-600 Exam Full Course (6+ hours) | Microsoft Fabric Analytics Engineer by Learn Microsoft Fabric with Will
- Learn the Fundamentals of Microsoft Fabric in 38 minutes by Learn Microsoft Fabric with Will
- Microsoft Analytics Fabric Engineer course by Microsoft Learn
- How To Prepare for the DP-600 Microsoft Fabric Certification Exam [Full Course] by Pragmatic Works
- How to pass Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric by Microsoft Power BI
- DP-600 | Microsoft Fabric Analytics Engineer Exam | 109 Practice Questions With Explanation by Learn With Priyanka
- What is Microsoft Fabric? by Pragmatic Works
- Learn Together: Get started with end-to-end analytics and lakehouses in Microsoft Fabric by Microsoft Power BI
- Learn Together: Get started with data warehouses in Microsoft Fabric by Microsoft Power BI
  - Note: There are quite a few “Learn Together” videos about Fabric. Check out as many as you can.
Additional Microsoft links:
- https://aka.ms/GetCertified/dp600
- https://aka.ms/IamReady/DP600Prepare
Microsoft Fabric Community Blog
Microsoft Community Blog post you might find useful. It is titled “Step-by-Step-Strategy-to-Ace-the-Microsoft-Fabric-Analytics“
Microsoft Fabric Career Hub – includes information for (1) Data Engineer and (2) Analytics Engineer
Reddit DP-600 Mega Thread
Books you might be interested in:
- Exam Ref DP-600 Implementing Analytics Solutions Using Microsoft Fabric
- Implementing Analytics Solutions Using Microsoft Fabric—DP-600 Exam Study Guide: Boost your skills with expert insights and certification-ready strategies for Microsoft analytics
Courses you might be interested in:
- Udemy: Microsoft DP-600 prep: Fabric Analytics Engineer Associate
  - Note: There are multiple, highly rated DP-600 courses available on Udemy
  - Tip: await the occasional Udemy sale to buy
- Coursera: Exam Prep DP-600: Microsoft Fabric Analytics Engineer

DP-600: Skills measured as of October 31, 2025:

Here you can learn in a structured manner by going through the topics of the exam one-by-one to ensure full coverage; click on each hyperlinked topic below to go to more information about it:

Skills at a glance

Maintain a data analytics solution (25%-30%)
Prepare data (45%-50%)
Implement and manage semantic models (25%-30%)

Maintain a data analytics solution (25%-30%)

Implement security and governance

Maintain the analytics development lifecycle

Prepare data (45%-50%)

Get Data

Transform Data

Query and analyze data

Implement and manage semantic models (25%-30%)

Design and build semantic models

Optimize enterprise-scale semantic models

Practice Exams:

We have provided 2 practice exams with answers to help you prepare.

DP-600 Practice Exam 1 (60 questions with answer key)

DP-600 Practice Exam 2 (60 questions with answer key)

Good luck to you passing the DP-600: Implementing Analytics Solutions Using Microsoft Fabric certification exam and earning the Fabric Analytics Engineer Associate certification!

Analytics, BI Administration, Big Data, Business Intelligence (BI) Development, Data Analysis, Data Development, Data Governance, Data Integration, Data Integration (ETL), Data Modeling, Data Strategy, Data Visualization, Data Warehousing, DP-600, Microsoft Certification, Microsoft Fabric, Performance Tuning, Power BI, Power Query, Reporting, SQL December 28, 2025January 5, 2026

Implement Performance Improvements in Queries and Report Visuals (DP-600 Exam Prep)

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: 
Implement and manage semantic models (25-30%) 
    --> Optimize enterprise-scale semantic models 
        --> Implement performance improvements in queries and report visuals

Performance optimization is a critical skill for the Fabric Analytics Engineer. In enterprise-scale semantic models, poor query design, inefficient DAX, or overly complex visuals can significantly degrade report responsiveness and user experience. This exam section focuses on identifying performance bottlenecks and applying best practices to improve query execution, model efficiency, and report rendering.

1. Understand Where Performance Issues Occur

Performance problems typically fall into three layers:

a. Data & Storage Layer

Storage mode (Import, DirectQuery, Direct Lake, Composite)
Data source latency
Table size and cardinality
Partitioning and refresh strategies

b. Semantic Model & Query Layer

DAX calculation complexity
Relationships and filter propagation
Aggregation design
Use of calculation groups and measures

c. Report & Visual Layer

Number and type of visuals
Cross-filtering behavior
Visual-level queries
Use of slicers and filters

DP-600 questions often test your ability to identify the correct layer where optimization is needed.

2. Optimize Queries and Semantic Model Performance

a. Choose the Appropriate Storage Mode

Use Import for small-to-medium datasets requiring fast interactivity
Use Direct Lake for large OneLake Delta tables with high concurrency
Use Composite models to balance performance and real-time access
Avoid unnecessary DirectQuery when Import or Direct Lake is feasible

b. Reduce Data Volume

Remove unused columns and tables
Reduce column cardinality (e.g., avoid high-cardinality text columns)
Prefer surrogate keys over natural keys
Disable Auto Date/Time when not needed

c. Optimize Relationships

Use single-direction relationships by default
Avoid unnecessary bidirectional filters
Ensure relationships follow a star schema
Avoid many-to-many relationships unless required

d. Use Aggregations

Create aggregation tables to pre-summarize large fact tables
Enable query hits against aggregation tables before scanning detailed data
Especially valuable in composite models

3. Improve DAX Query Performance

a. Write Efficient DAX

Prefer measures over calculated columns
Use variables (VAR) to avoid repeated calculations
Minimize row context where possible
Avoid excessive iterators (SUMX, FILTER) over large tables

b. Use Filter Context Efficiently

Prefer CALCULATE with simple filters
Avoid complex nested FILTER expressions
Use KEEPFILTERS and REMOVEFILTERS intentionally

c. Avoid Expensive Patterns

Avoid EARLIER in favor of variables
Avoid dynamic table generation inside visuals
Minimize use of ALL when ALLSELECTED or scoped filters suffice

4. Optimize Report Visual Performance

a. Reduce Visual Complexity

Limit the number of visuals per page
Avoid visuals that generate multiple queries (e.g., complex custom visuals)
Use summary visuals instead of detailed tables where possible

b. Control Interactions

Disable unnecessary visual interactions
Avoid excessive cross-highlighting
Use report-level filters instead of visual-level filters when possible

c. Optimize Slicers

Avoid slicers on high-cardinality columns
Use dropdown slicers instead of list slicers
Limit the number of slicers on a page

d. Prefer Measures Over Visual Calculations

Avoid implicit measures created by dragging numeric columns
Define explicit measures in the semantic model
Reuse measures across visuals to improve cache efficiency

5. Use Performance Analysis Tools

a. Performance Analyzer

Identify slow visuals
Measure DAX query duration
Distinguish between query time and visual rendering time

b. Query Diagnostics (Power BI Desktop)

Analyze backend query behavior
Identify expensive DirectQuery or Direct Lake operations

c. DAX Studio (Advanced)

Analyze query plans
Measure storage engine vs formula engine time
Identify inefficient DAX patterns

(You won’t be tested on tool UI details, but knowing when and why to use them is exam-relevant.)

6. Common DP-600 Exam Scenarios

You may be asked to:

Identify why a report is slow and choose the best optimization
Identify the bottleneck layer (model, query, or visual)
Select the most appropriate storage mode for performance
Choose the least disruptive, most effective optimization
Improve a slow DAX measure
Reduce visual rendering time without changing the data source
Optimize performance for enterprise-scale models
Apply enterprise-scale best practices, not just quick fixes

Key Exam Takeaways

Always optimize the model first, visuals second
Star schema + clean relationships = better performance
Efficient DAX matters more than clever DAX
Fewer visuals and interactions = faster reports
Aggregations and Direct Lake are key enterprise-scale tools

Practice Questions:

Go to the Practice Exam Questions for this topic.