DP-203 flashcards
DP-203 · Build recall with a focused review session.
33 cards. Read the term and the definition here. The deck at the top of this page is optional.
- Hash distribution
- Synapse dedicated pool distribution that assigns rows to distributions by a hash of one column; best for large fact tables with a high-cardinality join key and even distribution.
- Replicated table
- A dedicated pool table copied to every compute node; suited to small dimension tables under about 2 GB compressed; writes replicate to all nodes, so avoid frequent loads.
- Partition switching
- ALTER TABLE SWITCH moves a partition between tables without data movement; requires identical filegroup, partition function, and schema on both tables.
- Delta transaction log
- The _delta_log directory records every change as JSON commits, providing ACID guarantees, time travel, and schema enforcement for Delta Lake tables.
- Serverless SQL billing
- Synapse serverless SQL pool charges per terabyte of data processed; predicate pushdown and partitioned folders reduce scanned bytes and cost.
- Tumbling window trigger
- An ADF trigger that runs on a fixed schedule, is stateful, supports historical backfill, and allows dependencies on other windows; event triggers are stateless.
- Auto Loader
- Databricks cloudFiles source that incrementally ingests new files using directory listing or file notification, with schema inference and evolution via a schema location.
- Watermark
- A Structured Streaming bound on how late data may arrive; events older than the watermark are dropped from stateful aggregations, limiting state size.
- Coalesce vs repartition
- Coalesce reduces partition count without a full shuffle; repartition performs a shuffle and can increase or decrease partitions and balance data.
- Dynamic data masking
- A SQL feature that masks column values in query results for unauthorized users; it does not encrypt data at rest, and db_owner or UNMASK sees originals.
- Row-level security
- A predicate function bound by a security policy; FILTER hides rows from queries, BLOCK raises errors on writes; applies to all users except sysadmin and db_owner.
- Resource class
- A dedicated pool setting (smallrc through xlargerc) that caps memory per query; larger classes give more memory but reduce concurrency at a given DWU.
- Result-set caching
- A dedicated pool feature that stores query output for up to 48 hours; the cache is invalidated when underlying table data changes and is configured per database or workload group.
- Materialized view
- A precomputed aggregation with automatic refresh; requires a clustered columnstore index and cannot reference other views; improves repeated aggregation queries.
- Star schema
- A model with denormalized dimension tables holding descriptive attributes and fact tables holding measures and foreign keys; fewer joins than a snowflake schema.
- SCD Type 2
- A dimension technique that preserves history by inserting a new row with effective dates and a current flag when an attribute changes, instead of overwriting.
- Delta MERGE
- A Delta Lake statement that upserts rows using WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT; requires a match condition to avoid duplicate inserts.
- foreachBatch
- A Structured Streaming sink method that runs custom logic per micro-batch, commonly used to MERGE streaming data into a Delta table for upserts.
- Adaptive Query Execution
- A Spark feature that coalesces shuffle partitions and switches to broadcast joins at runtime; enabled with spark.sql.adaptive.enabled=true.
- Broadcast join
- A Spark join that replicates a small table to every executor to avoid shuffling the large table; controlled by spark.sql.autoBroadcastJoinThreshold.
- Always Encrypted
- A SQL feature protecting data in use; the column master key lives in Key Vault and the column encryption key in the database; deterministic encryption allows equality joins.
- Column-level security
- GRANT SELECT ON table(column) restricts access to specific columns; not supported on columnstore tables in dedicated SQL pools.
- Workload management
- Dedicated pool classifiers route queries to workload groups that set importance and resource caps, isolating ETL from BI queries.
- Data exfiltration protection
- A Synapse workspace setting that restricts outbound connections from the managed virtual network to approved targets, preventing data movement to unapproved destinations.
- Managed identity
- An Azure AD identity assigned to a resource such as a Synapse workspace; used to access storage without storing secrets, granted via RBAC roles.
- PolyBase external table
- A dedicated pool table that maps to files in ADLS using an external data source and file format; used to query lake data without loading it.
- CETAS
- CREATE EXTERNAL TABLE AS SELECT in Synapse serverless writes query results to Parquet files in the lake, enabling reusable transformed datasets.
- OPENROWSET
- A Synapse serverless function that reads files directly from the lake with a format and schema; supports ad hoc queries and schema inference.
- Data flow debug
- An ADF session that starts a Spark cluster for data flow preview; it incurs cost while active and should be stopped after use.
- Schema drift
- A data flow setting that allows columns present in the source but absent from the defined schema; paired with the byName column pattern for resilient mappings.
- Delta Live Tables expectations
- Data quality rules in DLT: expect warns, expect_or_drop drops violating rows, and expect_or_fail fails the pipeline; metrics appear in the DLT UI.
- Purview lineage
- Microsoft Purview captures data movement lineage automatically for ADF copy and data flow activities; Synapse and Databricks lineage require configuration.
- Synapse data sharing
- A feature that exposes dedicated pool tables to other workspaces without copying data, using a share and a shared database.