ARA-C01 Question 183
Single answerCI/CDA data platform team is implementing CI/CD for Snowflake database objects across DEV, TEST, and PROD. They want a deployment approach that supports automated promotion through environments, minimizes hard-coded environment-specific values, and preserves object dependencies. The team stores SQL definitions for schemas, tables, views, and tasks in Git and uses a pipeline runner to execute deployments. Which approach is the MOST appropriate?
- A
Package all object DDL into a single SQL script with fully qualified DEV object names, then use search-and-replace in the pipeline to swap DEV with TEST or PROD before execution.
- B
Use a declarative, state-based deployment process that templates environment-specific values externally, resolves dependencies in deployment order, and runs under a dedicated deployment role in each environment.
- C
Clone the DEV database into TEST and PROD during each release so that all changes, including tasks and views, are copied exactly without needing scripted deployment logic.
- D
Manually run CREATE OR REPLACE statements for each changed object in every environment, because object replacement is the safest way to ensure consistency across deployments.
Show answer and explanation
Correct answer: B
Explanation
For Snowflake CI/CD, the strongest architectural pattern is to keep object definitions in version control and deploy them through an automated pipeline that is environment-aware without embedding environment-specific values directly in source SQL. In practice, this means parameterizing items such as database names, schemas, warehouses, notification integrations, and role references, then applying them during pipeline execution. A declarative or state-based deployment model is particularly effective because it compares desired state from source control to the target environment and applies changes in the correct order.
This approach is preferable to string substitution on static scripts, manual deployments, or using database cloning as the primary release mechanism. Snowflake supports zero-copy cloning, which is valuable for rapidly provisioning lower environments and testing, but cloning is not a substitute for governed promotion of versioned code. Likewise, manual CREATE OR REPLACE execution does not meet CI/CD objectives for automation and auditability.
Relevant Snowflake best practices include using role-based access control with least privilege, separating environment-specific configuration from object logic where possible, and managing dependencies among database objects during deployment. Snowflake documentation on database object management, zero-copy cloning, tasks, RBAC, and DevOps-oriented workflows supports these principles.
- A. Incorrect.
This is not the most appropriate approach. Simple search-and-replace across hard-coded fully qualified names is brittle and error-prone, especially when object references, database names, warehouse names, integrations, or role names differ by environment. It also does not inherently manage dependency ordering, drift detection, or safe promotion practices. Teams often start here because it seems easy, but it scales poorly and increases deployment risk.
- B. Correct.
This is the best answer. A declarative, state-based CI/CD approach aligns well with Snowflake deployment best practices because it promotes repeatability and automation while reducing environment-specific hard coding. Externalizing variables such as database names, warehouse names, and integration references supports promotion across DEV, TEST, and PROD. Dependency-aware deployment ordering is important for objects like schemas, tables, views, and tasks, since dependent objects may fail if deployed before their prerequisites exist. Running deployments with a dedicated role per environment follows least-privilege and operational governance practices.
- C. Incorrect.
This is incorrect because database cloning is useful for environment creation, testing, and point-in-time replication of metadata/data, but it is not a general-purpose CI/CD promotion mechanism for controlled production releases. Cloning DEV directly into higher environments can propagate unintended data, immature object definitions, or environment-specific configurations. It also bypasses the discipline of versioned, auditable deployment scripts or declarative state promotion from source control.
- D. Incorrect.
This is incorrect. Although CREATE OR REPLACE can be used in deployment automation for some object types, a manual process does not satisfy CI/CD goals of repeatability, auditability, and reliable automated promotion. In addition, replacing objects without considering dependencies, grants, operational state, and downstream impact can cause outages or unexpected behavior. The misconception is that replacement alone equals consistency, when the real requirement is controlled, automated, dependency-aware deployment.