Voice of the DBA

Author: way0utwest

  • The Load of Real Time Data Warehouses

    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.

  • SQL Saturday Boston 2024 Slides

    Thanks to everyone that came to my talks. Slides are below.

    If you have questions, please reach out.

  • Small Data SF 2024

    I can’t remember how I heard about Small Data SF 2024, but it caught my eye. The mix of sessions had me interested in going, especially with Mother Duck and Duckdb being the main sponsors. I’ve run into DuckDb a few times in the last couple of months, so I was interested in what I could learn about small data and a different group of people than I normally see at events.

    When a customer visit cancelled, I requested the learning and development (L&D) time and budget and got it approved. I booked flights and a hotel and headed to San Francisco.

    The structure of the conference was interesting to me. I’ve been lucky to get to a few smaller conferences in the past (100-200ppl) and I like them. SQL Bits, PASS Data Community Summit, and other events are nice, but I tend to like small events.

    This conference had the tag line of Think small, develop locally, ship joyfully. There were other tags, and you can read their manifesto, but essentially this conference looked at the idea that lots of work with data (OLTP or analytics) can be done on small sets, with local databases or local data.

    Day 1

    Day 1 started late, at 12p with lunch. I liked that, though I took advantage of the late start to sleep in and get a late breakfast, so I really wandered around and chatted with people with a coffee. Food was nice, as it always is in San Fran. Lots of dietary choices, and mixes of stuff. The event was in a co-working facility, so there were always snacks around (chips, nuts, fruit, etc.).

    Day 1 was two workshops. Each was 3 hours with a break between them. Essentially these were vendor sessions for hands on work with a product. There was a happy hour after, but I skipped it.

    My first workshop was from Mother Duck, a vendor building on DuckDB. The workshop was based on this github repo, and showed how to use dbt to move some data around. It was hands-on, and things worked well for me, but this was a mix of CLI work, python, database work, and more. Some people definitely struggled with the workshop. I found this interesting, and I learned a few things. I’m definitely interested in doing some DuckDB work to analyze data in a way that is different (and simpler) than Snowflake or Fabric. I could see people doing this.

    The second workshop was from Outerbase, which essentially is a way to work with multiple databases on the web. It’s a light Object-Explorer/Query tool in some ways, but they’re also trying to do some AI work to help stub out a web interface for your database. They had us try to build some methods and web code that we could paste into a React or Angular (or others) framework. This one was OK, but I am not sure this is a great use of AI. I was hoping for a bit more.

    Day 2

    Day 2 was all day, from 830-530. I arrived to find a lot of people getting breakfast. Again, hot food, cold food, GF, etc. Lots of choices. One cool thing was a coffee bar where you could get baristas to make a nice drink, but you could also get a Mother Duck mug for your drink. I have too many mugs, but I liked this one, so I got one.

    2024-09_0189

    This was a one track conference, which I also like. Everyone gets a shared experience, we have common things to talk about, and things change often. I also don’t have to go find rooms. In this case, one large rooms with a low stage.

    2024-09_0191

    New talks every 20 minutes, on a variety of topics. The agenda was wide and varied. I think a few talks were meh, but most were interesting. I’ve got some editorials coming, but the first talk on Big Data was great, as was the second one on different tooling we might use for both development and analysis of smaller sets of data.

    Note, small data doesn’t mean kb or less. It notes that many queries can be run on GB of data on a laptop, and with today’s network and laptop capabilities, this can make sense. There was also some limited domain views of the ways you might shard your data to lots of databases, and you might do more local work, not central db connections. That makes sense in some cases, but not all.

    I also think some of the speakers (quite a few startup people) minimize or don’t think about the true scale problems when workloads grow, nor about the hassles of pulling all this data together and synching it. In any case, I think their ideas work for some problem domains.

    There were a few panels, as well as a presentation of a paper from Amazon. One super interesting thing was Redshift shows like a 60:40 split of reads to writes. That seems crazy. However, an exec from FiveTran talked about that matching their experience where many data warehouses are running constant updates from OLTP systems, something that their customers sometimes don’t realize. He wasn’t sure if this was a good idea as well, but it’s been good for their business.

    As seems to be the case, there was a satirical talk on BI tools and how they don’t always help. An analyst for one of the political campaigns gave a funny humorous look at the world of vendors and customers.

    After the last talk, there was a short happy hour, where I had the chance to chat with a few people. Silicon Valley is a strange place, full of people working in startups, formerly from startups, or wanting to start one. Everyone has a good idea, which I think is true, and so many of them want to chat about their thing or your thing.

    As you might expect, a lot of people at AI-focused or thinking AI. It’s neat to hear their experiences and what they think. Certainly I saw some neat demos or using small models (again small data) and feeding a user query into the model along with some data from a database or a flat file. That was interesting and something I think could be useful in different ways that are focused. I expect more and more people to get comfortable with AI based work.

    Ultimately, I had a nice, refreshing two days that got me thinking about data differently and how there are different ways to approach problems and solutions. Perhaps one of the neater things I saw was PySheets, Python in spreadsheets. Just don’t try it in Chrome, and make sure to use the little A* button to test the AI.

  • Where’s the Printer Friendly Option?

    When is the last time you read an article/blog/etc. on the Internet and saw a button for a print friendly version? That used to be something on every page, and one people often shared on social media (or email) because it didn’t have all the advertisements in it. I remember having to help code this feature on SQL Server Central when we started as plenty of people wanted to print articles out and read them later. That desire led to Andy brainstorming that we should release The Best of books each year.

    I was reading about how the Internet has changed many things in our lives and I thought about these links. I searched a number of places I visit often and there are no more printer links. I’m guessing with mobile devices and various save services, most people have gotten used to using digital technology to consume information?

    I still print things at times, though fairly rarely. I don’t often consume anything on physical media anymore, including books. I’ve tried to read a few times on paper, but it’s inconvenient to me now. I have to remember to pack something or carry it, I need a light often, it just doesn’t work as well.

    I rarely see paper in use in meetings anymore at all. Whether I’m at a Redgate office or a customer site, most people seem to have monitors, projectors, sharing apps, and more so paper is just rarely used.

    At the same time, it’s not completely out of date. It works well and it’s simple. I see it used for announcements, for small handouts, signs, and menus. Quite a few of us don’t like the digital menus from QR codes and it seems most restaurants I’ve visited still create physical menus. Signs and announcements are the places I still see paper in use regularly. I will say I’ve seen a few people (a very few) using e-ink devices, which is something I’m tempted to use. I do find writing helps me remember things better.

    The world continues to create more and more data, while finding more numerous and novel ways to disseminate it. For much of the time, the paperless office exists, and I see less and less use of paper for distributing information, but it hasn’t completely disappeared. Except, perhaps, from the web.

    Steve Jones

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

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