Unknown number of resulting columns SQL-L044

Queries that produce an unknown number of result columns are an anti-pattern.

Querying all columns using * produces a query result where the number or ordering of columns changes if the upstream table’s schema changes.

This should generally be avoided because it can cause slow performance, cause important schema changes to go undetected, or break production code.

Eg, If a query does SELECT t.* and is expected to return columns a, b, and c, the actual columns returned will be wrong/different if columns are added to or deleted from the input table.

Bad practice

WITH cte AS (
    SELECT * FROM foo
)

SELECT * FROM cte
UNION
SELECT a, b FROM t

Recommended

Specify columns explicitly.

WITH cte AS (
    SELECT * FROM foo
)

SELECT a, b FROM cte
UNION
SELECT a, b FROM t