Why Snowflake SYSTEM$STREAM_HAS_DATA can be TRUE with zero rows
In Snowflake, SYSTEM$STREAM_HAS_DATA can be TRUE even when querying the stream returns zero change rows.
Start with the symptom
In Snowflake, SYSTEM$STREAM_HAS_DATA can be TRUE even when querying the stream returns zero change rows.
What the documentation establishes
The function uses table version metadata differences, so TRUE is not a guarantee that CDC rows are available.
A false positive can occur when changes return the table to its prior state, or when a view filters out changed underlying rows.
Five checks at a glance
| Scene | Point | Limit |
|---|---|---|
| 1 | TRUE does not guarantee CDC rows. | TRUE does not guarantee a nonzero CDC row count. |
| 2 | Metadata difference ≠ nonzero stream rows. | The function evaluates version-metadata differences. |
| 3 | Net-zero changes and selective views can create false positives. | Net-zero changes or view filtering can create a false positive. |
| 4 | Consuming a stream changes its offset. | Consuming the stream through DML advances its offset. |
| 5 | STALE_AFTER is a prediction, not an incident verdict. | No real DML, task-cost or data-loss incident was tested. |
Do not over-interpret the symptom
A persistent TRUE can cause unnecessary task runs. Consuming the stream in DML advances the stream offset, so that action is different from read-only diagnosis.
Read-only preflight
- TRUE does not guarantee CDC rows.
- Metadata difference ≠ nonzero stream rows.
- Net-zero changes and selective views can create false positives.
- Consuming a stream changes its offset.
- STALE_AFTER is a prediction, not an incident verdict.
Evidence limits
STALE_AFTER is a predicted staleness deadline, not proof of a particular loss event or safe state. No DML was run in a real account.
This article interprets first-party documentation and uses hypothetical diagrams. It does not report a live device, database, customer-data, outage, recovery or performance test.
- https://docs.snowflake.com/en/sql-reference/functions/system_stream_has_data
- https://docs.snowflake.com/en/user-guide/streams-manage
Watch the short explanation
The public YouTube schedule may be linked here after the provider assigns the native video ID. The article and local video are already self-contained.