CODEWITHSUNDEEP
sqlunit-testingsnowflake-cloud-data-platform
Marty C.
Marty C.

Reputation: 696

How to select multiple rows with fake data in Snowflake

In Snowflake, is there an easier way to select multiple rows of fake data in memory without loading actual data into a table? Below is a sample query showing how I currently generate an object containing multiple rows of fake data.

with
  fake_row_1 as (
    select
      1 as num,
      'one' as txt
  ),
  fake_row_2 as (
    select
      2 as num,
      'two' as txt
  ),
  fake_row_3 as (
    select
      3 as num,
      'three' as txt
  ),
  fake_table as (
    select * from fake_row_1 union
    select * from fake_row_2 union
    select * from fake_row_3
  )
select *
from fake_table

I am trying to test changes to query logic, and instead of loading and unloading test data into a test table, I am trying to stage a fake table in memory to more quickly validate expected results.

Ideally, I would be able to run a query similar to the following.

with
  fake_table as (
    select
      columns (num, txt)
      values (1, 'one'),
             (2, 'two'),
             (3, 'three')
  )
select *
from fake_table

Upvotes: 5

Views: 9701

Answers (3)

KyleMit
KyleMit

Reputation: 30107

You don't even need a CTE. Using the values sub-clause in the from clause

allows the specification of a set of constants to be used to form a finite set of rows.

The syntax also allows you to specify an alias for the table name and column names like this:

SELECT *
FROM (VALUES  
  (1,'one'),
  (2,'two'),
  (3,'three') 
) AS fake_rows (num, txt)

Which will yield the following table fake_rows

NUM TXT
1 one
2 two
3 three

Upvotes: 5

Mike Gohl
Mike Gohl

Reputation: 737

Could you do the union in the CTE?

with
  fake_rows as (
    select
      1 as num,
      'one' as txt
  union
    select
      2,
      'two'
  union
    select
      3,
      'three'
  )
select *
from  fake_rows

This might be a little cleaner:

with
  fake_rows as (
    select $1 AS txt,
           $2 as num
    FROM 
    (VALUES  
     (1,'one'),
     (2,'two'),
     (3,'three') 
         )) 
select * from  fake_rows

Upvotes: 5

arunve
arunve

Reputation: 3

I think you answered your question here, you can generate hard coded data like above within a create table statement and that should get you what you need.

Upvotes: 0

Related Questions