DP-500 cheat sheet

Azure Synapse Analytics dedicated SQL pools store data in a distributed MPP engine; table distribution options are hash, round-robin, and replicated, chosen by join patterns and table size.

Hash distribution on the most frequently joined large-fact column minimizes data movement; round-robin suits staging tables with no clear join key; replicated suits small dimension tables (guideline: under ~2 GB).

Clustered columnstore indexes are the default for dedicated SQL pool tables and deliver best compression and scan performance for large fact tables; use heap or clustered index for small staging tables.

Azure Data Lake Storage Gen2 hierarchical namespace enables directory-level ACLs and POSIX permissions; Blob storage without it supports only flat RBAC and SAS access.

Synapse serverless SQL pools bill per TB of data processed, not per provisioned capacity; use OPENROWSET with FORMAT='PARQUET' and predicate pushdown to limit scanned bytes.

Azure Data Factory mapping data flows run on Spark clusters; integration runtime choice (Azure, self-hosted, SSIS) determines network path and on-premises connectivity.

Delta Lake on ADLS Gen2 adds ACID transactions, schema enforcement, and time travel via the transaction log (_delta_log); Databricks and Synapse Spark both read it.

Partitioning strategy: partition large fact tables by date at a granularity matching query filters (monthly for multi-year, daily for recent-window queries); over-partitioning degrades performance.

Star schema is the target for analytical models: fact tables hold measures and foreign keys, dimension tables hold descriptive attributes; avoid snowflaking unless normalization is required.

Microsoft Purview Data Map stores assets, classifications, and lineage; the Data Catalog surfaces them, and sensitivity labels can be applied to files and columns.

Purview scans use a managed integration runtime or self-hosted IR; scan rule sets define file types, classification rules, and the schedule.

Azure RBAC roles (Owner, Contributor, Reader, Storage Blob Data Reader/Contributor) and POSIX ACLs in ADLS Gen2 both apply; effective access is the union for the same principal, and ACLs can only be more permissive when combined with RBAC.

Row-level security in dedicated SQL pools uses a predicate function with CREATE SECURITY POLICY; column-level security uses GRANT SELECT ON table(column) syntax.

Dynamic data masking in Synapse/SQL masks data at query time for non-privileged users; masking does not encrypt data at rest and privileged users see unmasked values.

Always Encrypted protects data in use, in transit, and at rest; column master key stored in Key Vault, column encryption key in the database; deterministic vs randomized encryption affects equality searches.

Microsoft Defender for Cloud and Defender for SQL provide threat detection, vulnerability assessment, and anomaly alerts; auditing writes to a storage account, Log Analytics, or Event Hub.

Purview lineage captures ADF, Synapse, and Databricks activities automatically when workspaces are connected; custom lineage can be pushed via the Atlas API.

Managed identity (system-assigned or user-assigned) is preferred over service principals with secrets for Synapse, ADF, and Databricks to access ADLS Gen2 and Key Vault.

Kimball dimensional modeling: conformed dimensions shared across fact tables, grain declared at the fact table level, surrogate keys for dimension members, and slowly changing dimension types 1 (overwrite), 2 (history rows with effective dates), and 3 (prior-value column).

Data Vault 2.0 composes hubs (business keys), links (relationships), and satellites (descriptive attributes with load timestamps); it favors auditability and parallel loading over query simplicity.

Tabular models in Power BI and Azure Analysis Services use a star schema; relationships should be one-to-many from dimension to fact with single-direction filtering unless bidirectional is strictly required.

Calculated columns are computed at processing time and stored; measures are computed at query time. Prefer measures for aggregations and calculated columns only for slicing attributes.

DAX time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, DATEADD) require a contiguous, marked date table with a relationship to the fact table.

Composite models allow DirectQuery and Import tables in one model; set storage mode per table and manage dual-mode tables to control cross-source joins.

Aggregation tables in Power BI store pre-aggregated data and are hit automatically when queries match; configure them via the aggregation table feature with a detail-table relationship.

Snowflake vs star: normalize dimensions only when attribute cardinality or governance demands it; flattening improves Power BI performance because fewer relationships mean fewer joins.

Incremental refresh in Power BI uses RangeStart/RangeEnd parameters and partitions the table by date; it requires the source to support filtering by those parameters.

Power BI Premium capacities (P SKUs) and Fabric capacities (F SKUs) provide dedicated compute; workspaces assigned to a capacity support larger models, paginated reports, and deployment pipelines.

Deployment pipelines have Development, Test, and Production stages; rules allow parameter overrides per stage, and deployment moves content between stages without rebuilding.

XMLA read/write endpoints enable external tools (Tabular Editor, ALM Toolkit) and programmatic partition management; read/write is required for advanced refresh and TMSL scripts.

Large semantic models (up to 400 GB on Premium) require XMLA read/write, incremental refresh, and often a composite or DirectQuery strategy for source-side filtering.

Power BI dataflows (Gen2) store transformed entities in Azure Data Lake Storage Gen2; computed entities and linked entities let multiple datasets reuse the same ETL.

Row-level security in Power BI: roles defined in the model with DAX filters; users added to roles in the service; use USERNAME() or USERPRINCIPALNAME() for dynamic rules and validate with 'View as role'.

Workspace roles: Admin, Member, Contributor, Viewer. Viewer can read content; Contributor can publish; Member can manage access; Admin can delete the workspace.

DirectQuery sends queries to the source on each visual interaction; Import caches data in memory. Dual mode tables act as Import or DirectQuery depending on the query.

Power BI embedded and the Power BI REST API support app-owns-data and user-owns-data embedding; service principal authentication requires tenant settings enabled.

Query performance in dedicated SQL pools: inspect sys.dm_pdw_exec_requests and sys.dm_pdw_request_steps for elapsed time and step distribution; look for Remote Scan and Shuffle Move operations indicating data movement.

Rebuild or reorganize columnstore indexes when fragmented; use ALTER INDEX ... REBUILD to restore compression. Check sys.dm_db_column_store_row_group_physical_stats for row group states.

Statistics: dedicated SQL pools create statistics automatically but stale stats cause poor plans; update with UPDATE STATISTICS or CREATE STATISTICS on join/filter columns.

Result-set caching in dedicated SQL pools caches query results for repeated identical queries; materialized views precompute aggregations and require automatic or manual refresh.

Synapse workload management: workload groups and classifier functions route queries to resource classes (small, medium, large, xlarge) controlling memory and concurrency.

Power BI performance: use Performance Analyzer to capture DAX query times per visual; reduce visual count, avoid high-cardinality columns, and remove unused columns to shrink the model.

DAX optimization: prefer SUMX over nested iterators, avoid FILTER over large tables when CALCULATE with a predicate suffices, and use variables (VAR) to avoid recomputation.

Azure Monitor and Log Analytics collect Synapse and ADF diagnostic logs; set diagnostic settings to send metrics and logs to a workspace, storage account, or Event Hub.

ADF monitoring: the Monitor hub shows pipeline and activity runs; integration runtime monitoring shows node CPU and queue depth for self-hosted IR; alert on failure rates via Azure Monitor alerts.