Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Easy
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?
- AData Pipelines with a Copy Data activity to a Lakehouse table.
- BSpark Notebooks with PySpark to read the JSON and write to a Lakehouse table.
- CDataflows Gen2 using the Web connector and Power Query Editor.
- DKQL Querysets directly querying the web API and saving results.
Show answer & explanationAnswer & explanation
Correct answer: C. Dataflows Gen2 using the Web connector and Power Query Editor.
Dataflows Gen2, powered by Power Query, excels at ingesting data from various sources, including semi-structured formats like JSON from web APIs. Its visual interface allows for easy application of basic transformations before loading data into a Lakehouse.
Why the other options are wrong
- A. Copy Data activities are better suited for structured data or when complex transformations are not required within the pipeline itself.
- B. Spark Notebooks are powerful for complex, large-scale transformations, but might be overkill and less efficient for basic transformations on semi-structured API data compared to Dataflows Gen2.
- D. KQL Querysets are for querying data already ingested, not for ingesting directly from external web APIs and applying transformations.
Dataflows Gen2 for API Ingestion
Dataflows Gen2 leverage Power Query to connect to and ingest data from various sources, including web APIs and semi-structured formats like JSON, enabling visual transformations.
- Uses Power Query Editor for visual ETL.
- Supports a wide range of connectors, including Web API.
- Ideal for ingesting semi-structured data and applying basic to intermediate transformations.
- Output can be loaded directly into a Lakehouse or Data Warehouse.
Memory trick: Web API data flows visually to the Lakehouse.