Prep for SnowPro Core Certification | Snowflake Exam COF-CO3

Explore traditional shared-disk and shared-nothing architectures, their trade-offs in scalability and availability, and how Snowflake combines them into a multi-cluster shared data architecture with centralized storage and distributed compute.

Explore Snowflake’s three-layer architecture—storage, compute, and cloud services—covering hybrid columnar storage, virtual warehouses for query processing, and the brain-like cloud services that manage authentication, metadata, and query optimization.

Compare standard, enterprise, business critical, and virtual private editions of Snowflake, and explore features like time travel, fail safe, multi-cluster warehouses, and column level security.

Explore how Snowflake decouples compute and storage costs, and learn how active warehouses, serverless features, and region-based credits determine total cloud pricing.

Understand data storage and transfer pricing, including on-demand vs capacity storage, region-based costs, data compression effects, and that data ingress is free while egress varies by region and cloud provider.

Learn to monitor table storage with the show tables command and the table storage metrics views in information schema and account usage, including active, time travel, and failsafe bytes.

Create and manage resource monitors to cap and monitor credit usage for warehouses or the account, with monthly quotas, actions at thresholds (notify, suspend, suspend immediately), and account admin privileges.

Monitor usage via the snow site interface and set up resource monitors. Set budgets and credits quotas, with actions to notify or suspend, including suspend immediately for accounts and warehouses.

Learn to create a Snowflake warehouse using SQL, specify size and multi-cluster settings, enable auto suspend and auto resume, and add documentation comments with create or replace.

Discover how SnowSQL CLI connects to Snowflake, install on Windows, and run DDL/DML operations from the command prompt, including loading and unloading data and a sample query.

Explore the Snowflake object hierarchy from accounts to databases, schemas, and tables. See how organizations use multiple accounts, warehouses, roles, and objects like views, functions, stages, and pipes.

Follow along as you load a csv from an external cloud provider into Snowflake, creating a database and table, and loading data with copy into from a public S3 bucket.

Learn about Snowflake internal stages (user, table, and named) and how to load and unload data with put and copy into, including the external stage distinctions.

Explore external stages for S3, Azure, and GCP, and create stage objects that reference locations. Learn to manage credentials with storage integrations and use commands like list and copy into.

Create and manage stages hands-on, list and describe stages, create external stages with an S3 URL, and test file availability to prepare for loading data with the copy command.

Explore the copy into command for bulk loading data into tables from staged files, including file selection, formats (csv, json, avro, xml), and unloading options, with warehouse and cost considerations.

Set up a worksheet and the orders table, then load the csv data from a public S3 stage using copy into, with file format and header skip configured.

define and reuse a file format object to control stage and copy behavior, including default csv interpretation and the ability to override in copy into commands.

Create and apply a file format object to a stage, describe the stage properties, and use the copy command to load data with CSV formats.

Explore dml commands for inserting and updating data in a table, including single and multiple inserts, inserting into specific columns, insert overwrite, update with where clauses, truncate, and drop.

Explore storage integration objects in Snowflake and connect an Azure container to a stage by creating a storage integration, granting Azure permissions, and reusing across stages.

Learn how snowpipe enables continuous loading with a serverless, no-warehouse solution that auto ingests files via cloud notifications or rest api, using a copy statement against a stage.

Explore how copy options govern data loaded with the copy into command and stage properties, including on error behavior, size limits, purge, and column matching.

Demonstrate Snowflake load error handling with copy options such as on error abort, continue, and skip file, including partial loads and error limits or percentages.

Explore validation mode in the copy command to validate data before loading, returning five rows or all errors without inserting into the target table.

Explore the validate function to surface errors from a copy command, calling it as a table function (select * from table(...)); use on error options such as continue or skip.

Unload Snowflake data to internal or external stages with copy into, pick formats (csv, json, parquet, xml), adjust splits and headers, and use select or transform before retrieving with get.

Unload data from the orders table into external and internal stages with the copy into command. Download the files to your local machine with get.

Explore Snowflake transformations with copy and copy into commands, using select statements to reorder columns, cast data types, or truncate data for etl, and review scalar, aggregate, window, table functions.

Explore Snowflake's approximate query processing with estimating functions, including hyperloglog for distinct counts, top-k frequent values with counter tuning, percentile estimation via t-digest, and minhash-based similarity.

Create and use user defined functions in Snowflake to extend capabilities with sql, Python, Java, or JavaScript; define arguments, return types, and bodies, then apply in select.

Compare stored procedures with user defined functions to clarify differences and use cases in Snowflake, and learn to create, call, with arguments, return type, and caller or owner rights.

Explore external functions in Snowflake, which execute outside Snowflake as remote services, enabling Azure or AWS integrations, third-party libraries, and languages like Go and C#, with performance and shareability tradeoffs.

Apply secure user defined functions and procedures to hide sensitive information from describe outputs and optimizer access. Balance security with performance by choosing secure versus standard functions based on sensitivity.

Create a sequence with optional start and increment (default 1), generate values via sequence.nextval, and use it as a table default, noting Snowflake sequences may have gaps.

Explore how Snowflake handles semi-structured data with the variant data type, including objects and arrays, and load JSON files via ELT while flattening hierarchies for practical querying.

Perform a hands-on load of semi-structured data into a JSON variant table using a stage pointing to a public S3 bucket and copy into, then view results.

Access semi-structured data in a variant column by using colon and dot notation to reveal modules like Snowflake and Azure, index arrays, and cast with double colon to varchar.

Learn to query semi-structured data from variant columns using colon references, cast data types with double colon, alias columns, apply dot notation for hierarchy, and flatten arrays with flatten function.

Master how to flatten semi-structured hierarchical data with the flatten function, converting arrays into relational table views and producing a lateral view for easy joining with the original data.

Explore advanced parsing with parse_json to insert json into a variant column, create tables, insert via select, and use lateral flatten to compare implicit and explicit joins.

Explore unstructured data in Snowflake and access files via scoped, file, and presigned URLs using SQL file functions: build scoped file URL, build stage file URL, and get presigned URL.

Enable directory tables on a stage. Refresh the stage, then query the directory for file urls, relative paths, size, and last modified via the build scoped file url function.

Explore data sampling techniques in Snowflake to accelerate development and testing by using row or Bernoulli sampling and block or system sampling, with seeds for reproducible results.

Learn how to create and schedule Snowflake tasks to run SQL statements or stored procedures, including privileges, cron schedules, and optional Snowflake managed compute, with DAGs for multiple child tasks.

Explore streams as change data capture tools recording data manipulation language changes for extract-transform-load and delta loads. Identify stream types and retention, and learn to query streams with tasks.

Build an etl with streams and tasks, creating sales and store tables, a stream on sales staging, and a one-minute task joining on store ID to populate the final table.

Explore Snowflake drivers and connectors—JDBC, Python, Spark, and Kafka—and use Partner Connect to set up a trial and integrate with Snowflake from the web interface and command line.

Learn Snowflake scripting as an extension of SQL for procedural logic in stored procedures, including declare blocks, begin and end, loops, and optional exception handling.

Explore the data protection life cycle with Snowflake time travel, accessing historical data and restoring dropped tables, schemas, or databases using timestamp, offset, or query ID.

Apply time travel in practice by loading data into a test table from a stage, then recover earlier states using offset, timestamp, or a query id.

Learn to undo and recover objects in Snowflake with undrop, using an intermediate backup table and query history to verify the correct query id before restoring, preserving time travel.

Configure retention period values for tables, schemas, databases, and accounts to enable time travel, override defaults, and manage historical data with edition-based limits.

Explore configuring retention periods in Snowflake, including setting account defaults and per-table overrides, to manage time travel and storage efficiently.

Learn how time travel and a non-configurable seven-day fail-safe protect historical data, recoverable only via snowflake support after time travel ends, with implications for storage costs.

Analyze storage cost for time travel and failsafe, using interface usage views and table storage metrics to understand active bytes and breakdown by stage, database, and time travel.

Learn about permanent, transient, and temporary table types in Snowflake, including creation, time travel retention, and failsafe rules, and how session scope affects temporary objects.

Discover zero-copy cloning, a metadata-only operation that creates independent clones referencing the same micro partitions, avoiding data duplication and extra storage. Learn privilege handling and time travel usage.

Clone a database from a managed source, including schemas, tables, views, and stages while excluding the named internal stage, then apply time travel to clone a table and verify privileges.

Discover data sharing in Snowflake, where storage is decoupled from compute and data is shared without copying. Grant read-only access via shares to consumers who use their own compute.

Learn hands-on data sharing in Snowflake by creating a secure view to share selected columns, grant access, and demonstrate cross-account sharing with a reader account.

Log in as account admin to consume a provider share, create a consumer database, set up a dedicated warehouse, and verify a secure view with privileges.

Explore cross region and cross cloud provider sharing via database replication, copying data to a primary database and a replica database with read-only access, periodic refreshes, and data residency considerations.

Explore snowflake access control through discretionary and role-based models, detailing securable objects, privileges, and ownership. Learn how roles grant privileges to users and how object and role hierarchies govern access.

Snowflake uses roles to receive privileges granted on objects, allows grant and revoke to users, and builds hierarchy from system defined to custom roles with a primary role in session.

Grant and revoke privileges on Snowflake objects with grant and revoke commands, covering tables, schemas, and databases, including ownership and global privileges.

Apply access control by creating a marketing database with sysadmin, defining a marketing admin role, granting usage and table privileges, and reviewing ownership and future grants.

Learn how multi-factor authentication protects Snowflake logins with the Duo app, including per-user enrollment, cross-method support, and token caching for smoother sign-ins.

Enable single sign-on with federated authentication for Snowflake and other apps. Configure Snowflake as the service provider with an external identity provider using SAML 2.0 and SIM provisioning.

Use key pair authentication as a secure alternative to password login for Snowflake client connections. Generate a private/public key pair (min 2048 bits) and configure the client to use it.

Apply column level security with dynamic data masking and external tokenization using enterprise edition features by defining masking policies on sensitive columns and honoring the current role at query time.

Apply dynamic data masking policies across columns by defining roles (analyst, analyst full, account admin) and enforcing them on the customers.phone column, with creation, alteration, and privilege grants.

Apply row level security in Snowflake with row access policies to filter data by current role and column values. Only the enterprise edition provides this feature.

Hands-on with row access policies in Snowflake: create a home manager role with furniture-only access on the orders table and validate using current role and policy logic.

Define access using network policies by listing allowed and blocked IP address ranges with blocked ranges taking priority; apply at account or user level via alter commands.

Snowflake encrypts all data at rest and in transit by default, with automatic 256-bit encryption, TLS 1.2 end to end encryption, and key rotation every 30 days.

Explore the account usage and information schema in Snowflake to query object metadata and historical usage via views, with notes on latency, retention, privileges, and managing dropped objects.

Snowflake releases deploy weekly, with full releases that add features and behavior changes and patch releases that fix bugs. A three-stage access process provides early, regular, and final access.

Explore the query profile, a graphical view of the operator tree and data flow that exposes bottlenecks and data spilling, with access via Northside and information and account usage schemas.

Analyze Snowflake caching strategies, including the query result cache in the cloud service layer, the data cache within each virtual warehouse, and the metadata cache, to optimize query performance.

Explore how Snowflake uses query result, metadata, and local data caches to speed queries on the orders table, and how cache behavior changes with warehouse status and query variations.

Explore how Snowflake stores data in immutable micro partitions on external cloud storage, with automatic compression and partition pruning to boost read performance in a columnar format.

Define clustering keys on tables to influence data distribution in micro partitions, boosting query performance via partition pruning; compare overlapping partitions and clustering depth to assess well-clustered tables.

Explore how clustering keys enable automatic clustering across micro partitions, the serverless, no active warehouse model, and trade-offs with costs, time travel retention, and selective queries using where clauses.

Explore two system functions for clustering: system clustering information and system clustering depth, and read a JSON report with partition counts, constant partitions, average depth, and histogram data.

Explore how clustering affects Snowflake query performance by examining clustering keys on sold_date and item_sk, testing queries, viewing query profiles, and using system clustering metrics.

Explore Snowflake's search optimization service to boost query performance for selective lookups and predicates. Understand how to enable, apply on tables or specific columns, and manage serverless costs and privileges.

Explore materialized views in Snowflake Enterprise Edition, which precompute and store results to boost performance, with automatic refresh, cost considerations, and limitations such as no joins or window functions.

Explore materialized views in Snowflake with a hands-on setup using a sample database. Test performance against queries and inspect refresh history and usage in the information schema and account usage.

Resize warehouses to match changing workloads; scale up for complexity, scale out with multi-cluster setups, and enable auto suspend/resume to optimize performance and caching.