Archives for January 2025

Welcome to this week’s Frosty Friday challenge! In this challenge, you’ll explore Snowflake’s powerful metadata and schema inference capabilities. Your task is to use INFER_SCHEMA to extract metadata from a Parquet file in a stage and transform it into a Snowflake table.

By following a series of steps, you will go from querying the metadata of a staged file to creating a Snowflake table using the inferred schema. Can you connect the dots and complete the pipeline?

Using the INFER_SCHEMA and GENERATE_COLUMN_DESCRIPTION functions, as shown in the first image, your task is to:

1. Query Metadata: Use the INFER_SCHEMA function to extract the metadata of a staged Parquet file. This metadata will include column names, data types, and file-level details.
2. Transform Metadata: Generate a CREATE TABLE statement using the inferred metadata (as shown in the second image) to define the table structure.

Enjoy finding it and remember to post your code 🙂

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.

Snowflake’s support for Jinja now includes imports – this means you can modularise your code, and use macros.

Your challenge is to create two scripts:

utils.sql:
Will contain a macro called power_of_two that meets the following requirements:

1. Accepts a list of numbers
2. For each number, returns:
   • The original number
   • The squared (two the power of two) number
3. Unions all

main.sql:
Will be the file that simply calls the macro – it should use a simple list of `[1, 2, 3, 4]`

If all is correct, you ought to get a result like:

Happy Jinjaing!