cancel
Showing results for 
Search instead for 
Did you mean: 
Technical Blog
Explore in-depth articles, tutorials, and insights on data analytics and machine learning in the Databricks Technical Blog. Stay updated on industry trends, best practices, and advanced techniques.
cancel
Showing results for 
Search instead for 
Did you mean: 
abhiraisaxena
Databricks Employee
Databricks Employee

Screenshot 2025-06-20 at 10.34.31 PM.png

In the world of modern data platforms, Infrastructure as Code (IaC) is essential for ensuring repeatable, secure, and scalable deployments. In this guide, we’ll walk through setting up an end-to-end CI/CD pipeline for Databricks SQL workloads across Dev, Test, and Prod environments using:

  • Terraform for infrastructure

Terraform should be used to provision infrastructure components such as SQL Warehouses, clusters, Unity Catalog objects, access controls, and secrets.

In the context of Databricks SQL, the warehouse is considered part of the infrastructure and typically remains stable, independent of the SQL code changes. While multiple warehouses can be created to support different workloads (e.g., interactive vs. scheduled), it is not recommended to spin up a separate warehouse for every individual query, job, or pipeline.

  • Databricks Asset Bundles (DABs) for Code

Databricks Asset Bundles should be used for all code-related deployments, including Jobs (Workflows), SQL files, Dashboards, Notebooks, and other workspace-level artifacts. They enable consistent, version-controlled deployment of analytical assets across environments. More details can be found on databricks documentation AWS | Azure | GCP

  • GitHub Actions for continuous deployment

Note: In this blog, we’ve demonstrated the setup on Azure, but the same approach—with minor changes to provider and resource configurations—can be extended to AWS and GCP, and integrated into multi-environment CI/CD pipelines for all three clouds.

Terraform for SQL Warehouse, Query, and Alert

We start by leveraging the Databricks Terraform Provider to automate infrastructure setup. Here’s a main.tf file to:

  • Deploy a SQL warehouse
  • Create a sample query (In this blog, we will build a sample query to be used inside the alert)
  • Set up a SQL alert (In this blog, we will create an alert that checks if there are any tables in the catalog using the sample query)
  • Support parameterization for multiple environments

Here’s a sample main.tf :

# main.tf
# -----------------------------------------------------------------------------
# Terraform Configuration
# -----------------------------------------------------------------------------
terraform {
  required_providers {
    databricks = {
      source  = "databricks/databricks"
      version = ">= 1.83.0"
    }
  }
  required_version = ">= 1.0"
}
# -----------------------------------------------------------------------------
# If using terraform to create azure resources before this step the <data "terraform_remote_state" "azure_workspaces"> can be invoked, else a backend (sample.tfbackend) file can also be used to define the azure resources as in this example.
# -----------------------------------------------------------------------------
#data "terraform_remote_state" "azure_workspaces" {
#  backend = "azurerm"
#  config = {
#    resource_group_name  = var.resource_group_name        # Resource Group for TF state
#    storage_account_name = var.storage_account_name       # Storage Account for TF state
#    container_name       = var.container_name            # Container for state files
#    key                  = var.key                      # Name of this state file
#  }
#}
# -----------------------------------------------------------------------------
# Databricks Provider Configurations for Each Environment
# These now use the host URLs from the variable (tfvars) file.
# -----------------------------------------------------------------------------
provider "databricks" {
  alias               = "default"
  host                = var.workspace_url_dev
  client_id     = var.databricks_client_id
  client_secret = var.databricks_client_secret
}
# -----------------------------------------------------------------------------
# Databricks SQL Warehouse Creation
# -----------------------------------------------------------------------------

resource "databricks_sql_endpoint" "warehouse" {
  provider         = databricks.default
  name             = var.warehouse_name
  cluster_size     = var.warehouse_cluster_size
  min_num_clusters = var.warehouse_min_clusters
  max_num_clusters = var.warehouse_max_clusters
  auto_stop_mins   = var.auto_stop_mins
  enable_photon    = var.enable_photon
  enable_serverless_compute   = var.enable_serverless

  tags {
    custom_tags {
      key   = "Environment"
      value = var.env
    }
  }
}
# -----------------------------------------------------------------------------
# Databricks SQL Warehouse Permissions
# -----------------------------------------------------------------------------
resource "databricks_permissions" "warehouse_permissions" {
  provider         = databricks.default
  sql_endpoint_id = databricks_sql_endpoint.warehouse.id

  access_control {
    group_name       = var.group_name
    permission_level = var.permission_level
  }

  # Optional: Add service principal or user
  # access_control {
  #   service_principal_name = var.sp_name
  #   permission_level       = var.sp_permission_level
  # }
}

# -----------------------------------------------------------------------------
# Databricks SQL Query Creation
# -----------------------------------------------------------------------------

resource "databricks_directory" "shared_dir" {
  provider = databricks.default
  path     = var.parent_path
}

resource "databricks_query" "sample_query" {
  provider     = databricks.default
  warehouse_id = databricks_sql_endpoint.warehouse.id
  display_name = var.sql_display_name
  query_text   = var.query_text #This query will be used for creating a sql alert
  parent_path  = var.parent_path
}

# -----------------------------------------------------------------------------
# Databricks SQL Alert Creation
# -----------------------------------------------------------------------------
resource "databricks_alert" "query_alert" {
  provider     = databricks.default
  display_name = var.alert_display_name
  query_id     = databricks_query.sample_query.id
  condition {
    op = var.alert_condition_op
    operand {
      column {
        name = var.alert_column_name
      }
    }
    threshold {
      value {
        double_value = var.alert_threshold_value
      }
    }
  }
}
# -----------------------------------------------------------------------------
# Outputs
# -----------------------------------------------------------------------------
output "sql_warehouse_urls" {
  description = "The JDBC URLs for the SQL Warehouses."
  value = {
   "${var.env}_dbsql_endpoint"  = databricks_sql_endpoint.warehouse.id
  }
}

output "sql_query_ids" {
  description = "The IDs of the created Databricks SQL Queries."
  value = {
    "${var.env}_sample_query_id"   = databricks_query.sample_query.id
  }
}

output "sql_alert_ids" {
  description = "The IDs of the created Databricks SQL Alerts."
  value = {
    "${var.env}_sample_alert_id"   = databricks_alert.query_alert.id
  }
}

Here is a sample variables.tf file

# variables.tf

variable "env" {
  description = "Environment name (dev/test/prod)"
  type        = string
}

# -----------------------------------------------------------------------------
# Databricks OAuth Credentials
# These variables define the credentials for the Service Principal that Terraform
# will use to authenticate to the Databricks workspaces to create internal resources.
# -----------------------------------------------------------------------------

variable "workspace_url" {
  description = "Databricks workspace URL"
  type        = string
}

variable "databricks_client_id" {
  description = "Azure AD Client ID (Application ID) for the Service Principal used by the Databricks provider."
  type        = string
}

variable "databricks_client_secret" {
  description = "Azure AD Client Secret (value) for the Service Principal used by the Databricks provider."
  type        = string
  sensitive   = true # Mark as sensitive to prevent its value from being displayed in logs or plan output.
}

# -----------------------------------------------------------------------------
# Databricks SQL Warehouse Creation
# -----------------------------------------------------------------------------

variable "warehouse_name" {
  description = "Name of the SQL warehouse"
  type        = string
  default     = "default_sql_warehouse"
}

variable "warehouse_cluster_size" {
  description = "Cluster size for SQL warehouse"
  type        = string
  default     = "2X-Small"
}

variable "warehouse_min_clusters" {
  description = "Min number of clusters for SQL warehouse"
  type        = number
  default     = 1
}

variable "warehouse_max_clusters" {
  description = "Max number of clusters for SQL warehouse"
  type        = number
  default     = 1
}

variable "auto_stop_mins" {
  description = "Auto stop time in minutes for SQL warehouse"
  type        = number
  default     = 10
}

variable "enable_photon" {
  description = "Enable Photon for SQL warehouse"
  type        = bool
  default     = false
}

variable "enable_serverless" {
  description = "Enable serverless compute"
  type        = bool
  default     = true
}

# -----------------------------------------------------------------------------
# Databricks SQL Warehouse Permissions
# -----------------------------------------------------------------------------

variable "group_name" {
  description = "Name of the group to assign permissions to the SQL warehouse"
  type        = string
  default     = "data-engineers"
}

# Optional: Uncomment if you want to use a service principal instead of a group
# variable "sp_name" {
#   description = "Name of the group to assign permissions to the SQL warehouse"
#   type        = string
#   default     = "data-engineers"
# }

variable "permission_level" {
  description = "Permission level for the SQL warehouse (e.g., 'CAN_USE', 'CAN_MANAGE')"
  type        = string
  default     = "CAN_USE"
}

# -----------------------------------------------------------------------------
# Databricks SQL Query Creation
# -----------------------------------------------------------------------------


 variable "sql_display_name" {
  description = "Display name for the SQL query"
  type        = string
  default     = "Example Test Query"
  }

 variable "query_text" {
  description = "SQL query text to be executed"
  type        = string
  default     = "SELECT 1 as count_tables;"
  }

   variable "parent_path" {
  description = "Parent path for the SQL query in Databricks"
  type        = string
  default     = "/Shared/Queries"
  }

# -----------------------------------------------------------------------------
# Databricks SQL Alert Creation
# -----------------------------------------------------------------------------

variable "alert_display_name" {
  description = "Display name for the SQL alert"
  type        = string
  default     = "Example Query Alert"
}

variable "alert_condition_op" {
  description = "Condition operator for the SQL alert (e.g., 'GREATER_THAN')"
  type        = string
  default     = "LESS_THAN"
  
}

variable "alert_column_name" {
  description = "Column name for the SQL alert condition"
  type        = string
  default     = "table_count"
}

variable "alert_threshold_value" {
  description = "Threshold value for the SQL alert condition"
  type        = number
  default     = 1
}

Here is a sample terraform.dev.tfvars file

# terraform.dev.tfvars

env = "dev"

# -----------------------------------------------------------------------------
# Azure Databricks Configuration for Terraform
# This section defines the Azure Databricks workspace configuration for Terraform.
# Ensure the workspace is already created and accessible with the provided credentials.
# Replace with your actual workspace details
# -----------------------------------------------------------------------------

workspace_url           = "<databricks-url>"
databricks_client_id     = "<client-id>" # Your Azure AD Application (Client) ID
databricks_client_secret = "<very-secret-client-secret>" # Your Azure AD Client Secret Value

# -----------------------------------------------------------------------------
# Databricks SQL Warehouse Creation
# -----------------------------------------------------------------------------

warehouse_name      = "dev_sql_warehouse"
warehouse_cluster_size  = "2X-Small"
warehouse_min_clusters  = 1
warehouse_max_clusters  = 1
auto_stop_mins         = 10
enable_photon          = false
enable_serverless       = true

# -----------------------------------------------------------------------------
# Databricks SQL Warehouse Permissions
# -----------------------------------------------------------------------------

group_name = "users" #Actual Group name here
permission_level = "CAN_USE"
# sp_name = "sp_name_here" # Optional: If you want to add a service principal
# sp_permission_level = "CAN_USE" # Optional: If you want to add a service principal

# -----------------------------------------------------------------------------
# Databricks SQL Query Creation
# -----------------------------------------------------------------------------

sql_display_name = "Check Count of Tables"
query_text = "SELECT COUNT(*) as table_count FROM system.information_schema.tables;" #This is a sample query
parent_path = "/Shared/Queries" # Path in Databricks where the query will be

# -----------------------------------------------------------------------------
# Databricks SQL Alert Creation
# -----------------------------------------------------------------------------

alert_display_name = "Example Query Alert" # Display name for the SQL alert
alert_condition_op = "LESS_THAN" # Condition operator for the alert
alert_column_name = "table_count" # Column name to check in the alert condition
alert_threshold_value = 1 # Value for the alert condition

Here is a sample dev.tfbackend file

resource_group_name = "<resource-group-name>" # Existing Resource Group
resource_account_name = "<storage-account-name>" # Existing Storage Account
container_name       = "<container-name>" # Existing Container
key                  = "<file_name.tfstate>" # name of the TF state file

Multi-Environment Deployment

Use different .tfvars files for environment-specific overrides:

# For dev
terraform init -backend-config="dev.tfbackend"
terraform workspace create dev
terraform workspace select dev
terraform plan -var-file="terraform.dev.tfvars"
terraform apply -var-file="terraform.dev.tfvars"

# For test
terraform init -backend-config="test.tfbackend"
terraform workspace create test
terraform workspace select test
terraform plan -var-file="terraform.test.tfvars"
terraform apply -var-file="terraform.test.tfvars"

# For prod
terraform init -backend-config="prod.tfbackend"
terraform workspace create prod
terraform workspace select prod
terraform plan -var-file="terraform.prod.tfvars"
terraform apply -var-file="terraform.prod.tfvars"
Other Useful Resources:

Databricks Asset Bundles (DABs) with Dashboard

Databricks  Asset Bundles (DABs) are a declarative, YAML-based packaging mechanism used to manage and deploy SQL analytics artifacts such as queries, dashboards, notebooks, and workflows. In the context of Databricks SQL (DBSQL), DABs enable version-controlled, environment-aware deployment of SQL queries, dashboard, workflows (jobs) etc.

DABs help teams separate infrastructure from code, supporting CI/CD pipelines by allowing consistent deployments across dev, test, and prod environments without redefining the infrastructure (like SQL Warehouses).

Here’s a sample databricks.yml:

# This is a Databricks asset bundle definition for dabs_cicd_demo.
# See https://docs.databricks.com/dev-tools/bundles/index.html for documentation.
bundle:
  name: dabs_cicd_demo # The name of the bundle, used to create a directory in the workspace.

include:
  # Include all resources in the 'resources' directory.
  - resources/*.yml"  # This will include all YAML files in the resources directory.
  
  # # Optional! If not choosing the above method, you can include specific resources for jobs, dashboards, and SQL queries.
  # - resources/dashboards.yml
  # - resources/jobs.yml

# Variable declarations. These variables are assigned in the dev/prod targets below.
variables:
  warehouse_id:
    description: The warehouse to use # This is the SQL warehouse ID where the SQL queries will be executed.
  catalog:
    description: The catalog to use # This is the catalog where the SQL queries will be executed.
  schema:
    description: The schema to use # This is the schema where the SQL queries will be executed.
  job_sp:
    description: SP to run job # This is a service principal ID that will be used to run the job.

targets:
  dev:
    mode: development
    # The default target uses 'mode: development' to create a development copy.
    # - Deployed resources get prefixed with '[dev my_user_name]'
    # - Any job schedules and triggers are paused by default.
    # See also https://docs.databricks.com/dev-tools/bundles/deployment-modes.html.
    default: true
    workspace:
      host: <dev-databricks-host>
      root_path: ~/.bundle/${bundle.name}/${bundle.target}
    variables:
      warehouse_id:
	  lookup:
	    warehouse: "<dev-warehouse-name>"
      catalog: dev_catalog
      schema: default
      job_sp: <dev-service-principal>

  test:
    mode: development
    workspace:
      host: <test-databricks-host>
      root_path: ~/.bundle/${bundle.name}/${bundle.target}
    variables:
      warehouse_id:
	  lookup:
	    warehouse: "<test-warehouse-name>"
      catalog: test_catalog
      schema: default
      job_sp: <test-service-principal>
    permissions:
      - group_name: users # Your group name here, e.g., 'users', 'admins', etc.
        level: CAN_RUN # This permission allows all users in the 'users' group to run the jobs defined in this bundle. CAN_RUN, CAN_MANAGE, CAN_VIEW, or CAN_EDIT can be used.

  prod:
    mode: production
    # The production target uses 'mode: production' to create a production copy.
    workspace:
      host: <prod-databricks-host>
      root_path: ~/.bundle/${bundle.name}/${bundle.target}
    variables:
      warehouse_id:
	  lookup:
	    warehouse: "<prod-warehouse-name>"
      catalog: prod_catalog
      schema: default
      job_sp: <prod-service-principal>
    permissions:
      - group_name: users # Your group name here, e.g., 'users', 'admins', etc.
        level: CAN_RUN # This permission allows all users in the 'users' group to run the jobs defined in this bundle. CAN_RUN, CAN_MANAGE, CAN_VIEW, or CAN_EDIT can be used.

You can deploy this bundle using databricks CLI or use GitHub Actions as shown in the next step.

Other Useful Resources:

GitHub Actions Workflow for CI/CD

In addition to the Terraform-based infrastructure pipeline, you can now automate Databricks Asset Bundle (DAB) deployments using GitHub Actions. Please note that to keep the example focused and concise, the Terraform pipeline is not shown here, though it can be fully integrated into the same GitHub Actions workflow if desired.

Once the initial setup is complete, any new feature or change should be developed in a feature branch and merged into the dev branch. This triggers the CI/CD pipeline, which automatically deploys the updated Databricks bundle and runs the associated workflow in the development environment.

After successful validation in dev, changes can be pushed to the test branch for QA. This deploys the bundle to the test workspace, enabling thorough testing in an isolated environment.

Once testing is complete, final deployment is achieved by pushing or merging the changes into the prod branch, promoting the updates to the production environment.

The following YAML workflow demonstrates how to deploy a Databricks bundle.

Here’s a sample yaml file deploy.yml:

name: Databricks Bundle Deploy

on:
  push:
    branches:
      - dev # Development branch
      - test # Testing branch
      - prod # Production branch

jobs:
  deploy:
    runs-on: ubuntu-latest
    # This picks the environment based on the branch name
    environment: ${{ github.ref_name }} # This will set the environment to 'dev', 'test', or 'prod' based on the branch name

    steps:
      - name: Checkout code # Checkout the code from the repository
        uses: actions/checkout@v4 # Checkout the code from the repository

      - name: Set Databricks bundle target based on branch # This step sets the target based on the branch name
        id: set-target # Set an ID for this step to use its output later
        run: |
          if [[ "${GITHUB_REF##*/}" == "dev" ]]; then
            echo "target=dev" >> $GITHUB_OUTPUT
          elif [[ "${GITHUB_REF##*/}" == "test" ]]; then
            echo "target=test" >> $GITHUB_OUTPUT
          elif [[ "${GITHUB_REF##*/}" == "prod" ]]; then
            echo "target=prod" >> $GITHUB_OUTPUT
          else
            echo "Unknown branch, exiting."
            exit 1
          fi

      - name: Set up Databricks CLI # This step sets up the Databricks CLI
        uses: databricks/setup-cli@main # Use the official Databricks CLI setup action

      - name: Debug Branch Info # This step is for debugging purposes to print branch information
        run: |
          echo "github.ref: ${{ github.ref }}"
          echo "github.ref_name: ${{ github.ref_name }}"
          echo "github.head_ref: ${{ github.head_ref }}"
          echo ${{ steps.set-target.outputs.target }}
          echo ${{ secrets.DATABRICKS_HOST }}

      - name: Validate Bundle # This step validates the Databricks bundle before deploying
        env:
          DATABRICKS_CLIENT_ID: ${{ secrets.DATABRICKS_CLIENT_ID }}
          DATABRICKS_CLIENT_SECRET: ${{ secrets.DATABRICKS_CLIENT_SECRET }}
          DATABRICKS_TENANT_ID: ${{ secrets.DATABRICKS_TENANT_ID }}
          DATABRICKS_AUTH_TYPE: oauth-m2m
        run: |
          databricks bundle validate --target ${{ steps.set-target.outputs.target }}

      - name: Deploy Bundle # This step deploys the Databricks bundle to the specified target
        env:
          DATABRICKS_CLIENT_ID: ${{ secrets.DATABRICKS_CLIENT_ID }}
          DATABRICKS_CLIENT_SECRET: ${{ secrets.DATABRICKS_CLIENT_SECRET }}
          DATABRICKS_TENANT_ID: ${{ secrets.DATABRICKS_TENANT_ID }}
          DATABRICKS_AUTH_TYPE: oauth-m2m
        run: |
          databricks bundle deploy --target ${{ steps.set-target.outputs.target }}

      # Optional: Run a Databricks workflow after deploy. Uncomment the following lines if you want to run a Databricks workflow after deploying the bundle
      # - name: Run Databricks workflow
      #   env:
          # DATABRICKS_CLIENT_ID: ${{ secrets.DATABRICKS_CLIENT_ID }}
          # DATABRICKS_CLIENT_SECRET: ${{ secrets.DATABRICKS_CLIENT_SECRET }}
          # DATABRICKS_TENANT_ID: ${{ secrets.DATABRICKS_TENANT_ID }}
          # DATABRICKS_AUTH_TYPE: oauth-m2m
      #   run: |
      #     databricks bundle run <resource-key> --target ${{ steps.set-target.outputs.target }} --refresh-all # Execute job after deployment

This structured flow ensures a seamless, automated, and reliable CI/CD process for Databricks SQL pipelines across development, testing, and production stages.

Other Useful Resources:

Putting It All Together

This setup achieves:

  • IaC-first provisioning of SQL Warehouses, Queries, and Alerts via Terraform
  • Declarative SQL assets deployment using DABs
  • Automated, environment-specific CI/CD pipelines with GitHub Actions
  • Secure deployments using service principals and GitHub secrets
  • Clear separation of Dev, Test, and Prod environments