All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
18 min
Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
Pick the right tool to bring data into Fabric and land it in a lakehouse or warehouse.
By the end of this lesson you can
- Choose Dataflow Gen2, a pipeline, a Copy job, a notebook or T-SQL to ingest data
- Set a Dataflow Gen2 data destination and its update method
- Read COPY INTO, INSERT ... SELECT and CTAS loads in a warehouse
- Load a Dataflow Gen2 query into a lakehouse table
From earlier lessons · 1 of 2
A partner's files must stay in its Amazon S3 bucket, but you need them in a Lakehouse. What should you create?
Pick one answer.
From earlier lessons · 2 of 2
You turned on OneLake integration for an import semantic model. When are its tables first written to OneLake?
Pick one answer.
Every Monday, Juniper Coffee's bean supplier emails a new price-list CSV. Finance's analyst, who knows Excel and Power Query but no code, must clean it and load it. IT also wants five years of till history copied once into the warehouse, then only new rows each night. Two jobs, and the right tool is different for each.
Five ways to ingest
Exam depth: know it cold.
| Tool | How you build | Transforms | Best for |
|---|---|---|---|
| Dataflow Gen2 | Power Query, no code. | 300+ steps: clean, merge, add columns. | Analysts who need to reshape data on the way in. |
| Pipeline with a Copy activity | A canvas of steps. | Light: types, column mapping. | Copying as one step in a bigger, ordered flow. |
| Copy job | A guided wizard, no pipeline. | Light. | Plain copies, full or incremental. |
| Notebook | PySpark code. | Anything Spark can do. | Large or messy data, code teams. |
| T-SQL in a Warehouse | SQL scripts. | SQL logic over whole tables. | SQL teams loading Warehouse tables. |
Decision rule: no-code reshaping is Dataflow Gen2. A plain copy is a Copy job. A copy inside a multi-step flow is a pipeline. Code teams use a notebook (Spark) or T-SQL (Warehouse).
Dataflow Gen2 and its data destinations
Exam depth: know it cold.
Dataflow Gen2 is low-code data prep in Power Query, the same editor as in Excel and Power BI Desktop. It has 150+ connectors and 300+ transformations. Power Query records your clicks as steps in a language called M.
Each query can have its own data destination: where the result is written on every refresh. Add one with Add data destination on the Home tab, or from Query settings. Destinations include:
- Fabric Lakehouse (Tables or Files), Fabric Warehouse, Fabric KQL database, Fabric SQL database.
- Azure SQL database, Azure Data Explorer, ADLS Gen2, Snowflake, PostgreSQL and SharePoint files.
You load into a new table, which the dataflow creates and recreates if deleted, or an existing table. Then you publish the dataflow and set a refresh schedule.
The destination settings decide what happens to rows already there.
| Setting | What it does |
|---|---|
| Use automatic settings | On by default for new tables. Replace, managed mapping, and the table is dropped and recreated each refresh. |
| Replace | Deletes the old rows and writes the dataflow's output. |
| Append | Adds the output to the rows already there. |
| Dynamic schema | With Replace, lets the table's columns change on republish. |
| Fixed schema | With Replace, keeps the columns, so relationships and measures survive. |
Automatic settings work for Lakehouse, Azure SQL Database and Snowflake. A Warehouse destination needs staging turned on, supports only fixed schema, and must be in the same workspace. A KQL database can't use Replace.
You are an analytics engineer at Juniper Coffee. A Dataflow Gen2 loads the supplier's full price list into a Lakehouse table every Monday. The BI team added relationships to the table. They must never be dropped, even if someone edits the query by mistake. What destination settings should you use?
Pick one answer.
Pipelines and Copy job
Exam depth: recognize it.
A pipeline is a Fabric item that runs activities in order, on a schedule. The Copy activity moves data from a source to a destination. Other activities run a dataflow, a notebook or a stored procedure. Use a pipeline when the copy is one step in a bigger flow.
A Copy job is its own item. You set it up in a wizard, with no pipeline to build:
- Full copy copies everything on every run.
- Incremental copy copies everything once, then only new or changed rows. It finds them with a watermark, a column that grows with each change such as a modified date, or with CDC (change data capture), which also catches deletes. The Copy job remembers where it stopped.
- It writes with Append by default, or Merge, Overwrite or SCD Type 2.
- It runs once or on one or more schedules.
Both can reach on-premises or private sources through the gateways from lesson 6.
You are an analytics engineer at Juniper Coffee. You must copy five years of till sales from Azure SQL Database into a Warehouse, then only rows with a newer SoldAt time every night. No reshaping is needed, and you want the least build effort. What should you use?
Pick one answer.
Code-first: notebooks and T-SQL in a warehouse
Exam depth: know it cold for which statement does what; recognize the rest.
A notebook runs PySpark code that reads files and writes Delta tables to a Lakehouse. Pick it for large or messy data and teams that write Python.
In a Warehouse, T-SQL loads data itself.
| Statement | What it does |
|---|---|
COPY INTO | Bulk-loads CSV, Parquet or JSONL files. Sources: ADLS Gen2, Blob Storage or OneLake. |
INSERT ... SELECT | Appends a query's rows to a table that already exists. |
CREATE TABLE AS SELECT (CTAS) | Creates a new table from a query, in one step. |
SELECT ... INTO | Also creates a new table from a query. |
MERGE | Inserts, updates or deletes rows to match a source (recognize it). |
The query can read a Lakehouse table in the same workspace by its three-part name, such as juniper_sales.dbo.till_sales.
-- 1. Bulk-load this week's price files into an existing tableCOPY INTO dbo.supplier_pricesFROM 'https://juniperfiles.blob.core.windows.net/prices/*.csv'WITH (FILE_TYPE = 'CSV', FIRSTROW = 2);-- 2. Build a new daily sales table from a Lakehouse tableCREATE TABLE dbo.daily_sales ASSELECT store_id, CAST(sold_at AS date) AS sale_date, SUM(amount) AS revenueFROM juniper_sales.dbo.till_salesGROUP BY store_id, CAST(sold_at AS date);
You are an analytics engineer at Juniper Coffee. The Warehouse table dbo.daily_sales already exists. Each night you must add yesterday's rows from the Lakehouse table juniper_sales.dbo.till_sales, keeping the old rows. Which statement should you run?
Pick one answer.
Try it in Fabric: Dataflow Gen2 into a lakehouse table
A free Fabric trial and the juniper_sales Lakehouse from last lesson are enough. Juniper's data isn't public, so this practice run uses Microsoft's public OData sample feed of shop orders. Counting orders per customer is the same job you'd do on Juniper's loyalty orders.
- In your workspace, select + New item > Dataflow Gen2.
- Select Get data > More, then View more.
- Select Other > OData. Enter
https://services.odata.org/v4/northwind/northwind.svc/and select Next. - Select the Orders table, then Create.
- Select the CustomerID column, then Transform > Group By. Keep Count rows and select OK.
- In Query settings, rename the query orders_per_customer.
- At the bottom of Query settings, select Choose data destination > Lakehouse. Pick juniper_sales and a New table.
- Leave Use automatic settings on, then Save settings.
- Select Publish. When the spinner stops, the table appears in the Lakehouse.
- Select the Schedule Refresh icon to run it daily.
Exam-style practice
You are an analytics engineer at Juniper Coffee. A finance analyst knows Power Query from Excel but writes no code. Each month she must clean the budget workbook, merge it with the store list and load the result into a Warehouse table. What should she use?
Pick one answer.
You are an analytics engineer at Juniper Coffee. Each night you must copy the till tables, then run a dataflow that cleans them, then run a stored procedure that builds summary tables, in that order. What should you build?
Pick one answer.
Worth keeping
- No-code reshaping: Dataflow Gen2. Plain copy: Copy job. A copy among many steps: pipeline.
- Code teams load with a notebook (Lakehouse) or T-SQL (Warehouse).
- Dataflow destinations: Replace for full loads, Append for new rows, fixed schema to keep relationships.
- COPY INTO loads files, INSERT ... SELECT appends, CTAS creates a new table.
- A Warehouse destination for a dataflow needs staging and a fixed schema.
On the job
Here's how Juniper Coffee ends up loading data.
The Monday price list goes through a Dataflow Gen2 that finance's analyst owns. It trims the supplier's odd column names, fixes the date format and writes to a Lakehouse table with Replace and fixed schema.
The till history arrives through a Copy job: one full load, then incremental copies each night on the SoldAt watermark.
You write the last step yourself: a T-SQL script in the Warehouse runs INSERT ... SELECT from the Lakehouse tables into the reporting tables. Your Power BI model reads those tables, and nobody copies the data by hand again.
The price list loads, but it's still messy: some prices are text, some stores are spelled two ways. Next lesson: Power Query in Dataflow Gen2, where you filter, fix data types, merge and add columns.
Leaderboard
Loading…
50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.
Sources for this lesson (10)
Each fact in the lesson is marked with the number of the page it comes from.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [82]Microsoft Learn: Microsoft Fabric decision guide: Choose between Warehouse and Lakehouse
- [87]Microsoft Learn: Microsoft Fabric decision guide: Choose a data movement strategy
- [90]Microsoft Learn: Microsoft Fabric decision guide: copy activity, Copy job, dataflow, Eventstream, or Spark
- [91]Microsoft Learn: What is Dataflow Gen2?
- [92]Microsoft Learn: Dataflow Gen2 data destinations and managed settings
- [93]Microsoft Learn: Quickstart: Create your first dataflow to get and transform data
- [94]Microsoft Learn: What is Copy job in Data Factory for Microsoft Fabric?
- [95]Microsoft Learn: Ingest data into the warehouse
- [96]Microsoft Learn: Ingest data into your Warehouse using Transact-SQL