求基于GitLab的Snowflake CI/CD多环境部署YAML配置文件

前置准备 #

在 GitLab 项目的 Settings > CI/CD > Variables 中配置以下敏感变量（建议勾选「Masked」和「Protected」）：

• SNOWFLAKE_ACCOUNT：Snowflake 账户标识（含区域后缀，如 xy12345.us-west-2）
• SNOWFLAKE_USER：Snowflake 操作用户名
• SNOWFLAKE_PASSWORD：Snowflake 用户密码
• SNOWFLAKE_ROLE：执行部署的 Snowflake 角色（如 ACCOUNTADMIN 或自定义部署角色）
• SNOWFLAKE_WH：使用的 Snowflake 虚拟仓库
• SNOWFLAKE_DEV_DB：开发环境数据库名
• SNOWFLAKE_STAGING_DB：预发布环境数据库名
• SNOWFLAKE_PROD_DB：生产环境数据库名

GitLab CI/CD YAML 配置文件 #

# 定义部署阶段
stages:
  - syntax_check
  - deploy_dev
  - deploy_staging
  - deploy_prod

# 语法检查阶段：验证SQL脚本语法正确性
syntax_check:
  stage: syntax_check
  image: snowflakedb/snowsql:latest
  script:
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH --validate-only -f ./scripts/ddl_script.sql
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH --validate-only -f ./scripts/dml_script.sql
  rules:
    - if: '$CI_COMMIT_BRANCH =~ /^dev|main$/'

# 部署至开发环境
deploy_dev:
  stage: deploy_dev
  image: snowflakedb/snowsql:latest
  script:
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH -d $SNOWFLAKE_DEV_DB -f ./scripts/ddl_script.sql
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH -d $SNOWFLAKE_DEV_DB -f ./scripts/dml_script.sql
  rules:
    - if: '$CI_COMMIT_BRANCH == "dev"'

# 部署至预发布环境
deploy_staging:
  stage: deploy_staging
  image: snowflakedb/snowsql:latest
  script:
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH -d $SNOWFLAKE_STAGING_DB -f ./scripts/ddl_script.sql
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH -d $SNOWFLAKE_STAGING_DB -f ./scripts/dml_script.sql
  rules:
    - if: '$CI_COMMIT_BRANCH == "main"'
      when: manual

# 部署至生产环境（手动触发，仅保护分支可执行）
deploy_prod:
  stage: deploy_prod
  image: snowflakedb/snowsql:latest
  script:
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH -d $SNOWFLAKE_PROD_DB -f ./scripts/ddl_script.sql
    - snowsql -a $SNOWFLAKE_ACCOUNT -u $SNOWFLAKE_USER -p $SNOWFLAKE_PASSWORD -r $SNOWFLAKE_ROLE -w $SNOWFLAKE_WH -d $SNOWFLAKE_PROD_DB -f ./scripts/dml_script.sql
  rules:
    - if: '$CI_COMMIT_BRANCH == "main"'
      when: manual
      allow_failure: false
  environment:
    name: production
    url: https://your-snowflake-account.snowflakecomputing.com/console#/internal/databases/$SNOWFLAKE_PROD_DB

关键说明 #

1. 镜像选择：直接使用官方 snowflakedb/snowsql 镜像，无需手动安装SnowSQL工具
2. 阶段逻辑：
   • 语法检查自动触发，确保脚本无语法错误
   • 开发环境自动部署，适配日常迭代
   • 预发布/生产环境需手动确认，降低误操作风险
3. 脚本路径：根据实际项目结构调整 ./scripts/ddl_script.sql、./scripts/dml_script.sql 的路径
4. 环境隔离：通过不同数据库变量区分开发、预发布、生产环境，避免交叉污染

内容的提问来源于stack exchange，提问作者Makkuva Komal Krishnamraju