..

🗄️🔍 database design & SQL links

SQL Online Learning Resources

Snowflake Cheat Sheet

Functions

NVL2 - Return values depending if first value is null.

ARRAY_AGG - Returns inputs pivoted into an array.

PIVOT - Transform narrow table to wider table by expanding dimensions to columns.

VALUES - Select from constant values

GENERATOR - Creates rows of data based either on a specified number of rows

CONNECT BY - Joins a table to itself recursively

ANY_VALUE - Returns non-deterministic value as an aggregate or window function

OBJECT_KEYS - Returns array of the top level of json object

ENCRYPT - Encrypts values with a passphrase

DECRYPT - Decrypts values with a passphrase, add to_varchar([value], ‘utf-8’)

SYSTEM$CLUSTERING_INFORMATION - Returns clustering information

useful examples

Clustering

SYSTEM$CLUSTERING_INFORMATION( '<table_name>'
    [ , { '( <expr1> [ , <expr2> ... ] )' | <number_of_errors> } ] )

Example

select SYSTEM$CLUSTERING_INFORMATION('models.staging.transactions', '(date_trunc(day, CREATED_AT_LOCAL))')

Pivot without using pivot example here

select
    parsed:project_name::string "project",
    parsed:"project mgr"::string "project mgr",
    parsed:"chief architect"::string "chief architect",
    parsed:"dba"::string "dba"
from
    (   select
            parse_json(proj_roles) parsed
        from
            (   select
                    concat('{', concat('"project_name','":"',project_name,'",', listagg(concat('"'
                    ,project_role, '":','"',person,'"'),',') ),'}')proj_roles
                from
                    (   select
                            project_name,
                            project_role,
                            person
                        from
                            "project_mgmt")
                group by
                    project_name));

Parsing email domains

SPLIT(email_address, '@')[SAFE_OFFSET(1)]

Convert date time to readable clock time

to_char({datetime_field}, 'HH12:MIPM')

Generator example to create 365 days into the future. Neat.

select
  dateadd(
    day,
    '-' || row_number() over (order by null),
    dateadd(day, '+1', current_date())
  ) as date
from table (generator(rowcount => 365))

How to overwrite Nulls using window function

select 
	c1, 
	coalesce(c2, 
		first_value(c2) over (partition by c1 order by c2 nulls last)
		) as c2
  from (values (1, 'one'), (1, null)) as v1 (c1, c2);

NVL2 example

NVL2( <expr1> , <expr2> , <expr3> )

If expr1 is NOT NULL, then NVL2 returns expr2.

If expr1 is NULL, then NVL2 returns expr3.

Convert UTC to Local

src.createdat::timestamp_ntz as created_at_utc,
convert_timezone('UTC', 'America/Los_Angeles', created_at_utc) as created_at_local,

Lateral Flatten

select id as "ID",
   f.value as "Contact",
   f1.value:type as "Type",
   f1.value:content as "Details"
 from persons p,
   lateral flatten(input => p.c, path => 'contact') f,
   lateral flatten(input => f.value:business) f1;