Strategies to Edit Production Data

Speaker 1: Welcome back after the break. Please find your seats. And we'll continue with a topic that is close to many of us who have actually run projects in production. I'm sure many of us know the feeling of having incorrect data in a production database and just fixing it by accessing the database via Django or via an database shell. and running a Django or SQL command and before hitting enter having that feeling of I could Remove, drop, or delete all the data in there right now. Oh god, I hope I'm okay. Julie is an engineering manager at Spring and is going to tell us how we should deal with those situations.

Speaker 1: Thank you

Speaker 2: Hi everyone, my name is Julie Q and I spend a lot of time working with databases Most of you have probably run across the issue that I'm going to talk about today, which is that odds sometime we all need to edit data in production And there's a lot of ways that we can go about doing this editing, some of which are going to be better than others. So today I'm here to share with you the evolution of processes that I've helped develop at my company Hopefully the strategies that I'm about to share will make editing production data safer at your organization too. A little bit about me. I'm an engineering lead at Spring. For those of you who haven't heard of Spring before, Spring is a fashion e-commerce

Speaker 2: marketplace that integrates with thousands of different brands. At Spring, I lead the catalog team, which is one of the engineering teams that builds out our product catalog. So this means that I spend a lot of time thinking about how data gets into our systems And over the last few years, more often than I would like to admit, I found myself behind the SQL prompt needing to make edits to production data. And after being in that situation a couple of times, I started to ask myself why. We're all told that editing data in our read-write database is bad practice. So why do we all do it anyways? Well for me, the first reason is often because the internal tools that I need to edit that data is just not available.

Speaker 2: I would have used a better method, but it doesn't exist. The second reason is that I ran into an edge case So sometimes we actually did take the time to build out a user interface, but it doesn't allow us to do all the things that we want it to do or need it to do. And lastly, sometimes you just have to make a time-sensitive change. And connecting to a remote database and making an edit can oftentimes just be the fastest and the easiest solution. It's really easy to get behind that SQL prompt and execute a query. And usually if we think about it, there's not really that much of a problem with it either For example,

Speaker 2: let's say I want to run this query. So I want to update the name of the first product in our products table to mean name Julie's product. And usually when I need to do this, uh before I would do that, I would have a coworker look over my shoulder and double check my query. But let's just say this time, it's Friday afternoon, and someone on the marketing team comes up to me and is like, Julie. I need you to make this change. The we promised the brands we would get this in before the weekend and they're one of our highest uh performing brands and if you don't do it, they're gonna pull themselves off the spring platform So of course I go ahead and I write this query and when I'm done I realize that everyone has already left for the weekend

Speaker 2: So at this point, um I could, you know, send a Slack message to someone on the engineering team, uh reach out to them, ask them to spot check me But then again I'm thinking, I've worked at Spring for two years. I run the catalog team. I've done this a million times before. What is the worst thing that's gonna happen? So uh I go ahead and I run the query, um I go to the kitchen, get some water, and uh come back to my desk, look down at my laptop. And I realized this is what I actually ran Um, for those of you who aren't familiar with SQL, the rest of the audience is laughing because

Speaker 2: They realized that I just updated every single product in our table to be named Julie's product So, um, I'm sure that some of us are laughing or maybe even cringing because we've actually had disaster stories similar to the one that I just described. So before we end up in this situation, let's talk about some strategies for safer editing. Today we're going to talk through five different strategies. These include strategies for maintaining a raw SQL spreadsheet, running scripts locally, running scripts on an existing server, using a task runner, and building out a fully fledged script runner

Speaker 2: service. We're going to walk through each of these strategies in order of the amount of effort it takes for that strategy to be implemented at your organization. And we'll also plot them in terms of the benefits that you'll receive For each strategy, we'll talk about three things. How that strategy works and an example of how to implement it, what's great about the strategy, and also what's not so great. The examples that I'm going to use in this talk involve a Python stack and a SQL database, but these strategies will actually work for any stack of your choice. So let's dive in. The first strategy we're going to talk about is relatively simple. It doesn't require any new code to be written or infrastructure to be built.

Speaker 2: Rather, it's simply developing a process for making sure that we get spot checked when we make edits to production. So what does this process look like? Well, let's go back to that query that I had wanted to run for the marketing team. I want to update the product with ID1 to be named Julie's product. And what I did a few slides ago was I just went ahead and I edited that field without any supervision. So we all kind of saw that that was a pretty dangerous situation to be in. So what we did at my company is we started maintaining a Google spreadsheet to actually record manual SQL queries against production. This Google spreadsheet allowed us to collaborate and review each other's queries before they got executed. It also gave us a checklist for what needed to be done before running a query against production.

Speaker 2: So here's what that checklist looks like. The first step would be for me to add a record to the spreadsheet. Some information I jot down include my name, uh the date, a description, what the query is And then I would actually write down who I wanted to review the query. The reviewer would then take a look and either approve or request some changes. The next step after we go back and forth a couple of times is for the reviewer to go ahead and actually approve the query. So once you get those two thumbs up, the next step is to run the query. We remind everyone to always run the query inside a transaction. That way, if anything bad happened, they can just go ahead and roll back.

Speaker 2: So what's great about this system? Well, the first is that it's really easy to implement. uh you could get this process up and running right before the uh the party at the end of the day. All you have to do is make a spreadsheet and write an email and describe it um to your team. But the effects can be really powerful. And that's because this strategy provides us with an audit trail. So after we implemented this process, we went from having ad hoc queries that We hoped people were getting spot checked to having a log of everything that was being run against production and who had looked at it This audit trail not only gave us information about who was running the queries, what they were running, but also information on why they were running these queries.

Speaker 2: And this led us to a lot of discussions about where maybe we should really be investing in internal tools or overhauling and rebuilding a system entirely. And lastly, this process works to promote the right behaviors. So it helps to not only encourage everyone to be more careful, but it also teaches engineers what the right things are to do And so right now we have a strategy that gives us the benefit of data editing. It requires very little upfront investment, and we've also implemented a manual version of a code review process and an audit trail. This is great because at your average startup, raw SQL edits are probably not gonna go away. We still use this spreadsheet today. But this process

Speaker 2: hasn't made it so that we are empowering engineers to avoid mistakes by making sure that they get spot checked Introducing this process also made it just slightly more painful for people to run queries that they needed, so then we're encouraged to build better tools. So what are some things that we're not yet getting with this process? Well, the first is that it's still pretty easy to make mistakes. After all, we're assuming that people are copying and pasting the queries that they need from the Google spreadsheet to the SQL prompt. But that might not be true. And even so, errors can happen when we're copy and pasting. This audit trail is also being maintained manually, so this opens up some room for error. After all, there's really nothing prevents

Speaker 2: someone from running an unchecked query. And lastly, while it's relatively easy to update the names of one product, It can be difficult to execute long and complex logic with raw SQL. And so if your query takes a really long time to run, it's gonna time out For example, let's go back to that query that we had been trying to run, and let's say we want to run some variation of it that's a little bit more complex. What if the marketing team hadn't given me a product ID? What if instead they had come to me and said, hey Julie, uh we have this brand, it's called Julie's Store. And right now there's a bunch of products in it and we need you to update every product that doesn't currently have a name

Speaker 2: but is active to be named Julie's product. So we can see how the queries can get pretty complex really quickly. So this brings us to our next strategy, which is writing scripts and running them locally. To run scripts locally, we first start by writing the script. To do this, uh you would take your SQL logic and actually convert it to code in your programming language of choice. Mine, of course, is Python. And I would usually also add an argument parser to my scripts to make them reusable. Especially if I plan to use this logic again in the future. For example, say to update product two to Julie's other product. Next, we're going to form a connection to the production database.

Speaker 2: You can use this using a VPN or an SSH gateway. And lastly, you'll go ahead and run the script from your terminal. In Python, this is what that command would look like. And I often like to write my scripts in such a way so that I can actually add a dry run flag. That way I can preview the results before actually committing changes to production. So what do we like about this strategy? Well the first thing is that scripts, unlike raw SQL, are reusable. So you only need to write the script once, and then you can just pass in different arguments in the future. Additionally, if you need to manipulate the outputs of the script, it's really easy to take it, pipe it into a text file, or another script you might want to run.

Speaker 2: And lastly, writing a script gives you access to all the rest of the code in your repository. It's easy to import functions from common code and reuse logic that already exists. So looking at our journey, uh we now have a strategy that gives us two benefits: the ability to edit and the ability to execute somewhat complex logic. At this point, we didn't need to set up any infrastructure, so the upfront investment is still relatively low, uh though a bit higher than just running queries with raw SQL. What don't we get with this process though? The first is that code review isn't being strictly enforced. So someone can write and run a script without any form of code review. The outputs of the script are also only available on the user's machine

Speaker 2: and will go away as soon as they exit out of the terminal. And lastly, we can run into network connectivity issues. So the script is going to stop running if, say, the internet dies out, or uh if the user closes their computer. Which kind of begs the question, what do we do if our scripts take a really long time to run? For example, what if I wanted to run this query many times? In fact, at Spring, we have over 50 million products in our database. So what if I wanted to update a field on all 50 million products? Well, usually uh when I first started working at Spring, what I would do is I would just come to work really early. Uh I would get there, open up my laptop, and just let the script run and make sure that uh my laptop is plugged in and everything is working right.

Speaker 2: And this was until one day I was sitting there and waiting and waiting. Um, and by the end of the day, only about 25% of the table had been processed. So I talked to my manager at the time and uh we started brainstorming. Wouldn't it be great if there was just a computer out there and it was just like my computer and had all the configurations I needed? Except, unlike my computer, we could guarantee that it was always connected to the internet and that we didn't have to worry about its battery power. Well, luckily for us, we actually do have a lot of EC2 instances running which do just that. Because after all, that is how our web app and stays up all the time. Uh so the next strategy we're going to talk about today is how to run the scripts on an existing server.

Speaker 2: Here's how this will work. Similar to running scripts locally, I would start by writing my uh migration script. Because we're running the script on a server, the second step here is we actually need to get the script onto the server. There's a variety of strategies for doing this. You could deploy or fetch a copy of the repository the script is in, or you can upload or transfer the script. We compile code before deploying an application, so what I would usually do is actually copy the script onto the remote server. I often use Jenkins as the server for which to run my scripts on because it's the server that we like to run automated tasks on, such as our deployments. And after the code exists on the server, the last step is to just SSH into the server and run the script inside a session.

Speaker 2: In general, this method of running scripts on Jenkins has worked really well for me and gotten me through some really major projects. This was until one day I happened to notice this message show up in our engineering Slack channel Our on-call engineer Maya happened to notice that Jenkins was down, and so she pinged her DevOps leave Justin to let him know. And I'm a really responsible engineer. So of course I did the responsible thing and also checked. And was like, yeah, hey Justin, it is down. And then I went back to doing my work. And two minutes later I had a realization. I think I shut down Jenkins.

Speaker 2: Uh and my hunch was right. Uh it turns out that the script that I had been running on Jenkins was eating up all of the CPU. Um and as a result, uh Jenkins was down and none of the engineers could actually run um their deployments. Um, it was a really easy fix though. Um all Maya had to do was just reboot the server and then Jenkins was back up. Which means that uh this is actually a pretty good strategy um still for a lot of large projects, especially if you monitor your code um just a little bit better than I did um and also if a little bit of downtime on that server. isn't the worst thing in the world.

Speaker 2: But it definitely uh triggered me to start thinking about looking into better systems. So before we talk about uh an uh improvements to the system. What's great about running scripts from an existing server? Well the main thing is that you can now run long scripts, or really any scripts, without needing to leave your computer on You can run your script inside a session and then come back and just check on it later. Similarly, because we're running the script on a remote server, we have the benefit of a much more reliable network connection We don't have to worry about things like the Wi-Fi getting disconnected. And lastly, you're gonna get all the benefits of running scripts on a server, and we haven't needed to build any new infrastructure.

Speaker 2: So when we look at this strategy in comparison to raw SQL and running local scripts, the main benefit that we see is that we can now run scripts for a really long time without worrying that our laptops uh are gonna shut off. The script has pretty good low investment costs. There's some DevOps configuration involved, which makes the investment higher than just running the script locally, but it's still a relatively easy system to rely on. What doesn't this strategy get us though? Well first, uh this strategy of running scripts can affect the resources on the server. So in my case we used up too much CPU, but scripts can also consume too much memory. Second, uh the need to actually copy the scripts onto the remote server, SSH into that server, and then start a screen session, it's really not the most user-friendly experience, and it opens up a lot of room for error.

Speaker 2: And lastly, we still don't have a persistent audit trail. Sure, there's logs being outputted onto the server, but They're probably gonna get lost once the session ends, and most likely only the engineer running the script can actually see them. So we can do better. Let's talk about a strategy next that is a lot more user-friendly for our engineers and has a persistent audit trail The next strategy we're going to talk about is using a task runner. A task runner is something that lets us automate the task involved in running a script. So this involves uh SSHE into the server, setting up a virtual environment, getting a copy of the code. All the repetitive things that we had to do when we were using an existing server. But best of all, logging is also built in.

Speaker 2: So we set up a task runner on Jenkins because we use it as our continuous integration server for builds and deployments. Jenkins also lets you write code and register arbitrary jobs, and the Jenkins build page provided us with a free user interface. So similar to the last strategy, we'll start by writing the script. Then we're going to get the script code reviewed, tested, and merged. And lastly now, instead of having to SSH into the server in order to uh run the script, we'll instead just run it from the Jenkins interface. So to do this you would go to the project, type the file path of the script you want to run, select the arguments that you want to use, and then click build. Jenkins will then take care of all the things like setting up a virtual environment, connecting to the remote database, and running the script with the arguments you input it.

Speaker 2: And you can just watch it all happen. This strategy is great because it's the first one we've seen that gives us detailed audit logs. The logs make it a lot easier to monitor the progress of the script, which is really useful for long scripts. Because the first step of our task runner is to fetch the latest version of master, all code being run on the task runner has to be code reviewed and merged into the repository. So this means that we're now able to enforce code review and provide the ability to write automated tests before the scripts are run. And lastly, we can now run all our scripts from a user interface as opposed to needing to SSH into a server, which minimizes room for error. And we've come pretty far on our journey now.

Speaker 2: At this point, we have a process for code review, for running automated tests, an audit trail, and a user interface. Setting up a task runner does take a bit of time, but when I think about the amount of time that our team of 50 engineers has been able to save because We don't need to do things uh like manually copy code onto a server or figure out uh how to correct mistakes from scripts with no logs. The upfront investment seems pretty worth it. There's still some reasons though that it's not exactly what we want. Mainly, uh, if you think about it, inputting command line arguments every single time you want to run a script Starts to get pretty annoying. It also makes managing credentials really hard.

Speaker 2: And this is particularly true if you have a lot of credentials. For us, this was the main driver to look into better solutions. After our task runner had been launched, I found that one engineer was still running long scripts by copying them onto an existing server And it was simply because his script just had over 30 credentials. It was just easier for him to store all of his credentials in a file on the server and Pipe them into a script when he needed to run it, um, and then type in a bunch of configs in other than type in a bunch of configs into the task runner UI. There's also not a clear separation of environment here. So I could easily use the database string from our dev environment and accidentally use credentials from production. That's not great.

Speaker 2: And lastly, there's no system in place for us to verify the arguments being passed into the script. For example, uh if I have a script to update the name of product one to Julie's product No one's there to make sure I'm updating product one instead of product two by accident or spelling Julie correctly. And all of these really annoying problems, they started to get me thinking. We have all these problems we're dealing with. Configuration management, logging, separating environments. Haven't we solved all of these problems before? After all, all of our existing applications do this. So why have I been trying to reinvent the wheel instead of just building something with the tools that we're already using?

Speaker 2: And so that's what I did next. I decided to use our existing tools to build an application to run scripts on. Our own internal script runner service. And it would be architected like the rest of our Python applications. So here's what that architecture looks like. An application server was set up for each environment to run scripts. Each server had access to credentials that we stored in Hyera, which are common to all of the other EC2 instances in its environment. The application was set up to connect to the database in its respective environment. And the steps for running the script are really similar to that with using a task runner. So first we start by writing the script, then we get a code review, tested, and merged. And then

Speaker 2: lastly, and the key difference here between this strategy and the task runner is that the user no longer needed to input a series of command line arguments. All they needed to do was select the environment they wanted to run the script in. And this is what it looks like to run a script with this UI. So the user would go to the user interface, type in the file path the script they wanted to run, select the environment, and then click build. And all the command line arguments that we had to manually input before, they're now available just as environment variables on the script on our server. Which brings us to some of the key benefits of the script on our service. The first of which is centralized configuration management.

Speaker 2: So we now have a system for using credentials in our script that is a lot more organized than selecting a bunch of drop-downs from command lines. The second is that the strategy allows us to maintain a clear separation of environment. And lastly, this strategy is the most user-friendly of all the strategies that we've seen. And the amazing thing about the script runner service though is that it doesn't have to stop there. There's even more that we can do. So for example, as we're trying to grow our engineering team, if a lot of engineers need to run scripts at the same time, we can just parallelize and scale our instances to make that possible. If we wanted to preview the effects of our scripts on the database before committing changes, we could build that preview mode functionality into Script Runner.

Speaker 2: And ultimately in the end, it's up to you on to decide how you want to customize your own version of Script Runner. So we've seen today a spectrum of the tools that you can use for editing production data. So some of you might want to know, which strategy should I use? Well, ultimately that depends on the needs of your team and your organization. So if your organization is small and things like speed is really important and making mistakes once in a while isn't the worst thing Raw SQL edits might make the most sense for you. But if your team is larger and as your team is growing, you might need more important functionality and want to think about investing in building out a script runner

Speaker 2: service. You can't have 50 engineers jumping into a Google spreadsheet every single time that they need to make an edit to production. So for us, as our engineering team was growing, building out a script on our service made a lot of sense. But the key here is that as you think about growing your business and growing the size of engineering team, make sure that you are also thinking about building out the internal tools needed to support them and allow them to do their best work Which kind of brings me to a key learning that I had in the course of building out these tools for my team And the first is that when you're building out tools for your team, it's important to not think to not only think about safety, but also think about speed and usability.

Speaker 2: Because after all, that's why we all get behind the SQL prompt in the first place. In the same way that it's important to think about the end user when you're building out consumer applications. It's also important to think about making something usable for your engineers. Because after all, engineers are also people. And on the flip side of that, a lot of times we find ourselves uh doing things that we all know that we shouldn't be doing We're all guilty of sort of looking at something, thinking about it, and then being like, you know what? We really shouldn't do it like this. Like I really shouldn't just be editing production data uh without any supervision. And then two seconds later turning around and then doing it anyways. You should think about taking the time to

Speaker 2: invest the effort up front to build the tools you need. Ideally, before you edit the names of 50 million products to Julie's product. Thank you all for listening and If you have any questions, I'll be here for the rest of the conference, or you can reach out to me on Twitter at JQ25. Thank you.

Speaker 1: Thank you very much. That was a very, very interesting talk and I think we can all very many of us can relate to the problems and solutions you you brought us. Thank you.