CertifyDeck
Start for free
All cheat sheets
DP-600 · CHEAT SHEET

Microsoft Certified: Fabric Analytics Engineer Associate

Key concepts grouped by official exam weight.

Download as PDF (free, no sign-up)

1. Maintain a data analytics solution

25–30%

Implement security and governance

  • Workspace roles (Admin, Member, Contributor and Viewer) are assigned to users or groups and apply to every item. A user in several groups gets the highest role. Viewer reads content and data with T-SQL but has no Spark or write access; Contributor creates and runs items; Member also adds people; only Admin deletes the workspace and connects Git.
  • Sharing an item gives access to that item without a workspace role and always grants Read. You can add ReadData (T-SQL), ReadAll (Spark and OneLake), Build or Write on semantic models, and Share, which only passes on the sharer's own permissions. Permissions inherited from a workspace role cannot be removed on the item; for finer warehouse access, use GRANT.
  • In a warehouse, RLS uses CREATE SECURITY POLICY with inline table-valued functions, CLS uses GRANT on columns, and OLS uses GRANT, REVOKE and DENY. In semantic models, RLS and OLS are defined in roles and only affect Viewers. OneLake security grants Read by folder, table, row or column, with a DefaultReader role for users who have ReadAll.
  • Microsoft Purview sensitivity labels are applied from the item header or settings and require edit permissions. They can be inherited on creation, downstream (never overwriting manual labels or lowering restriction) and from sources in Import models. In the service they do not limit access unless a protection policy is used; encryption travels with supported exports.
  • Endorsement helps people find trustworthy content through three badges. Anyone with write permissions can apply Promoted; Certified and Master data can be applied only by users authorized by the Fabric administrator, who also need write access. Everything except Power BI dashboards can be promoted or certified, and Master data applies only to items that hold data.

Maintain the analytics development lifecycle

  • Fabric Git integration works at workspace level: only a workspace admin connects it to or disconnects it from an Azure DevOps or GitHub branch, and you then commit workspace changes or update from the whole branch. Only item definitions are versioned, never data, and each developer can branch out to an own workspace to isolate development.
  • A Power BI project (PBIP) saves the report and semantic model as folders of Git-ready text files: definition.pbir references the model and the definition folder holds the model in TMDL. The default .gitignore excludes cache.abf and localSettings.json. Publishing from Power BI Desktop also sends the data cache, while Git and the APIs deploy only metadata.
  • A deployment pipeline assigns one workspace to each stage (Development, Test and Production by default) and copies metadata, not data, between paired items. Data source, parameter and default lakehouse rules are created in the target stage and apply at the next deployment. Deploying requires being a pipeline admin and at least a Contributor in both workspaces.
  • Impact analysis shows the items and workspaces that a change to an item might affect, as direct children or all downstream items, and lets you email their contacts with Notify contacts. It requires write permission on the item, and for data sources it shows only direct children. Lineage view doesn't show downstream items in other workspaces.
  • The XMLA endpoint exposes each workspace as an Analysis Services server: it is read-only by default and must be enabled for read-write on the capacity to deploy models, run TMSL or perform fine-grained partition refreshes. Contributors act as database admins, and data source credentials are set in the service, not through XMLA.

2. Prepare data

45–50%

Get data

  • Connections are created in Manage connections and gateways and have their own access list (User, User with resharing, Owner). On-premises data needs the standard gateway; personal mode only serves Import models in Power BI. A workspace identity avoids handling secrets and enables trusted workspace access to firewall-enabled storage.
  • OneLake catalog centralizes discovery: Explore lists accessible items with filters, domains and details (schema, lineage); Govern shows governance status and Secure shows security roles. Real-Time hub, present in every tenant, gathers streams, KQL tables, streaming connectors, and Fabric, Azure and business events.
  • To access data without copying, use OneLake shortcuts (internal or to ADLS, S3, GCS and more), with optional caching to reduce egress. To ingest: Mirroring for continuous read-only replication, Copy job for bulk and incremental copies without pipelines, pipelines for orchestration, Dataflow Gen2 for Power Query, eventstreams for streaming and Spark for code.
  • Eventhouse serves streaming events and granular analysis with KQL; Cosmos DB in Fabric serves NoSQL; SQL database in Fabric serves OLTP; Warehouse serves T-SQL enterprise warehousing with multi-table transactions; and Lakehouse serves Spark and unstructured data with a read-only SQL endpoint. All are in OneLake, opt-in for KQL databases.
  • Eventhouse OneLake availability creates a logical Delta copy of a KQL database or table that Spark, lakehouses and Direct Lake can read; while it is on you can't rename tables, change column types or purge data. OneLake integration for a semantic model writes its import tables as Delta tables after every refresh, so shortcuts can reuse that data.

Transform data

  • In Fabric Data Warehouse and the SQL analytics endpoint you can create views, inline table-valued functions and stored procedures; the endpoint is read-only, so logic that modifies tables belongs in a warehouse. T-SQL materialized views aren't supported: use materialized lake views for persisted results, and stored functions in KQL databases.
  • To enrich data in Power Query you use custom (M formula), conditional, from-examples or index columns, and merges, with fuzzy matching on text columns. In a warehouse, ALTER TABLE only adds nullable columns and new tables are created with CTAS, SELECT INTO or INSERT...SELECT; in KQL, extend adds calculated columns to the result set.
  • A star schema separates fact tables, holding dimension keys and measures, from denormalized dimension tables with a surrogate key. In Fabric Warehouse the surrogate key is usually BIGINT IDENTITY, the date key an int in YYYYMMDD format, and primary and foreign keys are declared NOT ENFORCED, so the ETL loads dimensions first and checks integrity itself.
  • Dimension tables are almost always denormalized, flattening hierarchies such as category and subcategory, because the redundancy pays off in performance and usability; a snowflake only makes sense to relate higher-grain facts or track history at higher levels. You denormalize with views that join tables, Power Query merges with expanded columns, or lookup in KQL.
  • In Dataflow Gen2, Group by summarizes rows with operations such as Sum, Count rows, Count distinct values or All rows, and supports fuzzy grouping for text with variations. In Fabric T-SQL, ROLLUP, CUBE and GROUPING SETS add subtotals and a grand total, and GROUP BY ALL groups by every non-aggregated item. In KQL, summarize aggregates per group and arg_max returns the full row of the maximum value.

Query and analyze data

  • The Fabric visual query editor lets you query warehouses, SQL analytics endpoints and mirrored databases without code, using Power Query diagram view: drag tables, merge them, filter and group. It only runs read-only SELECT logic built from folding steps, shows the generated T-SQL, and saves the result as a view or as a table (CREATE TABLE AS SELECT).
  • In a Fabric warehouse or SQL analytics endpoint you filter rows with WHERE before grouping and groups with HAVING afterwards; GROUP BY ALL groups by every non-aggregated column, and ROLLUP or CUBE add subtotals. Three-part names join items in the same workspace, and the SQL analytics endpoint supports views but not table creation or DML.
  • In KQL you filter with where, choose columns with project and aggregate with summarize ... by, using bin() to bucket dates and functions such as count(), countif(), dcount() (an estimate) or arg_max() to return the full row of the maximum. has matches whole terms and is faster than contains, which matches substrings; top sorts descending by default and take does not guarantee which rows it returns.
  • A DAX query needs at least one EVALUATE followed by a table expression (a scalar goes inside curly braces) and allows a single DEFINE block with query-scoped measures. SUMMARIZECOLUMNS groups and aggregates while dropping blank rows, TOPN can return extra rows when there are ties, and CALCULATE replaces filters on the same column unless KEEPFILTERS is used.

3. Implement and manage semantic models

25–30%

Design and build semantic models

  • Import copies data into memory and needs refreshes; DirectQuery queries the source and suits huge volumes or near real-time data, with limits on M and DAX; Dual mixes both for dimensions. Direct Lake loads OneLake Delta tables with the VertiPaq engine and its refresh copies only metadata: on OneLake supports several sources with no DirectQuery fallback, while on SQL uses one source and can fall back.
  • In a semantic model, relationship cardinality decides the table type: the one side is a dimension and the many side a fact. Denormalize snowflake dimensions into one table, create one table per role for role-playing dimensions, and load Type 2 dimensions already versioned by a data warehouse, because Power Query can't produce them.
  • To relate two dimensions many-to-many, use a bridging table with one-to-many relationships and one bi-directional relationship, and hide the bridge. Higher-grain facts relate through a single-direction many-to-many relationship. Many-to-many and cross source group relationships are limited: they use inner join semantics and don't support RELATED.
  • Variables (VAR) are evaluated once and outside the filters that RETURN applies, improving performance and readability. In CALCULATE, prefer Boolean filters with KEEPFILTERS and keep FILTER for conditions on measures. WINDOW, OFFSET and INDEX work with row positions within partitions, and information functions such as ISINSCOPE or HASONEVALUE detect the context.
  • Calculation groups apply reusable expressions through SELECTEDMEASURE to explicit measures only, and precedence decides how several groups nest. Dynamic format strings change the format by context without turning the measure into text, unlike FORMAT. Field parameters let readers choose in a slicer which measures or columns a visual uses.

Optimize enterprise-scale semantic models

  • To speed up reports, limit visuals per page and use drillthrough and report page tooltips, apply Top N filters to large tables and switch off unneeded totals with DirectQuery. Query reduction turns off cross-filtering and adds Apply buttons. Performance analyzer breaks each visual into DAX query, Direct query, visual display and other durations.
  • To improve DAX performance, use variables so the same expression isn't evaluated twice, DIVIDE when the denominator can be zero or BLANK and COUNTROWS to count rows. Pass Boolean filters to CALCULATE (with KEEPFILTERS to keep existing filters) and keep FILTER for conditions on measures. Avoid IFERROR and don't convert BLANK to zero.
  • A Direct Lake model refreshes through framing, which copies only metadata and fixes the Delta version that queries see; columns are loaded into memory on demand. Automatic updates frame when changes are detected and can be turned off to wait for ETL. With Direct Lake on SQL, DirectLakeBehavior decides whether DirectQuery fallback is automatic, blocked or forced.
  • Direct Lake on OneLake reads Delta tables from one or more Fabric items through OneLake APIs, enforces OneLake security and never falls back to DirectQuery; it's the recommended option for new models. Direct Lake on SQL uses a single source, checks permissions at the SQL analytics endpoint and falls back to DirectQuery for views, SQL RLS or exceeded guardrails.
  • Incremental refresh filters the table with the Date/Time parameters RangeStart and RangeEnd, which the service overrides to build refresh and historical partitions in a rolling window; the filter should fold to the source. Options: detect data changes with an audit column, only complete days and a real-time DirectQuery partition. Republishing drops the partitions: use the XMLA endpoint for metadata-only changes.
Practice this certification