Hacker News Reader

Comment by dm03514 | Hacker News Reader

Sorry nothing positive to say here.

I’ve been using dbt and MDS for nearly 3.5 years and I believe the entire approach is profoundly broken. There’s really nothing “modern” about it, especially compared to software engineering.

https://on-systems.tech/blog/135-draining-the-data-swamp/

Building on Extracted operational data is hard at best and a business altering security liability at worse.

I believe The MDS trails at least a decade behind modern software engineering practices, lacking industry guidance and generic tooling to support: CI/CD, versioned deployment artifacts, zero downtime deployments, unit testing, observability, monitoring and alerting.

MDS Data engineering is a meme in the industry, the expectation of “100%” combined with the lack of modern tooling makes success really hard to achieve.

View on HN

The mistake everyone makes is treating the entire space as if it's somehow different than the rest of software engineering, when it is precisely exactly the same.

Well… no I would disagree. I am a data engineer who relentlessly pushes for more classic SWE best practices in our engineering teams, but there are some fundamental differences you cannot ignore the data engineering that are not present in standard software engineering.

The most significant of these is that version control is not guaranteed to be the primary gate to changes to your system’s state. The size, shape, volume, skew of your data can change independently of any code. You can have the best unit/integration tests and airtight CI/CD but it doesn’t matter if your date source suddenly starts populating a column you were not expecting, or the kurtosis of your data changes and now your once performant jobs are timing out.

Compounding this, there is no one answer for how to handle data changes. Sure you can monitor your schema and grind everything to a halt the second something is different in your source, but then you’re likely running a high false positive rate, and causing backup or data loss issues for downstream consumers.

The truth is change management and scaling is more or less a predictable and solved problem in traditional SWE. But the same principles can’t be applied with the same effectiveness to DE. I have seen a number of SWE-turned-DE struggle precisely because they do not believe there to be any difference between the two. It would be nice if it were so simple.

You are massively oversimplifying "classic SWE" here. Ultimately it makes no difference.

How you handle contract violations between dependencies and third parties is not an inherently different problem. What should you do if a data source changes schema? How do you detect it, alert, take action? How are users or customers or consumer jobs impacted? This is all just normal software.

In many ways it feels like you're making my case for me.

Whenever I get confronted with SWE best practices it is all about version control, unit tests, integration tests and CI/CD.

What are some good resources for the other things you describe?

I don’t have great links handy, but read up on observability.

You’ll want to read up on what people do on error, especially in the case that contracts change (search “api versioning”.)

Thank you!

It may be that those topics are part of the SWE process but not generally applicable for example UI work and therefore overlooked.

But it may also be something else. That a process can create Grafana dashboards and processes for API updates like a pro.

But only the person that really cares about the data and know it from inside out can catch the "unknown unknowns". And for that you need to give access (and hand over responsibilities) to Analyst persons too and in some ways trust them too, even though they may not have as rigorous processes.

>The most significant of these is that version control is not guaranteed to be the primary gate to changes to your system’s state. The size, shape, volume, skew of your data can change independently of any code.

That has been true in SWE since the days of ENIAC.

Machine learning people have cottoned on to this, so MLOps was invented.

The training part of MLOps is important to ensure replicability and other desirable properties or the ML artifact, but the rest is clearly good CI/CD and observability.

We had an application engine come in and run an ML project.

The first thing he did was remove real data from training, as he was shocked to see that development wasn’t done against dummy data.

And once the dev environment is running on real data, the changes in how you develop and operationalize just seems to cascade in my experience.

I am slightly confused by your reply. Can you elaborate a bit, was it good or bad?

You can develop a database schema for your application on dummy data, and test your business logic on dummy data. But you can't develop a machine learning model on dummy data.

It is best practice to keep live data out of your development environment for normal software development, but it is impossible for machine learning projects.

Yep. I advise my clients to have a controlled, sandbox partition of prod. Call it the whiteboard or what have you, things can be erased or you can cache model version runs. Your data transforms can happen in dev but training should happen against real data. Synthetic data can be a compromise but that can sharply bias your model and, since it a harder lift, often multiplies data prep time

Yes, this is what I thought and exactly what I am experiencing right now.

It is somewhat frustrating to see the engineering/platform team spending time on this kind of abstraction that is essentially useless for doing the work we need to do.

This. Nearly everyone is confused.

I was a fan of dbt for a while, but the shine wore off when I saw one of my smartest coworkers try to use it. His development speed was about an order of magnitude slower than what I expect from data scientists using dataframe packages like R's dplyr, Python's polars, or Spark DataFrames. In my own experience, dbt is significantly better than writing raw SQL, but still nowhere near a normal software development experience. It needs an IDE badly, but the current IDE is cloud-only.

I am currently building my analytics pipelines on top of R's dbplyr package, which allows me to run dplyr operations on database tables just as if they were local data frames. Since it's R, it's not without warts, but at least I get to use a real programming language and the free and fantastic RStudio IDE.

> allows me to run dplyr operations on database tables

Which require that data take a round trip between the R process and the database. It's not uncommon for these jobs to spend more time in the read/write step than doing meaningful work, and why I prefer dbt when possible to keep transformations as close to the data as possible.

The second point the grandparent made was that they were using dbplyr, which allows you to avoid having the data take a round trip between the R process and the database.

The dbplyr package takes your R code and executes it in your database, returning a remote dataframe. This is quite useful because there is no mvoving data back and forth.

We built the perfect IDE for dbt at Deep Chanel, with autocomplete automatic real time type checking for the whole project.

Sadly, the company closed this June, and even the website is already down.

You need to find a way to release this product. I would pay to use it.

It's already been released and publicly available for free for almost a year.

So where is it, if "even the website is already down"?

ETA: Seems it isn't. https://www.deepchannel.com/ That what you meant?

Hmm, may be I've had network issues last time I checked. Anyway, that's the only place you can get it — it's a completely free desktop app, but it's not open source.

This is extremely interesting. You might be onto something.

Imo dbt sits in the analysts space. SQL is the language they know and dbt is the best solution to scale that.

In that sense it should only be used for last mile data transformations. Not for wrangling raw data into neat tables.

I am curious, what do you perceive as the difference between data transformations and "wrangling raw data into tables"?

Edit: I am also curious why you consider SQL to be inferior for the latter?

The difference is that raw data can come in many shapes (e.g. impossibly nested jsons) and unexpected quality (changing field names...). I cannot easily work with this in SQL and definitely not write tests to cover the ever increasing complexity of dealing with messy data.

After that step the data is a lot more uniform, so then it's easy to use SQL.

Dbt isn't a substitute for pandas/R to a data scientist. They are complements. Dbt is for the data transformation pipeline that prepares the data so the DS can write simple queries using their favourite tools on curated data.

The core divide in this space is SQL vs. $OTHER_LANGUAGE. If you're fluent in SQL you'll be great at dbt. If you're only fluent in not-SQL you won't.

A lot of these discussions are basically "can we please just use the language/tooling we know", and sometimes the answer's yes! A lot of people breathe a big sigh of relief when they discover you can use like, Spark through Python and what-not. Regardless of whether or not it's a good idea or good use of resources, this space is advancing because the market demands it. But like, I don't think you're ever gonna use dbt with not-SQL; it's all in on SQL. Feels like maybe it was the wrong fit for your team.

I should say I'm not sure if your coworker was an SQL person so dunno if this directly applies. Just saying what my experience has been.

Y’all don’t have CI/CD? Maybe it’s hyped but we do simple stuff. Snowflake schema DWH. Jinja2 templated SQL or dbt. Airflow with a monolith tool that does transforms and such. Is it perfect? No. Is it understandable? Very much so.

Testing is such an interesting concept in data engineering. One needs consistent test data. We aim to implement that with snapshots eventually but now we have sanity checks at each layer.

> lacking industry guidance and generic tooling to support: CI/CD, versioned deployment artifacts, zero downtime deployments, unit testing, observability, monitoring and alerting.

Ci cd is doable and easy: changes are deployed via GitHub actions, the CI part is a bit missing I guess without tests. Versioned artifacts also, add a tag to the airflow job to know what tagged version of the code is running. Zero downtime deployments we do that all day — I mean using views we can a/b deploy changes to the underlying tables and then do a simple schema change to the view and nobody knows the difference. Unit testing still yet to be done. Observability and alerting we use internal dashboards and sentry.

What am I missing?

FWIW, we are building a CI/CD solution snowflake https://www.bytebase.com/docs/tutorials/database-change-mana...

Testing and monitoring in every aspect are the only things that are greatly lacking, you always need to glue something together to get nice tests or some kind of data contract monitoring.

Relevance and actual value, I'd assume.

> CI/CD, versioned deployment artifacts, zero downtime deployments, unit testing, observability, monitoring and alerting.

This definitely stood out to me when I was working with a lot of Snowflake’s new products, especially their “native apps”.

I started on some bespoke tooling given the lack of everything that you mentioned and at the time was thinking about how to productize it / turn it into some open source tools but I’ve not gotten back around to it since leaving that job.

I had a really great experience working with sqlglot from Tobiko Data https://tobikodata.com/ — haven’t had a chance to check out their sqlmesh project yet but I have some faith in their work.

Yeah seconding sqlglot, has saved me _tons_ of time. I think yeah, they're smart and onto something here.

A surprising number of companies (even large, well known ones) don't even have basic dashboards and metrics, most of what you mention is irrelevant to most companies.

dbt was a huge leap forward in this regard.

It enables source code control, modularity, testing, CI/CD, environments, documentation, git branching, local development experience.

Not a fanboy, but it's who reason for being is basically to enable "software engineering practices for data".

Yeah, that’s exactly what the Data team does here. It was researched, it was tested, it was implemented, it was proven, it gets improved from time to time, it works.

We Platform just help them test or setup some things from time to time, but that workflow they have with dbt works quite well, and all the things they’ve complained in these threads we have yet to suffer (at the magnitude people is complaining).

Not sure if there is something “better” these days, but these teams have not had a single major incident with their dbt workflow.

Yup, it is not perfect but I will take dbt every day against the old way as some random DDL files in git.

So basically its huge advantage is that it saves its code as text, which makes it gittable?

(Dang, and here the tool I've been designing in my head for several years was going to claim that niche... So unique. ;-)

Nah, the main thing is the dependency resolution. So you make pipelines like:

- employees

- tall_employees

- tall_employees_by_salary

These tables (dbt would call them models because they can also be views depending on how they're configured) depend on each other, so you have to build them in a specific order. Without something like this, you're manually running SQL to build your pipelines, and that's very error prone. With something like this you can run tests (they're also SQL in dbt, which is pretty common in data engineering teams), run your pipeline in CI, etc.

There's a bunch of other features, but they all hang off this basic idea.

> you're manually running SQL to build your pipelines

you can have some sh file which runs SQL in desired order..

> runs SQL in desired order

Sure, one at a time. dbt builds a DAG of the SQL operations and can run transformations concurrently.

and why would you need this? If the goal is to reduce e2e latency, then optimizing SQL is likely much more beneficial, since it can improve performance by NNN times, not just by N times.

Well there's 2 main reasons.

The first is that you can update a subset of your pipeline, like say you have an internal dashboard for server stats and another external dashboard that's a product for customers, and you notice a problem w/ the server stats dashboard. You can have dbt just build the models that underlie the server stats dashboard and also exempt any model used by the external dashboard.

The second is that you can effectively defer processing. dbt builds views by default (though it of course will also build tables, including ephemeral tables that are cleaned up after a build), which means your transforms don't take place until you query the data. If you can get away with it, this is what you want, and there are lots of ways to stay on views (caching, use BigQuery, etc). This means your pipeline at least has the potential of being "real time".

But, overall we're talking about pipelines that have 100s or 1000s of models. You need some kind of system for that; a shell script isn't gonna cut it.

You obviously can have modularity with shell scripts:

run_all_etl.sh

build_internal_dashboard_etl.sh (called from run_all_etl.sh)

build_external_dashboard_etl.sh (called from run_all_etl.sh)

No you misunderstand. If you have some kind of dependency tree that's like:

    A        B        C        D
        1        2        3
        i        ii
    Ai     i-ii

...and you want to only rebuild Ai and anything that it might rely on, all you do is `dbt build +Ai`. If you want to rebuild B and update anything that relies on it, all you do is `dbt build B+`

> You obviously can have modularity with shell scripts

Just as kind of a meta point, I think you're assuming that people are introducing a lot of incidental complexity into this problem, but I don't think you're open to the possibility that there's a lot of essential complexity here. I'm talking about DAG tools and the features that hang off of them, and you're saying "hey there's new thing called shell scripting". That's not useful in this conversation. Maybe going forward, let's assume people know about Bash.

In my example ETL is divided into modules with clear scope and dependencies, as I believe should happened in software practices.

If you have kitchen sink of 1000 tables chaotically connected to each other, then yes, you need DAG management tool to manage chaos..

> If you have kitchen sink of 1000 tables chaotically connected to each other, then yes, you need DAG management tool to manage chaos.

This describes 99% of data teams, so I think we agree!

> This describes 99% of data teams, so I think we agree!

there is a chance that dbt incentivies data teams to go that route, otherwise they would think about modularity and ownership more.

I'm a Vim user and I've made this point when I rail against IDEs; it's even on the front page today [0]: "5. The belief that complex systems require armies of designers and programmers is wrong. A system that is not understood in its entirety, or at least to significant degree of detail by a single individual, should probably not be built."

I think I agree, but I think there are a lot of things you could say about this. What if you're just starting out and you don't know how to efficiently design systems? What if you work in an industry of people under 40 (under 30?) who by definition can't have the kind of experience that would let you design small systems? What if you work for a company that doesn't want to give you the time to figure it out? What if you have a competitor that's just shipping trash and forcing you out of the market with a "we'll clean up the tech debt later" mentality? How do we make tools that are powerful enough to enable us to do hard things, but wise enough to keep us from doing foolish things? Should we rely on our tools to do this? Is this an argument against centralization? Isn't that deeply inefficient?

Honestly I struggle with this all the time. I've got the outlines of some ideas and there are some tech communities grappling with the same issues, but I think we're just at the very beginning of a rethink about how we do software.

[0]: https://liam-on-linux.dreamwidth.org/88032.html

> How do we make tools that are powerful enough to enable us to do hard things, but wise enough to keep us from doing foolish things? Should we rely on our tools to do this?

IMO: No we shouldn't. And that's why I'm in the "Think about it yourself, bulid your ETL with a clean structure so you know what your 1,000 [SQL|shell] scripts do" in stead of the "Let your tool figure it out for you" one.

> Is this an argument against centralization? Isn't that deeply inefficient?

Or for centralization, if we hark back to the "You can get a loooong way on a single server" argument above. Depends on how you define "centralization", I suppose. (Seems to me everything always depends on your definitions, in the end.)

But wait, does this still have anything to do with IDEs? As opposed to Vim (or, say, Notepad)? If so, I'd say IDEs also belong to the category good centralization. At least older ones, before they became desktoppified Web apps with baked-in AS (Artificial Stupidity)...

> Honestly I struggle with this all the time. I've got the outlines of some ideas and there are some tech communities grappling with the same issues, but I think we're just at the very beginning of a rethink about how we do software.

On that debate I'm not so much conservative as... Reactionary.

How many models are in "run all etl", and what if one fails? You can't see how issues can arise here, with what you describe? I mean, go ahead and write your SQL pipeline in bash. Many of us are using dbt because we see a lot of benefit, we aren't all morons.

> How many models are in "run all etl", and what if one fails?

hundred tables. one fail -> whole pipeline fails to ensure e2e and overall consistency.

> and why would you need this?

So things finish considerably faster?

> then optimizing SQL is likely much more beneficial

This only gets you so far. Running transformations one-at-a-time when the server can do more is just holding you back. My last dbt project had over 300+ tables and running transformations serially would have taken an order of magnitude longer than running concurrently.

> Running transformations one-at-a-time when the server can do more is just holding you back

in general, the end goal of such ETL optimization is to have all your CPU or IO saturated. But I imagine for 300 tables transformations it could be too much work.

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.

> 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.

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.

This is an old sub-thread now, but I would agree that dbt is a very simple idea and easy to use at its core. Store your models in text files, in git, add a dag. It’s easy to use and very lightweight so I don’t see any reason to roll your own.

Then you get things like testing, documentation, modularity if you want it.

Reply on news.ycombinator.com