I Built an End-to-End E-Commerce Analytics Platform Using Azure Synapse

Building a data engineering project is one thing.

Building a pipeline where data actually moves from raw files → data lake → transformation → warehouse → analytical views → Power BI is a completely different experience.

For this project, I wanted to understand what an end-to-end analytics platform looks like using Azure Synapse Analytics, rather than learning each Azure service in isolation.

The result was an E-Commerce Sales Analytics Platform built around ADLS Gen2, Synapse Pipelines, Serverless SQL, Dedicated SQL Pool, and Power BI.

The Goal

The objective was straightforward:

Take e-commerce sales data from a raw CSV file and turn it into something that can support business analytics and reporting.

But I wanted the project to cover the complete lifecycle.

That meant working with:

• Data Lake storage
• Data ingestion
• SQL-based exploration
• Data curation
• Data warehousing
• Dimensional modeling
• MPP distribution strategies
• Analytical views
• Business intelligence

The final architecture followed a simple pattern:

ADLS Gen2 → Synapse Pipelines → Serverless SQL → Dedicated SQL Pool → Power BI

This became the foundation of the project.

The Architecture

At a high level, the data flows through several layers.

Press enter or click to view image in full size

The important part for me was understanding why each layer existed, rather than simply connecting Azure services together.

1. Starting With ADLS Gen2

The first layer was Azure Data Lake Storage Gen2.

I structured the lake into separate areas:

ecommerce/
│
├── incoming/
│   └── sales.csv
│
├── raw/
│   └── sales.csv
│
├── curated/
│   └── sales/*.parquet
│
└── warehouse/

Each area had a different purpose.

Incoming was the original landing location.

Raw contained the data after pipeline ingestion.

Curated contained query-ready Parquet data.

Warehouse was reserved for warehouse-oriented data.

This separation helped me understand one of the fundamental ideas in data engineering:

Don’t treat storage as one big bucket.

Different stages of data should have clearly defined purposes.

2. Ingesting the Data With Synapse Pipelines

The next step was getting the source data into the raw layer.

I created a Synapse pipeline that used:

Get Metadata → Copy Activity

The pipeline checked the incoming file and then copied:

incoming/sales.csv
        ↓
raw/sales.csv

The Synapse Workspace Managed Identity was given the appropriate access to ADLS so that the pipeline could interact with the storage account without embedding storage credentials directly into the pipeline.

This was also my first practical exposure to using Azure Managed Identity + RBAC for data access.

3. Exploring the Data With Serverless SQL

Once the raw data was available in ADLS, I didn’t immediately load everything into a warehouse.

Instead, I used Synapse Serverless SQL to work directly against files in the data lake.

This was one of the most interesting parts of the project.

With OPENROWSET, I could query external data without first loading it into a traditional relational table.

For example, the curated Parquet files could be queried directly from the lake.

SELECT TOP 100 *
FROM OPENROWSET
(
    BULK 'curated/sales/*.parquet',
    DATA_SOURCE = 'ECommerceData',
    FORMAT = 'PARQUET'
) AS sales;

Serverless SQL therefore acted as the lake analytics layer, allowing me to explore and work with the data before bringing it into the warehouse.

4. Creating Curated Parquet Data With CETAS

I then explored CETAS — CREATE EXTERNAL TABLE AS SELECT.

The idea behind CETAS is simple:

Take the result of a SQL query and materialize it into external storage.

In this project, that meant producing curated Parquet data in ADLS.

Raw Data
   ↓
Serverless SQL
   ↓
CETAS
   ↓
Curated Parquet

Serverless SQL was able to successfully read the generated Parquet files, making this a useful demonstration of SQL-based lake curation.

5. Moving Into a Dedicated SQL Pool

After working with the data lake, I moved into the warehouse layer.

The Dedicated SQL Pool became the analytical warehouse:

DW_ECOMMERCE

This is where the project started looking more like a traditional enterprise analytics architecture.

Instead of querying files directly, the data would be loaded, transformed, modeled, and organized for analytical workloads.

6. The Problem With the Source CSV

This is where the project became much more realistic.

The source CSV wasn’t perfectly aligned with the structure I wanted in the warehouse.

For example, the source data had OrderID in a different column position from the target warehouse table.

The dates were also coming in as strings.

And rather than blindly trusting the source TotalProfit, I wanted profit to be calculated transparently:

Profit = Total Revenue − Total Cost

So instead of loading the CSV directly into strongly typed warehouse columns, I introduced a raw staging layer.

7. Raw Staging Before Transformation

The raw staging table stored the incoming CSV values largely as strings.

CSV
 ↓
stg_Sales_Raw
 ↓
Type Conversion
 ↓
stg_Sales

This separated ingestion from transformation.

The raw table could safely receive the source data, after which SQL transformations converted values into their appropriate types.

For example:

TRY_CAST(OrderID AS BIGINT)

TRY_CONVERT(DATE, OrderDate, 101)TRY_CAST(UnitPrice AS DECIMAL(18,2))

Profit was calculated during this transformation:

TotalRevenue - TotalCost

This approach made the ingestion process more resilient to source-format inconsistencies.

8. Building a Dimensional Model

Once the staging data was clean, I modeled it into a simple analytical warehouse.

The core model consisted of:

              DimDate
                 |
                 |
DimProduct — FactSales — Region

The major tables were:

• DimProduct
• DimDate
• FactSales

The fact table contained measures such as:

• Units Sold
• Revenue
• Cost
• Profit

while the dimensions provided descriptive context for analysis.

This gave me practical experience with a star-schema style warehouse model.

9. Understanding Distribution in Synapse

One of the concepts I specifically wanted to understand was how data distribution works in an MPP warehouse.

I used different strategies for different types of tables.

ROUND_ROBIN

Used for staging tables because it provides a simple ingestion strategy.

REPLICATE

Used for smaller dimension tables such as DimProduct and DimDate.

HASH

Used for the larger FactSales table.

The idea is that distribution should be chosen based on the workload and how tables are commonly joined.

Using the wrong distribution strategy can lead to unnecessary data movement between compute nodes.

10. Loading the Warehouse With COPY INTO

For the final warehouse loading process, I used COPY INTO.

The process was:

ADLS raw/sales.csv
        ↓
COPY INTO
        ↓
stg_Sales_Raw
        ↓
Transform
        ↓
stg_Sales
        ↓
Dimensions + Fact

COPY INTO became the practical bulk-loading mechanism for the Dedicated SQL Pool.

I also explored how it compares with the traditional PolyBase + CTAS approach.

The final implementation used COPY INTO, while PolyBase was treated as an important conceptual MPP loading pattern.

11. Creating Analytical Views

Once the fact and dimension tables were populated, I created views specifically for reporting.

The three main analytical views were:

Revenue by Region

Region
Revenue
Profit
Units Sold

Revenue by Product

Product
Revenue
Profit
Units Sold

Monthly Revenue

Year
Month
Revenue
Profit

These views created a clean boundary between the warehouse model and the reporting layer.

12. Connecting Power BI

Finally, the data reached the business intelligence layer.

I connected Power BI Desktop to the Dedicated SQL Pool and exposed the analytical views.

The report consisted of three main pages:

Revenue by Region

A regional revenue comparison.

Revenue by Product

A product-level revenue analysis.

Monthly Trends

A time-based view of revenue and profit.

This completed the entire journey:

Raw File
   ↓
Data Lake
   ↓
Pipeline
   ↓
SQL Processing
   ↓
Warehouse
   ↓
Analytical Views
   ↓
Power BI

The Part That Didn’t Go According to Plan

One of the biggest lessons from this project came from something that didn’t work.

Initially, I explored loading the CETAS-generated Parquet files directly into the Dedicated SQL Pool.

Serverless SQL could read the Parquet successfully.

But the Dedicated SQL Pool encountered a Parquet reader compatibility error.

Rather than forcing the architecture to work in a way that wasn’t reliable in the environment, I changed the final warehouse loading path to use the original CSV with COPY INTO.

This still allowed the project to demonstrate the important concepts while keeping the final implementation reproducible.

That was probably one of the most valuable lessons from the project:

A technically interesting architecture isn’t useful if you can’t reliably make it work.

Another Real-World Data Problem

The source CSV also exposed another issue.

The column ordering didn’t match the warehouse schema.

A direct typed load could therefore try to place a Region value into an OrderID column.

The solution was to introduce the raw staging table and perform explicit transformations afterward.

This reinforced an important data engineering principle:

Separate ingestion from transformation whenever the source data isn’t completely trustworthy.

Why I Removed Spark

The original learning plan included Spark-related components.

But I ultimately decided not to use Spark, Spark Pools, Spark Notebooks, Delta Lake, or Lake Database in the final implementation.

The reason wasn’t that Spark wasn’t useful.

The goal of this project could already be achieved using Synapse Pipelines, Serverless SQL, and Dedicated SQL Pool, and removing Spark-related dependencies made the project easier to reproduce and complete reliably.

So the final architecture deliberately became Spark-free.

What This Project Actually Taught Me

Before this project, it was easy to think about data engineering as a collection of individual tools.

ADLS.

SQL.

Pipelines.

Data warehouse.

Power BI.

But building the complete system changed that perspective.

The real value comes from understanding how these components work together.

A file arriving in storage is not yet analytics.

It needs to be:

landed → ingested → explored → transformed → modeled → loaded → queried → visualized

And every stage introduces its own engineering decisions.

The Final Architecture

The complete platform can be summarized as:

SOURCE
                  sales.csv
                      |
                      ↓
                 ADLS Gen2
                      |
                      ↓
              Synapse Pipeline
                      |
                      ↓
                 Raw Layer
                      |
                      ↓
              Serverless SQL
             /              \
        OPENROWSET          CETAS
                              |
                              ↓
                       Curated Parquet
                              |
                              ↓
                    Dedicated SQL Pool
                              |
                 ┌────────────┼────────────┐
                 ↓            ↓            ↓
            DimProduct     DimDate      FactSales
                              |
                              ↓
                    Analytical Views
                              |
                              ↓
                           Power BI

This represents the final lake-to-warehouse-to-BI architecture implemented in the project.

Final Thoughts

The biggest takeaway from this project wasn’t learning another Azure service.

It was understanding the data lifecycle.

I started with a simple CSV file.

By the end, that file had gone through:

ADLS Gen2 → Synapse Pipelines → Serverless SQL → Curated Data → Dedicated SQL Pool → Dimensional Model → Analytical Views → Power BI

And along the way, I had to deal with authentication, RBAC, data types, source inconsistencies, staging, distribution strategies, loading methods, SQL transformations, and even technology choices that didn’t work as expected.

That is what made this project valuable to me.

It wasn’t just:

“I built a dashboard.”

It was:

“I built the data platform underneath the dashboard.”

And that is the part of data engineering I wanted to understand.