Auto increment skip the sequence with merge statemnt in snowflake

I am working on a use case where I need to implement a surrogate key. I have a column ID that should auto-increment by 1 but when I use merge it skips the 2 to 4 sequence.

create or replace table auto_increment(id int primary key autoincrement start 1 increment 1,name varchar(50),city varchar(50));

create or replace table emp(name varchar(50),city varchar(50));

create or replace stream emp_stream on table emp;

insert into emp values('salman','mumbai'),('akshay','pune'),('aamir','mumbai');

merge into auto_increment as a
using emp_stream as e
on e.name=a.name
when matched then update
set a.name=e.name,
a.city=e.city
when not matched then insert (name,city) values(e.name,e.city);

select * from auto_increment;

ID
1
2
3

insert into emp values('aamir','chennai'),('akshay','mumbai'),('ranjikant','chennai'),('mahesh babu','hyderabad');

merge into auto_increment as a
using emp_stream as e
on e.name=a.name
when matched then update
set a.name=e.name,
a.city=e.city
when not matched then insert (name,city) values(e.name,e.city);

select * from auto_increment;

ID
1
2
3
6
7

why it has skipped 4,5? when I use merge again, it gives more gaps in the sequence.

Solution 1:

It's already answered here:

MERGE command results in gaps in sequence numbers

Per the Snowflake documentation, Snowflake does not guarantee there will be no gaps in sequences.

https://docs.snowflake.net/manuals/user-guide/querying-sequences.html.

I can say that Snowflake development team is working on improving sequences for MERGE statements.