Tag: data warehousing

Each time I compile and curate the Database Weekly newsletter, I find lots of Fabric content from the various sources I watch to compose the newsletter. Since I primarily deal with the Microsoft Data Platform stack, this makes sense. Most of the things I am interested in are related to Microsoft, and as a result, I tend to use sources that also use SQL Server, Power BI, Fabric, and related technologies. I do look for other related data items, but I am heavily MSSQL focused.

Recently, I stumbled on a piece that contains Fabric Alternatives in AWS, GCP, and OCI. It covers some of the options on these cloud platforms at a very high level. A product name and short description, but it shows there are other choices. I found it interesting that Databricks is mentioned, but not Snowflake. I’m not sure why that is, as Databricks is on Azure (and other platforms) as is Snowflake, but perhaps the author doesn’t consider Snowflake a peer? That seems strange.

I don’t have a lot of customers using Fabric, but when I work with SQL Server heavy clients, they always ask my opinion on Fabric. Microsoft has devoted a lot of resources (engineering and marketing) to Fabric, and that has many customers considering Fabric for a data warehouse. However, my view is still that Fabric is an incomplete system and unfinished (from an engineering view) platform. I would still be hesitant to adopt it, especially after some high-profile outages.

AWS has several warehousing options, and I find a number of customers using Databricks or Snowflake as their main warehousing options if they have left on-premises platforms. Both of these seem fairly mature, well understood with lots of documentation, examples in online articles, and plenty of staff that can work on these systems. If I were thinking about a data warehousing system separate from my OLTP SQL Server (on-prem, Azure SQL, MI) database, I’d look at one of these.

Those of you reading this are likely in the Microsoft space, so do you feel the same way? Or have you bought into the Fabric marketing? Microsoft is spending a lot of money there, and even adding a SQL Database to the platform. Microsoft clearly thinks they can compete with these other options (Databricks and Snowflake).

Do you?

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

If you have a data warehouse, what do you think your ratio of reads to writes is on any given day? Do you think 1:1, as in one read for each write? Is it 10:1, with 10 reads for each write? 100:1? Do you track this in any way?

One would think that most of the databases we work on in the transactional world have many more reads than writes. I’d have assumed the ratios might be higher for data warehouses, where we load data that is queried (read) as the primary use case. After all, I expect that there are lots of people querying data that is loaded into this warehouse, with relatively few changes.

I saw a presentation recently of a paper from Amazon that analyzed Redshift workloads (Why TPC is Not Enough). Redshift is the Amazon AWS data warehousing platform that has proven to be very popular in many companies as a cloud warehouse. One interesting piece of analysis is that there are more reads than writes, but barely. There is a lot of insert/update/delete activity, which is different than the TPC benchmarks for warehouses (TPC-H and TPC-DS). Those benchmarks tend to be 75+% reads. Hence the paper and analysis.

However, Redshift isn’t like that in the real world. I had a chance to chat with one of the authors, and with another ETL specialist and they noted that the current pipelines that many companies use aren’t running in batch mode, but run in constant mode, moving data almost constantly. Combining this with relatively few queries from smaller groups of people results in a fairly close ratio of 1:1 for reads to writes. That was quite surprising to me.

Presumably, some of this is because people will run relatively large queries for a report, and then spend time analyzing the data while the ETL/ELT processes continue to move data to the warehouse. Much of the design for storing data in warehouses, especially columnar ones, is with the idea that the data doesn’t change much. Certainly, columnstore indexes perform better when they are being read, not necessarily when they are updated.

I wonder how much of this architecture of constant writes and updates has driven the world towards a Lakehouse architecture where data is written out and then transformed into cleaner versions that are copies, albeit some of them smaller than the original. Often this data is also written to separate delta/parquet files as well, which means new writes usually occur to discrete objects rather than a managed database structure. From a logical thought standpoint, that seems to make more sense to me, even though I still tend to think most of us could use a relational structure for warehousing data.

If you have a real-time warehouse being updated, perhaps you want to measure your ratio of reads to writes and possibly rethink how to manage the system. If you don’t have a specific warehouse, which is most of us, you probably like querying the real-time updates of your transactional data in the same place as it’s stored. Either way, it’s interesting to think about the impact of a real-time workload on the performance your users experience.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

I think the cloud is a perfect place for a data warehouse. In many organizations, I’ve found that a data warehouse system is often the largest SQL Server database, both in size of database and also in terms of resources allocated. These systems often handle many complex queries for business users and are allocated a large number of CPUs as well as lots of RAM. Even then, many ad hoc BI tools or lots of “what-if” queries can bring the system to its knees, often causing lots of stress for database administrators during the periods of time when the system is in heavy use.

Fortunately, many of these systems aren’t in use all the time. Often these are systems used by financial departments to “close the books” at month, quarter, or year end. It’s at these times when lots of resources are needed. Outside of these times, the data warehouse might be one of the least used systems, which makes it a perfect choice for a cloud, scale on demand, environment. Scale up when needed, down when not, limit your costs to the resources you need, when you need them.

Microsoft has increased the capabilities of the Azure SQL Data Warehouse quite a few times across the last few years. I was thrilled to see ASDW separate out storage from compute, allowing customers to scale up the query, or compute, nodes independently of the storage used. Changes last year improved the ability to move data around between compute nodes as well as increased the number of concurrent queries.

These improvements are perfect for data warehouses, and if you are looking to build a more responsive SQL Server based warehouse, you ought to take a look at ASDW. More and more customers are finding it valuable and cost effective in their businesses. What seemed to once be a niche idea has grown into a business that quite a few customers are using, with the demand growing.

What’s more, the move to Big Data Clusters in SQL Server 2019 seems to have adapting some of this technology to the regular SQL Server product many of us use. These will separate out the storage from compute, something that should help many of us scale our systems to meet the demand of our workloads. I haven’t tried a big data cluster yet, but I’m looking forward to seeing how well one works and if it truly scales SQL Server further than I would have dreamed.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.