Build Your Own Spark Job Doctor in Microsoft Fabric

Microsoft Fabric makes it incredibly easy to spin up Spark workloads: notebooks, Lakehouse pipelines, dataflows, SQL + Spark hybrid architectures—the whole buffet.

What’s still hard?
Knowing why a given Spark job is slow, expensive, or flaky.

  • A Lakehouse pipeline starts timing out.
  • A notebook that used to finish in 5 minutes is now taking 25.
  • Costs spike because one model training job is shuffling half the lake.

You open the Spark UI, click around a few stages, stare at shuffle graphs, and say the traditional words of Spark debugging:

“Huh.”

This is where an AI assistant should exist.

In this post, we’ll walk through how to build exactly that for Fabric Spark: a Job Doctor that:

  • Reads Spark telemetry from your Fabric environment
  • Detects issues like skew, large shuffles, spill, and bad configuration
  • Uses a large language model (LLM) to explain what went wrong
  • Produces copy-pasteable fixes in Fabric notebooks / pipelines
  • Runs inside Fabric using Lakehouses, notebooks, and Azure AI models

This is not a fake product announcement. This is a blueprint you can actually build.


What Is the Fabric “Job Doctor”?

At a high level, the Job Doctor is:

A Fabric-native analytics + AI layer that continuously reads Spark job history, detects common performance anti-patterns, and generates human-readable, prescriptive recommendations.

Concretely, it does three main things:

  1. Collects Spark job telemetry from Fabric
    • Spark application metrics (tasks, stages, shuffles, spills)
    • Spark logs & events (Driver/Executor/Event logs)
    • Optional query plans
    • Spark session configs
  2. Analyzes jobs using rules + metrics
    • Identifies skew, large shuffles, spill, etc.
    • Scores each job run and surfaces the top issues.
  3. Uses an LLM to generate a “diagnosis sheet”
    • Root cause in plain English
    • Fixes with code + config snippets for Fabric Spark
    • Expected impact on performance/cost

Let’s build it step by step, Fabric-style.


Part 1: Getting Spark Telemetry Out of Fabric

Before you can diagnose anything, you need the raw signals. In Fabric, there are three main ways to see what Spark is doing:

  1. Fabric Apache Spark diagnostic emitter → logs/metrics for each application
  2. Spark application details (UI / REST)
  3. In-job logging from notebooks/pipelines (e.g., configs, query plans)

You don’t have to use all three, but combining them gives you enough for a very capable Job Doctor.


1. Configure the Fabric Apache Spark Diagnostic Emitter

The core telemetry pipeline starts with the Fabric Apache Spark diagnostic emitter, configured on a Fabric environment.

At a high level, you:

  1. Create or use an environment for your Spark workloads.
  2. Configure one or more diagnostic emitters on that environment.
  3. Point each emitter to a sink such as:
    • Azure Storage (Blob, ADLS)
    • Azure Log Analytics
    • Azure Event Hubs

For example, an emitter to Azure Storage might be configured (conceptually) like this:

spark.synapse.diagnostic.emitters: MyStorageEmitter
spark.synapse.diagnostic.emitter.MyStorageEmitter.type: AzureStorage
spark.synapse.diagnostic.emitter.MyStorageEmitter.categories: DriverLog,ExecutorLog,EventLog,Metrics
spark.synapse.diagnostic.emitter.MyStorageEmitter.uri: https://<account>.blob.core.windows.net/<container>/<folder>
spark.synapse.diagnostic.emitter.MyStorageEmitter.auth: AccessKey
spark.synapse.diagnostic.emitter.MyStorageEmitter.secret: <storage-access-key>

Once this is in place:

  • Every Spark application (notebook, job, pipeline activity that spins up Spark) will emit diagnostic records.
  • Those records land as JSON lines describing driver logs, executor logs, Spark listener events, and metrics.

From there, you can:

  • If using Storage: Create a shortcut in a Lakehouse pointing at the container/folder.
  • If using Log Analytics: Build KQL queries or export into Fabric (e.g., into a KQL DB or as files you later hydrate into a Lakehouse).

We’ll assume the storage pattern for the rest of this post:

Spark app → Fabric environment with diagnostic emitter → Azure Storage → OneLake shortcut → Lakehouse.


2. Shape of the Raw Logs (and Why You’ll Normalize Them)

The emitter doesn’t give you a nice stageId / taskId table out of the box. Instead, you’ll see records like:

{
  "timestamp": "2024-05-01T12:34:56Z",
  "category": "Metrics",
  "applicationId": "app-20240501123456-0001",
  "properties": {
    "metricName": "executorRunTime",
    "stageId": 4,
    "taskId": 123,
    "value": 9182,
    "otherFields": "..."
  }
}

Or an EventLog record with a payload that looks like the Spark listener event.

To build a Job Doctor, you’ll:

  1. Read the JSON lines into Fabric Spark
  2. Explode / parse the properties payload
  3. Aggregate per-task metrics into per-stage metrics for each application

We’ll skip the exact parsing details (they depend on how you set up the emitter and which events/metrics you enable) and assume that after a normalization job, you have a table with one row per (applicationId, stageId, taskId).

That’s what the next sections use.


3. Capturing Query Plans in Fabric (Optional, but Powerful)

Spark query plans are gold when you’re trying to answer why a stage created a huge shuffle or why a broadcast join didn’t happen.

There isn’t yet a first-class “export query plan as JSON” API in PySpark, but in Fabric notebooks you can use a (semi-internal) trick that works today:

import json

df = ...  # some DataFrame you care about

# Advanced / internal: works today but isn't a public, stable API
plan_json = json.loads(df._jdf.queryExecution().toJSON())

You can also log the human-readable plan:

df.explain(mode="formatted")  # documented mode, prints a detailed plan

To persist the JSON plan for the Job Doctor, tie it to the Spark application ID:

from pyspark.sql import Row

app_id = spark.sparkContext.applicationId

spark.createDataFrame(
    [Row(applicationId=app_id, query_plan_json=plan_json)]
).write.mode("append").saveAsTable("job_doctor.query_plans")

A couple of caveats you should mention in a real blog:

  • _jdf.queryExecution().toJSON() is not guaranteed to be stable across Spark versions. It’s an advanced, “use at your own risk” trick.
  • You don’t need to capture plans for every single query—just key bottleneck notebooks or critical pipelines.

Even capturing a subset massively improves the quality of LLM explanations.


4. Capture Spark Config for Each Run

Fabric Spark lets you set configs at:

  • Environment / pool level (resource profiles, environment settings)
  • Notebook / job level (spark.conf.set(...))
  • Pipeline activity level (Spark job settings)

Inside the running Spark job, you can capture the effective session config like this:

from pyspark.sql import Row

app_id = spark.sparkContext.applicationId
conf_dict = dict(spark.conf.getAll())  # session-level config

config_rows = [
    Row(applicationId=app_id, key=k, value=v)
    for k, v in conf_dict.items()
]

spark.createDataFrame(config_rows).write.mode("append").saveAsTable("job_doctor.spark_conf")

Now the Job Doctor can say things like:

  • “AQE was disabled for this job.”
  • “Shuffle partitions was left at default 200, which is low for your data size.”

You’re building a small “Job Doctor mart” inside Fabric:

  • job_doctor.raw_logs (from emitter)
  • job_doctor.stage_metrics (aggregated)
  • job_doctor.stage_issues (rule engine output)
  • job_doctor.spark_conf (per-application configs)
  • job_doctor.query_plans (optional)

All keyed by applicationId.


Part 2: Loading and Normalizing Spark Logs in a Fabric Lakehouse

Let’s assume you’ve done one-time wiring:

  • Azure Storage container with Spark diagnostics
  • OneLake shortcut from that container into a Lakehouse
  • A Fabric Spark notebook attached to that Lakehouse

From that notebook:

logs_df = spark.read.json("Tables/spark_diagnostics_raw")  # or your shortcut path
display(logs_df.limit(10))

You’ll see something like:

  • timestamp
  • category (DriverLog, ExecutorLog, EventLog, Metrics, …)
  • applicationId
  • properties (nested JSON with stage/task/metric detail)

The normalization step (which you can run as a scheduled pipeline) should:

  1. Filter down to metrics/events relevant for performance (e.g. task / stage metrics)
  2. Extract stageId, taskId, executorRunTime, shuffleReadBytes, etc., into top-level columns
  3. Persist the result as job_doctor.task_metrics (or similar)

For the rest of this post, we’ll assume you’ve already done that and have a table with columns:

  • applicationId
  • stageId
  • taskId
  • executorRunTime
  • shuffleReadBytes
  • shuffleWriteBytes
  • memoryBytesSpilled
  • diskBytesSpilled

Aggregating Stage Metrics in Fabric

Now we want to collapse per-task metrics into per-stage metrics per application.

In a Fabric notebook:

from pyspark.sql import functions as F

task_metrics = spark.table("job_doctor.task_metrics")

stage_metrics = (
    task_metrics
    .groupBy("applicationId", "stageId")
    .agg(
        F.countDistinct("taskId").alias("num_tasks"),
        F.sum("executorRunTime").alias("total_task_runtime_ms"),
        # Depending on Spark version, you may need percentile_approx instead
        F.expr("percentile(executorRunTime, 0.95)").alias("p95_task_runtime_ms"),
        F.max("executorRunTime").alias("max_task_runtime_ms"),
        F.sum("shuffleReadBytes").alias("shuffle_read_bytes"),
        F.sum("shuffleWriteBytes").alias("shuffle_write_bytes"),
        F.sum("memoryBytesSpilled").alias("memory_spill_bytes"),
        F.sum("diskBytesSpilled").alias("disk_spill_bytes"),
    )
    .withColumn(
        "skew_ratio",
        F.col("max_task_runtime_ms") /
        F.when(F.col("p95_task_runtime_ms") == 0, 1).otherwise(F.col("p95_task_runtime_ms"))
    )
    .withColumn("shuffle_read_mb", F.col("shuffle_read_bytes") / (1024**2))
    .withColumn("shuffle_write_mb", F.col("shuffle_write_bytes") / (1024**2))
    .withColumn(
        "spill_mb",
        (F.col("memory_spill_bytes") + F.col("disk_spill_bytes")) / (1024**2)
    )
)

stage_metrics.write.mode("overwrite").saveAsTable("job_doctor.stage_metrics")

This gives you a Fabric Lakehouse table with:

  • skew_ratio
  • shuffle_read_mb
  • shuffle_write_mb
  • spill_mb
  • p95_task_runtime_ms
  • num_tasks, total_task_runtime_ms, etc.

You can run this notebook:

  • On a schedule via a Data Pipeline
  • Or as a Data Engineering job configured in the workspace

Part 3: Adding a Rule Engine Inside Fabric

Now that the metrics are in a Lakehouse table, let’s add a simple rule engine in Python.

This will run in a Fabric notebook (or job) and write out issues per stage.

from pyspark.sql import Row, functions as F

stage_metrics = spark.table("job_doctor.stage_metrics")

# For simplicity, we'll collect to the driver here.
# This is fine if you don't have thousands of stages.
# For very large workloads, you'd instead do this via a UDF / mapInPandas / explode.
stage_rows = stage_metrics.collect()

Define some basic rules:

def detect_issues(stage_row):
    issues = []

    # 1. Skew detection
    if stage_row.skew_ratio and stage_row.skew_ratio > 5:
        issues.append({
            "issue_id": "SKEWED_STAGE",
            "severity": "High",
            "details": f"Skew ratio {stage_row.skew_ratio:.1f}"
        })

    # 2. Large shuffle
    total_shuffle_mb = (stage_row.shuffle_read_mb or 0) + (stage_row.shuffle_write_mb or 0)
    if total_shuffle_mb > 10_000:  # > 10 GB
        issues.append({
            "issue_id": "LARGE_SHUFFLE",
            "severity": "High",
            "details": f"Total shuffle {total_shuffle_mb:.1f} MB"
        })

    # 3. Excessive spill
    if (stage_row.spill_mb or 0) > 1_000:  # > 1 GB
        issues.append({
            "issue_id": "EXCESSIVE_SPILL",
            "severity": "Medium",
            "details": f"Spill {stage_row.spill_mb:.1f} MB"
        })

    return issues

Apply the rules and persist the output:

issue_rows = []

for r in stage_rows:
    for issue in detect_issues(r):
        issue_rows.append(Row(
            applicationId=r.applicationId,
            stageId=r.stageId,
            issue_id=issue["issue_id"],
            severity=issue["severity"],
            details=issue["details"]
        ))

issues_df = spark.createDataFrame(issue_rows)

issues_df.write.mode("overwrite").saveAsTable("job_doctor.stage_issues")

Now you have a table of Spark issues detected per run inside your Lakehouse.

Later, the LLM will use these as structured hints.


Part 4: Bringing in the LLM — Turning Metrics into Diagnosis

So far, everything has been pure Spark in Fabric.

Now we want a model (e.g., Azure AI “Models as a Service” endpoint or Azure OpenAI) to turn:

  • job_doctor.stage_metrics
  • job_doctor.stage_issues
  • job_doctor.spark_conf
  • job_doctor.query_plans

into an actual diagnosis sheet a human can act on.

In Fabric, this is simplest from a Spark notebook using a Python HTTP client.

Below, I’ll show the pattern using an Azure AI serverless model endpoint (the one that uses model: "gpt-4.1" in the body).


1. Prepare the Prompt Payload

First, fetch the data for a single Spark application:

import json
from pyspark.sql import functions as F

app_id = "app-20240501123456-0001"  # however you pick which run to diagnose

stages_df = spark.table("job_doctor.stage_metrics").where(F.col("applicationId") == app_id)
issues_df = spark.table("job_doctor.stage_issues").where(F.col("applicationId") == app_id)
conf_df   = spark.table("job_doctor.spark_conf").where(F.col("applicationId") == app_id)
plans_df  = spark.table("job_doctor.query_plans").where(F.col("applicationId") == app_id)

stages_json = stages_df.toPandas().to_dict(orient="records")
issues_json = issues_df.toPandas().to_dict(orient="records")
conf_json   = conf_df.toPandas().to_dict(orient="records")
plans_json  = plans_df.toPandas().to_dict(orient="records")  # likely 0 or 1 row

Then build a compact but informative prompt:

prompt = f"""
You are an expert in optimizing Apache Spark jobs running on Microsoft Fabric.

Here is summarized telemetry for one Spark application (applicationId={app_id}):

Stage metrics (JSON):
{json.dumps(stages_json, indent=2)}

Detected issues (JSON):
{json.dumps(issues_json, indent=2)}

Spark configuration (key/value list):
{json.dumps(conf_json, indent=2)}

Query plans (optional, may be empty):
{json.dumps(plans_json, indent=2)}

Your tasks:
1. Identify the top 3–5 performance issues for this run.
2. For each, explain the root cause in plain language.
3. Provide concrete fixes tailored for Fabric Spark, including:
   - spark.conf settings (for notebooks/jobs)
   - suggestions for pipeline settings where relevant
   - SQL/DataFrame code snippets
4. Estimate likely performance impact (e.g., "30–50% reduction in runtime").
5. Call out any risky or unsafe changes that should be tested carefully.

Return your answer as markdown.
"""


2. Call an Azure AI Model from Fabric Spark

For the serverless “Models as a Service” endpoint, the pattern looks like this:

import os
import requests

# Example: using Azure AI Models as a Service
# AZURE_AI_ENDPOINT might look like: https://models.inference.ai.azure.com
AZURE_AI_ENDPOINT = os.environ["AZURE_AI_ENDPOINT"]
AZURE_AI_KEY      = os.environ["AZURE_AI_KEY"]

MODEL = "gpt-4.1"  # or whatever model you've enabled

headers = {
    "Content-Type": "application/json",
    "api-key": AZURE_AI_KEY,
}

body = {
    "model": MODEL,
    "messages": [
        {"role": "system", "content": "You are a helpful assistant for optimizing Spark jobs on Microsoft Fabric."},
        {"role": "user", "content": prompt},
    ],
}

resp = requests.post(
    f"{AZURE_AI_ENDPOINT}/openai/chat/completions",
    headers=headers,
    json=body,
)

resp.raise_for_status()
diagnosis = resp.json()["choices"][0]["message"]["content"]

If you instead use a provisioned Azure OpenAI resource, the URL shape is slightly different (you call /openai/deployments/<deploymentName>/chat/completions and omit the model field), but the rest of the logic is identical.

At this point, diagnosis is markdown you can:

  • Render inline in the notebook with displayHTML
  • Save into a Lakehouse table
  • Feed into a Fabric semantic model for reporting

Part 5: What the Job Doctor’s Output Looks Like in Fabric

A good Job Doctor output for Fabric Spark might look like this (simplified):


🔎 Issue 1: Skewed Stage 4 (skew ratio 12.3)

What I see

  • Stage 4 has a skew ratio of 12.3 (max task runtime vs. p95).
  • This stage also reads ~18.2 GB via shuffle, which amplifies the imbalance.

Likely root cause

A join or aggregation keyed on a column where a few values dominate (e.g. a “default” ID, nulls, or a small set of hot keys). One partition ends up doing far more work than the others.

Fabric-specific fixes

In your notebook or job settings, enable Adaptive Query Execution and skew join handling:

spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")

If the query is in SQL (Lakehouse SQL endpoint), enable AQE at the session/job level through Spark configuration.

If one side of the join is a small dimension table, add a broadcast hint:

SELECT /*+ BROADCAST(dim) */ f.*
FROM fact f
JOIN dim
  ON f.key = dim.key;

Estimated impact:
30–50% reduction in total job runtime, depending on how skewed the key distribution is.


📦 Issue 2: Large Shuffle in Stage 2 (~19.7 GB)

What I see

  • Stage 2 reads ~19.7 GB via shuffle.
  • Shuffle partitions are set to 200 (Spark default).

Likely root cause

A join or aggregation is shuffling nearly the full dataset, but parallelism is low given the data volume. That leads to heavy tasks and increased risk of spill.

Fabric-specific fixes

Increase shuffle partitions for this job:

spark.conf.set("spark.sql.shuffle.partitions", "400")

For pipelines, set this at the Spark activity level under Spark configuration, or through your Fabric environment’s resource profile if you want a new default.

Also consider partitioning by the join key earlier in the pipeline:

df = df.repartition("customer_id")

Estimated impact:
More stable runtimes and reduced likelihood of spill; wall-clock improvements if your underlying capacity has enough cores.


💾 Issue 3: Spill to Disk (~1.8 GB) in Stage 3

What I see

  • Stage 3 spills ~1.8 GB to disk.
  • This correlates with under-parallelism or memory pressure.

Fabric-specific fixes

  • Adjust cluster sizing via Fabric capacity / resource profiles (enough cores + memory per core).
  • Increase spark.sql.shuffle.partitions as above.
  • Avoid wide transformations producing huge intermediate rows early in the job; materialize smaller, more selective intermediates first.

You can persist the diagnosis text into a table:

from pyspark.sql import Row

spark.createDataFrame(
    [Row(applicationId=app_id, diagnosis_markdown=diagnosis)]
).write.mode("append").saveAsTable("job_doctor.diagnoses")

Then you can build a Power BI report in Fabric bound to:

  • job_doctor.diagnoses
  • job_doctor.stage_metrics
  • job_doctor.stage_issues

to create a “Spark Job Health” dashboard where:

  • Rows = recent Spark runs
  • Columns = severity, duration, shuffle size, spill, etc.
  • A click opens the AI-generated diagnosis for that run

All inside the same workspace.


Part 6: Stitching It All Together in Fabric

Let’s recap the full Fabric-native architecture.

1. Telemetry Ingestion (Environment / Emitter)

  • Configure a Fabric environment for your Spark workloads.
  • Add a Fabric Apache Spark diagnostic emitter to send logs/metrics to:
    • Azure Storage (for Lakehouse shortcuts), or
    • Log Analytics / Event Hubs if you prefer KQL or streaming paths.
  • (Optional) From notebooks/pipelines, capture:
    • Spark configs → job_doctor.spark_conf
    • Query plans → job_doctor.query_plans

2. Normalization Job (Spark / Data Pipeline)

  • Read raw diagnostics from Storage via a Lakehouse shortcut.
  • Parse and flatten the records into per-task metrics.
  • Aggregate per-stage metrics → job_doctor.stage_metrics.
  • Evaluate rule engine → job_doctor.stage_issues.
  • Persist all of this into Lakehouse tables.

3. AI Diagnosis Job (Spark + Azure AI Models)

  • For each new (or most expensive / slowest) application:
    • Pull stage metrics, issues, configs, and query plans from Lakehouse.
    • Construct a structured prompt.
    • Call your Azure AI / Azure OpenAI endpoint from a Fabric Spark notebook.
    • Store the markdown diagnosis in job_doctor.diagnoses.

4. User Experience

  • Fabric Notebook
    • A “Run Job Doctor” cell or button that takes applicationId, calls the model, and displays the markdown inline.
  • Data Pipeline / Job
    • Scheduled daily to scan all runs from yesterday and generate diagnoses automatically.
  • Power BI Report in Fabric
    • “Spark Job Health” dashboard showing:
      • Top slowest/most expensive jobs
      • Detected issues (skew, large shuffle, spill, config problems)
      • AI recommendations, side-by-side with raw metrics

Everything lives in one Fabric workspace, using:

  • Lakehouses for data
  • Spark notebooks / pipelines for processing
  • Azure AI models for reasoning
  • Power BI for visualization

Why a Fabric-Specific Job Doctor Is Worth Building

Spark is Spark, but in Fabric the story is different:

  • Spark jobs are tied closely to Lakehouses, Pipelines, Dataflows, and Power BI.
  • You already have a single control plane for capacity, governance, cost, and monitoring.
  • Logs, metrics, and reports can live right next to the workloads they describe.

That makes Fabric an ideal home for a Job Doctor:

  • No extra infrastructure to stand up
  • No random side services to glue together
  • The telemetry you need is already flowing; you just have to catch and shape it
  • AI can sit directly on top of your Lakehouse + monitoring data

With some Spark, a few Lakehouse tables, and an LLM, you can give every data engineer and analyst in your organization a “Spark performance expert” that’s always on call.

I’ve included a sample notebook you can use to get started on your Job Doctor today!


This post was created with help from (and suggested to me) by ChatGPT Pro using the 5.1 Thinking Model

Recap of Guy in a Cube Livestream for June 17th, 2023

Here’s a recap of the YouTube video titled “Power BI and Azure Synapse Analytics (formerly SQL Data Warehouse) – Guy in a Cube” by Patrick LeBlanc and Adam Saxton from June 17th.

The video starts with an introduction to Azure Synapse Analytics, formerly known as SQL Data Warehouse, and its integration with Power BI. The hosts, Patrick and Adam, discuss the benefits of using Synapse Analytics with Power BI, including the ability to handle large volumes of data and perform complex transformations.

They then demonstrate how to use Azure Synapse Studio, a unified web user interface for managing and monitoring your Azure Synapse Analytics workspace. They show how to create a new workspace, load data into a data frame, and use the Data Wrangler tool to manipulate and transform the data.

The Data Wrangler tool is compared to Power Query in Power BI, as it generates Python code for each transformation step, similar to how Power Query generates M code. They show how to drop columns, add new ones, and perform other transformations using the tool. They also highlight the need for a “close and apply” feature to write the transformed data back into the lake house.

The hosts also discuss the use of VS Code with Azure Synapse Analytics and Power BI, and how it can be used to add code to a notebook. However, they note that additional steps are needed to write the transformed data back into the lake house.

They also discuss the use of Python and Spark in Azure Synapse Analytics, and how they can be used to perform more complex transformations and analyses. They show how to use the pandas library to import and normalize JSON data, and how to convert it to a Spark data frame.

The video concludes with a Q&A session, where the hosts answer questions from the audience. They discuss topics such as data residency, best practices for migrating from Google Data Studio to Power BI, and the importance of taking time off work.

Overall, the video provides a comprehensive overview of Azure Synapse Analytics and its integration with Power BI and offers practical tips and demonstrations for using these tools effectively.

This blogpost was created with help from ChatGPT Pro and using the Voxscript plugin

Calling the OpenAI API from a Microsoft Fabric Notebook

Microsoft Fabric notebooks are a versatile tool for developing Apache Spark jobs and machine learning experiments. They provide a web-based interactive surface for writing code with rich visualizations and Markdown text support.

In this blog post, we’ll walk through how to call the OpenAI API from a Microsoft Fabric notebook.

Preparing the Notebook

Start by creating a new notebook in Microsoft Fabric. Notebooks in Fabric consist of cells, which are individual blocks of code or text that can be run independently or as a group. You can add a new cell by hovering over the space between two cells and selecting ‘Code’ or ‘Markdown’.

Microsoft Fabric notebooks support four Apache Spark languages: PySpark (Python), Spark (Scala), Spark SQL, and SparkR. For this guide, we’ll use PySpark (Python) as the primary language.

You can specify the language for each cell using magic commands. For example, you can write a PySpark query using the %%pyspark magic command in a Scala notebook. But since our primary language is PySpark, we won’t need a magic command for Python cells.

Microsoft Fabric notebooks are integrated with the Monaco editor, which provides IDE-style IntelliSense for code editing, including syntax highlighting, error marking, and automatic code completions.

Calling the OpenAI API

To call the OpenAI API, we’ll first need to install the OpenAI Python client in our notebook. Add a new cell to your notebook and run the following command:

!pip install openai

Next, in a new cell, write the Python code to call the OpenAI API:

import openai

openai.api_key = 'your-api-key'

response = openai.Completion.create(
  engine="text-davinci-002",
  prompt="Translate the following English text to French: '{}'",
  max_tokens=60
)

print(response.choices[0].text.strip())

Replace 'your-api-key' with your actual OpenAI API key. The prompt parameter is the text you want the model to generate from. The max_tokens parameter is the maximum length of the generated text.

You can run the code in a cell by hovering over the cell and selecting the ‘Run Cell’ button or bypressing Ctrl+Enter. You can also run all cells in sequence by selecting the ‘Run All’ button.

Wrapping Up

That’s it! You’ve now called the OpenAI API from a Microsoft Fabric notebook. You can use this method to leverage the powerful AI models of OpenAI in your data science and machine learning experiments.

Always remember that if a cell is running for a longer time than expected, or you wish to stop execution for any reason, you can select the ‘Cancel All’ button to cancel the running cells or cells waiting in the queue.

I hope this guide has been helpful. Happy coding!


Please note that OpenAI’s usage policies apply when using their API. Be sure to understand these policies before using the API in your projects. Also, keep in mind that OpenAI’s API is a paid service, so remember to manage your usage to control costs.

Finally, it’s essential to keep your API key secure. Do not share it publicly or commit it in your code repositories. If you suspect that your API key has been compromised, generate a new one through the OpenAI platform.

This blogpost was created with help from ChatGPT Pro

Building a Lakehouse Architecture with Microsoft Fabric: A Comprehensive Guide

Microsoft Fabric is a powerful tool for data engineers, enabling them to build out a lakehouse architecture for their organizational data. In this blog post, we will walk you through the key experiences that Microsoft Fabric.

Creating a Lakehouse

A lakehouse is a new experience that combines the power of a data lake and a data warehouse. It serves as a central repository for all Fabric data. To create a lakehouse, you start by creating a new lakehouse artifact and giving it a name. Once created, you land in the empty Lakehouse Explorer.

Importing Data into the Lakehouse

There are several ways to bring data into the lakehouse. You can upload files and folders from your local machine, use data flows (a low-code tool with hundreds of connectors), or leverage the pipeline copy activity to bring in petabytes of data at scale. Most of the marketing data in the lakehouse is in Delta tables, which are automatically created with no additional effort. You can easily explore the tables, see their schema, and even view the underlying files.

Adding Unstructured Data

In addition to structured data, you might want to add some unstructured customer reviews to accompany your campaign data. If this data already exists in storage, you can simply point to it with no data movement necessary. This is done by adding a new shortcut, which allows you to create a virtual table and virtual files inside your lakehouse. Shortcuts enable you to select from a variety of sources, including lakehouses and warehouses in Fabric, but also external storage like ADLS Gen 2 and even Amazon S3.

Leveraging the Data

Once all your data is ready in the lakehouse, there are many ways to use it. As a data engineer or data scientist, you can open up the lakehouse in a notebook and leverage Spark to continue transforming the data or build a machine learning model. As a SQL professional, you can navigate to the SQL endpoint of the lakehouse where you can write SQL queries, create views and functions, all on top of the same Delta tables. As a business analyst, you can navigate to the built-in modeling view and start developing your BI data model directly in the same warehouse experience.

Configuring your Spark Environment

As an administrator, you can configure the Spark environment for your data engineers. This is done in the capacity admin portal, where you can access the Spark compute settings for data engineers and data scientists. You can set a default runtime and default Spark properties, and also turn on the ability for workspace admins to configure their own custom Spark pools.

Collaborative Data Development

Microsoft Fabric also provides a rich developer experience, enabling users to collaborate easily, work with their lakehouse data, and leverage the power of Spark. You can view your colleagues’ code updates in real time, install ML libraries for your project, and use the built-in charting capabilities to explore your data. The notebook has a built-in resource folder which makes it easy to store scripts or other code files you might need for the project.

In conclusion, Microsoft Fabric provides a frictionless experience for data engineers building out their enterprise data lakehouse and can easily democratize this data for all users in an organization. It’s a powerful tool that combines the power of a data lake and a data warehouse, providing a comprehensive solution for data engineering tasks.

This blogpost was created with help from ChatGPT Pro

Related reading

How Spark Compute Works in Microsoft Fabric

Spark Compute is a key component of Microsoft Fabric, the end-to-end, unified analytics platform that brings together all the data and analytics tools that organizations need. Spark Compute enables data engineering and data science scenarios on a fully managed Spark compute platform that delivers unparalleled speed and efficiency.

What is Spark Compute?

Spark Compute is a way of telling Spark what kind of resources you need for your data analysis tasks. You can give your Spark pool a name, and choose how many and how big the nodes (the machines that do the work) are. You can also tell Spark how to adjust the number of nodes depending on how much work you have.

Spark Compute operates on OneLake, the data lake service that powers Microsoft Fabric. OneLake provides a single place to store and access all your data, whether it is structured, semi-structured, or unstructured. OneLake also supports data from other sources, such as Amazon S3 and (soon) Google Cloud Platform³.

Spark Compute supports both batch and streaming scenarios, and integrates with various tools and frameworks, such as Azure OpenAI Service, Azure Machine Learning, Databricks, Delta Lake, and more. You can use Spark Compute to perform data ingestion, transformation, exploration, analysis, machine learning, and AI tasks on your data.

How to use Spark Compute?

There are two ways to use Spark Compute in Microsoft Fabric: starter pools and custom pools.

Starter pools

Starter pools are a fast and easy way to use Spark on the Microsoft Fabric platform within seconds. You can use Spark sessions right away, instead of waiting for Spark to set up the nodes for you. This helps you do more with data and get insights quicker.

Starter pools have Spark clusters that are always on and ready for your requests. They use medium nodes that will dynamically scale-up based on your Spark job needs. Starter pools also have default settings that let you install libraries quickly without slowing down the session start time.

You only pay for starter pools when you are using Spark sessions to run queries. You don’t pay for the time when Spark is keeping the nodes ready for you.

Custom pools

A custom pool is a way of creating a tailored Spark pool according to your specific data engineering and data science requirements. You can customize various aspects of your custom pool, such as:

  • Node size: You can choose from different node sizes that offer different combinations of CPU cores, memory, and storage.
  • Node count: You can specify the minimum and maximum number of nodes you want in your custom pool.
  • Autoscale: You can enable autoscale to let Spark automatically adjust the number of nodes based on the workload demand.
  • Dynamic allocation: You can enable dynamic allocation to let Spark dynamically allocate executors (the processes that run tasks) based on the workload demand.
  • Libraries: You can install libraries from various sources, such as Maven, PyPI, CRAN, or your workspace.
  • Properties: You can configure custom properties for your custom pool, such as spark.executor.memory or spark.sql.shuffle.partitions.

Creating a custom pool is free; you only pay when you run a Spark job on the pool. If you don’t use your custom pool for 2 minutes after your job is done, Spark will automatically delete it. This is called the \”time to live\” property, and you can change it if you want.

If you are a workspace admin, you can also create default custom pools for your workspace, and make them the default option for other users. This way, you can save time and avoid setting up a new custom pool every time you run a notebook or a Spark job.

Custom pools take about 3 minutes to start, because Spark has to get the nodes from Azure.

Conclusion

Spark Compute is a powerful and flexible way of using Spark on Microsoft Fabric. It enables you to perform various data engineering and data science tasks on your data stored in OneLake or other sources. It also offers different options for creating and managing your Spark pools according to your needs and preferences.

If you want to learn more about Spark Compute in Microsoft Fabric, check out these resources:

This blogpost was created with help from ChatGPT Pro and Bing

Data Engineering in Microsoft Fabric: An Overview

Data engineering plays a crucial role in the modern data-driven world. It involves designing, building, and maintaining infrastructures and systems that enable organizations to collect, store, process, and analyze large volumes of data. Microsoft Fabric, a comprehensive analytics solution, offers a robust platform for data engineering. This blog post will provide a detailed overview of data engineering in Microsoft Fabric.

What is Data Engineering in Microsoft Fabric?

Data engineering in Microsoft Fabric enables users to design, build, and maintain infrastructures and systems that allow their organizations to collect, store, process, and analyze large volumes of data. Microsoft Fabric provides various data engineering capabilities to ensure that your data is easily accessible, well-organized, and of high-quality.

From the data engineering homepage, users can perform a variety of tasks:

  • Create and manage your data using a lakehouse
  • Design pipelines to copy data into your lakehouse
  • Use Spark Job definitions to submit batch/streaming jobs to Spark clusters
  • Use notebooks to write code for data ingestion, preparation, and transformation

Lakehouse Architecture

Lakehouses are data architectures that allow organizations to store and manage structured and unstructured data in a single location. They use various tools and frameworks to process and analyze that data. This can include SQL-based queries and analytics, as well as machine learning and other advanced analytics techniques.

Microsoft Fabric: An All-in-One Analytics Solution

Microsoft Fabric is an all-in-one analytics solution for enterprises that covers everything from data movement to data science, real-time analytics, and business intelligence. It offers a comprehensive suite of services, including data lake, data engineering, and data integration, all in one place.

Traditionally, organizations have been building modern data warehouses for their transactional and structured data analytics needs and data lakehouses for big data (semi/unstructured) data analytics needs. These two systems ran in parallel, creating silos, data duplicity, and increased total cost of ownership.

Fabric, with its unification of data store and standardization on Delta Lake format, allows you to eliminate silos, remove data duplicity, and drastically reduce total cost of ownership. With the flexibility offered by Fabric, you can implement either lakehouse or data warehouse architectures or combine these two together to get the best of both with simple implementation.

Data Engineering Capabilities in Microsoft Fabric

Fabric makes it quick and easy to connect to Azure Data Services, as well as other cloud-based platforms and on-premises data sources, for streamlined data ingestion. You can quickly build insights for your organization using more than 200 native connectors. These connectors are integrated into the Fabric pipeline and utilize the user-friendly drag-and-drop data transformation with dataflow.

Fabric standardizes on Delta Lake format. Which means all the Fabric engines can access and manipulate the same dataset stored in OneLake without duplicating data. This storage system provides the flexibility to build lakehouses using a medallion architecture or a data mesh, depending on your organizational requirement. You can choose between a low-code or no-code experience for data transformation, utilizing either pipelines/dataflows or notebook/Spark for a code-first experience.

Power BI can consume data from the Lakehouse for reporting and visualization. Each Lakehouse has a built-in TDS/SQL endpoint, for easy connectivity and querying of data in the Lakehouse tables from other reporting tools.

Conclusion

Microsoft Fabric is a powerful tool for data engineering, providing a comprehensive suite of services and capabilities for data collection, storage, processing, and analysis. Whether you’re looking to implement a lakehouse or data warehouse architecture, or a combination of both, Fabric offers the flexibility and functionality to meet your data engineering needs.

This blogpost was created with help from ChatGPT Pro