Description

SnowPro® Advanced: Administrator Overview

The SnowPro® Advanced: Administrator Certification will test advanced knowledge and skills used to apply comprehensive data cloud administrative principles using Snowflake and its components. This certification will test your ability to:

1. Manage and administer Snowflake accounts
2. Manage and administer Snowflake data security and governance
3. Manage and maintain database objects
4. Manage and maintain virtual warehouses
5. Perform database monitoring and tuning
6. Perform data sharing and use the Data Exchange and Snowflake Marketplace
7. Administer disaster recovery, backup, and data replication

SnowPro® Advanced: Administrator Candidate

2+ years of Snowflake Data Cloud Administrative experience, including practical, hands-on experience using Snowflake. In addition, successful candidates should have fluency with ANSI and Snowflake extended SQL.

Target Audience:

1. Snowflake Administrators/Snowflake Data Cloud Administrators
2. Database Administrators
3. Cloud Infrastructure Administrators
4. Cloud Data Administrators

Exam Format:

1. Exam Version: ADA-C01
2. Total Number of Questions: 65
3. Question Types: Multiple Select, Multiple Choice
4. Time Limit: 115 minutes
5. Language: English
6. Registration fee: USD 375
7. Passing Score: 750 + Scaled Scoring from 0 – 1000

Unscored Content:

Exams may include unscored items to gather statistical information for future use. These items are not identified on the form and do not impact your score, and additional time is factored into account for this content.

Prerequisites:

SnowPro Core Certified

Delivery Options:

1. Online Proctoring
2. Onsite Testing Centers

Exam Domain Breakdown:

This exam guide includes test domains, weightings, and objectives. It is not a comprehensive listing of all the content that will be presented in this examination. The table below lists the main content domains and their weightings.

Exam Topics:

Outlined below are the Domains & Objectives measured on the exam. To view subtopics, download the exam study guide.

Snowflake Security, Role-Based Access Control (RBAC), and User Administration

1. Set up and manage Snowflake authentication.
2. Set up and manage network and private connectivity.
3. Set up and manage security administration and authorization.
4. Given a set of business requirements, establish access control architecture.
5. Given a scenario, create and manage access control.
6. Given a scenario, configure access controls.

Account Management and Data Governance

1. Manage organizations and accounts.
2. Manage organizations and access control.
3. Implement and manage data governance in Snowflake.
4. Given a scenario, manage account identifiers.
5. Given a scenario, manage databases, tables, and views.
6. Perform queries in Snowflake.
7. Given a scenario, stage data in Snowflake.
8. Given a scenario, manage streams and tasks.

Performance Monitoring and Tuning

1. Given business requirements, design, manage, and maintain virtual warehouses.
2. Monitor Snowflake performance.
3. Manage DML locking and concurrency in Snowflake.
4. Given a scenario, implement resource monitors.
5. Interpret and make recommendations for data clustering.
6. Manage costs and pricing.

Data Sharing, Data Exchange, and Snowflake Marketplace

1. Manage and implement data sharing.
2. Use the Data Exchange.
3. Use the Snowflake Marketplace.

Disaster Recovery, Backup, and Data Replication

1. Manage data replication.
2. Given a scenario, manage Snowflake Time Travel and Fail-safe.

TABLE OF CONTENTS

Set up and manage Snowflake authentication.

– Establish federated authentication and Single Sign-on (SSO)

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

– Implement Multi-Factor Authentication (MFA)

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

– Utilize key pair authentication and perform key pair rotation

1. Create, set up, and configure a Snowflake user for key pair authentication
2. Configure key pair rotation

– Configure and use OAuth protocol options

1. Use OAuth 2.0 in Snowflake
2. Compare Snowflake OAuth to External OAuth
3. Configure Snowflake OAuth for custom clients
4. Configure OAuth for technology providers (such as, Tableau, Looker, Microsoft Power BI, OKTA, Azure AD, etc.)
5. 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

1. Configure and manage network policies
2. 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

Snowflake Security, RBAC, and User Administration

Set up and manage security administration and authorization.

– Use and monitor SCIM

1. Describe SCIM and its use cases as they relate to Snowflake
2. Manage users and groups with SCIM
3. 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

1. Discretionary Access Control (DAC)
2. 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

– Describe the benefits of an organization

– Describe organizational tasks

1. Create and name an organization
2. Name various types of organization accounts
3. Identify what regions are available for a given organization

– Understand account tasks

1. View, create, and list accounts
2. Change account names
3. Enable replication for accounts

– Manage Tri-Secret Secure

– Manage encryption keys in Snowflake

1. Describe how Snowflake encrypts customer data
2. Describe encryption key rotation and periodic rekeying configuration
3. 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

1. Implement and manage column-level security using masking policies
2. Use external tokenization to protect Personal Identifiable Information (PII)

– Describe the differences between data masking and external tokenization

– Implement and manage row access policies

1. Configure a row access policy on an object
2. 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

1. Identify use cases where tagging would be beneficial
2. Implement and manage tagging
3. Implement tag-based masking policies
4. Implement data classification (EXTRACT_SEMANTIC_CATEGORIES, ASSOCIATE_SEMANTIC_CATEGORIES)

Account Management and Data Governance

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

1. Use Snowsight dashboards to monitor activity
2. Share worksheets and dashboards
3. Generate and share Snowsight charts
4. Given a scenario, stage data in Snowflake.

– Stage data files from a local file system

1. Use SnowSQL
2. Use Snowsight

– Create, manage, and maintain Snowflake’s internal and external stages

1. Data exfiltration, storage integrations, etc.
2. Given a scenario, manage streams and tasks.

– Outline user-managed (virtual warehouse) tasks and associated use cases

1. Schedule tasks
2. Permissions required for creating and executing tasks
3. Troubleshoot task historical runs

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

– Outline streams and associated use cases

1. Create, monitor, and consume streams
2. 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

1. Describe use cases and benefits
2. Describe, establish, and maintain a scaling policy
3. 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

1. Compare compile versus runtime optimizations
2. Identify/create efficient queries

– Articulate the execution path

– Use effective joining conditions

– Perform grouping, sorting, and ordering

1. Troubleshoot common query performance issues
2. If data spilling is present, describe its impact and remediation tactics
3. If data pruning is not occuring, describe its impact and remediation tactics
4. 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

1. Resultset cache
2. 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

1. Use the search optimization service
2. Create external tables
3. Use data caching
4. 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

Performance Monitoring and Tuning

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

1. Describe the differences between account_usage and organization_usage
2. Monitor accounts and usage on the organization level

– Use the ORGANIZATION_USAGE schema in the SNOWFLAKE shared database

1. Monitor and calculate data transfer costs
2. Monitor and calculate data replication costs

– Forecast and monitor costs and pricing

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

– Describe the use cases for the account_usage and information_schema

1. Views available from the information_schema
2. Latency and data retention considerations

– Monitor and calculate data storage usage/credit

– Monitor and calculate warehouse usage/credits

1. Demonstrate cost saving strategies
2. 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

1. Types of sharing (such as one to one/one to many, private exchange, Snowflake Marketplace)
2. Sharing among different editions of Snowflake
3. 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

1. Create, manage, and maintain an outbound data share
2. Share objects securely in a data share (for example, what type to use)
3. 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)

Data Sharing, Data Exchange, and Snowflake Marketplace

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

1. Create and manage data provider profiles
2. 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

Disaster Recovery, Backup and Data Replication

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

1. Automatic clustering
2. Materialized views
3. External tables
4. Policies (masking and row access)
5. Table streams
6. Tasks
7. Stages (internal and external)
8. Access controls
9. Historical usage data
10. Tags
11. Pipes
12. 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

1. What is database failover or failback?
2. 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

For more information on SnowPro® Advanced Administrator; please visit here.

Contact Locus IT support team for further training details.