Taking the Azure Fabric Ignite Edition Challenges to Complete

Documenting Microsoft Learning challenge on fabric

Ignite Edition Challenge: Prepare for the next generation of data analytics with Microsoft Fabric

Microsoft Fabric - Unified data analytics platform

Introduction to end-to-end analytics using Microsoft Fabric : Link

These notes follow a sales-data workflow through the challenge: create a workspace and lakehouse, explore the data with Spark, automate ingestion, then load a warehouse. The private archive screenshots record my lab checkpoints rather than a production implementation. The original security module stopped at an outline; the security design and solved checks below now explain the missing concepts. The historical lab checkpoints still do not establish a current deployment or verified permissions.

Azure fabric workspace: https://app.fabric.microsoft.com/home

• Getting started with the intro module, fabric is a analytics platform with all the required services for data integrated to one service. It has services integrated to ingest, store, transform and analyze data all in one place.
• Exciting for any member in data field having all in one place and managing it will be simpler than managing 4-5 different services in different places and permissions across services.
• Fabric has integrated data lake called onelake, OneCopy is a reuse goal: multiple engines can access shared data, but transformations, caches, semantic models and some connectors can still create copies and consume compute. link
• One Lake is built on top of ADLS Azure. Data can be stored in all open formats like delta, parquet, csv, JSON. OneLake is the shared storage layer; do not infer that every workload, cache and external source is physically stored there
• In Onelake we can create shortcuts and point the data assets to other services , shortcuts reference source data subject to the connector, caching and permission behavior; they do not guarantee universal immediate synchronization.
• Workspaces - we can control resources and access controls on the workspaces and differentiate DEV UAT PROD env.
• Workspace resources can be integrated to GIT and deploy, we can configure compute resources and other config details directly from ADO.
• Fabric brings together data ingestion and storage with OneLake, and Fabric Data Factory provides pipeline/dataflow ingestion and orchestration; Direct Lake is a Power BI semantic-model storage mode, not an ingestion connector, easy integration to Power BI and much more.
• we need an organizational email to create a trail account , process : link , free trial restrictions and capabilities : link

The first three screenshots cover access and workspace setup: signing in, activating trial capacity and creating the workspace. They establish where the later lab artifacts live; they do not yet show an ingested or transformed dataset.

• Login to Azure fabric : https://app.fabric.microsoft.com/home , you can provide access to other users even in trail account.
• OneLake stores files; a managed lakehouse table normally uses Delta, while uploaded CSV/JSON/Parquet files stay files until explicitly transformed/registered. Intro to fabric is module is now complete, on to the next module in this challenge.

Get started with lakehouses in Microsoft Fabric Module : link

• Foundation of Fabric is lakehouse built on top of onelake storage layer, good integration to compute engines for processing.
• lakehouse data is organized as schema on read, stores data by default as delta table format but supports all file formats.
• Ingest data to lakehouse from many different sources and also there is a concept of shortcuts where we can link data from ADLS gen2 or some external sources directly.
• Access to lakehouse can be managed at workspace level or item-level sharing, item-level access is useful for granting read-only access for reporting or analytical needs.
• Lakehouse supports data governance features , sensitivity labels (microsoft purview). Transformation on ingested data can be done using spark or dataflows gen 2.
• Creating a lakehouse provisions the lakehouse and its SQL analytics endpoint. Since 5 September 2025, default semantic models are no longer automatically created for new lakehouses/warehouses; create and maintain a reporting model explicitly.
• Common ingestion options include, upload data(local data) , Dataflow Gen2 (import and transform using power query) , Notebooks (will have access to pools) , Data Factory copy activity. We will have two options load to place files directly or load to tables.
• we can use custom JARS to create frameworks in spark and provide for custom implementations. - more info in the link : spark-config
• Shortcuts refer to data without requiring a full initial copy. Identity depends on shortcut type and consuming engine: direct OneLake access normally checks the caller at the target, external shortcuts use a configured connection, and delegated SQL/semantic-model modes can use an item owner or fixed identity. Test the actual access path instead of assuming every query forwards the end user. - more info on creating short cuts in lakehouse : shortcuts
• There are three mechanisms to ingest or transform data - notebooks (pyspark,scala, SQl), dataflow gen2( power query interface) , Pipelines to do Ingesting, transforming and loading.
• The final enriched data can be accessed from notebooks (for ML development or analytical), using semantic model users can build power BI reports, also analysts can use SQL endpoint to query data.
• Doing the exercise on creating lakehouse in my trail account - link
• Using visual tools for transformation and querying using sql pool, below attached few dashboard pics created on ingested sales data.
• Complete lab instructions : lab-link

In the lakehouse exercise, follow the sales data from storage to a queryable view and then to reporting. The archived screenshots are checkpoints in that path. Keeping those roles separate helps explain why a lakehouse, SQL endpoint and reporting model can refer to related data without being the same interface.

• Final view of lakehouse in the workspace for the exercise.

• Completed this module , on to the next module ...

Use Apache Spark in Microsoft Fabric : Link

• In this module looking in to configuring spark in fabric, scenarios for spark jobs, utilizing spark df,sql and visualize data in spark notebooks.

• In fabric we have spark pools to compute and process data, the pools contains two kind of nodes - A head node which coordinated and distributes process through driver program. - worker nodes are where the distributed compute happens

• we will initially have starterpool but we can also create custom pool depending on our workload and compute needs.

• while creating a new pool, we have options to select either memory optimized pools or other (Node family). In node size we have small, medium, big pools to select from. It also has auto scale and dynamically allocate executor bars where we can select and provide the range. more details on custom spark pool : link

• An environment selects a compatible runtime, libraries and compute settings; a pool is compute capacity, not a simultaneous mixture of runtime versions. These examples use the historical Fabric Runtime 1.3 (Spark 3.5, Delta 3.2, Python 3.11) contract. The October 2026 documentation marks 1.3 end-of-support-announced, so review its successor before new deployments. runtime documentation : link

• we can create custom env with specifying spark version, check built-in functions , add public libraries and custom libraries, specify spark pool that env should use and over ride any default spark configurations , we can also upload resource files which are accessible in the env. more details : link

• The native execution engine is enabled through the environment’s Acceleration setting in current documentation; the older spark.native.enabled/ColumnarShuffleManager magic is not a durable setup recipe. Select a supported runtime and test operator fallback and results before assuming every query accelerates. This review changes no environment or Spark configuration.

• we can enable high concurrency to share a Spark session for compatible workloads with notebook isolation rules; sharing limits and identity constraints are version-specific, not a fixed two-user guarantee, we also have MLFlow logging option to log model training and management operations for ML workloads and experiments.

• we have two modes to invoke a spark session, by notebook or a spark job. Use notebook mode to do interactive analysis or define a spark job to run a script on demand or on schedule. this option is available when clicked on new item in the workspace page.

  The pool and environment settings above provide the compute; this example supplies the work. It loads the sales CSV into a DataFrame, selects and filters columns, aggregates by date, and writes Parquet output. Compare those operations with the notebook screenshot rather than treating the screenshot as a standalone result.

The snippets are retained as lab notes, not a tested script: the corrected schema uses a string name and numeric quantity/prices; notebook magics/Scala cells remain separate notebook examples. Validate the header and column types before relying on the filters or sums below.

Code blocks and all the data used here is sales data : sales

# Python cell (select PySpark as the cell language)
df = spark.read.load('Files/data/sales.csv',
    format='csv',
    header=True
)
display(df.limit(10))
 

A separate Scala cell expresses the same read:

val df = spark.read.format("csv").option("header", "true").load("Files/data/sales.csv")
display(df.limit(10))

A separate PySpark cell declares a schema. The inspected sample contains four fractional digits in some UnitPrice/TaxAmount values, so preserve scale 4 rather than rounding on ingest. Check the actual CSV header/order before use:

# Defining schema for loading
 
from pyspark.sql.types import *
from pyspark.sql.functions import *
 
productSchema = StructType([
    StructField("SalesOrderNumber", StringType()),
    StructField("SalesOrderLineNumber", StringType()),
    StructField("OrderDate", DateType()),
    StructField("CustomerName", StringType()),
    StructField("EmailAddress", StringType()),
    StructField("Item", StringType()),
    StructField("Quantity", IntegerType()),
    StructField("UnitPrice", DecimalType(18, 4)),
    StructField("TaxAmount", DecimalType(18, 4))
    ])
 
df = spark.read.load('Files/data/sales.csv',
    format='csv',
    schema=productSchema,
    header=True,
    dateFormat='yyyy-MM-dd',
    mode='FAILFAST',
    enforceSchema=False)
display(df.limit(10))
 
#Selecting required columns
pricelist_df = df.select("OrderDate", "EmailAddress")
 
# Filtering with some conditions
bikes_df = df.select("OrderDate", "EmailAddress", "UnitPrice").where((df["UnitPrice"]>=3200) & (df["OrderDate"] >= "2019-07-05") )
 
# Revenue excluding tax is quantity * unit price, not the sum of unit prices.
counts_df = df.groupBy("OrderDate").agg(
    sum(col("Quantity") * col("UnitPrice")).alias("RevenueBeforeTax"))
display(counts_df)
 
# Just getting group by with count
counts_df = df.select("OrderDate", "EmailAddress", "UnitPrice").groupBy("OrderDate").count()
display(counts_df)
 
# Writing to parquet
# Optional write, only in a new disposable lab workspace; not executed here.
bikes_df.write.mode("errorifexists").parquet('Files/batch1_sales_lab/bikes.parquet')
 
# Writing to a partition
bikes_df.write.partitionBy("OrderDate").mode("errorifexists").parquet("Files/batch1_sales_lab/bike_data")
 
# Reading from a partition 
road_bikes_df = spark.read.option('basePath', 'Files/batch1_sales_lab/bike_data').parquet(
    'Files/batch1_sales_lab/bike_data/OrderDate=2019-07-05')
display(road_bikes_df.limit(5))
 
# basePath tells Spark where partition discovery begins; OrderDate stays available.
 

• spark sql, we can create the view from the df and then use spark sql to write queries, this is temp view only exists in the session. If we need to persist we can save as table.
• Microsoft fabrics preferred method of saving is delta format, the spark catalog support tables of various formats. we can also create external tables pointing data to external storage location, typically folder in lakehouse. Note : Deleting an external table doesn't delete the underlying data.
• Delta tables support partitioning; do not assume Hive-style bucketing is supported for Delta. Choose a partition key only when data size/cardinality justify it. %%sql runs a separate Spark SQL cell, not Python syntax.

df.createOrReplaceTempView("sales_view")
 
# Optional table creation in a disposable attached lakehouse.
# errorifexists prevents silently replacing an existing table.
df.write.format("delta").mode('errorifexists').saveAsTable("batch1_products")
 
 
df_products = spark.sql("SELECT * FROM batch1_products")
display(df_products.count())
 
 
#Partitioning the delta table for better performance
 
df.write.format("delta").mode("errorifexists").partitionBy("OrderDate").saveAsTable("sales_partitioned")
 
 
sales_df = spark.sql("SELECT * \
                      FROM sales_partitioned ")
display(sales_df.count())
 
 

Run this as a separate Spark SQL cell (or place %%sql as its first line in a notebook):

SELECT OrderDate, COUNT(SalesOrderNumber) AS ProductCount
FROM sales_partitioned
GROUP BY OrderDate
ORDER BY OrderDate
 

To make the aggregation readable, the next example counts sales-order rows by date and plots those counts. Despite the ProductCount label, it is not summing the Quantity column or counting distinct product types. Aggregate in Spark first: converting the result to pandas collects it into driver memory, so this pattern is appropriate only when the resulting table fits there.

from matplotlib import pyplot as plt
 
# Get the data as a Pandas dataframe
data = spark.sql("SELECT OrderDate, COUNT(SalesOrderNumber) AS ProductCount \
FROM sales_partitioned \
GROUP BY OrderDate \
ORDER BY OrderDate").toPandas()
 
# Clear the plot area
plt.clf()
 
# Create a Figure
fig = plt.figure(figsize=(12,8))
 
# Create a bar plot of product counts by category
plt.bar(x=data['OrderDate'], height=data['ProductCount'], color='orange')
 
# Customize the chart
plt.title('Product Counts by OrderDate')
plt.xlabel('OrderDate')
plt.ylabel('Products')
plt.grid(color='#95a5a6', linestyle='--', linewidth=2, axis='y', alpha=0.7)
plt.xticks(rotation=70)
 
# Show the plot area
plt.show()

Read each bar as the number of non-null sales-order-number entries for that date. A taller bar means more matching rows in this dataset, not necessarily more revenue; answering that question would require a different aggregation.

• exercise to revise all concepts on spark df and charting : link
• completed the module on to the next..

Orchestrate processes and data movement with Microsoft Fabric : link

• Data Pipelines in fabric - series of activities which copy/transform/load to onelake or other sources. we can schedule them and it is very similar to ADF in azure

• Core concepts in pipelines - activities which are kind of executable tasks and we can control the flow of pipeline from the final execution status of the activity and control the flow, in activities we have transformation activites (copy data from source , data flow to apply transformations ,we can also use notebooks to transform as we did in earlier module and provide destination to write to)

• control flow activities to implement loops , conditional branching , manage variables and parameters.

• more on the activities overview : documentation

• Pipelines can be parametrized so that we can use this as a parameter in your activites (final saved folder name can be this parameter), each pipeline run creates a Unique run ID

• Copy data - mostly used activity to get the source data ingested, copy data good to directly connect to source get raw data, but if you have multiple sources and needs transformation before storing use Data Flow - Documentation.

• there are already some pipeline templates to choose from in fabric , check and select the template that best suits your usecase to have a better starting point.

• You can see the pipeline execution history, run the pipelines.

• exercise in this module : link

• setting the http to read data from git sales csv and writing as files to lakehouse workspace. There is delete activity which we used to delete the in files/folders if any retention needs to be applied (wild card also supported).

• we also can add notebooks directly to the pipeline to trigger.

table_name = "sales_copy"
 
from pyspark.sql.functions import *
 
# Read the new sales data
df = spark.read.schema(productSchema).option("header", "true").option("mode", "FAILFAST").option("enforceSchema", "false").option("dateFormat", "yyyy-MM-dd").csv("Files/new_data/*.csv")
 
## Add month and year columns
df = df.withColumn("Year", year(col("OrderDate"))).withColumn("Month", month(col("OrderDate")))
 
# Preserve the source name: splitting on the first space is not a general name parser.
df = df.withColumn("CustomerDisplayName", trim(col("CustomerName")))
 
# Filter and reorder columns
df = df["SalesOrderNumber", "SalesOrderLineNumber", "OrderDate", "Year", "Month", "CustomerDisplayName", "EmailAddress", "Item", "Quantity", "UnitPrice", "TaxAmount"]
 
# Read-only review preview. A production loader must implement the batch contract below.
display(df.limit(10))
# Do not blindly append on every retry; validate and merge by the source line key.
 
# Just checking some syntax
display(df.select(col("OrderDate"), year(to_date(col("OrderDate"), "yyyy-MM-dd"))))
 

The pipeline example moves the work from an interactive notebook into a repeatable sequence. Its notebook derives date fields, preserves the display name and previews the transformed rows. A scheduled writer must use the idempotent staging/merge contract below rather than the original blind append. Two limits matter before scheduling it: the syntax-check now uses df, and rerunning an append can duplicate rows unless the pipeline handles repeat inputs.

Ingest Data with Dataflows Gen2 in Microsoft Fabric : link

• In this module we will see : Dataflow capabilities in Microsoft Fabric, Dataflow solutions to ingest and transform data and Include a Dataflow in a pipeline

• data flow gen 2 used for executing ETL pipelines , allows to extract from different source, apply transformations and load to destination. we can add dataflow gen2 in to data pipeline activity if we choose to, destination is not compulsory in dataflow gen2.

• low code interface, reusability and provide self-serve user access to subset of data lakehouse.

• dataflow gen2 uses power query online to visualize transformations, we can create Dataflow Gen2 in DF workload, powerbi workspace or directly in lackhouse.

• It supports wide connectors to on-prem relational dbs, excel or flat files, shareporint, fabric lakehouses. some transformations which we can apply with this are obviously filter and sorting, pivot , unpivot, merge,append,split, conditional split, replace, reorder columns, ranking, top n etc.

• A Power Query query describes transformations. Staging and a configured destination determine materialization; naming a query does not by itself create a permanent warehouse table. Referenced queries can share intermediate transformations, but inspect refresh behavior rather than assuming one execution.

• Diagram view shows the pictorial representation with diff shapes for diff functions/transformations.

• data preview pane lets you see a subset of data with transformations applied. Query Settings shows applied steps; the formula bar/Advanced Editor exposes the M expressions behind them. You can also set a data destination to lakehouse, warehouse,dql db.

• Power Query Documentation - link

• We can combine dataflow gen2 and pipelines to have additional operations on transformed data. a pipeline can orchestrate a dataflow with other steps; dataflows can also have their own supported refresh scheduling.

• Exercise - link

• Raw data using for the exercise : sales.csv

• Import the above data by selecting import Text/CSV and give URL, add custom column and give logic (in this case extracting month), add destination to your lakehouse (append or replace options)

• we can add the dataflow created by adding a dataflow activity to the pipeline and tag to the already created dataflow gen2 flow to the activity settings of dataflow. Provide the destination to the lakehouse as append type or replace to directly load to the table.

• Data factory in fabric documentation to learn more : link, finally completed this module.

Get started with data warehouses in Microsoft Fabric : link

• Official documentation on data warehouses in fabric : link

• Data warehouses are analytical stores built on a relational schema to support SQL queries, in this module we have describe what is data warehouses in fabric, data warehouse vs lakehouse, working with data warehouse, manage fact and dim tables in data warehouse. Seems to be a important module in fabric as this is where the enriched data layers are defined.

• Data warehouse in fabric is designed for more structured relational type data ingestion, dim and fact. Supports T-SQL to query and transform similar to a relational storage, it is a distinct Warehouse item with its own T-SQL write surface, not the read-only lakehouse SQL analytics endpoint.

• A business/natural key identifies an entity in the source (for example ProductCode). A surrogate key identifies a warehouse dimension row independently. In SCD2, ProductCode can have several historical rows with different surrogate keys; each fact references the row valid at its event time. The key is not an index.

• There are special type of dimension tables to provide additional context? : time dimensions provide event occurred during time periods , SCD(slowly changing dimensions) are dimension tables that track changes to dimension attributes overtime?

• star schema, most transactional dbs tables are normally normalized to reduce duplications. Star schemas denormalize dimension attributes to simplify analysis, but still join fact rows to dimensions. Declare the fact grain before choosing keys or aggregation.

• General process to implement a dw solution, ingest data to data lake, load data from these files present in data lake to staging tables, upsert to dim and fact , validate counts/keys and review supported statistics/optimization features. Fabric Warehouse manages distributed storage; do not copy SQL Server index or Synapse distribution commands blindly.

• If we just need to query data on top of data lake, we can do so with out duplicating it to data warehouse, but by directly querying from lakehouse by cross-database querying.

• we can use t-sql in query editor from the browser to run DML and DDL commands, create views, tables. Also these is a low code ETL visual query editor where we can apply transformations.

• We have datamodel view and building relations between tables. we can also create measures(similar to calculated fields in power BI - DAX) , Data Analysis Expressions (DAX) formula language.

• Semantic-model creation and table membership are version/configuration dependent; explicitly verify and maintain the reporting model, semantic model allows to query the dw content externally by BAs. Also we can create reports on query results or create new reports on dw tables directly.

• there are different roles and security while querying and storing data in fabric, we have workspace permissions and item permissions . workspace permissions provide access throughout the workspace contents. with item permissions we can provide access to individual warehouses in workspace and Read (connect to the item), ReadData (read its data through SQL) and ReadAll (read through OneLake/Apache Spark). Broad workspace roles can confer more access than narrow SQL grants; evaluate effective permissions across both surfaces

• Monitoring tools provide frameworks to identify query metrics, anomalies. also Kill the queries if the query is consuming lot of resources.

  • sys.dm_exec_connections: Returns information about each connection established between the warehouse and the engine.
  • sys.dm_exec_sessions: Returns information about each session authenticated between the item and engine.
  • sys.dm_exec_requests: Returns information about each active request in a session.

• Data warehouse exercise : Link

Load data into a Microsoft Fabric data warehouse : link

• In this modules, strategies to load data into fabric, data pipelines, loading data using T-SQL, load and transform using dataflow gen2.

• Microsoft Fabric is centered around a single data lake. data in fabric is stored in parquet files in data lake and between different services the data can be shared without copying for etl/analysis.

• data load strategies in to dw in fabric, staging your data , staging need not be loading data into the internal storage but could be external as well. It serves to optimize resources and avoid throttling. Full load/incremental loading mechanisms. More on incremental loading : link

• dimension tables - attributes with details or characteristics. the changes of these dimension tables can be characterized to SCD - slowly changing dimensions with below defs(picked as is from module)

  • Type 0 SCD: The dimension attributes never change.
  • Type 1 SCD: Overwrites existing data, doesn't keep history.
  • Type 2 SCD: Adds new records for changes, keeps full history for a given natural key.
  • Type 3 SCD: History is added as a new column.
  • Type 4 (Kimball convention): split selected rapidly changing or frequently used attributes into a mini-dimension. A fact captures keys for both the base entity dimension and the profile mini-dimension.
  • Type 5: add a current-profile reference from the base dimension to that mini-dimension and overwrite this reference with Type 1 semantics. Facts retain their historical profile key while analysts can also join through the base dimension to the current profile.
  • Type 6: keep Type 2 historical rows and add current-value Type 1 columns to them. When the current attribute changes, update that current-value column across all versions for the durable entity key; the historical column on each version remains unchanged. This supports both “as was” and “as is” grouping.

Solved profile question: customer C had segment Bronze for a January sale and becomes Gold in March. Type 4 keeps the January fact’s Bronze profile key. Type 5 additionally points C’s base row at the current Gold profile. Type 6 can keep Bronze in the January version’s historical segment column while its current-segment column, and that of all C’s versions, becomes Gold. State the chosen convention rather than treating the original “Type 6 = Type 2 + Type 3” shorthand as a loader specification.

Primary definitions: Kimball Type 4, Type 5, Type 6, read 7 October 2026.

For a Type 2 product-name change, close the current interval and insert its successor in one target-supported transaction. Use a half-open validity interval [ValidFrom, ValidTo) and an explicit effective timestamp. The algorithm below is a design contract, not executed Fabric transaction SQL.

SCD2 transaction (engine-specific SQL implementation required):
1. Lock/serialize the natural key's current version using the target engine's supported strategy.
2. If no current version: insert the initial version.
3. If attributes are unchanged: do nothing (retry is idempotent).
4. If attributes changed: expire the current row at effective_at AND insert the new row
   with ValidFrom=effective_at, ValidTo=infinity, IsActive=true.
5. Commit both changes atomically; facts refer to the applicable surrogate key.

The old IF/ELSE expired existing rows without inserting successors. This corrected algorithm covers both actions and stable date-column semantics; it is deliberately not represented as executed Fabric transaction SQL. Test unchanged/replayed input, two simultaneous changes and late-arriving facts against the actual warehouse concurrency contract.

• first we normally load the dim tables so if fact references the dim it will be already present. data pipelines to load data in warehouse, All data in a Warehouse is automatically stored in the Delta Parquet format in OneLake.
• ingesting using data pipelines - link The load below failed because TaxAmount arrived as text while the warehouse column expected REAL. My note from this run was to recreate the copy stage after encountering the schema mismatch. The error is a useful boundary between staging and curated data: landing a raw value as text can preserve it for inspection, but the analytical layer still needs validated numeric conversion before amounts can be used reliably.

ErrorCode=DWCopyCommandOperationFailed: Column 'TaxAmount' of type REAL is not compatible with Parquet physical type BYTE_ARRAY, logical type UTF8. Inspect staging schema and perform a validated decimal conversion before the curated load. [Historical workspace-specific URI omitted.]

• we can also load data using t-sql, copy command loads data to the table from sources,we can provide arguments to store rejected rows, skip header etc while copying(The COPY statement currently supports the PARQUET and CSV file formats.)

• we can also load data using data flow gen2, keeping gifs from module here for reference.

• exercise for loading data in to dw using t-sql : link

Secure a Microsoft Fabric data warehouse

Warehouse security is a set of intersecting surfaces, not one “share” switch. Begin with an access matrix: the ingestion identity needs narrowly scoped write access to staging, the transformer needs its curated targets, and the reporting identity needs only the approved analytical objects. A workspace Admin/Member/Contributor can have broader rights than a report reader; test with a genuinely low-privilege principal instead of your own owner account.

1. Workspace and item access: establish who can discover/connect to the item and which broad data permissions it inherits. Do not grant ReadAll merely to make a SQL report work: OneLake access is another route to the files.
2. Object and column permissions: grant SELECT on approved views/columns where appropriate, rather than every table. A view can omit email or an internal identifier; a client hiding the column is not access control.
3. Row-level security: a database security policy filters rows using a predicate tied to the authenticated identity and a trusted entitlement mapping. A user-supplied TenantId filter alone is not RLS. Evaluate authorized and unauthorized tenants through the exact SQL/semantic-model path your report uses.
4. Dynamic data masking: changes query presentation for principals without unmask privilege; the stored data is unchanged. Masking is not encryption and is not a complete defense against inference by a principal allowed arbitrary queries. Combine permissions with carefully selected query surfaces.
5. Audit and negative tests: record the effective principal, surface, grant scope, query and expected denial. Broad OneLake file access can bypass assumptions made from SQL-only tests; verify both or deliberately deny the unused path.

Solved access exercise. Analyst A should see tenant A revenue but not raw customer email or tenant B. Give A only the needed item/connect and curated SQL/report permissions, apply tenant filtering at the trusted data boundary, omit/restrict email and test as A. If A also has a workspace role/ReadAll that permits reading raw files, the design is not complete; remove that broad route or apply an appropriate supported OneLake policy. A masked email in one screenshot proves none of these negative cases.

Sales-batch and history exercise

Batch B has two order lines: (O1, 1, quantity=2, unit=10.00) and (O1, 2, quantity=1, unit=5.00). Its pretax revenue is 25.00, not 15.00 (sum of unit prices). The accepted grain is (source, SalesOrderNumber, SalesOrderLineNumber) with a stable batch identity. Retry B: counts and revenue must stay 2 and 25.00. A distinct order O2 with the same prices is a legitimate additional sale, not a value-level duplicate to drop.

Product P changes name from Old to New at day 5. Version keys 101 and 102 cover [day1, day5) and [day5, infinity) respectively. A day4 fact keeps 101; a day5 fact receives 102. Replaying the same change inserts nothing. A late day3 change needs interval reconstruction, not blindly expiring the current row. Conflicting changes at the same effective time need a declared source ordering or rejection policy.

Pipeline acceptance: validate header/order and numeric/date parsing, reject missing business keys or invalid quantities, reconcile accepted/rejected counts and exact-decimal totals, then commit batch completion only after the target write succeeds. FAILFAST catches parsing failures, but domain checks and duplicate-key checks are still required. Dataflow preview, Spark job success and SQL endpoint visibility each test a different stage.

Corrected contracts and failure analysis

The lakehouse SQL analytics endpoint is a read-only T-SQL surface over Delta tables; it is not the same write surface as a Fabric Warehouse. Use Spark or another supported lakehouse write path for data changes. Tables registered in the lakehouse and arbitrary files under Files have different discovery/query behavior.

Give ingestion a declared schema and row-level validation; permissive parsing can silently create nulls. Partition pruning depends on filtering partition columns; reading a single leaf directory can hide partition discovery unless the base path is supplied. Do not infer that a partition column vanished from the dataset merely because a leaf read omitted it. Appending a retried sales batch doubles totals unless batch keys or merge logic enforce idempotency.

Distinguish notebook success, pipeline success and data-quality success. A successful job can write the wrong row count. Keep input hash, batch ID, accepted/rejected counts and output totals, then validate the warehouse/lakehouse consuming surface independently. Trial capacity and portal steps are historical, not current pricing promises.

Boundary exercise with solution

A daily batch with 100 sales is appended twice. How do you repair the pipeline without hiding legitimate duplicate purchases?

Source-backed review notes

• What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn — accessed 2026-10-07. Exact supporting passage: “The SQL analytics endpoint gives you a read-only T-SQL query surface over the Delta tables in your lakehouse. Every lakehouse automatically provisions a SQL analytics endpoint when created — there's nothing extra to set up. Behind the scenes, the SQL analytics endpoint runs on the same engine as the Fabric Data Warehouse, so you get high-performance, low-latency SQL queries without managing infrastructure.”

Version-specific reference contracts

Documentation snapshots accessed 7 October 2026; mutable service/browser documentation is timestamped evidence, not an immutable product-version pin. Exact supporting passages and hashes accompany the review ledger.

• Runtime 1.3 in Fabric - Microsoft Fabric | Microsoft Learn — The historical Runtime 1.3 baseline is Spark 3.5/Delta 3.2/Python 3.11 and now EOSA, not a recommended new deployment pin.
• Power BI Semantic Models - Microsoft Fabric | Microsoft Learn — New lakehouses/warehouses no longer automatically create default semantic models.
• Unify Data Sources With OneLake Shortcuts - Microsoft Fabric | Microsoft Learn — Shortcut authorization depends on target, connection and delegated engine identity.
• Native execution engine for Fabric Data Engineering - Microsoft Fabric | Microsoft Learn — Current native-engine setup uses the environment Acceleration control, not a durable magic-command recipe.
• CSV Files - Spark 3.5.6 Documentation — CSV enforceSchema=false checks header positions; FAILFAST is not domain validation.
• Modeling Dimension Tables in Warehouse - Microsoft Fabric | Microsoft Learn — A surrogate key identifies a dimension row, not an index.
• Secure Your Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn — Dynamic masking changes query results, not stored data or all inference risk.
• High concurrency mode in Apache Spark compute for Fabric - Microsoft Fabric | Microsoft Learn — High concurrency has notebook/session isolation constraints, not a blanket two-user promise.

Review and execution boundary

Microsoft Fabric lakehouse/SQL analytics endpoint and Spark notebook concepts; archived challenge progress is not current credential or platform verification.

Reviewed on 7 October 2026 against the official source snapshots linked below. The complete current and original lesson were reviewed. Official Fabric snapshots support conceptual guidance; the sales/history fixture checks a local model, not Fabric transactions, capacity, IAM or cloud execution. Historical setup commands and optional exercises were not executed. No cloud resources, third-party packages or external side effects were created. Old screenshots and unavailable private assets remain in the private recovery archive, not prerequisites for this lesson.