Microsoft DP-700 prep: Fabric Data Engineer Associate

Sign into Microsoft Fabric, explore Fabric and Power BI views, and set up a 60-day free trial with a work or school email while learning how to manage workspaces.

Learn why you need a work email address for Power BI and Fabric and how to obtain one via a Microsoft 365 business trial or by buying your own domain.

Create and configure a fabric capacity and fabric workspace to store and run fabric items, using a free trial and assigning the workspace; note expiration after trial.

Identify fabric capacity needs by comparing SKUs from f2 to f2048 and sizing for cost, reservations, pausing, and pay-as-you-go. Explain licensing types and OneLake storage implications.

Explore a quick tour of fabric, including lake house, warehouse, notebooks, and data flow Gen two, with SQL, Spark, pipelines, and security concepts.

Learn to ingest data with data flows and data flow gen two in Power Query, connecting to Excel, SQL Server, and CSV sources via the One Lake data hub.

Select and configure a data destination for your data flow, choosing lake house or targets, and decide between new or existing tables, append or replace, with fixed or dynamic schema.

Save a dataflow gen two as a template and schedule its refresh; export templates to parquet, using weekly or daily frequencies with time zones and failure notifications.

Enable fast copy in dataflow gen2 to bring pipeline copy speed to a graphical dataflow. Supported sources include data lake gen2, azure blob, sql databases, and lakehouse.

Monitor data transformation in data flows and diagnose issues by reviewing refresh history, statuses, duration, and details; explore staging, separate loading and transformation, and identify fabric bottlenecks to optimize performance.

Split large data flows, use pipelines to sequence tasks, and stage data for transformations; leverage query folding to run work on source databases and reduce timeouts.

Explore dataflow gen two in the home and view tabs. Import an Excel workbook via Power Query, promote headers, and adjust data types with applied steps and locale-aware conversions.

Explore removing rows and columns and applying filters and sorts in dataflows, including top rows, text and date filters, and basic column operations.

Explore grouping and aggregating data in a dataflow using group by, count rows, and multiple aggregations like average and percentile, while mastering duplicate versus reference queries.

Merge queries to denormalize data by joining dim product category and dim product subcategory, using inner, left, right, or full joins, and expand results into one consolidated table.

Append queries stack rows from multiple tables into a single dataset, with columns having the same name kept and differently named columns producing nulls; merging adds columns.

Identify and resolve duplicate, missing, and null data using column profiles, filtering, and replacing nulls; remove duplicates and apply right outer and right anti-join for existing but not ordered products.

Explore transform and add column operations in a dataflow, transforming existing columns or creating new ones, with functions for data types, text, numbers, dates, and conditional logic.

Create a data flow gen two from an Excel workbook, upload and format data, add a year column, and aggregate outer London by year in the Fabric lake house destination.

Learn the M language, the function language behind dataflows and Power Query, focusing on let and in, gui steps, and functions like promote headers, demote headers, and select columns.

Learn number functions in the GUI, including transform and add column operations, arithmetic (multiply, subtract, divide), rounding, abs, power, sqrt, mod, and is even or odd with sign.

Explore M text functions in Power Query and dataflows, mastering length, start, end, middle extraction, before/after/between delimiters, and text case, trim, clean, merge, and split column techniques.

Learn to manipulate date and time data with functions for extracting year, month, day, start and end of periods, and durations, plus current time in local or utc time zones.

Group rows by product key to compute total product cost, then apply aggregations like sum, average, median, min, max, and distinct; filter, sort, rename columns, and remove or keep rows.

Explore the M table functions in Power Query, such as reverse rows, transpose, replace values, fill up or down, append and merge queries, and pivot, unpivot, or expand tables.

learn to ingest data by using a data pipeline, add a data flow, and attach notebooks, stored procedures, and Office 365 Outlook emails to orchestrate concurrent activities.

Explore two ways to copy data in a pipeline: the copy data assistant and the copy activity, from lake house to warehouse, with mapping, staging, and validation.

Schedule data pipelines with daily, weekly, hourly, or every-minute repeats and set start and end dates; monitor runs via run history and the monitoring hub, and view input/output JSON.

Identify and resolve pipeline errors and optimize a data pipeline. Use run history and error messages like varchar 8000 to guide type conversion and data truncation decisions.

Explore sample data and templates for data pipelines, using copy data assistant to inspect datasets like covid-19 and taxi, and copy data from databases to lake house or warehouse.

Create a fabric lakehouse in a new workspace and import data from Excel with dataflow gen2, publishing pipelines that load fact internet sales into dim customer and dim sales territory.

Upload csv files to a fabric lakehouse, load them as tables (m to m actual) for faster sql queries; compare files versus tables and use One Lake File Explorer.

Choose copying method by data size and transformations: small files via local upload; small data via data flow; large data via copy tool; complex transforms via notebook code.

Ingest data with a notebook connected to a lake house, loading via spark or pandas, and copy results to a table using csv paths and relative ABFS paths.

Save a Spark data frame to files or delta tables using modes like overwrite or append, and formats such as csv, json, or parquet, linked to the medallion bronze-silver-gold workflow.

Learn how to load data from a table in PySpark or SQL, display results with df.show or display, and compare methods using spark.read.table, spark.read.format('delta').load, and spark.sql in a lakehouse.

Load the demography csv into the lakehouse as a new table using header names and a comma separator, then create a notebook to load it as a PySpark data frame.

Learn to reduce the number of columns in Spark dataframes by selecting specific columns, aliasing names like actual sales, and understanding case sensitivity and SQL equivalents.

Explore how to filter data using where, filter, and limit in PySpark and SQL, including top and tail operations.

Master advanced filtering with the where clause in PySpark and SQL, including between, contains, like, or, and, not, and is in (PySpark) or in (SQL) for dataframe queries.

Practice activity 4 using PySpark demonstrates creating dataframes, renaming and selecting columns, filtering geography keys, and deriving an ip location column with FR or GB country region codes.

Build spark sql select from geography table, rename city to state. Derive ip location using substring from ip address locator, and filter geography key >= 200 and FR or GB.

Convert data types with cast and convert in PySpark and Spark SQL, exploring int, decimal(p,s), bigint, varchar, date, and bool, and using describe to inspect results.

Import data with an explicit schema to enforce types in PySpark. Define a schema target with a struct of fields, load CSV with header true, and save as delta table.

Format dates as strings in PySpark with date_format, importing PySpark.sql.functions, and aliasing results; explore tokens for year, month, day, and weekday, plus concat and lit.

Aggregate data by country using group by, and sum the actual values, renaming the result to actual total. Use having to filter the aggregated totals above 10,000.

Learn how to sort data using order by and sort in PySpark and SQL, including sorting by location or country with default ascending order and optional descending.

Explore how to use all six SQL clauses—select, from, where, group by, having, and order by—in one query, with SQL and PySpark equivalents and aliasing the sum as actual total.

Load a PySpark data frame from the dim customer table, filter by yearly income 130,000, concatenate first and last names into full name, and compute average income by occupation.

Apply six principal clauses of the select statement to dim customer table, create full name with concat, format first purchase date, group by occupation, and use having on average income.

Learn to merge data using union and union by name in spark, handle differing column counts with nulls, and compare with SQL unions and dataflow gen two.

Identify and resolve duplicate data by grouping by country, location, and actual; use having or where, and employ distinct or drop duplicates in PySpark data frame and SQL.

Join actual and target sales data by country using an inner join, after aggregating and casting to int in PySpark and SQL, and save as delta tables for joined results.

Master inner, left, right, and full joins in PySpark and SQL, including renaming columns and handling alias conflicts; also explore dataflow gen2 joins for denormalized lakehouse schemas.

Identify missing data and null values across joins, rename columns to distinguish actual and target, and apply fillna, coalesce, and replace values in SQL, PySpark, and dataflow Gen two.

Create a PySpark bridge table from a distinct country list, union it with the target data, then left join to the actual data for the lakehouse result.

practice activity demonstrates transforming data in a lakehouse with PySpark by reading geography, selecting city and state province code, aliasing, filtering by country region code, and joining on geo key.

Learn to craft and combine SQL queries using select, union, and full join operations on dim geography and dim customer tables, handle nulls and aliases, and use having for duplicates.