Embrace Delta Lakes and reduce the SQL Server compute resources contention

-


Data management tools are evolving at a great speed, and this can be overwhelming.

Data volumes and variety evolve and grow as well.


Data Engineers are required to transform those waterfalls of data into business insights.

The data is arriving from a vast range of sources, like social-media networks, 3rd party partners or internal micro services. If you are experienced SQL Server DBA, you know how versatile the product is. It is very tempting and feels correct to use the tool that you know the best.

We can use SQL Server for almost any data management task. We can use SQL Server to watch over the storage for a new unprocessed files.

We can load the raw data into SQL Server Staging Area SQL Server database.

We can efficiently clean, enrich and aggregate the data using highly expensive relational database resources (even if you are not using SQL Database in the cloud, every Enterprise edition core still cost about $7K)

SQL Server Relational engine high cost echoes the product complexity and extraordinary features that help to manage data consistency and integrity under high transactional load. Those expensive resources should be mainly dedicated to OLTP workloads as well as to condensed, well aggregated and focused Data Marts.


I want to suggest adding to your DBA toolbelt an emerging class of data management technologies called Data Lakes.

Data Lakes are designed to hold, process and store data and they are very cost effective. Data Lakes usually use affordable hardware and data sets do not need to be indexed. Data can be stored either in raw format (Data scientists in many cases will prefer to analyze an original data) as well as in cleaned, enriched, and aggregated formats.

Aggregated data can be loaded into your Data Warehouse, which can be our beloved SQL Server, serving Data Analysts queries and reports. Aggregated data sets are smaller and you will not need to dramatically increase the size of data warehouse every few month.


Delta Lake https://delta.io/ is an open-source project that enables building data warehouses on a top of storage systems, it's a storage layer from Spark which runs on top of an Azure Data Lake, ensuring data integrity with ACID transactions. Delta lake technology leverages efficient Apache Parquet files compression, allows to specify and enforce schema to make sure datatypes are correct and all required columns are present. Delta Lake enables table schema changes, data versioning and full audit trail of the changes.


In Azure cloud, we have the following services that integrate with Delta Lake


1. Azure Databricks – has integrated Delta Lake into their managed services, it allows you to use SQL, Python, R or Scala to query the Delta Lake

2. Azure Synapse Analytics – serverless data pools utilize Spark to read and write data from your Delta Lake in PySpark, .NET and Scala

3. Azure Data Factory – supports Delta Lake connector in Copy activity, in Data Flow and Databricks Notebook activities

4. Azure HDInsight: in Spark and Hive you can download and install Delta Lake.

5. Power BI: you can query Delta Lake tables directly.

Do not fall back on known methods, take advantage of the low storage and low processing costs.

Make your company data agile and allow your users to access original data immediately as soon as it arrives to Data Lake, way before your aggregations are ready.

Keep your Sql Server compute resources dedicated to end user queries.


As Jeff Bezos says, what is dangerous is not to evolve.


Yours

Maria

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