Multi-Cloud Pipeline: S3, Databricks & Fabric

Build a cross-cloud data pipeline connecting AWS S3, Databricks, and Microsoft Fabric.

Introduction

30 Second Summary

Most companies don't keep all their data in one place. Sales numbers might live in one cloud, the tools to clean that data in another, and the dashboards that executives check every morning in a third.

In this project, you will build a multi-cloud data pipeline that stores raw sales data in AWS S3, transforms it in Databricks, and delivers clean results to Microsoft Fabric for analysis.

What You'll Build

You will run a regional sales query in Fabric, watch it return wrong results because the raw data is messy, fix the data in Databricks, and re-run the same query to see correct numbers.

By the end of this project, you'll have:

  • A Fabric Lakehouse that queries raw S3 data through an OneLake shortcut, where you can see firsthand how inconsistent casing, duplicates, and missing values produce wrong analytics results.
  • A PySpark transformation notebook in Databricks that reads messy data from S3, standardizes text, parses dates, fills gaps, removes duplicates, and writes clean Parquet files back to S3.
  • A side-by-side comparison of raw vs. clean query results in Fabric, proving the full S3-to-Databricks-to-S3-to-Fabric pipeline delivers accurate regional sales numbers.
  • Secret Mission: extend the pipeline with a products dimension table, join it with orders in Databricks, and query revenue by product category in Fabric.

Are there any prerequisites?

This project assumes you already have three things set up: an AWS account with the AWS CLI configured, a Databricks workspace running on AWS, and a Microsoft Fabric workspace (trial or paid).

You do not need prior experience with any of these tools. Step 1 walks you through creating every resource from scratch.

Before We Start

Before diving in, take a moment to commit to what you are building and why it matters.

You are about to wire together three platforms into a single data pipeline. AWS S3 acts as your shared data layer, Databricks transforms messy data into clean output, and Microsoft Fabric queries the results for analytics.

Set Up S3 Bucket and Sample Data

Every multi-cloud pipeline needs a shared storage layer that all platforms can access independently. In this project, AWS S3 is that layer. It sits at the center of the architecture so both Databricks and Microsoft Fabric can read and write data without talking to each other directly.

In this step, you'll create an S3 bucket, load it with sample sales data that has intentional quality issues, and create a dedicated IAM user so Fabric can connect to the bucket securely.

In this step, get ready to:
  • Create an S3 bucket with raw/ and clean/ prefixes and upload messy sample data.
  • Create an IAM user with read-only S3 permissions for Microsoft Fabric.
  • Verify the bucket structure from the command line.
Create the S3 bucket and upload sample data

S3 bucket names must be globally unique across all AWS accounts. You'll include your name in the bucket name to avoid collisions.

  • Open your terminal and move to your Desktop by running this command:
cd ~/Desktop
  • Create a new S3 bucket by running this command:
aws s3 mb s3://[[BUCKET_NAME="your-name-multicloud-pipeline"]]

What does this command do?

The aws s3 mb command creates ("makes") a new S3 bucket. The bucket name you chose becomes the globally unique address for your data.

You should see a message confirming the bucket was created, like make_bucket: your-name-multicloud-pipeline.

Seeing a BucketAlreadyExists error?

S3 bucket names are globally unique. If someone else already has that name, try adding a number or extra word to make it unique.

help me fix my S3 bucket name error

Now you need a sample data file to upload. This CSV has 15 rows of sales data with intentional quality problems that you'll fix later using Databricks.

  • Create a new file called orders.csv on your Desktop. Open your preferred text editor, paste the following content, and save it as orders.csv on your Desktop:
order_id,customer_name,product,quantity,price,order_date,region
1001,John Smith,Widget A,5,29.99,2024-01-15,north
1002,jane doe,Widget B,3,49.99,01/20/2024,South
1003,JOHN SMITH,Widget A,5,29.99,2024-01-15,NORTH
1004,Bob Johnson,Widget C,,99.99,2024-02-01,east
1005,Alice Brown,widget b,2,49.99,Feb 5 2024,West
1006,jane Doe,Widget D,1,19.99,2024-02-10,south
1007,Charlie Wilson,Widget A,4,29.99,2024/02/15,North
1008,Bob Johnson,Widget B,6,49.99,2024-02-20,East
1009,alice brown,Widget C,3,99.99,2024-03-01,WEST
1010,John Smith,Widget D,2,19.99,03-05-2024,north
1011,Diana Lee,Widget A,7,29.99,2024-03-10,South
1012,Charlie Wilson,Widget B,,49.99,2024-03-15,north
1013,Eve Martinez,widget a,1,29.99,Mar 20 2024,East
1014,BOB JOHNSON,Widget C,,99.99,2024-02-01,EAST
1015,Frank Green,Widget D,3,19.99,2024-04-01,West

What's wrong with this data?

This data has four categories of quality issues that would break any analytics query.

  • Inconsistent casing: north, North, and NORTH all appear as separate regions.
  • Multiple date formats: 2024-01-15, 01/20/2024, Feb 5 2024, and more.
  • Missing values: three rows have empty quantity fields.
  • Duplicate records: rows 1001 and 1003 are the same order entered twice with different casing.
  • Upload the CSV to the raw/ prefix in your bucket by running this command:
aws s3 cp orders.csv s3://[[BUCKET_NAME="your-name-multicloud-pipeline"]]/raw/orders.csv
  • Create the empty clean/ prefix by running this command:
aws s3api put-object --bucket [[BUCKET_NAME="your-name-multicloud-pipeline"]] --key clean/

Why two prefixes?

The raw/ prefix holds the original, messy data. The clean/ prefix is where Databricks will write the transformed output later. Keeping them separate means Fabric can create a shortcut to each prefix independently.

  • Confirm the upload worked by listing the raw/ prefix:
aws s3 ls s3://[[BUCKET_NAME="your-name-multicloud-pipeline"]]/raw/

You should see orders.csv listed with its file size and upload timestamp.

Don't see orders.csv in the listing?

Make sure you ran the aws s3 cp command from the same directory where you saved orders.csv. If you saved it somewhere other than your Desktop, either move the file or cd to the correct folder first.

help me troubleshoot my S3 upload

Create an IAM user for Fabric

Microsoft Fabric connects to S3 using an IAM access key pair. You'll create a dedicated IAM user with the minimum permissions Fabric needs: the ability to list the bucket and read objects from it.

  • Open the IAM console in your browser at https://console.aws.amazon.com/iam/.
  • In the left navigation pane, click Users.
  • Click Create user.
  • Enter fabric-s3-reader as the user name.
  • Do not check the box for console access. This user only needs programmatic access via access keys.
  • Click Next.
  • On the permissions page, click Next again. You'll add an inline policy after the user is created.
  • Click Create user.

You should see your new fabric-s3-reader user in the users list.

  • Click the user name fabric-s3-reader to open the user details page.
  • Select the Permissions tab.
  • Click Add permissions, then select Create inline policy.
  • In the Policy editor section, select the JSON option.
  • Clear any existing content and paste the following policy:
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::[[BUCKET_NAME=\"your-name-multicloud-pipeline\"]]",
                "arn:aws:s3:::[[BUCKET_NAME=\"your-name-multicloud-pipeline\"]]/*"
            ]
        }
    ]
}

What does this policy allow?

  • s3:ListBucket lets Fabric browse the bucket's contents and discover files inside each prefix.
  • s3:GetObject lets Fabric read the actual file data (CSV, Parquet) from the bucket.

The two Resource lines scope these permissions to your specific bucket. The first targets the bucket itself (for listing). The second targets all objects inside it (for reading).

  • Click Next.
  • Under Policy name, enter FabricS3ReadOnly.
  • Click Create policy.

The policy now appears under the Permissions tab for your user. Next, you need to generate access keys so Fabric can authenticate as this user.

  • Select the Security credentials tab on the same user details page.
  • In the Access keys section, click Create access key.
  • On the Access key best practices & alternatives page, select Other, then click Next.
  • Click Create access key.

Save your credentials now

You will see an Access key ID and a Secret access key. Copy both values and save them somewhere secure (a password manager or a local text file that you will not commit to version control).

The secret access key is shown only once. If you close this page without saving it, you will need to create a new access key.

  • After saving both values, click Done.

Lost your secret access key?

If you closed the page before copying the secret, go back to the Security credentials tab, delete the access key you just created, and create a new one.

help me recreate my IAM access key

Verify the bucket structure

Before you move on, confirm that your S3 bucket has the right structure. Think about what you expect to see before running the next command.

  • Verify the bucket contents by running this command:
aws s3 ls s3://[[BUCKET_NAME="your-name-multicloud-pipeline"]]/raw/

You should see orders.csv listed. This confirms your S3 bucket is ready and the raw data is accessible.

Your S3 bucket is loaded and your IAM credentials are ready. Next up, you'll connect Fabric to this raw data and see exactly how messy it is when you try to run analytics on it.

Connect Fabric to Raw S3 Data

Your S3 bucket is loaded with messy sales data and ready for analysis. The next step is connecting Microsoft Fabric so you can query that data directly.

Fabric can access S3 files through OneLake shortcuts, which are pointers to data in another cloud. No data is copied. In this step, you'll create a Lakehouse, link it to your raw S3 data, and run a regional sales query to see what the data looks like.

In this step, get ready to:
  • Create a Fabric Lakehouse with an S3 shortcut to the raw data.
  • Load and display the CSV data in a Spark notebook.
  • Run a regional sales aggregation query to analyze the data.
Create a Lakehouse and S3 shortcut

A Lakehouse in Fabric gives you a place to store and query data using Spark notebooks. You'll create one now, then add an S3 shortcut so Fabric can read your raw CSV directly from AWS.

  • Open the Fabric portal and navigate to your workspace.
  • Select + New item at the top of the page.
  • Search for or select Lakehouse.
  • Enter a name for your Lakehouse (for example, SalesLakehouse) and select Create.

The Lakehouse opens to the Explorer view, showing empty Tables and Files sections.

What is a Lakehouse?

A Fabric Lakehouse combines a file storage area (Files) for raw or semi-structured data with a table area (Tables) for structured Delta data. Everything is stored in OneLake and accessible to every Fabric engine.

Now you'll create an S3 shortcut under Files so Fabric can read your raw CSV without copying it from AWS.

  • In the Explorer pane on the left, select the ellipsis (...) next to Files.
  • Select New shortcut.
  • Under External sources, select Amazon S3.
  • In the URL field, enter your S3 bucket URL in the format:
https://[[BUCKET_NAME="your-bucket-name"]].s3.[[AWS_REGION="your-region-code"]].amazonaws.com
  • For Connection, select Create new connection.
  • Enter a name in the Connection name field (for example, s3-raw-connection).
  • In the Access Key ID field, paste the Access Key ID you generated for the IAM user in the previous step.
  • In the Secret Access Key field, paste the corresponding Secret Access Key.
  • Select Next.

Why use the Files section for this shortcut?

Shortcuts under Files can point to data in any format (CSV, Parquet, JSON). Shortcuts under Tables only auto-discover Delta-format data. Since you are starting with a raw CSV, the Files section is the right place.

  • In the folder browser, navigate to and select the raw prefix (folder) in your S3 bucket.
  • Select Next.
  • On the review page, rename the shortcut to s3_raw.
  • Select Create.

The Lakehouse refreshes automatically. You should see s3_raw appear as a folder under Files in the Explorer pane, with orders.csv visible inside it.

Don't see the shortcut?

  • Double-check that the S3 URL matches the format https://BucketName.s3.RegionCode.amazonaws.com exactly. A typo in the bucket name or region code causes a connection failure.
  • Verify that the IAM user has both s3:GetObject and s3:ListBucket permissions on the bucket.
  • Make sure you selected the raw folder (not the bucket root) as the shortcut target.

Still stuck? Help me troubleshoot my S3 shortcut connection in Fabric.

Load raw data in a Spark notebook

With the shortcut in place, you can now read the CSV data using a Spark notebook. This lets you see exactly what the raw data looks like before running any analysis.

  • From the Lakehouse ribbon at the top, select Open notebook, then select New notebook.

A new Spark notebook opens with your Lakehouse already attached in the left panel.

  • Rename the notebook to explore_raw_data by clicking the notebook name at the top of the page and typing the new name.
  • In the first code cell, paste the following code to load the CSV from the S3 shortcut:
# Cell 1: Load raw CSV from S3 shortcut
df = spark.read.csv(
    "Files/s3_raw/orders.csv",
    header=True,
    inferSchema=True
)
display(df)
print(f"Total records: {df.count()}")

What does this code do?

  • spark.read.csv("Files/s3_raw/orders.csv", ...) reads the CSV file through the shortcut. The relative path Files/s3_raw/ resolves to the S3 shortcut you just created.
  • header=True tells Spark the first row contains column names.
  • inferSchema=True lets Spark automatically detect data types for each column.
  • display(df) renders the DataFrame as a table in the notebook output.
  • Run the cell by clicking the Run icon (play button) to the left of the cell.

You should see a table displaying all 15 rows of the raw orders data. The output also prints Total records: 15.

Take a look at the data. You might already notice some issues: customer names appear in different cases (John Smith, JOHN SMITH), region values are inconsistent (north, North, NORTH), and some quantity values are missing.

Cell not running?

  • The first cell may take 30-60 seconds to start while Spark initializes a session. Wait for the session to become ready.
  • Confirm the shortcut name is s3_raw in the Explorer pane. If you named it something else, update the path in the code to match.
  • Make sure your Lakehouse is pinned as the default in the left panel (indicated by a pin icon next to the name).

Still stuck? Help me troubleshoot loading CSV data from an S3 shortcut in a Fabric Spark notebook.

Run the regional aggregation query

Now that you can see the raw data, try a real analytics query. You'll group orders by region to calculate total revenue and order counts. This is the kind of query a business analyst would run to compare regional performance.

  • Add a new code cell below the first one by hovering between cells and selecting Code.
  • Paste the following code into the new cell:
# Cell 2: Regional aggregation - see the problem
df.createOrReplaceTempView("raw_orders")
result = spark.sql("""
    SELECT region,
           COUNT(*) as order_count,
           ROUND(SUM(quantity * price), 2) as revenue
    FROM raw_orders
    GROUP BY region
    ORDER BY revenue DESC
""")
display(result)
print(f"Region count (should be 4): {result.count()}")

What does this code do?

  • createOrReplaceTempView("raw_orders") registers the DataFrame as a temporary table so you can query it with SQL.
  • The SQL query groups orders by region, counts orders, and calculates total revenue (quantity times price).
  • The final print statement shows how many distinct region groups the query returned.

Before you run this cell, think about what you expect to see. The data has four real regions: North, South, East, and West. How many rows do you think the query will return?

  • Run the cell.

The results are not what you expected. Instead of 4 region groups, the query returns 8 or more. The print statement confirms this: Region count (should be 4): 8 (the exact count depends on the number of distinct case variations in the data).

Look at the output closely. You'll see north, North, and NORTH treated as three separate regions. Spark SQL treats text comparisons as case-sensitive, so each capitalization variant creates its own group.

Why are the results wrong?

The raw data has three quality problems that break this query.

  • Inconsistent casing: north, North, and NORTH appear as three separate regions instead of one.
  • Duplicate records: some orders appear more than once (for example, order 1001 and 1003 are the same order with different casing), inflating the totals.
  • Missing values: some rows have null quantities, which means quantity * price evaluates to null and that revenue disappears from the sum.

This is exactly the problem you'll fix. In the next steps, you'll bring in Databricks to clean the data, then reconnect Fabric to the polished result.

✔️ Awesome, I've got everything!

Great. Double check you've saved the notebook (Ctrl+S or Cmd+S) and both cells show output.

ⓧ I'd like to double check the full code

Your explore_raw_data notebook should have two cells. Scroll up and confirm each one is present in order:

  • Cell 1: Loads the CSV with spark.read.csv, calls display(df), and prints the total record count.
  • Cell 2: Creates a temp view called raw_orders, runs the regional aggregation SQL query, displays the result, and prints the region count.

If any cell is missing, add it and re-run.

You've just proven the problem: raw data breaks analytics. Querying directly on messy data gives you wrong numbers that would mislead any business decision. Next up, you'll connect Databricks to S3 so you can start cleaning this data.

Connect Databricks to the S3 Bucket

In the previous step, you ran a regional sales query in Fabric and watched it fall apart. "north", "North", and "NORTH" all showed up as separate regions, duplicates inflated totals, and the numbers were unreliable.

To fix these quality issues, you need a transformation engine. Databricks on AWS has native S3 integration through Unity Catalog, giving you governed access to read the raw data and write clean results back to S3. In this step, you'll connect Databricks to the same S3 bucket and confirm it can see the messy data.

In this step, get ready to:
  • Create a Unity Catalog external location connecting Databricks to your S3 bucket with read/write access.
  • Create a Python notebook and read the raw CSV directly from S3.
  • Inspect the data quality issues from the Databricks side.
Create an external location for your S3 bucket

An external location is a Unity Catalog object that maps an S3 path to an IAM role. It tells Databricks where your data lives and what credentials to use when accessing it.

  • Open your Databricks workspace in a new browser tab.
  • In the left sidebar, click Catalog.
  • Click Connect, then click External locations.
  • Click Create external location.
  • Select Set up Automatically, then click Next.

What is automated setup?

The automated setup uses AWS IAM temporary delegation to provision both the IAM role in your AWS account and the storage credential in Databricks. You do not need to manually create an IAM role or configure trust policies.

  • For External location name, enter a descriptive name like s3-pipeline-bucket.
  • For S3 bucket URL, enter your bucket path in the format s3://your-bucket-name.
  • Make sure Limit to read-only use is unchecked. Databricks needs to write clean data back to this bucket in the next step.
  • Click Log into AWS and create external location.
  • In the Review AWS Resources dialog, review the resources Databricks will create in your AWS account.
  • Click Initiate external location creation.
  • A new window prompts you to log in to AWS. Sign in with your AWS credentials.
  • Click Allow access to approve the temporary delegation request.

How long does provisioning take?

Databricks provisions the IAM role and external location in the background. This can take up to 8 minutes. When the status changes to Available, the external location is ready to use.

  • Wait for the external location status to show Available.
  • Click on the external location you just created, then click Test connection to confirm Databricks can reach your S3 bucket.

You should see a success message confirming the connection works.

Connection test failed?

  • Make sure you left the read-only checkbox unchecked. If you selected it, delete the external location and create a new one.
  • Confirm your S3 bucket name is correct and the bucket exists in your AWS account.
  • If the status shows Failed, click the external location to see the detailed error message. A common cause is the delegation request expiring before approval.

Help me troubleshoot my Databricks external location connection to S3.

Create a notebook and read raw data from S3

With the external location in place, Databricks can now read and write to your S3 bucket using standard PySpark commands. Time to pull in the raw data and see what you're working with.

  • In the Databricks sidebar, click New, then select Notebook.
  • Name the notebook transform_orders.
  • Make sure the default language is set to Python.
  • In the first cell, paste the following code to read the raw CSV from S3:
# Cell 1: Read raw data from S3
raw_df = spark.read.csv(
    "s3://[[BUCKET_NAME="your-bucket-name-here"]]/raw/orders.csv",
    header=True,
    inferSchema=True
)

display(raw_df)
print(f"Raw record count: {raw_df.count()}")

What does this code do?

  • spark.read.csv() reads the CSV file directly from S3 using the external location you just created. Databricks resolves the s3:// path through Unity Catalog's storage credential.
  • header=True tells Spark to treat the first row as column names, and inferSchema=True automatically detects data types.
  • display(raw_df) renders the data as a table in the Databricks notebook, and the print statement confirms the total row count.

Before you run this cell, think about what you expect to see. Will the data look familiar?

  • Run the cell by clicking the play button or pressing Shift+Enter.

You should see the same 15 rows of messy data you saw in Fabric. The output reads Raw record count: 15. Same data, different engine. Databricks is now reading directly from S3.

Seeing a permission error?

  • Double-check the bucket name in the S3 path matches your actual bucket name exactly.
  • Confirm the external location status is Available. If it is still provisioning, wait for it to complete before running the cell.
  • Make sure the external location path covers the full bucket, not just a specific prefix.

Help me fix my Databricks notebook that can't read from S3.

Inspect the data quality issues

You already know the data is messy from Fabric's broken query results. Now let's quantify the specific problems before you fix them.

  • Add a new cell below Cell 1 and paste the following code to inspect the quality issues:
# Cell 2: Inspect data quality issues
from pyspark.sql.functions import col

print("=== Region values (should be 4) ===")
raw_df.select("region").distinct().show()
print(f"=== Null quantities: {raw_df.filter(col('quantity').isNull()).count()} ===")

What does this code do?

  • raw_df.select("region").distinct().show() lists every unique region value in the dataset. You should see 4 real regions (North, South, East, West), but the inconsistent casing creates 8 or more variants.
  • raw_df.filter(col('quantity').isNull()).count() counts how many orders have missing quantity values. These gaps will skew any revenue or average calculations.
  • Run Cell 2 by pressing Shift+Enter.

The output shows the full list of distinct region values. You should see entries like north, North, NORTH, east, East, EAST, and more. The null quantity count shows 3 rows with missing values.

What exactly is broken?

The raw data has four distinct problems that need fixing.

  • Inconsistent casing across customer names, products, and regions splits identical values into separate groups.
  • Multiple date formats make time-based analysis impossible without standardization.
  • Missing quantity values (3 rows) skew revenue and average calculations.
  • Duplicate records (same customer, product, and date) inflate order counts and totals.

✔️ Awesome, I've got everything!

Great. Double check you've saved your notebook.

ⓧ I'd like to double check the full code

Here is the full transform_orders notebook so far. Make sure your two cells match:

# Cell 1: Read raw data from S3
raw_df = spark.read.csv(
    "s3://[[BUCKET_NAME="your-bucket-name-here"]]/raw/orders.csv",
    header=True,
    inferSchema=True
)

display(raw_df)
print(f"Raw record count: {raw_df.count()}")

# Cell 2: Inspect data quality issues
from pyspark.sql.functions import col

print("=== Region values (should be 4) ===")
raw_df.select("region").distinct().show()
print(f"=== Null quantities: {raw_df.filter(col('quantity').isNull()).count()} ===")

You've now confirmed the same data quality problems from two different platforms. Fabric showed the symptoms (broken query results). Databricks just showed the root causes (8+ region variants, 3 null quantities, duplicates). Next up, you'll write the PySpark transformations to fix every one of these issues.

Transform and Write Clean Data to S3

In the previous step, you connected Databricks to your S3 bucket and confirmed it can read the raw CSV. You also saw the quality issues up close: inconsistent casing, null values, and duplicate rows.

Now you'll fix those issues. In this step, you'll write a PySpark transformation pipeline that standardizes text, parses five different date formats, fills missing values, and removes duplicate records. The clean output goes back to S3 as Parquet files, ready for Fabric to consume.

In this step, get ready to:
  • Standardize text, parse dates, fill nulls, and remove duplicates in PySpark.
  • Write the clean DataFrame to S3 as Parquet files.
  • Verify the output by reading the Parquet files back from S3.
Build the transformation pipeline

The raw data has four distinct problems: inconsistent casing ("north", "North", "NORTH"), five different date formats, missing quantity values, and duplicate order records. Each one breaks analytics in a different way.

  • In the transform_orders notebook, create a new cell below Cell 2.
  • Start the transformation by adding the following code, which handles text standardization and date parsing:
# Cell 3: Transform - standardize, parse dates, fill nulls, deduplicate
from pyspark.sql.functions import initcap, trim, to_date, coalesce, row_number
from pyspark.sql.window import Window

# Standardize text columns
clean_df = (
    raw_df
    .withColumn("customer_name", initcap(trim(col("customer_name"))))
    .withColumn("product", initcap(trim(col("product"))))
    .withColumn("region", initcap(trim(col("region"))))
)

# Parse multiple date formats
clean_df = clean_df.withColumn(
    "order_date",
    coalesce(
        to_date(col("order_date"), "yyyy-MM-dd"),
        to_date(col("order_date"), "MM/dd/yyyy"),
        to_date(col("order_date"), "yyyy/MM/dd"),
        to_date(col("order_date"), "MM-dd-yyyy"),
        to_date(col("order_date"), "MMM d yyyy")
    )
)

What does this code do?

  • initcap capitalizes the first letter of each word, and trim removes leading and trailing whitespace. Together, they turn "JOHN SMITH", "john smith", and "John Smith" into one consistent value.
  • coalesce tries each to_date format in order and returns the first one that successfully parses. This handles all five date formats in the dataset without needing to know which format each row uses.
  • col is already available from the import in Cell 2, so it doesn't need to be imported again.
  • Still in the same cell, add the following code below what you just pasted to handle null quantities, duplicates, and the final output:
# Fill missing quantities with median
median_qty = clean_df.filter(
    col("quantity").isNotNull()
).approxQuantile("quantity", [0.5], 0.01)[0]
clean_df = clean_df.fillna({"quantity": int(median_qty)})

# Remove duplicates (same customer, product, date)
window_spec = Window.partitionBy(
    "customer_name", "product", "order_date"
).orderBy("order_id")
clean_df = (
    clean_df
    .withColumn("row_num", row_number().over(window_spec))
    .filter(col("row_num") == 1)
    .drop("row_num")
)

display(clean_df)
print(f"Clean record count: {clean_df.count()}")
print("=== Region values after cleaning ===")
clean_df.select("region").distinct().show()

What does this code do?

  • approxQuantile calculates the median of existing quantity values. The three null quantities get filled with this median instead of being dropped entirely, which preserves records that carry valid data in every other column.
  • The Window function groups rows by customer name, product, and order date. Within each group, row_number assigns sequential numbers ordered by order_id. Keeping only row 1 from each group removes true duplicates while preserving the original record.

Before you run this cell, how many records do you expect to remain after removing duplicates? Think about which rows in the raw data share the same customer, product, and date.

  • Run Cell 3 by clicking the play button or pressing Shift+Enter.

The output table shows all columns with consistent title-case text, standardized dates, and no null quantities. Below the table, the print output confirms 13 clean records (down from 15, meaning two duplicates were removed) and exactly 4 distinct regions: East, North, South, West.

Which records were duplicates?

Order 1003 (JOHN SMITH, Widget A, 2024-01-15) is a duplicate of order 1001. Order 1014 (BOB JOHNSON, Widget C, 2024-02-01) is a duplicate of order 1004. After standardizing the text, both pairs matched on customer name, product, and order date.

Cell 3 throwing an error?

  • If you see a NameError for raw_df or col, run Cell 1 and Cell 2 first. Notebook cells share the session, so earlier cells must run before later ones can reference their variables.
  • If you see a syntax error, check that both code blocks were pasted into the same cell with no missing lines between them.
  • Why is my transformation cell failing?
Write clean data to S3 as Parquet

The clean DataFrame lives in memory right now. To make it available for Fabric to consume, you need to write it back to S3 in a format optimized for analytics.

  • Create a new cell below Cell 3. Write the clean data to S3 by adding the following code:
# Cell 4: Write clean data to S3 as Parquet
clean_df.write.mode("overwrite").parquet(
    "s3://your-bucket-name-here/clean/orders/"
)
print("Clean data written to s3://your-bucket-name-here/clean/orders/")

What does this code do?

This writes the clean DataFrame as Parquet files to the clean/orders/ prefix in your S3 bucket. Parquet is a columnar storage format that compresses data efficiently and lets query engines read only the columns they need.

mode("overwrite") replaces any existing files in that prefix. This means you can re-run the notebook without conflicts.

Replace the bucket name

Replace your-bucket-name-here with the same bucket name you used in Cell 1. The path must match exactly for the external location to authorize the write.

  • Run Cell 4.

You should see the confirmation message printed below the cell. This means the Parquet files are now sitting in your S3 bucket under the clean/orders/ prefix.

Write failed with an access denied error?

  • Check that your Unity Catalog external location was created with read/write access. If it was set to read-only, you need to update the IAM role policy to include s3:PutObject permissions.
  • Verify the S3 path in Cell 4 matches the bucket and prefix covered by your external location.
  • Why is my Databricks write to S3 failing?
Verify the output in S3

A successful write does not guarantee the data is correct. Reading the Parquet files back from S3 confirms both that they were written and that the content matches your expectations.

Before you run the next cell, what do you expect the record count to be? How many distinct regions should appear?

  • Create a new cell below Cell 4. Read the Parquet files back by adding the following code:
# Cell 5: Verify the output
verify_df = spark.read.parquet(
    "s3://your-bucket-name-here/clean/orders/"
)
print(f"Verified: {verify_df.count()} clean records in S3")
verify_df.select("region").distinct().show()

What does this code do?

This reads the Parquet files from S3 into a new DataFrame, completely independent of the in-memory clean_df. If the count and region values match what Cell 3 produced, you know the write-then-read round-trip succeeded.

  • Replace your-bucket-name-here with your actual bucket name.
  • Run Cell 5.

The output confirms 13 clean records in S3. The distinct region values show exactly 4 rows: East, North, South, West. This matches the Cell 3 output, proving the data survived the round-trip to S3 intact.

Seeing 0 records or a path error?

  • Double-check that the S3 path in Cell 5 matches the path in Cell 4 exactly, including the trailing slash after orders/.
  • Make sure Cell 4 ran without errors before running Cell 5. If Cell 4 failed silently, no Parquet files were written.
  • Why is my Parquet verification showing unexpected results?

✔️ Awesome, I've got everything!

Great. Double-check that your transform_orders notebook has 5 cells total: Cell 1 reads raw data, Cell 2 inspects quality, Cell 3 transforms, Cell 4 writes to S3, and Cell 5 verifies the output.

ⓧ I'd like to double check the full code

Your transform_orders notebook should have 5 cells in this order. Scroll up and confirm each one is present:

  • Cell 1 reads the raw CSV from S3 with spark.read.csv, displays it, and prints the raw record count (15).
  • Cell 2 imports col, shows distinct region values, and counts null quantities.
  • Cell 3 imports initcap, trim, to_date, coalesce, row_number, and Window. Standardizes text, parses dates, fills nulls with median, removes duplicates, then displays the clean DataFrame (13 records, 4 regions).
  • Cell 4 writes the clean DataFrame to S3 as Parquet at clean/orders/.
  • Cell 5 reads the Parquet files back from S3 and prints the verified record count and distinct region values.

Your S3 bucket now holds both the raw CSV and the clean Parquet files. Next up, you'll connect Fabric to the clean data and re-run the same regional query that broke in Step 2. This time, it will return the right numbers.

Analyze Clean Data in Fabric

Your Databricks transformation pipeline cleaned the messy data and wrote polished Parquet files back to S3. The data quality issues that broke your regional analytics in Step 2 should now be gone.

In this step, you will connect Microsoft Fabric to the clean S3 data and re-run the same regional aggregation query. If the pipeline worked, you will see exactly 4 regions instead of 8+.

In this step, get ready to:
  • Create an S3 shortcut to the clean Parquet data in your Fabric Lakehouse.
  • Run the same regional aggregation query on the clean data.
  • Compare raw and clean results to prove the pipeline works end-to-end.
Create an S3 shortcut for clean data

The clean Parquet files are sitting in S3 at the clean/orders/ prefix. To access them from Fabric without copying any data, you need a second OneLake shortcut pointing to this new location.

  • Switch back to your Fabric workspace in the browser.
  • Open your Lakehouse from the workspace item list.
  • In the Lakehouse Explorer, click the ellipsis ... next to Files.
  • Select New shortcut.
  • Under External sources, select Amazon S3.

Already have a connection?

If you see the S3 connection you created in Step 2 in the Connection dropdown, select it and skip to the browse step. Otherwise, create a new connection with the same bucket URL and IAM credentials you used earlier.

  • In the URL field, enter your bucket URL in the format https://BucketName.s3.RegionCode.amazonaws.com.
  • Enter the same Access Key ID and Secret Access Key from the IAM user you created in Step 1.
  • Click Next.
  • In the storage browser, expand the folder tree and select the checkbox next to the clean/orders/ prefix.
  • Click Next to reach the review page.
  • Click the pencil icon in the action column and rename the shortcut to s3_clean.
  • Click Create.

The Lakehouse Explorer refreshes automatically. You should see s3_clean appear under the Files section alongside your existing s3_raw shortcut.

Don't see s3_clean under Files?

  • Make sure you created the shortcut under Files, not Tables. Right-click Files specifically.
  • Double-check that you selected the clean/orders/ prefix in the storage browser, not the raw/ prefix.
  • If the connection fails, verify your IAM credentials still have s3:GetObject and s3:ListBucket permissions.

Still stuck? Help me troubleshoot my S3 shortcut connection in Fabric.

Load and query the clean data

With the shortcut in place, you can read the clean Parquet files directly from a Spark notebook. This is the same zero-copy pattern you used in Step 2, but now pointing at transformed data.

  • From the Lakehouse menu, select Open notebook, then select New notebook.
  • Rename the notebook to analyze_clean_data by clicking the notebook name at the top of the page.
  • In the first cell, load the clean Parquet data from the shortcut by pasting this code:
# Cell 1: Load clean Parquet from S3 shortcut
df = spark.read.parquet("Files/s3_clean/")
display(df)
print(f"Total records: {df.count()}")

What does this code do?

  • spark.read.parquet("Files/s3_clean/") reads the Parquet files through the shortcut you just created. The relative path resolves to the default Lakehouse's OneLake storage, which redirects to S3 behind the scenes.
  • display(df) renders the DataFrame as a table so you can visually inspect the clean records.
  • The print statement confirms the total record count.
  • Run the cell by pressing Shift+Enter.

You should see 13 records displayed in the output table. Notice that names are now title-cased, regions are consistent, and there are no null quantities.

Seeing 0 records or a read error?

  • Confirm the shortcut name is s3_clean by checking under Files in the Lakehouse Explorer. The path in your code must match exactly.
  • Make sure your Databricks notebook completed the Parquet write in Step 4. If the clean/orders/ prefix is empty in S3, re-run Cell 4 of the transform_orders notebook.

Need help? Help me debug why my Fabric notebook can't read from the s3_clean shortcut.

Now for the moment of truth. You are about to run the same regional aggregation query that produced broken results in Step 2.

Before you run it, think back to the raw data query. It returned 8+ region groups because "north", "North", and "NORTH" were treated as separate values. How many region groups do you expect the clean data to produce?

  • Add a new cell below Cell 1 and paste this aggregation query:
# Cell 2: Regional aggregation - see the fix
df.createOrReplaceTempView("clean_orders")
result = spark.sql("""
    SELECT region,
           COUNT(*) as order_count,
           ROUND(SUM(quantity * price), 2) as revenue
    FROM clean_orders
    GROUP BY region
    ORDER BY revenue DESC
""")
display(result)
print(f"Region count (should be 4): {result.count()}")

What does this query do?

This is the exact same aggregation logic you ran against the raw data in Step 2. It groups orders by region, counts orders, and sums revenue.

The only difference is the source. Instead of reading raw_orders, you are reading clean_orders from the Databricks-transformed Parquet files.

  • Run the cell by pressing Shift+Enter.

The output now shows exactly 4 rows: East, North, South, and West. The region count prints as 4.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your notebook.

ⓧ I'd like to double check the full code

Here is the complete analyze_clean_data notebook for reference:

# Cell 1: Load clean Parquet from S3 shortcut
df = spark.read.parquet("Files/s3_clean/")
display(df)
print(f"Total records: {df.count()}")

# Cell 2: Regional aggregation - see the fix
df.createOrReplaceTempView("clean_orders")
result = spark.sql("""
    SELECT region,
           COUNT(*) as order_count,
           ROUND(SUM(quantity * price), 2) as revenue
    FROM clean_orders
    GROUP BY region
    ORDER BY revenue DESC
""")
display(result)
print(f"Region count (should be 4): {result.count()}")
Compare raw and clean results

This comparison is the proof that your multi-cloud pipeline works. The same query, the same Fabric notebook engine, but drastically different results.

What changed between raw and clean?

  • The raw query in Step 2 returned 8+ region groups because "north", "North", and "NORTH" were counted separately. The clean query returns exactly 4 regions (East, North, South, West) because Databricks standardized the casing.
  • The raw totals were inflated by duplicate records (e.g., order 1001 and 1003 were the same order). The clean data removed 2 duplicates, dropping the count from 15 to 13 records.
  • The raw revenue calculations were skewed by NULL quantities. The clean data filled those with the median value so every row contributes to the sum.

The data traveled from S3 to Databricks for transformation, back to S3 as clean Parquet, and into Fabric for analysis. No data was copied into Fabric itself. The OneLake shortcuts read directly from S3.

Secret mission

Add a Products Dimension Table

Real data pipelines rarely process a single flat file. In this extension, you add a second dataset to S3, join it with your clean orders in Databricks, and query revenue by product category in Fabric. This tests your ability to extend a working pipeline with cross-table transformations.

Clean Up Your Resources

Clean Up Your Resources

This project uses AWS S3 free tier, Databricks Free Edition (or trial credits), and a Microsoft Fabric trial, so there are no ongoing costs for the sample data you created. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • S3 bucket with raw/ and clean/ data prefixes (orders.csv, products.csv, and Parquet output files).
  • IAM user with S3 read permissions and an access key pair.
  • Databricks notebooks (transform_orders and enrich_orders) and Unity Catalog external location.
  • Fabric Lakehouse with S3 shortcuts (s3_raw, s3_clean, s3_enriched) and notebooks (explore_raw_data, analyze_clean_data, analyze_enriched_data).

Keep everything running

No action needed. Choose this if you plan to continue exploring the multi-cloud pipeline or want to build on top of it.

  • Your S3 bucket stores only a few kilobytes of data, well within the free tier (5 GB storage, 20,000 GET requests, 2,000 PUT requests per month for 12 months on existing accounts).
  • Databricks Free Edition notebooks remain accessible with no charge. If you are on the 14-day trial, monitor your remaining credits in the account console.
  • Your Fabric trial capacity continues counting down whether you use it or not. Non-Power BI items like notebooks and Lakehouses become inactive 7 days after trial expiry. Export or move your work to paid capacity before the trial ends.

Pause - I'll come back to this later

No specific pause actions are needed for this project's resources.

  • S3 data persists at negligible cost within the free tier for existing accounts.
  • Databricks Free Edition has no running compute to stop. Compute only runs while you are actively executing notebook cells.
  • Your Fabric trial capacity continues counting down. Keep an eye on your trial expiry date at app.fabric.microsoft.com in the account manager.

Delete - I don't want to use this again

Remove all project resources and start fresh.

  • Delete the S3 bucket and all its contents by running this command in your terminal:
aws s3 rb s3://[[BUCKET_NAME="your-bucket-name"]] --force
  • Delete the IAM user you created for the Fabric S3 shortcut:
  • Open the AWS Console and navigate to IAM.
  • Select Users from the left sidebar.
  • Click on the IAM user you created for this project.
  • Click Delete and type the user's name to confirm.
  • Delete Databricks resources:
  • In your Databricks workspace, open the Workspace browser.
  • Right-click the transform_orders notebook and select Move to Trash.
  • Right-click the enrich_orders notebook and select Move to Trash.
  • Navigate to Catalog, then External locations. Select the external location you created for your S3 bucket and delete it.
  • Delete Fabric resources:
  • In your Fabric workspace, right-click each notebook (explore_raw_data, analyze_clean_data, analyze_enriched_data) and select Delete.
  • Right-click the Lakehouse and select Delete. This removes the Lakehouse along with all three S3 shortcuts (s3_raw, s3_clean, s3_enriched).

Automated setup resources

If you used the automated setup method for the Databricks external location, Databricks created an IAM role in your AWS account. Deleting the external location in Databricks does not remove that IAM role. Check your AWS IAM console for any roles created by the Databricks setup and remove them if no longer needed.

Nice Work!

Nice Work!

You just built a multi-cloud data pipeline that moves data from AWS S3 through Databricks transformation and back to S3 for analysis in Microsoft Fabric. You proved that three separate cloud platforms can share a single data layer and deliver reliable analytics.

You've learned how to:

  • Connected Microsoft Fabric to AWS S3 using OneLake shortcuts for zero-copy cross-cloud data access, and saw firsthand how raw data quality issues produce wrong analytics results.
  • Built a PySpark transformation notebook in Databricks that standardized text with initcap, parsed five date formats, filled missing values with the median, and deduplicated records using window functions.
  • Proved the pipeline end-to-end by comparing broken raw query results (8+ region groups) against correct clean query results (exactly 4 regions) in Fabric, confirming the S3-to-Databricks-to-S3-to-Fabric pipeline delivers reliable analytics.
  • Secret Mission: Extended the pipeline with a products dimension table, joining datasets in Databricks and querying enriched data by product category in Fabric.

Ready to quiz yourself?