How does Snowflake handle NULL values?

Question

For example, in my dataframe I have a column of NULL values that I plan to edit later, let's say for letter grades. Here is some example for now:

import csv
import pandas as pd
import numpy as np
df = pd.read_csv('MOCK_DATA.csv')
df.head()

 

   id first_name last_name                     email  null field  blank_field
0   1      Paule    Tohill   [email protected]       False         NaN
1   2       Rebe   Slyford  [email protected]        True         NaN
2   3   Angelita    Antoni        [email protected]       False         NaN
3   4      Giffy      Dehm       [email protected]       False         NaN
4   5        Rob    Beadle       [email protected]       False         NaN

I want to import the data to later change the blank_field column's type. I understand how to use SQLAlchemy with the Python connector.

df.to_sql(con=con, name='Grades', if_exists='replace', flavor='mysql')

Do I need to specify anything here to change the blank_field -column? How will Snowflake handle the NaN values?

Answer 1

The column length will be the max of the longest value inserted or VARCHAR(16777216) if the column only contains NULL.

Then you can increase the size of the varchar column after creating the table but you can't decrease it. (Only very limited cases where you wouldn't be suited to using VARCHAR(MAX). Your only charged for what you actually store and the performance is based on the max length of the data, not the allowable limit).

https://docs.snowflake.net/manuals/sql-reference/sql/alter-table-column.html

create or replace temp table x as
select $1 as c1,$2 as c2
from values
('NaN',NULL)
;

desc table x;

create or replace temp table y as
select $1 as c1,$2 as c2
from values
('NaN',NULL)
,('A','B')
;

desc table y;

create or replace temp table z as
select $1 as c1,$2 as c2
from values
('A','B')
,('NaN',NULL)
;

desc table z;