SnowPro Associate: Platform Question 80
Single answer○ PythonA data engineering team needs to load a daily CSV file from an internal application into a Snowflake table using Python. The file is generated on an application server outside Snowflake. The team wants a solution that is simple to automate in a Python script, minimizes custom parsing logic, and uses Snowflake features for bulk loading. Which approach should the engineer use?
- A
Use the Snowflake Connector for Python to upload the file to a stage and then execute a COPY INTO command to load the target table.
- B
Use a Python worksheet in Snowsight to read the local CSV file directly from the application server and insert rows one at a time into the target table.
- C
Use Python UDFs to open the CSV file from the application server and write the contents directly into the target table.
- D
Use only INSERT statements from the Python script for each row because Snowflake bulk loading requires files to be manually uploaded in the UI first.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use the Snowflake Connector for Python together with Snowflake staging and COPY INTO. In a real-world Python-based ingestion workflow, the connector is commonly used to authenticate, execute SQL, and automate file loading steps. Snowflake best practices for structured file ingestion recommend staging files first and then using COPY INTO for efficient bulk loading, rather than row-by-row inserts. Python worksheets and Python UDFs serve different purposes: worksheets are for interactive development in a Snowflake-managed environment, and UDFs are for in-database processing of query data, not external file ingestion. Relevant Snowflake documentation includes the Snowflake Connector for Python, data loading guidance for PUT/COPY INTO patterns, and documentation describing the scope of Python worksheets and Python UDFs.
- A. Correct.
Correct. For files that exist outside Snowflake, a common and recommended pattern is to use the Snowflake Connector for Python to automate loading. The script can upload the file to an internal stage and then run COPY INTO to bulk load the data. This approach is practical, scalable, and aligns with Snowflake best practices for loading structured data files such as CSV.
- B. Incorrect.
Incorrect. Python worksheets in Snowsight run in Snowflake-managed environments and do not provide direct access to arbitrary files on an external application server. Also, inserting one row at a time is less efficient than using Snowflake's bulk loading capabilities. This option reflects the misconception that Python worksheets are equivalent to running a local Python script with direct filesystem access.
- C. Incorrect.
Incorrect. Python UDFs are designed to process data already available in Snowflake queries, not to access arbitrary files on external application servers for ingestion workflows. They are not the correct mechanism for orchestrating file uploads and bulk data loads. This option confuses data transformation logic with ingestion/orchestration tasks.
- D. Incorrect.
Incorrect. Snowflake bulk loading does not require manual upload through the UI. Files can be staged programmatically using SQL commands or client tools, including the Snowflake Connector for Python, and then loaded with COPY INTO. Row-by-row INSERT statements are generally less efficient and are not the preferred approach for loading CSV files at scale.