When should you use Azure Databricks?

-

 


Once upon a time, Sql Server was our central tool for data management, for both OLTP (online transactions processing) or OLAP(online analytical processing) database systems.

We have used Sql Server Agent Jobs to pull the data from FTP or any other source. We have used Sql Server stored procedures to pull the data into the Staging database. We have used Sql Server stored procedures to enrich and aggregate the data. And we have used Sql Server as a data serving layer. 

These days we need to consider utilizing various cloud services. Attempts to lift and shift existing systems into the cloud often end up being quite expensive if we tend to keep Sql Server taking care of all data pipeline stages. 

There are multiple great services in the Azure cloud and Microsoft tends to build each product with multiple features allowing it to take care of multiple pipeline stages. This does not mean that we need to go back to the monolith architecture, let's find out where each service fits.

Data Factory can pull the data from any source, orchestrate the flow, and enrich and aggregate the data.
DataBricks can pull the data from any source, orchestrate the flow, enrich and aggregate the data, serve the data from data lakehouse and visualize it using dashboards.
Synapse Analytics with integrated pipelines can pull the data from any source, orchestrate the flow, enrich and aggregate the data and serve the data. 
PowerBI can pull the data from any source, enrich and aggregate the data and serve and visualize the data. 
Sql Managed instance can pull the data from any source, enrich and aggregate the data and serve the data. 

In the above image, we can see where Microsoft positions each of the abovementioned cloud services and how Azure services can be used as a lego piece to build the data warehouse pipeline architecture.

Azure Data Factory should be used as an ingestion and orchestration service. Load data from external or internal sources and store it in the Azure Data Lake Storage.

Azure Data Lake should be housing data of all types, except data used for online transaction processing.

Azure Databricks's purpose is to prepare, transform, enrich and train datasets.

Azure Synapse's purpose is to serve clean aggregated data from Azure Data Lake Storage.

Power BI will be a visualization layer, reading data off the Azure Data Lake storage or Azure Synapse Analytics and generating analytical reports and dashboards.

Sql Managed Instance is not in the above image because its too expensive to be used in data warehousing architecture. Sql Server's purpose is to take care of online transaction processing, store and serve relational data and support high amounts of inserts, updates and deletes while making sure the data is consistent. This is a very difficult zone, therefore, this service price is quite high.

Can be great to hear your opinion or observations in the comments...

Comments

Post a Comment

Popular posts from this blog

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

-
Image
After so many years of writing SQL queries, I cannot believe I never came across a QUALIFY query clause. It feels like discovering a treasure in the depths of the database ocean. Even ChatGPT was not aware of it! QUALIFY query clause was invented by Teradata and is not a part of SQL Standart but apparently, multiple database vendors are supporting it, for instance, Snowflake and Databricks. QUALIFY clause solves the challenge when applying a window function as a query filter. SQL Standart and most database vendors will not allow to run of a window function as a part of a WHERE clause because window functions get evaluated after the HAVING clause. We typically create a CTE or subquery adding a window function to be able to filter on a it at a later query stage. Here is an example of how you can use QUALIFY to piece-of-cake filter on the window function.  I will use the Snowflake database, as an example. Let us consider that we have a list of queries and we need to find the last query ex
Read more

Look back and realize how far you came

-
Image
It appeared out of nowhere while I was having a strong argument with the ton of dust that had taken over my computer room. My former favourite book, "Advanced Transact-SQL for SQL server 2000" brought an upset look to my face, the one I usually get from our NetApp admin. He usually gives me such a look when I ask him for more disk space. At the time of SQL 2000 there were far less good SQL books available than today and amazing SQL programming tricks were living on Itzik’s book pages; and, similar to the Rubeus Hagrid’s “The Monster Book of Monsters” it attacked anyone who attempted to open it, unleashing wild and exciting brilliant ideas. Nowadays there are a lot of SQL books but not all of them are worth reading. Some of them should be strongly avoided and if you touch them you should immediately wash your hands and have a drink or three. A few month ago I was asked to technically review some new SQL Server book. Reading their missleading claims of how the optim
Read more

The Greatest Reasons to use or not to use a Centralized Data Access Architecture

-
Image
When developing a modern Data Platform Layer, one of the main decisions is whether to opt for centralized or decentralized data access architecture.  There is no “one-fits-all” solution, both have advantages and disadvantages. A Centralized Data Access Architecture would usually mean duplicating data from the operational layer into the analytical layer and applying various transformations to data to support and speed up data analytics. Operational online transaction processing Layer , where all microservices and their operational databases are located. Analytical Data Layer , where we would have data lakes that support data Scientists' work and a data warehouse, that supports Business Intelligence. Transformations, ETL or ELT data pipelines , which are moving data from the operational layer into the analytical layer. I f we opt for a Centralized Data Access architecture, what would be the benefits and the drawbacks? CONSISTENCY : consolidating data into a central location can
Read more