Why would using column aliases in a select statement fill in null values with the column name?

Question

I am using DBT to select data with column aliases, and I am wondering why the column name would become the default value if there are no values in the column I am renaming. How would you fix this select statement so that it doesn't fill in the null values with the column name?

Walkthrough:

I have a table that is basically empty in my database which I generated from dbt seed.

my_data
----┬------┬------
 A  |  B 1 |  C 2
----┼------┼------
 g  |      |  
----┼------┼------    
 h  |      |     
----┴------┴------

and then I select this data in dbt_model and do a dbt run.

select 
    A as column1, 
    'B 1' as column2, 
    'C 2' as column3
from {{ ref(`my_data`)}}

for some reason this ends up with a table looking like this

dbt_model
---------┬---------┬---------
 column1 | column2 | column3
---------┼---------┼---------
    g    |   B 1   |   C 2  
---------┼---------┼---------   
    h    |   B 1   |   C 2  
---------┴---------┴---------

but that isn't what I wanted. I wanted this. So how do I do a column alias to rename the columns in the select statement? Why would it behave this way?

dbt_model
---------┬---------┬---------
 column1 | column2 | column3
---------┼---------┼---------
    g    |         |        
---------┼---------┼---------   
    h    |         |        
---------┴---------┴---------

Are you sure you don't have single quotes like so - `'B' as column2`. — Andrew, Sep 14 '22 at 14:47
Actually I do have quotes! The columns have spaces in them, so I had to use the single quotation marks. Just edited the question to include those. I don't understand yet why that would affect the query though. — iamyolanda, Sep 14 '22 at 14:56
Single quotes indicate string data. If your column names have spaces in them (bad bad bad design...) you will have to refer to the documentation for your RDBMS on how to handle that. Could be double quotes, back ticks, square brackets. — Andrew, Sep 14 '22 at 15:34

score 0 · Accepted Answer · answered Sep 14 '22 at 16:37

In most SQL dialects, single quotes are used as string literals. So in your query:

select 
    A as column1, 
    'B 1' as column2, 
    'C 2' as column3
from {{ ref(`my_data`)}}

You are not selecting from the columns called B 1 and C 2, you are selecting the string literals "B 1" and "C 2", which is why your result is what it is.

In Postgres-like databases, you should use double quotes for names with spaces in them:

select 
    A as column1, 
    "B 1" as column2, 
    "C 2" as column3
from {{ ref(`my_data`)}}

In MySQL-like dialects (including Google BigQuery), you should use backticks instead:

select 
    A as column1, 
    `B 1` as column2, 
    `C 2` as column3
from {{ ref(`my_data`)}}

Why would using column aliases in a select statement fill in null values with the column name?

1 Answers1