Snowflake SnowPro Advanced - Administrator Certification Exam Syllabus

Set up and manage Snowflake authentication.
- Establish federated authentication and Single Sign-on (SSO)

• Implement federated authentication/SSO as it relates to Snowflake
• Configure an Identity Provider (IdP) for Snowflake
• Configure, use, and manage federated authentication with Snowflake

- Implement Multi-Factor Authentication (MFA)

• Enroll a Snowflake user in MFA
• Use MFA with different Snowflake drivers and connectors (such as, Web UI, SnowSQL, JDBC, ODBC, etc.)
• Monitor users who do not have MFA enabled
• Reset passwords and temporarily disable or permanently remove MFA from a user

- Utilize key pair authentication and perform key pair rotation

• Create, set up, and configure a Snowflake user for key pair authentication
• Configure key pair rotation

- Configure and use OAuth protocol options

• Use OAuth 2.0 in Snowflake
• Compare Snowflake OAuth to External OAuth
• Configure Snowflake OAuth for custom clients
• Configure OAuth for technology providers (such as, Tableau, Looker, Microsoft Power BI, OKTA, Azure AD, etc.)
• Outline how Snowflake OAuth is impacted by federated authentication, network policies, and private connectivity

- Manage passwords and password policies

Set up and manage network and private connectivity.
- Establish network policies

• Configure and manage network policies
• Describe network policy behavior when both account-level and user-level network policies exist

- Establish private connectivity to Snowflake internal stages

• Implement and manage cloud provider interfaces and private endpoints for internal stages

- Establish private connectivity to the Snowflake service

• Implement and manage private connectivity between cloud providers and Snowflake

- Access the Snowflake SQL API
- Use IP address allowed lists and blocked lists for access using network access policies

Set up and manage security administration and authorization.
- Use and monitor SCIM

• Describe SCIM and its use cases as they relate to Snowflake
• Manage users and groups with SCIM
• Enable, configure, and manage SCIM integration

- Prevent data exfiltration with PREVENT_UNLOAD_TO_INLINE_URL and REQUIRE_STORAGE_INTEGRATION _FOR_STAGE_CREATION
- Manage service accounts, API integration, and automated authentication (for example, key pair authentication)

Given a set of business requirements, establish access control architecture.
- Describe access control framework

• Discretionary Access Control (DAC)
• Role-Based Access Control (RBAC)

- Describe the uses for, and hierarchy of, system-defined roles
- Use cases for custom security roles
- Demonstrate key concepts of access control
- Describe the implications of role inheritance when granting or revoking privileges
- Describe the enforcement model
- Demonstrate how to grant access to specific objects within a database that requires privilege inheritance

Given a scenario, create and manage access control.
- List and use different privileges available for each object type in Snowflake
- Custom security roles and users (for example, include related SHOW commands)
- Audit user activity history and query activity history across a Snowflake account

Given a scenario, configure access controls.
- Use system-defined roles
- Create custom roles
- Use secondary roles
- Implement inheritance and nesting of system-defined roles
- Follow best practices for using and securing the ACCOUNTADMIN role
- Align usage of object access with business functions
- Describe cloned objects and their impact on granted privileges
- Designate additional Administrators in Snowflake
- View granted privileges TO users and roles, and ON objects
- Implement and manage future grants including restrictions and limitations
- Evaluate the various scenarios using warehouse grants (for example, USAGE, OPERATE, MODIFY, MONITOR)
- Implement and manage managed access schemas
- Provide access to a non-account Administrator to monitor billing and usage information
- Manage account-level permissions

Manage organizations and accounts.
- Describe the benefits of an organization
- Describe organizational tasks

• Create and name an organization
• Name various types of organization accounts
• Identify what regions are available for a given organization

- Understand account tasks

• View, create, and list accounts
• Change account names
• Enable replication for accounts

- Manage Tri-Secret Secure
- Manage encryption keys in Snowflake

• Describe how Snowflake encrypts customer data
• Describe encryption key rotation and periodic rekeying configuration

Manage organizations and access control.
- Follow best practices when using the ORGADMIN role
- Compare the differences between ORGADMIN and ACCOUNTADMIN roles

Implement and manage data governance in Snowflake.
- Mask column data in Snowflake

• Implement and manage column-level security using masking policies
• Use external tokenization to protect Personal Identifiable Information (PII)
  - Describe the differences between data masking and external tokenization

- Implement and manage row access policies

• Configure a row access policy on an object
• Compare row access policies to secure views

- Perform auditing of access history

• Audit access history details using the access history views

- Use tagging and classification in Snowflake

• Identify use cases where tagging would be beneficial
• Implement and manage tagging
• Implement tag-based masking policies
• Implement data classification (EXTRACT_SEMANTIC_CATEGORIES, ASSOCIATE_SEMANTIC_CATEGORIES)

Given a scenario, manage account identifiers.
- Describe the differences between account names and account locators
- Identify when a given account identifier needs to be used
- Use region IDs and region groups

Given a scenario, manage databases, tables, and views.
- Implement Snowflake table structures
- Establish and use temporary and transient tables
- Establish and use external tables
- Implement and manage views, secure views, and materialized views
- Outline table design considerations
- Outline the use cases when cloning is beneficial
- Outline data storage and data retention considerations

Perform queries in Snowflake.
- Use Snowflake sequences
- Use persisted query results
- Demonstrate the ability to cancel statements for both a single user as well as for other users
- Use query history filters including client-generated queries and queries executed by user tasks
- Visualize query results with Snowsight

• Use Snowsight dashboards to monitor activity
• Share worksheets and dashboards
• Generate and share Snowsight charts

Given a scenario, stage data in Snowflake.
- Stage data files from a local file system

• Use SnowSQL
• Use Snowsight

- Create, manage, and maintain Snowflake internal and external stages

• Data exfiltration, storage integrations, etc.

Given a scenario, manage streams and tasks.
- Outline user-managed (virtual-warehouse) tasks and associated use cases

• Schedule tasks
• Permissions required for creating and executing tasks
• Troubleshoot task historical runs

- Outline Snowflake-managed (serverless) tasks and associated use cases
- Outline streams and associated use cases

• Create, monitor, and consume streams
• Describe how data retention configuration affects usage of streams

- Given business requirements, design, manage, and maintain virtual warehouses.
- Outline the impact on data loading, and query processing based on warehouse sizes
- Configure warehouse properties (auto-suspend, auto-resume)
- Given a scenario, manage warehouse usage in sessions and size the warehouse accordingly
- Given a scenario, manage a multi-cluster warehouse

• Describe use cases and benefits
• Describe, establish, and maintain a scaling policy
• Monitor multi-cluster warehouses

- Monitor Snowflake performance.
- Evaluate and interpret Query Profiles to improve performance

• Describe the components of the Query Profile:
  - Steps
  - Operator tree
  - Operator nodes
  - Operator types
• Compare compile versus runtime optimizations
• Identify/create efficient queries
  - Articulate the execution path
  - Use effective joining conditions
  - Perform grouping, sorting, and ordering
• Troubleshoot common query performance issues
• If data spilling is present, describe its impact and remediation tactics
• If data pruning is not occuring, describe its impact and remediation tactics
• Describe the various timeout parameters

- Use an explain plan
- Compare and contrast different caching techniques available in Snowflake and the impact of caching on performance

• Resultset cache
• Local disk (warehouse) cache
  - What is the impact of warehouse resumption/suspension on local disk cache?
• Metadata cache

- Implement performance improvements
- Recommend the use of materialized views

• Use the search optimization service
• Create external tables
• Use data caching
• Use the query acceleration service

- Manage DML locking and concurrency in Snowflake.
- Describe DML concurrency considerations
- Follow best practices for DML locking and concurrency
- Monitor transaction activity

• Abort transactions

- Given a scenario, implement resource monitors.
- Create, manage, modify, and remove resource monitors based on use cases and business requirements

• Set up notifications for resource monitors

- Interpret and make recommendations for data clustering.
- Configure and maintain cluster keys

• Create and enable cluster keys
  - Outline a methodology for explicit clustering
• Use the automatic clustering service
  - Monitor and assess usage
• Follow best practices for clustering
  - Lowest cardinality column first
  - Fewer columns is generally better
  - Verify table scan is the problem - otherwise a cluster key will not help

- Describe micro-partitions, their benefits, and their impact
- Retrieve clustering information (depth, ratio, and histogram)

- Manage costs and pricing.
- Manage organization costs

• Describe the differences between account_usage and organization_usage
• Monitor accounts and usage on the organization level
  - Use the ORGANIZATION_USAGE schema in the SNOWFLAKE shared database
• Monitor and calculate data transfer costs
• Monitor and calculate data replication costs

- Forecast and monitor costs and pricing

• Enable resource monitor notifications
• Determine when warehouses should be suspended or resumed based on cost and pricing

- Describe the use cases for the account_usage and information_schema

• Views available from the information_schema
• Latency and data retention considerations

- Monitor and calculate data storage usage/credit
- Monitor and calculate warehouse usage/credits

• Demonstrate cost saving strategies
• Use resource monitors

- Describe how Snowflake credits are consumed by the cloud services layer (such as Snowpipe, materialized views, and automatic clustering)
- Apply techniques for cost optimization

- Manage and implement data sharing.
- Given a scenario, implement sharing solutions and impacts

• Types of sharing (such as one to one/one to many, private exchange, Snowflake Marketplace)
• Sharing among different editions of Snowflake
• Sharing cross-regions or cross-clouds
  - The role of replications
  - Cross-cloud auto fulfillment for listings
• Configure data sharing programmatically
  - Share different types of data objects including secure functions
  - Describe the role of context functions in data sharing

- Manage data providers and consumers

• Create, manage, and maintain an outbound data share
• Share objects securely in a data share (for example, what type to use)
• Use secure objects to share data
  - Secure views
  - Secure User-defined Functions (UDFs)
• Create, manage and maintain readers accounts
  - Create user and role for access
  - Create resource monitors
  - Create objects
  - Determine if there is a need to store data (CREATE DATABASE)
• Import, manage, and maintain inbound data shares

- Use the Data Exchange.
- Manage administration and membership
- Access the Data Exchange
- Outline the process of becoming a data provider

• Create, edit, or delete provider profiles

- Manage data listings

• Publish, edit, unpublish, or republish data listings

- Use the Snowflake Marketplace.
- Access the Snowflake Marketplace to browse listings

• Request access to a Snowflake Marketplace listing (as a consumer)

- Request that new data or a data provider be added to the Snowflake Marketplace

• Create and manage data provider profiles
• Create, submit, manage, and modify a data listing

- Manage listing requests

• View and manage pending listing requests

- Manage data listings
- Monitor data sharing usage

Manage data replication.
- Describe the differences between primary and secondary databases
- Replicate database objects
- Replicate account-level objects
- Manage access controls
- Perform database replication
- Enable scheduled replication
- Outline the database replication processes with respect to the different Snowflake editions

• Replicate data to a lower Snowflake edition

- Describe the limitations of database replications
- Outline the implications of database replications (for example, billing)
- Outline database replications considerations for:

• Automatic clustering
• Materialized views
• External tables
• Policies (masking and row access)
• Table streams
• Tasks
• Stages (internal and external)
• Access controls
• Historical usage data
• Tags
• Pipes
• Cloned objects

- Perform replication across multiple accounts
- Outline the impact of failing-over databases across multiple accounts
- Redirect client connections in case of fail-over
- Design and implement disaster recovery and business continuity plans

• What is database failover or failback?
• Awareness of cost implications

- Implement backup best practices in Snowflake

Given a scenario, manage Snowflake Time Travel and Fail-safe.
- Data retention periods
- Enable and/or disable
- Query historical data
- Restore dropped objects
- Snowflake edition implications