An example of how to load an delimited file with ragged columns (or tailer). Loads view external table

Load_ragged_TSV_External_Table.sql

/* Original Data: test_data.txt

col1~col2~col3~col4~col5
a1~b1~c1~d1~e1
a2~b2~c2~d2~e2
a3~b3~c3~d3~e3
a4~b4~c4~d4~e4
a5~b5

*/

-- define tilde delimited file format
-- Note: the argument 'ERROR_ON_COLUMN_COUNT_MISMATCH' is not needed for this approach to work
-- con't: but it is needed for the approach that loads directly to Snowflake
-- con't: it is included here to keep continuity in the ragged table load examples

CREATE OR REPLACE FILE FORMAT tilde_format
TYPE = 'CSV'
FIELD_DELIMITER = '~'
SKIP_HEADER = 1
ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE
NULL_IF = ('\\N', 'NULL', 'NUL', '');

-- Create an external table
-- 'col1' through 5 can be defined of the schema is known, otherwise omit and work with Variant column called 'value'
create OR REPLACE EXTERNAL table TEST_TSV_TABLE4(
    col1          VARCHAR AS (value:c1::varchar),
    col2          VARCHAR AS (value:c2::varchar),
    col3          VARCHAR AS (value:c3::varchar),
    col4          VARCHAR AS (value:c4::varchar),
    col5          VARCHAR AS (value:c5::varchar),
    FILENAME      VARCHAR AS (METADATA$FILENAME),
    ROWNUMBER     NUMBER  AS (METADATA$FILE_ROW_NUMBER), 
    MD5HASH       VARCHAR AS (METADATA$FILE_CONTENT_KEY), 
    L_MODIFIED    TIMESTAMP_NTZ AS (METADATA$FILE_LAST_MODIFIED), 
    SCAN_TIME     TIMESTAMP_LTZ AS (METADATA$START_SCAN_TIME))
 location = @trp_ext_stage
 auto_refresh = true
 PATTERN='.*\.txt'                       // can use regex to leverage file name format
 file_format=tilde_format;

-- Optional: Select from external table
SELECT * FROM TEST_TSV_TABLE4 LIMIT 100;

-- Create View/Table from External table
CREATE OR REPLACE VIEW New_TSV_View AS
SELECT *
FROM TEST_TSV_TABLE4;

-- Optional: Select from View or Table
Select * FROM New_TSV_View;