Why Enterprises Are Moving from Azure Data Factory + SQL Server to Databricks?

Ridhi Arora — Sat, 08 Aug 2026 07:24:12 +0000

For years, a combination of Azure Data Factory (ADF), SQL Server Management Studio (SSMS), and SQL-based ETL pipelines has been the backbone of many enterprise data platforms. It was reliable, familiar, and sufficient for handling structured data workloads.

As organizations adopt cloud-native architectures, AI, and real-time analytics, many are modernizing their data platforms. This article explains why that shift is happening.

Traditional Architecture

Typical Stack

Azure Data Factory for orchestration
SQL Server & SSMS for transformations
Stored Procedures for business logic
Data Warehouse for reporting

This architecture works well for moderate volumes and structured data.

The Challenges

1. Business Logic Becomes Hard to Maintain

As projects mature:

Hundreds of stored procedures accumulate.
Dependencies become difficult to trace.
Small changes can impact multiple downstream processes.
Debugging becomes slower.

2. Performance Bottlenecks

When data grows from millions to billions of records:

Long-running SQL transformations
Longer pipeline execution
Higher infrastructure costs
Reduced scalability

The gap widens sharply once volumes cross the hundreds-of-millions mark — exactly where distributed compute starts to matter most.

3. Modern Data Doesn't Look Like Tables

Organizations now process:

JSON
CSV
Parquet
Streaming data
IoT events
Semi-structured data

Traditional SQL-centric systems struggle with this diversity.

Why Databricks?

1. Massive Scalability

Apache Spark distributes workloads across clusters, enabling efficient processing of terabytes and petabytes of data.

2. Better Performance

Distributed execution significantly speeds up joins, aggregations, and transformations.

3. Multi-language Support

Engineers can work with:

SQL
Python
Scala
R

4. Unified Platform

Databricks combines:

Data Engineering
Analytics
Data Science
Machine Learning
AI

5. Delta Lake

Benefits include:

ACID Transactions
Time Travel
Schema Enforcement
Schema Evolution

Developer Workflow

Migration Challenges

Learning Spark
Rewriting complex SQL logic
Optimizing cluster costs
Setting up CI/CD
Data governance

Migration is as much about people and processes as technology.

Key Takeaways

Traditional Platform	Modern Platform
SQL Stored Procedures	PySpark
SSMS	Databricks Notebooks
Scale Up	Scale Out
Relational Data	Structured + Semi-Structured
Separate Tools	Unified Analytics

Final Thoughts

Databricks is not replacing Azure Data Factory or SQL Server because they are obsolete.

ADF remains an excellent orchestration service, and SQL Server continues to be valuable for many workloads.

The shift is happening because modern enterprises require:

Larger-scale processing
Flexible compute
Collaborative development
AI-ready platforms
Faster delivery of analytics

For data engineers, this transition is an opportunity to rethink how modern data platforms are built.

Note: This article intentionally uses generalized enterprise patterns and does not describe any confidential implementation details from a specific organization.

Unveiling the Power of Window and Ranking Functions in SQL

Ridhi Arora — Wed, 03 Jan 2024 18:27:00 +0000

In the realm of SQL, where data manipulation is an art form, two powerful techniques stand out for their ability to unravel valuable insights from datasets: Window Functions and Ranking Functions. Let's dive into the intricacies of these functions and explore how they can elevate your data analysis game.

1. The Essence of Window Functions

1.1 Understanding Windows

Window functions operate within a specified range of rows related to the current row in a result set. Think of a window as a focused view of your data, allowing you to perform calculations or aggregations on a subset of rows.

Syntax of Window Functions

SELECT column1, column2,
window_function(column3) OVER (
[PARTITION BY partition_column1, ...]
                        ORDER BY order_column1 [ASC | DESC], ...
                        ROWS BETWEEN N PRECEDING AND M FOLLOWING
             ) AS result_column
FROM your_table;

window_function: The function you're applying to column3.
PARTITION BY: Divides the result set into partitions for independent calculations.
ORDER BY: Specifies the order of rows within the window.
ROWS BETWEEN: Defines the range of rows for calculations.

1.2 Types of Window Functions

Aggregation Functions: SUM(), AVG(), MIN(), MAX() within a window.
Ranking Functions: RANK(), DENSE_RANK(), ROW_NUMBER() for assigning ranks.
Lead and Lag Functions: LEAD(), LAG() for accessing values in subsequent or preceding rows.
Window Frame Functions: Customizing the range of rows for calculations.
Percentile Functions: Analysing data distribution in percentiles.
First and Last Value Functions: Retrieving the first or last value within a window.

2. Understanding Ranking Functions

2.1 What is a Ranking Function?

Ranking functions are a subset of window functions that assign a rank to each row based on specified criteria. These functions are invaluable when you need to prioritize, identify top or bottom performers, analyse performance within groups, and detect trends or outliers.

2.2 Types of Ranking Functions

2.2.1 RANK()

Assigns a unique rank to each row based on the specified order.
Syntax: RANK() OVER (ORDER BY column_name [ASC | DESC]);
Example:

SELECT  product_name, sales,  RANK() OVER (ORDER BY sales DESC) AS sales_rank FROM sales_data;

This query assigns a rank to each product based on sales, ordering them from the highest to the lowest.

2.2.2 DENSE_RANK():

Similar to RANK(), but without skipping ranks for tied values.
Syntax: DENSE_RANK() OVER (ORDER BY column_name [ASC | DESC]);
Example:

SELECT product_name, sales, DENSE_RANK() OVER (ORDER BY sales DESC) AS sales_dense_rank FROM sales_data;

This query assigns a dense rank to each product based on sales, without skipping ranks for tied values.

2.2.3 ROW_NUMBER():

Assigns a unique sequential number to each row within the window.
Syntax: ROW_NUMBER() OVER (ORDER BY column_name [ASC | DESC]);
Example:

SELECT  product_name, sales, ROW_NUMBER() OVER (ORDER BY sales DESC) AS sales_row_number FROM sales_data;

This query assigns a unique row number to each product based on sales, regardless of ties.

3. Unleashing the Power of Ranking Functions

3.1 Customized Data Prioritization

Ranking functions provide a means to prioritize data based on specific criteria. Whether you're dealing with sales numbers, exam scores, or any other metric, these functions offer flexibility in sorting order and partitioning.

Example:

SELECT product_name, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM sales_data;

This query assigns a rank to each product based on sales, ordering them from the highest to the lowest.

3.2 Identifying Top and Bottom Performers

Ranking functions excel in pinpointing top and bottom performers within a dataset. By assigning ranks to rows based on performance metrics, you can easily spot the highest and lowest values.
Example:

SELECT employee_name, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM employee_sales;

Here, each employee gets a rank based on their sales performance, making it clear who's at the top.

3.3 Analysing Performance within Groups

The PARTITION BY clause in ranking functions is a game-changer for group-based analysis. This feature allows you to evaluate performance within different subsets of data, offering valuable insights into how groups compare.
Example:

SELECT department, employee_name, sales, RANK() OVER (PARTITION BY department ORDER BY sales DESC) AS sales_rank
FROM employee_sales;

By partitioning the data by department, you can discern the top performer in each department.

3.4 Detecting Trends and Outliers

Ranking functions prove invaluable in detecting trends and outliers within ordered data. By examining the ranking of data points over time or across different dimensions, you gain a clearer picture of significant changes.
Example:

SELECT date, stock_price, RANK() OVER (ORDER BY date) AS price_rank
FROM stock_prices;

Analysing the ranking of stock prices over time can reveal trends or outlier events.

4. Conclusion

In the dynamic landscape of SQL, mastering window and ranking functions is akin to unlocking a treasure trove of analytical capabilities. These tools empower you to delve deeper into your data, discover patterns, and derive meaningful insights. As you embark on your SQL journey, remember that the combination of window and ranking functions opens doors to a realm where data becomes a narrative waiting to be told

DEV Community: Ridhi Arora

Why Enterprises Are Moving from Azure Data Factory + SQL Server to Databricks?

Traditional Architecture

Typical Stack

The Challenges

1. Business Logic Becomes Hard to Maintain

2. Performance Bottlenecks

3. Modern Data Doesn't Look Like Tables

Why Databricks?

1. Massive Scalability

2. Better Performance

3. Multi-language Support

4. Unified Platform

5. Delta Lake

Developer Workflow

Migration Challenges

Key Takeaways

Final Thoughts

Unveiling the Power of Window and Ranking Functions in SQL

1. The Essence of Window Functions

1.1 Understanding Windows

1.2 Types of Window Functions

2. Understanding Ranking Functions

2.1 What is a Ranking Function?

2.2 Types of Ranking Functions

2.2.1 RANK()

2.2.2 DENSE_RANK():

2.2.3 ROW_NUMBER():

3. Unleashing the Power of Ranking Functions

3.1 Customized Data Prioritization

3.2 Identifying Top and Bottom Performers

3.3 Analysing Performance within Groups

3.4 Detecting Trends and Outliers

4. Conclusion