DP-203 cheat sheet

Azure Synapse dedicated SQL pool distributions: HASH for large fact tables on a high-cardinality join key; ROUND_ROBIN for staging or no clear key; REPLICATE for small dimension tables under ~2 GB per compute node.

Clustered columnstore index (CCI) suits large fact tables (>= 1 million rows); use heap or clustered index for small tables, and add secondary indexes sparingly because each rebuilds on load.

Partition switching requires identical filegroup, partition function, and schema; use ALTER TABLE ... SWITCH to move a partition in or out without data movement.

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

Delta Lake ACID guarantees come from the transaction log (_delta_log); OPTIMIZE compacts small files, ZORDER BY co-locates related data, VACUUM removes unreferenced files after the retention window.

Synapse serverless SQL pool charges per TB of data processed; OPENROWSET with FORMAT='PARQUET' and predicate pushdown on partitioned folders minimizes scanned bytes.

Star schema: dimension tables hold descriptive attributes with surrogate keys; fact tables hold measures and foreign keys; snowflaking normalizes dimensions and adds joins.

Cosmos DB partition key choice: high cardinality, even write distribution, and included in common query filters; a logical partition caps at 20 GB.

Azure Data Factory mapping data flows run on Spark clusters; use the Optimize tab to set partition type (round robin, hash, fixed) and broadcast small tables in joins.

ADF copy activity parallelism is controlled by parallelCopies and dataIntegrationUnits; the DIU count scales throughput for cloud-to-cloud copies.

Tumbling window triggers in ADF are stateful, run on a fixed schedule, and support backfill and dependency chains; event triggers fire on blob creation and are stateless.

Databricks Auto Loader (cloudFiles) incrementally ingests new files using directory listing or file notification; schema inference and evolution are supported via cloudFiles.schemaLocation.

Structured Streaming output modes: append adds new rows only; update emits changed rows; complete rewrites the full result table and requires an aggregation.

Watermarking in Structured Streaming bounds state by dropping data older than the watermark; withOutputMode append requires a watermark for aggregation output.

Spark partitioning: repartition shuffles data and changes partition count; coalesce reduces partitions without a full shuffle and is used before writes.

Synapse Spark notebooks integrate with dedicated SQL pools via the connector; use staging tables with CTAS for bulk loads and PolyBase for external tables.

Dynamic data masking hides data in query results but does not encrypt at rest; unmasked users with db_owner or UNMASK permission see original values.

Row-level security uses a predicate function with CREATE SECURITY POLICY; FILTER predicates hide rows, BLOCK predicates raise errors on write.

Always Encrypted protects data in use; column master key stored in Key Vault, column encryption key in the database; deterministic encryption allows equality joins.

Synapse SQL audit logs and diagnostic settings route to Log Analytics, Storage, or Event Hubs; enable SQL analytics metrics to track DWU usage and queued queries.

Resource classes in dedicated SQL pools (smallrc through xlargerc) cap memory per query; a larger resource class helps memory-intensive operations like large hash joins.

Result-set caching in dedicated SQL pools stores query output for up to 48 hours; it is invalidated when underlying table data changes.

Materialized views with automatic refresh improve repeated aggregation queries; they require a clustered columnstore index and cannot reference other views.

Azure Monitor alerts on pipeline failed runs, Spark metrics, and DTU or DWU thresholds; Log Analytics KQL queries join ADF pipeline runs with activity runs for root cause.

Synapse dedicated SQL pool table types: clustered columnstore for facts, replicated for small dimensions, hash-distributed for large joins; heap for staging loads.

Statistics: CREATE STATISTICS on columns used in joins and filters; auto-create statistics is on by default in dedicated pools but sampled, so manual stats improve plans.

Delta Lake time travel reads prior versions via VERSION AS OF or TIMESTAMP AS OF; retention is bounded by the log retention interval (default 30 days).

Parquet vs Delta: Parquet lacks transactions and schema enforcement; Delta adds the log, schema evolution, and MERGE support for upserts.

ADLS Gen2 access control: RBAC roles (Storage Blob Data Reader/Contributor/Owner) apply at management scope; ACLs apply at directory and file scope; most restrictive wins unless a mask grants access.

Synapse link to Cosmos DB enables analytical queries without impacting transactional throughput; the analytical store uses columnar format.

File formats: Avro row-based with schema, ORC columnar optimized for Hive, Parquet columnar with predicate pushdown; choose columnar for analytics, row-based for frequent full-row writes.

Delta MERGE INTO performs upserts: WHEN MATCHED THEN UPDATE, WHEN NOT MATCHED THEN INSERT; requires a unique key match condition to avoid duplicate inserts.

ADF data flow sink partitioning options: round robin, hash, key, and single partition; single partition writes one file and hurts downstream parallelism.

Mapping data flow transformations: Derived Column adds or modifies columns; Aggregate groups and aggregates; Surrogate Key generates monotonically increasing keys; Flatten explodes arrays.

Databricks job clusters terminate after the job; all-purpose clusters persist for interactive use; use job clusters for scheduled pipelines to reduce cost.

Structured Streaming checkpoints store offsets and state; a checkpoint location must be unique per stream and stored in durable storage (ADLS or DBFS).

Spark caching: cache() stores in memory and disk; persist(StorageLevel) allows control; unpersist frees memory; caching is beneficial for reused DataFrames across iterations.

ADF integration runtime types: Azure (cloud-to-cloud), self-hosted (on-premises), and Azure-SSIS; linked services reference the IR that can reach the data store.

Watermark and late data: withWatermark('ts','10 minutes') allows data up to 10 minutes late; events older than the watermark are dropped from stateful aggregations.

Column-level security uses GRANT SELECT ON table(col) TO user; columnstore tables do not support column-level security in dedicated SQL pools.

Managed identity authentication to ADLS avoids storing secrets; grant the Synapse workspace or Databricks workspace identity the Storage Blob Data Contributor role.

Query performance troubleshooting in dedicated pools: check sys.dm_pdw_exec_requests for queued queries, sys.dm_pdw_request_steps for step timing, and look for data movement (ShuffleMoveOperation).

Skew detection: DBCC PDW_SHOWSPACEUSED or sys.dm_pdw_nodes_db_partition_stats reveal uneven row distribution; a skewed hash key causes one distribution to dominate.

Data flow monitoring: the ADF monitoring hub shows per-activity duration, rows read/written, and data flow partition statistics for bottleneck identification.

Databricks cluster sizing: driver handles orchestration, workers handle tasks; autoscaling adjusts worker count between min and max; photon accelerates SQL and columnar workloads.

Azure Monitor diagnostic settings capture Synapse SQL request and step logs; use KQL to find long-running queries and correlate with resource utilization.

Key Vault integration: use a linked service with managed identity to retrieve secrets at runtime; never embed credentials in pipeline parameters or notebooks.

Synapse dedicated pool hash distribution column choice: high cardinality, even distribution, used in frequent joins, and not used in small lookup tables.

Replicated tables: full copy on every compute node; best for tables under 2 GB compressed; avoid writes to replicated tables because each write replicates.

PolyBase external tables: CREATE EXTERNAL DATA SOURCE points to ADLS, CREATE EXTERNAL FILE FORMAT defines Parquet or DelimitedText, CREATE EXTERNAL TABLE maps the schema.

CTAS creates a new table from a query and is the fastest way to bulk-load a dedicated pool; supports distribution and partition options in the WITH clause.

Delta Lake schema evolution: mergeSchema=true on write or write with overwriteSchema; ALTER TABLE ADD COLUMNS is supported; schema enforcement rejects mismatched writes by default.

ADLS Gen2 soft delete and versioning protect against accidental deletion; configure retention at the storage account level.

Synapse SQL pool restore points and geo-backup: user-defined restore points via the portal or PowerShell; geo-backup restores to a paired region.

Cosmos DB consistency levels from strongest to weakest: Strong, Bounded Staleness, Session, Consistent Prefix, Eventual; Session is the default.

ADF tumbling window triggers: stateful, historical backfill, retry, and dependency on other windows; event triggers are stateless and cannot backfill.

Data flow debug session spins up a Spark cluster for preview; it incurs cost while active and should be stopped after use.

ADF copy activity with polyBaseSettings or copyCommandSettings chooses the bulk load method; PolyBase requires a staging blob for Synapse.

Spark join strategies: broadcast join for small tables (auto via spark.sql.autoBroadcastJoinThreshold), sort-merge join for large tables, and shuffle hash join when one side is small but not broadcast-eligible.

Structured Streaming foreachBatch allows custom sink logic per micro-batch, including MERGE into Delta for upserts.

Databricks Delta Live Tables: streaming tables and materialized views defined in SQL or Python; expectations enforce data quality and drop or fail on violation.

ADF parameterization: pipeline parameters, dataset parameters, and global parameters; use them to build reusable pipelines across environments.

Spark Adaptive Query Execution (AQE) coalesces shuffle partitions and switches to broadcast joins at runtime; enable with spark.sql.adaptive.enabled=true.

Synapse workspace managed identity and SQL admin: the workspace identity can be granted ADLS access; the SQL admin login controls dedicated pool permissions.

Sensitivity labels and classification: SQL classification scans columns for patterns; labels propagate to reports but do not enforce access.

Auditing in dedicated SQL pools: enable at the server level to write to Storage, Log Analytics, or Event Hubs; captures successful and failed logins and DDL.

Cost optimization: pause dedicated SQL pools when idle; serverless pools bill per TB scanned; use result-set caching and materialized views to reduce scans.

Databricks cluster policies and tags enable chargeback; spot instances reduce worker cost but can be preempted; use driver on-demand for reliability.

Query store and DMVs: sys.dm_pdw_exec_requests shows active and recent queries; sys.dm_pdw_waits shows lock and resource waits.

Data exfiltration protection in Synapse workspaces restricts outbound connections to approved targets; managed virtual network controls egress.

Private endpoints place Synapse and storage on a virtual network; public network access can be disabled to enforce private connectivity.

Synapse dedicated pool partitioning: partition on a date column for large fact tables; each distribution has its own partitions; too many partitions (thousands) increases metadata overhead.

Clustered columnstore index rowgroup quality: aim for 1 million rows per rowgroup; use REORGANIZE to merge small rowgroups and REBUILD to defragment.

Delta Lake liquid clustering (or ZORDER) co-locates related data for skip; ZORDER works on columns with high cardinality used in filters.

ADLS Gen2 lifecycle management rules transition or delete blobs by last-modified or last-access time; applies to base blobs, not snapshots.

Synapse serverless: CREATE EXTERNAL TABLE with a folder path and FORMAT; views over OPENROWSET allow reuse; filepath() returns the source file path for filtering.

Cosmos DB analytical store is columnar and auto-synced; Synapse link queries it with T-SQL; it does not affect transactional RU consumption.

Azure SQL and Synapse: temporal tables track history; ledger tables provide tamper-evident audit; choose based on compliance requirements.

Data Lake Storage Gen2 ACL inheritance: new files inherit parent directory ACLs; use the default ACL to control inheritance and access ACL for the directory itself.

ADF mapping data flow joins: inner, left outer, right outer, full outer, and cross; broadcast small tables via the Optimize tab to avoid shuffling large tables.

Data flow sink file naming: use the 'File name option' (default, pattern, or single) and partition settings to control output file count.

ADF copy activity fault tolerance: skip incompatible rows, log them to storage, and set the fault tolerance threshold.

Structured Streaming sources: Kafka, Event Hubs, Delta, file; sinks: Delta, Parquet, console, memory, foreachBatch; exactly-once with Delta sinks via idempotent writes.

Databricks Autoloader file notification mode uses Event Grid and Queue Storage; directory listing mode scans the directory on each trigger.

Spark SQL functions: explode flattens arrays, posexplode returns position and value, from_json parses JSON strings with a schema.

ADF data flow schema drift: enable 'Allow schema drift' to handle columns that appear in source but not in the defined schema; use the 'byName' column pattern.

Delta Live Tables expectations: expect drops rows, expect_or_fail fails the pipeline, expect_or_drop logs metrics; use for data quality gates.

Synapse dedicated pool workload management: classifier assigns queries to workload groups; workload groups set importance and resource caps; use to isolate ETL from BI queries.

Resource class and concurrency: dedicated pools have fixed concurrency by DWU; larger resource classes reduce concurrency but give more memory per query.

Query plan analysis: look for Table Scan on large tables (missing stats or index), BroadcastMoveOperation (small table replicated), and ShuffleMoveOperation (repartitioning).

Azure Monitor metrics for Synapse: DWU used percent, active queries, queued queries, and connections; alert on queued queries and DWU saturation.

Databricks Spark UI: stages tab shows shuffle read/write and spill; a skewed task duration indicates data skew; fix with salting or repartition.

ADF self-hosted integration runtime high availability: install multiple nodes and associate them with one IR for failover.

Key Vault access policies vs RBAC: choose one model per vault; managed identity needs Get and List secret permissions.

Log Analytics KQL for ADF: ADFPipelineRun and ADFActivityRun tables join on RunId; filter by Status == 'Failed' to find failed runs.

Synapse dedicated pool vs serverless: dedicated has provisioned DWU and persistent tables; serverless queries files in the lake and bills per TB scanned.

External tables vs OPENROWSET in serverless: external tables require a defined schema and are better for repeated queries; OPENROWSET is ad hoc and supports schema inference.

Delta Lake as a source for Synapse: serverless can query Delta via OPENROWSET with FORMAT='DELTA'; dedicated pools need PolyBase external tables or CETAS.

CETAS (CREATE EXTERNAL TABLE AS SELECT) in serverless writes query results to Parquet in the lake; use for transforming lake data.

ADLS Gen2 and Synapse: grant the workspace managed identity Storage Blob Data Contributor; serverless uses the identity for OPENROWSET access.

Cosmos DB partition key and Synapse link: the analytical store requires the container to enable analytical TTL; Synapse link reads it without RU cost.

Data lake zones: raw/bronze, curated/silver, and serving/gold; each zone has distinct ACLs and retention policies.

Azure SQL Database elastic pools share DTUs or vCores across databases; use for multi-tenant SaaS with variable peak loads.

ADF pipeline vs data flow: pipelines orchestrate activities; data flows run Spark transformations; use data flows for complex transformations, copy for simple movement.

ADF linked service vs dataset: linked service defines the connection; dataset defines the structure and references the linked service.

ADF triggers: schedule, tumbling window, event, and custom; only tumbling window is stateful and supports backfill.

Mapping data flow source options: inline dataset, dataset reference, and parameterized; use parameters to reuse flows across environments.

Databricks notebooks vs jobs: notebooks are interactive; jobs schedule notebooks or JARs with cluster configuration and retries.

Structured Streaming trigger modes: processingTime (default), once, availableNow, and continuous; availableNow processes all available data and stops.

ADF data flow sink: use 'Optimize' to set partitioning; single partition for small outputs, hash for large joins, round robin for even distribution.

Spark write modes: append, overwrite, errorIfExists, ignore; Delta adds mergeSchema and overwriteSchema options.

Synapse SQL pool permissions: CONTROL, ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE; grant at database, schema, or object scope; db_owner bypasses masking.

ADLS Gen2 RBAC vs ACL: RBAC applies to the whole account or container; ACLs apply to directories and files; a user needs Execute on parent directories to traverse.

Managed private endpoints in Synapse connect to storage and other services privately; approved from the target resource.

Data masking in Synapse: MASKED WITH (FUNCTION='default()') on a column; users with UNMASK see original data.

Azure Monitor alerts: metric alerts on DWU and queued queries; log alerts on ADF failed runs via KQL.

Databricks secret scopes: backed by Key Vault or Databricks; reference secrets with dbutils.secrets.get; never print secrets.

Cost monitoring: Azure Cost Management budgets and alerts; tag resources by environment and project for chargeback.

Query performance: use result-set caching and materialized views in dedicated pools; avoid SELECT * on columnstore tables to preserve column pruning.

Microsoft Purview scans Synapse, ADLS, and Azure SQL to build a data map; classifications and sensitivity labels support governance.

Purview lineage captures ADF copy and data flow lineage automatically; Synapse and Databricks lineage require configuration.

Data retention: ADLS lifecycle rules delete or tier blobs; Synapse tables require manual purge or partition switching to remove old data.

Delta Lake VACUUM default retention is 7 days; time travel beyond retention fails; set delta.logRetentionDuration and deletedFileRetentionDuration appropriately.

Schema evolution in Delta: mergeSchema on write; ALTER TABLE ADD COLUMNS; schema enforcement rejects mismatched writes unless overwriteSchema is set.

Synapse dedicated pool table statistics: auto-create is on; manual CREATE STATISTICS on join and filter columns improves plans; update after large loads.

Data sharing: Synapse data sharing exposes tables to other workspaces without copying; Azure Data Share shares storage snapshots.

Row-level security predicate functions are schema-bound; changing the function requires dropping and recreating the security policy.

ADF data flow data quality: use the 'Assert' transformation or derived column with expressions to validate rows; route bad rows to a separate sink.

Delta Live Tables expectations enforce quality: expect (warn), expect_or_drop (drop row), expect_or_fail (fail pipeline); metrics appear in the DLT UI.

ADF mapping data flow: use 'Conditional Split' to route rows by expression; 'Select' to rename and reorder; 'Sort' and 'Window' for ranking.

Databricks workflows orchestrate notebooks, JARs, and SQL with dependencies; use for multi-task pipelines with retries and alerts.

Structured Streaming deduplication: dropDuplicates within a watermark bounds state; without a watermark state grows unbounded.

Spark schema enforcement: define an explicit schema on read to avoid inference cost and type drift; use from_json with a schema for nested data.

ADF parameterization and global parameters: global parameters are constant across pipelines; pipeline parameters vary per run.

Data flow monitoring: the ADF monitoring hub shows rows read/written and partition statistics; use to detect skew and bottlenecks.

Synapse SQL auditing writes to Storage, Log Analytics, or Event Hubs; enable at server level; captures logins, DDL, and DML depending on the audit action groups.

Microsoft Defender for SQL detects anomalous access and injection attempts; enable at the server or workspace level.

ADLS Gen2 diagnostic logs capture read, write, and delete operations; route to Log Analytics for KQL analysis of access patterns.

Synapse workspace managed virtual network with data exfiltration protection restricts outbound traffic to approved targets; private endpoints for inbound.

Databricks cluster audit logs record cluster creation, termination, and access; export to a log delivery destination for retention.

Query optimization: inspect sys.dm_pdw_request_steps for data movement; eliminate ShuffleMoveOperation by aligning distribution keys with join keys.

Result-set caching: enable at the database or workload group level; cache is invalidated when the underlying table changes; max size configurable.

Cost control: pause dedicated pools, use serverless for ad hoc queries, and set budgets in Cost Management with alerts at thresholds.

Synapse dedicated pool scaling: adjust DWU in the portal or PowerShell; scaling takes minutes and temporarily pauses queries.

Backup and restore: dedicated pools have automatic backups and geo-backups; user-defined restore points via the portal; restore to a new pool.

ADLS Gen2 redundancy: LRS, ZRS, GRS, RA-GRS, GZRS; choose GRS or GZRS for cross-region durability; RA-GRS allows read access to the secondary.

Delta Lake restore: RESTORE TABLE to a version or timestamp; use for rollback after bad writes.

Synapse serverless: no persistent storage; data lives in the lake; use external tables and CETAS for reusable results.

Cosmos DB backup modes: periodic (default) and continuous; continuous supports point-in-time restore within the retention window.

Azure SQL failover groups provide automatic geo-failover and readable secondary endpoints for disaster recovery.

Data lake partitioning: partition by date (year/month/day) for time-series data; too many small files hurt serverless query performance.

ADF pipeline monitoring: the monitoring hub shows run history, duration, and status; use 'Debug' for interactive runs and 'Trigger Now' for production.

ADF retry policy: set retry count and interval on activities; use for transient failures; not for data quality failures.

Databricks job retries: configure max retries and timeout; use repair runs to rerun only failed tasks.

Structured Streaming monitoring: query.lastProgress and query.status via the StreamingQuery object; metrics include input rows, batch duration, and state size.

Spark dynamic allocation: spark.dynamicAllocation.enabled adjusts executors based on workload; combined with autoscaling clusters for cost control.

ADF self-hosted IR: install on a VM for on-premises access; multiple nodes for high availability; update regularly for security patches.

Delta Live Tables pipelines: continuous vs triggered; triggered runs process available data and stop; continuous runs keep the cluster running.

Azure Monitor alerts on ADF: use log alerts with ADFPipelineRun and ADFActivityRun tables to detect failed runs.

Synapse dedicated pool workload isolation: create workload groups and classifiers to route ETL and BI queries; set importance (low, below_normal, normal, above_normal, high).

Concurrency limits: dedicated pools have fixed concurrency by DWU; larger resource classes reduce concurrency; monitor queued queries via sys.dm_pdw_exec_requests.

Query plan red flags: Table Scan on large tables, BroadcastMoveOperation for large tables, ShuffleMoveOperation, and tempdb spills.

Statistics maintenance: update statistics after large loads; use sys.dm_pdw_nodes_db_partition_stats to detect skew.

Databricks Spark UI: a few long tasks in a stage indicate skew; fix with salting, repartition, or broadcast join.

ADLS Gen2 performance: enable hierarchical namespace; use large files (256 MB to 1 GB) to reduce per-file overhead; avoid many small files.

Key Vault: use managed identity with Get and List permissions; rotate secrets and reference via linked services.

Cost alerts: set budgets in Cost Management; tag resources; pause idle dedicated pools and clusters.

Star schema vs snowflake: star has denormalized dimensions and fewer joins; snowflake normalizes dimensions and adds joins; choose star for query performance.

Slowly changing dimensions: Type 1 overwrites, Type 2 adds a new row with effective dates and a current flag, Type 3 adds a previous-value column.

Fact table grain: define the lowest level of detail; avoid mixed grains; use a surrogate key and foreign keys to dimensions.

Synapse dedicated pool: hash-distribute fact tables on the most common join key; replicate small dimensions; partition facts by date.

Delta Lake medallion architecture: bronze (raw), silver (cleaned), gold (aggregated); each layer has distinct schema and quality rules.

Cosmos DB: denormalize for read performance; embed related data in a single document when accessed together; reference when data is large or shared.

Data vault: hubs (business keys), links (relationships), and satellites (attributes with history); use for auditable enterprise models.

Surrogate keys: integer keys generated by the warehouse; natural keys remain in the dimension for traceability.

ADF data flow: use 'Derived Column' for SCD Type 2 effective dates; 'Window' transformation for ranking and row numbers; 'Exists' for lookups.

Delta MERGE for SCD Type 2: match on natural key and current flag; update the old row to expire it and insert the new row.

Databricks: use MERGE INTO with a source DataFrame; for SCD Type 2, use two passes (expire then insert) or a single MERGE with conditions.

Structured Streaming: use foreachBatch to implement MERGE for upserts into Delta; idempotent writes via Delta transactions.

Spark: use window functions (row_number, rank, lag, lead) for deduplication and change detection; partition by the natural key and order by timestamp.

ADF mapping data flow: 'Surrogate Key' transformation generates keys; 'Aggregate' groups; 'Join' combines; 'Union' stacks.

Delta Live Tables: use APPLY CHANGES for CDC; supports SCD Type 1 and Type 2 with sequencing.

Data quality: define expectations at each medallion layer; fail or drop rows on violation.

Columnstore compression: columnstore indexes compress better than rowstore; use for fact tables; rowstore for small dimension tables.

Partition elimination: filter on the partition column to skip partitions; ensure the filter is a constant or parameter, not a function of the column.

Statistics on join columns: missing stats cause scans and broadcast moves; create stats on foreign keys and filter columns.

Result-set caching: caches query output; invalidated on data change; max size configurable; use for repeated BI queries.

Materialized views: precompute aggregations; automatic refresh; require a clustered columnstore index; cannot reference other views.

Databricks: use OPTIMIZE and ZORDER on filter columns; VACUUM removes old files; liquid clustering for evolving access patterns.

Cosmos DB: partition key choice affects RU cost; cross-partition queries are expensive; use the partition key in query filters.

ADLS Gen2: use large files and partitioning; serverless queries scan less with predicate pushdown on partitioned folders.

Dynamic data masking: masks data in results; does not encrypt at rest; db_owner and UNMASK see original values; use for PII columns.

Row-level security: predicate function with FILTER (hide rows) or BLOCK (error on write); schema-bound; applies to all users except sysadmin and db_owner.

Column-level security: GRANT SELECT ON table(col); not supported on columnstore tables in dedicated pools.

Always Encrypted: protects data in use; deterministic encryption for equality joins; randomized for stronger security; column master key in Key Vault.

TDE: encrypts data at rest; enabled by default in Azure SQL; in Synapse dedicated pools, TDE is on by default.

ADLS Gen2: RBAC and ACLs; most restrictive wins unless a mask grants access; use default ACLs for inheritance.

Managed identity: avoids secrets; grant the workspace identity access to storage; use for linked services.

Private endpoints: place services on a virtual network; disable public access; use data exfiltration protection in Synapse.

ADF: store credentials in Key Vault; reference via linked service with managed identity; never embed secrets in pipeline parameters.

Databricks: use secret scopes backed by Key Vault; reference with dbutils.secrets.get; never print secrets.

ADF: use managed virtual network and managed private endpoints to connect to storage privately.

Structured Streaming: secure Event Hubs and Kafka with SAS or managed identity; store connection strings in Key Vault.

Spark: use a service principal or managed identity to access ADLS; configure spark.conf with the OAuth endpoint and secret scope.

ADF: use parameterized linked services to avoid hardcoding; global parameters for constants.

Delta Lake: use table ACLs in Databricks; grant SELECT, MODIFY, or ALL PRIVILEGES at catalog, schema, or table scope.

Data quality: validate and mask PII in the silver layer; do not expose raw PII in gold layers.

Synapse SQL auditing: enable at server level; write to Storage, Log Analytics, or Event Hubs; captures logins, DDL, and DML based on action groups.

Microsoft Defender for SQL: detects anomalous access and injection; enable at the server or workspace level.

ADLS Gen2 diagnostic logs: capture read, write, and delete; route to Log Analytics for KQL analysis.

Synapse: workspace managed virtual network with data exfiltration protection; private endpoints for inbound.

Databricks: cluster audit logs; export to a log delivery destination for retention.

Key Vault: access policies vs RBAC; choose one; managed identity needs Get and List.

Azure Monitor: metric alerts on DWU and queued queries; log alerts on failed ADF runs.

Cost control: pause idle pools; use serverless for ad hoc; set budgets and alerts.

Synapse dedicated pool: hash-distribute large fact tables on the join key; replicate small dimensions; partition facts by date.

Clustered columnstore: rowgroup quality; aim for 1 million rows per rowgroup; REORGANIZE to merge; REBUILD to defragment.

Result-set caching: caches query output; invalidated on data change; use for repeated BI queries.

Materialized views: precompute aggregations; automatic refresh; require a clustered columnstore index.

Partition elimination: filter on the partition column; avoid functions on the column.

Statistics: create on join and filter columns; update after large loads.

Serverless: minimize scanned bytes with predicate pushdown and partitioned folders.

Delta: OPTIMIZE and ZORDER on filter columns; VACUUM removes old files.

Spark: broadcast join for small tables; sort-merge for large; shuffle hash when one side is small but not broadcast-eligible.

AQE: coalesces shuffle partitions and switches to broadcast joins at runtime; enable with spark.sql.adaptive.enabled=true.

Partitioning: repartition shuffles; coalesce reduces partitions without a full shuffle; use before writes.

Caching: cache() stores in memory and disk; persist(StorageLevel) for control; unpersist frees memory.

ADF data flow: use the Optimize tab to set partition type; broadcast small tables in joins.

Structured Streaming: watermark bounds state; dropDuplicates within a watermark.

Delta: OPTIMIZE compacts small files; ZORDER co-locates related data.

Databricks: photon accelerates SQL and columnar workloads; autoscaling adjusts workers.

Synapse dedicated pool: workload management isolates ETL from BI; workload groups set importance and caps.

Resource classes: larger classes give more memory per query but reduce concurrency.

Query plan red flags: Table Scan on large tables, BroadcastMoveOperation for large tables, ShuffleMoveOperation, tempdb spills.

Skew detection: DBCC PDW_SHOWSPACEUSED and sys.dm_pdw_nodes_db_partition_stats.

Databricks Spark UI: a few long tasks in a stage indicate skew; fix with salting or repartition.

ADLS Gen2: large files reduce per-file overhead; avoid many small files.

Azure Monitor: alert on DWU saturation and queued queries.

Cost: pause idle pools; use serverless for ad hoc; set budgets.

Synapse dedicated vs serverless: dedicated has provisioned DWU and persistent tables; serverless queries files and bills per TB scanned.

External tables vs OPENROWSET: external tables need a schema and suit repeated queries; OPENROWSET is ad hoc and supports inference.

Delta as a source: serverless queries Delta via OPENROWSET with FORMAT='DELTA'; dedicated needs PolyBase or CETAS.

CETAS: writes query results to Parquet in the lake; use for transforming lake data.

ADLS Gen2 and Synapse: grant the workspace managed identity Storage Blob Data Contributor.

Cosmos DB analytical store: columnar and auto-synced; Synapse link reads it without RU cost.

Data lake zones: raw, curated, serving; distinct ACLs and retention.

Azure SQL elastic pools: share DTUs or vCores; use for multi-tenant SaaS.

ADF pipeline vs data flow: pipelines orchestrate activities; data flows run Spark transformations.

Linked service vs dataset: linked service defines the connection; dataset defines the structure.

Triggers: schedule, tumbling window, event, custom; only tumbling window is stateful and supports backfill.

Data flow source options: inline dataset, dataset reference, parameterized.

Databricks notebooks vs jobs: notebooks are interactive; jobs schedule with cluster configuration and retries.

Structured Streaming trigger modes: processingTime, once, availableNow, continuous.

Data flow sink: Optimize tab sets partitioning; single for small outputs, hash for large joins.

Spark write modes: append, overwrite, errorIfExists, ignore; Delta adds mergeSchema and overwriteSchema.