Crack the Snowflake Data Engineer Interview 100+ Q&A.

Learn whether to apply transformations during copy loading in Snowflake. Avoid transformations for raw tables to speed loads; perform transformations in SQL after loading, except for large files with columns.

Establish connectivity between Snowflake and cloud storage by creating a storage integration, configuring AWS roles, and using an external stage to copy data to and from Snowflake.

Explore ingesting data into Snowflake using external tables from cloud storage, inserting into a target table, and streaming via streams for near real-time loads, without snowpipe, copy, or DBT.

Validate the file with copy command in validation mode to return all errors before loading into the target table; fix issues and revalidate if errors appear.

Set up automated validation and quality control for source files before loading into Snowflake, using Python to read S3 files and validate, or validate in tables and skip bad files.

Learn how Snowflake prevents duplicate file loads by storing load metadata. Explore when to reload after truncation and how to use the copy command's force option for large data loads.

Explore data loading strategies in Snowflake projects using tasks and stored procedures for batch merges. Use streams for near real-time updates, Snowpipe for automated ingestion, and external tables.

Explain why data lake storage should use parquet for ingestion, noting Snowflake copy performs better with CSV, but avoid converting parquet to CSV to prevent duplication.

Truncating a table in Snowflake cannot be done with a UDF; UDFs can't run DDL or delete statements and require a select to invoke. Use a stored procedure for truncation.

Explore the benefits of stored procedures and user-defined functions (UDFs) in Snowflake, including code reusability, single source logic for margins, and composing multiple statements into a reliable sequence within tasks.

Assess how stored procedures encapsulate the copy command to ingest data files from cloud storage, enable multi-statement sql via tasks, and truncate staging tables for batch loads.

Snowflake UDFs support SQL, Python, JavaScript, and Java. In practice, answer with SQL for UDFs and stored procedures unless you are confident in JavaScript or Java.

Explore sending emails from Snowflake via notification integration, with email type and enabled settings, using allowed recipients. Use the send email command in stored procedures to report progress or failures.

Explore how Snowflake integrates with scheduling tools like Airflow or Control-m to create dependencies between cloud storage files and Snowflake jobs, and to send success or failure emails.

Airflow enables cross-system job dependencies that Snowflake tasks cannot, coordinating MySQL data load, Snowflake copy, and post-process archiving to S3 after successful ingestion.

Identify when Snowpipe is not recommended for files outside 10 MB to 250 MB, and use batch load with copy command or external tables with streams for near real-time ingestion.

Snowpipe enables near real time data ingestion by loading data as soon as a file appears in the source location, automating the loading process—not achievable by scheduled batch jobs.

Rely on cloud storage notifications; Snowpipe ingests new files and ignores files created before it. Load older files with a copy command, or alter pipe refresh covers only seven days.

Explore snowpipe, a serverless data ingestion pipeline that auto loads files from S3 or Azure Blob storage into Snowflake, enabling near real-time automated data availability with no warehouse management.

Pause a Snowpipe pipe when files are skipped due to data issues. Check pipe status, then issue an alter pipe command to pause, investigate, resolve with the vendor, and resume.

Set up Snowpipe by creating a storage integration, defining a file format, creating the stage, testing the copy command, creating the pipe, and configuring cloud notifications.

Explore a Snowpipe scenario for continuous, automated ingestion of IoT data from S3. See how GPS updates every five minutes in a car loan portfolio are available to business users.

Learn to monitor and troubleshoot snowpipe by checking pipe status, reviewing copy history, validating loads, fixing data issues, and restarting to process new files.

Learn how Snowpipe handles bad rows by default skipping entire files, and how to set on error to continue so only bad rows are skipped and good data loads.

Snowflake tasks schedule SQL or stored procedure executions, run on fixed times or intervals, and may use streams to trigger when data exists, with hierarchical dependencies; external integrations are limited.

Learn to monitor Snowflake tasks using the information schema task history, view recent executions, and troubleshoot failures by suspending, resuming, or viewing errors via a created view.

Keep tasks inactive by default to prevent unintended actions. Activate using alter task resume and verify with show tasks before scheduling runs, such as a 20-minute truncate of a table.

Snowflake's serverless tasks run without a warehouse, automatically allocating compute resources; provide an initial warehouse size if desired, then Snowflake auto-adjusts based on task history.

Learn the maximum limits of a dag: up to 1000 tasks including the root, with 100 predecessors and 100 child tasks, and how to form dependencies using the after keyword.

Suspend the failing task to prevent repeats, remove it from the task tree, perform table maintenance, fix and test changes, then resume the task with alter task remove after clause.

Demonstrate three use cases of tasks in a Snowflake project: daily storage metrics with a stored procedure, task-driven streams for CDC, and 60-minute refreshed reporting tables.

Use the task dependent view to determine parent and child relations in a task tree, showing grandchildren with recursive=true and stand-alone tasks clearly.

Learn how Snowflake's resource monitors manage credits in a metered service by setting limits, triggering alerts, and suspending warehouses to prevent excessive credit consumption from poorly written queries.

Snowflake allows one account-level resource monitor; warehouse-level monitors are possible, but a warehouse cannot belong to multiple account-level monitors, and thresholds trigger suspends and notifications as credit quotas are reached.

Explore how a resource monitor controls credit usage with notify and suspend at 90%, notify and suspend immediately at 95%, and notify alerts at 85% to prevent bill shock.

Estimate your Snowflake data warehouse size to reflect project complexity, aiming for a 20 TB sweet spot to show experience with a complex environment.

Explore how to generate primary keys in snowflake using sequences in insert statements or the autoincrement option, demonstrated with inserting rows into employee tables.

Learn how to compare a customer table in qa with the production table across different Snowflake accounts, by sharing data to qa or using Spark and Python.

Prefer transient tables for daily-truncated staging data. They support up to one day of time travel, unlike temporary tables that last only a session.

Learn how transient schemas restrict permanent tables, enabling only transient or temporary tables, while permanent schemas support transient, temporary, and permanent tables; a live demo verifies the behavior.

Snowflake enforces only not null constraints; primary keys are not enforced, so duplicates can be inserted, while not null constraints prevent null values and can improve query performance.

Discover what a stage in Snowflake is, including external stages in cloud storage and internal stages stored inside Snowflake, and how the copy command loads data.

Use internal stages in Snowflake to store files with the put command when no cloud storage account is available, before processing and loading with the copy command.

Determine the number of files in a Snowflake stage using the list (LZ) command in SnowSight for both external and internal stages, to load files from a stage efficiently.

Explore the three internal Snowflake stages: user stage, table stage, and named internal stage, and learn how privileges, automatic creation, and loading data across stages work, including listing files.

Learn how to add, download, and remove files from a Snowflake internal stage using SnowSQL commands: put to upload, get to download, and remove to delete, with authentication via CLI.

Install snow sql on the on-prem server, authenticate with key pair, and use put to upload files to the internal stage, then copy to load into the target table.

Explain how a Snowflake stream becomes empty as data is consumed, and how to purge or consume it using create table from stream, create or replace, and merge.

Learn how to join a stream and a table in Snowflake using the join keyword for an append-only stream, and the merge command to consume updates, deletes, and inserts.

Explore a streams use case in Snowflake, ingesting IoT data via Snowpipe into raw tables and moving it to a permanent target table with a stream, task, and stored procedure.

A 1 GB file arrives and must be ingested near real time. Snowpipe is unsuitable; create an external table, define a stream, and poll data into Snowflake.

Discover change tracking as an alternative to streams, showing how it enables time-window data capture and multi-ETL consumption, enabled via alter table and not usable with external tables.

Explore Snowflake streams as near real time change data capture to propagate inserts, updates, and deletes to a target table, and learn how to create streams with create stream command.

Explore the three Snowflake streams: default standard, append only, and insert only, and their use with source tables and external tables to enable near real time data ingestion.