CCNA Secure, monitor, and optimize data storage and data processing Questions

The first step to secure access to the Azure Synapse Analytics dedicated SQL pool is to configure authentication and authorization using Microsoft Entra ID and RBAC. This establishes who can access the pool and what they can do. Once that is in place, you can then implement additional security measures like auditing (option B), column-level security (option A), or dynamic data masking (option D), but these are secondary steps.

Option B is not the first step because auditing tracks access but does not control it. Option A is too granular for initial access control. Option D obfuscates data but does not prevent unauthorized access.

When SU utilization is consistently at 90% or higher, the job is resource-constrained, which leads to backpressure and falling behind. The most direct way to improve throughput is to increase the number of Streaming Units (SUs), as this adds more compute capacity. Option A (changing output to use a partition scheme) can improve performance in some cases but is not the immediate step and may not resolve high SU utilization.

Option B (reducing window duration) can cause data loss or inaccurate results, and does not address the resource bottleneck. Option D (decreasing event ordering tolerance) also risks data loss and is not a throughput improvement measure.

Azure Synapse Studio monitoring hub provides a centralized view of dedicated SQL pool activity, including running and completed queries, and dynamic management views (DMVs) can be queried to identify long-running queries and resource bottlenecks. Option A is wrong because Microsoft Purview Data Map is a data governance and cataloging service, not a performance monitoring tool. Option C is wrong because Azure Log Analytics queries can be used for historical analysis but require configuration and are not the primary tool for real-time live query monitoring in Synapse dedicated SQL pools.

Option D is wrong because Azure Monitor Workbooks provide customizable dashboards for various Azure services, but they are not specifically designed for direct query monitoring in Synapse SQL pools.

A self-hosted integration runtime (IR) installed on an on-premises VM can connect to SQL Server over the private network, then communicate with Azure Data Factory over port 443. Managed identity can be used for authentication to Azure Data Factory. Option A is incorrect: Azure IR with VNet injection still requires a public endpoint for on-premises SQL Server, which is not accessible.

Option B is incorrect: Point-to-site VPN provides connectivity but does not directly support managed identity for ADF authentication, and additional configuration is needed. Option D is incorrect: Using an Azure IR with a public endpoint requires the on-premises SQL Server to have a public endpoint, which it does not, and it does not utilize managed identity for authentication.

Deploying the Azure Databricks workspace in a VNet with forced tunneling enabled and a firewall ensures that cluster nodes have no public IP addresses and all outbound traffic is routed through the firewall. Option A is incorrect because service endpoints do not prevent public IPs on cluster nodes. Option C is incorrect because NSGs alone do not force traffic through a firewall; forced tunneling requires a route table with default route to the firewall.

Option D is incorrect because Azure Private Link only provides private connectivity to the workspace, but does not control outbound traffic from cluster nodes.

To reduce shuffle writes in a Databricks batch job using Delta Lake, you should partition the Delta table by the most frequently used filter column (B) and use the OPTIMIZE command with Z-Ordering on join keys (E). Partitioning limits data shuffling by filtering out irrelevant partitions, and Z-Ordering colocates related data, reducing shuffle size during joins. Option A (auto-optimize) helps compaction but does not directly reduce shuffle writes.

Option C (broadcast hash join) only helps if one table is small, not for large tables. Option D (increasing shuffle partitions) often increases shuffle writes due to more tasks.

The -DefaultScope parameter in Azure Data Lake Storage Gen2 sets a default ACL entry. Default ACLs do not set permissions on the current directory; instead, they define permissions that are inherited by new child items (files and subdirectories) created under that directory. Therefore, option B is correct.

Option A is incorrect because default ACLs do not replace the access ACL; access ACLs are set separately without -DefaultScope. Option C is incorrect because default ACLs do not apply to existing items; they only affect future items. Option D is incorrect because default ACLs apply to both new files and new subdirectories.

Mapping Data Flow in Azure Data Factory allows you to transform data during ingestion. Using derived column transformations, you can apply masking functions (e.g., substituting characters, hashing) to sensitive columns before writing to the staging area. This approach masks data before it reaches the staging area, meeting the requirement.

Option A (Azure Policy) is for compliance and cannot mask data. Option B (Dynamic Data Masking on source) masks data at query time, not during ingestion. Option D (Azure Purview) is for data governance and classification, not for masking data during a pipeline.

The 'Transactions' metric in Azure Monitor can be used to count the number of requests to the storage account, and you can set a metric alert with a threshold of 20,000 aggregated over an hour. This is the most efficient method as it directly uses the metric without needing complex queries. Option A is wrong because it requires creating a Log Analytics workspace and writing a KQL query, which is more complex and less efficient than a metric alert.

Option B is wrong because Activity Log alerts are for management events like 'List Storage Account Keys', not for data transaction counts. Option D is wrong because Azure Advisor provides recommendations, not custom alerting on specific metric thresholds.

The most likely cause is that the storage account does not have an event subscription configured for blob creation. The BlobEventsTrigger in Azure Data Factory relies on an event subscription from the storage account to the trigger. Without this subscription, the trigger will not fire even when blobs are created in the specified path.

Option D correctly identifies this issue. Option A is incorrect because the blobPathBeginsWith property includes the container name prefix; Option B is incorrect because the trigger is configured to listen to blob creation events; Option C is incorrect because the ProcessPipeline does not require parameters from the trigger.

Options A and B are correct. Option A: Azure Monitor diagnostic settings can be configured to send resource logs (including storage audit logs) to Log Analytics, Storage, or Event Hubs for auditing. Option B: Storage Analytics logs provide detailed information about successful and failed requests to a storage account, which can be used for auditing.

Option C is incorrect because Azure RBAC role assignments are for access control, not auditing. Option D is incorrect because Azure Policy enforces compliance rules, not auditing. Option E is incorrect because Microsoft Defender for Cloud provides security alerts and threat protection, but not detailed access auditing.

The Self-Hosted Integration Runtime (SHIR) acts as the gateway between on-premises data sources and Azure Data Factory. If the SHIR is not running or cannot communicate with the Azure Data Factory service, the pipeline fails with a 'Gateway is offline' error. Option A is incorrect because using the Azure Integration Runtime for an on-premises source would cause a different error, not 'Gateway is offline'.

Option B is incorrect because region configuration for the Azure Integration Runtime is irrelevant when a SHIR is required. Option C is incorrect because the error relates to the gateway, not to SQL Server remote connection settings.

Partitioning on a frequently filtered column enables partition elimination, reducing the amount of data scanned and improving query performance. Option A (result-set caching) caches query results for repeated queries, but this question is about slow queries on a large fact table, not repeated queries. Option B (increasing DWU) might improve performance by providing more resources, but it is a more costly approach and not the best first step; partitioning is a better design optimization.

Option C (creating additional statistics on all columns) is unnecessary because they already have statistics on columns used in WHERE and JOIN; statistics on all columns would waste resources and not help.

Microsoft Entra ID authentication allows users to authenticate with their existing identities, and Azure RBAC on the storage account provides granular, identity-based access control without managing separate SQL logins or credentials, minimizing administrative overhead. Option B is incorrect because managed identities are designed for service-to-service authentication, not for individual users. Option C is incorrect because storage account access keys provide broad, shared access that does not support per-user identity and auditing.

Option D is incorrect because SAS tokens require token generation and management per user, adding overhead.

Replicated tables are recommended for small dimension tables because they are copied to all compute nodes, avoiding data movement during joins. This reduces excessive data movement without changing distribution columns. Option B is incorrect because round-robin distribution distributes data evenly but does not reduce data movement for joins; it is typically used for staging tables.

Option C is incorrect because using hash distribution for all tables can lead to data movement when joining on different columns, and it is not a one-size-fits-all solution. Option D is incorrect because partitioning alone does not reduce data movement; it is used for data management and pruning, not for minimizing shuffle operations.

Options C and E are correct. C: Transparent Data Encryption (TDE) encrypts data at rest in the dedicated SQL pool, protecting against unauthorized access to the physical storage. E: Column-level security restricts access to sensitive columns based on user roles or identities, ensuring that only authorized users can view or modify sensitive data.

A is incorrect because firewall rules control network access, not data protection within the database. B is incorrect because Azure Storage encryption is enabled by default for all storage accounts and is not a security measure specific to Synapse SQL pool. D is incorrect because Dynamic Data Masking obfuscates data from all users, but it does not prevent access; users can still see the original data if they have permissions, unlike column-level security which can block access entirely.

You create a database user mapped to the Microsoft Entra group in the serverless SQL pool database, then grant SELECT ON SCHEMA::sales to that user. This ensures only members of the 'DataScientists' group have permission to read data from the 'sales' schema. Option A is wrong because a server-level login for the group does not grant specific schema-level permissions; the 'public' role provides broad access.

Option C is wrong because the 'Synapse SQL Administrator' role grants too many permissions (admin-level access) and is not schema-specific. Option D is wrong because a contained database user with password does not leverage Microsoft Entra group membership, and the 'db_datareader' role grants read access to all tables, not just the 'sales' schema.

To evaluate the performance of an Azure Data Lake Storage Gen2 account, key metrics include Ingress (data written to the account), Success E2E Latency (end-to-end latency of successful requests), and Blob Count (total number of blobs/objects). Option A (Ingress) is correct because it measures the throughput of data entering the storage account. Option C (Success E2E Latency) is correct as it reflects the overall response time for successful operations.

Option E (Blob Count) is correct because it helps assess scalability and cost. Options B (CPU Usage) and D (Available Storage Capacity) are not metrics for storage accounts; CPU is for compute resources, and capacity is a property, not a performance metric.

To authenticate to Azure Synapse Analytics using a managed identity, you should enable the system-assigned managed identity on the Stream Analytics job (it is enabled by default) and then configure the output to use managed identity authentication. Option A is correct. Option B is incorrect because a SAS token is not a managed identity.

Option C is incorrect because while a user-assigned managed identity could be used, the system-assigned managed identity is sufficient and is the simpler option. Option D is incorrect because SQL Server authentication does not use managed identity and is less secure than managed identity authentication.

Storing connection strings and secrets in Azure Key Vault and referencing them via linked services ensures secrets are stored securely and not exposed in pipeline definitions. Option D is correct: Enabling Managed Virtual Network (VNet) isolates data flows within a managed network boundary, preventing public network access. Option A is incorrect: Configuring a self-hosted integration runtime on a public cloud VM does not necessarily improve security; it may expose the runtime to the public internet.

Option B is incorrect: Using the default Auto-resolve Integration Runtime is not recommended for secure data processing because it may use public endpoints and lacks network isolation. Option E is incorrect: Allowing all public IP addresses to access the Azure Synapse workspace exposes the workspace to potential security threats.

Enabling 'Azure AD-only authentication' in the Azure SQL Database server settings disables SQL authentication and enforces Microsoft Entra ID authentication, which also ensures auditing through Entra ID logs. Option A is incorrect: Conditional Access policies enforce MFA for Entra ID authentication, but they do not disable SQL authentication, leaving the attack surface open. Option C is incorrect: Creating a firewall rule restricts IP addresses but does not disable SQL authentication.

Option D is incorrect: Azure RBAC controls management plane access (e.g., resource management), not database-level authentication; it does not disable SQL authentication.

The correct actions are D and E. For star join queries in Azure Synapse Analytics dedicated SQL pool, best practices are to use hash distribution on fact tables using the join key (option E) to distribute data across distributions efficiently, and use replicated distribution on dimension tables (option D) to avoid data movement. Option A is wrong because round-robin distribution is suitable for staging or temporary tables, not for large fact tables in star schemas.

Option B is wrong because dimension tables should be replicated, not hash-distributed, to minimize shuffling. Option C is wrong because heap tables are not optimized for analytical queries; clustered columnstore index is the recommended table structure for fact tables.

Enabling soft delete with a 30-day retention period allows recovery of accidentally deleted files within that window. Configuring a lifecycle management rule to delete blobs older than 90 days automatically enforces the retention policy. This combination minimizes administrative effort.

Option B is incorrect because Azure Policy cannot enforce retention or recovery at the blob level. Option C is incorrect because versioning is not equivalent to soft delete for ADLS Gen2 and Azure Policy does not manage retention. Option D is incorrect because manual deletion does not provide automatic enforcement and Azure Backup is not designed for storage-level lifecycle management.

The recommended security improvement is to modify the linked service to use the managed identity for authentication (option B). The user-assigned managed identity is already configured but not being used; instead, the linked service is using an account key, which requires storing a secret. Using the managed identity eliminates the need for secrets and leverages Azure AD authentication, which is more secure and easier to manage.

Option A (firewall rule) addresses network access but not authentication. Option C (remove managed identity and use service principal) would replace one identity-based approach with another, but the managed identity is already in place and should be utilized. Option D (store key in Key Vault) still involves storing a secret, which is less secure than using the managed identity directly when possible.

Geo-backup is a built-in feature for Azure Synapse Analytics dedicated SQL pools that automatically takes full backups at regular intervals and replicates them to a paired region. The default recovery point objective (RPO) is 1 hour, meeting the requirement. Option A is incorrect because active geo-replication is a feature for Azure SQL Database, not for Synapse dedicated SQL pools.

Option B is incorrect because automatic restore points are local and not automatically replicated; copying them via Azure Data Factory adds complexity and does not guarantee the RPO. Option D is incorrect because user-defined restore points are manual and would require additional orchestration to store them in a secondary region, making it less reliable and harder to meet the 1-hour RPO consistently.

In Azure Data Lake Storage Gen2 (with hierarchical namespace enabled), ACLs are used to set granular permissions at the directory or file level. To grant only execute permission on the root directory without read, you must set an ACL entry that specifically grants execute (--x) to the 'data-engineers' group. Option A is incorrect because disabling hierarchical namespace would change the storage type and not achieve the desired ACL-based permission.

Option B is incorrect because the Storage Blob Data Reader role grants read and list access, not just execute. Option C is incorrect because firewall rules control network access, not permissions on directories.

A new scan rule set in Purview with classification rules for sensitive data types. Purview allows you to create custom scan rule sets that include classification rules for sensitive data types like credit card numbers and social security numbers. When you run a scan on Azure Data Lake Storage Gen2, it uses these rules to automatically detect and classify the sensitive data.

Option A is incorrect because Azure Information Protection (AIP) scanner is for classifying and protecting files in on-premises file shares and SharePoint, not for scanning Azure Data Lake Storage. Option B is incorrect because Microsoft Defender for Cloud is for security posture management and threat detection, not data classification. Option D is incorrect because Azure Policy with guest configuration is for auditing and enforcing compliance settings on virtual machines, not for data classification in storage.

Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault provides database-level encryption for dedicated SQL pools in Azure Synapse Analytics. TDE encrypts data at rest, and using a customer-managed key (CMK) allows the organization to control the key lifecycle. Option B is incorrect because Azure Purview is for data governance and classification, not encryption.

Option C is incorrect because Azure Disk Encryption encrypts VM disks, not the SQL pool database. Option D is incorrect because Always Encrypted is a column-level encryption feature, not for full database encryption.

Increasing the 'Degree of copy parallelism' allows multiple threads to read from the source and write to the sink concurrently, significantly improving throughput for large datasets like 50 GB. Option B is incorrect because staging copy is used for scenarios like cross-cloud transfers or to leverage intermediate storage for performance, but it adds overhead and is not the first optimization for direct on-premises to Azure Blob copy. Option C is incorrect because increasing activity retry count only handles transient failures, not performance issues.

Option D is incorrect because reducing Data Integration Units would decrease the resources available for the copy activity, worsening performance.

Controlling access involves two layers: the serverless SQL pool and the underlying storage. Users must have both CONNECT permission on the SQL pool to query and appropriate ACLs on the storage to read the data. Granting the Microsoft Entra ID group CONNECT permission and configuring ACLs on the storage account to allow read access for the group ensures that only those users can access data through the serverless SQL pool.

Option A is incorrect because the Storage Blob Data Reader role alone does not grant SQL-level permissions. Option C is incorrect because firewall rules are network-level and do not provide granular user access. Option D is incorrect because SAS tokens are not recommended for user-level access and bypass Entra ID permissions.

The correct architecture. Event Hubs Capture automatically captures streaming data into Avro files in Blob Storage, which can be organized by date in folder structures. External tables in Azure Synapse can query these files with partition elimination, providing good query performance.

This approach handles high-volume ingestion efficiently without impacting the streaming pipeline. Option A is incorrect because PolyBase in Azure Synapse does not natively support the Delta format (it supports Parquet, ORC, etc.), so loading Delta format using PolyBase is not feasible. Option B is incorrect because direct writes from Stream Analytics to a dedicated SQL pool are not efficient for millions of events per hour due to batch constraints and potential bottlenecks.

Option C is incorrect because Azure SQL Database with elastic scaling is intended for online transaction processing (OLTP) workloads, not large-scale analytical processing.

Azure Storage Insights provides pre-built dashboards to monitor storage account metrics like capacity, transactions, and latency, aiding performance monitoring. Option D is correct: Azure Storage Analytics logs (now part of Azure Monitor) capture latency and request rate data, helping analyze performance. Option E is correct: Enabling diagnostic settings in Azure Monitor captures read and write request details, useful for troubleshooting performance issues.

Option A is incorrect: Network Security Group flow logs monitor network traffic for security purposes, not storage performance. Option C is incorrect: Lifecycle management policies optimize costs by tiering data, not for performance monitoring.

Azure Data Lake Storage Gen2 uses POSIX-style ACLs that allow granular permissions. To restrict an application to write-only access without read or list, you grant execute (for directory traversal) and write permissions on the target directory via an ACL entry for the application's service principal. Option A is incorrect because firewall rules control network access, not data permissions.

Option C is incorrect because a SAS with list permission would allow listing; write-only SAS is not easily achievable at directory level. Option D is incorrect because the Storage Blob Data Contributor role at the account level grants read, write, and delete permissions, not write-only.

Correct answers: A, D, E. The Microsoft Purview Data Map consists of components that define how data sources are registered and scanned. Scan rule sets (A) are used to configure scanning rules for data sources.

Data sources (D) are the actual data locations that are registered and scanned. Classifications (E) are the rules or patterns that identify data types (e.g., PII) during scanning; they are a core component of the Data Map. B (Sensitivity labels) is incorrect because sensitivity labels are part of Microsoft Information Protection, not a component of the Data Map itself, though they can be applied to assets in Purview.

C (Data flows) is incorrect because data flows are part of Azure Data Factory, not the Data Map.

Result-set caching stores the exact query results and can serve the dashboard query instantly if the underlying data has not changed. Since the query runs every 5 minutes, setting the cache expiration to 5 minutes ensures fresh data. Option A (columnstore index) is wrong because the table likely already has a columnstore index (the default in Synapse).

Option B (materialized view) is wrong because materialized views require maintenance and may not match the exact query. Option D (hash distribution on date) is wrong because it can cause data skew and does not reduce scan overhead as effectively as caching.