Reference¶

Views¶

ACCOUNT_USAGE is a schema stored in SNOWFLAKE shared database that contains similar information as in information_schema but retains historical data and may have a lag when compared to information_schema
information_schema table functions can obtain account level information and history
queries against information_schema can fail if they return too much data when not enough filters are specified
information_schema returns results to which the user is authorized. E.g. to view TASK_HISTORY() returns data only for task owners, ACCOUNTADMIN or MONITOR EXECUTION

View/Function	Type	purpose
`copy_H`,`()`	AU Inf	load history of both `Snowpipe` and `COPY INTO`
`load_H`	AU Inf	load history of only `COPY INTO`; Inf view is limited to 10,000 rows
`metering_H`	AU	credits usage by hour, including serverless
`snowpipe_streaming_file_migration_H`	AU	data loaded using Snowpipe Streaming
`warehouse_metering_H()`	Inf	hourly credit usage for date range, specific WH
`warehouse_load_H()`	Inf	load activity for date range, specific WH
`{database,stage}_storage_usage_H(<date>)`	AU OU Inf	show storage usage by X
`data_transfer_H`,,`()`	AU OU Inf	cross region/cloud data transfer
`[database_]replication_usage_H`,,`()`	AU OU Inf	cross region/cloud data transfer
`login_H[_by_user]()`	Inf	login history [by user]
`policy_references()`	Inf	list users (and account) and policy for given policy, or given user/account
`query_H_by_X()`	Inf	query history by X (`user` `session` `DataWarehouse`)
`query_attribution_H`	AU	compute and and QAS credits attributed by query ID
`table_constraints`	AU Inf	unique, PK and FK
`query_acceleration_eligible`	AU	shows QAS eligible time and scale factor, by Query ID
`query_acceleration_H`	AU	shows credits used by Warehouse by period
`rest_event_H()`	Inf	SCIM REST API events made to Snowflake
`listing_telemetry_daily`	DSU	telemetry data by exchange and region
`listing_events_daily`	DSU	events (`cancel purchase` `get` `request` `purchase` `trial`) from consumers
`task_H()`	Inf	Task executions in last 7 days
`tag_references`, `()`	AU Inf	objects associated with the tag
`tag_references_all_columns()`	Inf	All tags, including inherited, set on all columns of a table/view
`tag_references_with_lineage()`	AU	objects associated with the tag including objects affected by the hierarchy

Functions¶

functions	purpose
`scan_result()`	retrieve the contents of previous query
`last_query_id()`	last executed query
`current_client()`	client connection (e.g. Web UI Tab)
`current_account()`	locator name of the current account
`current_organization_name()`	organization name of the current account
`current_account_name()`	account name of the current account
`current_session()`	a session within a client connection (Worksheet within the same tab)
`is_role_in_session()`	returns true if a given role is part of current role's hierarchy
`invoker_role()`	execution context role for masking policy, e.g. owner role of UDF, View, Task etc
`invoker_share()`	name of the share, or `NULL` if local
`is_granted_to_invoker_role()`	returns true if `invoker_role()` is granted the given role
`hash_agg()`	non-cryptographic hash of set of rows. order of rows doesn't matter, but column order matters
`task_dependents()`	obtains dependent tasks

Execution context

System Functions¶

`system$` function	purpose
`allowlist`	returns host names and port names to allow in firewall config
`abort_session`	abort given session ID
`cancel_all_queries`	cancel all running queries
`cancel_query`	cancel query
`clustering_depth`	(`table`[, `columns`[, `predicate`]]) -> avg clustering depth
`clustering_information`	(`table`[, `expr`]) -> {tot, tot const} partitions, avg {overlap, depth}, histogram, errors
`estimate_search_optimization_costs`	returns cost estimates for enabling SO
`get_login_failure_details`	get details about login errors when using OAuth, SAML or key-pair
`get_snowflake_platform_info`	get VPC ID of the Snowflake account
`last_change_commit_time`	approx last change commit time of a table
`pipe_status`	returns status of a pipe as JSON object
`{query_}reference`	creates a reference to a query/table with temporary privileges
`stream_get_table_timestamp`	returns table version that corresponds to current stream offset
`stream_has_data`	returns TRUE if the stream has data
`get_predecessor_return_value`	Get predecessor task's return value
`get_tag(<tag>, <obj>, <dom>)`	returns tag value for given object
`get_tag_allowed_values(<tag>)`	returns allowed values of a tag
`whitelist_PrivateLink`	returns private-link hosts and ports to add to firewall's allowed list
`whitelist`	returns hosts and ports to add to customer-firewall's allowed list
`verify_external_volume('<vol>')`	verifies that Sowflake can successfully authenticate to the storage provider

Parameters / Limits¶

session parameters can be set at session -> user -> account; object parameters table -> schema -> database -> account or warehouse -> account levels

Name	Default	type	purpose
`max_data_extension_time_in_days`	14	Tb	extension (default 14) to data retention if any stream has unconsumed data
`statement_timeout_in_seconds`	2 days	Sess,WH	timeout interval for a long running query
`lock_timeout`	12 Hr	Sess	timeout interval in seconds for a long running query
`user_task_timeout_ms`	1 Hr	ms	(TASK property) Long running task is terminated after this time
`allow_id_token`	false	bool	allow Federated SSO tokens to be cached
`allow_client_mfa_caching`	false	bool	allow MFA tokens to be cached
`periodic_data_rekeying`	false	bool	enable re-encryption of data with keys older than 1 year (Enterprise+ only)

SNOWFLAKE database roles¶

Role	Purpose
`alert_viewer`
`budget_creator`
`classification_admin`
`classification_viewer`
`copilot_user`
`core_viewer`
`data_metric_user`
`data_privacy_viewer`
`document_intelligence_creator`
`governance_admin`
`governance_viewer`
`ml_user`
`monitoring_viewer`
`notification_viewer`
`object_viewer`
`organization_accounts_viewer`
`organization_billing_viewer`
`organization_governance_viewer`
`organization_usage_viewer`
`pypi_repository_user`
`reader_usage_viewer`
`security_viewer`
`sharing_usage_viewer`
`spcs_registry_viewer`
`usage_admin`
`usage_viewer`
`workload_insights_user`
`workload_optimization_user`

Metadata¶

Object	Metadata
Stage	`METADATA$FILENAME`, `METADATA$FILE_ROW_NUMBER`
Ext Table	`METADATA$FILENAME`, `VALUE`
Dir Table	`RELATIVE_PATH`, `SIZE`, `LAST_MODIFIED`, `MD5`, `ETAG`, `FILE_URL`
Stream	`METADATA$ACTION`, `METADATA$ISUPDATE`, `METADATA$ROW_ID`
Kafka	`RECORD_CONTENT`, `RECORD_METADATA`
Flatten	`SEQ`, `KEY`, `PATH`, `INDEX`, `VALUE`, `THIS`

Internal Parameters¶

General format alter account <acct> set <param>=<val> parameter_comment='<text>'

Name	Default	Acc/User	purpose
`WINDOW_FUNCTION_MAX_WINDOW_SIZE`	1000	UA	Number of rows allowed for Window function