2. Combining and slicing data is a craft, and doing it subtly wrong in one step can lead to fatal errors in the outcome.

And most importantly, it can be very difficult to notice. Numbers don't smell.

That is why I would be very hesitant to give a slightly more than trivial task to an engine that fails 25% of the time.

But I guess that is the same as any other programming task. Just that other programming tasks require a lot of boilerplate where an AI can help. SQL is much more straight to it.

Maybe it could be useful to ask questions that are similar to writing testcases "how can I verify that my query is doing the right thing"?

Ever heard of LATERAL joins/CROSS APPLY?

  SELECT loop.value, x.squared
  FROM generate_series(1,5) AS loop(value)
  CROSS JOIN LATERAL (SELECT loop.value * loop.value AS squared) AS x;

And this is the point I was trying to make.

Instead of start with the "how", learn to do the "what".

- Reference data from the previous part of the query (the "left-hand side")

- Return multiple columns

The only way you can achieve it is with LATERAL/CROSS APPLY.

Regular correlated subqueries can only return a single column, so something like this doesn't work:

  SELECT
    loop.val, (SELECT loop.val * loop.val, 'second column') AS squared
  FROM
    (SELECT loop.val FROM generate_series(1,5) AS loop(val)) as loop

   error: subquery must return only one column

It's sets all the way down. A set of f(x) is still a set.

    CREATE TEMP TABLE temp_results(value int, value_squared int);

    DO $$
    DECLARE
        r int;
    BEGIN
        FOR r IN SELECT generate_series FROM generate_series(1,5)
        LOOP
            INSERT INTO temp_results VALUES (r, r * r);
        END LOOP;
    END$$;

    SELECT * FROM temp_results;

But you're right. Postgres does allow for-loops like this. (They're also slower than the equivalent set-oriented approach.)

https://chat.openai.com/share/931b1778-6393-4e86-94b4-b3b5a5...

   SELECT loop.value, loop.value * loop.value
   FROM generate_series(1,5) AS loop(value)

The reason people use a relational database is because it has loops that are faster, safer, and more efficient than anything you can write.

The point is that by getting rid of loops you remove one way of telling the computer "How" to do it. Start here, do it in this order, combine the data this way.

When "How" is a solved problem, it is a waste of your brain to think about that again. "What" is a better use of your brain cycles.

different kind of loops can be different, e.g. 2 nested loop with quadratic time:

for i in t1: for j in t2:

vs sort + merge join with n log n time.

WITH RECURSIVE cnt(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 5 ) SELECT x FROM cnt;

And then do a regular CROSS JOIN on that table.

It's not clear to me that (mathematically) a lateral join can be reduced to a recursive cte (and if the performance of a recursive cte would be acceptable for the cases where it does work as a substitute).

> Maybe it could be useful to ask questions that are similar to writing testcases "how can I verify that my query is doing the right thing"?

that seems like a good thread to pull on!

The number of use cases which are too heavy to finish in hours but small enough to fit in a single instance is pretty limited.

b) Only basic SQL works on any database and even then there are major differences in how they treat things like nulls, type coercion etc.

Programmers think the most valuable language is a programming language. Therefore, an LLM that can generate quality code in that language should also be extremely valuable.

I'd argue that the most valuable language is the natural language of the organization you're writing code for.

That language is vague and ambiguous, and encapsulates many hidden assumptions about how that organization works.

For example, if the organization is a retailer or a wholesaler, you might ask the LLM to generate SQL to return last month's sales and inventory for each site and product category.

The LLM can query the database system tables and guess which tables have sales transactions and inventory transactions. The LLM can look at foreign key definitions to make a good guess how to join these tables with metadata about items, sites, categories and the calendar.

But will the LLM know that inventory is a stock, and sales is a flow? Will it know it should sum the sales transactions but average the inventory balances, or take just the beginning or ending inventory balance?

Many human engineers struggle to translate ambiguous requests in natural language into code which reflects the assumptions and mechanics of the organization.

An LLM that generates consistently useful code needs a world model of how things work, not just how to write code.

Whilst Snowflake is pretty popular the days of elaborately modelled EDWs are long gone.

And so typically I find I am doing queries, transformations etc in some abstraction layer e.g. Spark on a data lake, ORM for web applications etc.

They're more prevalent than ever in my experience. Consider the popularity of dbt.

Don't forget the models other's create for you - often hilariously slow code to present a set of facets that often barely align with your business delivery needs; and don't forget to sync it even more slowly with Fivetran, the DE platform of the future!

This doesn't make any sense, or I'm guessing you've never actually used it. Modeling is something you can do with dbt, not what dbt does (or is, or can be?). I've used it to create data marts and EDW's with hundreds of tables, no differently than I would have created a decade ago with other tools.

The C for loop on the other hand…

SQL was created in 60s. It has not really kept up with the pace of modern programming language ergonomics. It was made for a single person executing a batch job query pulling data from the database.

On the other hand, I kind of agree SQL is good to learn. It's an counter example on how not to design a programming language.

The IBM System R and SEQUEL paper was 1974, while Oracle 2 was the first commercial database which added it in 1979.

Despite all the hate MongoDb deserves, it solved the problem how application developers can easily get data in and out of a database.

1. The order of the phrases, the lack of trailing commas, the fact that an awful organization controls the standards and gatekeeps them to a bizarre extent.

They may save you a bit of time initially but when your company gets bigger, the ORMs will become a bottleneck.

For example I've encountered queries that are not only slow, but they generate several hundred megabytes of output all of which is sent to the user's web browser where JavaScript selects the relevant two kilobytes of data to show the user.

The worst I've ever seen was a system where every single write to the database would be sent to every single web browser viewing certain webpages. 99.999999% of the writes were completely irrelevant and javascript in the browser would simply disregard them. The server load was immense... and eventually our Sysadmin brought it to someone's attention. Where we found out it was leaking sensitive data.

At which point - but not earlier! - you just make your ORM print the queries, fix them manually (or write them from scratch). You then can ditch the ORM, use it as a query builder DSL only, or use escape hatches provided by the ORM to inject raw SQL.

Don't use ORMs as a crutch to get away with not knowing SQL, that's bad. However, saving "a bit" - and with good ORMs, that "bit" is quite large - of time in the beginning is often very valuable. Just have a clear "exit strategy" for when (and in 90% of projects, if) it's needed.

Also the way each DB supports its own dialer is quite maddening.

I found while declarative and functional programming languages are not as often used, however learning them made me a better programer.

[1]: https://stackoverflow.com/questions/10925689/functional-prog...

Yeah this is the issue I have with all of the SQL generation stuff. Not only should the SQL be valid, a prompt like "generate a query that pulls sales for the last quarter" should generate the same output for everyone without fail. Vanna's business logic embedding is a good first step but even then it is only correct like 90% of the time with GPT-4.

Even then, it will only work if there are strong standards and data governance structures in place that everyone within an organization is aligned on. For example, "sales" can mean different things to different people and all of that needs to be buttoned up as well.

In either case, validation is the key step - you can't just trust that your SQL query is correct regardless of if you have manually written it, you still have to go through the data and check it.

That's where the SQL generation stuff can save time - if 50% of the time you can get to an answer in half the time, then it's great! Normally in my experience with current-gen LLM's when they fail they fail quickly, so the other 50% of queries don't take twice as long to write manually.

Then there is the other use case - if you aren't sure why a particular SQL query is erroring, these LLM's are great at telling you why and fixing your code.

There cannot be any AI involved when processing the definition of a KPI. Otherwise you'll never be able to roll it out to thousands of users when there's always a 90% (or even 99%) chance that the business logic might not get applied correctly.

Check out what we do at Veezoo (https://www.veezoo.com) with the Knowledge Graph / Semantic Layer to mitigate that.

The kind of stuff that it is very easy to validate if it works or not :)

I am building a warehouse management system at the moment, and it's great to quickly churn out lots of SQL views (particularly as the schema is changing/evolving slightly as I am writing it, so being able to go back to GPT4 to churn through the changes to some of the 'views' of my pages helps, even if it requires a little testing/validation).

That's the story of the LLMs in general.

The hype is free. Startup ecosystem are a bonus.

2) Sam Altman has a shady track record publicly, and if you believe the things people say privately he has consistently done business very dishonestly throughout his career. He is the CEO, and virtually the entire executive team are people he brought in from his network. It’s his company.

3) To give one example of many, OpenAI recently changed the terms of ChatGPT so that web users conversations can now be trained on (and if you want to save any chats you must opt in). Presumably this also applies to all conversations you had under the old policy despite saying they would never train on those conversations.

I could go on at length…

Uber

Airbnb

Facebook

The entire financial industry

also, every huge corporation that paid a vast (but relative to their market cap, insignificant) settlement, long after incidents in question, without admitting guilt.

Corporations have essentially culled vast numbers of humans until forced to stop. What’s a little lucrative nosiness in that context?

The massive tide of legally gray (including very dark gray) media hoovered up by training data vacuums isn’t exactly an industry secret. Whether any known player is more serious about protecting data source interests over their own ambitions remains to be verifiably demonstrated.

Someone once said, “it is easier to ask forgiveness than permission”. Someone might add, “if ever, or only performatively for congressional testimony theatre purposes, and definitely only after requiring a more punitive legal/regulatory moat to lock in the benefits of your non-compliance”.

ANY sketchiness should be taken seriously. Not making accusations. But sooner or later, somebody is going to say, “it’s a lot easier to cry and complain than claw back information someone took from you, who has billions to spend on lawyers”.

Closer would be FTX which unambiguously claimed one thing and did the opposite, but I do not think OAI has FTX level of dysfunction.

[0] Google: lied about deleting data (Google as a verb)

[1] Google: lied about deleting data (Google as a noun) https://arstechnica.com/tech-policy/2023/02/us-says-google-r...

[2] Google: lied about deleting data (Google as a definition in Webster’s dictionary -> Rickroll)

That said, there are 3 quite worrying future possibilities, both stemming from the level of investment and commitment by Microsoft in OpenAI - a huge percentage of Azure’s value is bet on an exclusive partnership with them. That gives OpenAI a lot of leverage. And customer data is a very tempting cookie jar to build a long term moat for these companies

Possibility 1: someone overtakes OpenAI models and you’re stuck on Azure who won’t offer that model

Possibility 2: OpenAI decides to break with Microsoft and you’re stuck with a useless application. AWS Bedrock is far less likely to leave an AI application obsolete.

Possibility 3: OpenAI put pressure on Microsoft to loosen the data protections around the service, or go around them entirely. This is a particular concern as the systems in the service become more complex and agentic and more difficult for Microsoft to audit. Model weights are highly opaque and Microsoft cannot trace the exact possible behaviour of these systems. What if GPT-6 changes its own weights during inference for example? How can Microsoft ever understand if that’s a true critical piece of functionality or a proxy method to access customer data etc.

How is it 3 months behind if you get access to current OpenAI models?

It took like 2 extra months for Azure OpenAI to get GPT-4 turbo. There's a noticeable time delay between OpenAI deploying their latest model and when Microsoft manages to shove it in Azure.

i give their intent the benefit of the doubt but I’ve been on the other side of too many data collection systems to trust that theirs is foolproof and more secure than my local machine.

fwiw 23andme genetic data (distinct from summary ancestry data) has not leaked afaik

These small SQL LLMs are indeed worthless, since LLM performance on SQL queries is quite limited right now, every % of accuracy matters.

0 employment contracts or user facing ToS I've read make a distinction between sharing schema vs data with unvetted third parties. In my opinion, schemas reveal pretty valuable info about an application (and they're absolutely valuable in aggregate because you can use them to train AI data scientists).

Maybe I sound like a curmudgeon but since I have the option of running the AI locally with a 5 percentage point accuracy loss, I absolutely will. If GPT-4 was 100% that would be different because you could build totally different things, but 83% has most of the same design problems as 78%.

https://time.com/6556827/openai-us-military-cybersecurity/

I have written a bunch of more or less complicated SQL during my career. And I am pretty sure that if I need to write a SQL statement that's anything but select * from table, my output won't work 75% of time.

I may be special case, but typically if I work on a hard problem, it is not a single hard problem but a sh*tload of connected simple problems. If I can get someone to solve the simple problems 75% of the time correctly so that I can spend my time figuring out how those simple problems are to be connected, I'm ore than happy. And that's exactly how I use chatgpt. I have learned not to ask too complex questions from it. But the simple ones, it mostly aces and when it does not , they are easy to spot, as it is not that I could not have solved them myself, I just did not want to spend time for that. Now, if only the chatgpt was not almost as lazy as me to produce long simple stuff, that would be awesome.