Which of the below function can a task use to see whether a stream contains change data for a table?

Which of the below function can a task use to see whether a stream contains change data for a table?
A . SYSTEM$STREAM_HAS_DATA
B. SYSTEM#STREAM_HAS_DATA
C. SYSTEM_HAS_STREAM_DATA

View Answer

Answer: A

Explanation

Whenever a TASK is used to ingest data from stream and then perform a DML operation, it is a best practice to check whether STREAM has data using the SYSTEM$STREAM_HAS_DATA function. I learnt it the hard way. By mistake I created a task in development and forgot to disable it, the STREAM check was not there. The task was running every 10 minutes and consumed all available credits in 2 days. To avoid these situations, in addition to check for the STREAM data, it is also a good practice to setup resource monitors at lower percentages. For example at 30%, 50%, 70% and 90%.

—————————

Additional Explanation from the snowflake documentation

Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table (using SYSTEM$STREAM_HAS_DATA) and either consume the change data or skip the current run if no change data exists.