Query and analyze data

Objective coverage

• Select, filter, and aggregate data by using the Visual Query Editor
• Select, filter, and aggregate data by using SQL
• Select, filter, and aggregate data by using KQL
• Select, filter, and aggregate data by using DAX

One question, several execution models

SQL, KQL, DAX, and a visual query editor can all select, filter, and aggregate data, but they do not execute in the same context. SQL queries relational tables and views. KQL expresses a sequence of operations over tabular event-oriented data. DAX queries a semantic model whose relationships, measures, and filter context contribute meaning. A visual editor presents supported transformations graphically and may generate SQL behind the interface. Translation requires preserving the business question, not mechanically replacing keywords.

Use this fixture throughout the chapter. West has two completed sales with amounts 40 and 15 and one cancelled sale with amount 100. East has one completed sale of 30. The completed-sales total is 85; West completed sales are 55; East completed sales are 30. There are four source rows but three completed sales. All amounts share one currency. If your query returns 185, it included the cancelled row. If it returns 85 for every region, the region filter or relationship is not doing what you intended.

Select, filter, and aggregate data by using the Visual Query Editor

A visual query editor lets an analyst build supported query steps without writing every SQL statement manually. The graphical steps still represent relational operations with types, joins, grouping keys, and population rules. A drag-and-drop join can multiply rows just as a handwritten join can. Treat the generated query and results as inspectable artifacts. The editor's convenience does not remove the need to understand the target warehouse or SQL analytics endpoint and the permissions used to query it.

Open the supported query experience for the target item and select the necessary tables. Keep only relevant columns, filter the intended population, and define joins with explicit keys and join kinds. Group by the required attributes and choose the appropriate aggregate. Inspect intermediate results and generated SQL where available. Validate the final result against a small known fixture, then save the query through the supported workflow. Confirm that the saved artifact and its dependencies are accessible to the intended users without broadening source permissions unnecessarily.

Build a query over Sales that keeps Region, Status, and Amount. Filter Status to Completed, group by Region, and sum Amount. The result should contain West 55 and East 30. Next add a Product lookup that has two rows for one product. Observe whether the join changes the number of contributing sales before grouping. If West becomes larger than 55, inspect match cardinality. A visually plausible diagram is not proof of a correct model; the fixture makes the semantic error visible.

If a required transformation is unsupported in the editor, use a supported SQL or preparation workflow rather than pretending the visual tool has every capability. Preview limits can also hide rare nulls or duplicate keys, so validate full-result controls where appropriate. When a step fails, inspect input types, source permissions, renamed columns, and generated expressions. For exam distinctions, a visual query defines a query or result shape; it does not automatically create a governed semantic model, enforce RLS, or materialize a scheduled data pipeline.

Scenario: A visual query returns a higher sales total after a lookup merge. The aggregation step is still Sum. Where should you investigate?

Answer: Inspect the merge keys and lookup uniqueness, then compare row counts immediately before and after the join. Correct the relationship or reduce the lookup to the intended grain. Changing Sum to Average would change the business question instead of fixing the duplicate contribution.

Recall: What should be checked in generated SQL? Why can a small preview be insufficient evidence for full-data correctness?

Select, filter, and aggregate data by using SQL

SQL describes a relational result. FROM and JOIN establish the input relation, WHERE filters rows, GROUP BY defines result groups, HAVING filters groups, and SELECT projects expressions. This logical explanation is useful for reasoning even though the optimizer can choose a different physical execution plan. Null uses three-valued logic: comparisons can be unknown rather than true or false. COUNT(*) counts rows, COUNT(column) excludes null values, and distinct counts address entity identity rather than raw row count.

Write the output grain and expected columns first. Identify the base population and join cardinalities, then add row predicates and grouping. Use explicit types and qualified column names where ambiguity is possible. Check null rules and date boundaries. Execute against a known fixture and reconcile the grand total to source controls. Inspect the actual query behavior and performance tools supported by the endpoint. Parameterize operational queries through supported mechanisms and avoid constructing executable SQL from untrusted input.

The fixture can be expressed as a self-contained read-only query:

WITH Sales AS (
    SELECT 'West' AS Region, 'Completed' AS Status, 40 AS Amount
    UNION ALL SELECT 'West', 'Completed', 15
    UNION ALL SELECT 'West', 'Cancelled', 100
    UNION ALL SELECT 'East', 'Completed', 30
)
SELECT Region, SUM(Amount) AS CompletedRevenue,
       COUNT(*) AS CompletedRows
FROM Sales
WHERE Status = 'Completed'
GROUP BY Region;

Expected rows are West, 55, 2 and East, 30, 1. Adding HAVING SUM(Amount) > 40 keeps West only, after both groups have been calculated. Replacing WHERE with that HAVING condition would not exclude cancelled rows and would answer a different question. A result without ORDER BY has no promised presentation order; compare keyed values rather than relying on whichever row appears first.

Debug joins before measures when totals inflate. Debug population predicates when rows disappear. A left join followed by WHERE RightTable.Flag = 1 excludes unmatched rows because their right-side value is null. If unmatched rows must remain, put the intended condition in the join or express the null-inclusive business rule carefully. Avoid applying DISTINCT as a universal repair: it can erase legitimate duplicates while leaving grain errors unexplained. Fabric's current T-SQL support must be checked for specialized functions and DDL; ordinary relational reasoning does not imply complete SQL Server feature parity.

Scenario: A report needs regions whose completed sales exceed 40. A query filters Amount > 40 before grouping and returns no West sales. What is wrong?

Answer: The predicate was applied to individual rows, but the requirement concerns each region's aggregate. Filter Status to Completed in WHERE, group by Region, and apply HAVING SUM(Amount) > 40. West's 40 and 15 combine to 55 even though neither individual row exceeds 40.

Recall: How do COUNT(*) and COUNT(Amount) differ when Amount is null? Why should an ordered display use ORDER BY explicitly?

Select, filter, and aggregate data by using KQL

Kusto Query Language composes tabular operations through a pipeline. Each operator receives a table and produces another table. where filters rows, project selects or derives columns, extend adds expressions, and summarize groups and aggregates. Time-series analysis also depends on timestamp meaning and bin boundaries. KQL is especially useful for event exploration, but it still requires clear entity identity and duplicate policy. Counting events is not necessarily counting orders or devices.

Identify the source table, its timestamp column, and whether the task concerns event time or ingestion time. Filter the relevant time and business population early when semantics allow. Project the columns needed downstream, then summarize at the requested grain. Use explicit bins for time windows and verify boundary behavior. Choose exact versus approximate distinct-count functions according to the requirement and current support. Validate with a small datatable fixture, then examine representative production-shaped performance with appropriate authorization.

A read-only KQL fixture makes the expected result visible:

let Sales = datatable(Region:string, Status:string, Amount:long)
[
    "West", "Completed", 40,
    "West", "Completed", 15,
    "West", "Cancelled", 100,
    "East", "Completed", 30
];
Sales
| where Status == "Completed"
| summarize CompletedRevenue = sum(Amount), CompletedRows = count() by Region
| order by Region asc

The totals match the SQL fixture because the business population and grouping match. For device events, a query such as summarize Events=count() by bin(EventTime, 5m), DeviceId creates five-minute device groups. It does not deduplicate repeated event deliveries or substitute ingestion time for event time. Those choices must be expressed separately if the requirement needs them.

String operators have distinct semantics. Equality, case-insensitive matching, term search, and substring search are not interchangeable. Select the operator that matches the data contract before tuning it. A join can also have default behavior that differs from a SQL learner's expectation, so specify the required join kind when cardinality matters. If counts disagree, examine repeated events, nulls, time-zone assumptions, late arrivals, and grouping boundaries. In exam questions, KQL pipeline order and event-window semantics often distinguish a correct result from a superficially similar query.

Scenario: A five-minute event chart shows a lower count than a raw ingestion counter after delayed messages arrive. Which timestamps and rules should be compared?

Answer: Compare whether the chart groups by event time while the counter records ingestion time, then inspect late-arrival and duplicate handling. A delayed event may belong to an earlier chart bucket. Reconcile the same event identities over the same defined interval before concluding that data was lost.

Recall: How do project and extend differ in the columns they retain? Why is count of events different from distinct count of devices?

Select, filter, and aggregate data by using DAX

DAX evaluates expressions against a semantic model. A measure's result depends on filter context from visuals, slicers, relationships, security, and explicit filter modifications. Row context is different: it identifies a current row during a calculated-column expression or iterator. CALCULATE evaluates an expression in modified filter context and can perform context transition. Understanding these contexts is more important than memorizing isolated function names, because the same measure can correctly return different values for a region row and a grand total.

Check model relationships and base measures before writing a complex expression. Define the desired population and whether existing filters should be replaced, preserved, or removed. Use explicit measures for governed calculations. Test a single region, multiple regions, no selection, an empty selection, and the grand total. For query work, use EVALUATE with a table expression in a supported DAX query tool. Keep model queries distinct from measure definitions: a measure expression alone is not a complete DAX query.

Assume a Sales table with the fixture columns and a valid Region dimension relationship where used. These model measures express the business rule:

Revenue = SUM ( Sales[Amount] )

Completed Revenue =
CALCULATE (
    [Revenue],
    KEEPFILTERS ( Sales[Status] = "Completed" )
)

KEEPFILTERS makes the Completed condition intersect an existing filter on Status rather than replacing it. A visual filtered to Cancelled therefore has no completed revenue instead of unexpectedly showing completed rows. To inspect totals with a DAX query against this model:

EVALUATE
SUMMARIZECOLUMNS (
    Sales[Region],
    "CompletedRevenue", [Completed Revenue]
)

Expect West 55 and East 30. A correctly connected Region dimension can be used instead of the fact column. If every region displays 85, inspect the relationship and any filter-removal expression before rewriting the aggregation.

A total is a new evaluation under the total's filter context, not necessarily the arithmetic sum of displayed cells. This matters for distinct counts, ratios, and measures that intentionally change context. Variables capture values in the context where they are evaluated; moving a variable outside a context-changing expression can change its meaning. For troubleshooting, simplify the measure into intermediate results and compare row-level and total contexts. The exam distinction is between stored row attributes, dynamic measures, and table-returning DAX queries, with security remaining part of the model's evaluation environment.

Scenario: A regional matrix repeats the all-region total in every row. The base SUM is correct when queried without the matrix. What should you investigate?

Answer: Check the Region relationship, its active state and filter direction, and whether the measure removes Region filters. Test the base measure with a directly filtered fact column to separate relationship propagation from arithmetic. Do not divide the repeated total by the number of displayed regions.

Recall: Why can a grand-total ratio differ from the sum of row ratios? How does KEEPFILTERS change the interaction with an existing filter on the same column?

Query workshop: preserve meaning during translation

Begin with a plain-language requirement: “For each region, report completed revenue during July, preserving sales whose product description is missing.” Four design commitments follow. The date window must be defined; Status must be filtered to Completed; Region is the output grouping; and the product lookup must not remove unmatched sales. A query in any language that violates one of these commitments is wrong even if it runs quickly and returns plausible numbers.

In SQL, a left join can preserve sales while exposing null product descriptions. In a visual editor, the merge kind and any later filters must preserve the same population. In KQL, explicitly choose the matching join behavior and inspect the result before summarizing. In DAX, the model relationships and blank-member behavior contribute to the result, so validate how unmatched fact keys appear. Translation is therefore a test of semantics across engines, not a line-by-line syntax substitution exercise.

Add a return of 5 in West and decide whether it is represented as a negative completed sale or as a separate return event. If the business definition says completed net revenue subtracts it, the expected West total becomes 50 and the combined total becomes 80. A query that simply counts status strings may need a revised definition. A semantic model might encapsulate the rule in a measure, while the warehouse prepares a curated net-amount column. Both can be defensible if the ownership and timing of the calculation are explicit.

Now add a second currency. Summing 50 dollars and 30 euros is not a meaningful consolidated revenue result without a currency conversion contract. The required rate might be transaction-date rate, monthly accounting rate, or another approved rule. A faster SUM does not solve this semantic gap. On an exam, a query question may supply enough assumptions to use a simple aggregate; in implementation, write down the assumptions rather than importing them silently from a simplified example.

A useful query review records five artifacts: the business question, the row grain, the small input fixture, the expected result, and the executed query. The fixture should include at least one case that would expose a likely mistake. A cancelled row exposes a missing status filter. A duplicate lookup exposes a grain expansion. A boundary timestamp exposes interval overlap. A null amount exposes count and null assumptions. The point is to make a wrong implementation fail visibly, not to collect a large number of nondiscriminating sample rows.

Worked interpretation: totals that should not add

Consider two regions with overlapping customers. West has customers C1 and C2; East has C2 and C3. Each region has two distinct customers, while the overall distinct customer count is three. Adding the displayed row counts gives four, which double-counts C2. A DAX grand total of three can therefore be correct even when a user expects the sum of visible rows. SQL or KQL must use the same entity-level definition to produce a comparable result.

For a ratio, West has revenue 100 and profit 10, while East has revenue 20 and profit 10. Regional margin percentages are 10% and 50%. The consolidated margin is total profit 20 divided by total revenue 120, approximately 16.67%. The unweighted average of 10% and 50% is 30%, and their sum is 60%; neither is the consolidated margin. Preserve numerator and denominator as reusable measures, then divide in the desired context.

For inventory, suppose an item has 10 units on Monday and 12 on Tuesday. Summing the two daily balances yields 22 unit-days only if that is deliberately the question; it is not Tuesday's stock on hand. A snapshot measure may select the last available date or another business-approved rule. This is why query correctness begins with measurement semantics. The database engine cannot infer whether a numeric column is a flow, a balance, a ratio, or an identifier.

Active investigation: three misleading successes

First, a query returns without error. That proves the engine accepted the request and produced a result under the current execution conditions. It does not prove that the join keys were correct. Compare the fixture and control totals.

Second, two queries return the same grand total. That is stronger evidence, but both could misallocate amounts between regions. Compare keyed group results and negative cases as well as the total. A pair of offsetting mistakes can hide under a correct sum.

Third, a query is faster after a filter is moved earlier. Confirm that the move preserved semantics. Filtering event rows before choosing the latest event can differ from choosing the latest event and then filtering its status. For an order that was Completed yesterday and Cancelled today, those two operation orders can produce opposite answers. Specify whether the question asks for orders that ever completed or orders whose current status is completed.

Shared Fabric foundations

The following field notes adapt this project’s original DP-700 explanations for the shared Fabric foundation. They provide supporting engineering context for the DP-600 objectives above; they do not add separate measured objectives.

Transform data by using PySpark, SQL, and KQL

These examples all aggregate valid sales by region, but they execute in different engines:

from pyspark.sql import functions as F

result = (
    sales.filter(F.col("Amount").isNotNull())
    .groupBy("Region")
    .agg(F.sum("Amount").alias("Revenue"))
)

SELECT Region, SUM(Amount) AS Revenue
FROM dbo.Sales
WHERE Amount IS NOT NULL
GROUP BY Region;

Sales
| where isnotnull(Amount)
| summarize Revenue = sum(Amount) by Region

PySpark favors distributed file/table processing, T-SQL relational warehouse workloads, and KQL event/time-series exploration. Equivalent syntax does not imply identical null, type, optimizer, or consistency behavior.

Language translation clinic

PySpark, T-SQL, and KQL share relational ideas but differ in type systems, null semantics, physical execution, and durable write behavior. A sound procedure is filter/select early, make types explicit, validate keys, transform, write to the engine-native target, and reconcile counts/totals. PySpark can distribute file/Delta work and expose Spark plans; T-SQL uses relational plans, constraints, and transactions in a warehouse; KQL excels at time-bounded event pipelines and dynamic values. Avoid collecting large Spark data to the driver, unbounded KQL scans, or row-by-row SQL operations.

Equivalent join and aggregate: the PySpark shape is:

orders.join(customers, "CustomerId", "left").groupBy("Region").agg(sum("Amount"))

In T-SQL use a LEFT JOIN plus GROUP BY; in KQL use join kind=leftouter then summarize. Before declaring equivalence, decide how unmatched keys, duplicate customer rows, null regions, decimal precision, and event-time bounds behave. Mini-lab: run a six-row fixture containing each edge case in all three engines and compare the ordered normalized result, not just that each query completed.

Optimize query performance

Across SQL, KQL, and Spark SQL, the durable principles are similar:

• reduce rows and columns early;
• use predicates that enable pruning and indexes/statistics where supported;
• avoid repeated parsing, conversion, and computation;
• join at compatible grain and reduce before joining;
• preaggregate/materialize repeated expensive results when freshness permits;
• inspect the engine's actual plan and runtime metrics.

Optimization must preserve correctness. Compare row counts, totals, null behavior, and representative results after a rewrite. Report both latency and capacity/resource change so a faster query that costs far more is visible as a trade-off.

Cross-engine query optimization field guide

The common model is scan → filter/project → join → aggregate/sort → return or write. Reduce data as early as semantics permit, use engine-prunable predicates, make join keys compatible, prevent many-to-many explosion, and calculate expensive repeated results once. But verify the actual engine plan: SQL statistics and relational operations, KQL extent/time pruning and operator pipeline, and Spark partition/file pruning plus shuffle have different evidence.

Compare both cold and representative warm-cache conditions, several runs, concurrency, result size, and capacity cost. A query can be faster because the result changed, cache warmed, data volume shrank, or capacity was quieter. Use a fixed correctness fixture plus production-scale measurement. Validate row count, key uniqueness, null behavior, decimal/time-zone semantics, and totals before accepting a rewrite.

Worked comparison. SQL WHERE OrderDate >= @start, KQL where EventTime >= start, and Spark .filter(col("OrderDate") >= start) can all reduce scans only when types, storage organization, and optimizer/source pushdown support it. Wrapping the stored date in string conversion may prevent pruning. Preaggregate a large fact by join key before joining to a small classification when only group totals are needed—but never if detail-level matching changes meaning.

If a rewrite shows no gain, check plan equivalence, predicate selectivity, storage/partitioning, statistics, cache, result transfer, and shared resource noise. If it is faster but CU/resource cost rises sharply, state the trade-off and decide against the SLA/cost objective. Mini-lab: build a result checksum and metrics table, then optimize one SQL, KQL, and Spark query using the same filter/project/join principles and engine-specific evidence.

OPTIMIZE compacts Delta files, V-Order changes file layout, partitioning enables pruning, and VACUUM removes obsolete files under retention rules. Copy parallelism differs from loop concurrency. Eventhouse retention differs from hot cache. Spark repartition differs from coalesce, and broadcast is a join strategy, not “send output everywhere.” A faster run is not proven optimization until correctness, comparable conditions, and resource cost are included.

Recall. Which metrics prove a small-file problem? Why can 12 concurrent copies be slower than four? What plan evidence distinguishes warehouse scan from queueing? When does a materialized view move cost rather than remove it? Why does one long Spark task suggest skew? How can conversion prevent pruning? For practice, write an A/B optimization record with hypothesis, fixed conditions, three baseline and three changed runs, correctness checksum, p50/p95, bytes scanned, and CU/compute result.

Sources and provenance

The measured objectives follow the DP-600 blueprint effective July 21, 2026. Product references were retrieved for this edition; the book manifest records the actual retrieval time and SHA-256 of each downloaded HTML response. Examples and scenarios are original teaching material. Shared field guides reuse this project’s original DP-700 writing where the underlying Fabric concepts apply. Microsoft Learn remains authoritative, especially for current feature support and capacity limits.

• Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
• Query Using the Visual Query Editor - Microsoft Fabric
• T-SQL Surface Area in Fabric Data Warehouse - Microsoft Fabric
• Tutorial: Learn Common Kusto Query Language Operators - Kusto
• DAX overview - DAX
• CALCULATE function (DAX) - DAX
• Comparing Copy Job, Mirroring, Copy Activity, and Eventstreams in Microsoft Fabric - Microsoft Fabric
• Load Tables in a Dimensional Model - Microsoft Fabric
• Differences between Dataflow Gen1 and Dataflow Gen2 - Microsoft Fabric
• Unify Data Sources With OneLake Shortcuts - Microsoft Fabric
• Mirroring - Microsoft Fabric
• Lakehouse and Delta Tables - Microsoft Fabric
• Optimize Delta Lake tables with V-Order in Fabric - Microsoft Fabric
• Performance Guidelines - Microsoft Fabric
• Best practices for Kusto Query Language queries - Kusto
• Spark errors overview in Microsoft Fabric - Microsoft Fabric