DP-600 Microsoft Fabric Analytics Engineer - Practice Test 5

The `where` operator filters rows. Example: StormEvents | where StartTime between (datetime(2024-01-01) .. datetime(2024-12-31)) | where State == "TEXAS" | where EpisodeId > 100 Multiple `where` operators can be chained or combined with `and`/`or`. The `where` operator is the KQL equivalent of SQL WHERE. See more: KQL & Eventhouse

`project` selects and optionally renames or calculates columns: StormEvents | project EventType, StartTime, DamageProperty | project EventTime = StartTime, EventType Use `project-away` to drop specific columns while keeping the rest. Use `project-rename` to rename without dropping columns. `project` is equivalent to SELECT in SQL. See more: KQL & Eventhouse

The `summarize` operator groups data and calculates aggregates: StormEvents | summarize Count = count(), TotalDamage = sum(DamageProperty) by State | order by TotalDamage desc Common aggregate functions: count(), sum(), avg(), min(), max(), dcount() (distinct count), make_list(), percentile(). The `by` clause defines the grouping - equivalent to SQL's GROUP BY. See more: KQL & Eventhouse

`extend` adds a computed column while keeping all existing columns: StormEvents | extend DamageMillions = DamageProperty / 1000000.0 | extend Duration = EndTime - StartTime | extend Category = iff(EpisodeId > 500, "High", "Low") It is equivalent to adding calculated columns in a SQL SELECT without aggregation. Unlike `project`, `extend` preserves all original columns. See more: KQL & Eventhouse

`take n` (aliased as `limit n`) returns n arbitrary rows from the table without any guaranteed ordering. For example: StormEvents | take 10 To return the n rows with the largest or smallest value of a column, use `top`: StormEvents | top 10 by DamageProperty desc `take` is for spot-checking; `top` is for ranked results. Both avoid fetching the entire table. See more: KQL & Eventhouse

`has` uses an inverted index for whole-term matching (separated by non-alphanumeric characters) - it is faster and should be preferred. `contains` matches any substring, including mid-word, and is slower because it cannot use the index. EventTitle has "storm" - matches "storm" as a word EventTitle contains "tor" - matches "tornado", "victor", "mentor" For case-sensitive variants use `has_cs` and `contains_cs`. Use `has` whenever searching for whole words for best performance. See more: KQL & Eventhouse

strcat(str1, str2, ...) concatenates strings: StormEvents | extend FullLocation = strcat(State, " - ", EventType) Other useful KQL string functions: - strlen(str): length of string - toupper(str) / tolower(str): case conversion - substring(str, start, length): substring extraction - trim(pattern, str): remove leading/trailing matches - split(str, delimiter): split into dynamic array See more: KQL & Eventhouse

The `join` operator combines rows from two tables based on matching key columns: FactSales | join kind=inner (Products | project ProductID, ProductName) on ProductID Join kinds: inner, leftouter, rightouter, fullouter, leftanti, rightanti, leftsemi, rightsemi. For lookup-style joins (small dimension table), `lookup` is more efficient than `join`. `union` is different - it appends rows from multiple tables (like SQL UNION ALL). See more: KQL & Eventhouse

`union` appends rows from multiple tables (by default includes all rows, like UNION ALL): union StormEvents2022, StormEvents2023, StormEvents2024 | where State == "FLORIDA" You can also use wildcards: union StormEvents* The `union withsource=TableName` adds a column showing which table each row came from. By default, KQL `union` is like SQL UNION ALL (keeps duplicates). Use `union kind=distinct` for deduplication. See more: KQL & Eventhouse

The `parse` operator extracts typed fields from a string column using a literal pattern: Logs | parse Message with "User=" UserName " Action=" Action " IP=" IPAddress This is powerful for semi-structured log data. For JSON, use parse_json() or mv-expand. The `parse-where` variant only keeps rows that match the pattern. `parse` with `regex` supports regex-based extraction. See more: KQL & Eventhouse

An Eventhouse in Fabric is a workspace item that serves as a container for one or more KQL (Kusto Query Language) databases. It is built on the same engine as Azure Data Explorer (ADX) and is purpose-built for real-time, time-series, and log analytics. Use Eventhouse when you need fast ingestion of high-frequency IoT/telemetry/log data and efficient time-series queries at scale. See more: KQL & Eventhouse

Eventhouse with a KQL database is designed for real-time, high-frequency time-series data. It supports high-throughput streaming ingestion, has built-in compression for time-series, and KQL is optimized for time filters and aggregations over time windows. Lakehouse and Warehouse are optimized for batch analytics, not real-time streams. Use Eventhouse for IoT telemetry, logs, clickstreams, and sensor data. See more: KQL & Eventhouse

iff(condition, trueValue, falseValue) evaluates a boolean condition and returns corresponding values: StormEvents | extend SeverityLabel = iff(DamageProperty > 1000000, "Major", "Minor") For multiple conditions, use case(): | extend SeverityLabel = case(DamageProperty > 1000000, "Major", DamageProperty > 100000, "Moderate", "Minor") `case()` is the KQL equivalent of SQL CASE WHEN. See more: KQL & Eventhouse

In Fabric, KQL database tables have an "OneLake availability" setting. Enabling this mirrors the table data continuously to a OneLake Delta table. That Delta path can then be referenced as a shortcut in a Lakehouse or queried by a Fabric Warehouse - enabling unified access to real-time data across Fabric experiences without copying. This is a key Fabric multi-modal analytics pattern. See more: KQL & Eventhouse

RLS allows report authors to define DAX filter expressions per role so each user sees only the rows they are authorized to see. For example, a sales manager sees only their region's data. Roles are defined in Power BI Desktop (Modeling tab -> Manage Roles), and users or groups are assigned to roles in the Power BI Service. Dynamic RLS uses USERPRINCIPALNAME() or USERNAME() to filter based on the logged-in user without separate roles per person. See more: Security & Governance

OLS (Object-Level Security) restricts visibility of entire tables or columns. Users in a role with OLS applied cannot see the restricted table/column in any report or query - it appears as if the object doesn't exist. OLS is configured in Tabular Editor or SSMS using SSAS Tabular model permissions. Unlike RLS (which filters rows), OLS hides structural objects entirely. Both can be used together. See more: Security & Governance

In Fabric Data Warehouse, column-level security is enforced using T-SQL GRANT/DENY: DENY SELECT ON dbo.Customers (SSN, CreditCardNumber) TO ReportRole; Users in ReportRole cannot SELECT those columns. Dynamic Data Masking is a related feature that partially obfuscates column values (shows ***) rather than completely denying access. Both CLS and masking are configured in the Fabric Warehouse SQL editor. See more: Security & Governance

Sensitivity labels from Microsoft Purview Information Protection can be applied to Power BI datasets, reports, dashboards, and other Fabric items. Labels like Public, General, Confidential, and Highly Confidential/Restricted classify data sensitivity. Labels can propagate automatically: if a dataset has Confidential, a report built on it inherits Confidential. Labels can enforce encryption when content is exported to Excel, PDF, or .pbix files. See more: Security & Governance

Fabric deployment pipelines have three stages: Development -> Test (Staging) -> Production. Each stage is mapped to a separate Fabric workspace. Changes are promoted forward - a developer publishes to Dev, a tester validates in Test, then a release manager deploys to Production. This prevents untested changes from going directly to Production and enables controlled release management. See more: Security & Governance

Deployment rules (also called binding rules) let you configure stage-specific overrides. Common uses: (1) Data source rule - point Production datasets to the production server connection instead of dev server, (2) Parameter rule - change Power Query parameters per stage. Without rules, deploying a dataset to Production would use the developer's personal data source credentials, which is incorrect. Rules ensure each stage uses the appropriate configuration. See more: Security & Governance

There are two endorsement levels in Fabric: - Promoted: Set by dataset/report owners to indicate the content is ready for broader use. No special permissions required. - Certified: Requires a Fabric tenant admin to enable certification and designate authorized certifiers. A certified item signals it has passed organizational quality vetting (documentation, data accuracy, support). Both appear as badges in the Fabric portal and improve discoverability in search results. See more: Security & Governance

Workspace roles (Admin/Member/Contributor/Viewer) grant broad access to all items in a workspace. Item-level permissions let you share a specific item (semantic model, report, Lakehouse, Warehouse) with a user who is not a workspace member. For semantic models, you can grant Build permission so users can create new reports on top of the dataset without workspace membership. For Lakehouse, you can grant ReadData or ReadAll at item level. See more: Security & Governance

In KQL, now() returns the current UTC datetime. Useful for relative time filters: StormEvents | where StartTime > ago(7d) StormEvents | where StartTime between (datetime(2024-01-01) .. now()) The ago() function returns a datetime relative to now - ago(1d) is 24 hours ago, ago(1h) is 1 hour ago, ago(30m) is 30 minutes ago. These are essential patterns for real-time dashboards and monitoring queries. See more: KQL & Eventhouse

The four workspace roles and their capabilities: - Admin: Full control - manage settings, add/remove any member (including other Admins), delete workspace, publish/manage all content. - Member: Publish and manage content, add Members/Contributors/Viewers (but not Admins). - Contributor: Create, edit, delete content they own; cannot manage settings or members. - Viewer: Read-only access to items in the workspace; cannot create or edit. Workspace roles apply to all items in the workspace. See more: Security & Governance

Direct report sharing grants a user access to exactly one report without workspace membership. The shared user can view the report and its underlying dataset (for the purposes of rendering the report), but cannot navigate the workspace, see other reports, or access raw data. This is the principle of least privilege applied to report distribution. For more granular data control, combine direct sharing with RLS so shared users see only their authorized rows. See more: Security & Governance