Swiss Army Django: Small Footprint ETL with Noah Kantrowitz

Hi there, uh I'm Noah Cantrut. Uh I work for IKEA on computer vision stuff, but I am not here to talk about that. I'm here to talk about ETL. Uh and I'm not going to be talking about work stuff. I'm going to use one of my side projects for this, a web online, mobile, etc. game called FarmRPG. If you're not a big gamer, don't worry. The important things to know are that like most RPGs, this has a lot of heavily interlinked data. So items that drop for monsters, and monsters are in locations, and items used in recipes, and you get recipes from quests, et cetera, et cetera. What this looks like at the SQL level is basically every table has a whole bunch of foreign keys to other tables in more like a web arrangement rather than the sort of more traditional tree structure you'll see in the database of a normal business REST application. And this isn't really related to the main thrust of the talk, but if anyone is asking themselves, did he really spend a year building a complicated ETL system for a free online webgame that he plays?

Uh yes, yes, I did, and I highly recommend that you do other fun side projects. It's a great way to learn these tools, and a lot of these things are now used in my day job. Big shout out to side projects where you can move at your own pace and no one bothers you if it's just down for a week. But okay, this talk is about ETL. What is ETL? Well at the heart, it's really literal. You pull some data out of somewhere, you run it through some transformations, then you load it into a database of some kind. They're not exactly the same thing as web scrapers, but it's the same general structure. So maybe instead of a website, you're scraping an internal API, or maybe it's a database instead of HTTP, but it's gonna be the same kind of general internal systems. And because I am talking about scraping, I'd be remiss if I did not point out that scraping hostile websites is against the terms of service of those websites in almost all cases.

Please make sure that you have permission from the owner before you scrape any website. If you don't know what you have permission to scrape, please either talk to the owner of the website or a trusted legal professional or both. But okay, uh the general shape of things. We're going to have extractors that are gonna pull stuff in, we're gonna have transforms that then do some structural changes to the data, and then we're gonna load it into a database. The extractors uh are going to generally give us back some kind of set of bytes. So whether it's a web scrape or whatever, we're gonna get back a pile of bytes. We have to turn that into structured data somehow. This is going to be the most of the transform phase. There's going to be three big steps. So this first is the parsing. Now bytes turning into structured data, that could be as simple as json.

loadS or something as complicated as HTML parsing. Then usually the second step of a transform is we're gonna have to move some data around, reshape it into something that'll match the queries are gonna run later. Now this could be something fancy like SQL normalization or denormalization. Or more commonly the more mundane version, renaming fields, changing variable types, you know, like parsing a string to an integer, things like that. And then optionally, we can deal with aggregations. Now there's two main ways that we could deal with aggregations. In the real ETL world, there's a third way, which is these huge, complex system-spanning tools, stuff like Spark. But for our cases, we're going to be targeting small footprint ETL systems. That's going to be the big thrust for this entire talk. So we don't need giant multi-server systems. The simplest answer is just do nothing.

Handle it at query time. Postgres is really fast, so this is probably enough for most people. But maybe you do want to pre-calculate them. You're going to reuse the same aggregates multiple times. You want to check the history of the aggregates, something like that. So as a sort of last step in our transform, we can make the aggregate and then store it back into the database during the load phase. Loading as a phase in Django is so simple that we don't normally think of it as a phase. We chuck some data at the RM, it ends up in the database. End of problem. Maybe if we get really fancy, we'll want to use Django Rest framework. We'll talk a little bit more and show some examples of that later. But really, in general, this isn't a thing you need to worry about. We have a whole suite of tools and we all know how to use them. Alright, most of this talk I'm going to be talking about async.

Async and Django has been a long journey, and that journey is not quite over yet. But a core thing to take away is that async Django is good and you can and should use it for real things in production. There are some limitations and we'll talk about that in the ORM later, but sort of the top line thing, async and Django is good. Why am I going to be doing this all in async Django? I want a small system. And the one of the great things about doing this in async Django is I can keep everything in one service. I can have all of my stuff in one box, one code base, one running thing. One of the uh more well-deserved pieces of big complex ETL systems is they're very finicky creatures. One service will fall out of sync with another service, and then it'll jam or start dropping data until some sad

-on-call engineer gets paged and has to come by and kick something or change a config file. It's really just not a great fun time for everyone By keeping things this simple, we've reduced the number of places that things can fail, and when they do fail, we have simpler answers. We've only got two services, Django and Postgres, restart one or both of them, and we're back in business. Also, being inside just one box means that we have better portability. We can move things between deployment tools, infrastructure services, and it's much easier to run for local development. If you've ever tried to run a big complex Spark system on your local laptop, I am sorry. The the hip thing for many years has been microservice everything. Microservice, microservice, microservice. And while I do run plenty of those,

They are not needed for everything. They're very important when projects get big and when they cross team boundaries. But if you've got one team working on one small thing, it doesn't need to be a dozen microservices. Embrace the monolith. It's fine, I promise. All right. So other talks, uh I think even some here, uh, as well as the Django documentation itself, go into this in much more detail. But starting in Django 4. 2, Creating an async Django app is basically the same as any other Django app. You create a project, you create an app in that project. It will come by default with all of the async stuff set up. When you start adding views, add them with async def instead of just def. No problem. All the default middleware is async compatible, so you don't even need to think about middleware until you start adding custom stuff.

Most major community projects are also async compatible, so no worries. If you have middleware that is not async compatible, Django will automatically adapt it for you, although at a performance cost. One thing Django doesn't currently include is a async compatible web server. So I prefer UVicorn mostly because it comes with this dash dash reload option. It gives me the same reload on change workflow as the standard develop server. But you can also use the same one for production, you just tweak some config options, and you're good to go. All right, any ETL system, it's a background scraper. We've got to have background tasks. And the standard option for background tasks in Django is going to be celery and celery beat. But that means running a whole bunch of stuff. You've got to run the Celery Daemons, you've got to run probably RabbitMQ, maybe Redis.

I don't want to do that. There are some smaller options. So a a sort of the next level down is custom management commands and cron, either normal cron or Kubernetes cron or whatever you've got. But we can go even smaller. One of the benefits of async is that we can just spawn stuff inside our application. Now, before you could do this with threads, this isn't completely new, but Python and threads. It doesn't always go super well. They can start competing with each other for resources and things like that. But with async, we don't have to worry about any of that. We can just make a function, it'll sit in a loop, it'll call it, and then it'll sleep, repeat forever. This is the basics of any scraper. Any load phase in ETL or uh sorry, extract phase in ETL parlance. The easiest way to do this in Django is the ready hook in app configs.

So that is a synchronous function that is called during the loading process. when Django gets to your app and says, okay, get everything all set up. So we can take that function that we just wrote and we want to spawn it off into the background. So it'll just sit and loop and every say 30 seconds, it'll run that function. Create task does exactly this for us. So it will continue running synchronously. Django will will carry on initializing. Uh create task doesn't block. It just spawns this in the background kind of as if it were a thread, but with no actual threads involved. But okay, we do need to talk about some of the downsides of this approach. With Celery and RabbitMQ, we've got a much more durable system. If the task is accepted by the queue, it's definitely gonna run at least once. If we're doing this all just in memory, well, what if the task crashes?

It's just gone. Tough. Okay, we can add some exception handling. We can add some retries. What are the process crashes? Again, the task is just gone. But for ETL systems in particular, this is usually fine. We're gonna run this every five minutes, every hour, whatever it is. We miss one hour, we'll pick up the data in the next hour. As long as you are scraping more than one hour at a time, every hour, you've got a built-in buffer for failure. Not everything can be handled that way, so you do sometimes need to think about your failure modes. We can do tracking of the database. We'll see an example of that in a second. But in general, make sure that you are thinking about what will happen if this task crashes or if my process crashes. So This idea of modeling for failure, let's say we want to send an email using async stuff.

This isn't really ETL specific, but just as an example, you know, we want to send an email in the background. You know, we could write a celery task for that. I literally have one of those in my production application. But if we write just an async task for send email and the process crashes while it's halfway through, that email just vanishes. What we could do instead is to make a database table for emails that are pending to send out with a either Boolean for has the sent out, or we can just delete them when they're sent. That gives us a much more convergent approach. So we'll just keep trying every 10 minutes. We'll check what emails need to be sent. If there are any that are pending, we will try them again. All right, async ORM. I mentioned a little before that this is one of the places where async Django has a couple of restrictions, but don't worry, they're not too bad.

So The big thing to know with the async RM, every function that you're used to in the standard synchronous RM, it's got a variant that starts with A, A for async. So a save, a update, a first. All of those things. Anything that would normally go and talk to a database has a version with A, and you call it with a wait. The two big limitations are one that you can't do transactions quite the same way and we'll see an example of that in a second. And also that queries can't overlap. So the overlap thing is mostly an implementation detail. Uh what happens is that you can still run them concurrently from async. io 's point of view. It thinks that you're running you know four parallel SQL queries. Internally inside Django, those are actually run on a background thread, and they only run one at a time. So you can still write code like that, but just understand that you won't get any performance benefits from concurrent SQL queries

yet. The transaction thing is a little bit more complicated. So if we just Did transactions normally in async land, the problem is that multiple queries could end up interleaving because each one is sort of giving up the the runtime of the current uh coroutine and yielding onto another one potentially. So what we do for transactions instead is that we have to put everything into a single synchronous block and call that using the sync to async helper that comes inside ASGI ref. This is not great and the Django team is still working on this. Uh if Andrew is in the room, I am very sorry. Um We've started to see true native async support in libraries like Psycho PG3 only in the last about six months. So as that starts showing up in more database drivers, it'll open a lot of doors for the async ORM. All right, uh

but we don't just want to talk databases. The most common that you're thing that you're gonna see in an extractor is HTTP. My recommendation is HTTPX. So it's Very familiar to anyone that has ever used requests before, but supports async. It's also got great testing support via a library called Respecs. There's another library, AIO HTT, that is also available. It's a little faster in terms of raw HTTP performance, but it doesn't support HTTP2, which kind of cancels out that benefit. But in either case, unlike the Django RM, both of these can fully overlap requests Although make sure if you're doing that that you do not accidentally overload the server on the other side. Ask me why I know. All right, uh showing code on the screen can be a little bit fraught, but let's Try and look at some examples for my case study project.

So this is gonna be the simplest ETL, and I have a bunch of these running right now. We call an HTTP and then we put it in the database, we repeat, everything is happy. This is going to be the sort of platonic ideal of a Django small form ETL. We combine this with that looping call helper that we saw before to run this every hour or every five minutes or whatever we need. And we've got ourselves an ETL server, but maybe this isn't enough for you. One of the sort of next levels up is foreign key support. So we've got, you know, like I mentioned at the beginning, I'm not just scraping one API endpoint, I'm scraping four or five, and they're all interlinked. Now some of those give me a true primary key. So I can just use them directly, no problem. But in a lot of cases, you get a symbolic primary key.

You know, I'll get the name of the related object. But no problem, because this ETL isn't built out of the config files of a dozen microservices, we can just write some code. So we write one a get call and we just keep going. So I mentioned DRF. This is the sort of next level up of complexity. DRF serializers are great in this use case if you're getting a single call that returns you sort of big, deeply nested data that crosses multiple tables. You can use DRF's nested serializers to deserialize out everything all at once. Now DRF doesn't natively support async, and you'll probably run into other uh Django libraries that don't, but this is just for an example. Sync to async has us covered no matter what. It can take any synchronous Django function and make it async compatible, no problem.

But okay. That looping call helper, uh we can define everything in the ready functions in all of our apps. That gets a little bit old. Maybe we want to do something more like Celery's task decorator. Django has a helper method for this called auto discover modules. So it takes uh the name of a module and you give it an object containing an attribute called underscore registry. It must be exactly called underscore registry. You don't get to pick that. But you just put it wherever you want, and you can write a small decorator that just adds things to that global registry. You call auto discover modules with the sub-module within each app that it should look for, and you've got yourself an equivalent to uh celery's task decorator. And sort of another level up of complexity towards celery or celery beat. Uh what if we don't just want to run things every hour?

We want more sort of cron-like scheduling. There's a great library for this called Chronitor. It'll handle all of the complex math. All you have to do is track when each task was last run. You could do it in a database or in an in-memory structure or in a file on disk, whatever you need. And Chronitor will tell you when it should next be run. So check if that time is ahead of now and run it. So we end up with one loop that should run say once a minute or once a second, checks all of these, no more problems. So this is an example of my actual cron loop. This does a slight bit more complexity in that it checks to make sure that the previous task has actually finished, so I don't stack up tasks if they take longer than the interval. And then inside that it just does the math with Chronitor, and that's all we really need.

Yes, this is duplicating code that Celery Beat or real cron would get you. For free, but as you can see, this is like 10 lines of code, and it dramatically cuts down on the operational complexity of the system. Another great use for ETL systems is tracking history. So there was a talk uh yesterday on PG history, so if you didn't see it, you'll have to watch the video. Um but it is great for this. It's implemented using uh database level triggers, so there's no need to like worry about Django signals or major performance impacts. Make sure you do exclude anything that is really commonly changed because if you are scraping every, say, 60 seconds and it changes every 60 seconds, your history tables get really big really fast. Again, ask me why I know. Another really common use case for ETL things is an incremental load. So you know we only want to get the things that have changed since the last time we scraped.

Sometimes this is going to be ID value, sometimes it'll be a mod time. But again, this isn't a config file, this is code, so we can just write a couple lines of ORM stuff, no problem. Really easy to implement. Big complex things again like Spark, they they do these DAG runners, so you can have multiple reusable stages in a in a big graph of steps. We can do that too, and it doesn't need to be nearly as complicated because again, we can just write a couple lines of code using standard async. io stuff. All right. Technically ETL only covers the data ingestion, and I don't have very long because we lost a little bit of time at the beginning. But you probably want to query that data or you wouldn't be gathering it. Now the simplest way to query it would just be SQL. But that's boring, so let's talk about something more fun.

I'm going to talk about GraphQL really quickly. But first a warning, GraphQL does not scale. There was a talk right before this on writing high-performance GraphQL, so I haven't actually seen that because I was practicing my talk. But just in general, uh GraphQL gets harder and harder to implement as your dataset gets larger and larger. If your stuff is very cacheable, that'd be f that might be fine, but otherwise beware the dragons. So why use it at all then? Because it lets me really quickly run generic queries against uh nested data. And again, I've got highly interlinked data, so I may want to query four or five tables. This is an example of querying across three tables. So this is items, quests, and a many-to-many table through them, and I can express that all in a single query.

The best tool I know of for this in Django is Strawberry. The core Strawberry Library does the sort of data flow stuff, and then there's a Strawberry Django library for helping get data from the ORM. It does stuff like you can uh automatically infer uh field types based on the Django models, and these work really well with static site generators. How am I doing on time? I can't see my clock. Okay, we are out of time, so I'm going to uh skip through the rest of this. Thank you very much.