SQL should be the default choice for data transformation logic
robinlinacre.com
robinlinacre.com
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.
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.
- 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.
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.
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.tr...) which, IMHO, has the benefits of both worlds.
I do this using sql
1. Extracting an 'ephemeral model' to different model file
2. Mock out this model in upstream model in unit tests https://github.com/EqualExperts/dbt-unit-testing
3. Write unit tests for this model.
This is not different than regular software development in a language like java.
I would argue its even better better because unit tests are always in tabular format and pretty easy to understand. Java unit tests on other hand are never read by devs in practice.
> in this day and age where enriching the data by running it through some ML model isn't that rare,
Still pretty rare, This constitutes a very minor percentage of ETL in an typical enterprise.
You should get to know better java developers. :-)
sql is so pretty and so very readable and maintable if done by people who know what they are doing.
See my comment here https://news.ycombinator.com/item?id=34580675
I don't use nested cte/stored procedures . I simply extract extract it to a new file and mark it 'ephemeral' in dbt, no different than what you'd do in a regular programming language.
I'm really contrasting SQL to other commonly used data manipulation APIs and dataframe libraries like dplyr, pandas, polars etc., but again, appreciate this should be clearer in the blog
The PySpark API has methods for SQL-like operations, so it's familiar to people like you and me who know and love SQL. There is no point to Pandas on a datalake since PySpark has dataframes.
I'm a huge proponent of SQL. I'm dubious of ORMs and think a lot of websites would be more performant if people just learned SQL, or whatever the idiomatic querying language for their chosen DB is. But with datalakes, pyspark is great.
I'm much less worried about that happening to SQL scripts.
If your db is scalable enough (like Snowflake) and you have the money, you can use SQL directly in the same way. But, I've seen data analysts writing bad SQL against Snowflake costing thousands of dollars per query.
It’s really tough to future proof anything. At least with pyspark you will have the code. And, there will always be experts out there. Worse case, if it’s important enough, someone will wrap it in an API and use it until something better comes along (see: 50 year old mainframes still in use).
Is this hosted databricks ?
But, tell me more. I always like a better way.
Is it more efficient to read/write from SSD? Right now, I need everything in memory.
You can choose when to cache the data in memory.
And it's the fact that you can write the code in Python, Scala etc instead of SQL.
So merely writing in a language that we believe will be around forever does not prevent your code from being rewritten.
Also unfamiliarity will make SQL feel inefficient for non-SQL programmers. The experience of rewriting SQL teaches some this lesson. But others find that the familiarity of the language of the rewrite makes it seem better to them, even though it is objectively worse. An objectively worse that only becomes visible when someone needs to optimize it for performance reasons. And it is seldom the programmer who did the inefficient write who has the skills to optimize it back to what SQL did in the first place!
(Yeah, I've had to do those optimization fixes.)
So on seeing one they can sometimes think it's quicker to redo this in a language I know rather than upskill enough to understand this.
One of the biggest problems with spark imo is that people think that the magic cloud is going to make it go fast, when it actually means that the REPL is just muuuuch slower to iterate on.
There are couple of famous examples
https://shopify.engineering/build-production-grade-workflow-...
"By using SQL, a much wider range of people can read your code, including BI developers, business analysts, data engineers and data scientists."
If you really want to advocate for PySpark in this context, you've gotta contend with everything the article brings up:
- More people will be able to understand your code
- Future proofed, with automatic speed improvements and ‘autoscaling’
- Make data typing someone else’s problem
- Simpler maintenance, with less dependency management
- Compatibility with good practice software engineering
- SQL is more expressive and versatile than it used to be
[1] https://mariadb.com/kb/en/quote/
You can then specify columns as a query, group_concat the result and evaluate resulting statement [2].
https://mariadb.com/kb/en/execute-immediate/
I hope that helps.
Depending on how big your data & org is - orgs solve this by writing their own query engines that can query many different data formats.
It is much more practical to optimize an engine, than to expect every data engineer to know how to optimally manipulate data from many different formats.
If you want to use the same logic / language to query some exotic dataset for specific use cases - it can often be worth it to write a custom engine that can do it - rather than expect all end-users to learn the ins & outs of the other databases & datasets (not to mention, to learn something beside SQL).
Instead, a single team (the query engine owners) can optimize the query engine - rather than individual users trying to optimize every script individually.
Your users can become masters of their engine / language (SQL) - because it can be used for the vast majority of cases.
For many reasons - you might want to store data in a format that MySQL / Postgres does not support natively (see Google Spanner). But, ideally, you'd still be able to leverage the fact that almost every programmer in the world can write SQL.
We don't use any of them though, we have custom C# ingestion code that can talk to most databases and Odata API's. .Net's lazy collections make it quite easy to create a consumer/producer pipeline for streaming large datasets without too much overhead.
Why would this be more predictable in something you handroll vs using something like trino.
For larger data, i'm a fan of writing everything in SQL, and executing it using AWS Athena (a Presto engine), which is extremely cheap.
If you have even larger data, you can consider AWS Glue (steering clear of any aws specific stuff and literally using it as spark SQL as a service). For many simple cases you don't really need to know any Spark, and you're just submitting SQL to AWS for execution.
But all of this assumes you're storing your data as files on disk (e.g. parquet). I'm less experienced in running everything in more traditional SQL engines like MS SQL server or postgres.
I am working on the (parallel, distributed) SQL engine and you can't even fathom how often I see SQL queries disguised-as/hand-compiled-into for loops in C++. Essentially, whatever SQL engine works on can and should be expressed as data tables and their processing and transformation should also be SQL-expressed.
shameless plug but if you want an open source/YC backed solution for data movement pipelines try Airbyte https://news.ycombinator.com/item?id=34533429 :)
- 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.
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.
It is frankly disappointing that something like a basic WordPress installation is not portable across MySQL/Postgres/MS/ORACL, etc
Code completion is impossible when selecting a.foo from fuck knows where.
I don't even think it'd be that hard to support a more modern syntax form, it would primarily be a reordering, so it would be easily transpilable.
IntelliJ does it perfectly. As many joins and CTEs as you want, IntelliJ will suggest all columns available.
It amazes me when competent developers / analysts / data scientists don't know (any) SQL. Have they stopped teaching this in University or something?
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.
The one I took was elective. I am willing to admit that from a professional ROI perspective, it was one of the best uses of time in my entire life and easily worth hundreds of thousands of dollars.
Most programs include parts of all three, but SQL per se (rather than like, relational algrebra) is pretty firmly in the professional training for software developers set, and schools that reject that aspect may not teach it.
In my experience recent code school/boot camp grads have as much or more practical sql as recent CS grads; probably because those schools are nearly totally focused on professionally applicable skills.
It should be mandatory along with understanding O notation and datastructures and algorithms, because really... unless you're doing a career which is like... 100% embedded development... you're going to be encountering databases and datamodeling as part of your career.
I'd be much happier if CS programs would graduate people who did a whole semester of first order logic, Date&Codd's foundational papers and why network&hierarchical databases are problematic, relational algebra / calculus, Datalog & friends, and then just toss in a "and this is how SQL does some of this but also mangles all of this..." at the end.
+ as a bonus, a DB implementation/internals course so people can understand what goes into query execution etc.
Because those people would then have some proper context before going off and butchering the world with ORMs and microservices...
But AFAIK so does my local university.
What is a trap I see often among ORM users is them running into the n+1 problem. However, that's actually a consequence of database implementations not being theoretically pure. If you had an ideal SQL database then their approach would be technically correct. They aren't wrong to think that way.
It's just that we don't have ideal SQL databases, so we have to resort to hacks to make things work in the real world. Why we need to break from the theoretical model and use those hacks can be difficult to understand if you aren't familiar with the implementation at a lower level.
That's what's amazing about ORMs handling the simple stuff.
ORM doesn't have anything to do with SQL statements. ORM only operates on the results those SQL statements produce.
You're probably thinking of the active record pattern, which combines ORM with query building into an interesting monstrosity. Indeed, query builders produce SQL statements and can save you from changing 500 odd raw SQL queries.
But as always: use the right tool for the job. A hammer is a great tool, but not suitable for removing screws (most of the time).
These are technically active record libraries. One of the popular ones is literally known as ActiveRecord, but the whole suite of them are implementations of the active record pattern.
ORM is simply the process of converting relations (i.e. sets of tuples, i.e. rows and columns) into structured objects and back again. This layer of concern is independent of query generation.
I’m mostly using entity framework core, which allows you to do quite complex queries, which can be really easy to read and maintain. But if you’re careless, you can create quite slow queries, that’s clear. But you can easily create slow queries with plain SQL too.
And nothing prevents you from using plain SQL libraries next to an ORM. Good ORMs expose the database connection, so you can do whatever you want.
Every tool can be abused.
And then they have these awful and subtle caching issues that mean if you fall back to SQL then you take a massive performance hit. Or if you run multiple servers against the same database you need another layer of complex caching infrastructure. Or if you update a table outside of the application you can get inconsistent results.
The whole thing is a nightmare of unnecessary complexity apparently intended to hide developers from the much lower complexity of databases.
ORMs are an inefficient, leaky and slow abstraction layer on top of a fast and complete abstraction layer.
I’ll never willingly use an ORM again.
Edited to add: the whole fragile contraption is done in an effort to move slices of the database into the memory context of a remote application. To what end? So we can update some counter and push the whole lot back out? It literally makes no sense.
1. Generate ORM entities from the database (usually there is a too for that) 2. Generate the database from ORM entities, and make changes via generated/manual migrations
You are supposed not to edit the generated side, just re-generate it on a change of the model.
Countless "replacements" for SQL have come around to make data accessible by non-expert, but regardless if your using a BI tool, reporting platform, ORM, whatever, pretty soon you're going to need to understand and express the logic, and that's when you really appreciate SQL's directness and terseness.
There's a woeful ignorance about what the relational data model is, how the industry arrived here, and how this is implemented. Problems or archaisms with SQL specifically become synonymous in people's heads with the relational model generally, and for a while that led down the quite problematic NoSQL road. Then slingshotted back to SQL -- but from my perspective SQL itself (not the relational model) is a problem. It doesn't compose well. It doesn't handle recursive relations well. It has an awkward syntax. It conflates concepts. It has an archaic datatype model. None of this is intrinsic to the relational model, but SQL becomes a limiting factor.
Disclaimer: I work for a DB company doing awesome stuff with the relational data model, but not SQL (RelationalAI) so am ... biased. Though I have always had those biases. :-)
https://cran.r-project.org/web/packages/data.table/vignettes...
I am not as familiar with R. Last time I worked in R (some years ago) equivalent R code was something like this caution I'm no expert in writing R so might be a better /more intuitive way...
Output_data <-merge(x=T1, y=T2, by="Date", all.x="True") %>% mutate(My_var = NAME) %>% fill(My_var)
In SQL the equivalent would need to use Over (Partition by) which is less intuitive for me to write.
It wasn't part of the mandatory coursework when I went to school. I really think it should be. It's not as though my CS program was 'pure theory', they taught lots of 'vocational' stuff but not SQL.
As for why people put off learning it.. I put off learning it longer than I care to admit because I thought I could simply do without it (ORMs, BerkeleyDB, etc), and fake it if put on the spot (I always understood the very basics, insofar as a SQL query can be understood if read as declarative English.) Ever since I bit the bullet and actually learned it properly, I've been kicking myself for not learning it upfront.
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.
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.
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.
This is what I do. Each pipeline starts in a fresh conda environment, or sometimes entirely fresh container (if running in GitLab CI or GitHub actions, for example) and does a pip install to pinned packages. So every run is a fresh install and there’s no dependency hell.
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.
I know you are pitching your startup, but that's just completely untrue.
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 %}Not saying it can't be done but that's my experience.
... or for someone to build a tiny framework on top of pytest that helps write automated SQL tests.
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.
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.
(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.
And historically it has been relatively hard to set up simple, fast running unit tests on SQL pipelines, though I think that's becoming easier nowadays.
Not saying it can't be done but that's my experience.
Is the actual reality, not only SQL.
This gets even more complicated when you start using vendor-specific extensions for string manipulation, json parsing etc in the data pipelines case. It might be literally impossible to execute your SQL outside of prod or a prod-like environment.
It's actually pretty easy to write test SQL test scripts for stuff.
I've had good luck writing scripts where the first one sets up preconditions, then it runs a million interconnected tests with no isolation between them, and then it tears down. It means you can't run a single test in isolation and you have to be aware of each test's postconditions, but it's a good "worse is better" solution for "I need to start testing now, not after I rearchitect my whole project to be isolatable and right a crapload of scaffolding and boilerplate". But every testing framework fights against this pattern.
For me, a testing script will load a clean schema+procedures with no data (empty tables), and the schema never changes. Each test inserts a handful of rows as needed in relevant tables, runs the query, and then checks the query result and/or changed table state. Then DELETE all table contents, and move on to the next test. Finally delete the entire test database once you're done.
If you have queries that alter tables, then each test starts with a brand-new schema and deletes the whole database once done.
The only kinds of tests this doesn't scale to are performance tests, e.g. is this query performant on a table with 500 million rows? But to me that's not the domain of unit tests, but rather full-scale testing environments that periodically copy the entire prod database.
So I'm curious why you consider an RDMBS to be hyper-interconnected state with interconnected tests?
Thus, the code to set up preconditions would be as complicated as the entire service tier of the admin screens. So it makes sense to write the test scripts as, instead of "insert" it's
1. "set up known good mostly-blank DB"
2. "Test the CREATE methods"
3. "Test the UPDATE methods"
4. "Test the DELETE methods"
5. "teardown"
* obviously it's not simple CRUD, this is just a simplification of how it goes.
It's not that this is an ideal workflow, it's just that "worse is better" here. This lets us get testing ASAP and move forward with confidence.
It's not just testing a single build up... but build ups and migrations from deployed, working options. Some solutions have different versions of an application/database at different clients that will update to "approved" versions in progress. (Especially in govt work)
I think the concepts of "application-owned RDBMS" vs "3rd party product that uses its own RDBMS" are the source of a lot of confusion sometimes.
If you manage your own database, testing shouldn't usually be particularly difficult. But when you're integrating with a third-party product, you generally can't effectively do unit tests. Just end-to-end integration tests of the type you're describing.
I'm currently working on a greenfield system in this class and the database development workflow is specifically designed to help make things sanely testable. So, first the application is designed to be very granularly modular and each module handles its own database tables... and each module is fully testable by itself. Yes, there can be/are dependencies between modules, but only the direct dependencies only ever have to be dealt with... when unit testing in the module itself, it's usually a pretty small slice of the database that needs to be handled. So what that means is any one component's test suite only needs to set the initial state for itself and its dependencies. What I do then is individual functional test can take that starting DB state, do it's work in a transaction, and then roll the transaction back at the end of the test. This way the only initial state created at the start of the process necessarily has to be dealt with. My "integration tests" will walk the business process statefuly, using only the initial DB state for dependents setup and walking the business process of creating all the data the module itself handles. Finally, unit test starting data is only ever defined in the component defining the tables which are to be loaded. This means that when dependents have their own tables, reference's are made to load that component's test data rather than trying to redefine a fresh set every time it shows up in the dependency tree.
Anyway, as said earlier, this kind of code development testing discipline isn't common amongst DBA types (the best to be able to figure out a good methodology for it) while a lot of application developers that have the testing discipling avoid the DB like the plague. So it never gets done until it absolutely has to be done. And they you end up ad hoc'ing it together.
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.
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).
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.
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.
>>> v = ""
>>> v + 42
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
TypeError: can only concatenate str (not "int") to str
CREATE TABLE varchars(v VARCHAR);
ALTER TABLE varchars ALTER COLUMN v TYPE INTEGER USING v::integer;
PREPARE v1 AS SELECT v + 42 FROM varchars;It is distinct from Python because in Python individual objects have types - and type resolution is done strictly at run-time. There is no compile time type checking because types do not exist at compile time at all. Note how in my example I am preparing (i.e. compiling) a query, whereas in your example you are executing the statement.
If you refrain from changing the types of columns in a database and prepare your queries you can detect all type errors before having to process a single row. That is not possible in a dynamically typed language like Python, and is very comparable to the guarantees that a language like Typescript offers you.
Types in Python are also static within the context of a statement. That's not particularly meaningful, though, as programs don't run as individual statements in a vacuum. Just like SQL, the state that has been built up beforehand is quite significant to what each individual statement ends up meaning.
> types do not exist at compile time at all.
Same goes for SQL. Given the previous program, it is impossible to determine what the type of v is until you are ready to execute the SELECT statement. If the CREATE TABLE statement fails, v will not be the VARCHAR you think it is. If someone else modifies the world between your CREATE TABLE statement and your SELECT statement, v also will not be the VARCHAR you think it is. All you can do is throw the code at the database at runtime and hope it doesn't blow up.
> whereas in your example you are executing the statement.
What do you think "" + 42 executes to, then? The thing is, it doesn't execute because it doesn't satisfy the type constraints. It fails before execution. If it were a weakly typed language, like Javascript, then execution may be possible. Javascript produces "42".
> and is very comparable to the guarantees that a language like Typescript offers you.
Not at all. Something like Typescript provides guarantees during development. SQL, being a dynamically typed language, cannot know the types ahead of time – they don't exist until the program has already begun execution – and will throw errors at you in runtime after you've moved into production if you've encountered a problem.
If you are working in a hypothetical adversarial world where people are changing data types out from under you then that might happen. That will also happen with any other shared data source, regardless of what language you might be using. If you are sharing a set of JSON documents and people start altering documents and removing keys then your Typescript program that ingests those documents will also start failing.
That is not a problem of the language but a fundamental problem of having a shared data source - changing to a different language will not solve that problem.
I think you are conflating SQL the language with a relational database management system. A relational database management system models a shared data source - and a shared data source can be modified by others. That introduces challenges no matter what language you are using.
> What do you think "" + 42 executes to, then? The thing is, it doesn't execute because it doesn't satisfy the type constraints. It fails before execution. If it were a weakly typed language, like Javascript, then execution may be possible. Javascript produces "42".
It is fundamentally different. Given a Python program, you cannot know whether or not it will produce type errors at run-time without executing the program. Type checking is done at run-time as part of the execution of the "+" operator. In SQL, type checking is done as a separate compilation step that can be performed without requiring any data. In Python the object has a type. In SQL the variable has a type.
Given a fixed database schema and a set of queries, you can compile the queries and know whether or not the queries will provide type errors. You cannot do this with Python.
> Not at all. The benefits of something like Typescript is that the guarantees are provided during development. SQL cannot know the types ahead of time – they don't exist until the program has begun execution – and will throw errors at you in runtime after you've moved into production if you've encountered a problem. That's exactly when you don't want to be running into type programs, and why we're largely moving away from dynamically typed languages in general.
SQL can provide the same guarantees as Typescript. However, unlike Typescript in which the variables are fixed, SQL always deals with processing data from an external data source - the tables. The data sources (i.e. tables) can be changed. That is not a problem a language can solve.
Like you mentioned earlier, if you refrain from changing types, a compiler can theoretically determine Python types as well. The Typescript type checker when run on Javascript code is able to do this. But that puts all the onus on you to be super careful instead of letting the language hold your hand.
> SQL can provide the same guarantees as Typescript.
1. It cannot as it does not provide guarantees about its memory model. Declaring a variable as a given type does not guarantee the variable will be created with that type. Typescript does guarantee that when you declare a type that's what the variable type is going to be.
2. As SQL is dynamically typed, allowing you to change the type of a variable mid-execution, what type a variable is depends on when that statement is up for execution. And as SQL is not processed sequentially, at least not in a meaningful sense, it is impossible for a compiler to determine when in the sequence it will end up being called upon.
SQL as a language is statically typed. It has variables that are statically typed [1], much like Typescript. All columns in a query have fixed types, and there is a separate compilation phase that resolves types of parameters - much like any other statically typed language.
SQL generally operates on tables - and tables model a persistent, shared data source. The Typescript equivalent to a table is JSON files on disk. There is a fundamental need to be able to change data sources as time goes on and business requirements change. That is why SQL supports modifying shared data sources, and supports operations like adding columns, changing column types and dropping columns. SQL deals with the fact that changes can be made to tables by rebinding (recompiling) queries when a data source is changed.
Any hypothetical language that will replace SQL will have to deal with the very same realities of people wanting to modify shared data sources. A different language cannot solve this problem because there is a fundamental need to change how data is stored at times.
Perhaps what you are looking for is better tooling around this problem. SQL can be used to power such tooling, because of its strong static type system that works alongside the persistent shared data source. For example - you could check if all your queries will still successfully compile after changing the type of a column.
[1] https://www.postgresql.org/docs/current/plpgsql-declarations...
No, we're only talking about SQL here, and specifically to the example you gave. It demonstrated strong typing, but not static typing. I am not sure where you think Typescript comes into play with respect to the core discussion.
> SQL as a language is statically typed.
As before, SQL defines the ALTER statement. SQL is no doubt strongly typed. Once you declare a type it will enforce that type until such time as it is explicitly changed (SQLite notwithstanding), but it can be changed. To be static means that it cannot change.
> SQL generally operates on tables - and tables model a persistent, shared data source. The Typescript equivalent to a table is JSON files on disk.
That's but an implementation detail. Much of the beauty of SQL, the language, is that it abstracts the disk and other such machine details away. Conceptually all you have is a machine that has functions that operate on sets (or vectors, perhaps, since SQL deviates from the relational model here) of tuples. Perhaps your struggle here is that you are hung up on specific implementations rather than the concepts expressed in these languages?
> Any hypothetical language that will replace SQL will have to deal with the very same realities of people wanting to modify shared data sources.
That's not a reasonable assumption. In practice you don't want arbitrary groups of people modifying a shared data source. You want one master operator that maintains full control of that data source, extracting the information other people need on their behalf. The n number of users feeding a virtual machine code fragments to build up an entire application is an interesting concept, but one that I am not sure has proven to be successful. It turns out we've learned a lot about software development since the 1970s. These days we usually build a program that sits in front of the SQL program to act as the master source in order to hide this grievous flaw, but a hypothetical SQL replacement can address it directly.
> SQL can be used to power such tooling, because of its strong static type system
Let's go back to your original example:
CREATE TABLE varchars(v VARCHAR);
ALTER TABLE varchars ALTER COLUMN v TYPE INTEGER USING v::integer;
PREPARE v1 AS SELECT v + 42 FROM varchars;
Now, you said before that each statement when observed in isolation is statically typed. While I suppose that is true to the extent that the the type won't randomly change in the middle of the execution of that statement, the statement alone doesn't provide type information either. Parsing `PREPARE v1 AS SELECT v + 42 FROM varchars;` in isolation, we still don't know what v is.
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.
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.
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.
ETL means a lot of very different things to different people.
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.
Any discussion of SQL at scale must include ClickHouse [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/] or the open-source Spark-ClickHouse Connector [https://github.com/housepower/spark-clickhouse-connector], and capability to scale SQL as a network service.
Disclosure: I work for ClickHouse
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.
Yeah, no.
Sql is good at look up and filtering, but not for linking heterogenous sources and massaging data.
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.
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.
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).
I think of more than a couple basic joins in a query to be a code smell.
Even complex things where you have to evaluate a rule for multiple entities can be covered with some clever functions/properties.
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?"
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.
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.
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.
Instead, I keep my data in a relational database and denormalize aspects of it out to them in order to answer queries that aren't a good fit for my database: https://2017.djangocon.us/talks/the-denormalized-query-engin...
You wind up defining relationships in code instead, across dozens of API calls without a central spec and it's super easy to get wrong. If it were as easy as you say we'd all be using MongoDB now.
Just saying other domains can run into "denormalization hell" and "super complicated API traversal".
Not everything fits into this mold, it's a matter of mix/match today, and there are no clear rules for how you get into scaling as needed...
> the noSql databases, like Apache Solr
SOLR (and Elastic Search) is a search engine, and is not a database, and should never, not be confused as one.
> We do not store information in countless tables with increasingly akward joins between them,
The whole "no joins" thing was really silly as the first thing that you do when working with a noSQL is write code like this parent=someThing.get(someObject); children=someObject.get(childrenMatchingCriteria); Which is a join. What Mongo and other noSQLs did was ship a reasonably performant and more importantly, easy for the developer way to store JSON objects. I spent a good number of years building on CouchDB, Firebase, DynamoDB and Mongo because it was easy to go fast, especially where the back end was just storing settings objects... Ironically, every project eventually needed two things that noSQL was supposed to not need: schemas and joins... so we implemented them in code (often in a React, Angular or Vue frontend).
Humans think in terms of categories of thing. The reason things live in the same category is that they share the same attributes.
The relational model works really well for this.
I'm ready to be convinced otherwise, but I'm going to need some really good examples of "human data" that fits NoSQL systems but can't be reasonably represented relationally.
The answer here is, like all things in tech, it depends. It depends on what the data is. It depends on what you want to do with the data. It depends on what expectations are being made about that data.
> We humans don't have/use tables mentally
I just ate lunch. When I stepped in they wrote my name on a list with the number in my party. The server wrote my order on a paper with one grid line per item. Two tables, the old fashioned way. Tables are very natural for people. Evidence: the enduring popularity of spreadsheets, and lunch.
Hierarchies aren't a disaster with relational, they're trivial. A table maps product attributes to products, products to orders, orders to customers.
But relational allows you to escape the limitations of strict hierarchy. Because another table also maps products to suppliers, and orders to shipments, and orders to customer satisfaction surveys.
People think with hierarchies yes, but those hierarchies are built out of relations. I'd actually say we think in relations, and that some of those relations can be expressed as hierarchies, but others (sometimes most) can't at all.
If you're using "countless tables" then you might have a problem with your modeling. And if you're using "increasingly awkward joins" you might be writing your joins wrong, or again modeling the data in a confusing way. Or you just have a prejudice against joins -- they're no more "awkward" than pointers in C. Database normal forms exist for a reason, because they ensure you're storing data in the way relational databases are designed to work with it.
I'd say the first thing you should start with on any modern knowledge/information system is SQL, and only migrate away from that if you're absolutely sure it's necessary.
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.
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 .
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.
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.
If the schema is "dynamic" then I'd accuse the business of being poorly-defined and not worthy of any development time.
Some things can't be locked in stone, and SQL will leave you out to dry when that's the case.
Honestly, I cannot live without that feature. [sorry, that is my OOP minute. Continue without me...]
With structure, SQL is not hard to read and write actually.
You can ask why ? I would say for maintainability reason.
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"]
SELECT
DISTINCT SUM(i.views),
SUM(i.clicks),
SUM(p.amount)
FROM
"payments" "p"
WHERE
user_id = 12
GROUP BY
dateAll 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"] select distinct
sum(i.views),
sum(i.clicks),
sum(p.amount)
from
"payments" "p"
where
user_id = 12
group by
dateHow do you think ?
https://www.postgresql.org/docs/current/tablefunc.html#id-1....
This raises a problem with the YAML DSL though. Consider the following (truncated) example:
main:
select:
- field: ct.*
from:
alias: ct
fields: ...
crosstab:
- select:
- extra_infos.problem_id
- extra_infos.info_type
- extra_infos.info_value
from: extra_infos
- select:
- extra_infos.info_type
distinct: true
from: extra_infos
But... "alias:" and "fields:" correspond to features of the from clause itself, whereas "crosstab:" is a special custom thing (a macro?) which ends up being compiled into a call to the identically-named crosstab() feature.
So to understand the YAML, I need to understand both how the crosstab() feature in PostgreSQL works AND how that YAML DSL represents "crosstab:" calls using a different syntax.
I looked up crosstab in the PostgreSQL manual but that information won't help me figure out the DSL.
So I think I'd rather stick with regular SQL.
It should have few keywords and structures as possible. Of course there's some "fixed keyword" like above crosstab (due to inconsistent SQL syntax i think).
"alias" + "fields" helps you build "as (field1, field2,...)" kind of thing. "select" + "from" must form a normal "sql query".
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.
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).
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.
Using SQL as the primary data analysis language ends up being very powerful and straightforward. I second the author’s recommendation.
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.
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
Cassandra is also not an analytics database, it is intended for "OLTP"-like workloads.
For simple SQL, absolutely. For complex real world SQL, almost certainly not.
I haven't seen SQL used as a transformation from api input to a table request, but it sounds interesting to explore
The verbosity of SQL makes it unwieldy to maintain for pipelines.
Add in if you want the new columns to be added in place and you have to bring in window functions.
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.
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.
QUEL is more dense for equivalent functionality:
replace users (preference = "blue") where id = 123
> * 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.*
Ok. Count me out !
In fact this analogy may be suggesting that what we are missing in this space is higher-level SQL dialects that "compile" to SQL
At which point importing them into and out of database tables becomes a whole lot more feasible.
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…
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