Hard Difficulty Questions

Write a custom Python script

Why wrong: While possible in notebooks, using custom Python scripts for simple merges is inefficient and harder to maintain compared to the native Dataflow GUI. Dataflow Gen2 is designed to abstract these complex operations into a low-code environment, providing better performance and native tracking of lineage within the Fabric workspace.

Create a temporary SQL view

Why wrong: Creating views in the source SQL database adds unnecessary dependency on the source system's compute resources. Dataflow Gen2 is designed to perform these transformations in the cloud, offloading the work from the source system to prevent performance degradation on production databases, which is a key principle of modern data engineering.

Export both to CSV first

Why wrong: Exporting data to intermediate CSV files introduces unnecessary latency and complexity. It breaks the automated ingestion flow and creates manual steps that are prone to error. Dataflow Gen2 can connect directly to both sources, allowing for seamless integration without the need for interim storage or manual file manipulation.

The service principal needs the 'Contributor' role to read data.

Why wrong: The 'Viewer' role is specifically designed for read-only access and should be sufficient for reading data from OneLake. Moving to 'Contributor' provides write permissions, which are unnecessary for simple data reading and would violate the principle of least privilege in a production analytics environment.

The service principal has exceeded its OneLake throughput quota.

Why wrong: Exceeding a throughput quota would typically result in a 'Throttling' or 'Too Many Requests' error (HTTP 429), not an 'Access Denied' error (HTTP 403). Access Denied is strictly related to authentication and authorization settings rather than the volume of data being read or the rate of requests.

OneLake does not support service principal authentication.

Why wrong: OneLake fully supports service principal authentication through standard Azure Active Directory (now Microsoft Entra ID) mechanisms. This is a common practice for automated pipelines and service-to-service communication within Fabric, making this explanation technically incorrect regarding the platform's capabilities and security architecture.

Microsoft Purview

Why wrong: Microsoft Purview is primarily used for data governance, cataloging, and security auditing across data estates. While it tracks lineage and sensitivity labels, it does not provide the performance metrics or compute utilization data necessary to monitor capacity health and throttling events.

Service Health Dashboard

Why wrong: The Service Health Dashboard is designed to show global status information regarding Microsoft service outages and planned maintenance. It does not provide insights into your specific tenant's resource consumption, user-level workload activity, or the granular throttling metrics required for performance optimization.

Power BI Desktop Performance Analyzer

Why wrong: The Performance Analyzer in Power BI Desktop is local to the report developer and identifies performance bottlenecks within a specific PBIX file. It cannot provide capacity-wide metrics or monitor compute throttling occurring on the Fabric backend during scheduled cloud refreshes or pipelines.

The Lakehouse is only accessible from within a specific Azure VNET.

Why wrong: Managed Private Endpoints secure the connection *from* Fabric *to* an external data source (like Azure SQL or ADLS Gen2). They do not, by themselves, restrict how users access the Fabric workspace or Lakehouse from their own client machines or corporate networks, which is handled by different networking settings.

The Lakehouse has been shared with an external guest user.

Why wrong: The 'hasManagedPrivateEndpoint' property is strictly a networking and infrastructure configuration. It has no relationship with the sharing status of the item or the presence of B2B guest users. Sharing and external access are managed through the workspace permissions and Entra ID (formerly Azure AD) settings.

OneLake shortcuts are disabled for this specific Lakehouse.

Why wrong: The presence of a Managed Private Endpoint does not disable shortcuts. In fact, Managed Private Endpoints are often used *to enable* shortcuts to work securely with data sources that are otherwise unreachable due to network restrictions. They are complementary features rather than mutually exclusive configurations.

Object-Level Security (OLS)

Why wrong: OLS restricts access to entire tables or columns rather than specific rows. If the requirement is to restrict access based on department while keeping the schema consistent, OLS is the wrong tool. OLS is used for hiding sensitive columns, not for filtering records based on user attributes.

Workspace Permissions

Why wrong: Workspace permissions govern access to the artifact, not the content within the data. Giving someone access to the workspace grants them full access to the underlying data. It is a coarse-grained control method that does not provide the granularity required to perform row-level filtering based on user department.

Data Masking

Why wrong: Data Masking obscures sensitive information within a column, like hiding part of an email address or credit card number. It does not filter rows out of a result set. For department-based row filtering, Data Masking would be ineffective and inappropriate, as it doesn't remove the unauthorized data records.

The connection must be configured to use the Gateway.

Why wrong: DirectLake is designed specifically to eliminate the need for an on-premises data gateway for Fabric-based data. Using a gateway would defeat the purpose of the DirectLake architecture, which is meant to provide high-speed access to data residing within the same cloud-based Fabric environment.

The data must be cached in the Power BI Service using Import mode.

Why wrong: DirectLake is specifically designed as an alternative to Import mode. Forcing an import defeats the benefits of DirectLake, which is intended to provide real-time updates and low memory usage. Enabling import would introduce data latency and increase storage overhead, contradicting the efficiency goals of this connection type.

Write a Spark notebook that uses 'mssparkutils' to move items.

Why wrong: While 'mssparkutils' is useful for file and lakehouse management, it does not have the capability to trigger workspace-level deployments or interact with the deployment pipeline service. Using a notebook for this purpose is an architectural misalignment, as notebooks are designed for data processing rather than infrastructure orchestration. The REST API is the correct tool for lifecycle automation.

Manually click the 'Deploy' button in the Fabric portal for every release.

Why wrong: Manual deployment is not an automated approach and fails to meet the requirement for a CI/CD pipeline integration. It is prone to human error, lacks auditability within the DevOps toolchain, and does not scale well for large teams with frequent updates. Automation is key to achieving the consistency and speed required in modern data engineering practices.

Configure a 'Data Factory' pipeline with a 'Copy' activity to move metadata.

Why wrong: Data Factory pipelines are intended for moving and transforming data, not for deploying workspace items or managing the Fabric lifecycle. Metadata for Fabric items is not stored in a way that a standard Copy activity can move it between stages to effect a deployment. This approach would result in data movement rather than the promotion of analytical artifacts.

The Lakehouse has too many OneLake shortcuts to external Azure regions.

Why wrong: While shortcuts might impact data access latency, they do not inherently cause a fallback to DirectQuery mode in a semantic model. Fallback is typically triggered by metadata mismatches, unsupported DAX features, or physical data layout issues within the Delta tables themselves. Shortcuts are transparent to the Direct Lake engine as long as the data is in the correct Parquet format.

The capacity has 'Bursting' enabled, which interferes with memory caching.

Why wrong: Bursting is a compute management feature and does not interfere with the logic of how the Power BI engine reads Parquet files from OneLake. Memory caching for Direct Lake is managed by the Power BI service's internal mechanisms and is independent of whether the capacity is currently bursting. Fallback is a functional limitation rather than a compute resource scheduling issue.

The user viewing the report has only 'Viewer' permissions on the workspace.

Why wrong: Permissions determine whether a user can see the data, but they do not change the technical execution mode of the semantic model. If a model is capable of running in Direct Lake mode, it will do so for any user who has access to the report. Fallback is caused by the technical configuration of the model or the data, not the user's role.

Manually export item .bim files and upload them to a GitHub repository.

Why wrong: Manual exports are prone to human error and do not provide the automated synchronization or change tracking offered by native Git integration. This approach bypasses the built-in Fabric source control features and makes it difficult to maintain a consistent state between the workspace and the repository. It is an inefficient method that lacks the governance required for modern analytics.

Assign the Workspace Admin role to the Service Principal used for Git.

Why wrong: While permissions are necessary, Git integration in Fabric typically uses the identity of the logged-in user rather than a standalone Service Principal for standard workspace sync. Assigning excessive admin roles to a Service Principal does not facilitate the version control process itself. Focus should remain on user permissions and repository access rights to ensure secure and effective synchronization.

Convert all Lakehouses to Warehouses to enable Git compatibility.

Why wrong: Both Lakehouses and Warehouses are supported in various capacities by Fabric's Git integration features, so converting types is unnecessary and counterproductive. Each artifact type has its specific use case, and Git integration is designed to handle different item types by serializing their unique metadata formats. Forcing a conversion would disrupt the data architecture without providing any version control benefits.

Create a Data Pipeline to copy Delta tables daily.

Why wrong: Copying data between workspaces creates redundant storage and introduces synchronization delays, which contradicts the goal of avoiding data duplication. This approach increases costs and makes it difficult to ensure that all departmental workspaces are looking at the most current version of the data. It also adds unnecessary complexity to the overall data orchestration and maintenance.

Export the central Lakehouse as a template and re-import it.

Why wrong: Exporting and re-importing a Lakehouse only replicates the structure and metadata, but it does not maintain a link to the original data or share the existing content. Each departmental workspace would still need its own data load process to populate the Lakehouse. This method fails to address the requirement for a shared, validated data source without duplication.

Configure cross-workspace Power BI DirectQuery models.

Why wrong: While cross-workspace Power BI models allow for report-level sharing, they do not address the need for departmental data engineers to access the raw Lakehouse tables for further processing. This approach limits the departmental users to visualization tasks only, whereas shortcuts provide full access to the underlying data for diverse analytical needs including Spark and SQL.

The pipeline fails immediately.

Why wrong: The configuration explicitly defines a retry policy with a count of 3. Therefore, the pipeline will not fail immediately. It will wait for the specified interval and then attempt the operation again to recover from the transient failure detected during the first execution.

The pipeline retries indefinitely.

Why wrong: The retry count is explicitly set to 3. There is no configuration in the exhibit that would trigger an infinite loop or indefinite retries. The pipeline will stop retrying once the defined limit of 3 attempts is reached, after which it will report a final failure.

The pipeline executes the next activity immediately.

Why wrong: If the Copy activity fails and the retry policy is active, the pipeline engine pauses execution to perform the retry. It does not skip the current activity or proceed to downstream activities until all retry attempts are exhausted or the activity finally succeeds.

Increase the number of partitions to 10,000.

Why wrong: Setting an arbitrarily high number of partitions often causes extreme overhead due to task scheduling and metadata management. This can lead to slow performance and can actually increase memory pressure on the driver node, which has to manage the scheduling of thousands of tasks concurrently.

Upgrade the Fabric capacity to the highest SKU.

Why wrong: Throwing more resources at a bad execution plan is an expensive and inefficient way to solve problems. While it might mask the issue, the underlying skew or inefficient transformation logic remains. Always optimize the code first before resorting to scaling up resources, as this ensures long-term cost-efficiency.

Use the .collect() method before writing the output.

Why wrong: The .collect() action pulls all data from the distributed Spark executors into the memory of the driver node. This is the most common cause of OOM errors in Spark. It should be avoided at all costs when processing large datasets, as the driver node cannot handle massive amounts of data.

A Pipeline with a Copy Activity using a 'Tumbling Window' trigger.

Why wrong: Tumbling Window triggers allow for scheduled data movement but still rely on periodic polling of the source database. This approach creates recurring load on the source and is not truly near real-time, as it introduces latency based on the interval of the window defined in the pipeline.

A Dataflow Gen2 with incremental refresh enabled.

Why wrong: While Dataflow Gen2 supports incremental refresh, it still requires a scheduled refresh interval and executes Power Query logic to identify changes. This process is generally more resource-intensive on both the source and Fabric compared to the log-based change data capture used by the Mirroring feature.

A Spark Notebook using the 'JDBC' connector in a loop.

Why wrong: Using a Spark Notebook in a continuous loop to query a source database is highly inefficient and difficult to manage. It places a significant and constant load on the source database's compute resources and requires custom error-handling logic to ensure data consistency and reliability.

Grant the service principal the 'Global Administrator' role in Entra ID.

Why wrong: Granting Global Administrator rights is a violation of the principle of least privilege and is not required for Fabric API access. Service principals only need the specific workspace-level roles or tenant-level API permissions necessary for the tasks they are designed to perform within the environment.

Assign a Power BI Pro license directly to the service principal.

Why wrong: Service principals do not require individual user licenses like Power BI Pro. Their access is governed by tenant settings and workspace roles. As long as the content is on a supported capacity, the service principal can perform actions without the overhead of per-user licensing costs.

The workspace must be assigned to a Pro or Premium Per User (PPU) license.

Why wrong: Workspace Managed Identities are a capacity-based feature and are not tied to individual Pro or PPU licenses. They require the workspace to be hosted on a dedicated capacity (Fabric or Power BI Premium) that supports the identity management features required for secure service-to-service communication.

The identity must be manually created in Microsoft Entra ID before it can be assigned to the workspace.

Why wrong: Unlike User-Assigned Managed Identities, a Workspace Managed Identity is automatically created by Fabric when the feature is enabled for the workspace. The system handles the lifecycle of the identity, ensuring it is uniquely tied to that specific workspace without requiring manual pre-provisioning in Entra ID.

The Managed Identity can only access resources within the same Azure region as the Fabric capacity.

Why wrong: Managed Identities can access resources across different Azure regions, provided the appropriate permissions are granted and network connectivity is available. There is no inherent regional restriction that prevents a Fabric workspace identity from accessing a storage account located in a different geographical location.

Manual file copying using the OneLake file system explorer.

Why wrong: Manual file copying is error-prone, violates CI/CD principles, and lacks traceability. Deployment must be automated through platform-native tools to ensure consistency across environments. Relying on manual processes introduces configuration drift, making it difficult to maintain reliable production environments and increasing the risk of downtime or data corruption.

Hardcoded connection strings in Notebook code.

Why wrong: Hardcoding connection strings is a major anti-pattern that inhibits portability across environments. It forces code changes for every deployment, increasing the risk of errors. Instead, connections should be managed via environment-specific configurations or secret vaults, allowing the same code to work in different stages without modification or security exposure.

VACUUM table_name

Why wrong: VACUUM removes old files that are no longer referenced by the transaction log, helping manage storage costs. It does not compact existing files or improve query performance through data organization. While important for maintenance, it is not the correct command for addressing small file overhead issues.

ANALYZE TABLE table_name COMPUTE STATISTICS

Why wrong: ANALYZE updates metadata statistics, which helps the query optimizer make better decisions about join strategies and scan plans. It does not physically rearrange or merge the underlying data files, meaning it will not solve the issue of excessive small files causing slow read performance.

REORG TABLE table_name APPLY

Why wrong: There is no REORG command in standard Delta Lake implementations. This command is typically associated with legacy database systems like DB2 or SQL Server. Using invalid syntax will result in execution errors, as it is not part of the Spark-based Delta Lake command set used in Fabric.

Add "requestInterval": "00:00:05" within the RestSource typeProperties section to throttle rapid API calls.

Why wrong: Throttling requests with an interval helps avoid rate-limiting errors from APIs, but it does not address individual HTTP request timeouts caused by slow-responding servers rendering large data payloads during active downloads.

Add "paginationRules": {"supportRFC5988": "true"} to automatically handle next-link headers provided by the API.

Why wrong: Configuring pagination rules such as RFC5988 support enables the copy activity to correctly parse continuation headers and iterate through multi-page API responses without exceeding single-request payload limits or timing out on large datasets.

Add "maxConcurrentConnections": 10 within the sink storeSettings to increase parallel write throughput.

Why wrong: Increasing concurrent connections affects how data is written to the destination Lakehouse, but it has no impact on the upstream HTTP source timeout behavior when retrieving slow pages from an external web service.

Add "compressionType": "GZip" to the source dataset definition to reduce payload size over the network.

Why wrong: Most REST APIs return uncompressed JSON unless explicitly negotiated via HTTP headers. Forcing a compression type at the source connector level without API support results in malformed requests rather than fixing timeouts.

Increase the 'numExecutors' value to 10.

Why wrong: Increasing the number of executors actually increases the resource demand on the Fabric capacity. During periods of high utilization, requesting more executors is more likely to result in the job being queued or failing due to lack of available CUs, rather than helping it succeed during contention.

Change the 'runtimeVersion' to 1.3.

Why wrong: Upgrading the runtime version provides newer Spark features and library updates but does not directly address resource availability or transient failures caused by capacity throttling. Runtime versions are generally changed for compatibility or performance improvements within the Spark engine itself rather than for retry logic.

Change the 'driverSize' to 'Large'.

Why wrong: Selecting a larger driver size allocates more memory and CPU to the Spark driver node. While this helps with jobs handling large metadata or complex orchestrations, it does not help with retry logic or capacity-based failures; it actually consumes more Capacity Units, potentially worsening the contention issue.

Convert all tables to heap structures.

Why wrong: Heap structures lack inherent ordering, forcing the SQL engine to perform full table scans for most analytical queries. This results in poor performance for large datasets, as the engine cannot leverage metadata for data skipping or efficient retrieval, making heaps unsuitable for primary fact tables in a data warehouse.

Increase the number of rows per file to 1 billion.

Why wrong: Extremely large files hinder parallel processing capabilities in Fabric. The engine benefits from a balance where file sizes are large enough to be efficient but small enough to allow for effective parallel tasks. A billion rows per file would likely lead to massive memory pressure and failed query executions.

Disable statistics collection for all columns.

Why wrong: Statistics are vital for the query optimizer to create efficient execution plans. Without accurate statistics, the optimizer might choose suboptimal join orders or scan methods, leading to severe performance degradation. Managing statistics is a core maintenance task for ensuring the engine generates the most performant execution plans.