[vjerancrnjak]

Depends.

If you do a query that computes something, the output columns have data types that you’d like to validate.

Checking that you receive an int, string or enum is unavoidable. Even a JOIN might surprise you with null values.

[sgarland]

> Checking that you receive an int, string or enum is unavoidable.

How would you be unaware of the data type if you defined the schema? Also, an ENUM is returned as a string; it’s only stored internally as an integer.

> Even a JOIN might surprise you with null values.

If you have foreign key constraints, you should never be able to get into a situation where you’re surprised by a NULL from an OUTER JOIN. You can certainly still have NULLs, but they shouldn’t come as a surprise.

[vjerancrnjak]

You can name the groups, there’s no schema for your group by response.

I can have a join on a cte or tmp table, then I’m out of the usual checks.

You can also have old code running on new schema, so better that it dies on load.

[sgarland]

> join on a cte

I’ll grant you that this can generate NULLs in a variety of ways (implicit type conversion, for one), but I also think that those issues could be caught via linting if nothing else. I’ll also grant you that this is shifting the goalposts a bit.

> old code running on new schema

Yeah, this would be the primary offender. I was thinking of perfect schema:code coupling, without needing to worry about other people doing dastardly things, but that’s sadly unrealistic for many orgs.