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

Options B and D are correct. B: Using spot instances reduces costs for fault-tolerant workloads. D: Autoscaling adjusts resources based on workload.

Option A is wrong because all-purpose clusters are more expensive and not recommended for production jobs. Option C is wrong because using larger VMs may lead to underutilization and higher costs. Option E is wrong because Azure Databricks is not serverless in the traditional sense; you pay for running VMs.

Option D is correct because the policy denies storage accounts where the default network access is set to 'Allow' (i.e., public access). Option A (denies creation only) is not true; it applies to existing accounts as well via auditing/deny. Option B (allows only HTTPS) is unrelated.

Option C (requires firewall) is not stated.

Option B is correct because changing the Customer table to replicated distribution eliminates data movement during joins. Option A is wrong because changing the distribution key of Sales to a different column may not solve skew if the key is not the join column. Option C is wrong because round-robin is not suitable for fact tables in star joins.

Option D is wrong because increasing DWU may alleviate symptoms but does not fix the root cause of data movement.

A self-hosted integration runtime is needed to connect to on-premises networks. To encrypt data in transit, the certificate must be used for HTTPS encryption. Option A is wrong because Azure IR cannot access on-premises directly.

Option C is wrong because ExpressRoute provides private connectivity but does not handle encryption at the application layer. Option D is wrong because VPN Gateway is a network-level solution; ADF still needs self-hosted IR.

Azure Storage Service Encryption (SSE) is enabled by default for all Azure Storage accounts, including Data Lake Storage Gen2, and encrypts data at rest using AES-256. Option A is wrong because Azure Disk Encryption is for VMs. Option B is wrong because Azure Key Vault is used for managing keys, not the encryption itself.

Option D is wrong because Azure Purview is for data governance.

A self-hosted integration runtime (SHIR) is required for on-premises data sources. To improve reliability, a high-availability SHIR with two or more nodes provides redundancy and load balancing. Option A is wrong because Azure IR cannot access on-premises networks.

Option B is wrong because Sharded staging doesn't address connectivity. Option D is wrong because VPN Gateway alone doesn't improve the IR reliability.

Option A is correct because 'Failed runs' metric directly indicates pipeline failures. Option B is wrong because 'Data read' and 'Data written' measure data throughput, not failures. Option C is wrong because 'Duration' shows runtime, but a timeout is a specific failure type.

Option D is wrong because 'Queue depth' is for integration runtimes, not pipeline failures.

Option A is correct because Azure Data Factory uses HTTPS by default, which enforces TLS encryption for data in transit. Option B is wrong because VPN is not required for TLS encryption. Option C is wrong because Azure ExpressRoute provides a private connection but does not replace TLS.

Option D is wrong because configuring the on-premises SQL Server to use SSL certificates is necessary but not sufficient; Azure Data Factory must also use HTTPS.

Option B is correct because customer-managed keys (CMK) in Azure Key Vault provide encryption at rest with customer control, and Azure Storage analytics logs (or diagnostic settings) provide file-level audit logs. Option A is wrong because Azure AD authentication does not provide encryption. Option C is wrong because Azure Information Protection is a labeling solution, not encryption at rest.

Option D is wrong because firewall rules do not encrypt data.

Options A, C, and E are correct. Materialized views improve performance for aggregations, result-set caching speeds up repeated queries, and workload management ensures resource isolation. Option B is wrong because T-SQL views are logical, not performance optimization.

Option D is wrong because geo-redundant storage is for disaster recovery, not performance.

Option D is correct: Use Azure RBAC roles for coarse permissions (e.g., Storage Blob Data Contributor for engineers), use ACLs for fine-grained control (e.g., deny read for data scientists on raw data), and enable diagnostic settings to log read operations to Log Analytics. Option A is incorrect: Storage Analytics logs are deprecated. Option B is incorrect: Lifecycle management is for tiering, not access control.

Option C is incorrect: RBAC only cannot provide granular control at file level.

Option C is correct because Transparent Data Encryption (TDE) encrypts data at rest. Option A is wrong because Always Encrypted encrypts data in use and in transit, not at rest. Option B is wrong because Dynamic Data Masking masks data in query results.

Option D is wrong because Azure Information Protection is a classification and labeling service.

Option B is correct because ACLs are the mechanism to grant granular permissions to specific users/groups in ADLS Gen2 with hierarchical namespace. Option A is wrong because RBAC roles grant access at the storage account or container level, and a single deny assignment would block everyone. Option C is wrong because private endpoints control network access, not identity-based permissions.

Option D is wrong because managed identity is used for service-to-service authentication, not for granting read access to a specific group.

Option B is correct because a lifecycle management policy can automatically delete blobs based on age. Option A is wrong because Azure Blob Storage inventory provides reports but does not delete. Option C is wrong because Azure Policy enforces compliance but does not delete data.

Option D is wrong because Azure Purview scopes metadata and data discovery, not lifecycle management.

InputEventsBacklog measures the number of input events that are not yet processed, indicating the job is falling behind. Option B is wrong because OutputEvents counts events written. Option C is wrong because WatermarkDelay shows the latency.

Option D is wrong because RuntimeErrors counts errors.

Option B is correct because the command sets auto-pause delay to 15 minutes, but the property 'IsTransactional' is not a valid property for Set-AzSynapseSqlPool; it will be ignored. Auto-pause is a serverless SQL pool feature, but for dedicated SQL pool, it's not supported; however, the command will attempt to set it but fail silently or be ignored. In practice, auto-pause delay is only for serverless SQL pools.

The most accurate answer is that auto-pause is set, but other properties are invalid.

Option B is correct because column-level security restricts access to specific columns. Option A is wrong because row-level security filters rows, not columns. Option C is wrong because dynamic data masking obfuscates data but does not prevent access.

Option D is wrong because Azure Purview is for data discovery and governance, not access control.

Option B is correct because a timeout of 0 means no timeout is applied. Option A is wrong because 0 does not mean immediate timeout. Option C is wrong because 0 does not mean infinite wait; it means no timeout.

Option D is wrong because 0 is not invalid.

Option A is correct because Azure Databricks supports CMK encryption via Azure Disk Encryption or by using a key vault key for the managed disk. Option B (Azure Storage Service Encryption) is for storage accounts, not managed disks. Option C (Azure SQL Database TDE) is irrelevant.

Option D (Azure Purview) is for data governance, not encryption.

Option C is correct because implementing a columnstore index on the fact table will compress data and improve scan performance for aggregations. Option A is wrong because hash distribution on TransactionDate would cause data skew (many rows per date) and is not suitable for high-cardinality columns. Option B is wrong because round-robin distribution would eliminate collocation benefits for joins with DimAccount.

Option D is wrong because table replication is for small dimension tables, not large fact tables.

Options A and B are correct. Distributing tables on join keys reduces shuffling. Using replicated tables for small dimension tables avoids broadcasting.

Option C is wrong because round-robin distribution increases data movement. Option D is wrong because hash distribution on a non-join column may cause unnecessary shuffling. Option E is wrong because increasing resource class does not directly reduce data movement.

Option A is correct because Azure Key Vault is used to store customer-managed keys for Azure Storage encryption. Option B is wrong because Microsoft Purview is for data governance. Option C is wrong because Azure Confidential Computing is for compute.

Option D is wrong because Microsoft Entra ID is for identity.

Option B is correct because enabling Microsoft Entra ID authentication and assigning roles via RBAC is the first step to control access. Option A is wrong because enabling auditing does not restrict access. Option C is wrong because column-level security only restricts specific columns, not overall access.

Option D is wrong because dynamic data masking obfuscates data but does not prevent access.

Option A is correct because increasing the number of SUs (scale out) is the direct way to increase throughput when SU utilization is high. Option B is wrong because it can cause data loss. Option C is wrong because it reduces throughput.

Option D is wrong because partitioning might help but the immediate need is to scale.

Option A is correct because SQL pool monitoring provides DMVs and metrics for query performance. Option B is wrong because Azure Monitor Logs can be used but is not the primary tool for live query monitoring. Option C is wrong because Log Analytics requires setting up data collection.

Option D is wrong because Microsoft Purview is for data governance, not performance monitoring.

Option B is correct: A self-hosted integration runtime (IR) installed on-premises with managed identity can connect to SQL Server using private network, then communicate with Azure Data Factory over port 443. Option A is incorrect: Azure IR cannot connect to on-premises SQL Server without a public endpoint. Option C is incorrect: Azure VPN Gateway requires additional network configuration and doesn't use managed identity.

Option D is incorrect: ExpressRoute is a private connection but doesn't address authentication.

Using Azure IR in the same region as the source and sink ensures data movement stays within Azure backbone, minimizing egress costs. Option A (staging) adds cost. Option B (self-hosted IR) is for on-premises.

Option C (polybase) is for loading into SQL DW but still uses IR.

Option D is correct because Azure Databricks can be deployed in a VNet injected configuration with no public IPs and route all traffic through a firewall. Option A is wrong because private endpoints are for data plane services, not for cluster nodes. Option B is wrong because service endpoints do not eliminate public IPs.

Option C is wrong because network security groups control inbound/outbound rules but do not force traffic through a firewall.

Option C is correct because Azure Databricks with Unity Catalog provides fine-grained access control at the table level and integrates with Entra ID for authentication, and audit logs capture access events. Option A is wrong because SCIM provisioning only syncs users, not access control. Option B is wrong because IP access lists control network access, not data access.

Option D is wrong because Microsoft Defender XDR is for security monitoring across Microsoft 365, not specifically for Databricks table-level auditing.

Option B is correct: Azure Monitor diagnostic settings can send Synapse SQL pool logs and metrics to Log Analytics. Then you can create alerts and run queries over historical data. Option A is incorrect: Dynamic Management Views (DMVs) only provide current state, not historical 30-day data.

Option C is incorrect: Query Store retains data for a limited time and doesn't integrate natively with Log Analytics. Option D is incorrect: Azure Storage Analytics is for storage accounts, not Synapse SQL.

Options A and D are correct. Optimizing the data layout with Z-Ordering and compaction reduces shuffle size. Option B is wrong because increasing the number of shuffle partitions can increase shuffle writes.

Option C is wrong because enabling auto-optimize helps but does not directly reduce shuffle writes. Option E is wrong because using broadcast hash join reduces shuffles if one table is small, but not generally for large tables.

Option A is correct because the roleDefinitionId corresponds to 'Storage Blob Data Contributor', and the scope is the resource group. Option B is wrong because the scope is not the storage account. Option C is wrong because the role is not 'Storage Blob Data Reader' (the GUID is different).

Option D is wrong because the scope is the resource group, not subscription.

Option C is correct because default ACLs are inherited by new child items created under that directory. Option A is wrong because default ACLs do not affect existing items. Option B is wrong because default ACLs apply to both files and directories.

Option D is wrong because default ACLs are separate from access ACLs.

Option A is correct because the managed identity must have the 'Storage Blob Data Contributor' RBAC role on the storage account to write data. Option B is wrong because the error is 403, not 404. Option C is wrong because no service endpoint or private endpoint is required.

Option D is wrong because firewall rules would cause a different error.

HTTPS and SMB over encryption ensure data is encrypted in transit. Option C (SSE) is at rest. Option D (Azure Disk Encryption) is for VMs.

Option E (IPSec) is not supported by Azure Storage directly.

Option D is correct because Data Flow allows transformation steps including column masking. Option A is wrong because Azure SQL Database dynamic data masking is applied at query time, not during copy. Option B is wrong because Azure Purview is for governance, not data masking.

Option C is wrong because Azure Policy is for compliance, not data masking at source.

Options A, B, and E are correct. Using replicated tables avoids shuffling for small tables; partitioning large tables enables partition elimination; using result-set caching reduces load. Option C is wrong because rowstore indexes are slower for analytics; columnstore is preferred.

Option D is wrong because round-robin distribution is for large tables with no natural key, but it may cause shuffles.

Option B is correct because the metric 'Transactions' is available at the storage account level and can be aggregated per hour. Option A is wrong because a Log Analytics query would be more complex and less efficient. Option C is wrong because Activity Log does not contain transaction counts.

Option D is wrong because Azure Advisor does not provide custom alerting on metrics.

Option C is correct because a managed private endpoint in the Data Factory's managed virtual network allows secure access to the SQL Database's private endpoint. Option A is wrong because the integration runtime must be in the same virtual network. Option B is wrong because self-hosted IR is for on-premises data sources.

Option D is wrong because Azure Bastion is for VM access, not data factory.

Diagnostic settings on the storage account can stream audit logs (like read, write, delete) to Log Analytics. Option B is wrong because Azure Activity Logs capture control plane operations, not data plane access. Option C is wrong because Azure Monitor alerts are for notifications, not log collection.

Option D is wrong because Microsoft Sentinel requires data ingestion from diagnostic settings first.

Option B is correct because BlobEventsTrigger requires a storage event subscription, which must be configured separately. Option A is wrong because the trigger is properly defined with the event type. Option C is wrong because the container is not specified; but the path begins with /input/ which implies a container.

Option D is wrong because pipeline parameters are not required.

The error indicates the file is not found or access denied. The most common cause is that the file path is case-sensitive on the FTP server. Verify the correct path with case sensitivity.

Option A is wrong because credentials are confirmed correct. Option B is wrong because the file exists. Option D is wrong because changing the connector may not help if path is wrong.

Options A and D are correct. Option A: Storage analytics logs capture successful and failed requests. Option D: Azure Monitor diagnostic settings can send logs to Log Analytics for auditing.

Option B is wrong because Azure Policy enforces compliance, not auditing. Option C is wrong because Microsoft Defender for Cloud provides security alerts, not detailed access logs. Option E is wrong because Azure RBAC is for access control, not auditing.

Option B is correct because sending diagnostic logs to a Log Analytics workspace allows querying and visualizing data from multiple factories in a single dashboard. Option A is wrong because Azure Monitor alerts are for notifications, not historical dashboards. Option C is wrong because Azure Data Factory monitoring views are per factory.

Option D is wrong because Azure Storage Analytics is for storage metrics, not pipeline runs.

Option B is correct because Unity Catalog supports ABAC through tags and Azure Databricks can use Microsoft Entra ID for authentication. Option A is wrong because row-level security is for filtering rows, not ABAC with tags. Option C is wrong because column-level security is for columns.

Option D is wrong because dynamic views are for custom logic, not attribute-based access control with tags.

Option B is correct because the Self-Hosted Integration Runtime (SHIR) is required for on-premises data sources. If the SHIR is not running or unreachable, the pipeline fails. Option A is wrong because the SHIR is used, not Azure IR.

Option C is wrong because the Azure IR cannot connect to on-premises networks. Option D is wrong because a VNet is optional for SHIR.

Option B is correct because partitioning can help with partition elimination, reducing data scanned. Option A is wrong because they already created statistics. Option C is wrong because increasing DWU might help but is not the best first step.

Option D is wrong because result-set caching is for repeated queries, not for improving scan efficiency.

Option D is correct because Microsoft Entra ID (formerly Azure AD) pass-through authentication allows users to authenticate using their existing Azure AD identities without managing separate SQL logins, reducing overhead. Option A is wrong because storage account keys provide broad access and are not tied to user identities. Option B is wrong because SAS tokens are time-limited and require token management.

Option C is wrong because managed identities are for service-to-service authentication, not individual user access.

Option B is correct because Azure Monitor collects and analyzes pipeline run logs and metrics. Option A is wrong because Azure Purview is for data governance. Option C is wrong because Azure Sentinel is a SIEM.

Option D is wrong because Azure Log Analytics is part of Azure Monitor but the question asks for the service that collects and analyzes logs.

Using replicated tables for small dimension tables reduces data movement because the table is copied to all nodes, avoiding shuffles. Option A is wrong because hash-distributing large fact tables is already common; replicating large tables is not recommended due to storage overhead. Option C is wrong because round-robin distribution is for staging or temporary tables.

Option D is wrong because partitioning does not directly reduce data movement for joins.

Purview uses scan rule sets to classify data. If the file type is not included in the default scan rule set, it will be skipped. Option A is wrong because Purview can scan Parquet/CSV.

Option B is wrong because registration is required. Option D is wrong because classification rules are defined in rule sets.

Options B and C are correct. Azure Monitor provides metrics and alerts; Log Analytics workspace enables querying pipeline run logs. Option A (Azure Sentinel) is a SIEM, not typically for pipeline monitoring.

Option D (Azure Dashboard) is a visualization tool, not a monitoring service. Option E (Azure Service Health) monitors Azure service health, not pipelines.

Option D is correct because result-set caching can significantly reduce query time for repeated queries by storing results in SSD, reducing resource contention. Option A is wrong because increasing DWUs changes the scale, which is not allowed per the requirement. Option B is wrong because materialized views help but do not directly address contention from repeated queries.

Option C is wrong because workload classification manages concurrency but does not reduce resource usage for repeated queries.

Options A and C are correct. A: Column-level security allows restricting access to sensitive columns. C: Transparent Data Encryption (TDE) encrypts data at rest.

Option B is wrong because Dynamic Data Masking obfuscates data but does not prevent access at the storage level. Option D is wrong because firewall rules are not a best practice for protecting sensitive data within the database. Option E is wrong because Azure Storage encryption is not applicable to Synapse SQL pool data.

Option B is correct because TDE encrypts data at rest and TLS 1.2 encrypts data in transit. Option A is wrong because Azure RBAC controls access but does not encrypt data. Option C is wrong because Always Encrypted is for client-side encryption, not for all data at rest.

Option D is wrong because Azure Key Vault stores keys but does not encrypt data itself.

Option B is correct because Security Center automations trigger actions (like sending to Event Hub) when specific security alerts or recommendations occur. Option A (Azure Sentinel) is incorrect because the automation is not creating incidents; it's forwarding alerts. Option C (Azure Monitor) is not involved.

Option D (Azure Policy) is for compliance, not alerting.

The query filters failed activity runs, groups by pipeline and hourly bins, then sorts by count descending to show pipelines with most failures. Option B is wrong because it's not about duration. Option C is wrong because it's not about all runs.

Option D is wrong because it's not about triggers.

Option C is correct because you create a database user mapped to the Microsoft Entra group, then grant SELECT on the schema to that user. Option A is wrong because RBAC at workspace level is too broad. Option B is wrong because it grants permissions to all users.

Option D is wrong because user mapping alone does not grant permissions.

Option C is correct because Cool storage is cost-effective for infrequent access, and a lifecycle policy moving to Archive after 30 days minimizes costs while retaining data for 7 years. Option A (Hot) is expensive for infrequent access. Option B (Premium) is for high throughput.

Option D (Archive immediately) incurs high retrieval costs for monthly access.

Option B is correct because Synapse RBAC roles like 'Synapse Artifact Publisher' control who can publish changes. Option A is wrong because Git permissions control access to the repository, not publishing to Synapse. Option C is wrong because Azure Policy enforces compliance rules, not access control.

Option D is wrong because Microsoft Entra ID is for identity, not specific publish permissions.

Option C is correct because partitioning the output and using a compatible table schema (e.g., columnstore index) improves write performance. Option A is wrong because the input partition scheme may not align with the output. Option B is wrong because batching can increase latency.

Option D is wrong because Azure SQL Database output has different tuning options; the question specifies Azure Synapse Analytics.

Option B is correct because the disabledAlerts list includes 'Sql_Injection' and 'Access_Anomaly', so those are suppressed. The remaining enabled alerts (e.g., SQL Injection Vulnerability, Data Exfiltration, Unsafe Action) will trigger alerts. Option A is wrong because SQL Injection is disabled.

Option C is wrong because Access Anomaly is disabled. Option D is wrong because the policy is enabled, so some alerts are active.

Options B, C, and D are correct. Success E2E Latency measures end-to-end latency, Blob Count tracks object count, and Ingress measures throughput. Option A is wrong because Available Storage Capacity is not a metric for storage accounts.

Option E is wrong because CPU usage is for compute, not storage.

Option C is correct because Stream Analytics jobs can use managed identity to authenticate to Azure Synapse Analytics. Option A is wrong because connection strings with SQL authentication are not managed identity. Option B is wrong because system-assigned managed identity is enabled automatically, but the output configuration must be set to use it.

Option D is wrong because a user-assigned managed identity is not required; system-assigned can be used.

Option B is correct because Microsoft Purview provides automated data classification and labeling for Azure Storage. Option A is wrong because Microsoft Sentinel is a SIEM, not for classification. Option C is wrong because Microsoft Defender for Cloud is for security posture, not data classification.

Option D is wrong because Azure Policy enforces rules but does not classify content.

Option B is correct: Managed VNet ensures data integration runs in a secure network boundary. Option D is correct: Azure Key Vault integration stores secrets securely. Option A is incorrect: Public endpoint access reduces security.

Option C is incorrect: Auto-resolve integration runtime may use public endpoints. Option E is incorrect: Self-hosted IR is for on-premises, not for cloud security.

Option C is correct: Set 'Azure AD-only authentication' to 'True' in the Azure SQL Database server properties. This disables SQL authentication and enforces Entra ID. Option A is incorrect: Azure RBAC controls management plane, not database access.

Option B is incorrect: SQL authentication is still enabled. Option D is incorrect: Conditional Access policies work with Entra ID but don't disable SQL authentication.

Option A is correct because Azure Monitor can create alerts based on ADF metrics like 'Failed pipeline runs'. Option B is wrong because Azure Service Health monitors Azure service health, not pipeline runs. Option C is wrong because Azure Log Analytics is for log queries, not alerting.

Option D is wrong because Microsoft Sentinel is for security.

Option D is correct because sys.dm_pdw_exec_requests shows currently running requests in a dedicated SQL pool. Option A is wrong because sys.dm_pdw_nodes is a system view, not for queries. Option B is wrong because sys.dm_pdw_request_steps shows steps of completed or running requests, not high-level status.

Option C is wrong because sys.dm_pdw_errors shows errors, not running queries.

Option A is correct because Azure Storage Encryption supports customer-managed keys via Key Vault. Option B is incorrect because Azure Information Protection is for classification and labeling. Option C is incorrect because Azure Disk Encryption is for VMs, not Blob Storage.

Option D is incorrect because Azure Purview is a data governance service.

Options B and D are correct. Using hash-distributed fact tables on the join key and replicated dimension tables are best practices for star joins. Option A is wrong because round-robin is for staging tables, not for fact tables.

Option C is wrong because clustered columnstore index is default and recommended, not heap. Option E is wrong because distribution on dimension tables should be replicated, not hash.

Enabling encryption at rest (Azure Storage Service Encryption) and using Azure RBAC to control access are both security best practices. Option C is wrong because firewall rules are network-level, not data-at-rest. Option D is wrong because soft delete is for recovery, not security.

Option E is wrong because diagnostic settings are for auditing, not encryption.

Option A is correct because Azure Stream Analytics provides job metrics in Azure Monitor, including watermark delay. Option B is wrong because Log Analytics can store logs but not directly show Stream Analytics metrics. Option C is wrong because Application Insights is for application monitoring, not Stream Analytics.

Option D is wrong because Azure Data Explorer is not for monitoring Stream Analytics.

Option A is correct because the policy denies any storage account that does not have the default service version set to '2020-10-02'. Option B is wrong because it denies, not audits. Option C is wrong because it applies to all storage accounts, not just those with defaultServiceVersion set.

Option D is wrong because it does not set the version; it denies if not set.