SNOWFLAKE DBA INTERVIEW QUESTIONS
1. Architecture & Storage
Explain Snowflake’s unique architecture. * Answer: It is a hybrid of shared-disk and shared-nothing architectures. It consists of three layers: Database Storage (columnar format, micro-partitions), Query Processing (Virtual Warehouses), and Cloud Services (metadata, security, optimization).
What are Micro-partitions and how do they benefit the DBA?
Answer: All data in Snowflake is automatically divided into micro-partitions (50MB to 500MB of uncompressed data). This eliminates the need for manual partitioning and enables "Pruning," where the engine only scans relevant partitions.
What is the difference between Time Travel and Fail-safe?
Answer: Time Travel allows you to access historical data (up to 90 days for Enterprise) for recovery or querying. Fail-safe is a non-configurable 7-day period after Time Travel expires where Snowflake Support can recover data in case of extreme failure.
2. Virtual Warehouses & Performance Tuning
How do you handle "Resource Queuing" in Snowflake?
Answer: If a warehouse is overloaded, queries queue. You can solve this by scaling up (increasing warehouse size for large queries) or scaling out (using Multi-cluster Warehouses for high concurrency).
Explain the difference between Scaling Up and Scaling Out.
Answer: Scaling Up (e.g., Small to Medium) increases the power of a single warehouse to handle complex, large-scale queries. Scaling Out adds more clusters to a warehouse to handle a high volume of concurrent users/queries.
How do you identify a poorly performing query?
Answer: Use the Query Profile in the Snowflake UI. Look for "Exploding Joins," "Data Spilling" (to local or remote storage), and high "Partitions Scanned" vs. "Partitions Total" ratios.
What is "Data Spilling" and how do you fix it?
Answer: Spilling occurs when the warehouse memory is insufficient for a query, forcing data to be written to local SSD or remote storage. The fix is usually to Scale Up to a larger warehouse size.
3. Security & Governance
How does Role-Based Access Control (RBAC) work in Snowflake?
Answer: Privileges are granted to roles, and roles are assigned to users. Key system roles include
ACCOUNTADMIN (top-level), SECURITYADMIN (manages grants), and SYSADMIN (creates objects).What is a "Secure View"?
Answer: It is a view designed for data privacy. It prevents users from seeing the underlying SQL definition or using certain optimization techniques to "guess" data in rows they shouldn't see.
How would you implement Column-Level Security?
Answer: Using Masking Policies. You can define a policy that shows the full data to an admin but masks it (e.g.,
XXXX-XXXX) for a general user role.4. Cost Management (Crucial for DBAs)
How can you prevent "Bill Shock" in a Snowflake account?
Answer: 1. Set Resource Monitors to alert or suspend warehouses when credit limits are hit. 2. Configure Auto-suspend on all warehouses (e.g., 60 seconds of inactivity). 3. Monitor the
STORAGE_USAGE and WAREHOUSE_METERING_HISTORY views in ACCOUNT_USAGE.Does Zero-copy Cloning incur extra costs?
Answer: Initially, no. Cloning creates a new set of metadata pointing to the same micro-partitions. You only pay for storage when data in the clone (or original) is modified or deleted, creating new micro-partitions.
5. Data Ingestion & Transformation
What is Snowpipe and how does it differ from the COPY command?
Answer:
COPY is used for bulk loading (manual/scheduled). Snowpipe is a continuous data ingestion service that loads files as soon as they arrive in a stage (S3/Azure Blob/GCS) using a "serverless" compute model.What is the VARIANT data type?
Answer: It is a special data type used to store semi-structured data like JSON, Avro, or Parquet. Snowflake optimizes this data internally, allowing you to query it using standard SQL (dot notation).
The Scenario: "How would you migrate a 10TB On-Premise SQL Server/Oracle database to Snowflake?"
This question tests your ability to plan for security, networking, and data integrity, rather than just running a COPY command.
Phase 1: Assessment & Strategy
Schema Conversion: Use tools to convert DDL. Note that Snowflake doesn't use traditional indexes (it uses micro-partitions), so you must remove index definitions and focus on Clustering Keys for very large tables.
Identify Data Types: Ensure semi-structured data (XML/JSON) is mapped to Snowflake's
VARIANTtype for better performance.
Phase 2: Data Movement
The "Stage" Strategy: You cannot move 10TB over the public internet easily.
Export data to flat files (Parquet or CSV) and compress them.
Use a cloud-native transport tool (like AWS Snowball or Azure Data Box) or a high-speed CLI (
gsutil,aws s3 cp).Load the data into a Cloud Stage (S3/Blob Storage/GCS).
Phase 3: The Loading Process
Warehouse Sizing: I would use a Large or X-Large Virtual Warehouse for the initial bulk load to ensure high throughput, then scale it down once the migration is complete.
Validation: Use the
VALIDATION_MODEparameter in theCOPY INTOcommand to check for errors without actually loading data, ensuring the file format matches the table schema.
Additional Common Scenario Questions
| Scenario | DBA Action/Solution |
| Accidental Table Drop | Use UNDROP TABLE <table_name>; immediately. |
| Query running for 10 hours | Set STATEMENT_TIMEOUT_IN_SECONDS at the warehouse or user level to auto-kill runaway queries. |
| Data Privacy (PII) | Implement Dynamic Data Masking so only HR roles see full Social Security Numbers, while others see XXX-XX-XXXX. |
| Cross-Region Disaster Recovery | Set up Database Replication to another Snowflake region and use Failover Groups. |
1. Role Design & Hierarchy
What is the "Functional Role vs. Access Role" (FRAR) model?
Answer: This is a best practice where Access Roles (AR) are granted technical privileges on specific objects (e.g.,
READ_RAW_DB,WRITE_STAGE_DB). These are then granted to Functional Roles (FR) which represent business functions (e.g.,DATA_ENGINEER,ANALYST). Users are assigned to FRs. This decouples object-level security from organizational changes.
Why should custom roles always be rolled up to
SYSADMIN?Answer: By granting custom roles to
SYSADMIN, you ensure that system administrators have visibility and control over all objects created by those roles. If a role is "orphaned" (not in the hierarchy),SYSADMINcannot manage its objects, leading to security blind spots.
How do "Secondary Roles" change the user experience?
Answer: Historically, users had to
USE ROLEto switch contexts. Enabling secondary roles (USE SECONDARY ROLES ALL) allows a user to perform actions using the aggregate privileges of all roles assigned to them in a single session, though the "Primary Role" still determines the ownership of any newly created objects.
2. Advanced Administration (SECURITYADMIN vs. USERADMIN)
What is the functional difference between
SECURITYADMINandUSERADMIN?Answer:
USERADMINis dedicated to creating users and roles.SECURITYADMINinheritsUSERADMINbut also holds theMANAGE GRANTSprivilege. This meansSECURITYADMINcan modify or revoke any grant in the account, even those they didn't create.
When would you use a Managed Access Schema?
Answer: In a Regular Schema, the object owner (the role that created a table) can grant access to others. In a Managed Access Schema, the ability to grant privileges is centralized to the Schema Owner (or roles with
MANAGE GRANTS). This is essential for high-governance environments to prevent "privilege sprawl."
Explain the "Future Grants" concept and its risks.
Answer: Future grants (
GRANT SELECT ON FUTURE TABLES IN SCHEMA...) automatically assign permissions to objects not yet created. The risk is that if a DBA accidentally creates a sensitive table in that schema, it is immediately accessible to the assigned roles without further review.
3. Troubleshooting & Scenarios
Scenario: A user has
SELECTon a table but gets an "Insufficient Privileges" error. What do you check?Answer: I check three things:
Does the role have
USAGEon the Database?Does the role have
USAGEon the Schema?Is there a Row Access Policy or Masking Policy attached to the table that might be blocking the specific user?
Scenario: You need to transfer ownership of 500 tables from a retired role to a new one. How do you do this efficiently?
Answer: I would use
GRANT OWNERSHIP ON ALL TABLES IN SCHEMA <name> TO ROLE <new_role> COPY CURRENT GRANTS;. UsingCOPY CURRENT GRANTSis critical to ensure existing access for other roles isn't wiped out during the transfer.
4. Automation & Governance
How do you implement RBAC at scale (Infrastructure as Code)?
Answer: For 5 years of experience, you should mention tools like Terraform, Pulumi, or specialized Snowflake tools like Permifrost or Snow-TRAM. Managing roles manually via SQL is not scalable for enterprise environments.
How does Snowflake handle "Database Roles"?
Answer: Database Roles exist within a specific database rather than at the account level. They are excellent for Data Sharing or isolating permissions within a single database without cluttering the global account role list.
Comparison Table: Schema Security Models
| Feature | Regular Schema | Managed Access Schema |
| Who can grant? | Object Owner (Creator) | Schema Owner only |
| Control | Decentralized / Flexible | Centralized / Strict |
| Best For | Dev / Sandbox | Production / Sensitive Data |
| Ownership | Creator owns object | Creator owns, but cannot grant |
Comments
Post a Comment