SELECT and Column Aliasing

Why a DE almost never ships SELECT *

When a raw table lands in the warehouse, its columns are named whatever the source system chose: ord_id, cust_nm, amt_c. Those names are cryptic, and they can change without warning. The first transform a data engineer writes, the staging model, selects only the columns downstream needs and renames each to a clean, predictable convention (usually snake_case).

SELECT * is the opposite of that discipline. It drags every column downstream, silently gains a column the moment the source adds one, and hides which fields your model actually depends on. Explicit projection is a contract: it documents exactly what you consume and insulates you from upstream churn. That is why the Apply and Practice exercises ask you to name each column and drop the ones a mart does not need (ord_status, internal_flag).

What SELECT and AS actually do

SELECT is the projection operator: it chooses which columns come back and in what order. It works row by row and never changes how many rows you get. Filtering rows is WHERE's job; setting the grain is GROUP BY's job. Projection only reshapes the columns.

AS renames a column in the output. The alias becomes the output column's name, independent of what the source called it. Downstream models bind to your stable alias, not the source's cryptic identifier.

SELECT
  ord_id  AS order_id,
  cust_id AS customer_id,
  amt_c   AS amount_cents
FROM orders;

Against the seed rows, that returns:

order_id | customer_id | amount_cents
1001     | 7           | 4999
1002     | 7           | 1250
1003     | 9           | 10000

Three input columns become three cleanly named output columns. ord_status is simply not projected, so it never reaches downstream. Note the amounts are in cents (4999 is 49.99), which is exactly why the alias amount_cents is worth writing: it encodes the unit for whoever reads the model next.

Pitfall: a missing comma silently drops a column

AS is optional in SQL. ord_id order_id means the same as ord_id AS order_id. That convenience is a trap. If you forget a comma between two columns, the query still runs:

SELECT ord_id cust_id FROM orders;   -- one column named cust_id, holding ord_id values

SQLite reads cust_id as the alias of ord_id, so you get a single column named cust_id full of order-id values, and no error. The fix is a comma between every projected column. When a projection returns fewer columns than you expected, a swallowed comma is the first thing to check.

Interview nuance: SQL's written order is not its execution order. The engine logically evaluates FROM and WHERE before it projects the SELECT list, so a SELECT-list alias is not yet visible in the same query's WHERE clause. In the SQL standard, and in PostgreSQL, MySQL, and SQL Server, SELECT amt_c AS amount_cents FROM orders WHERE amount_cents > 2000 fails there with an unknown-column error. SQLite, the engine behind this lesson, is unusually lenient and resolves the alias anyway, so that exact query happens to run here. The portable habit is to filter on the real column, WHERE amt_c > 2000. Interviewers ask about alias-in-WHERE to check that you understand logical query order, and that engines differ on it.