Memory hook: Same question, different execution and context.
Must remember
SQL works with relational rowsets: WHERE filters input rows, GROUP BY forms groups and HAVING filters grouped results. A left join followed by a right-table predicate in WHERE can unintentionally eliminate unmatched rows. Count records and distinguish COUNT(*) from counting a nullable expression.
KQL uses a tabular pipeline. Start from the correct table, apply a selective time filter, project needed columns and summarize at the required grain. For example, Events | where Timestamp > ago(1d) | summarize Total=count() by bin(Timestamp, 1h) counts events by hour. The pipe passes a result to the next operator; it is not a shell command.
DAX queries operate over a semantic model and its relationships. EVALUATE returns a table expression; SUMMARIZECOLUMNS can group dimensions and evaluate measures. Existing filter context and measure logic affect the result. A model measure may disagree with raw SQL because its business definition, relationships, security or date role differs.
The Visual query editor builds supported SQL queries interactively. Inspect generated operations and validate the same joins, filters and aggregation semantics you would in hand-written SQL. A graphical canvas does not eliminate cardinality mistakes.
Investigate mismatched totals in a fixed sequence: source snapshot/freshness, permissions, row grain, joins, filters, data types, then calculations. Avoid changing the formula until you know which layer introduced the discrepancy. Test one small known dataset so each expected row and total can be explained.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Filter aggregated SQL groups | HAVING. |
| Count events per hour | KQL summarize with a time bin. |
| Query a governed model measure | DAX with the intended filter context. |
Traps
- SQL row filters and group filters occur at different logical stages.
- The same metric name does not prove identical definitions across engines.
Active recall
1. WHERE versus HAVING?
Filter source rows versus filter grouped results.
2. What does KQL project do?
Select or compute output columns.
3. What does EVALUATE return?
The result of a DAX table expression.
4. Why can left-join rows disappear?
A later predicate may reject the null values of unmatched right-side rows.
5. First check when totals differ?
Verify the same data freshness, identity, grain and filters before rewriting calculations.