Search code examples
sqlsnowflake-cloud-data-platformdbt

How to skip a row in Snowflake select statement

I'm trying to run the query below to load a table in Snowflake via DBT. The data source is a csv file with a header. How I can skip the header in a statement like this? I understand that there's an option to use Copy Into, but I hope to use this query format. Thanks.

Select t.$1 as col1, t.$2 as col2
from @stage (pattern =>'.*/path/to/filename.csv') t //how do I skip the first row in filename.csv?
Solution

  • Snowflake has metadata info available to you when reading from a stage for things like file name, row number, file timestamp, etc. You can use that to skip the first row:

    Select t.$1 as col1, t.$2 as col2
from @stage (pattern =>'.*/path/to/filename.csv') t
where metadata$file_row_number > 1;

    Full list of metadata fields can be found here.