Hacker News Reader

Comment by jameslk | Hacker News Reader

One of the experiences that stuck with me most going from doing software engineering stuff to data engineering stuff is the very difficult and sometimes complete lack of tooling around testing and debugging issues in SQL and pipelines. As software engineers, we have debuggers, unit testing, mocking frameworks, e2e tools, BDD languages like Cucumber, code quality tools, etc. But when you're working on a pipeline and you're wondering what will happen if you run it, sometimes your best bet is actually to just run it and wait 20 min for it to complete. Or run some portion of it ad hoc. Or if you want to know what a SQL query is going to do in the black box of your query engine, you might try to parse the esoteric language of a query explainer. The best tools seem to be available after deployment such as data quality tests, dashboards and alerts. I think there's a lot of opportunity to improve the ecosystem.

View on HN

Agreed. Outside the world of ML (but working with reasonably large data, ie a few billion rows of server log entries in varying formats) I had some success grabbing a narrow slice (single-digit millions) and experimenting w/ ways to query it using combination of regex and SQL, thanks to an amazing little powertool called lnav (https://lnav.org). It's like an intuitive, scriptable, mini-ETL on the commandline you can learn in no time. I once used it to track down the root cause of a p0 in prod, in parallel to the ops gurus and most senior devs using dedicated tools and processes; we arrived at the same conclusion around the same time, and the stuff I'd extracted helped speed the patch. Felt pretty good, and I've been singing lnav's praises ever since.

Shoot I must be getting old, rambling on a tangent. Get off my lawn! ;)

Writing this comment as I 'actually run it and wait 20 min for it to complete.'

I totally agree. There has been some progress here recently. Have you checked out DBT and their testing features?

https://docs.getdbt.com/docs/testing

Just because people don't write tests and use tooling doesn't mean it doesn't exist!

When I write pipelines I write unit tests for the business logic and integration tests for the overall process.

Whether you use SSIS, Alteryx, Spark whatever you can write tests and you can debug using known inputs, rather than running an entire pipeline to see that is works!

These are my typical thoughts on ETL testing:

https://the.agilesql.club/2019/07/how-do-we-test-etl-pipelin...

https://the.agilesql.club/2019/08/how-do-we-prove-our-etl-pr...

https://the.agilesql.club/2019/09/how-do-test-the-upstream-d...

https://the.agilesql.club/2019/10/how-to-test-etl-processes-...

I have started using prefect.io as a workflow runner. It helps me define tasks and deeply define what should happen on failure of a task including cleaning up a cluster.

Because Tasks are just functions you could test your tasks with regular unit testing and mocking methods.

But I agree a lot of data people seem to think interactive is good enough.

How do you think Prefect compares to Airflow / Dagster?

What makes prefect stand out is the highly pythonic API and the powerful primitives for defining failure scenarios. Additionally, for me, integrating with dask is key as dask has become one of my main tools.

Heres a rewrite of the airflow tutorial in prefect: https://docs.prefect.io/core/examples/airflow_tutorial_dag.h...

My advice is try it out and see if you like the api. I think that the airflow ui is still a killer feature in 2019 though.

Theres also this post from the creator of prefect on why not airflow: https://medium.com/the-prefect-blog/why-not-airflow-4cfa4232...

I haven’t tried dagster yet, how does it compare to airflow?

By the way prefect is mostly a wrapper around dask.delayed

There are guys who generate synthetic testing data so you can get "looks like real data" test fixtures. I used to work in CRM with PII and honestly I just put in my own details as test fixtures more often than not where you want to run final integration tests on whether the whole thing actually handles 'realish' data (you have to filter out all the junk `example.com` and whatnot emails in prod). Should've used the synthetic data in the first place because someone at our company accidentally opted out the CTO while testing out the flow and our integration tests broke.

> if you want to know what a SQL query is going to do in the black box of your query engine, you might try to parse the esoteric language of a query explainer.

Possibly a side effect of nosql movement/proliferation that these basic things became esoteric. Like a vet care for dinosaurs :) The tooling around data pipelines, like debugging, profiling, lineage, etc was too a typical stuff of the ETL tools of yesteryear like Informatica for example.

Occasionally I ask what the world would look like if we had a web browser that was designed from the first to be testable. Would e2e testing be simple and repeatable?

I could just as easily ask the same about databases, or dozens of other tools in our toolchain. Makes me wonder if there's space for something like SQLite and testable.

Probably Datomic. Databases are hard to test because they're big piles of global mutable state. A database designed around values, pure functions, and other FP constructs should be inherently easier to test.

Agreed. SQL being declarative, is primarily why there haven't been many tools to augment the development process. Same goes for machine learning, albeit SQL is not fuzzy like ML. No way to know whether a model "works" unless you train and run it.

Reply on news.ycombinator.com