SnowPro® Advanced Architect Exam Prep (ARA-C01) Unofficial

Explore Snowflake parameters and how account, session, and object levels control behavior. Learn setting, unsetting, and overriding parameters with alter and show commands, plus practical examples.

Learn how to show parameters and override Snowflake settings across account, database, schema, warehouse, user, and session levels, alter values, and unset to return to the default value.

Compare single versus multiple Snowflake accounts for dev, QA, and prod landscapes, weighing simplicity, cost, security, and data separation for enterprise compliance.

Explore how Snowflake's access control framework combines discretionary, role-based, and user-based access control to grant, inherit, and revoke privileges on securable objects like tables and warehouses via roles and users.

Master access control by implementing role-based access control with roles, users, and grants across databases, schemas, and warehouses. Compare this with user-based access control and future grants.

Demonstrates configuring secondary roles with separate schema access for finance and sales, tests isolated permissions, assigns roles to a user, and shows using secondary roles for joins.

Grant privileges to roles and assign roles to users to control object access, illustrating privilege inheritance and role hierarchy with table privileges like select, truncate, delete, and ownership.

Learn how Snowflake roles work across account, database, and organization levels, including primary and secondary roles, system defined roles like account admin and security admin, and organization-wide visibility.

Learn how to create database roles, assign them to a custom account level role, and confine privileges to a single database and its objects.

Create and assign a database level role, grant select on all tables in a schema and usage on schema and warehouse, and verify roles in each database.

Explain how access roles encapsulate low-level privileges and how functional roles align with specific job functions, by aggregating privileges into higher-level roles like IT developer and data analyst.

Demonstrates creating functional and access roles across finance, HR, and DX databases, granting usage and select, insert, update, delete privileges, and rolling up access roles into functional roles.

Adopt best practices for managing system rules by using access rules instead of direct permissions, avoid default accounts and roles, create custom roles, apply least privilege, and audit security configurations.

Discover how to connect Snowflake to cloud via storage integration, create an S3 bucket, policy, and role, and use copy into to load data from AWS to Snowflake.

Learn to create an AWS account via the console, verify email and phone, enter payment details for identity verification (small charge and refund), and choose the $0 plan.

Learn to connect AWS and Snowflake by creating an S3 bucket, defining a policy and role, configuring a storage integration, and loading data into Snowflake via an external stage.

Learn how to unload data to S3 using storage integrations and stages. Alter and configure the storage integration, create a stage, and unload data with or without a stage.

Discover secure view in Snowflake, a view that hides underlying sql logic to enforce strong data access during data sharing, with a trade-off in performance due to limited predicate pushdown.

Demonstrates column level data masking with a CC mask on the cust.credit_card_number, enforcing role-based visibility for account admin versus others, and how to alter or unset masking.

Apply row level security to restrict visibility to individual rows, so sales, marketing, and IT see only their own records, with row level security enforced before column level security.

Demonstrates row level access policy implementation, creates roles for sales, marketing, and it, and shows how to grant policy-based data filtering by department.

Discover how aggregate policies, available in enterprise edition or higher, enforce data privacy in Snowflake by requiring queries to group data into a minimum size, such as three records.

Learn how aggregation policies control access by returning salary aggregates only when groups meet a minimum size of three, and how to create, alter, describe, and drop policies.

Explore projection policies, a first-class schema-level object in Snowflake that control what a column can be projected in query results, protecting sensitive data and per-user visibility.

Explore how projection policies control column-level data exposure, via a demo of the SSN projection policy, role-based access, and join behavior to show when data is projected or restricted.

Learn data lineage and object dependencies in Snowflake, tracking data movement and how tables and views depend on each other using lineage and dependency tools.

Trace dependencies and lineage from a sales table to downstream objects like views and a materialized view, using code and UI to assess impact.

Learn how Snowflake object tags enterprise edition or higher attach key-value metadata to tables, views, and columns to support centralized or decentralized data governance, compliance, and monitoring.

Create object tags with defined allowed values, then assign them to tables and warehouses. Verify tag usage in account governance and monitoring, and learn how to view and drop tags.

Explore how Snowflake meets global and country-specific regulatory requirements, with edition-based compliance, data governance, encryption, time travel, RBAC, and advanced data security features for sensitive data.

Explore Snowflake's multi-layer security framework, including network security with policies and private connectivity, IAM authentication with MFA and key pair options, and data encryption at rest and in transit.

Explore how data encryption protects Snowflake data at rest with AES-256 and in transit with TLS 1.2, enabling end-to-end encryption, client-side encryption, key rotation, Rekeying, and Tri Secret Secure.

Configure network rules and policies to control inbound traffic and use external network access for outbound traffic, with CIDR IPv4 controls and precedence for policy enforcement.

Explore how to create and manage network policies and rules in Snowflake, including allowed and block rules, ingress and egress, IPv4, and activating policies at account or user level.

Enable private connectivity to route inbound traffic via private IP addresses from AWS Private Link, Azure Private Link, or Google Cloud Private Service Connect to Snowflake, enhancing security.

Explore IAM authentication policies in Snowflake, choose between password, federated, and key-pair methods, enforce MFA, and tailor access with identity providers and granular account- or user-level policies.

Explore federated authentication and SSO with snowflake, detailing SP and IDP roles, SAML 2.0 compliance, and flows for SP-initiated and IDP-initiated logins using Okta or Microsoft ADFS.

Discover how multi-factor authentication adds layers beyond passwords using devices, authenticator apps, biometrics, or location, with Snowflake mfa powered by Duo and admin-enabled enrollment.

Enable multi factor authentication for snowflake login with an authenticator app or passkey, via QR setup and code entry; admins can enforce MFA.

Learn key pair authentication for Snowflake, a robust alternative to passwords, generating a 248-bit RSA key pair with OpenSSL and mapping the public key to a Snowflake user.

Explore open authorization with OAuth 2.0, a framework that grants limited access to Snowflake data via user consent, an authorization server, and access tokens.

Explore Snowflake security integration, focusing on identity and access management, authentication and authorization for users and apps, by integrating external IDPs, OAuth servers, and MFA providers.

Explore authentication best practices for Snowflake, prioritizing OAuth/open authentication, keypair, and native options, and enforce MFA across options while applying network security and granular access controls.

Compare star schema and data vault models, detailing fact and dimension tables versus hubs, links, and satellites, and highlight hash difference, SCD type two, history, and compliance.

Explain how key and column constraints behave in Snowflake, noting limitations for primary key, unique, and foreign key, and the not null requirement, plus enable, disable, validate, and rely.

Explore how primary key, foreign key, unique, and not null constraints behave in Snowflake, including enable and validate, and how rely on constraints can optimize queries.

Explore Snowflake data sharing options, including direct share within the same region or provider, cross-region replication, marketplace listings, private data exchange, paid listings, and data clean room controls.

Explain direct share in snowflake, where a data provider shares specific objects with a consumer or readers account via a share, with read-only access and cost implications.

Demonstrate secure data sharing by creating a readers account, building a restricted view with a filter (age >= 18), and managing the share from setup to revocation.

Explore public and internal listings in the Snowflake marketplace, featuring data apps and freemium options, plus private data exchange for selective sharing within or across organizations and VPN edition.

Navigate Snowflake marketplace to find data products with filters, access free and paid data, and start a 30-day trial. Learn to install, query, share, and publish listings.

Explore Snowflake data clean rooms, a secure, privacy-preserving environment for multiple parties to analyze combined datasets via web app or API without exposing raw data.

Explore Snowflake's data exchange and marketplace, sharing data within and beyond your organization with controlled access and admin authorization, and publish or consume data, apps, and services via provider studio.

Explore the Snowflake marketplace to discover free data, search by category or provider, run queries, check health, publish via data provider studio, and preview apps.

Learn when to use cloning, direct share, listings, and dcr for Snowflake data sharing. Replicate to the same region and provider before sharing; auto fulfillment by Snowflake handles the rest.

Organize data into zones—bronze raw ingestion, silver staging with cleaning, and gold business modeling—across dev, quality, and production environments to enable data lifecycle governance, lineage, and performance tuning.

Explore Snowflake environments, including development, pre-prod, quality, production, and sandbox, and learn how teams build, test, and go live from development to production, with POCs in sandbox.

See how Snowflake acts as a cloud data platform that handles multiple workloads—from batch processing and bulk load to streaming with Snowpipe, data science, machine learning, and secure data sharing.

Explore virtual warehouses like Gen one, Gen two, and Snowpark optimized, with sizes from extra small to six xlarge, multi-cluster scaling, and scaling policy options.

Create a snow site warehouse by selecting type (gen one or gen two), size, and a comment, then enable auto resume, auto suspend, multi-cluster, and query acceleration to manage workloads.

Explore the difference between ETL and ELT, showing how ETL pre-processes data outside the warehouse while ELT loads raw data into Snowflake and transforms it there using warehouse power.

Learn how snowflake supports development lifecycles by migrating objects across landscapes with db and schema isolation, automating deployment with terraform or snowflake cli, ci/cd, git, and rollback.

Learn to integrate Git with Snowflake by creating a GitHub secret token, configuring an API integration, connecting a repo, cloning, and pushing updates to keep code and data in sync.

Learn snowflake ML functions and machine learning concepts, including training with features and labels for spam classification. Explore forecasting, anomaly detection, classification, and insights, with Snowflake managing models and costs.

Demonstrates building a Snowflake ML classification model from synthetic training data to predict user actions: purchase, add to wishlist, or not interested.

Explore snowflake cortex LM functions, enabling access to hosted AI models like OpenAI and Lama from Meta, with task-specific AI SQL functions and supporting helper functions for automation.

Explore how to use Snowflake cortex AI functions to parse documents with OCR and layout modes, translate text, classify content, and compare models like OpenAI and Snowflake Arctic.

Package data logic with the Snowflake native app framework and a Streamlit UI to deliver secure, in-account dashboards via the marketplace, with no data egress and dealer-specific access.

Visualize Snowflake's container hierarchy from organization to accounts, then to warehouse and database, then schema with securable objects like tables, views, stages, procedures, and functions.

Explore the hierarchy of databases and schemas in Snowflake, including transient schemas and how regular versus managed access schemas control ownership and privileges.

Explore how managed schema controls access and ownership, grant privileges with data engineering and analyst roles, and compare it to normal schemas and ownership rules.

Learn Snowflake data types including numeric, string, binary, date and time, geospatial, and semi-structured, and compare permanent, transient, and temporary tables with cloning, time travel, and failsafe options.

Learn Snowflake time travel and continuous data protection, using before timestamp, offset, or query id to view data, with configurable retention and failsafe recovery beyond retention via Snowflake staff.

Explore time travel in Snowflake by setting UTC, creating a customer table, and viewing past data with timestamps and query IDs. Also adjust retention, clone data, and recover dropped tables.

Explore the cost implications of time travel in Snowflake, including data retention storage, failsafe duration, and compute charges for time travel queries.

Explore how cloning creates independent clones of tables and schemas, including clone data and clone of a clone, and see how streams behave when cloning a schema versus individual streams.

Explore replication of Snowflake objects to secondary accounts across regions or clouds, understand failover to promote a secondary as primary, and learn about read-only replicas, costs, and limitations.

Explore how Snowflake loads data from external sources into its data warehouse, using stages to move structured, semi-structured, and unstructured data from cloud, on-prem, files, and API feeds.

Explore Snowflake data source types, including data at rest, data in motion, external sources and formats, streaming data, and API sources, plus ingestion via Snowpipe, copy into, and external tables.

Compare bulk data loading with the copy command to continuous loading using Snowpipe and Snowpipe streaming, including file size guidance, metadata retention, and error handling in Snowflake.

Learn how stages in Snowflake locate data files in cloud storage, distinguishing internal stages (table stage, user stage, name stage) from external stages and noting ownership, accessibility, and data location.

Load data into a Snowflake table using stage or direct file load, map headers, and manage errors with append, replace, and force load options.

Learn data transformation during load by reading stage files, selecting and concatenating columns, skipping headers, reordering, and applying casts, truncation, and force reload.

Define and reuse file formats in Snowflake, detailing CSV properties, skip header, null handling, and compression, and learn precedence rules for copy into, stage, and table.

Snowpipe automatically loads data from internal and external stages into a staging table using pipes and copy commands. It uses stream and task to move data to target tables.

Learn to query cloud-stored data in Snowflake with external tables, without copying, using schema-on-read and metadata like file names and row numbers, with formats Parquet, Avro, ORC, JSON, CSV.

This lecture demonstrates creating an external table that points to a bucket via storage integration and external stage, using a csv file format, and refreshing to include new files.

Explore iceberg table, an open table format stored in the cloud with Snowflake managed and externally managed flavors. Discover ACID compliance, schema evolution, snapshot time travel, and lakehouse advantages.

Create and use an iceberg table with an external volume in Snowflake, connect to S3 storage, load data from a standard table, and enable writes back to S3.

Snowflake uses schema detection to auto create table schemas from files during initial load, and schema evolution to adapt tables as files change during ongoing ingestion.

Explore schema detection and evolution by inferring a table schema from CSV data, creating the table, and loading data on the fly while evolving the schema with new columns.

Learn how data source changes impact Snowflake flows, including file format, structure, and changes, and implement staging, variant data handling, and schema drift detection to minimize ingestion and downstream risks.

Demonstrates copy into table with onerror options, showing abort, continue, and skip file behaviors, including thresholds and percentage limits to control error handling during bulk loading.

Explore copy into commands, including size limit, first file processing, and options like continue on error, return failed only, match by column name, pass header true, and enforce length.

Use validation mode to simulate a copy command and surface errors without loading data. Employ return errors and return all errors to identify issues from prior loads.

Explore the Snowflake ecosystem and learn how to connect Snowflake to third-party apps using ODBC and JDBC drivers, connectors, API endpoints, and the Snowflake CLI with Snowpark.

Enable raw connectivity to Snowflake through drivers implementing standard database connectivity protocols like ODBC and JDBC, and note manual configuration for BI tools and Java apps.

Explore how Snowflake connectors wrap underlying drivers to simplify integration with ecosystems like Python, Kafka, ServiceNow, and Google Analytics, delivering ready-to-use APIs that hide driver complexity.

Learn to connect Snowflake to Power BI and Tableau using native connectors or an ODBC driver, configure DSN if needed, and choose DirectQuery for fresh data.

Snowflake Kafka connector enables real-time data ingestion from publishers to topics and partitions, supports JSON and Avro formats, and creates internal stages, tables, and pipes for loading.

Use Snowflake api for light programming access and automation when drivers or connectors are unavailable, including Snowflake sql rest api and Snowpipe rest api for ingestion.

Explore the snowflake ecosystem with snow SQL and snowflake cli, comparing admin and automation use and showing how cli manages accounts, projects, resources, and native apps.

Learn to install and use the Snowflake CLI, add connections, test them, and run queries to manage databases, schemas, and stages in Snowflake.

Snowpark enables coding in Python, Java, or Scala inside Snowflake, with Snowpack acting as the bridge to run ML pipelines and data processing without moving data.

Map the Snowflake ecosystem to exam scenarios by selecting drivers, connectors, api endpoints, rest, http, and tools like Snowpack, Snowpark, and Snowflake CLI.

Explore how Snowflake allow list enables secure access from corporate environments by whitelisting host names and ports via the system allow list function, with guidance for infra and security teams.

Explore unloading data from Snowflake to cloud or desktop via stages, put/get, with optional single file, partitioning, and default CSV gzip, plus storage integration and format options.

Demonstrate unloading data by creating an internal stage, uploading a csv, loading into the customers name table, unloading back with put and get, and partitioning by region.

Understand snowflake views, including non-materialized, materialized, and secure views, and their advantages of no storage and up-to-date data, with limitations like base-table dependency and no alteration.

Explore materialized view, which stores data with query results to pre-compute analyses for faster repeated queries; Snowflake handles auto refresh and costs.

Create normal, materialized, and secure views with select queries. Grant database, schema, and warehouse access to admin roles, and compare secure versus normal view definitions.

Explore dynamic tables in Snowflake, an auto-refreshing, multi-table structure that replaces streams and tasks, keeping data up to date with a configurable target lag.

Demonstrate creating and refreshing a dynamic table, including configuring target lag, selecting a warehouse, feeding from a base table, and monitoring refresh history.

Explore semi-structured data in Snowflake, using native types array, object, and variant to ingest and query JSON, Avro, ORC, parquet, and XML formats, with unloading options JSON and parquet.

Explore semi-structured data through a practical JSON workflow in Snowflake: load, query, and cast JSON data into a readable table, using bracket and dot notation.

Learn advanced JSON handling and semi-structured data in Snowflake, flattening arrays such as zones, divisions, and orders to transform JSON into a readable table for efficient querying.

Demonstrates converting data to semi-structured formats JSON and Parquet by creating file formats and stages, copying data, and using parse JSON versus try pass JSON to handle invalid records.

Use snowflake streams to capture changes in source tables and apply them to targets. Track inserts, updates, and deletes via metadata$action and metadata$row_id, with retention rules to avoid stale streams.

Learn to create streams on a source table, enable change tracking, and apply inserts, updates, and deletes to a target table using merge, including default and append-only streams.

Explore creating and managing Snowflake tasks in a demo, including selecting the warehouse, scheduling frequency, resuming and suspending tasks, defining dependencies, and monitoring results via task history and streams.

Explore the data database to view task details, run history, and graphs, then edit tasks, switch between warehouse and serverless, and configure auto retry, automatic suspend, and timeout.

Explore how external functions in Snowflake invoke remote services via a proxy, with API integration metadata, batched JSON requests, and latency and scaling considerations.

Stored procedures are named, pre-compiled blocks of sql with control flow, stored in the database and callable from sql, written in java, js, python, scala, sql, or snowflake scripting.

Explore user defined functions (udf) and table functions (udtf) in Snowflake, including scalar and table outputs, overloading, naming rules to avoid system function conflicts, and multi-language options.

demonstrates UDF and UDTF basics, including create or replace, overloading, and SQL versus JavaScript returns; shows returning tables with UDTF and using secure procedures and grants.

Understand how Snowflake's micro partitions auto-divide tables into 50–500 MB of uncompressed data, using columnar storage. Leverage this design for efficient query pruning, time travel, and cloning.

Snowflake automatically prunes micro partitions during queries, scanning only partitions where name equals y to improve performance. This pruning is automatic and requires no manual action.

Explore how data clustering co-locates like values in the same micro partitions to boost Snowflake query performance. Learn when to enable clustering and the associated storage and compute costs.

Choose clustering keys for Snowflake tables and materialized views by evaluating where clauses. Consider join predicates and cardinality, then cluster by date and type to reduce partitions and improve performance.

Demonstrates clustering in Snowflake by selecting a clustering key and enabling cluster by, then compares performance before and after. Partition scans drop from 27 to 5, with faster queries.

Explore snowflake caching concepts across cloud service layer and warehouse cache, including query result cache, metadata cache, and local disk cache, with rules on expiry, exceptions, and when caches apply.

Explore how Snowflake caches query results, metadata, and warehouse data, and how to enable or disable caching with use cache result; observe cache reuse and when current timestamp bypasses it.

Explore query profile concept to visualize data flow from multiple tables through joins and filters, identify expensive nodes and spillage, and learn to optimize queries or scale the warehouse.

Explore query profiling in Snowflake, including query profile, history, explain plans, and information schema and account usage to monitor execution.

Learn to monitor credit usage for user managed warehouses and cloud services, set credit quotas, frequencies, and threshold-driven actions such as suspend or notify, via SnowSite UI or code.

Configure a resource monitor to track quota usage at account or warehouse level, schedule start and end dates, set reset frequency, and define threshold actions with email notifications.

Explore how the search optimization service enhances point lookup and analytical queries by maintaining a search access path for variant object and array columns using equality and in predicates.

Learn how SOS search optimization service works by enabling search optimization on a 100 million records customer table, reducing partitions scanned from 215 to one partition and lowering query cost.

Learn how the query acceleration service speeds up warehouse workloads by prioritizing select queries, reducing outlier impact and unpredictable data volume, and tuning the scaling factor for cost-aware performance.

Explore information schema and account usage, two read-only metadata schemas with views and table functions that reveal database objects, login history, and usage data.

Explore access history in account usage to audit read and write operations, including direct and indirect object access, base objects, DDL changes, and policy references.

Learn to monitor storage and compute costs in Snowflake using the compute warehouse, query history, and admin cost management to identify expensive queries, storage usage, and anomalies.

Discover how Snowflake event tables store telemetry from UDFs and procedures, with logging, tracing, and metrics, and learn when to use default or user created tables.

Explore snowflake telemetry event tables to capture logs, traces, and metrics; view events, adjust account and database level parameters, and build a custom app events table with a demo procedure.

Master performance optimization in Snowflake with storage and warehouse tuning, query optimization, clustering, materialized views, time travel, memory monitoring, rightsizing, and filtering to boost speed and reduce cost.