Master the DP-600

An interactive study guide built on 7 memory techniques to help you pass the Microsoft Fabric Analytics Engineer Associate exam.

Maintain 25-30%Prepare 45-50%Semantic 25-30%

What it covers

Semantic models, DAX, Direct Lake, query folding, security (RLS/OLS/CLS), deployment pipelines, and Power BI report optimisation within Microsoft Fabric.

Ideal for

BI developers, Power BI report authors, data analysts building semantic models, and analytics engineers working in Microsoft Fabric.

Aspire to this if

You're a data analyst wanting to move into analytics engineering, or a SQL developer looking to master DAX and the modern Microsoft BI stack.

Section 1 / Spatial Memory

The Map

Tap any component to see what you need to know for the exam.

🏢 OneLake

Central Hub

🏭 Data Factory

Orchestration

🧹 Dataflows Gen2

Power Query Online

📓 Notebooks

PySpark · Spark SQL

🏛️ Lakehouse

Schema-on-read · Spark

🏗️ Warehouse

Schema-on-write · T-SQL

⚡ Eventhouse

Streaming · KQL

🔗 SQL Analytics Endpoint

Auto-generated · Read-only

🧮 Semantic Model

Direct Lake · Import · DirectQuery

🎨 Power BI Reports

Visualisation

Section 2 / Narrative Memory

The Story

Follow the narrative to build a mental model of how everything connects.

📦

The Arrival

Raw data arrives at the grand harbour of OneLake, the one lake to rule them all. Every piece of data in Microsoft Fabric automatically docks here -no exceptions. OneLake is built atop Azure Data Lake Storage Gen2, providing a single unified namespace for the entire organisation.

Exam IntelOneLake = OneDrive for data. One logical lake per tenant. Built on ADLS Gen2. All Fabric items store data in OneLake automatically.

🏗️

The Factory Floor

Inside the Data Factory, pipelines hum with 170+ connectors pulling data from every corner of the enterprise. The Copy Activity moves raw materials from source to destination, while orchestration pipelines coordinate the entire operation.

Exam IntelData Factory = orchestration + data movement. Copy Activity for transfers. Supports full and incremental loading patterns. Pipeline templates available.

🧹

The Cleaning Crew

Dataflows Gen2 workers scrub, transform, and polish the data using Power Query Online. They watch the folding indicators carefully -5 states: Folding, Not Folding, Might Fold, Opaque, and Unknown. When steps fold, transformations push back to the source.

Exam IntelQuery folding = pushing transforms to source. Check icons in Dataflow WEB UI (not desktop!). Column Quality/Distribution/Profile for data profiling. CSV files can't fold.

🔬

The Laboratory

Data scientists in the Notebook Lab run PySpark experiments. They broadcast small DataFrames to every node, use withColumn to engineer features, and maintain their Delta tables with regular VACUUM and OPTIMIZE cycles.

Exam IntelPySpark key methods: broadcast(), .withColumn(), .cast(), df.summary(). Delta maintenance: VACUUM, OPTIMIZE, DESCRIBE HISTORY. Predict function works with Spark SQL and PySpark.

🏛️

The Twin Vaults

Data reaches two great vaults. The Lakehouse welcomes all comers -structured, semi-structured, unstructured -with a flexible schema-on-read philosophy. The Warehouse, more selective, demands structured data and rewards it with full T-SQL power and multi-table transactions.

Exam IntelLakehouse = schema-on-read, Spark-first, Delta tables. Warehouse = schema-on-write, T-SQL, multi-table transactions. Lakehouse for data engineering/ML. Warehouse for traditional BI/analytics.

🔗

The Shortcut

A magical portal -the OneLake Shortcut -lets data appear in multiple places without being copied. Zero-copy access across workspaces, across lakehouses, even across clouds.

Exam IntelShortcuts = zero-copy data access. Work across workspaces and external sources. No data duplication. Appear as regular folders/tables in the lakehouse.

🏗️

The Architect's Workshop

The Semantic Model architect arranges tables into a perfect star schema -fact tables at the centre recording transactions and events, dimension tables radiating outward with descriptive attributes. Relationships flow one-to-many from dimension to fact, with single-direction cross-filtering keeping queries clean and predictable.

Exam IntelStar schema: fact tables (transactions) + dimension tables (attributes). Relationships: 1-to-many default, single cross-filter direction (dimension→fact). Bi-directional cross-filter only when needed (causes ambiguity). Many-to-many requires bridge table. USERELATIONSHIP() activates inactive relationships.

⚡

The Power Source

Storage modes determine how the model draws its power. Import caches everything in memory for blistering speed. DirectQuery reaches through live portals to the source. Direct Lake -the newest and most elegant -reads Delta tables directly from OneLake without importing or querying. Composite models mix all three.

Exam IntelImport = in-memory cache, fastest, scheduled refresh. DirectQuery = live, no size limit, slower. Direct Lake = reads Delta parquet directly, near real-time, capacity guardrails. Composite = mix modes. Direct Lake fallback: Automatic (default), DirectLakeOnly, DirectQueryOnly.

📐

The Formula Chamber

In the Formula Chamber, DAX expressions bring the model to life. CALCULATE -the most powerful function -transforms filter context, while context transition converts row context into filter context inside iterators. Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR unlock temporal analysis, but only when the date table is properly marked.

Exam IntelCALCULATE modifies filter context -the #1 tested DAX concept. Context transition: row context becomes filter context inside CALCULATE/iterators. Time intelligence requires a marked date table with continuous dates. TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, DATESYTD. Calculation groups use SELECTEDMEASURE(), precedence property, explicit measures only.

🛡️

The Security Sentinels

Three sentinels guard the data. RLS filters rows with DAX expressions. CLS hides specific columns. OLS -the most powerful -makes entire tables and columns invisible, as if they never existed.

Exam IntelRLS = DAX filter expressions + USERPRINCIPALNAME(). CLS = column-level hiding. OLS = requires Tabular Editor, cascading effect on dependent measures, objects completely invisible. Key difference: RLS filters data, OLS hides metadata.

🎨

The Gallery

Finally, data is displayed in the Gallery of Reports. The Performance Analyzer watches for slow visuals. Viewers browse with confidence, knowing Promoted items are team-approved, Certified items meet org quality standards, and Master data items are the organisation's single source of truth.

Exam IntelPerformance Analyzer separates DAX query time from render time. Endorsement: Promoted (team) → Certified (org quality) → Master data (single source of truth). Sensitivity labels propagate downstream. Reduce visuals per page for performance.

🔄

The Cycle Continues

Deployment pipelines move everything from Dev to Test to Prod. Git integration via PBIP format enables PR reviews. The XMLA endpoint opens the door to enterprise tools like Tabular Editor and DAX Studio.

Exam IntelDeployment pipelines: default Dev→Test→Prod (supports 2-10 stages) with data source binding rules. Git: PBIP/PBIR format. XMLA endpoint for Tabular Editor, SSMS, scripted deployments. Impact analysis traces downstream dependencies.

Section 3 / Acronym Memory

Mnemonic Wall

Memorable acronyms and phrases to anchor key exam concepts in your memory.

🗺️

MPS

The 3 exam domains (updated April 2026). Prepare is the biggest slice. Semantic models increased to 25-30%.

💾

DILD

Storage modes from newest to oldest.

🔒

ROC

Security layers. RLS filters Rows, OLS hides Objects, CLS restricts Columns.

🪟

VOW

"I VOW to learn window functions." DAX window function building blocks.

🔢

SIX

"The SIX rule: if you need multiple columns, add an X." Iterator function rule.

🚀

DTP

Deployment pipeline stages in order.

🛠️

TABS

The 4 key external tools for Power BI enterprise management.

📊

QDP

Data profiling tools in Dataflows Gen2. Quality = % valid/error/empty. Distribution = distinct vs unique counts. Profile = min/max/statistics.

⭐

STAR

Star schema design rules. Single cross-filter direction from dimension to fact. Avoid bi-directional unless absolutely needed.

🕐

CATS

Core DAX patterns. CALCULATE = context modifier. ALL/ALLSELECTED/ALLEXCEPT remove filters. Mark as Date Table required for time intelligence.

🔧

VOCO

Delta table maintenance commands.

⭐

PCM

"PCM = Promoted → Certified → Master data." Three endorsement levels.

⚡

WISE

KQL operators in logical order.

⚡

FLASH

Direct Lake fallback triggers.

🔄

Incremental refresh parameters. Must be exact names. Query folding strongly recommended. No equality on both parameters. No IR for Direct Lake.

👥

AMVC

Workspace roles from most to least powerful.

🔑

BUILD

Build permission capabilities.

Section 4 / Contrast Memory

Versus Arena

Side-by-side comparisons to sharpen your understanding of similar concepts.

LakehousevsWarehouse

Lakehouse vs Warehouse

Aspect	Lakehouse	Warehouse
Schema	Schema-on-read	Schema-on-write
Language	Spark (PySpark/SQL)	T-SQL
Data types	All (structured, semi, unstructured)	Structured only
Transactions	Single table	Multi-table
Best for	Data engineering, ML, data science	BI analytics, reporting
Storage	Delta tables + Files	Tables only
Auto-endpoint	SQL Analytics Endpoint (read-only)	Full SQL (read-write)

ImportvsDirectQueryvsDirect Lake

Import vs DirectQuery vs Direct Lake

Aspect	Import	DirectQuery
Data location	In-memory cache	Source system / OneLake Delta
Performance	Fastest	Slowest / Fast (with fallback)
Freshness	Scheduled refresh	Real-time / Near real-time
Size limit	Model size limit	No limit / Capacity guardrails
Refresh needed	Yes	No / Framing only
Best for	Small-medium data	Must be live / Fabric-native data

RLSvsCLSvsOLS

RLS vs CLS vs OLS

Aspect	RLS	CLS
Restricts	Rows	Columns / Tables & Columns
Method	DAX filter expressions	Column hiding per role / Tabular Editor required
User sees	Object exists, data filtered	Column hidden / Object doesn't exist
Key function	USERPRINCIPALNAME()	N/A / N/A
Cascading	No	No / Yes -dependent measures hidden
Config in PBI Desktop	Yes	Yes / No -external tool only

Direct Lake: OneLakevsSQL Endpoints

Direct Lake Source Comparison

Aspect	Direct Lake: OneLake	SQL Endpoints
Source	Delta parquet files in OneLake	Auto-generated SQL analytics endpoint
Performance	Best (direct file read)	Good (may fallback to DirectQuery)
Views support	No	Yes (SQL views)
Cross-DB queries	No	Yes
Best for	Lakehouse Delta tables	Warehouse data, need SQL views
Fallback risk	Lower	Higher (views trigger fallback)

Dataflow Gen1vsGen2

Dataflow Gen1 vs Gen2

Aspect	Dataflow Gen1	Gen2
Output	Power BI dataset only	Multiple destinations
Engine	Power Query	Power Query + enhanced compute
Staging	Limited	Lakehouse staging
Performance	Standard	Fast copy + scale-out
Fabric native	No	Yes

PromotedvsCertifiedvsMaster Data

Endorsement Levels

Aspect	Promoted	Certified
Who sets it	Users with write access	Designated certifiers / Designated certifiers
Meaning	"Team trusts this"	"Meets org quality standards" / "Single source of truth"
Governance	Informal	Formal process / Formal + authoritative

MeasuresvsCalculated Columns

Measures vs Calculated Columns

Aspect	Measures	Calculated Columns
Evaluated	Query time (dynamic)	Refresh time (static)
Context	Filter context	Row context
Storage	No storage cost	Stored in model
Performance	Generally better	Increases model size
Use for	Aggregations, ratios, KPIs	Row-level flags, categories
Best practice	Prefer measures	Use sparingly

CALCULATEvsNo CALCULATE

CALCULATE vs No CALCULATE

Aspect	CALCULATE	No CALCULATE
Context	Modifies filter context	Inherits current context
Transition	Converts row → filter context	No context transition
Filters	Can add/remove/replace filters	Uses existing filters only
Common with	ALL, FILTER, VALUES, time intel	Simple SUM, COUNT, AVERAGE
Use for	Ratios, totals ignoring slicers, YoY	Basic aggregations
Exam weight	Very heavily tested	Assumed knowledge

SinglevsBi-directional Cross-filter

Cross-filter Direction

Aspect	Single	Bi-directional Cross-filter
Direction	Dimension → Fact only	Both directions
Default	Yes (recommended)	No (opt-in)
Performance	Better	Slower, more complex
Ambiguity risk	None	Can create ambiguous paths
Use when	Standard star schema	Many-to-many bridge tables, slicers from fact side
Best practice	Prefer this always	Only when required, document why

AggregatorsvsIterators

Aggregators vs Iterators

Aspect	Aggregators	Iterators
Examples	SUM, AVERAGE, MIN, MAX	SUMX, AVERAGEX, MINX, MAXX
Naming	No suffix	End in X
Parameters	Single column	Table + expression
Multi-column	Cannot	Required for multi-column
Context	Filter context	Row context per row
Performance	Faster	Slower on large tables

Query Folding: YesvsNo

Query Folding: Yes vs No

Aspect	Query Folding: Yes	No
Indicators	Folding (green)	Not folding / Might fold / Opaque / Unknown
Processing	At data source	Locally in Power Query
Performance	Fast, efficient	Slow, resource-intensive
Examples	SQL Server, Azure SQL	CSV files, web scraping
Required for	Incremental refresh	N/A
Check in	Dataflow WEB UI	(same)

OneLake ShortcutsvsData Copy

OneLake Shortcuts vs Data Copy

Aspect	OneLake Shortcuts	Data Copy
Data duplication	No (zero-copy)	Yes
Storage cost	None	Double
Freshness	Always current	Stale until refreshed
Cross-workspace	Yes	Yes
External sources	Yes (ADLS, S3)	Via pipelines
Use when	Real-time access needed	Transformation required

Section 5 / Grouping Memory

Cheat Sheet

Organised reference grouped by exam domain — everything you need on one page.

Maintain a Data Analytics Solution

25-30%

Workspace Roles & Governance

Admin > Member > Contributor > Viewer
Build Permission: Create reports, Analyze in Excel, composite models, cross-workspace access
Sensitivity Labels: Public → General → Confidential → Highly Confidential. Propagate downstream
Endorsed: Promoted → Certified → Master data (single source of truth)
F-SKU (Fabric), Premium. Shared = PBI only

Deployment & Version Control

Deployment Pipelines: Dev → Test → Prod (supports 2-10 stages)
Data source binding rules per stage
Impact analysis for downstream dependencies
Git: PBIP/PBIR text format for PR reviews. Notebooks as source files
XMLA Endpoint: Tabular Editor, SSMS, partitioning, scripted deployments

Performance & Monitoring

Performance Analyzer: DAX query time vs visual render time
Query Diagnostics: backend DQ/DL behaviour
Fewer visuals per page. Summary over detail
Dropdowns not lists for high-cardinality slicers
Disable unnecessary cross-filtering between visuals

Security

RLS: DAX filter expressions + USERPRINCIPALNAME(). Test with "View as" role
OLS: Tabular Editor only. Objects completely invisible. Cascading effect on measures
CLS: Column-level hiding per role
Sensitivity labels propagate downstream to reports

Query Languages

T-SQL: warehouse + SQL analytics endpoint. Visual Query Editor for no-code
KQL: where, summarize, render, extend. Eventhouse/real-time analytics
DAX: EVALUATE + SUMMARIZECOLUMNS. CALCULATE for context modification
Lineage: Source → Dataflow → Lakehouse → Model → Reports

Prepare Data

45-50%

OneLake & Shortcuts

One lake per tenant. ADLS Gen2. Unified namespace
Shortcuts: zero-copy, cross-workspace, cross-cloud (ADLS, S3)

Storage

Lakehouse: schema-on-read, Spark, Delta + Files, Z-Order, file-level security
Warehouse: schema-on-write, T-SQL, multi-table tx, cross-DB queries
Eventhouse: streaming/events, KQL, telemetry, logs, IoT
SQL Analytics Endpoint: auto-gen for lakehouses, read-only T-SQL

Data Movement

Data Factory: 170+ connectors, Copy Activity, full + incremental
Dataflows Gen2: Power Query Online, query folding (web UI icons!)
Profiling: Column Quality / Distribution / Profile

Query Folding

5 indicator states: Folding, Not Folding, Might Fold, Opaque, Unknown
Check in web UI only (not desktop)
CSV never folds. Required for incremental refresh

Notebooks & Delta

PySpark + Spark SQL. broadcast() for small DFs
.withColumn(), .cast(). predict() in both languages
VACUUM, OPTIMIZE, DESCRIBE HISTORY, Z-Order

Semantic Models

20-25%

Star Schema Design

Fact tables: transactions/events (Sales, Orders). Dimension tables: descriptive attributes (Date, Product, Customer)
Relationships: 1-to-many from dimension to fact. Single cross-filter direction (default)
Bi-directional cross-filter: only for many-to-many bridge tables. Creates ambiguity risk
USERELATIONSHIP(): activates inactive relationships in CALCULATE. One active relationship per path
Avoid snowflake schema (chained dimensions) — flatten into star for best performance

Storage Modes

Import: fastest, in-memory, scheduled refresh. Best for small-medium data
DirectQuery: live queries to source, slower, no size limit. Best for must-be-live
Direct Lake: reads Delta parquet directly from OneLake. No import, no live query overhead
Composite: mix modes in one model. Remote models (chained) vs local tables
Large format: enable for >10GB OR any XMLA write operations

Direct Lake

Fallback modes: Automatic (default), DirectLakeOnly (errors instead of fallback), DirectQueryOnly
Fallback triggers: RLS on table, views (auto-generated SQL endpoint), capacity guardrails exceeded
Framing: Direct Lake equivalent of refresh — updates metadata pointers to new Delta files
No incremental refresh for Direct Lake — framing replaces it
Direct Lake on OneLake: reads Delta parquet files directly. Best performance. Source must be in OneLake
Direct Lake on SQL endpoints: uses auto-generated SQL endpoint. Supports views but may trigger fallback to DirectQuery
Choose OneLake when: data is in lakehouse Delta tables, maximum read performance needed
Choose SQL endpoints when: need SQL views, cross-database queries, or data is in warehouse

Composite Models & Bridge Tables

Mix Import, DirectQuery, and Direct Lake tables in one model
Local tables: owned by this model (Import or DirectQuery to external source)
Remote tables: chained from another published semantic model (DirectQuery connection)
Use case: extend a shared enterprise model with local Import tables for department-specific data
Requires Build permission on source semantic model for chaining
Bridge tables for many-to-many: dimension → bridge → dimension. Bi-directional cross-filter on bridge relationships

CALCULATE & Context

CALCULATE: modifies filter context — the most tested DAX function on the exam
Context transition: row context → filter context when CALCULATE wraps an iterator
CALCULATE + ALL(): removes all filters (total regardless of slicers)
CALCULATE + ALLSELECTED(): removes visual-level filters but keeps slicer/page filters
CALCULATE + FILTER(): adds row-level filtering, but slower than direct CALCULATE filters
CALCULATE + KEEPFILTERS(): intersects new filter with existing (instead of replacing)

Time Intelligence

REQUIRES: Mark as Date Table (or auto date/time, but disable for perf)
Date table must have continuous dates, no gaps, unique date column
TOTALYTD / TOTALQTD / TOTALMTD: running totals. Wraps CALCULATE + DATESYTD
SAMEPERIODLASTYEAR: same date range from prior year. For YoY comparisons
DATEADD(DateColumn, -1, YEAR): shift any period. Flexible alternative
PARALLELPERIOD: shifts entire periods (whole months/quarters/years)

DAX Patterns

VAR/RETURN: evaluated once, improves perf and readability
Iterators (X): SUMX, AVERAGEX — table + expression, row context per row
Window: INDEX (nth row), OFFSET (relative), WINDOW (range) + ORDERBY/PARTITIONBY
Info functions: ISBLANK, HASONEVALUE, ISINSCOPE, SELECTEDVALUE
Calc groups: SELECTEDMEASURE(), precedence property (higher = outermost), explicit measures only
Field parameters: dynamic column/measure switching in visuals. Created via Tabular Editor or DAX
Dynamic format strings: FORMAT expressions on measures for conditional formatting

Model Performance

Disable Auto date/time (creates hidden tables per date column)
Reduce column cardinality — fewer distinct values = better compression
Remove unused columns from Import models — every column costs memory
Prefer measures over calculated columns (no storage cost)
Star schema outperforms snowflake — fewer joins, better scan patterns
Default summarisation: set to "Don't summarize" for non-additive columns
Data categories (Geography, URL, Image URL, Barcode) — enables map visuals and special rendering

Incremental Refresh

RangeStart/RangeEnd parameters (exact names, DateTime type!)
Query folding strongly recommended — must fold for best performance
No equality operator on both parameters simultaneously
No incremental refresh for Direct Lake — use framing instead
Hybrid tables (real-time + incremental) = Premium/Fabric capacity only

External Tools

Tabular Editor: OLS config, calc groups, partitions, best practices. THE key enterprise tool
DAX Studio: query perf analysis, VertiPaq Analyzer (model compression stats)
ALM Toolkit: schema comparison and deployment between environments
Best Practice Analyzer (BPA): automated model quality checks

Section 6 / Method of Loci

The Memory Palace

Walk through themed rooms — each object anchors a concept in spatial memory.

The Operations Centre

Maintain -Where the analytics solution is governed and monitored

🏢

OneLake & Workspace

One lake per tenant, ADLS Gen2. Roles: Admin > Member > Contributor > Viewer. F-SKU (Fabric), Premium

🔑

Build Permission

Create reports from models, Analyze in Excel, composite models, cross-workspace

⭐

Governance

Endorsed: Promoted → Certified → Master data. Sensitivity Labels propagate downstream. Lineage tracing

🔍

Performance Monitoring

Performance Analyzer: DAX query vs render time. Query Diagnostics for DQ/DL behaviour. Fewer visuals per page

🚀

Deployment & Git

Pipelines: Dev → Test → Prod. Binding rules. Git: PBIP/PBIR. XMLA endpoint for enterprise tools

🛠️

External Tools & Query

Tabular Editor, DAX Studio, ALM Toolkit, BPA. T-SQL, KQL, Visual Query Editor

The Data Lab

Data Preparation -Where raw becomes refined

🏭

Data Factory

170+ connectors, Copy Activity, pipeline orchestration

🧹

Dataflows Gen2

Power Query Online, query folding indicators (web UI only!)

📊

Data Profiling

Quality (valid/error/empty %), Distribution (distinct/unique), Profile (statistics)

🔗

OneLake Shortcuts

Zero-copy data access across workspaces and clouds

📓

Notebooks

PySpark: broadcast(), withColumn(), cast(). Delta: VACUUM, OPTIMIZE

🔄

Query Folding

5 states: Folding, Not Folding, Might Fold, Opaque, Unknown. Web UI only. CSV never folds

The Model Workshop

Semantic Models -Where data becomes meaning

⭐

Star Schema

Fact tables (centre) + dimension tables (radiate out). 1-to-many relationships. Single cross-filter direction default. Flatten snowflake into star

🔗

Relationships

1-to-many (default). Bi-directional only for many-to-many bridges. USERELATIONSHIP() for inactive. One active path per table pair

⚡

Storage Modes

Import (fastest, cached) → Direct Lake (reads Delta parquet) → DirectQuery (live, slowest). Composite = mix all three

🔄

Direct Lake

Fallback: Automatic/DirectLakeOnly/DirectQueryOnly. Triggers: RLS, views, guardrails. Framing replaces refresh

🧮

CALCULATE

Modifies filter context — #1 tested concept. Context transition: row→filter. Combine with ALL, FILTER, KEEPFILTERS, time intel

🕐

Time Intelligence

TOTALYTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD. REQUIRES marked date table with continuous dates

📐

DAX Patterns

Iterators end in X (SUMX). VAR/RETURN evaluated once. Window funcs need ORDERBY. Calc groups use SELECTEDMEASURE()

🏎️

Performance Tuning

Disable Auto date/time. Reduce cardinality. Remove unused columns. Measures > calc columns. Set "Don't summarize" defaults

The Security Vault

Security & Governance -Where trust is enforced

🔒

RLS

DAX filter expressions. USERPRINCIPALNAME(). Filters rows only. Dynamic per user

🔐

OLS

Tabular Editor ONLY. Hides entire objects. Cascading: dependent measures vanish

🛡️

CLS

Column-level hiding. Can't protect measures or tables (use OLS for that)

📋

XMLA Endpoint

Enterprise management. Tabular Editor, SSMS. Enable large format for writes

🚀

Deployment Pipelines

Dev → Test → Prod. Data source binding rules. Impact analysis

🔀

Git Integration

PBIP/PBIR format. PR reviews. Notebooks as source files (.py/.sql)

Section 7 / Pattern Recognition

Pattern Spotter

Decision trees and trigger-answer pairs — see the pattern, know the answer.

Which Data Store?

Which Data Store?
  ├── Streaming / Events / IoT → Eventhouse (KQL)
  ├── Unstructured / Semi-structured → Lakehouse
  ├── Need multi-table transactions? → Yes → Warehouse
  ├── Need multi-table transactions? → No → Need Spark / ML? → Yes → Lakehouse
  ├── Need multi-table transactions? → No → Need Spark / ML? → No → Need T-SQL? → Yes → Warehouse
  └── Need multi-table transactions? → No → Need Spark / ML? → No → Need T-SQL? → Either works → Lakehouse (more flexible)

Which Storage Mode?

Which Storage Mode?
  ├── OneLake Delta tables → Direct Lake
  ├── OneLake Delta tables → Need guaranteed no fallback? → DirectLakeOnly setting
  ├── External source, needs to be live → DirectQuery
  ├── Small-medium, can schedule refresh → Import
  └── Mix of sources / needs → Composite Model

Which Security Layer?

Which Security Layer?
  ├── Which rows users see → RLS
  ├── Which columns users see → CLS
  ├── Hide entire tables/columns from existence → OLS
  ├── Classify data sensitivity → Sensitivity Labels
  └── File-level in lakehouse → OneLake RBAC

Which Direct Lake Source?

Which Direct Lake Source?
  ├── Data in lakehouse Delta tables → Direct Lake on OneLake
  ├── Need SQL views or cross-database queries → Direct Lake on SQL endpoints
  ├── Data in warehouse tables → Direct Lake on SQL endpoints
  ├── Maximum performance, no fallback risk → Direct Lake on OneLake + DirectLakeOnly mode
  └── Need to combine with external DirectQuery → Composite Model

Which CALCULATE Pattern?

Which CALCULATE Pattern?
  ├── Ignore all slicers / show grand total → CALCULATE + ALL(Table)
  ├── Ignore visual filters, keep page/slicer filters → CALCULATE + ALLSELECTED()
  ├── Remove filter on one column only → CALCULATE + ALLEXCEPT or REMOVEFILTERS
  ├── Add a filter AND keep existing filters → CALCULATE + KEEPFILTERS()
  ├── Year-to-date running total → CALCULATE + DATESYTD (or TOTALYTD shortcut)
  ├── Same period last year comparison → CALCULATE + SAMEPERIODLASTYEAR
  └── Use an inactive relationship → CALCULATE + USERELATIONSHIP()

Which External Tool?

Which External Tool?
  ├── Configure OLS → Tabular Editor
  ├── Create calculation groups → Tabular Editor
  ├── Analyse DAX query performance → DAX Studio
  ├── Compare/deploy models between environments → ALM Toolkit
  ├── Check model best practices → Best Practice Analyzer
  └── Analyse model storage/compression → VertiPaq Analyzer

Decision Cards

"zero-copy" or "no data duplication"→OneLake Shortcuts

"schema-on-read"→Lakehouse

"multi-table transactions"→Warehouse

"streaming" or "telemetry" or "IoT"→Eventhouse

"objects completely hidden" or "as if deleted"→OLS (Object-Level Security)

"Tabular Editor required"→OLS configuration

"SELECTEDMEASURE()"→Calculation Groups

"RangeStart / RangeEnd"→Incremental Refresh

"fallback to DirectQuery"→Direct Lake on SQL endpoints

"query folding required"→Incremental Refresh

"single source of truth"→Master data endorsement

"propagate downstream"→Sensitivity Labels

"VACUUM or OPTIMIZE"→Delta Table Maintenance

"broadcast()"→Small DataFrame optimisation in PySpark

"precedence property"→Calculation Groups

"context transition" or "row becomes filter"→CALCULATE inside an iterator

"mark as date table" or "continuous dates"→Time Intelligence requirement

"TOTALYTD" or "SAMEPERIODLASTYEAR"→Time Intelligence (needs marked date table)

"USERELATIONSHIP()"→Activating inactive relationships in CALCULATE

"bi-directional" or "both directions"→Cross-filter direction (avoid unless many-to-many)

"disable auto date/time"→Model performance optimisation

"framing" (not "refresh")→Direct Lake metadata pointer update

"star schema" or "fact and dimension"→Semantic model design best practice

"remote model" or "chained model"→Composite Model with DirectQuery to published model

"bridge table" or "many-to-many relationship"→Bridge table pattern with bi-directional cross-filter

"Direct Lake on SQL endpoint" or "views in Direct Lake"→Direct Lake on SQL endpoints (may fallback)

"extend enterprise model" or "department-specific"→Composite model: chain remote + local Import tables

"KEEPFILTERS()"→Intersect (not replace) filters in CALCULATE

"PBIP or PBIR format"→Git Integration / version control

"impact analysis"→Deployment Pipelines / Lineage

"enable large format even for small models"→XMLA write operations

Ready to certify?

Train with practitioners, not presenters

Lucid Labs delivers Microsoft certification training led by Microsoft Certified Trainers (MCTs) and grounded in real-world project experience. We adapt every session to your team's environment, data stack, and business objectives — because the best exam prep comes from engineers who build these solutions every day.

🎯

Tailored Content

Training built around your actual data, your tools, and your use cases - not generic slides.

🛠️

Hands-On Labs

Work through real scenarios in your own environment with expert guidance at every step.

📈

Exam + Capability

Pass the exam and build lasting skills your team can apply from day one.

Talk to us about Microsoft Fabric Analytics Engineer (DP-600) training

Custom training for teams & individuals — remote or on-site across Australia

Keith Oak

Chief Technology Officer — Lucid Labs

Microsoft Solutions Partner architect specialising in Fabric, Azure Data & AI, and GitHub Enterprise. 18+ years delivering data platforms for Australian businesses — building the systems these exams test every day.

LinkedIn ↗lucidlabs.com.au ↗Published 29-03-2026