DP-500 flashcards

38 cards. Read the term and the definition here. The deck at the top of this page is optional.

Hash distribution

A dedicated SQL pool table distribution that places rows with the same value of the distribution column on the same compute node, eliminating data movement for joins on that column.

Replicated table

A dedicated SQL pool distribution that copies the full table to every compute node, suited to small dimension tables under roughly 2 GB to avoid shuffle operations during joins.

Serverless SQL pool billing

Synapse serverless SQL pools charge per terabyte of data processed by each query, not per provisioned hour, so filtering and column pruning directly reduce cost.

Clustered columnstore index

A column-oriented index storing data in compressed row groups, delivering high compression and fast large scans; it is the default table type in dedicated SQL pools.

Delta Lake transaction log

The _delta_log directory records every commit as JSON, enabling ACID transactions, schema enforcement, time travel, and consistent reads for Spark and Synapse workloads.

Star schema

A dimensional model with central fact tables holding measures and foreign keys, surrounded by denormalized dimension tables holding descriptive attributes used for filtering and grouping.

ADLS Gen2 hierarchical namespace

A storage feature that organizes blobs into directories and enables POSIX ACLs at directory and file level, required for fine-grained access control in data lakes.

Mapping data flow

An Azure Data Factory feature that runs visually designed transformations on a Spark cluster, supporting schema drift, joins, and sinks to lake or warehouse targets.

Purview scan rule set

A configuration in Microsoft Purview that defines which file types to scan, which classification rules to apply, and how often a registered data source is scanned.

Effective ACL plus RBAC access

In ADLS Gen2, a principal's access is the union of RBAC role assignments and POSIX ACL grants; ACLs cannot restrict access already granted by RBAC.

Row-level security predicate

A dedicated SQL pool security policy applies an inline table-valued function to filter rows per user, enforced for all queries including those from reporting tools.

Dynamic data masking

A policy that obfuscates column values in query results for non-privileged users while leaving stored data unchanged; privileged users see the original values.

Always Encrypted

A feature where the client driver encrypts and decrypts column data, so the database engine never sees plaintext; keys live in Key Vault and the database.

Purview lineage

A record of data movement from source to destination, captured automatically from connected ADF, Synapse, and Databricks workspaces or pushed through the Atlas API.

Managed identity for data access

An Azure AD identity assigned to a service such as Synapse or ADF, allowing token-based access to storage and Key Vault without storing secrets in code.

Defender for SQL alerts

Threat detection that monitors database activity for anomalous patterns such as SQL injection attempts and unusual access locations, raising alerts in Defender for Cloud.

Slowly changing dimension type 2

A dimension design that preserves history by inserting a new row with new surrogate key, effective dates, and a current-flag column whenever an attribute changes.

Data Vault hubs and links

Hubs store unique business keys, links store relationships between hubs, and satellites store descriptive attributes with load timestamps, supporting auditable parallel loading.

DAX calculated column versus measure

A calculated column is evaluated at processing time and stored in the model; a measure is evaluated at query time and responds to slicer and filter context.

Marked date table

A contiguous date table marked as a date table in the model, required for DAX time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR to work correctly.

Aggregation table

A Power BI table storing pre-aggregated values at a coarser grain, automatically used when a query can be answered from it, reducing scans of the detail table.

Composite model

A Power BI model combining Import, DirectQuery, and dual-mode tables, allowing cross-source relationships while controlling where each table's data is stored.

Incremental refresh parameters

Power BI incremental refresh requires RangeStart and RangeEnd parameters of date type used in a filter, partitioning the table and refreshing only recent partitions.

Bidirectional relationship risk

Bidirectional filtering lets a dimension filter flow to another dimension through a fact, which can create ambiguous paths and slow queries; prefer single direction.

Deployment pipeline stages

Power BI deployment pipelines assign workspaces to Development, Test, and Production stages, moving content between them and applying stage-specific rules for parameters.

XMLA read-write endpoint

A Premium or Fabric capacity setting that exposes the tabular model for external tools and scripts, enabling partition management and advanced refresh operations.

Power BI workspace roles

Admin, Member, Contributor, and Viewer roles control workspace permissions; Viewer reads content, Contributor publishes, Member manages access, Admin deletes the workspace.

DirectQuery versus Import

Import caches data in memory for fast queries; DirectQuery sends each visual's query to the source, keeping data current but depending on source performance.

Power BI dataflow Gen2

A cloud ETL object storing transformed entities in Azure Data Lake Storage Gen2, reusable by multiple datasets through linked and computed entities.

Dynamic RLS with USERPRINCIPALNAME

A row-level security pattern where a mapping table links user principal names to allowed dimension values, filtered by a DAX expression evaluated per signed-in user.

Remote Scan and Shuffle Move

Execution step types in dedicated SQL pool request steps that indicate data movement between nodes; their presence signals a distribution or join key problem.

Workload groups and classifiers

Synapse features that route queries to resource classes based on classifier rules, controlling memory allocation and concurrency for different user or workload types.

Result-set caching

A dedicated SQL pool feature that returns cached results for identical repeated queries within a retention window, bypassing computation and reducing elapsed time.

Performance Analyzer

A Power BI Desktop tool that records the duration of each visual's DAX query and rendering, identifying the slowest visuals and queries for optimization.

Columnstore row group health

Row groups in a compressed state perform best; the physical stats DMV shows open, closed, and compressed groups, and rebuilding merges small groups to restore compression.

Synapse diagnostic settings

Configuration that streams workspace logs and metrics to Log Analytics, a storage account, or an event hub for query monitoring, alerting, and long-term retention.

DAX variables

VAR declarations in a DAX expression evaluate once and reuse the result, avoiding repeated computation of the same subexpression and improving readability and performance.

Self-hosted integration runtime monitoring

The ADF monitoring view reports node CPU, memory, and queue depth for a self-hosted integration runtime, indicating when additional nodes are needed for throughput.