SnowPro Advanced Architect cheat sheet

Three layers: storage (cloud object storage holding micro-partitions), compute (virtual warehouses running MPP), and cloud services (metadata, optimization, transactions, security).

Micro-partitions are immutable, columnar, 50-500 MB uncompressed; per-column min/max metadata maintained in the cloud services layer drives partition pruning.

Warehouse sizes XS through 6XL; each step up doubles both compute and credits/hour. Resizing creates new compute nodes with a cold local disk cache.

Snowflake is hybrid: shared cloud storage with shared-nothing compute. Warehouses do not share memory or local SSD cache with each other.

Edition gating: Standard (1-day Time Travel), Enterprise (90-day Time Travel, zero-copy cloning, multi-cluster warehouses, materialized views, search optimization, row access policies), Business Critical (PHI data, tri-secret secure, private connectivity), VPS.

Time Travel retention is 0-1 day on Standard and 0-90 days on Enterprise+; Fail-safe is a fixed, non-configurable 7 days beyond Time Travel and is not user-accessible.

Cloud services credits are billed only for consumption exceeding 10% of that day's warehouse credit usage.

Zero-copy cloning is a metadata operation (Enterprise+); clones share micro-partitions with the source until either side mutates data.

System roles: ACCOUNTADMIN (account and billing), SECURITYADMIN (grants, network policies), USERADMIN (users and roles), SYSADMIN (objects); custom roles are normally attached under SYSADMIN.

Privileges are granted to roles, roles to roles, roles to users. Effective access is the union of privileges held by all granted roles down the hierarchy.

Future grants (GRANT SELECT ON FUTURE TABLES IN SCHEMA) apply only to objects created after the grant, not to existing objects.

In a managed access schema, only the schema owner grants privileges on contained objects; ownership moves with GRANT OWNERSHIP ... COPY CURRENT GRANTS.

Network policies allow or block IP ranges; a policy set on a user overrides the account-level policy for that user.

Authentication options: password with MFA, key-pair for service users, SAML2 SSO, OAuth, and SCIM for automated user provisioning.

Every object has an owner role that controls grants on it unless the containing schema is managed access.

Business Critical adds tri-secret secure (Snowflake key plus customer key in the cloud KMS), private connectivity, and support for regulated data such as PHI.

Pruning depends on selective filters over well-clustered columns; verify with EXPLAIN and SYSTEM$CLUSTERING_INFORMATION, where average depth near 1 is ideal.

Automatic clustering is serverless and runs only for tables with a clustering key; clustering keys should be low-to-medium cardinality columns used in frequent filters.

Result cache returns identical query text within 24 hours without running a warehouse; it is invalidated when underlying micro-partitions change and requires the role to have access to all referenced objects.

Scale up (larger warehouse) to fix spills and slow large scans; scale out (multi-cluster warehouse) to fix queueing from concurrent users.

Query Profile signals: partitions scanned versus total (pruning issue), bytes spilled to local or remote storage (memory issue), queue time (concurrency issue).

Remote spill means the warehouse exhausted local memory and disk; a larger warehouse usually removes spill and shortens runtime.

Search optimization service (Enterprise+) accelerates equality and IN predicates, substring searches, and VARIANT field lookups, and bills as serverless compute.

For joins, cluster very large tables on join keys, filter before joining, and check Query Profile for exploding joins; warehouse metering plus QUERY_HISTORY identifies the costliest statements.

Time Travel supports AT/BEFORE queries, UNDROP, and cloning from a past offset; retention is 0-1 day on Standard and up to 90 days on Enterprise+.

Fail-safe is a fixed 7-day period after Time Travel ends; only Snowflake support can restore data from Fail-safe.

Zero-copy clones share micro-partitions, so storage is charged only for partitions that diverge after the clone.

Masking policies are column-level and evaluated at query time, so masked values are also returned through views and shared objects for non-exempt roles.

Row access policies filter rows per role at query time; the standard pattern joins a mapping table of role-to-key values inside the policy.

Object tagging supports tag propagation to columns and enables tag-based masking plus reporting through ACCOUNT_USAGE.TAG_REFERENCES.

ACCESS_HISTORY provides column-level read and write lineage per query; OBJECT_DEPENDENCIES shows object-to-object lineage.

Sensitive data classification uses native classifiers and writes results to system tags such as SNOWFLAKE.CORE.SEMANTIC_CATEGORY and SNOWFLAKE.CORE.PRIVACY_CATEGORY.

A share carries metadata only: the provider grants USAGE on the share, and the consumer creates a database from it and queries with its own warehouse credits.

The provider pays storage for shared data; the consumer pays compute. With reader accounts the provider pays both storage and compute.

Shareable objects include tables, external tables, secure views, secure UDFs, and schemas containing them; base tables should not be shared when logic or columns must be hidden.

Reader accounts are created and funded by the provider; the consumer cannot create objects in them or load data into them.

Cross-region or cross-cloud sharing requires database replication or a Marketplace listing; a share by itself never moves data between regions.

Replicated secondary databases are read-only until promoted; failover requires Business Critical or higher, and account objects require a replication group.

Consumers access a shared database through IMPORTED PRIVILEGES; they see only the objects granted in the share, not the provider's account.

Marketplace listings (public or private) are backed by secure shares and provide the delivery mechanism for cross-cloud distribution.

Streams record change-tracking offsets on tables, views, and external tables; a stream becomes stale when its offset falls outside the source table's Time Travel window.

Stream types: standard captures inserts, updates, and deletes; append-only captures inserts only; insert-only is used for external tables.

SYSTEM$STREAM_HAS_DATA in a task WHEN clause skips execution when the stream has no new rows, saving credits.

Tasks form a DAG with a single root and dependent children scheduled by CRON or interval; user-managed tasks require a running warehouse, serverless tasks use per-second compute.

A task owner role must hold EXECUTE TASK plus the privileges needed by the task's SQL statements; task chains run in the owner role context.

Dynamic tables replace many streams-and-tasks pipelines with a declarative query and a target lag, using incremental refresh where possible and full refresh otherwise.

Snowpipe Streaming (Kafka connector or SDK) inserts rows directly into tables with seconds-level latency and no file staging.

Stored procedures support SQL, JavaScript, Python, Java, and Scala; Snowpark runs Python, Java, or Scala code inside Snowflake compute.

Bulk load pattern: PUT the file to a stage (SnowSQL only, not the web UI), then COPY INTO the table with FILE_FORMAT, optional PATTERN, ON_ERROR, and PURGE.

Target 100-250 MB compressed files; fewer larger files reduce per-file overhead, while many tiny files slow both COPY and Snowpipe.

External stages should use a storage integration that holds the IAM role or service principal, avoiding embedded cloud keys.

VALIDATION_MODE=RETURN_ERRORS loads and rolls back to surface format and transformation errors without committing rows.

Snowpipe auto-ingest relies on cloud event notifications feeding a queue, or on REST API calls; it uses serverless compute billed per file.

COPY ON_ERROR options are CONTINUE, SKIP_FILE, SKIP_FILE_, ABORT_STATEMENT; SKIP_FILE can be scoped to a percentage of errored files.

External tables are read-only and depend on metadata sync via event notifications or ALTER TABLE ... REFRESH; scan cost depends on file pruning.

Unload with COPY INTO a stage or external location using a FILE_FORMAT; SINGLE forces one file, otherwise file count is driven by parallelism and MAX_FILE_SIZE.

Warehouse credits per hour: XS 1, S 2, M 4, L 8, XL 16, 2XL 32, 3XL 64, 4XL 128, 5XL 256, 6XL 512.

Resource monitors set a credit quota with notify, suspend, or suspend-immediate actions and a daily, weekly, monthly, or never-resetting frequency; account-level monitors cover all warehouses.

Cloud services credits are free until they exceed 10% of that day's warehouse credits; only the excess is charged.

Serverless features bill outside warehouse metering: Snowpipe, automatic clustering, search optimization, materialized view maintenance, serverless tasks, replication, dynamic table refresh, and classification.

Auto-suspend of 60 seconds suits sporadic warehouses; longer values preserve the local disk cache but bill for idle time.

Doubling warehouse size doubles the credit rate; if runtime does not at least halve, total credits for that workload increase.

Storage charges cover active data plus Time Travel and Fail-safe versions; clones add no storage until their micro-partitions change.

Cost attribution uses METERING_DAILY_HISTORY by service type, WAREHOUSE_METERING_HISTORY by warehouse, and QUERY_HISTORY for per-statement cost; budgets add spend thresholds with notifications.