Snowflake Data Engineer 100 + Interview Question & Answers

Snowflake Data Engineering scenario question and answer for experienced engineers to pass interview in the first attempt

What you'll learn

Learn the most important questions needed for snowflake Data Engineering interview preparation

Learn tips and tactics to better handle snowflake data engineering interview questions

Improve confidence before attending interview snowflake data engineering interviews

Get a great job and improve income and job growth

Requirements

Knowledge of Snowflake Data Engineering

Description

Do you worry when you need to go and attend a snowflake data engineer interview because of which you decide to not try new job opportunitiesIf this is you, you may have stumbled upon a solution to your challenge. This course the first of its type on udemy is designed to help folks like you to increase their confidence and get a dream job, a pay hike , remote work or maybe a better work environment This course may help you get to the jobs of your dreams by increasing your confidence.It covers several scenario question for senior Data Engineers.  Basic questions for Junior Engineers and everything else in between.There are over 100+ question and answers in this course.3+ hours of Interview questions , the most comprehensive  course for interview questions on Udemy.The topics covered in this course are belowLOADING DATA IN SNOWFLAKE UDF AND STORED PROCEDURE JOB SCHEDULING SNOWPIPE TASK RESOURCE MONITOR DATA WAREHOUSING STAGES STREAMS TIME TRAVEL FAIL-SAFE ZERO COPY CLONING WAREHOUSE UNLOADING DATA FROM SNOWFLAKE DATA SHARING AND SECURE VIEW COST SAVING ARCHITECTURE DYNAMIC DATA MASKING & ROW ACCESS POLICY UNSTRUCTURED DATA PERFORMANCE TUNING IINTERVIEW MANAGEMENT I have covered  under Interview managementHow to increase the interviewers confidence in you.How to handle questions that you do not know. Feb-2024- Initial Release

Overview

Section 1: Introduction

Lecture 1 About this course and the Instructor

Section 2: Loading Data in Snowflake

Lecture 2 Do you use transformation when loading data using the COPY command?

Lecture 3 What is needed to establish connectivity between snowflake and cloud storage ?

Lecture 4 How can we ingest data without using snowpipe, COPY or 3rd party ETL like DBT ?

Lecture 5 You received files from your new vendor, you are aware of the layout of ..?

Lecture 6 How will you setup a process of automatic validation and quality control of ..?

Lecture 7 Once a file is loaded with the COPY command can you reload the file again ?

Lecture 8 What is the data loading strategy in your project ?

Lecture 9 When ingesting data from data lake, are most of your files in parquet format ..?

Section 3: UDF and Stored Procedure.

Lecture 10 You want to truncate a table in snowflake, can you use a UDF to do so ?

Lecture 11 What are the benefit of using stored procedure and UDF ?

Lecture 12 Have you created stored procedures in your project, what dd you use them for ?

Lecture 13 What language does snowflake UDF support ?

Section 4: Job Scheduling

Lecture 14 Can we send emails from Snowflake ?

Lecture 15 In your project do you use snowflake to send emails ?

Lecture 16 Why do you use airflow instead of using tasks to schedule jobs in snowflake ?

Section 5: Snowpipe

Lecture 17 In which scenario is using snowpipe not recommended in snowflake ?

Lecture 18 Why would you use snowpipe instead of batch job in your project data load?

Lecture 19 There are several files in cloud storage that was received 11 days ago, you ..?

Lecture 20 What is snowpipe.What are its advantages ?

Lecture 21 How do we pause execution of a pipe which is skipping files due to data issues ?

Lecture 22 What are the steps for setting up Snowpipe ?

Lecture 23 Provide me a scenario in your project where you used snowpipe?

Lecture 24 How can you monitor and troubleshoot snowpipe errors?

Lecture 25 Due to a few bad rows in the file your snowpipe load is skipping files. How ..?

Section 6: Task

Lecture 26 What is a task , What are its advantages ?

Lecture 27 How can you monitor tasks in snowflake ?

Lecture 28 When a task is created is it Active or inactive by Default ?

Lecture 29 What would happen if we created a task without a warehouse ?

Lecture 30 What is the maximum Number of tasks that a DAG can have +1 ?

Lecture 31 You changed a table column causing a task to fail, how will you handle it ?

Lecture 32 Explain a scenario in your project where you have used tasks ? 3 Use Cases

Lecture 33 How will you find dependency information between tasks on a tree of tasks?

Section 7: Resource Monitor

Lecture 34 What is a resource monitor? How would you ensure that a badly written query ..?

Lecture 35 How many Resource monitors can you have in your account ?

Lecture 36 What actions can be taken inside a resource monitor ?

Section 8: Data Warehousing

Lecture 37 How big is your snowflake data warehouse

Lecture 38 How do you generate PK for tables in snowflake ?

Lecture 39 We have a table called customer in QA and we need to compare data in it with ..?

Lecture 40 For staging tables being truncated daily what table type do you prefer ?

Lecture 41 Can I create permanent tables in a transient schema ?

Lecture 42 Does snowflake support constraints ?

Section 9: Stages & SnowSQL

Lecture 43 What is a stage in snowflake ?

Lecture 44 In what scenario do you see the need for project to use internal stage ?

Lecture 45 How can you determine how many files are there in a stage ?

Lecture 46 What are the different type of internal stages in snowflake ?

Lecture 47 How can you add, download and remove files from an Internal Stage ?

Lecture 48 You have 100 files on a server which is on premises that you want to upload to ?

Section 10: Streams

Lecture 49 How does a stream become empty ? How can you purge a stream ?

Lecture 50 Can you join a stream and table ?

Lecture 51 In what scenario did you use streams in your project ? Use Case 1

Lecture 52 In what scenario did you use streams in your project ? Use Case 2

Lecture 53 What is change tracking on a table ? Why is it used instead of streams ?

Lecture 54 What is a stream ?

Lecture 55 What are the different type of streams ?

Section 11: Time Travel

Lecture 56 What is time travel ? What are its advantages ?

Lecture 57 What are the different options you can use to recover data with time travel ?

Lecture 58 A table has been truncated and you are not able to recover it using time ..?

Lecture 59 A junior developer dropped a table, you have been asked to recover it how ..?

Lecture 60 How will you find the data retention parameter of a table ?

Lecture 61 What is the max time travel available on any edition of snowflake ?

Lecture 62 Does the undrop command work with Schema and databases ?

Section 12: Fail-Safe

Lecture 63 What is fail-safe ? What are its advantages ?

Lecture 64 How will you measure how much of data from your tables is in fail-safe and …?

Lecture 65 Do transient and temporary tables have time-travel ?

Section 13: ZeroCopy Cloning

Lecture 66 What is zero Copy Cloning ,What are its advantages over create table as select ?

Lecture 67 When cloning a table do the grants get copied over ?

Lecture 68 When cloning a table do the cluster Keys get copied over ?

Lecture 69 In your project did you use Zero Copy cloning ,Scenario Question ?

Lecture 70 Lets us say that data in your production table was accidentally updated how ..?

Lecture 71 You have a table in a snowflake prod account and want to clone it and bring ..?

Lecture 72 If I clone a table and update the clone will the original table get updated ?

Section 14: Warehouse

Lecture 73 What is the maximum number of clusters you can have in a cluster ?

Lecture 74 What kind of scaling policy do you use on your project ?

Lecture 75 What is scaling up vs scaling out

Lecture 76 What is the auto suspension time period you use on the warehouse used in ..?

Section 15: Unloading Data from Snowflake

Lecture 77 What is data unloading ?

Lecture 78 When unloading data into cloud storage what best practices do you use ?

Lecture 79 Was there a scenario in your project where you had to unload the data to ..?

Lecture 80 Do you prefer unloading data into multiple files or single file from snowflake ?

Lecture 81 Can you unload data from multiple tables in a COPY command ?

Lecture 82 How are parquet files zipped when being unloaded ?

Lecture 83 How do we modify column data types when unloading data ?

Lecture 84 What problems have you encountered when unloading data ?

Lecture 85 How can you limit the size of a file generated by unloading data from snowflake

Section 16: Data Sharing and Secure view

Lecture 86 What is a secure view. Why would you create a secure view ?

Lecture 87 Have you created a data share and shared data in your project ?

Lecture 88 What are reader Accounts? Have you created reader accounts in your project ?

Lecture 89 What objects can we share in snowflake ?

Section 17: Cost Savings

Lecture 90 How can you reduce storage cost on your project ?

Lecture 91 What are the best practices that we can follow to reduce credit consumption ?

Section 18: Architecture

Lecture 92 What is the max data storage capacity of snowflake ?

Section 19: Dynamic Data Masking & Row Access Policy

Lecture 93 How do you protect sensitive data like Date of Birth and SSN# from being …?

Lecture 94 What is Dynamic Data Masking ?

Lecture 95 What is ROW ACCESS Policy In Snowflake ?

Section 20: Unstructured Data

Lecture 96 Can you unload data from a relational table to a JSON stage with the COPY ..?

Lecture 97 What data type do you recommend to store JSON and XML data ?

Lecture 98 Can you explain a scenario in your project where you needed to use variant ..?

Lecture 99 What are the semi-structured data types supported by snowflake ?

Lecture 100 What does the flatten command do ?

Section 21: Performance Tuning

Lecture 101 What is query acceleration? How can you enable query acceleration ?

Lecture 102 What is QUERY_ACCLERATION_MAX_SCALE_FACTOR ?

Lecture 103 What is memory spillage ?

Lecture 104 How will you fix memory spillage issues ?

Lecture 105 What is partition pruning or query pruning in snowflake ?

Lecture 106 What is metadata cache ?

Lecture 107 What is the difference between automatic clustering and manually clustering ..?

Lecture 108 You suspect that queries are waiting to get picked up by a warehouse to be ..?

Section 22: Interview Management

Lecture 109 How to handle questions you dont know ?

Lecture 110 How to increase the interviewers confidence in you ?

Section 23: Bonus Lecture

Lecture 111 Bonus

This course are for Snowflake Data Engineers who want to improve their Snowflake interviewing skills