Snowflake/SQL - How to number groups

Question

How do I give every value within a given group the same ID number, incrementing up by one with every new group? In the below example, I want to group together all the rows where the first row is "dog." The first instance of "dog" is assigned ID=1. If the next row down is not also "dog," then set its ID=1. Keep going until you hit another "dog" row. Then increment up by 1. And so forth.

Value	ID
dog	1
cat	1
mouse	1
dog	2
dog	3
dog	4
cat	4
cat	4
dog	5
mouse	5

I need to write the code in SQL. The logic in Alteryx's multi-row formula tool would be:

IF [ROW-1:Value] IS NULL AND [Value]='dog' THEN 1
ELSEIF [ROW-1:Value] IS NULL AND [Value]!='dog' THEN NULL()
ELSEIF [Value] = [ROW-1:Value] AND [Value]='dog' THEN [Row-1:ID] + 1
ELSE [ROW-1:ID]
ENDIF

What's the corresponding way to do this in SQL?

I know I can reproduce the table and join it to itself, offset (i.e. "moved down") by 1 row. Then I could compare values in table A with values in table B, and create a new column with the Alteryx-like logic I outlined above. That's a lot of steps though. Is there an easier way to do it in SQL? The CONDITIONAL_CHANGE_EVENT function looks almost like it will do what I need it to do, but not quite. I don't want to number rows within groups. I want to number the groups themselves.

Answer 1

Using conditional_true_event() (as suggested by Greg), and using an id to order by (as suggested by Mike):

with data as (
    select index, value
    from table(split_to_table('dog,cat,mouse,dog,dog,dog,cat,cat,dog,mouse', ','))
)

select *, conditional_true_event(value='dog') over(order by index) generated_id
from data
order by index

Which gives us results identical as the ones requested by the question.