BenchMark Quizzes by MDFT Pro

Apply Windowing Functions to Lakehouse Data

You work for MDFT Pro, a well-known training agency that offers certification preparation courses worldwide. Claire, a Data Analyst at MDFT Pro, is planning to process three different datasets in Microsoft Fabric. The third dataset (Dataset3) is stored in a lakehouse and contains time-series data tracking student course progress over several months. The data includes timestamps for each learning activity, course completions, and assessment scores. Claire needs to perform bulk loading and transformation of this data, applying row-based windowing functions to calculate running totals, moving averages of student performance, and ranking students within their cohorts. The transformation requires functions like ROW_NUMBER(), RANK(), and aggregations over specific time windows (previous 7 days, previous 30 days). The solution should use built-in functionality to minimize development effort while providing optimal performance for analytical queries on large volumes of time-series data.

The datasets have the following characteristics:

Dataset1: Requires a unique auto-incrementing integer primary key (starting at 1, incrementing by 1)
Dataset2: Semi-structured data requiring bulk transfer with custom visualizations for development
Dataset3: Located in a lakehouse, requires row-based windowing functions during bulk loading

What should Claire use to process Dataset3?

Choose the correct answer from the options below.

Explanations for each answer:

A T-SQL statement is incorrect. While T-SQL supports windowing functions like ROW_NUMBER() and PARTITION BY, it's primarily used for querying data warehouses in Fabric. For lakehouse data with bulk loading scenarios, T-SQL is less optimal than KQL for high-performance analytical queries.
A Dataflow Gen2 dataflow is incorrect. Dataflow Gen2 uses Power Query M language for transformations and doesn't provide built-in support for advanced row-based windowing functions. It's designed for ETL operations rather than analytical queries with complex windowing logic.
A Notebook is incorrect. While notebooks support windowing functions through PySpark SQL, they require more development effort compared to using KQL's built-in windowing capabilities. KQL querysets provide more optimized performance for analytical queries on lakehouse data.
A KQL Queryset is correct. KQL querysets are ideal for Dataset3 because they provide built-in window functions for row-based operations, can efficiently query lakehouse data through OneLake shortcuts using the external_table() function, and are optimized for high-performance analytical queries with bulk data. KQL offers native support for complex windowing operations with minimal development effort.

Learn more about KQL window functions:

KQL Window Functions

Practice Exam

Apply Windowing Functions to Lakehouse Data

Discuss this question on social media: