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
      --> Group and aggregate data

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

Grouping and aggregating data are among the most common and important data transformation operations performed by data engineers. Organizations rarely analyze raw transactional data directly. Instead, they summarize, categorize, and aggregate data to create meaningful business metrics such as total sales, average order value, monthly revenue, customer counts, inventory levels, and operational KPIs.

In Microsoft Fabric, grouping and aggregation can be performed using several technologies, including:

• SQL in Fabric Data Warehouses and Lakehouses
• PySpark notebooks
• Dataflows Gen2
• KQL (Kusto Query Language)
• Data pipelines as part of larger ETL/ELT processes

For the DP-700 exam, you should understand:

• Why grouping and aggregation are important
• When to aggregate data
• How to implement aggregations using SQL, PySpark, KQL, and Dataflows Gen2
• Common aggregation functions
• Performance considerations
• Aggregations in dimensional modeling and analytics solutions

Why Group and Aggregate Data?

Raw data often contains millions or billions of records.

For example:

Business users usually want summarized information such as:

This transformation is accomplished through grouping and aggregation.

Benefits include:

• Faster analytics
• Reduced storage requirements
• Easier reporting
• Improved dashboard performance
• Simplified business intelligence models

Understanding Grouping

Grouping combines records that share common values.

Examples:

Group by:

• Customer
• Product
• Region
• Department
• Date
• Month
• Year

Example:

Grouped by Region:

The GROUP BY operation creates logical categories before aggregation calculations occur.

Common Aggregation Functions

Data engineers should be familiar with the most common aggregation functions.

SUM()

Calculates totals.

Example:

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

Result:

COUNT()

Counts rows.

SELECT Region,
       COUNT(*) AS OrderCount
FROM Sales
GROUP BY Region;

Used for:

• Number of customers
• Number of transactions
• Number of products

AVG()

Calculates averages.

SELECT ProductCategory,
       AVG(SalesAmount) AS AverageSale
FROM Sales
GROUP BY ProductCategory;

Used for:

• Average order value
• Average response time
• Average inventory level

MIN()

Returns the smallest value.

SELECT MIN(OrderDate)
FROM Orders;

Used for:

• Earliest order
• Lowest temperature
• Minimum cost

MAX()

Returns the largest value.

SELECT MAX(OrderDate)
FROM Orders;

Used for:

• Latest transaction
• Highest sales amount
• Maximum inventory quantity

Grouping and Aggregation Using SQL

SQL is the most common approach for aggregation in Fabric.

Example:

SELECT
    ProductCategory,
    SUM(SalesAmount) AS TotalSales,
    COUNT(*) AS OrderCount,
    AVG(SalesAmount) AS AverageSale
FROM Sales
GROUP BY ProductCategory;

This query:

1. Groups records by category
2. Calculates total sales
3. Counts orders
4. Calculates average sales

Multi-Column Grouping

You can group by multiple columns.

Example:

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

Result:

This provides more granular analysis.

Filtering Aggregated Results with HAVING

WHERE filters rows before aggregation.

HAVING filters after aggregation.

Example:

SELECT Region,
       SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY Region
HAVING SUM(SalesAmount) > 1000000;

Only regions exceeding $1 million in sales are returned.

Aggregation Using PySpark

PySpark is commonly used for large-scale aggregation operations in Lakehouses.

Example:

from pyspark.sql.functions import sum
sales_df.groupBy("Region") \
        .agg(sum("SalesAmount").alias("TotalSales")) \
        .show()

Result:

Multiple Aggregations in PySpark

from pyspark.sql.functions import sum, avg, count
sales_df.groupBy("Region").agg(
    sum("SalesAmount").alias("TotalSales"),
    avg("SalesAmount").alias("AvgSales"),
    count("*").alias("OrderCount")
)

This performs several calculations in a single operation.

Aggregation Using KQL

KQL is commonly used in Real-Time Intelligence workloads.

Example:

Sales
| summarize TotalSales=sum(SalesAmount)
    by Region

Result:

Multiple Aggregations in KQL

Sales
| summarize
    TotalSales=sum(SalesAmount),
    AvgSales=avg(SalesAmount),
    OrderCount=count()
    by Region

This pattern is common in real-time analytics.

Aggregation in Dataflows Gen2

Dataflows Gen2 provides a low-code interface.

Using the Group By transformation, users can:

• Sum values
• Count rows
• Calculate averages
• Find minimum values
• Find maximum values

Typical steps:

1. Connect to source
2. Select Group By
3. Choose grouping columns
4. Define aggregation functions
5. Load results

This approach is useful for citizen developers and low-code ETL scenarios.

Aggregation in Dimensional Models

Aggregations are commonly used before loading data into fact and dimension tables.

Example:

Raw transactions:

Aggregated customer sales:

This summary table can support reporting and dashboards.

Fact Table Aggregations

Fact tables often store:

• Transaction-level facts
• Daily summaries
• Monthly summaries

Examples:

Transaction Fact

Daily Aggregate Fact

Aggregated fact tables improve query performance.

Window Aggregations vs Group Aggregations

Data engineers should understand the difference.

Group Aggregation

Returns one row per group.

SELECT Region,
       SUM(SalesAmount)
FROM Sales
GROUP BY Region;

Window Aggregation

Preserves detail rows.

SELECT
    OrderID,
    Region,
    SalesAmount,
    SUM(SalesAmount)
        OVER(PARTITION BY Region)
        AS RegionTotal
FROM Sales;

Useful for:

• Running totals
• Rankings
• Percentages
• Advanced analytics

Performance Considerations

Grouping and aggregation can be expensive.

Best practices include:

Filter Early

Reduce data before aggregation.

WHERE OrderDate >= '2026-01-01'

Aggregate Close to the Source

Avoid moving unnecessary detailed records.

Use Partitioning

Partitioning helps Spark process data efficiently.

Examples:

• Date
• Region
• Customer segment

Use Delta Tables

Delta tables improve performance through:

• Data skipping
• File optimization
• Efficient query execution

Avoid Excessive Cardinality

Grouping on highly unique columns can reduce efficiency.

Bad example:

GROUP BY TransactionID

Good example:

GROUP BY Region

DP-700 Exam Tips

Remember the following:

• GROUP BY creates logical groups before aggregation.
• HAVING filters aggregated results.
• SQL uses GROUP BY.
• PySpark uses groupBy() and agg().
• KQL uses summarize.
• Dataflows Gen2 provides Group By transformations.
• Aggregated fact tables improve reporting performance.
• Window functions preserve detailed rows while performing calculations.
• Aggregations are frequently used when preparing dimensional models.
• Filtering before aggregation improves performance.

Practice Exam Questions

Question 1

A data engineer needs to calculate total sales by region in a Fabric Warehouse.

Which SQL function should be used?

A. AVG()

B. COUNT()

C. SUM()

D. MAX()

Correct Answer: C

Explanation: SUM() calculates the total of numeric values. AVG() calculates averages, COUNT() counts rows, and MAX() returns the largest value.

Question 2

A Fabric notebook must calculate the number of orders per customer.

Which aggregation function should be used?

A. COUNT()

B. AVG()

C. MIN()

D. MAX()

Correct Answer: A

Explanation: COUNT() returns the number of rows in each group, making it ideal for counting orders.

Question 3

You need to remove regions with total sales less than $500,000 after aggregation.

Which SQL clause should you use?

A. ORDER BY

B. WHERE

C. DISTINCT

D. HAVING

Correct Answer: D

Explanation: HAVING filters aggregated results after the GROUP BY operation is completed.

Question 4

Which KQL operator is primarily used for aggregation?

A. project

B. summarize

C. extend

D. join

Correct Answer: B

Explanation: The summarize operator performs grouping and aggregation in KQL.

Question 5

A Fabric Dataflow Gen2 developer wants to calculate average sales by product category.

Which transformation should be used?

A. Merge

B. Append

C. Group By

D. Split Column

Correct Answer: C

Explanation: The Group By transformation supports aggregation operations such as averages, sums, counts, minimums, and maximums.

Question 6

What is the primary purpose of a GROUP BY clause?

A. Sort rows

B. Remove duplicates

C. Filter rows

D. Create logical groups for aggregation

Correct Answer: D

Explanation: GROUP BY organizes rows into groups before aggregate calculations are performed.

Question 7

Which PySpark operation performs grouping before aggregation?

A. select()

B. filter()

C. groupBy()

D. orderBy()

Correct Answer: C

Explanation: groupBy() defines the grouping columns that will be used by aggregation functions.

Question 8

Which scenario is most appropriate for a window aggregation?

A. Total sales by region only

B. Average salary by department only

C. Customer counts by state only

D. Display each transaction along with the total sales for its region

Correct Answer: D

Explanation: Window functions preserve detail rows while calculating aggregates across a defined partition.

Question 9

A data engineer groups a dataset by TransactionID, where every TransactionID is unique.

What is the likely result?

A. Improved aggregation performance

B. Reduced cardinality

C. Limited performance benefits because each group contains one row

D. Automatic partition optimization

Correct Answer: C

Explanation: Grouping by a highly unique column creates many groups and often provides little analytical value.

Question 10

When preparing data for a dimensional model, why are aggregated tables often created?

A. To increase data duplication

B. To improve reporting and query performance

C. To eliminate dimension tables

D. To replace fact tables entirely

Correct Answer: B

Explanation: Pre-aggregated tables reduce the amount of data that must be processed during reporting, improving performance and user experience.

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

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
      --> Choose an appropriate data store

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 responsibilities of a Microsoft Fabric Data Engineer is selecting the appropriate data store for a given workload. The choice of data store directly affects performance, scalability, maintainability, security, data modeling approaches, and overall solution cost.

Microsoft Fabric provides multiple storage and analytics options, each optimized for specific use cases. The DP-700 exam expects candidates to understand when and why to use each storage option, especially in batch ingestion and transformation scenarios.

A common exam theme is evaluating business requirements and selecting the most appropriate storage technology among:

• OneLake
• Lakehouse
• Data Warehouse
• KQL Database
• Eventhouse
• Mirrored Databases
• Delta Tables
• Shortcuts
• External Storage Sources

Understanding the strengths and limitations of each option is critical for success on the exam.

Understanding Data Stores in Microsoft Fabric

A data store is a repository where data is persisted and made available for processing, analytics, reporting, or machine learning.

Selecting a data store depends on factors such as:

• Data structure
• Data volume
• Query patterns
• Processing requirements
• Latency requirements
• User skill sets
• Governance needs

A poor storage choice can result in:

• Slow query performance
• Increased complexity
• Higher costs
• Difficult maintenance

OneLake: The Foundation of Fabric

What is OneLake?

OneLake is the unified logical data lake for Microsoft Fabric.

It serves as:

• A single storage layer
• A centralized data repository
• A common storage foundation for all Fabric workloads

Key characteristics:

• Tenant-wide storage
• Based on Azure Data Lake Storage Gen2 architecture
• Supports open formats
• Eliminates data silos

Benefits

• Single copy of data
• Unified governance
• Centralized security
• Reduced duplication

Exam Tip

OneLake itself is not typically selected as the analytics store. Instead, workloads such as Lakehouses and Warehouses are built on top of OneLake.

Lakehouse

What is a Lakehouse?

A Lakehouse combines features of:

• Data lakes
• Data warehouses

Lakehouses support:

• Structured data
• Semi-structured data
• Unstructured data

while maintaining:

• Open file formats
• Delta Lake support
• Spark processing

When to Choose a Lakehouse

Choose a Lakehouse when:

• Data engineering workloads dominate
• Spark processing is required
• Data science workloads are expected
• Large volumes of raw data must be stored
• Data arrives in multiple formats

Examples:

• IoT data
• Clickstream data
• Log files
• Data science projects

Lakehouse Advantages

Open Format Storage

Uses Delta Parquet files.

Spark Integration

Optimized for notebooks and Spark jobs.

Data Science Friendly

Supports machine learning workflows.

Multi-format Data

Handles structured and unstructured data.

Lakehouse Limitations

• Less familiar to traditional SQL developers
• Some reporting scenarios may perform better in a warehouse

Data Warehouse

What is a Fabric Warehouse?

A Fabric Warehouse is a fully managed relational analytics platform optimized for SQL workloads.

Characteristics:

• Relational tables
• T-SQL support
• ACID transactions
• Optimized query engine

When to Choose a Warehouse

Choose a Warehouse when:

• Business users primarily use SQL
• Dimensional models are required
• Reporting workloads dominate
• Data is highly structured

Examples:

• Enterprise reporting
• Financial reporting
• Sales analytics
• Executive dashboards

Warehouse Advantages

Familiar SQL Experience

Supports T-SQL.

Strong BI Integration

Works seamlessly with reporting tools.

Relational Modeling

Supports star and snowflake schemas.

High Query Performance

Optimized for analytical queries.

Warehouse Limitations

• Less flexible for unstructured data
• Not ideal for large-scale machine learning workloads

Lakehouse vs Warehouse

Delta Tables

What Are Delta Tables?

Delta tables are transactional storage structures built on Delta Lake technology.

Benefits include:

• ACID compliance
• Schema enforcement
• Schema evolution
• Time travel
• Reliable batch processing

When to Use Delta Tables

Use Delta tables when:

• Reliable batch ingestion is required
• Incremental loads are needed
• Historical versions must be maintained
• Data quality controls are important

Exam Tip

Most Lakehouse production data should be stored as Delta tables.

KQL Database

What is a KQL Database?

KQL databases are optimized for:

• Log analytics
• Telemetry
• Time-series analysis
• Streaming workloads

They use:

• Kusto Query Language (KQL)

instead of SQL.

When to Choose KQL Databases

Choose KQL databases for:

• Application logs
• Monitoring data
• Telemetry
• Operational analytics

Examples:

• Server monitoring
• Manufacturing sensors
• Website activity tracking

Advantages

• Extremely fast ingestion
• High-performance time-series analysis
• Optimized aggregations

Limitations

• Not designed for dimensional modeling
• Less suitable for traditional reporting

Eventhouse

What is Eventhouse?

Eventhouse is a Real-Time Intelligence component designed for large-scale event analytics.

Eventhouse supports:

• Streaming ingestion
• KQL analytics
• High-volume telemetry

When to Use Eventhouse

Choose Eventhouse when:

• Massive event volumes exist
• Real-time analytics are required
• Streaming data dominates

Mirrored Databases

What Are Mirrored Databases?

Mirroring replicates operational database data into Fabric.

Supported sources include:

• Azure SQL Database
• Azure SQL Managed Instance
• SQL Server (supported scenarios)
• Other supported databases

When to Choose Mirroring

Choose mirroring when:

• Near real-time synchronization is required
• Minimal ETL is desired
• Operational systems must remain the source of truth

Advantages

• Reduced data movement
• Simplified architecture
• Near real-time availability

Shortcuts

What Are Shortcuts?

Shortcuts provide virtual access to data stored elsewhere.

The data remains in its original location.

Examples:

• Azure Data Lake Storage Gen2
• Amazon S3
• Other OneLake locations

When to Use Shortcuts

Choose shortcuts when:

• Data duplication must be avoided
• Multiple teams share datasets
• Existing storage investments should be preserved

Benefits

• No copying
• Reduced storage costs
• Single source of truth

Choosing Based on Workload Type

Data Engineering Workloads

Recommended Store:

✔ Lakehouse

Reason:

• Spark-native
• Flexible storage
• Delta support

Business Intelligence Workloads

Recommended Store:

✔ Warehouse

Reason:

• SQL optimized
• Dimensional modeling
• Reporting performance

Data Science Workloads

Recommended Store:

✔ Lakehouse

Reason:

• ML integration
• Notebook support
• Large-scale data processing

Streaming Analytics

Recommended Store:

✔ KQL Database
✔ Eventhouse

Reason:

• Low latency
• High ingestion rates

Operational Data Replication

Recommended Store:

✔ Mirrored Database

Reason:

• Simplified synchronization

Common DP-700 Scenario Questions

Scenario 1

A company wants to store structured and unstructured files while supporting Spark notebooks.

Best choice:

Lakehouse

Scenario 2

A finance department needs highly optimized SQL reporting.

Best choice:

Warehouse

Scenario 3

An operations team analyzes billions of telemetry records daily.

Best choice:

KQL Database or Eventhouse

Scenario 4

A company wants to use data already stored in ADLS Gen2 without copying it.

Best choice:

Shortcut

Scenario 5

A data science team requires Delta tables and machine learning workflows.

Best choice:

Lakehouse

Decision Framework

Ask the following questions:

Is the workload primarily SQL analytics?

→ Warehouse

Is Spark required?

→ Lakehouse

Is machine learning required?

→ Lakehouse

Is the data mostly telemetry or logs?

→ KQL Database

Is the data streaming continuously?

→ Eventhouse or KQL Database

Must external data remain in place?

→ Shortcut

Must operational databases replicate into Fabric?

→ Mirrored Database

Best Practices

Store Curated Data as Delta Tables

Provides reliability and transactional consistency.

Avoid Unnecessary Copies

Use shortcuts when appropriate.

Match Store to Workload

Do not force all workloads into one store.

Separate Raw and Curated Layers

Use Medallion Architecture:

• Bronze
• Silver
• Gold

Use Warehouses for Reporting

Leverage optimized SQL engines.

Use Lakehouses for Engineering

Take advantage of Spark and Delta Lake.

DP-700 Exam Focus Areas

You should understand:

✓ OneLake architecture

✓ Lakehouse capabilities

✓ Warehouse capabilities

✓ Delta tables

✓ KQL databases

✓ Eventhouse

✓ Mirrored databases

✓ Shortcuts

✓ SQL vs Spark workloads

✓ Reporting vs data science workloads

✓ Structured vs semi-structured data

✓ Batch vs streaming storage choices

✓ Common architecture decision scenarios

Practice Exam Questions

Question 1

A company needs to store structured, semi-structured, and unstructured data while enabling Spark notebook processing. Which data store should be selected?

A. KQL Database

B. Warehouse

C. Lakehouse

D. Eventhouse

Answer: C

Explanation

Lakehouses support multiple data formats and integrate natively with Spark, making them ideal for data engineering and data science workloads.

Question 2

Which Fabric data store is optimized for traditional SQL-based analytical reporting?

A. Eventhouse

B. Shortcut

C. Lakehouse

D. Warehouse

Answer: D

Explanation

Warehouses are designed for structured relational analytics and provide strong T-SQL support.

Question 3

A company collects billions of sensor readings each day and requires fast time-series analysis.

Which data store is most appropriate?

A. Warehouse

B. Shortcut

C. KQL Database

D. Mirrored Database

Answer: C

Explanation

KQL databases are optimized for telemetry, logs, and time-series analytics.

Question 4

You need to provide access to data stored in Azure Data Lake Storage Gen2 without copying the files into Fabric.

What should you use?

A. Warehouse

B. Mirrored Database

C. Delta Table

D. Shortcut

Answer: D

Explanation

Shortcuts provide virtual access to external data sources while avoiding duplication.

Question 5

Which feature is provided by Delta tables?

A. Automatic dashboard creation

B. ACID transaction support

C. Real-time alerting

D. Event routing

Answer: B

Explanation

Delta tables provide ACID transactions, schema enforcement, and reliable data processing.

Question 6

A business intelligence team primarily writes T-SQL queries and builds dimensional models.

Which storage option should they use?

A. Eventhouse

B. KQL Database

C. Warehouse

D. Lakehouse

Answer: C

Explanation

Warehouses are optimized for SQL analytics and dimensional modeling.

Question 7

A company wants near real-time replication of operational Azure SQL Database data into Fabric.

Which solution should be selected?

A. Mirrored Database

B. Eventhouse

C. Warehouse

D. Delta Table

Answer: A

Explanation

Mirrored databases synchronize source database changes into Fabric with minimal ETL effort.

Question 8

Which Fabric storage option is generally the best choice for machine learning workloads?

A. Warehouse

B. Lakehouse

C. KQL Database

D. Eventhouse

Answer: B

Explanation

Lakehouses support Spark, notebooks, Delta tables, and machine learning workflows.

Question 9

What is the primary purpose of OneLake?

A. Replacing all Lakehouses

B. Serving as Fabric’s unified storage foundation

C. Providing only streaming analytics

D. Managing deployment pipelines

Answer: B

Explanation

OneLake is the centralized storage layer that underpins all Fabric workloads.

Question 10

A solution requires high-volume real-time event analytics with streaming ingestion.

Which Fabric component is most appropriate?

A. Warehouse

B. Mirrored Database

C. Delta Table

D. Eventhouse

Answer: D

Explanation

Eventhouse is designed specifically for large-scale streaming analytics and real-time intelligence scenarios.

Exam Tip

For DP-700, many questions can be answered by remembering this mapping:

A common exam pattern is to present multiple technically possible options. Focus on selecting the option that is optimized for the workload, not merely one that could work.

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