51 ms·
SQL should be the default choice for data transformation logic
- nojito 4y agoNo it shouldn't. The verbosity of SQL makes it unwieldy to maintain for pipelines.
- RobinL 4y agoWhat do you recommend instead and why?
- nojito 4y agoWe have migrated close to 90% of our sql code to Dask/Ray and we could not be happier.
- noloblo 4y agoDoesn't declarative style of sql make it less verbose than say an imperative style
- blowski 4y agoA lot of SQL is not particularly declarative. You know precisely how you want it to run and you keep writing code until it runs that way.
- ontologiae 4y agoI use to write complex SQL query very usually, and I can say that it's far more concise than imperative code. Compared to it's own semantics, SQL is actually verbose, but its semantics is so powerful that you gain size.
- nojito 4y agoPlease share how many lines of sql it will take to statistically normalize multiple columns. Or even something simple like null cleaning columns based on dynamic thresholds.
- ontologiae 4y agoFor normalization, for instance : Select stddev(col1)/avg(col1), stddev(col2)/avg(col2),... from mytable group by key. One line.
- nojito 4y agoThat's just a handful of columns. You have to manually type out each and every arrangement. Add in if you want the new columns to be added in place and you have to bring in window functions.
- simonw 4y agoCan you expand on what you mean by verbosity here? I've found that using CTEs has really tightened up my larger SQL queries, and made them a great deal easier to understand and maintain in the future.
- BenoitP 4y agoVerbosity ? In the accidental vs essential complexity dimension, it is the most dense language in essential complexity I know (for the value it provides). This simple command `UPDATE users SET preference = 'blue' WHERE id = 123` virtually contains: * concurrency control * statistics, which will help: * execution plans evaluation, which will reduce IO cost with the help of: * (several categories of) indexes * type checking * data invariants checking * point in time recovery * enables dataset-wide backup strategies * ISO standard way of structuring data * an interface that empowers business people And that's just a few items off the top of my mind. Even APL is less dense. There are no execution plans in APL.
- deleted 4y ago[deleted]
- randomdata 4y ago> it is the most dense language in essential complexity I know QUEL is more dense for equivalent functionality: replace users (preference = "blue") where id = 123 Not to mention that it actually adheres to relational calculus, unlike the wild and reckless SQL. > * type checking It is only dynamically typed, though, which isn't all that useful. There is good reason why we are seeing static type systems being bolted on to most dynamically typed languages these days (e.g. Typescript). SQL would do well to add the same, but it seems it is seen as a sacred cow that cannot be touched, so I won't hold my breath.*
- deleted 4y ago[deleted]
- noloblo 4y agoWhat's duckdb Why is it needed over say postgresql or mysql
- RobinL 4y agoDuckDB is a no-dependency SQL engine that's available from multiple programming languages. A bit like SQLite but optimised for analytics. For instance, in Python, it can be installed using `pip install duckdb`. For things like unit tests, it can be very valuable because no additional services need to be running.
- simonw 4y agoIt's a really interesting new (emerged in the last few years) piece of technology. It's kind of a cross between SQLite and columnar analytical databases such as Snowflake or Cassandra. You can run it happily on a laptop, but because it's column oriented it can calculate aggregates (group by/count queries for example) incredibly fast - so it's better for a lot of analytical workloads than regular row-oriented databases. I have some initial notes from trying it out here: https://til.simonwillison.net/duckdb/parquet https://til.simonwillison.net/duckdb/parquet
- _benedict 4y agoCassandra isn't columnar, it's row-oriented. The original authors coined a new term "column family" which is just a nonsense (and confusing) term, meant to describe its original relatively unstructured API, which has unfortunately stuck in all descriptions of the database, and leads to confusion like this. Cassandra is also not an analytics database, it is intended for "OLTP"-like workloads.
- jjtheblunt 4y agoI must be jaded because every decree of the form “you should…” causes me skepticism that a salesman is at work.
- RobinL 4y agoAuthor here. Appreciate the feedback. I agree It's a tricky stylistic choice. I did think about it, and decided to go with it this time. The rationale was to avoid interrupting the flow with writing 'in my opinion', or 'I recommend' multiple times. I have tried to be balanced though, and explain there's is no 'one size fits all' solution. FWIW everything I reference in the blog is open source - I have no commercial motive.
- jjtheblunt 4y agoYes, your work looks very cool and pertinent to me personally; well done and thanks for sharing it too.
- eatonphil 4y agoOne of the few lessons in school that is emblazoned in my mind is a writing class in middle school in the US (in central PA) where they deducted points for the use of first person in opinion pieces. The reasoning was that it's obviously an opinion piece, not law. Any use of first-person would be redundant.
- jjtheblunt 4y agowas the same in Ferris Bueller style suburbia around Chicago
- jjtheblunt 4y agoupdate: this author's work is just cool stuff
- woooooo 4y agoMost SQL centric projects I've touched had a severe lack of automated tests. Not saying it can't be done but that's my experience.
- simonw 4y agoI'd love to find a SQL testing tool that has similar ergonomics to pytest. ... or for someone to build a tiny framework on top of pytest that helps write automated SQL tests.
- drewcoo 4y ago> SQL testing tool that has similar ergonomics to pytest What exactly does that mean? You can write SQL queries and run them through PyTest and if you do it in CI there's no repetitive human injury involved.
- simonw 4y agoSure, you can do that: but most people don't. I would be delighted to see a tool grab enough mindshare to become the safe default testing tool for SQL code, in a similar way to pytest in Python world.
- heywhatupboys 4y agoIf you praise pytest as some unique test runner, I pressume you haven't worked with many actually good testing engines... SQL has more than enough to compete with the stdlib og Pythong for testing
- simonw 4y agoIf you can point me to testing frameworks that are better (as in more enjoyable to use) than pytest I would LOVE to hear about them. (pytest isn't part of the Python standard library.) Especially if you've got some suggestions for JavaScript, where I have yet to find anything that makes me enjoy test writing in the same way that pytest does.
- tremon 4y agoTo me, data pipelines are about moving data from one place to another. and that's not really covered by the SQL spec. The article doesn't specify what constitutes a "data engineering pipeline", but for me there's three components to it: - orchestration, or what happens when - data manipulation, aka the T in ETL - data movement, getting data from A to B Orchestration? Please do not use dynamic SQL, you're digging a hole you'll never get out of. Moreover, SQL has zero support for time-based triggers so you'll need a secondary system anyway. Manipulating the data? Sure, use SQL, as long as you're operating on data from one database. Trying to collate data from multiple databases may have unpredictable performance issues even in the engines that support it. Moving data? There are much better options. And the same caveat as for orchestration applies here: every RDBMS vendor has its own idea of what external query's should look like (openrowset, external tables, linked servers) with their own performance considerations.
- kasey_junk 4y agoEven on the manipulation side I tend to disfavor sql. Even though it’s improved a lot the testing situation in sql is still not where a more traditional programming language is, modularity comes either in the form of ever nested cte, stored procs or dbt style templates. And sql types are wholly dependent on the sql engine, which can lead to wacky transforms. Sql is great for adhoc analysis. If something isn’t adhoc, there is almost always a better tool.
- marcyb5st 4y agoAgreed. I would also point out maintainability. How do you test some SQL logic in isolation? Additionally, in this day and age where enriching the data by running it through some ML model isn't that rare, doing it in SQL by exposing it through an API and invoking some UDF on a per-row basis is extremely inefficient due to network RTT. In my opinion it is much better to use something like Apache Beam and load the model in memory of your workers and run predictions "locally" on batches of data at the time. On the other hand I see the value in expressing "simple" logic in SQL, especially when joining a series of tabular sources. That's why I am super happy with Apache beam SQL extensions (https://beam.apache.org/releases/pydoc/2.30.0/apache_beam.transforms.sql.html https://beam.apache.org/releases/pydoc/2.30.0/apache_beam.tr...) which, IMHO, has the benefits of both worlds.
- prepend 4y agoI’ve found that sql pipelines end up depending heavily on a specific database (ssis packages for sqlserver) so really suck when you don’t want to use that database any more, or still need something to coordinate and run all the sql. So if I have to have something to run all the sql and test it , etc etc why not do the pipeline there? You can still include sql in the pipeline for the parts in database. But trying to do everything in database can be unwieldy. For example, pulling a csv from a file system, pulling a json from an api, linking them, and storing the output as a csv somewhere. Doing that in sql is possible, but why? I think having a mixed bag of tools for the pipeline is the right default. And having something highly portable for the top layer of orchestration is probably not going to be sql.
- jeltz 4y agoIsn't that true for all tools which currently exist? I do not think there exists any such highly portable thing.
- JoBrad 4y agoNot in my experience. Handling transformation in e.g. Python doesn't require you to know much about the SQL language variant that the db is using.
- prepend 4y agoPython scripts are really portable and can run in lots of environments (including in sqlserver).
- throwayyy479087 4y agoThis is the 1 advantage of ORMs - makes apps db portable.
- bruiseralmighty 4y agoI agree for one-offs and for simple mappings. If I had to do this problem as part of some personal workflow used only by myself, then I would just use `pandas` or some equivalent for the entire thing and have it live in a jupyter notebook. However, if the mapping is even somewhat complicated, or this pipeline has to be shared and productized in some way, then it would be better to load the data using some `pandas` like tool, store it on a `tsql` flavored database or datalake, and then exported as a .csv file using a native tool or another `pandas` equivalent again. Having a pipeline live solely on a notebook that is passed around leaves too much risk for dependency hell and relying on myself to create the csv as needed is too brittle. Either have the pipeline live on its own container that can be started and run as needed by anyone, or dump the relevant data into the datalake and perform all the needed transformations there where the workflow can be stored and used repeatedly.
- gibsonf1 4y agoYou could also argue that the 20th century relational database is a terrible way to model information as it is so different than the way we humans actually do. Hierarchies are a key to information modeling for us, but a disaster with relational. We do not store information in countless tables with increasingly akward joins between them, we think in terms of relations between things and their hierarchies of relations. One thing is not in 20 different places referenced via a key id, its just in one place mentally linked in relation to many others. For this reason, the noSql databases, like Apache Solr etc (which are open source at no cost), are far superior to actually capturing information in a more human way, and doing it at far bigger scale and faster. So I would say the last thing you would want to start with on any modern knowledge/information system would be SQL.
- eska 4y agoYes, humans think in hierarchies, but they think in multiple hierarchies. You’re in one hierarchy at work, another in your family, another at your Karate dojo. This cannot be represented in a single hierarchy. In non-relational you therefore end up duplicating data and suffer the associated ACID and performance issues. The only reason people are fine with it is because they don’t care about data integrity and won’t fix these problems in hindsight, but that’s what the non-relational database developers tell them to do since they declare it’s not their responsibility.
- simonw 4y agoWhat you're saying sounds good on paper, but I don't think real-world experience backs it up. I've not seen a non-relational system really have sticking power for human knowledge representation yet, despite many people making many attempts at it. Meanwhile, most SQL databases include excellent support for JSON column types now, if you want to mix in some data that doesn't fit neatly into rows and columns.
- gibsonf1 4y agoWe are using it for our conceptual AI with space-time digital twin, and so are many others: https://solr.apache.org/community.html#powered-by https://solr.apache.org/community.html#powered-by
- d_watt 4y agoWhat I personally like about SQL here is that you can start to think about your transformations in a purer "relational algebra" sense. You're able to declare the expected outcomes, and let the engine figure out the best way to handle that, as opposed to actually having to figure out how to join, aggregate, window, etc. SQL transformations shine in environments where regular batching in short intervals is acceptable. Materialize and Flink are both super interesting for overcoming the batching shortcoming into realtime materialization via SQL. Regarding the concerns about tests, it's true it's harder to do unit testing of SQL, but for me the tradeoff is it feels like there's a whole class of bug that's eliminated by focusing on the relational algebra. If you can avoid the category of work of defining the procedures to transform the data, and only define the expected outcome, things can be much simpler.
- jaggederest 4y agoIn my experience, testing SQL is just like any other language. You define input data, execute the script under test, and then check the output (and possibly the state of the system) to confirm. The issue is more pronounced when you start doing DDL on the fly, but as long as you generally confine yourself to extracting data in a specific format, it's not too hard. I've done it entirely in sql - you can have a "test data" table and compare it to a "results" table that gives you an oracle of truth*. The best is when you have rollups or other basic math that needs to be tested, so you can do parallel calculations (in another language or by hand) to ensure that the math is right. The worst is when you have small format changes that alter e.g. the order of the output - then you're left with either making your tests order-invariant or twiddling them for every ORDER parameter, which can be frustrating. *Truth is only as good as your ability to input results, of course.
- revskill 4y agoI'm working on a YAML to SQL tool, which allows simple macros at https://yaml2sql.netlify.app https://yaml2sql.netlify.app With structure, SQL is not hard to read and write actually. You can ask why ? I would say for maintainability reason.
- heywhatupboys 4y agoare you seriously consider this declare: eql: operator: = left: $1 value: $2 get_sum: operator: $1 sum: apply: ["get_sum", "sum"] args: - $1 group_by: field: $1 main: from: payments alias: p distinct: true select: - apply: ["sum", "i.views"] - apply: ["sum", "i.clicks"] - apply: ["sum", "p.amount"] where: - apply: ["eql", "user_id", "12"] group: - apply: ["group_by", "date"] more readable than this? SELECT DISTINCT SUM(i.views), SUM(i.clicks), SUM(p.amount) FROM "payments" "p" WHERE user_id = 12 GROUP BY date
- revskill 4y agoAs i said, it's about maintainability, not about length ;) All that declaration can be reused and implicitly imported. SO main query is just simple as main: from: payments alias: p distinct: true select: - apply: ["sum", "i.views"] - apply: ["sum", "i.clicks"] - apply: ["sum", "p.amount"] where: - apply: ["eql", "user_id", "12"] group: - apply: ["group_by", "date"]
- simonw 4y agoI'm not seeing the readability benefit over this (which I reformatted and converted to lower-case, personal taste): select distinct sum(i.views), sum(i.clicks), sum(p.amount) from "payments" "p" where user_id = 12 group by date
- revskill 4y agoThen try this query instead ? https://yaml2sql.netlify.app/play/crosstab/ https://yaml2sql.netlify.app/play/crosstab/ How do you think ?
- exabrial 4y agoThe point that stood out to me was SQL and strong types. I still don’t understand the fear of data types, even when dynamic language programmers still treat their variables with invisible types, and leave the guessing game to the next guy. There are only a handful of static values in the known universe that don’t have types or units. Intentionally avoiding types is irrational (math pun).
- randomdata 4y agoSQL is strongly typed, but not statically typed. It is dynamically typed. Like you say, it leaves you guessing what the variables might be and waits until runtime to blow up if you made a mistake. I don't understand the fear of types either. We can let the lack of static typing pass for early revisions of SQL as perhaps we didn't know any better back then, but how has modern SQL not caught up to the modern age of software engineering? Even Javascript/EMCAScript is starting to introduce static typing features, and that's a low bar to contend with.
- mytherin 4y agoMost implementations of SQL are not dynamically typed - they are statically typed. There is an explicit compilation phase (`PREPARE`) that compiles the entire plan and handles any type errors. For example - this query throws a type error when run in Postgres during query compilation without executing anything or reading a row of the input data: CREATE TABLE varchars(v VARCHAR); PREPARE v1 AS SELECT v + 42 FROM varchars; ERROR: operator does not exist: character varying + integer LINE 1: PREPARE v1 AS SELECT v + 42 FROM varchars; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. The one notable exception to this is SQLite which has per-value typing, rather than per-column typing. As such SQLite is dynamically typed.
- randomdata 4y agoThat is strong typing. Python, a dynamically typed language, exhibits the same quality: >>> v = "" >>> v + 42 Traceback (most recent call last): File "<stdin>", line 1, in <module> TypeError: can only concatenate str (not "int") to str Types are most certainly not static (at least not in Postgres or most other implementations): CREATE TABLE varchars(v VARCHAR); ALTER TABLE varchars ALTER COLUMN v TYPE INTEGER USING v::integer; PREPARE v1 AS SELECT v + 42 FROM varchars;
- deleted 4y ago[deleted]
- thedudeabides5 4y agoWhat if you are trying to get data into and out of spreadsheets?
- deleted 4y ago[deleted]
- simonw 4y agoIt appears to me that many spreadsheet users have started to realize that - for spreadsheets that are being used to store data - there's a lot of value in sticking to a rigid rows-and-columns format for those sheets, rather then messing around with creative layouts. At which point importing them into and out of database tables becomes a whole lot more feasible.
- tremon 4y agoThen you're not a data engineer, you're an administrative worker.
- whiddershins 4y agoI’ve been working on a large project where the team made exactly this decision, and it has been eye opening. Using SQL as the primary data analysis language ends up being very powerful and straightforward. I second the author’s recommendation.
- clircle 4y ago> These alternative tools were developed to address deficiencies in SQL, and they are undoubtedly better in certain respects. Specifically, dplyr and pandas are handy because they live inside a complete programming language, which is something you might need if you are working on a data project.
- rwhaling 4y agoThe biggest win to me is: when your data pipelines are in SQL, changes and maintenance can be somebody else's problem. I've had a ton of success asking our marketing and business teams to own changes and updates to their data - very often they know the data far better than any engineer would, and likewise they actually prefer to own the business logic and to be able to change it faster than engineering cycles would otherwise allow.
- ramraj07 4y agoDo you mean you ask them to edit the sql? With some oversight this can be fine but non engineers can easily end up making the sql infinitely slower not to mention get things wrong (most commonly doing inner joins or have nulls in where in joins).
- tracker1 4y agoI've seen plenty of devs and da's do the same. The nice thing about SQL is it's easy enough to create a query that gives you what you want, but at scale it falls flat or purforms poorly. It's easy enough to work through most of the time, but too many lack the understanding of knowing when bottlenecks are likely to happen, and if/when it may be an issue. I think of more than a couple basic joins in a query to be a code smell.
- bob1029 4y agoWe do the same thing with our business logic & product configuration. I still haven't found something I couldn't expose as a SQL configuration opportunity. Even complex things where you have to evaluate a rule for multiple entities can be covered with some clever functions/properties.
- philmcp 4y agoI'd say SQL is the most underrated engineering skill out there. It amazes me when competent developers / analysts / data scientists don't know (any) SQL. Have they stopped teaching this in University or something?
- Kon-Peki 4y agoMy memory could be a little hazy, but I don't remember any required course that dealt with SQL when I was in the CS program at a pretty highly-regarded university 25 years ago. I took a course in which I learned quite a lot about SQL and in retrospect it was an extremely useful course to have taken.
- SCdF 4y agoWe did some in CS101/102 (first year required CS courses). Specifically normalised forms and the like.
- robertlagrant 4y agoWe had to do it in the UK, circa 20 years ago.
- ncphil 4y agoI avoided learning SQL in the client-server training program that got me started in tech back in the late 90s, only to have to learn it on the job during a global ERP deployment a decade later. Should have taken the class, would have meant at least a few less sleepless nights.
- icedchai 4y agoI remember taking a databases class (either junior or senior year) that covered database design and also SQL with Oracle. We even got into Pro*C, which was some crazy Oracle-specific C pre-processor. It definitely wasn't required. My roommate took it and failed.
- edejong 4y agoAt a Dutch CS study we had an SQL course that started with first-order logic, relational algebra and went to on to project that into SQL. It also taught 3NF/4NF and BCNF, indices, r-trees, query planning and optimisation.
- Pxtl 4y agoThe problem is that the default/naive mode for an ETL in most ACID SQL implementations is "lock every table you're touching and give zero feedback as to how long it will be locked". Eventually you end up writing more and more cryptic code to work around the fact that this is not something supported out-of-the-box in any mainstream RDBMS. Now, should it be better? Would I expect a vendor-supported solution like Microsoft SQL server to provide a simple tool that lets me write a Merge statement with the understanding that I want it to be eventually-consistent rather than expecting me to spend weeks learning yet another tool with it's own DSL to handle this case? Absolutely.
- brugidou 4y agoShameless plug of our internal "sql first" pipeline system powering large scale data sets. https://medium.com/criteo-engineering/scheduling-data-pipelines-at-criteo-part-1-8b257c6c8e55 https://medium.com/criteo-engineering/scheduling-data-pipeli...
- deleted 4y ago[deleted]
- hellodanylo 4y agoI find SQL very hard to use when the data schema and/or transformation graph needs to be dynamic (e.g. depends on the data itself). It's hard to make SQL dynamic even at build time -- Jinja+SQL is one of the worst development experiences I have ever had.
- bob1029 4y ago> I find SQL very hard to use when the data schema and/or transformation graph needs to be dynamic If the schema is "dynamic" then I'd accuse the business of being poorly-defined and not worthy of any development time.
- gilbert_vanova 4y agoThat's great except for when you're interacting with a decrepit data system from 10 years ago with a variable record format. Some things can't be locked in stone, and SQL will leave you out to dry when that's the case.
- lolive 4y agoI use graph database, and resources of the graph are typed with types/supertypes. Relationships also are typed with types/supertypes. And my queries are heavily dependant on that typing structure. Honestly, I cannot live without that feature. [sorry, that is my OOP minute. Continue without me...]
- thomoco 4y agoAgree with the OP that SQL will almost assuredly still be in use for 20+ years in the future, given the simplicity and flexibility of the declarative language, standardization, and as applicable to today as it was then to our big data problems. Any discussion of SQL at scale must include ClickHouse [https://clickhouse.com/docs/en/install#self-managed-install https://clickhouse.com/docs/en/install#self-managed-install], given it's broad open-source use, integrations available for Spark with JDBC [https://github.com/ClickHouse/clickhouse-jdbc/ https://github.com/ClickHouse/clickhouse-jdbc/] or the open-source Spark-ClickHouse Connector [https://github.com/housepower/spark-clickhouse-connector https://github.com/housepower/spark-clickhouse-connector], and capability to scale SQL as a network service. Disclosure: I work for ClickHouse
- deleted 4y ago[deleted]
- znaimon 4y agoClickHouse is unparalleled in terms of performance at scale.
- pletnes 4y agoSurely, AutoHotKey should be the default. Most «data moving people» are not programmers. Okay, maybe that’s «data pipelines», not «data engineering pipelines», but…
- lolive 4y agoCopy/paste is the most widely used data pipeline in the world. Sounds to me like a natural winner for data management. [but AutoHotKey is definitely a valid #2 !]
- logicalmonster 4y agoSQL might be the right choice in many or even most situations, but I'm really skeptical whenever somebody says there's a "default choice" in technology. Every system has certain strengths and weaknesses compared to others. If you're only reaching for one technology choice without first assessing the project in context with all of the tradeoffs involved, I don't think you're doing things right.
- tracker1 4y agoI'd say that A SQL variation is probably the right first choice for most software projects where server stored data is involved. As much as I enjoy other options (Mongo, Cassandra, BigTable, Dynamo, etc) in different scenarios. A lot of this will come down to many developers do use SQL first... Especially in Java and C# circles (corporate it developers in particular). So staying closer to what people are familiar with has value. I will generally push for PostgreSQL or CockroachDB over other SQL varieties though. MS-SQL is okay, Oracle can be a pain, both being costly. Not a fan of Maria/MySQL only because of old behaviors, and every time I've used it, there's something annoying (utf8 isn't, as an example). In either case, a SQL database service can often scale to the low millions of users if you're pretty good with how you structure things... A poorly constructed database and application can generally scale at least to thousands of users without issue. As most application development are internal business applications, that's usually enough.
- 8note 4y agoA default choice really means to check your context for the weaknesses of the default. If you aren't going into something tool is specifically bad at, use it. Default choices are an agile optimization -- they make sure you don't spend a lot of time on solving problems that you might not have. The detailed tradeoffs for the best and worst tool for a job are context dependent, and that context changes over time. Your default choice will be good enough under most contexts.
- lolive 4y ago"Make data typing someone else’s problem" Ok. Count me out !
- makach 4y ago<XXX> should be the default choice for <YYY> I agree, and disagree. Thankfully new technology ZZZ challenges the current paradigm XXX and ensures continous improvent in area YYY. Also XXX picks up the cool stuff from ZZZ (occasionally).
- BulgarianIdiot 4y agoI'm fine with this, but SQL has some lingering shortcomings: - Inability to express recursive/nested datasets directly (tree-like). - General inability to express structural sharing and graphs directly. - Inability to express associative (map-like) relationships directly. - ... Some of these are solved in the SQL standard, but not universally adopted. Others are solved by particular databases, but also not universally adopted. For the rest, of course you can solve it if we add the condition "some assembly required" when you get the data. But if you have to assemble and disassemble the data at every step, it stops being a viable pipeline choice. It's as good as serializing into CSV, JSON, or whatever. In fact, JSON at least can nest.
- tracker1 4y agoLargely my take as well... I will say, I'm not a fan of using the DB Engine to self-injest or export data... I do prefer newline delimited json for import/export as it tends to be easier than CSV usually is. The variations in SQL dialects/engines are pretty broad... and in some cases (mysql, ugh) straight ANSI SQL syntax may or may not work as expected. Not to mention more esoteric data like JSON or XML data columns. One other niggle, don't do data analytics against your live database servicing your applications... use a read mirror or replica node... The types of queries data analysts tend to run are often less than ideal in terms of performance characteristics. Developers can create bad enough queries as it is, let alone a DA query locking a key table for many seconds.
- raverbashing 4y agoSQL doesn't even have a standard way of creating tables and getting the table structure It is frankly disappointing that something like a basic WordPress installation is not portable across MySQL/Postgres/MS/ORACL, etc
- thehappypm 4y agoIt’s definitely true that making a dataset into something SQL can elegantly handle is not always easy, for example, dealing with JSON. But that’s also kind of the point. It thrives on flat, relational data. And having that relational layer makes doing things with data very easy.
- CalRobert 4y agoI've joined projects for a few big data pipelines now and every single time people were using R (shudder) or Pandas for what ultimately could have been done with SQL (ideally with DBT, which makes testing much easier). And every time it was because data scientists apparently thought every single problem needed R or Pandas and SQL was beneath them.
- benjaminwootton 4y agoFortunately the field is splitting into data engineers who build the pipelines and data scientists who analyse the results. I think that’s a good thing as the pipeline should look more like software engineering.
- CalRobert 4y agoUnfortunately I have found myself dealing with trying to "productionize" appallingly bad code from DS. https://news.ycombinator.com/item?id=33787270 https://news.ycombinator.com/item?id=33787270 has a good discussion on this.
- usgroup 4y agoYeah except that SQL is not composable and managing 1000s of lines of SQL in a repository without much more than partial means for abstraction (e.g. views, UDFs, etc) is unattractive.
- shireboy 4y agoI recently ran into this dilemma. I was tasked with writing a feature to allow importing a bunch of CSV into a system. Do I 1) stand up Azure Data Factory and build a pipeline to take the CSV from blob storage, massage it, and upsert the db or 2) write a few lines of C#+SQL to suck in the CSV and do the same? #2 is not quite as trivial as it may seem in real-world. Customer wants preview of data before update, undo capability, etc. But even so, I went with #2. I'm wary of doing things because they seem "easier" to me, but these huge data pipeline tools just seem like overkill at least for the use cases I'm tasked with.
- benjaminwootton 4y agoMost people don’t actually have a big data problem. If you can load and manipulate your CSV in memory within your app then it’s not worth all of that data infrastructure.
- RobinL 4y ago2) seems very reasonable to me. as a Python user, I'd have done something similar using the Python duckdb library (i.e. steering clear of any heavyweight tools)
- intrasight 4y agoMuch of my current work is supporting a large data warehouse in the energy industry. Most of the ETL code is SQL. I hate it but I know that there's no good alternative. Lots of the ETL stored procs are >1000 lines of code. There is lots of duplication - of code and data. There is no testing. There is little documentation.
- BiteCode_dev 4y agoSo any transformation thay requires accessing something else than the data must be done in sql? Connecting to a ftp, parsing a yaml file, getting data from a rst api? And then text, date and maths opeartiond also in sql? Yeah, no. Sql is good at look up and filtering, but not for linking heterogenous sources and massaging data.
- sn_master 4y agoNo. SQL lends itself to write-only code. Even if it starts with good intentions, over time almost always things get out of hand.
- awill88 4y agoDisagree, there should be no default choice.
- anon223345 4y agoOh hell no, schema.org Ever tried doing deep analysis on metadata or semi-structured data with SQL tables? Ill stick with the format Google Knowledge Graph, Facebook Opengraph, and numerous other graph data firms trust…
- ww520 4y agoData engineering pipeline is basically ETL. SQL is for running reports. ETL can have many variances, especially with so many different kinds of data source, structured and unstructured. A new kind of data source (e.g. video) would require a new way to extract useful data, transform it into some useful forms (e.g. products mentioned in video), and load them into the common stores (e.g. RDBMS). Then can use SQL to manipulate the data and run reports.
- gigatexal 4y agoThere is a ton that can be done in SQL to transform data after data is bulk exported into staging tables. I really do like the ELT transition from ETL. Which is kind of nice as the DB is usually really beefy in terms of compute and if distributable like say BigQuery it just scales seamlessly (provided your credit card has a high enough limit) and you don't have to worry about all the distributed systems stuff you might have to deal with if you were running a Spark cluster on your own.
- lysecret 4y agoFunny to me ETL was always about getting data to a format where you can then use SQL to run whatever on. ETL means a lot of very different things to different people.
- papandada 4y agoYes, this was my world for a number of years too. And now "suddenly", it's not. All the "niche" stuff I started hearing about in the early 2010s seems to have hit critical mass to even be the norm in boring industries' enterprise world.
- listenallyall 4y agoI kind of think the pivoting to a new concept keeps arising because there is no magic solution. Data is hard. And yet since everybody is pretending to be experts in data mining, AI, ML, corporate execs feel left behind because they either don't have these people, or the team they do have isn't producing much, if anything, of value. Every company/enterprise has more and more data yet it isn't translating to knowledge. The existing data infrastructure isn't working, some new idea comes along and gains steam and everyone thinks it will fix their problems, it won't, and then it too will become the solution that needs to be replaced by the next new idea.
- Izkata 4y agoNote in the comments here people have used ELT instead of ETL a few times. It's not a typo, doing the "load" before the "transform" specifically refers to copying the data into the database without changing it, then doing the transformation within the database (probably with SQL).
- ProcNetDev 4y agoSQL is fine. But as an engineer, I much prefer getting a notebook over a 1000-line plate of SQL spaghetti. Some of our data scientists prefer SQL and that's fine. We figure out how to speed it up and ship it. But I've gotten a few of them onto the PySpark+notebook train and it is just a much more productive way of working, IMO. We can extract things into functions with docs & linting. We can easily look at intermediate sub-queries. Yes, you "can" do all the in SQL but the open-source tooling for Python is just really nice.
- billythemaniam 4y agoIf you want to do it all in SQL, DBT gives similar capabilities. Modern data warehouses + DBT is really why there is a lot more "just use SQL" talk in the data engineering world recently.
- captaintobs 4y agoNice work! As a software / data engineer, I totally agree. I always default to SQL and then only write Spark code when needed.
- waffletower 4y agoI would strongly recommend reconsidering the suggestion that SQL serve as a data engineering pipeline default. We use SQL heavily at our organization, and use it for broad, significant ELT workloads. Its use does help to interface across multiple departments to provide business intelligence across our organization. It does serve as a sort of data lingua franca and touchstone for analyst and engineer interactions. In essence, we use SQL, largely via DBT, where appropriate. However, everywhere else, which comprises a significant collection of data pipeline services, uniform where possible but definitely heterogenous, we default to Clojure; we also utilize Python where more appropriate. Of these 3 languages SQL clearly has the least generality, least testability, least API integration capability, and definitely the most awkward data processing capability. I feel it is naive to suggest it could serve as a default language for data engineering pipelines, particularly given the need to interact with cloud services and third parties.
- tehlike 4y agoI'd like to see more PRQL for data transformation logic.. https://github.com/PRQL/prql https://github.com/PRQL/prql
- qorrect 4y agoSame here!
- rhasson 4y agoSQL is simpler to understand for majority of users. It's easier to get started without learning a tool chain, programming best practices, etc. that could present a challenge to new users. SQL is also great at representing relationships between datasets and developing business logic transformations tends to be simpler and easier to understand. Oftentimes, when using another programming language you end up with many modules, imported libraries, code hacks and optimizations. It all can very quickly make it difficult to read. One example is dbt. They started as writing simple SQL models. With the introduction of Jinja, majority of models look nothing like SQL anymore. You need to visualize your model to understand relationships. It took the beauty of SQL and mucked it. At Upsolver we built a streaming+batch ETL tool that lets you build data pipelines in SQL. We did it because it's easier for non-data engineers to get started, easy to version and maintain as code and easy to automate (not that you can't do this with other languages). The same goes with kSQLDB, Materialize and even Spark and Flink use SQL as a way to simplify onboarding for non-developers.
- billythemaniam 4y ago> One example is dbt. They started as writing simple SQL models. With the introduction of Jinja, majority of models look nothing like SQL anymore. You need to visualize your model to understand relationships. It took the beauty of SQL and mucked it. I know you are pitching your startup, but that's just completely untrue.
- srcreigh 4y agoThe dbt site describes this as a simple example: select * from {{ref('really_big_table')}} {% if incremental and target.schema == 'prod' %} where timestamp >= (select max(timestamp) from {{this}}) {% else %} where timestamp >= dateadd(day, -3, current_date) {% endif %}
- billythemaniam 4y agoAs someone who has written 100s (maybe 1000s at this point) of DBT models, the amount of Jinja you need is at most 1-2% of the codebase.
- ideamotor 4y agoI generally agree but the lack of support and inadequate speed for PIVOT like operations stops it from being true. https://stackoverflow.com/tags/pivot/info https://stackoverflow.com/tags/pivot/info Compare to tidyverse pivot_wider() ans pivot_longer(). No contest.
- strangescript 4y agoWhile this is true its also not moving the needle forward. HTML and CSS are great at building views, but you know what is easier to work with, JSX. All of these specialized tools do their job well, but its empirically easier and faster to just work in a single language to accomplish your project goals if at all possible. Node didn't get popular because it was the "best" framework for apis. It got popular because a bunch of people who already knew JS could start writing server code. Nothing trumps developer experience, and I hardly know anyone who could honestly say SQL is their favorite language.
- ianzakalwe 4y agoSQL is a query language, not a transformation language.
- goodlinks 4y agois query not almost another word for transformation?
- flippingbits 4y agoThis high-level blog post discusses using SQL vs. Python for implementing data pipelines: https://datacater.io/blog/2023-01-18/python-vs-sql-data-pipelines.html https://datacater.io/blog/2023-01-18/python-vs-sql-data-pipe... TLDR: SQL feels most natural for joining and aggregating data sets; Python is favorable for filtering and transforming data due to its higher flexibility and extendability
- college_physics 4y agoPlease no. SQL is more like the assembly language of databases. Close to the database "metal" but not really suitable to elegantly express data transfomation logic. In fact this analogy may be suggesting that what we are missing in this space is higher-level SQL dialects that "compile" to SQL
- RobinL 4y agoSee the last part of the blog post! There are numerous promising attempts to do this. The problem at the moment is that most are at quite an early stage and it's unclear which will become popular.
- AntonioL 4y agoAt my shop we do exactly this, we do ELT as opposed to ETL. (E=Extract, L=Load, T=Transform). We put our input files (think of JSON documents) in the database, and then the data processing is a materialised SQL query. This has a few benefits: - The SQL dumps are very light as they will consist only of the input files, the materialisation is just a query, no need to store the transformed data. - Some time there is an error in the business logic, how do we backfill? That is easy, we update the body of the materialised SQL query and then refresh the materialisation. - Transactions are very hard. We find this great for batch use cases. Exciting to see progress in this space in the last years: - materialize for incremental maintenance of materialised views - postgres has a patch to start support incremental maintenance of materialised views in the works .
- AtNightWeCode 4y agoI would say it may be easier to transform data into a canonical data model from outside the SQL sphere. Parsing flat files or XML files in SQL was common in the past. Today, there are tools with capabilities beyond SQL that can extract data and populate data sets. A clear separation between the responsibilities of data ingestion and data makes the architecture more robust. SSIS for instance can easily become messy because things end up being coupled in unintentional ways.
- TheRealPomax 4y ago> More people will be able to understand your code For simple SQL, absolutely. For complex real world SQL, almost certainly not.
- nabla9 4y agoSQL is based on relational algebra (SQL allows dirty shortcuts that break the mathematical abstraction) https://en.wikipedia.org/wiki/Relational_algebra https://en.wikipedia.org/wiki/Relational_algebra
- zxcvbn4038 4y agoFor one employer I wrote a ton of tools that used sqlite as an memory datastore for manipulating data. All of the tools were written in perl and I found that if the data had any complexity then you needed an expert perl coder to deal with the in-memory representation. However, if you simplified it down to a few SQL queries that updated/queried an sqlite database then even the most novice perl coder could use/maintain/enhance the tools. For really large datasets you could back the table with a file and get really good results - a lot of programmers can't deal with a dataset larger than core memory, sqlite handles it with ease.
- doctor_eval 4y agoI found something similar, we moved a lot of logic out of Java and into SQL and PL/PGSQL. We realised that most of our business logic is mostly data transformation anyway. Suddenly, the more advanced non developers who were logging into github for other reasons, could understand the code, and even occasionally debug it or help us work through new features. These people were domain specialists, so being able to tap into their skills and knowledge at the code level was amazing. And this was in addition to the very significant performance and code density benefits we achieved. There were so many unexpected benefits from moving our logic into SQL that I would struggle to justify implementing future projects any other way.
- andix 4y agoÍ‘m not happy with SQL for querying data. Especially joins, windowing, subqueries and CTEs are often quite clunky. Especially CTEs are often not doing what you intend (full table scans, poor performance). SQL is a great standard, because you can use it everywhere, but it’s not perfect for everything. You can feel, that it comes from ancient times.
- hpcjoe 4y agoAs someone who works with 10s to 100s of TB of data from SQL and SQL-like DBs on a daily basis, writes lots of analytical code, I can say, emphatically, BS. Unless your analyses are trivial (column mean, max, etc.), chances are you need a real programming language behind you. And when you are working with dataframes that are 300+ million rows (glances over to his work machine, yup, that's a smaller analysis I need to run), you need a compiled and fast language for this, which can run in parallel on multiple threads and machines. You aren't using SQL for this. You aren't likely using pandas for this, or pyspark.
- gfiorav 4y agoI learnt this when I worked at www.carto.com In Geo Analysis, you start with Raw data and then apply a series of transformations to it. The idea that all these transformations could be summarized in chain of SQL commands fascinated me. I took that with me and apply it frequently every time anything even remotely resembles an ETL: "could it be done in SQL?"
- 8note 4y agoIts interesting that mapping templates for AWS products like api gateway and appsync use VTL and JavaScript for doing their data transformations instead of SQL. I haven't seen SQL used as a transformation from api input to a table request, but it sounds interesting to explore
- vivegi 4y agoOne of the tricks that I have used with SQL, particularly during development, debugging and unit testing is to use the pattern of INPUT + PROCESS -> OUTPUT. INPUT is the transactions we wish to process and PROCESS is the SQL that takes the INPUT and the set of tables required for processing (essentially an SQL join) and create the OUTPUT table. This is an atomic step and can be defined using a CREATE TABLE XX AS SELECT ...; statement. This is quite fast and even for large tables, this can be parallelized (using the parallel query extensions for the database engine). It is also testable by comparing the INPUT with the OUTPUT and ensuring that the OUTPUT meets the specs. In batch environments, this is an invaluable trick.
- ffff__ddan 4y agoIn my experience SQL gets unreadable fast when it comes to more complex query. I prefer PySpark where you can use python functions and classes to structure your code.