ARA-C01 Question 184
Single answerCI/CDA data platform team is implementing CI/CD for Snowflake objects across DEV, TEST, and PROD accounts. They use Git for version control and want deployments to be automated, repeatable, and safe. The team must promote changes such as tables, views, tasks, and grants while minimizing environment-specific script edits and reducing the risk of privilege drift between environments. Which approach should the architect recommend?
- A
Store DDL scripts in Git, hardcode fully qualified object names and role names for each environment, and use separate deployment branches per environment so changes can be manually adjusted before release.
- B
Use a declarative, state-based deployment approach with environment parameterization, keep object definitions in source control, and automate deployments through a service principal role with least privilege and repeatable promotion through pipelines.
- C
Have developers run CREATE OR REPLACE statements directly from their local workstations against each target environment after pull request approval, because Snowflake automatically reconciles grants during object replacement.
- D
Export object definitions from DEV after each sprint, manually compare them to PROD, and apply only the changed SQL statements in PROD to avoid introducing unintended differences from source control.
Show answer and explanation
Correct answer: B
Explanation
For Snowflake CI/CD, best practice is to keep database code and configuration in version control, use automated pipelines to validate and promote changes, and avoid environment-specific script rewrites wherever possible. A declarative or state-based deployment model is generally the most maintainable for managing Snowflake objects across accounts or environments because it allows teams to define the desired end state and apply it consistently. Parameterization should handle differences such as database names, warehouses, or integration endpoints instead of creating separate manually edited scripts. Automated deployment should run under a dedicated role with least privilege so that changes are controlled, repeatable, and auditable. This aligns with Snowflake guidance around role-based access control, change management, and DevOps-oriented deployment patterns. A key architectural concern is preventing drift: the same tested code should move through environments, and grants or dependencies should be explicitly managed rather than assumed to be preserved by object replacement behavior.
- A. Incorrect.
This is incorrect because hardcoding environment-specific object names and role names into scripts increases maintenance overhead and deployment risk. It leads to frequent script rewrites between DEV, TEST, and PROD, which is the opposite of a robust CI/CD design. Using separate branches per environment for manual edits also encourages configuration drift and makes it harder to prove that the same tested change is what was ultimately promoted.
- B. Correct.
This is correct because a declarative, state-based approach aligns well with CI/CD goals for Snowflake: desired object definitions are versioned in source control, deployment logic is parameterized per environment, and the same pipeline promotes tested changes consistently. Using a dedicated automation identity or service role with least privilege reduces operational risk and supports auditable, repeatable deployments. This approach also helps manage grants and other object dependencies in a controlled way rather than relying on ad hoc manual SQL execution.
- C. Incorrect.
This is incorrect because local, manual execution is not a reliable CI/CD practice. It reduces auditability, increases the chance of inconsistent deployments, and bypasses pipeline controls. In addition, the statement about Snowflake automatically reconciling grants during object replacement is a misconception. Depending on the object type and the deployment pattern used, replacing objects can affect privileges and dependencies, so grant management should be explicitly handled as part of the deployment process.
- D. Incorrect.
This is incorrect because exporting from DEV and manually comparing to PROD is a brittle release process that treats environments as the source of truth instead of source control. Manual diff-and-apply workflows are error-prone, difficult to audit, and do not support repeatable promotions. A mature CI/CD pattern should derive deployments from version-controlled definitions and pipeline automation rather than manual environment comparison.