[2025] SnowPro Advanced Architect COURSE

Create a snowflake data cloud account by starting a 30-day free trial with $400 credit, selecting the business critical edition on aws in paris, activating the account, and bookmarking it.

explain how a Snowflake organization is the top-level container for accounts, enabling centralized view, consolidated billing, data sharing and failover, with a three-part architecture and editions from standard to enterprise.

Learn how Snowflake account identifiers uniquely identify an account across the organization and network, using the preferred org name plus account name format rather than the legacy account locator.

Compare one account and multiple accounts in SnowPro: benefits include centralized user management, private connectivity, single monitoring, and zero copy cloning; drawbacks include limited environment separation and role visibility.

Snowflake showcases compliance certifications—ISO 27001, ISO 27018, SOC 2 Type II, FedRAMP, Hitrust CSF, PCI DSS—to assure data integrity, security, and governance for cloud services.

Explore how parameters modify account, user, session, and object behavior, set defaults, and use show parameters and alter commands to adjust encryption key size, memory limits, and network rules.

Store access to external cloud storage via a storage integration in Snowflake, enabling staging and data load without credentials.

Learn to replicate tables across a Snowflake organization using replication groups, activate replication, and understand automatic failover between accounts.

Explore public and private connectivity to snowflake, including aws privatelink, azure private link, gcp private service connect, and corporate network options like direct connect, express routes, and interconnect.

Discover how Snowflake enforces access control with RBAC and discretionary access control, using securable objects and granular privileges, with grant and revoke workflows and best practices for sensitive data.

Discover system defined and custom roles, including organ min, account admin, security admin, sysadmin, user admin, and public. Learn why grant practices favor the sysadmin role for all custom roles.

Apply best practices for system defined roles, including account admin, security admin, and sysadmin, to avoid granting these roles and to manage grants, object ownership, and the top role hierarchy.

The lecture explains selecting a primary role at session start, with default and public fallbacks, and how create statements rely on the primary role while other queries use both.

Master access control commands for managing users, rules, and privileges, including grant and revoke actions on warehouses and feature tables, set default rules, and manage access schema.

Practice hands-on access control in snowflake by creating a database, a user, and a role, granting privileges, and verifying grants to understand the access control model.

Designate an additional account administrator and enable MFA to secure access, while creating a custom role and using manage access schema to restrict grant decisions.

Showcase how managed schemas govern grants, revealing that only the schema owner can grant permissions on objects in the schema, even if the table owner created them.

Implement Snowflake best practices for sensitive data by creating a sensitive-data role with usage and read on database, schema, and tables, then grant through approved enterprise permission management.

Explain authentication concepts, logging in and the difference from authorization, and compare interactive and non-interactive methods like SSO with SAML 2.0, MFA, keypair, and OAuth 2.0 code grant.

explains username password authentication with or without mfa, including geo security service, self enrollment, and mfa workflows, and federated authentication with saml 2.0 using sp and idp for sso.

Discover oats, the open authorization flow, where a client app uses an authorization server to obtain a token and access a resource server like Snowflake with consent options.

Compare machine authentication methods: key pair and OAuth, covering 2048-bit RSA keys, public and private keys, secret transmission over the wire, and rotation without disrupting the service.

Snowflake's network security framework includes out-of-the-box TLS 1.2 encryption, CIDR-based network policies with allow and block lists, and private networking via cloud providers for private end-to-end connections.

Create, list, and delete network policies using code or the snow site interface, then activate via account updates. Define allowed and blocked IPs with CIDR ranges.

Understand end-to-end encryption and client-side encryption, using random and master keys to secure data before it reaches S3 or Snowflake stages, with internal and external stages and Snowflake encryption.

Snowflake encrypts data at rest with data encryption keys, rotates keys every 30 days, and enables rekeying, all within a four-layer hierarchical model (root, account, table, file).

Explore secure data sharing in Snowflake by creating shares for provider and consumer accounts, sharing read-only databases of tables, external tables, secure views, secure materialized views, and secure UDFs.

Practice secure data sharing by creating a database, schema, and share. Grant usage, describe shares, and manage inbound and outbound shares, including public shares.

Snowflake time travel lets you access data deleted or modified, stored in a time travel zone for up to 90 days, varying by edition, with a 7-day failsafe.

Master time travel to read and restore data deleted or modified across tables, schemas, and databases, set retention, and recover historical states.

Secure views block access to underlying data and the view definition, while standard and materialized views differ in visibility and pre-computation.

Apply column-level security in snowflake by using masking policies to protect sensitive data, with dynamic masking or tokenization, and enforce access based on user roles like analyst or support.

Explore column level security in Snowflake by applying a masking policy to a column, testing with authorized and unauthorized roles, and enabling data access at query time.

Explore zero copy cloning and security: snapshot tables, views, schemas, and databases with no extra storage, privilege inheritance rules, and copy grants that omit ownership.

Explore schema on write versus schema on read, comparing structure, speed, and flexibility, and learn how Snowflake supports both approaches with stages, tables, and external tables.

Discover Snowflake marketplace, where providers publish data and consumers access real-time datasets, pay or free, across regions and clouds via snow grid, with no heavy APIs.

Install a read-only economy data atlas from the Snowflake marketplace, explore the data dictionary, and run sample queries against a provider share with no storage costs.

Reader accounts enable sharing data with non-Snowflake users, hosted in the same region and edition. Provider pays costs and manages access; reader accounts only consume data shared by provider.

Explore secure data sharing with listings, direct share, and data exchange to enable cross-region access without consumer data storage, and learn publishing and monetization on the Snowflake marketplace.

Demonstrate hands-on secure data sharing by creating a listings dataset, provisioning a secondary GCP account in Netherlands, configuring one-minute refresh, and validating near real-time cross-region replication via private shares.

Create and manage a private data exchange to securely share listings with invited providers and consumers across cloud regions. Publish listings with provider profiles and access data via private sharing.

Create and manage a data exchange in Snowflake, define admin, consumer, and provider roles, publish listings, and process data requests between accounts.

Compare private sharing via direct share, listings, and data exchange across same/different regions and clouds, with cross cloud auto-fulfillment; public sharing relies on marketplace and listings for data publishing.

Outline reference architectures for health analytics and IoT on the Snowflake cloud data platform, from log ingestion to alerts, plus data lake and machine learning development lifecycles with Snowflake tools.

Explore data recovery through replication and failover in Snowflake, enabling cross-region and cross-cloud replication for high availability of databases and shares with cron or minute schedules.

Learn to set up data replication across Snowflake accounts by creating a secondary account, configuring a replication group for databases, and validating a six-minute replication schedule.

Enable cross-region failover groups to replicate objects for disaster recovery, promote a secondary as primary during outages, manage data lag with replication schedules, and restore continuity through refresh and promotion.

Practice setting up and testing a failover with secondary accounts, replication, failover groups, and promoting the secondary to primary during outage, with data replication and refresh steps.

download a usa real estate dataset from kaggle to start the data engineering section. extract the 14mb zip to csv with 1.2 million rows and 10 columns.

Generate and convert RSA key pairs to connect to a Snowflake account, create a user with a public key, grant account admin, and enable key rotation without disruption.

Explore data ingestion in Snowflake, from staging to loading data into tables. Learn to use internal or external stages, Snowpipe, copy, and cloud or on-premise storage, including Kafka streaming.

Learn how staging in Snowflake stores, compresses, and prepares data for loading and unloading, including internal stages (user, table, named) and external stages.

Sign up for a free AWS account, verify your email, set a root password, provide basic details and a credit card for identity verification, then access the AWS Management Console.

Create an S3 bucket, upload a Kaggle file, and configure a Snowflake external stage with a storage integration to read data from AWS S3.

Explore how to load data into Snowflake, compare bulk and continuous loading using copy into table, Snowpipe, and Kafka connector, with file formatting and sizing best practices.

Discover bulk loading with copy into table from internal and external stages, including configurations for AWS, Azure, and GCP, and techniques for validation and monitoring.

Create a bulk loading workflow by configuring csv and json file formats and a stage, then use copy into to load data into tables. Validate results and purge loaded files.

Explore continuous loading of streaming and microbatch data using Snowpipe, Snowpipe streaming, and the Kafka Connector, all powered by Snowflake managed compute across external and internal stages.

Use Snowpipe to load data from stage files into a table as they arrive via event-driven, serverless processing, with auto ingest through cloud messaging or REST endpoints and microbatch loading.

Learn hands-on how to load data from AWS S3 into a Snowflake table with Snowpipe, using storage integration, stage, and event notifications for automatic ingestion.

Explore the Snowpipe streaming API to load data in real time into Snowflake tables via the Snowflake Ingest SDK, insert rows, and channels, using offset tokens and last committed offsets.

Explore how Kafka and Kafka Connect enable reading and writing data streams, with the Snowflake Kafka connector delivering JSON or Avro data to Snowflake via Snowpipe streaming.

Explore external tables as an offloading alternative to loading, enabling you to query data stored outside Snowflake without loading into Snowflake via an external stage, including on-premises and S3-compatible storage.

Materialized views are pre-computed data sets in Snowflake that speed up frequent queries, aggregations, and where clauses, avoiding base-table access, with enterprise edition and cost considerations.

Call external functions to execute remote code outside Snowflake by routing SQL requests through an API integration and a gateway to a remote service like AWS Lambda or Azure Functions.

Explore stored procedures that encapsulate SQL logic, execute with owner or caller rights, and support inline or file code in Java, JavaScript, Python, Scala, or Snowflake scripting.

Explore user defined functions (UDF) in Snowflake, including scalar and tabular UDFs (udtf), languages like Python, Java, JavaScript, and how to create, call, and choose between UDF and stored procedure.

Master streams in SnowPro Advanced Architect to track delta change data capture, with standard, append-only, and insert-only types; learn offsets, metadata, retention, and consumption best practices.

Explore how tasks run a single sql statement or stored procedure, form dags, and manage serverless or user managed compute resources, schedules, and billing.

Learn how streams and tasks detect new data, orchestrate loads, and apply business logic. Use system stream data and the when parameter to trigger actions on CDC changes.

Explore how semi-structured data is handled in Snowflake using variant, array, and object types, and learn to parse, navigate, and flatten Json data.

Explore semi-structured data with variants and arrays in snowflake, casting values, parsing json, constructing and modifying objects and arrays, and using the flatten function to handle undefined values.

Master data unloading in Snowflake by exporting tables to delimited Json or parquet files using internal, external, or cloud storage stages, and downloading results via Snowflake get or cloud tools.

Create a database and a table, define Json and CSV file formats, export data to stages using copy commands, verify files, and clean up.

Explore SnowSQL, a command line interface for Snowflake that runs SQL queries and manages objects. Authenticate with private key, external browser, or MFA, and configure settings in snow sql conf.

Explore Snow SQL hands-on by creating an API user with a public key, logging in, and using put and get to manage a stage and database.

Explore how the snowflake odbc driver enables cross-platform database connectivity, from installing and configuring odbc.ini and DSNs on Mac, Windows, and Linux to testing connections.

Configure and test the odbc driver for snowflake by creating a DSN, editing odbc.ini, and granting account admin; then create a database and an internal stage and upload a file.

Explore the Snowflake JDBC driver, a Java database connectivity API requiring Java 1.8+, including download from Maven, pom integration, driver class path, and a configurable connection string.

Learn to interact with snowflake via the sql api, a rest api with three endpoints: statements post, statements/statement_handle get, and console post for cancellation, authenticated by oats or key pair.

Learn to use the Snowflake SQL API hands-on, generate a jwt with Python, configure Postman for bearer authentication, and run a sample select from the customer table.

Explore how the Snowflake Connector for Spark enables fast, secure data transfer between Spark clusters and Snowflake using internal and external transfer with stages and copy commands.

Discover the snowflake connector for python, enabling python apps to connect to snowflake without jdbc or odbc, with pip installation and support for python 3.8+, SQLAlchemy, and pandas.

Learn how persistent query results cache and reuse optimize repetitive queries by checking cached results, rewriting with pushdown and column pruning, and using virtual warehouses when data is unchanged.

Explore the search optimization service, an enterprise-edition index that speeds predicate-based queries by activating on tables or columns, with equality, substring, and geo methods, plus support for semi-structured data.

Explore Snowflake virtual warehouse concepts, comparing standard and Snowpark optimized warehouses, supporting select and DML operations, with auto suspend and auto resume, multiple sizes, and multi-cluster auto scale for workloads.

Differentiate scaling up or down from scaling out or in by resizing a warehouse to a size to boost query performance, or adding clusters to increase parallelization for concurrent queries.

Examine the snowflake account usage and information schema, including object metadata and historical usage. Note latency differences, with account usage showing delays and the information schema delivering up-to-date insights.

Explore how the Snowflake optimizer rewrites queries using materialized views, metadata values for max/min/mean, column pruning, and push-down filters to boost performance.

Clustering keys co-locate data in micro partitions to speed queries and reduce costs, but consume credits. Choose a clustering key from columns used in selective filters and joins, 3–4 columns.