General - Snowpipe Flashcards
what is snow pipe used for?
continuous data load - loading new or delta files to target from source based on notification
what is the end to end workflow in setting up snow pipe?
- new files coming into the source
- SNS notifications
- SQS queue
- SF pipe copies data from SQS queue to the target
does snowpipe need active WH?
no it is serverless computing and charged accordingly.
is snowpipe micro batch or real time streaming?
is is more of a micro batches - minute level processing
is AWS SNS the only way to trigger notifications for new file in snowpipe?
No, we can use custom Python code as well.
What is the SQL construct of snowpipe?
CREATE OR REPLACE PIPE
AS COPY INTO
When pipe is created is it automatically activated or started?
If not how will you resume?
it is not automatically activated…you have to resume it using alter command
ALTER PIPE resume
how do you monitor pipe whether it is running?
And how do you check if pipe has any data loaded or not for load history?
SYSTEM$PIPESTATUS
SYSTEM$validatepipe
you can also use LOAD_HISTORY, COPY_HISTORY and METERING_HISTORY in account usage view to understand pipes loading and credit taken
Does pipe will load data to target automatically as soon as a source file is placed? Do you need any other components?
No, it needs notifications and queue…SNS and SQS or a python code
Will snowpipe load if same file is placed ?
no generally it will not load if same files come in.
what are the _HISTORY views in ACCOUNT_USAGE?
COPY_HISTORY
LOAD_HISTORY
METERING_HISTORY
what is METERING_HISTORY view in ACCOUNT_USAGE?
this is for checking credit usage in general
Does pipe guarantee order of how file is loaded?
nope..it does not guarantee file load order.
Can you tell pipe what data to consume if there are multiple files? if yes what is the command?
yes you can….you can mention when resuming using ALTER command like pipe name and modified after etc.
