Skip to content
The Data EngineeringThe Data EngineeringBeta
About
Main content

A conversation has state; SQL as the interface

What changes when a back-channel exists, and why is declarative SQL the shape that lets the callee be smart?

The back-channel appears

A file must carry everything, because nobody can ask it anything. A protocol can ask. So:

  • Meaning can be negotiated at connection time.
  • So the payload no longer has to describe itself.
  • So the contract moves out of the bytes and into the conversation.

But the brings its own constraint, and it dominates this article:

A file read that fails can be repeated: nothing was in flight. A request that fails leaves you unable to tell three cases apart:

  1. It never happened.
  2. It happened, and the reply was lost.
  3. It is still happening.

This ambiguity is not an implementation defect. It is a theorem about communication over an unreliable channel (known in computer science as the Two Generals problem), and no protocol removes it (why a missing reply tells you nothing). Every protocol only decides who has to cope.

So the questions now are: what is agreed once, and what is agreed per request? What happens when the connection dies? How does the receiver tell the caller what it may do next? Questions about efficiency (, compactness, columnar payloads) are the same as in the file part and matter less here than people assume.

SQL as the interface mechanism

At a boundary, SQL is not mainly a language. It is the decision to make the interface . That choice has a mechanical consequence that no other interface here achieves.

  • Declarative: the caller says what it wants. The callee is free to choose how. That freedom is what makes a query optimizer (the part of a database that chooses an execution plan) possible.
  • Imperative: the caller says how: fetch rows, loop, filter in the client. The plan is fixed at the caller, and optimization becomes structurally impossible.
# Imperative: the client decides the plan. The server must ship every row.
total = 0
for row in cursor.execute("SELECT * FROM shipments"):
    if row.country == "IN":
        total += row.weight_kg

# Declarative: the server decides the plan (index? scan? parallel?).
cursor.execute("SELECT SUM(weight_kg) FROM shipments WHERE country = 'IN'")

This is why SQL survives every attempt to replace it. The replacements keep being imperative, and an imperative interface gives up the right to be optimized.

The second property: SQL is text over a wire. The query is composed by one party and interpreted by another party that may have a different schema, different types and different functions. Nothing checks the query until the server parses it. This is late : the failure appears at the callee, at run time, not at the caller's compile time.

Dialects: not vendor sloppiness

Many people see SQL dialects as sloppy work by vendors. That is wrong. A standard can specify only the intersection of what all engines can do. When an engine does something better than the standard (approximate aggregation, nested types, time travel, sampling), there is no standard spelling, so it gets a private one. Divergence is therefore produced by differences in capability, not by bad intent, and it grows fastest where engines innovate. The corollary: portable SQL is available only at the price of using the weakest common feature set. So "write once, run on any warehouse" is an unstable promise rather than a lie.

Reflection

information_schema and similar tables are the reflection mechanism: the protocol describes itself through itself.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'shipments';

That is what allows a generic client to work with an engine it was never built for. It is the same trick as a file: pay a little to carry a description, and buy compatibility with strangers.

In one sentence: declarative is the only interface shape that lets the callee be smart; everything SQL is criticized for is the price of that.