Microsoft Fabric Project Osmos Tutorial: Build and Validate a Lakehouse

1. What is the Fabric data engineering agent (Project Osmos)?

Project Osmos is an outcome-driven data engineering agent for Microsoft Fabric. With a normal coding assistant you ask for a notebook snippet and paste it somewhere. Here you describe the result you need and point the agent at the lakehouse that will host the work. It can inspect authorized data, create and run Spark notebooks, transform data, write Delta tables, and test the results against criteria you set.

The key difference is that the task runs inside Fabric. It keeps its own state and carries on after your local CLI disconnects. You start and steer it from your coding client, then open the task in the Fabric lakehouse experience to see its status, specification, activity, errors, and outputs.

What it is good at

• Exploring an unfamiliar lakehouse and working out which tables matter.
• Building or modernizing a bronze, silver, and gold transformation flow.
• Producing Spark notebooks and Delta tables as part of a single task.
• Testing data-quality rules: duplicate keys, broken relationships, invalid values, reconciliation totals.
• Comparing implementation options and documenting the result before you promote anything.

What stays your job

Business definitions, source-of-truth decisions, authorization, retention, cost review, and acceptance testing are still yours. A task that finishes without an error is not automatically a correct data product. The most useful prompts turn “correct” into something measurable and make the agent show evidence for each check.

2. Project Osmos vs Microsoft Fabric Data Agent

The names are close but the products solve different problems. This split also keeps this tutorial separate from our existing Microsoft Fabric Data Agent guide.

For the wider design thinking behind agents, contracts, metadata, and pipeline recovery, read our agentic data engineering tutorial. That article covers the pattern. This one covers Microsoft’s specific Project Osmos implementation.

3. Prerequisites: check these before installing

Most first-run failures come from environment or permission problems, not from the prompt. Run through this list first.

• Fabric workspace: It must sit on an eligible paid Fabric capacity. Trial SKUs are not supported for this workflow.
• Lakehouse: Create or identify a lakehouse in the target workspace.
• Workspace role: Your account needs Contributor or higher on the workspace, plus access to the intended source and destination data.
• Tenant settings: A Fabric administrator must enable “Users can use Copilot and other features powered by Azure OpenAI.” Microsoft also strongly recommends enabling “Users can use Copilot, AI Agents, and other AI experiences powered by OpenAI as a Microsoft Subprocessor,” though it is documented as not required.
• Regional settings: If your capacity is in a region where OpenAI is not available, extra tenant settings decide whether data may be processed or stored outside the capacity’s geography, compliance boundary, or national cloud. Get your administrator to check these against your data-residency rules.
• Capacity setting: Enabling AI data transformation in capacity settings is recommended by Microsoft but not required.
• Local tools: Azure CLI and one supported client: GitHub Copilot CLI, Claude Code, or Codex. Microsoft lists Git as a prerequisite for Codex.

4. Install Project Osmos using the current Skills for Fabric collection

Installation update: Microsoft’s current Project Osmos repository says the project-osmos skill is now part of the fabric-skills@fabric-collection bundle in the Skills for Fabric repository. For a new setup, install that collection instead of the older standalone Project Osmos plugin. If you already have a working install, look at what is there before adding another bundle so you don’t end up with duplicates.

Step 1: Sign in to the correct Azure tenant

Install Azure CLI if you don’t have it, then sign in from your terminal. Use the tenant that holds your Fabric workspace. If you belong to several tenants, pick the right one explicitly.

az login --allow-no-subscriptions

For a guest or cross-tenant workspace, the documented pattern is to name the resource tenant:

az login --tenant <RESOURCE_TENANT_ID> --allow-no-subscriptions

Never paste access tokens or credentials into the Project Outcome prompt. Authenticate through the supported CLI flow only.

Step 2: Install the Fabric collection

Pick the command group for your client. These follow Microsoft’s public repository guidance at the time of review. CLI syntax moves around during preview, so if a command differs in your installed version, trust the official install page.

copilot plugin marketplace add microsoft/skills-for-fabric
copilot plugin install fabric-skills@fabric-collection

claude plugin marketplace add microsoft/skills-for-fabric
claude plugin install fabric-skills@fabric-collection

codex plugin marketplace add microsoft/skills-for-fabric
codex plugin add fabric-skills@fabric-collection

Step 3: Start with a read-only discovery task

Before the agent writes a single table, check that it can find your lakehouse and describe what is in it. Give your client the lakehouse URL and ask for read-only discovery:

Tell me the names of the tables and files in this Fabric Lakehouse:
<PASTE_LAKEHOUSE_URL>

Do not create, update, delete, or overwrite any data or artifacts.
Return the discovered object names and any access errors only.

This only proves the agent can see the lakehouse. It doesn’t guarantee that later writes will work. If it can’t identify the workspace or lakehouse, fix the tenant, URL, capacity, or permissions before going further.

5. Hands-on lab: build a retail medallion lakehouse

The lab is small on purpose, but it includes the controls a production-minded engineer expects: source preservation, deterministic deduplication, quarantine handling, idempotent reruns, and measurable validation. Use synthetic data in a separate lab workspace for your first run.

Target architecture

Step 1: Define the sample data contract

For a repeatable lab, use these source shapes, or adapt the names to data you own:

If these files already exist in the lakehouse, ask the agent to inspect the real schemas and sample values before it writes any code. Don’t let it guess column names and hope they match.

Step 2: Submit one complete Project Outcome

Swap RetailLab and RetailEngineeringLab for your own lakehouse and workspace names, then paste the whole request into your client once the skill is installed and verified. Treat it as a reusable template. It is not a record of a task that has already run in your tenant.

Use Project Osmos in the Fabric workspace "RetailEngineeringLab"
and lakehouse "RetailLab".

Goal:
Build a small, auditable retail medallion pipeline from the source data
in this lakehouse.

First inspect:
List the available files, tables, schemas and representative values.
If customers.csv, products.csv and sales.csv already exist, use their real
schemas. If the files do not exist, create clearly labelled synthetic test
CSV files under Files/landing and report that they are synthetic.
Do not modify or delete source files or existing source tables.

Sources:
- customers.csv: customer_id, country, created_at
- products.csv: product_id, category
- sales.csv: order_line_id, order_id, customer_id, product_id, order_date,
  quantity, unit_price, modified_at

Transformations:
1. Preserve source values in Bronze Delta tables and include useful ingest
   metadata where appropriate.
2. In Silver, apply explicit data types, normalize dates, trim and validate
   business keys, and handle duplicate records deterministically using
   modified_at where the source supports it.
3. Quarantine malformed rows and rows with missing required identifiers.
   Do not silently drop rejected records; report the rejection reason and count.
4. Validate customer and product references before creating the curated sales
   output. Do not invent relationships that are absent from the source.
5. Create Gold tables named gold_daily_sales and gold_customer_sales. Define
   line revenue explicitly as quantity multiplied by unit_price and document
   whether any returns, discounts, taxes or currency conversions are present
   in the source. Do not assume those fields exist if they do not.

Required artifacts:
- 01_load_bronze notebook
- 02_transform_silver notebook
- 03_build_gold notebook
- 04_validate_retail notebook
- Bronze, Silver, Gold and quarantine tables with clear names
- A short run summary listing assumptions, changed/created artifacts and
  validation results

Safety and reruns:
Treat source files and existing source tables as read-only. Write to separate
staging targets first. Do not overwrite, delete, rename or promote over any
existing target without explaining the change and obtaining approval. Make
reruns idempotent: rerunning the same source should not create duplicate
business keys or duplicate revenue. Keep unexpected schema changes locked
unless a compatible change is explicitly justified.

Validation:
- Report source, accepted, rejected and output row counts.
- Check uniqueness of customer_id, product_id and order_line_id at the
  appropriate layer.
- Check for orphaned sales rows with missing customer or product references.
- Check for invalid dates, null required keys, negative quantities and invalid
  unit prices; explain the handling rule for each case.
- Reconcile accepted sales revenue from Silver to Gold before rounding.
- Report quarantined row counts and reasons.
- Run the notebooks and tests in the target Fabric environment. If any critical
  check fails, leave the outputs staged, do not promote them, and explain the
  failure and recommended correction.

At the end, provide the notebook and table names, row-count reconciliation,
validation pass/fail status, unresolved assumptions, and a safe next step.
Do not claim a test passed unless it was executed and its result was observed.

Step 3: Review the intake and execution plan

Before you approve the run, read the task settings Project Osmos proposes. The interface may change during preview, but Microsoft documents settings for permission boundaries, write safety, promotion, rerun behaviour, schema evolution, artifact type and destination, and reasoning effort.

1. Confirm it found the right workspace and lakehouse.
2. Check that source objects are read-only and output targets are named explicitly.
3. For the first lab, pick staging or clone-and-promote rather than iterating directly on a target.
4. Confirm the notebook and table names, and read any assumptions it lists.
5. Approve only when you understand the expected impact and the write path is safe.

Step 4: Let the task run, then inspect the artifacts

Once you approve, Project Osmos runs the work in Fabric and gives you a task ID or link. Open the lakehouse in Fabric and find the task entry. Look at the specification, status, activity, errors, notebooks, tables, and validation evidence. You can also ask your client to report task status or send follow-up instructions.

A green status is not proof of success. Open the notebooks, look at real rows, and check the output totals yourself. If the task keeps running after you close the terminal, that is normal, because the managed task lives in Fabric.

6. Task settings worth reviewing

Microsoft’s best-practices page recommends complete outcomes, clear read and write boundaries, predictable reruns, staged writes, and measurable success evidence. It also covers cases like incremental loads and data-quality remediation: Project Osmos best practices.

7. Validate the outputs like a data engineer

Validation is what separates AI-generated code from a workflow you can trust. Build the evidence checklist into the outcome, then read the executed notebook results yourself.

Validation checklist

• Schema: Expected data types, required columns, and schema compatibility.
• Uniqueness: One accepted customer per customer_id, one product per product_id, no duplicated accepted order_line_id.
• Referential integrity: Sales rows with no valid customer or product match are reported or quarantined as specified.
• Completeness: Source rows reconcile to accepted rows plus rejected rows, after any documented filters.
• Financial arithmetic: Silver line revenue equals the Gold aggregation, same formula, before rounding.
• Rerun behaviour: Replay the same input in the lab and confirm duplicate keys and totals don’t grow.
• Traceability: The run summary lists assumptions, rejected records, created artifacts, open issues, and the real pass or fail status.

Example Spark SQL checks

These show the kind of checks to request in the validation notebook. They assume the tables and columns were created as specified in the lab, and they are examples rather than results from a live run.

1. Duplicate sales line keys

SELECT
    order_line_id,
    COUNT(*) AS row_count
FROM silver_sales
GROUP BY order_line_id
HAVING COUNT(*) > 1;

2. Orphaned customer references

SELECT COUNT(*) AS orphaned_sales
FROM silver_sales AS s
LEFT ANTI JOIN silver_customers AS c
  ON s.customer_id = c.customer_id;

3. Line revenue against the Gold daily summary

SELECT SUM(quantity * unit_price) AS silver_revenue
FROM silver_sales;

SELECT SUM(gross_revenue) AS gold_revenue
FROM gold_daily_sales;

Only use gross_revenue if that is the column name the agent actually created. If it picked another documented name, change the query. And if Silver and Gold exclude different records by design, reconcile like for like on accepted records instead of forcing the totals to match.

Use a validation matrix, not a “looks fine” review

8. Reading the generated notebooks: what I look for first

Passing tests is half the review. The other half is reading the code the agent wrote, because generated Spark code can pass a small lab dataset and still fail on real volumes. These are the things worth scanning for before you promote anything. None of them is specific to Project Osmos; they apply to any generated pipeline code.

A merge pattern to ask for on reruns

If the agent appends on every run, ask it to switch to a keyed merge. This is the shape to look for. Adjust table and column names to what your task created.

MERGE INTO silver_sales AS t
USING staging_sales AS s
  ON t.order_line_id = s.order_line_id
WHEN MATCHED AND s.modified_at > t.modified_at THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *;

The modified_at condition matters. Without it, an older copy of a row can overwrite a newer one when files arrive out of order.

A quick sanity routine before you promote

1. Open each notebook top to bottom and read the first cell. Parameters and paths tell you a lot.
2. Search each notebook for the red flags in the table above.
3. Pick five source rows by hand and follow them through Bronze, Silver, and Gold, including at least one that should have been quarantined.
4. Run the task a second time on the same input and compare row counts and revenue totals.
5. Only then decide whether the output is good enough to promote.

9. When to use it, and when to skip it

Preview agents are easy to over-apply. This is how I would sort the work today.

A good rule of thumb: if you could not explain to an auditor how the output was produced and checked, don’t promote it yet. The agent speeds up the build. It doesn’t take over accountability for the result.

10. Troubleshooting Project Osmos

11. Limitations, security and cost

Limitations to check before you plan a project

• Preview status: The Microsoft Learn pages reviewed do not describe Project Osmos as generally available. Capabilities and commands can change, regional availability can be limited, and there is no production SLA.
• Outbound access protection: The current getting-started documentation says workspaces with it enabled are not supported.
• Client workflow: You start and steer Project Osmos from a supported local agent such as GitHub Copilot CLI, Claude Code, or Codex. The overview documentation says direct interaction from Copilot in the Fabric portal is not currently supported.
• Capacity: Trial SKUs are not supported. Check your workspace’s paid capacity and capacity-level configuration before you start.
• Governance: Review regional processing and subprocessor settings before sending organizational data to AI-backed features.

Security rules worth following

• Use a dedicated lab workspace and synthetic data for the first run.
• Treat Fabric and OneLake permissions as the real access boundary. The permission boundary in a task is a scope instruction, not access control.
• Keep tokens, passwords, connection strings, and unnecessary sensitive data out of prompts.
• Keep source tables read-only until output tests pass and promotion is approved.
• Record the task ID, generated artifacts, validation evidence, and a rollback plan for any change to an existing workflow.

How much does Project Osmos cost?

The reviewed documentation does not describe a separate per-task price. It requires an eligible Fabric capacity, so the real cost depends on your capacity, your workload, and your tenant configuration. Check current Fabric capacity pricing and your own capacity-usage monitoring before running larger jobs. We don’t quote a dollar figure here because no universal per-task price is established in the documentation.

For capacity planning in general, see our Microsoft Fabric capacity optimization guide and confirm current billing details in Microsoft’s official pricing documentation.

12. Project Osmos FAQ

Official documentation and further reading

Project Osmos keeps changing during preview, so treat these primary sources as the final word on install syntax, prerequisites, and current limits.

• Microsoft Learn: What is Fabric data engineering agent (Project Osmos)? Overview, workflow, capabilities, and how it differs from other Fabric experiences.
• Microsoft Learn: Get started with Project Osmos Prerequisites, first task, settings, monitoring, and current limitations.
• Microsoft Learn: Project Osmos best practices Complete outcomes, safe writes, reruns, and validation.
• Microsoft: Skills for Fabric repository Current collection and installation details.
• Microsoft: Project Osmos repository Distribution notes and supported client commands.
• Microsoft Learn: Enable and configure Copilot in Fabric Tenant and capacity configuration.
• Microsoft Learn: Install Azure CLI Installation and sign-in prerequisites.

Related Ultimate Info Guide articles: Microsoft Fabric Data Agent, Agentic Data Engineering Tutorial, Fabric Lakehouse Tutorial, Fabric Capacity Optimization, and Editorial Policy and Standards.