Dwh.dev documentation
  • What is Dwh.dev?
  • Features
    • Data catalog
      • PIPEs
      • Fancy SQL Highlight
    • Data Lineage
      • Object-level lineage
      • Column-level Lineage
      • In-query lineage
      • Navigation
      • JOIN and WHERE
      • Equals column lineage
      • Strong and Weak dependencies
      • Default and Virtual Columns
      • Implicit Types casting
      • Circle dependencies
      • Argument forwarding
    • TASKs
  • Integrations
    • Snowflake
      • Offline mode
      • Secured online mode
      • Snowflake Marketplace
    • DBT
    • ETL
    • BI
      • Looker
      • Other
    • API
  • Snowflake SQL Syntax and Behavior
    • Identifiers
    • Reusing column aliases
    • SELECT * ILIKE EXCLUDE REPLACE RENAME
    • Scalar functions name resolution special behavior
    • Functions overloading
    • CTE as an expression alias
    • ASOF Join
    • UDF named arguments
    • Objects auto renaming
    • Columns auto renaming
Powered by GitBook
On this page

Was this helpful?

  1. Snowflake SQL Syntax and Behavior

Reusing column aliases

In any database, you can use aliases for both columns and expressions within queries:

SELECT
    id AS user_id,
    name AS user_name,
    age AS user_age,
    age * 2 AS user_age_doubled
FROM users;

But what can we do with these aliases? Database vendors allow different things. For example, in MySQL and PostgreSQL, aliases can only be used in GROUP BY and ORDER BY. In Clickhouse and Snowflake, however, aliases can be used everywhere. But, as usual, there are nuances :)

Let's create syntactically identical VIEWs:

CREATE TABLE abc AS SELECT 1 AS a, 100 AS b, 1000 AS c;

CREATE VIEW v13 AS 
SELECT 
    a+1 AS b,
    b+1 AS c
FROM abc;

CREATE VIEW v14 AS 
SELECT 
    a+1 AS d,
    d+1 AS e
FROM abc;

At first glance, the lineage for these views should be the same. But let's see what happened:

Why in V13 the column C not reuse the alias B? Because in Snowflake when using an alias equivalent to the original column name from the source, the original column takes precedence!

By the way, in Clickhouse, it works differently...

What was meant by saying that aliases work everywhere?

Aliases can be reused in JOIN and WHERE clauses:

CREATE TABLE t12 AS SELECT 1 AS a, 100 AS b;
CREATE TABLE t13 AS SELECT 1 AS c, 100 AS d;

CREATE VIEW v15 AS
SELECT 
 a + b AS e,
 c + d AS f
FROM 
  t12 
  JOIN t13 ON e = f;

or even like this:

CREATE VIEW v16 AS
SELECT 
  a1 + b1 AS e,
  c1 + d1 AS f,
  ROUND(e, f) AS g,
  ROUND(f, e) AS h 
FROM 
  t12 AS t121(a1, b1) 
  JOIN t13 AS t131(c1, d1) ON e = f
WHERE 
  g = h;
PreviousIdentifiersNextSELECT * ILIKE EXCLUDE REPLACE RENAME

Last updated 8 months ago

Was this helpful?

Since in we display not only the data flows but also the , you will also see the original column sources in the corresponding section.

Dwh.dev
columns used in JOIN and WHERE