Snowflake streams provide change data capture functionality by recording an offset against the underlying change-tracking metadata of a source object. A stream allows downstream processes to identify inserted, updated, and deleted records without repeatedly scanning the entire source table.
Creating a stream on a standard table requires CREATE STREAM on the destination schema and SELECT on the underlying source table. The role also requires sufficient privileges on the parent database and schema to resolve and access the relevant objects.
Option D correctly places CREATE STREAM at the schema level while retaining USAGE on the database and schema and SELECT on the table.
For example:
GRANT USAGE ON DATABASE PROD TO ROLE ETL_ROLE;
GRANT USAGE ON SCHEMA PROD.WORKING
TO ROLE ETL_ROLE;
GRANT CREATE STREAM ON SCHEMA PROD.WORKING
TO ROLE ETL_ROLE;
GRANT SELECT ON TABLE PROD.WORKING.SOURCE_TABLE
TO ROLE ETL_ROLE;
```
Options A and C incorrectly place CREATE STREAM on the table or database. Option B omits the necessary creation privilege.
Important qualification: If change tracking has not already been enabled on the source table, the initial stream creation may require the source table's OWNERSHIP privilege to enable change tracking automatically. Therefore, D represents the minimum grants when the source is appropriately prepared, but it is not universally sufficient for every new source table.
Official Snowflake references: CREATE STREAM: Access Control Requirements; Introduction to Streams.
Submit