Step2Study

Microsoft Certified: Fabric Analytics Engineer Associate practice questions

216 free questions with answers and explanations.

Practice test
  1. 151.A data engineer is using a Spark Notebook in Microsoft Fabric to process a large dataset of customer reviews. The dataset contains a 'review_text' column, which often includes HTML tags and special characters that need to be removed. The engineer also needs to convert all text to lowercase and remove common stop words. Which PySpark function or method is most efficient for cleaning the 'review_text' column in this scenario?Prepare and transform data (20-25%)
  2. 152.A data engineer is using a Dataflow Gen2 to ingest sales data from an Azure SQL Database. The sales data table includes a 'SaleDate' column of type `datetimeoffset`. The destination in the Lakehouse is a Delta table where the corresponding column should be stored as `DATE` (without time or offset information). Which Power Query transformation is the most direct and efficient to achieve this data type conversion?Prepare and transform data (20-25%)
  3. 153.A data engineering team is using Dataflows Gen2 to ingest and transform data from a diverse set of source systems, including SQL databases, REST APIs, and CSV files stored in Azure Data Lake Storage Gen2. They need to ensure that the data types are correctly inferred and consistently applied across all data sources to prevent data quality issues downstream. Which feature within Dataflows Gen2 should the team leverage to achieve this efficiently and reliably?Prepare and transform data (20-25%)
  4. 154.A data engineer needs to ingest log files from an Azure Data Lake Storage Gen2 account into a Fabric Lakehouse. The log files are organized in a hierarchical folder structure by year, month, and day (e.g., `logs/2023/01/01/log.json`). The requirement is to load all log files from a specific year and month into a single table, ensuring that new files added to that month's folder are automatically picked up in subsequent runs. Which Data Pipeline activity and configuration should be used?Prepare and transform data (20-25%)
  5. 155.A data engineer is designing a Data Pipeline to ingest data from an external REST API that requires an API key for authentication, which should be securely managed. The API key must not be hardcoded in the pipeline. Which method should the engineer use to store and retrieve the API key within the Data Pipeline?Prepare and transform data (20-25%)
  6. 156.A data engineer is working on a Dataflow Gen2 to ingest product catalog data from an external REST API. The API requires an API key to be passed in the request header for authentication. To ensure secure credential management and avoid hardcoding the key, how should the API key be handled in the Dataflow Gen2?Prepare and transform data (20-25%)
  7. 157.A global e-commerce company uses Microsoft Fabric to analyze customer order data. They have multiple Dataflows Gen2, each ingesting order details from different regional sales systems. The data engineer needs to combine the results of these Dataflows into a single, unified table in a Lakehouse for comprehensive reporting. How should the engineer achieve this efficiently within Microsoft Fabric?Prepare and transform data (20-25%)
  8. 158.A global e-commerce company needs to ingest product catalog data from multiple regional SQL Server databases into a centralized Fabric Lakehouse. Each regional database has an identical schema for the product table. The solution must ensure that data from all regions is combined into a single table in the Lakehouse, with minimal manual effort for schema evolution. Which Dataflows Gen2 feature is best suited for this scenario?Prepare and transform data (20-25%)
  9. 159.A data engineer is working on a Dataflow Gen2 to ingest sales data from an Azure SQL Database. One of the columns, `OrderDate`, is stored as a `VARCHAR` in the source database but contains valid date strings (e.g., '2023-10-26'). For analytical purposes in the Lakehouse, this column must be converted to a proper `Date` data type to enable date-based filtering and calculations. Which Power Query transformation should the engineer apply to the `OrderDate` column to achieve this conversion reliably?Prepare and transform data (20-25%)
  10. 160.A data engineering team is setting up an ingestion process for customer survey responses. The responses arrive as CSV files in an Azure Data Lake Storage Gen2 folder. The team needs to ensure that only new or modified files are processed in each run to avoid reprocessing unchanged data. Which feature of the Copy Data activity in a Data Pipeline should be configured to achieve this incremental loading efficiently?Prepare and transform data (20-25%)
  11. 161.A data engineering team is setting up an ingestion process for customer survey responses using Dataflows Gen2. The survey data is provided daily as new CSV files in an Azure Data Lake Storage Gen2 folder. Each CSV file contains responses for a single day. The team needs to ensure that only new files are processed and appended to the existing Lakehouse table each day, avoiding re-processing or duplicating historical data. Which Dataflows Gen2 feature is best suited for achieving this incremental file ingestion?Prepare and transform data (20-25%)
  12. 162.A data engineering team needs to ingest historical sales data from an on-premises Oracle database into a Microsoft Fabric Lakehouse. The data volume is large, and the ingestion process must be scheduled daily. Which Microsoft Fabric component is the most appropriate for this task?Prepare and transform data (20-25%)
  13. 163.A data engineer is working on a project to consolidate customer data from various regional databases into a central Lakehouse in Microsoft Fabric. Each regional database has slightly different schema variations (e.g., 'Cust_ID' vs 'CustomerID', 'First Name' vs 'FName'). The engineer needs to standardize these column names and data types, combine the datasets, and ensure data quality by removing duplicate records based on a unique identifier. Which feature within Dataflows Gen2 is primarily used for these types of schema and data quality transformations?Prepare and transform data (20-25%)
  14. 164.A data engineering team is setting up an ingestion process for customer survey responses. The responses are collected via a third-party service that provides a daily export of data in Parquet format, delivered to a specific Azure Data Lake Storage Gen2 path. The team needs to ensure that historical data is preserved and that new daily files are appended to the existing Lakehouse table without re-processing the entire dataset each day. Which Spark DataFrame write mode should be used to achieve this incremental loading strategy?Prepare and transform data (20-25%)
  15. 165.A data engineer is designing a data ingestion solution for a new e-commerce platform. The platform generates transactional data in real-time as JSON messages through an Azure Event Hub. These messages need to be captured, transformed, and loaded into a Lakehouse table with minimal latency for near real-time analytics. Which Microsoft Fabric component is specifically designed to ingest and process streaming data from Event Hubs?Prepare and transform data (20-25%)
  16. 166.A data engineer is using a Dataflow Gen2 to ingest sales transaction data from a REST API. The API returns data in JSON format, and some fields contain nested arrays of product details. The engineer needs to flatten these nested arrays into separate rows to create a tabular structure suitable for analysis. Which Power Query transformation should be used?Prepare and transform data (20-25%)
  17. 167.A data engineering team is using Dataflows Gen2 to ingest sales data from a legacy CSV file. The file uses a semicolon (`;`) as a delimiter instead of the standard comma. When the engineer attempts to load the CSV file into Dataflows Gen2, all data appears in a single column. Which configuration setting needs to be adjusted in Dataflows Gen2's Power Query Editor to correctly parse the file?Prepare and transform data (20-25%)
  18. 168.A company is migrating its on-premises SQL Server database to Microsoft Fabric. They have a large fact table, `SalesData`, containing over 500 GB of historical sales records, which needs to be ingested into a Lakehouse table. This ingestion needs to happen as a one-time full load, followed by daily incremental loads for new and updated records. The solution should be scalable and reliable. Which Microsoft Fabric tool is most appropriate for this scenario?Prepare and transform data (20-25%)
  19. 169.A data engineer is working on a Dataflow Gen2 to ingest product catalog data from an on-premises SQL Server database. The database contains multiple tables (e.g., Products, Categories, Suppliers) that need to be combined into a single, denormalized table in the Lakehouse. The engineer wants to perform a series of JOIN operations between these tables within the Dataflow Gen2's Power Query Editor to achieve the denormalized structure. Which Power Query transformation function should the engineer use to combine two tables based on a common key?Prepare and transform data (20-25%)
  20. 170.A data engineering team is using a Spark Notebook in Microsoft Fabric to clean and transform a large dataset of customer reviews. The dataset contains a column named 'review_text' which often includes leading/trailing whitespace, multiple internal spaces, and special characters that need to be removed or replaced. Which PySpark function is most efficient for performing these string cleaning operations across the entire DataFrame column?Prepare and transform data (20-25%)
  21. 171.A financial services company needs to ingest daily transaction data from an external vendor's SFTP server into a Lakehouse in Microsoft Fabric. The SFTP server requires authentication using an SSH key pair for security. The data engineering team plans to use a Data Pipeline for this ingestion. Which sequence of steps is required to securely configure the connection to the SFTP server?Prepare and transform data (20-25%)
  22. 172.A financial services company needs to ingest daily transaction data from an on-premises Oracle database into a Fabric Lakehouse. The data volume is moderate (tens of gigabytes per day), and the process requires reliable scheduling and monitoring. The company has already set up an On-premises data gateway. Which Fabric component should be used to orchestrate this data movement?Prepare and transform data (20-25%)
  23. 173.A logistics company uses a legacy ERP system that generates daily CSV files containing shipment details. These files are dropped into a specific folder in Azure Data Lake Storage Gen2. The data engineering team needs to ingest these files into a Lakehouse table, but only after ensuring that all columns have the correct data types (e.g., 'ShipmentID' as integer, 'ShipmentDate' as date) and that any rows with missing critical values (e.g., 'ShipmentID' is null) are rejected. Which PySpark DataFrame operation is most suitable for enforcing data types and handling nulls during ingestion in a Spark notebook?Prepare and transform data (20-25%)
  24. 174.A manufacturing company uses a legacy SCADA system that exports daily production logs as text files with a fixed-width format. Each record has specific fields like 'Timestamp' (14 chars), 'Product ID' (10 chars), and 'Quantity' (5 chars), always occupying the same character positions. A data engineer needs to ingest these files into a Lakehouse using a Spark Notebook in Microsoft Fabric, ensuring correct parsing of each field into its respective column. Which PySpark DataFrame function or method is best suited for efficiently parsing this fixed-width data?Prepare and transform data (20-25%)
  25. 175.A data engineer is using a Data Pipeline to ingest data from an on-premises SQL Server database into a Fabric Lakehouse. The SQL Server is behind a corporate firewall and is not directly accessible from the internet. Which component is required to enable secure connectivity between the Data Pipeline in Microsoft Fabric and the on-premises SQL Server?Prepare and transform data (20-25%)
  26. 176.A data engineer is designing a real-time analytics solution for an e-commerce platform in Microsoft Fabric. The platform generates a continuous stream of customer activity data (e.g., page views, add-to-cart events, purchases) that needs to be ingested, enriched, and made available for immediate dashboarding and alerting. The solution must support high throughput, low latency, and integration with other Fabric components like KQL databases and Lakehouses. Which Microsoft Fabric component is best suited for ingesting and processing this real-time event stream?Prepare and transform data (20-25%)
  27. 177.A data engineer is developing a Dataflow Gen2 to ingest data from an external REST API. The API requires a specific HTTP header, `X-API-Key`, with a sensitive API key value for authentication. To ensure security and avoid hardcoding the key, the engineer wants to store this key securely and reference it in the Dataflow. Which Microsoft Fabric feature should be used to manage this sensitive credential?Prepare and transform data (20-25%)
  28. 178.A data team needs to transform a large dataset (several terabytes) of customer interaction logs stored in a Lakehouse. The transformation involves complex regex parsing of log messages, sentiment analysis using a custom Python library, and then aggregating results by customer ID. The team requires a highly scalable solution with maximum flexibility for custom code. Which approach within Microsoft Fabric is the most appropriate?Prepare and transform data (20-25%)
  29. 179.A data engineer is using a Spark Notebook in Microsoft Fabric to process a large dataset of customer orders. The dataset contains a 'ProductDescription' column which often includes inconsistent spacing, special characters, and leading/trailing whitespace. The engineer needs to clean this column by removing all non-alphanumeric characters (except spaces), collapsing multiple spaces into a single space, and trimming leading/trailing whitespace, all while optimizing performance for a large DataFrame. Which PySpark function combination is most efficient for this task?Prepare and transform data (20-25%)
  30. 180.A data engineer is designing a solution to ingest real-time sensor data from IoT devices into Microsoft Fabric. The data arrives as a continuous stream of JSON messages, and it needs to be immediately available for near real-time analytics. Which Fabric component is specifically designed for this type of ingestion and initial processing?Prepare and transform data (20-25%)
  31. 181.A manufacturing company needs to analyze sensor data from IoT devices. This data arrives in nearly real-time in CSV format and is stored temporarily in a blob storage account. Before loading into a Lakehouse, the data needs to be aggregated hourly, and specific error codes (e.g., 'ERR-001', 'ERR-002') should be filtered out. The data engineers want to minimize manual coding as much as possible for this continuous process. Which Microsoft Fabric tool combination is the most efficient?Prepare and transform data (20-25%)
  32. 182.A retail company collects customer feedback data through various channels, resulting in semi-structured JSON files stored in Azure Data Lake Storage Gen2. Before this data can be analyzed, it needs to be parsed, flattened, and certain PII (Personally Identifiable Information) fields must be masked. The analytics engineering team prefers to use a code-first approach with robust data manipulation capabilities. Which Microsoft Fabric tool is best suited for these requirements?Prepare and transform data (20-25%)
  33. 183.A data engineer needs to apply a series of complex, custom business rules to transform a large dataset (hundreds of millions of rows) ingested into a Fabric Lakehouse. These rules involve conditional logic, aggregations, and joins with several other large lookup tables. The transformation must be highly performant and scalable. Which Spark transformation technique is generally considered the most efficient for this scenario?Prepare and transform data (20-25%)
  34. 184.A data engineer needs to ingest log files generated by a web application into a Lakehouse. These log files are stored in Azure Blob Storage and are typically 100-200 MB each, arriving frequently throughout the day. The ingestion process must be resilient to transient network issues and ensure data integrity. Which feature of the Copy Data activity in a Data Pipeline is crucial for meeting these requirements?Prepare and transform data (20-25%)
  35. 185.A data engineering team is tasked with ingesting data from a financial API that provides daily transaction records. The API requires authentication via an API key and returns data in JSON format. The team needs to apply basic transformations, such as filtering out test transactions and renaming a few columns, before loading the data into a Lakehouse table for further analysis. Which Microsoft Fabric tool is the most efficient and suitable for this ingestion and initial transformation process, given that the team prefers a low-code approach?Prepare and transform data (20-25%)
  36. 186.A data engineer is configuring a Data Pipeline in Microsoft Fabric to ingest CSV files from an Azure Data Lake Storage Gen2 account into a Lakehouse. The source ADLS Gen2 account has multiple containers and folders, and the file paths often include dynamic elements like dates (e.g., `/rawdata/sales/2023/10/26/data.csv`). The engineer needs to ingest files from a specific date range or a particular subfolder based on pipeline execution parameters. Which Data Pipeline feature should be used to achieve this flexible and dynamic file path ingestion?Prepare and transform data (20-25%)
  37. 187.A data engineer is using a Dataflow Gen2 to ingest and transform data from multiple CSV files stored in a folder in Azure Data Lake Storage Gen2. All files in the folder have the same schema. The engineer wants to combine all these files into a single table, apply a common set of transformations, and then load the aggregated data into a Lakehouse. Which Power Query transformation function is most efficient for combining multiple files with the same schema from a folder?Prepare and transform data (20-25%)
  38. 188.A data engineer is designing a real-time analytics solution for a smart factory. Sensor data from various machines (temperature, pressure, vibration) needs to be ingested continuously and immediately processed for anomaly detection. The data volume is expected to be extremely high, and low latency is critical. Which Microsoft Fabric component is best suited for ingesting this real-time, high-volume sensor data?Prepare and transform data (20-25%)
  39. 189.A global logistics company uses Data Pipelines in Microsoft Fabric to ingest tracking data from various regional operational systems into a central Lakehouse. Due to varying network conditions and source system availability, some ingestions occasionally fail or are delayed. The data engineering team needs a mechanism to automatically retry failed data copy activities with an increasing delay between attempts, and to limit the total number of retries to prevent indefinite loops. Which Data Pipeline configuration should they use?Prepare and transform data (20-25%)
  40. 190.A data engineering team is tasked with ingesting semi-structured JSON data from a web API into a Lakehouse in Microsoft Fabric. The data structure can vary slightly between calls, and the team needs to apply basic transformations like renaming columns and filtering rows before loading. Which Fabric tool is the most appropriate for this ingestion and transformation scenario?Prepare and transform data (20-25%)
  41. 191.A data engineer is developing a Data Pipeline in Microsoft Fabric to ingest daily sales reports from an Azure Data Lake Storage Gen2 (ADLS Gen2) container into a Lakehouse. The sales reports are CSV files, and their names follow a pattern like 'sales_report_YYYYMMDD.csv'. The engineer needs to ensure that only files from the current day are ingested. Which feature of the Copy Data activity should be used to achieve this dynamic file selection?Prepare and transform data (20-25%)
  42. 192.A data engineering team is using a Spark Notebook in Microsoft Fabric to perform complex ETL operations on a large dataset. They frequently need to apply a series of custom business rules that involve iterating over rows, performing conditional logic, and calling external libraries. Currently, they are using PySpark User-Defined Functions (UDFs) for these operations. However, the performance is significantly slower than expected for their large dataset. What is the most effective strategy to optimize the performance of these custom transformations in PySpark?Prepare and transform data (20-25%)
  43. 193.A data engineer is working with a large dataset (several terabytes) of customer interaction logs stored in a Fabric Lakehouse. The team needs to perform complex data cleansing, enrichment with external lookup tables, and aggregation before loading the data into a data warehouse. This process requires custom logic and highly optimized performance. Which Fabric tool is the most appropriate for these complex transformations?Prepare and transform data (20-25%)
  44. 194.A data engineer needs to ingest log files generated by a web application into a Lakehouse. The log files are stored in an Azure Blob Storage container. Due to potential network issues or temporary service outages, the ingestion process might occasionally fail. The engineer wants the Data Pipeline to automatically attempt to re-run failed ingestion steps a few times before giving up. Which setting in the Data Pipeline's Copy Data activity should be configured?Prepare and transform data (20-25%)
  45. 195.A data engineer is working on a Dataflow Gen2 to ingest product data from a legacy CSV file that uses a semicolon (`;`) as a delimiter instead of a comma. The file also contains a header row. Which option in the Power Query Editor's Text/CSV connector configuration should the engineer adjust to correctly parse this file?Prepare and transform data (20-25%)
  46. 196.A data engineer is optimizing a Spark SQL query that involves joining two very large tables, `orders` (100 GB) and `customers` (500 MB), both stored in a Microsoft Fabric Lakehouse. The `customers` table is relatively small compared to `orders`. The engineer wants to ensure that Spark broadcasts the smaller `customers` table to all executors to speed up the join operation. Which join hint should the engineer use in the Spark SQL query?Explore and analyze data (15-20%)
  47. 197.A data modeler is building a semantic model in Microsoft Fabric. The model needs to include a measure that calculates the 'Total Sales' for the current month, but only for products that had sales in the *previous* month. The 'Sales' table contains 'SaleAmount' and 'SaleDate'. A 'Date' dimension table is also available. Which DAX pattern should be used to achieve this calculation?Implement and manage semantic models (30-35%)
  48. 198.A data analyst is working with a large Spark Delta table named `customer_transactions` in Microsoft Fabric. The table contains columns like `transaction_id`, `customer_id`, `transaction_date`, and `amount`. The analyst needs to calculate the cumulative sum of `amount` for each `customer_id`, ordered by `transaction_date`. Which Spark SQL window function should the analyst use to achieve this?Explore and analyze data (15-20%)
  49. 199.A data engineer is working with a Spark Delta table named `product_reviews` in Microsoft Fabric. The table contains a `review_text` column and a `sentiment_score` column. The engineer needs to add a new column named `review_category` based on the `sentiment_score`. If the `sentiment_score` is greater than 0.75, the `review_category` should be 'Positive'. If it's less than 0.25, it should be 'Negative'. Otherwise, it should be 'Neutral'. Which PySpark function or method should the data engineer use to achieve this?Explore and analyze data (15-20%)
  50. 200.A data engineer is designing a semantic model in Microsoft Fabric. The model will contain sensitive customer information that should only be visible to specific sales regions. The requirement is that users from Region A should only see data for Region A, and users from Region B should only see data for Region B. There is no overlap in regional data access. Which security mechanism should the data engineer implement to achieve this granular control efficiently?Implement and manage semantic models (30-35%)