2 ms·

The goal of ETL is to transform data for practical applications, for which CPU and memory is there to be utilized. This particular dbt process was running agai

The goal of ETL is to transform data for practical applications, for which CPU and memory is there to be utilized. This particular dbt process was running against the second smallest Redshift instance, which barely noticed when the process kicked off every 10 minutes to run incremental updates. Would run for maybe 60-90 seconds, with only the occasional lock contention with long-running analyst queries that was easily mitigated.

riku_iki 3y ago
> The goal of ETL is I said goal of "such ETL optimization", not ETL. Luckily for me it is an hour and 50 lines(likely less) of code to write such parallel executor with dependency tracking if need will arise.
- camgunz 3y ago
  I had the misfortune of having to reimplement dbt at work (we have a weirdo "don't directly connect to app DBs" rule so I had to add support for running arbitrary Python to fetch data from our microservices over gRPC), and yeah the dependency tracking, subsetting, testing, parallelism and such is super easy -- it's more like 1000 lines of Python/SQL for the basics, but yeah no sweat really. Where we ran into issues was: - Availability: the general model of dbt is DROP the model (OK by default they TRUNCATE but w/e) and rebuild it. But this means that model's unavailable (read: locked) during rebuilding. Further, you can't do that if any downstream view depends on your model. Even if you decide it's OK to drop the downstream view, rebuild all its upstream dependencies, then rebuild it, it's unavailable the whole time. Further, you probably don't have just one downstream view depending on these upstream models, so you've got to work out all the downstream views to DROP and then rebuild, which is like a secondary dependency tree to walk while walking the primary dependency tree. But, that mind-bending weirdness aside, you're essentially making your whole DB unavailable during a build. Sometimes this is OK, for us it really wasn't. - Incremental tables added a lot of complexity: rebuilding bigger tables that mostly don't change is a big waste of time and resources. But this is an entirely parallel code path which more or less doubled our code burden. We tried pretty hard to manage these, which ultimately was a big rabbit hole. We eventually settled on schema versioning where we'd build a whole new DB in a different schema, run tests on it, and promote it if it passed. This let us avoid both the availability and incremental ditches, and as a bonus we could instantly restore past schemas if we discovered issues (restoring DB backups is sloooooooow and lossy). But, I think the engineering lesson here was we either should have rescinded that weirdo app DB rule, or restricted our custom work to ELTing the data out of our apps and used dbt downstream of that.

riku_iki 3y ago

> The goal of ETL is I said goal of "such ETL optimization", not ETL. Luckily for me it is an hour and 50 lines(likely less) of code to write such parallel executor with dependency tracking if need will arise.

camgunz 3y ago
I had the misfortune of having to reimplement dbt at work (we have a weirdo "don't directly connect to app DBs" rule so I had to add support for running arbitrary Python to fetch data from our microservices over gRPC), and yeah the dependency tracking, subsetting, testing, parallelism and such is super easy -- it's more like 1000 lines of Python/SQL for the basics, but yeah no sweat really. Where we ran into issues was: - Availability: the general model of dbt is DROP the model (OK by default they TRUNCATE but w/e) and rebuild it. But this means that model's unavailable (read: locked) during rebuilding. Further, you can't do that if any downstream view depends on your model. Even if you decide it's OK to drop the downstream view, rebuild all its upstream dependencies, then rebuild it, it's unavailable the whole time. Further, you probably don't have just one downstream view depending on these upstream models, so you've got to work out all the downstream views to DROP and then rebuild, which is like a secondary dependency tree to walk while walking the primary dependency tree. But, that mind-bending weirdness aside, you're essentially making your whole DB unavailable during a build. Sometimes this is OK, for us it really wasn't. - Incremental tables added a lot of complexity: rebuilding bigger tables that mostly don't change is a big waste of time and resources. But this is an entirely parallel code path which more or less doubled our code burden. We tried pretty hard to manage these, which ultimately was a big rabbit hole. We eventually settled on schema versioning where we'd build a whole new DB in a different schema, run tests on it, and promote it if it passed. This let us avoid both the availability and incremental ditches, and as a bonus we could instantly restore past schemas if we discovered issues (restoring DB backups is sloooooooow and lossy). But, I think the engineering lesson here was we either should have rescinded that weirdo app DB rule, or restricted our custom work to ELTing the data out of our apps and used dbt downstream of that.