How to generate data to test Snowflake abilities to handle tables with thousands of columns?

With this script you can create a table with 2000 (or any number) of columns, giving each a default random value:

CREATE or replace PROCEDURE create_wide_table()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
ss = 'create or replace table wide2000 (id int';
for (const x of Array(2000).keys()) {
  ss += ', a' +x + ' int default uniform(1, 10000, random())';
}
ss += ');'
//return ss;
  var rs = snowflake.execute( { sqlText:  ss       } );
  return 'Done.';
  $$;

That will generate and execute a SQL query that looks like:

create or replace table wide2000 
  (id int
   , a0 int default uniform(1, 10000, random())
   , a1 int default uniform(1, 10000, random())
   , ...
);

You can modify the script to generate data that better resembles your type of data and sparsity.

What's cool about this table is that it has default random numbers for most columns. Therefore adding a million rows to it becomes really easy.

Create the table first:

call create_wide_table();

Generate a million rows:

insert into wide2000 (id) 
select seq8()
from table(generator(rowcount => 1000000)) 

Run your experiments:

select *
from wide2000
limit 10

For more complex data generation needs, check https://github.com/RobertFehrmann/fehrminator.

Below similar implementation as Felipe's answer, but using SQL only for ad-hoc table creation.

Code generator:

SET cols_num = 20;
SET rows_num = 100;
SET tab_prefix = 'wide';

SELECT 
   LISTAGG(REPLACE(CHAR(13)||',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())'
                  ,'<index>', seq8()+1)
           ,'') WITHIN GROUP(ORDER BY seq8()) AS column_list
   
   ,REPLACE(REPLACE(
          'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
          ,'<table_name>', $tab_prefix || $cols_num)
          ,'<column_list>', column_list) AS create_table_sql
          
   ,REPLACE(REPLACE(
          'INSERT OVERWRITE INTO <table_name>(id) SELECT seq8()' || CHAR(13)
          'FROM TABLE(GENERATOR(rowcount => <rows_num>));'
          ,'<table_name>', $tab_prefix || $cols_num)
          ,'<rows_num>', $rows_num) AS insert_into_sql
FROM TABLE(GENERATOR(rowcount => $cols_num));

Output:

CREATE OR REPLACE TABLE wide20(id INT  
,a1 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a2 INT DEFAULT UNIFORM(1, 10000, RANDOM())
...
,a19 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a20 INT DEFAULT UNIFORM(1, 10000, RANDOM()));

INSERT OVERWRITE INTO wide20(id) SELECT seq8() FROM TABLE(GENERATOR(rowcount => 100));

Data check:

SELECT * FROM wide20;

While these solutions are fine for adding data, they probably don't answer "evaluating" performance, as Snowflake performance is highly dependent on the nature of the data. E.g. E.g. filling it with random numbers would likely make pruning nearly non-existent. I would discourage thousands of columns, unless the non-NULL data is very sparse. While Snowflake only scans required columns for a query, thousands of columns is going to allow very few rows in a micro-partition, requiring much more Remote I/O and warehouse storage. This would tend to offset the benefits of pruning. What is the scenario for this? Seems you'd be much better off segmenting the data into separate tables and joining when necessary.