Skip to content

Repository files navigation

🧱 Databricks SQL Query Terraform Module

Provisions a single saved Databricks SQL query (databricks_query) against the databricks/databricks provider ~> 1.117.0.

TerraformProviderModuleTypeResourcesPosture

🧩 Overview

  • 📝 Creates one databricks_query — a saved SQL/Lakeview query bound to a specific SQL warehouse.
  • 🧮 Supports widget-style query parameters ({{ key }} markers) via a dynamic "parameter" block — text, numeric, date, date-range, enum (dropdown), and query-backed dropdown widget types.
  • 🚫 Never accepts a credential, host, or account ID; never creates the warehouse it runs against.
  • 🌍 Workspace-plane only — this resource cannot be used with an account-level provider.

💡 Why it matters: a saved query is the reusable unit behind dashboards, alerts, and ad hoc analyst work. Its warehouse_id, display_name, and query_text are all schema-required, and parent_path is force-new — moving a query's workspace folder destroys and recreates it.


❤️ Support this project

If these Terraform modules have been helpful to you or your organization, I'd appreciate your support in any of the following ways:

Whether it's a star, a professional connection, or a coffee, every gesture helps keep these modules actively maintained and continually improving. Thank you for being part of the community!


🗺️ Where this fits

flowchart LR
WAREHOUSE["terraform-databricks-sql-warehouse"]
style WAREHOUSE fill:#1B3139,color:#fff,stroke:#1B3139,stroke-width:1px
CATALOG["terraform-databricks-catalog"]
style CATALOG fill:#F2F2F2,color:#1B3139,stroke:#CCCCCC,stroke-width:1px
SCHEMA["terraform-databricks-schema"]
style SCHEMA fill:#F2F2F2,color:#1B3139,stroke:#CCCCCC,stroke-width:1px
THIS["terraform-databricks-sql-query"]
style THIS fill:#FF3621,color:#fff,stroke:#1B3139,stroke-width:1px
ALERT["terraform-databricks-sql-alert"]
style ALERT fill:#1B3139,color:#fff,stroke:#1B3139,stroke-width:1px
WAREHOUSE -->|"id becomes warehouse_id"| THIS
CATALOG -->|"name becomes catalog (optional)"| THIS
SCHEMA -->|"name becomes schema (optional)"| THIS
THIS -->|"id becomes query_id"| ALERT
Loading

ℹ️ terraform-databricks-sql-warehouse and terraform-databricks-sql-alert are seeded modules in this same authoring batch and do not yet have a committed README/.tf at the time this README was written — the diagram and the end-to-end composition example (§ Example Library) reflect their planned contracts from this library's catalog table, not a verified cross-module terraform plan. terraform-databricks-sql-warehouse wraps databricks_sql_endpoint, not a resource literally named databricks_sql_warehouse — the module name and the underlying Terraform resource name genuinely differ; this module's own warehouse_id variable references that module's id output by module name throughout this README.

🧬 What this builds

flowchart TB
subgraph INPUTS["var.*"]
CORE["display_name / query_text / warehouse_id"]
CTX["catalog / schema / description / parent_path / owner_user_name"]
POLICY["apply_auto_limit / run_as_mode / tags"]
PARAMS["parameters (map, keyed by parameter name)"]
end
KEYSTONE["databricks_query.this"]
style KEYSTONE fill:#1B3139,color:#fff,stroke:#1B3139,stroke-width:1px
subgraph INTERNAL["dynamic blocks inside the keystone"]
PBLOCK["dynamic parameter block, one per map key"]
SUBBLOCK["text_value / numeric_value / date_value / date_range_value / enum_value / query_backed_value, one per parameter"]
end
subgraph OUTPUTS["outputs"]
ID["id"]
NAME["display_name"]
end
CORE --> KEYSTONE
CTX --> KEYSTONE
POLICY --> KEYSTONE
PARAMS --> PBLOCK
PBLOCK --> SUBBLOCK
SUBBLOCK --> KEYSTONE
KEYSTONE --> ID
KEYSTONE --> NAME
Loading

Resource inventory: one resource, databricks_query.this. No separate child resource type — parameter (and its six value-type sub-blocks) is a repeated nested block on the keystone itself, rendered via dynamic "parameter" over var.parameters, a keyed map(object(...)).

✅ Provider / Versions

RequirementValue
Terraform>= 1.12.0
databricks/databricks~> 1.117.0
Provider blockNone — the caller's root module configures provider "databricks" {}
tags / custom_tagsSupported — databricks_query.tags is a plain list(string) (see below)
timeoutsNot confirmed present on databricks_query in the pinned schema — none added

Schema notes that bite:

  • databricks_query (not databricks_sql_query) is the correct, current resource this module wraps. The provider's own registry documentation states databricks_query "supersedes databricks_sql_query" and provides a migration guide. databricks_sql_query still exists in the pinned ~> 1.117.0 schema and uses a visibly different, older argument shape (data_source_id/name/query instead of warehouse_id/display_name/query_text). The compiled terraform providers schema -json for this pinned provider version does not carry a machine-readable deprecated: true flag on either resource — that information lives in the registry's rendered prose documentation, not the wire schema. This README therefore calls databricks_sql_query this library's house-designated legacy/superseded predecessor, not a schema-verified deprecation, and a reader who finds databricks_sql_query in older tutorials should treat this distinction as the reason this module doesn't use it.
  • tags on databricks_query is a plain list(string) — a genuinely different shape from three sibling modules that all handle "tags" differently: terraform-databricks-job and terraform-databricks-pipeline both model tags as map(string), while terraform-databricks-sql-warehouse (wrapping databricks_sql_endpoint) models it as a nested tags { custom_tags { key, value } } block remapped from a map(string) caller variable. A reader moving between these SQL/BI and compute modules should not assume a shared tags contract — each module's tags/custom_tags variable is typed to match its own resource's actual schema, consistent with this library's house rule that Databricks has no universal tags contract.
  • display_name, query_text, and warehouse_id are the three schema-Required attributes — all three are non-optional, non-defaulted fields in this module's variables.tf.
  • parent_path is force-new — the provider's own documentation states "If changed, the query will be recreated."
  • run_as_mode's legal values (OWNER, VIEWER) come from the provider's registry documentation, not the compiled machine schema — terraform providers schema -json for this pinned version exposes run_as_mode as a plain optional string with no enum metadata. This module's validation {} block is grounded in the registry doc text (confirmed against the live provider documentation), which is a verifiable but distinct source from the wire schema.

🔑 Required Databricks Permissions & Scopes

  • Applying identity needs execution access to the target warehouse_id, and — if catalog/schema are set — the corresponding Unity Catalog USE_CATALOG/USE_SCHEMA privileges plus SELECT on any object query_text references. This is Databricks' general SQL-execution permission model, not an argument this module's own schema encodes directly.
  • Post-creation sharing (letting other users/groups view, run, edit, or manage this specific query) is confirmed via the provider's own "Access Control" documentation for databricks_query: a separate databricks_permissions resource with a sql_query_id argument and access levels including CAN_RUN (minimum), CAN_VIEW, CAN_EDIT, CAN_MANAGE. This module does not create that resource itself — pair it with terraform-databricks-permissions.
  • No account-level permissions required — this is a workspace-plane resource.

Databricks Prerequisites

  • Workspace-level provider context — confirmed via the provider's own documentation: "This resource can only be used with a workspace-level provider!"
  • The referenced warehouse_id must already exist. This module does not create the warehouse.
  • If catalog/schema are set, both must already exist in the target metastore.

📁 Module Structure

terraform-databricks-sql-query/
├── providers.tf # required_providers only — no provider {} block
├── variables.tf # display_name, query_text, warehouse_id (required), parameters, tags,...
├── main.tf # databricks_query.this + dynamic "parameter" blocks
├── outputs.tf # id first, then display_name
├── SCOPE.md # cross-module contract
├── README.md # this file
└── examples/
└── basic/
└── main.tf # smallest real, runnable call

⚙️ Quick Start

module"row_count_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Daily Row Count"query_text="SELECT count(*) AS row_count FROM analytics.raw.events"warehouse_id=var.warehouse_id
}

The caller's root module configures provider "databricks" {} (host + auth) and passes it in implicitly; this module accepts neither.

🔌 Cross-Module Contract

Consumes:

InputTypeSource module
warehouse_idstringterraform-databricks-sql-warehouse output id
catalogoptional(string)terraform-databricks-catalog output name
schemaoptional(string)terraform-databricks-schema output name

Emits:

OutputDescriptionConsumed by
idUnique ID of the created queryterraform-databricks-sql-alert (query_id input), databricks_permissions (sql_query_id input)
display_nameName of the saved queryAuditing / drift-detection tooling

📚 Example Library

1 · Minimal parameterless query
module"row_count_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Daily Row Count"query_text="SELECT count(*) AS row_count FROM analytics.raw.events"warehouse_id=var.warehouse_id
}
2 · Text-widget parameter
module"region_lookup_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Sales by Region"query_text="SELECT * FROM analytics.raw.sales WHERE region = {{ region }}"warehouse_id=var.warehouse_idparameters={
region = {
title ="Region"
text_value = { value ="us-east-1" }
}
}
}
3 · Numeric-widget parameter
module"top_n_customers_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Top N Customers by Spend"query_text="SELECT * FROM analytics.gold.customer_spend ORDER BY spend DESC LIMIT {{ limit }}"warehouse_id=var.warehouse_idparameters={
limit = {
title ="Row limit"
numeric_value = { value =25 }
}
}
}
4 · Date-widget parameter (dynamic value)
module"yesterday_events_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Events on a Given Day"query_text="SELECT * FROM analytics.raw.events WHERE event_date = {{ as_of_date }}"warehouse_id=var.warehouse_idparameters={
as_of_date = {
title ="As-of date"
date_value = {
dynamic_date_value ="YESTERDAY"
precision ="DAY_PRECISION"
}
}
}
}

ℹ️ dynamic_date_value accepts NOW or YESTERDAY per the provider's registry documentation — use date_value.date_value instead for a fixed, non-relative date.

5 · Date-range-widget parameter (explicit range)
module"quarterly_report_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Quarterly Revenue Report"query_text="SELECT * FROM analytics.gold.revenue WHERE report_date BETWEEN {{ report_range.start }} AND {{ report_range.end }}"warehouse_id=var.warehouse_idparameters={
report_range = {
title ="Report period"
date_range_value = {
precision ="DAY_PRECISION"
date_range_value = {
start ="2026-01-01"
end ="2026-03-31"
}
}
}
}
}
6 · Enum (dropdown) parameter
module"status_filter_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Orders by Status"query_text="SELECT * FROM analytics.raw.orders WHERE status = {{ status }}"warehouse_id=var.warehouse_idparameters={
status = {
title ="Order status"
enum_value = {
enum_options ="pending\nshipped\ndelivered\ncancelled"
values = ["pending"]
}
}
}
}

ℹ️ enum_options is newline-delimited per the provider's registry documentation, not a list(string) — this module passes the string through unmodified.

7 · Query-backed dropdown parameter
module"customer_drilldown_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Customer Order Drilldown"query_text="SELECT * FROM analytics.gold.orders WHERE customer_id = {{ customer }}"warehouse_id=var.warehouse_idparameters={
customer = {
title ="Customer"
query_backed_value = {
query_id = var.customer_lookup_query_id
multi_values_options = {
separator =","
}
}
}
}
}

ℹ️ query_backed_value.query_id references another saved query's id — typically an earlier terraform-databricks-sql-query module instance's id output.

8 · Unity Catalog–scoped query
module"curated_sales_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Curated Sales Summary"query_text="SELECT * FROM sales_summary"warehouse_id=var.warehouse_idcatalog="analytics"schema="curated"
}
9 · Unbounded result set (auto-limit disabled)
module"full_export_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Full Customer Export"query_text="SELECT * FROM analytics.gold.customers"warehouse_id=var.warehouse_idapply_auto_limit=false
}

⚠️ Secure default is true (1,000-row cap). Only disable it for a query whose caller genuinely needs the full, unbounded result set — an unbounded query against a large table can drive up warehouse compute cost.

10 · run_as_mode variants
module"dashboard_backing_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Dashboard Backing Query"query_text="SELECT * FROM analytics.gold.kpis"warehouse_id=var.warehouse_idrun_as_mode="OWNER"
}
module"analyst_ad_hoc_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Analyst Ad Hoc Query"query_text="SELECT * FROM analytics.raw.events"warehouse_id=var.warehouse_idrun_as_mode="VIEWER"# secure default; explicit here for clarity
}

💡 run_as_mode is a closed two-value enum (OWNER, VIEWER) enforced by this module's own validation {} block, grounded in the provider's registry documentation rather than the compiled schema (see "Schema notes that bite").

11 · Tags for UI filtering
module"tagged_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Monthly Cohort Retention"query_text="SELECT * FROM analytics.gold.cohort_retention"warehouse_id=var.warehouse_idtags=["finance", "monthly", "retention"]
}

ℹ️ tags is a plain list(string) on this resource — not a map(string). See "Schema notes that bite" for the three-way inconsistency across sibling modules.

12 · Explicit workspace folder and owner
module"shared_finance_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Shared Finance Query"query_text="SELECT * FROM analytics.gold.revenue"warehouse_id=var.warehouse_idparent_path="/Shared/Finance/Queries"owner_user_name="finance-reporting-svc@financialpartners.com"
}

⚠️parent_path is force-new — moving this query to a different workspace folder later destroys and recreates it.

13 · for_each-driven multi-query creation at scale
locals {
regional_queries={
"us-east"="us-east-1""us-west"="us-west-2""eu"="eu-west-1"
}
}
module"regional_sales_queries" {
for_each=local.regional_queriessource="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Sales Summary — ${each.key}"query_text="SELECT * FROM analytics.gold.sales WHERE region = '${each.value}'"warehouse_id=var.warehouse_id
}

ℹ️ for_each is applied at the caller's root-module level — this module itself has no child collection to iterate over; each instance creates exactly one query.

14 · Minimal least-privilege baseline (recommended starting point)
module"baseline_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Baseline Query"query_text="SELECT 1"warehouse_id=var.warehouse_id# apply_auto_limit left at its secure default: true# run_as_mode left at its secure default: "VIEWER"
}
🏗️ 15 · End-to-end composition — warehouse → query → alert
module"reporting_warehouse" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-warehouse.git?ref=v1.0.0"name="reporting-warehouse"cluster_size="Small"
}
module"failed_jobs_query" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-query.git?ref=v1.0.0"display_name="Failed Jobs Last 24h"query_text="SELECT count(*) AS failure_count FROM analytics.gold.job_runs WHERE status = 'FAILED' AND run_date >= current_date - 1"warehouse_id=module.reporting_warehouse.id
}
module"failed_jobs_alert" {
source="git::https://github.com/microsoftexpert/terraform-databricks-sql-alert.git?ref=v1.0.0"display_name="Failed Jobs Threshold Alert"query_id=module.failed_jobs_query.idcondition={
op ="GREATER_THAN"
operand = {
column = { name ="failure_count" }
}
threshold = {
value = { double_value =5 }
}
}
}

ℹ️ terraform-databricks-sql-warehouse and terraform-databricks-sql-alert are not yet committed modules at the time this README was written — this composition wires their planned contracts per this library's catalog table (terraform-databricks-sql-warehouse's id output into this module's warehouse_id; this module's id output into terraform-databricks-sql-alert's query_id), not a verified cross-module terraform plan.

📥 Inputs

VariableTypeDefaultNotes
display_namestring— (required)
query_textstring— (required)Not parsed as SQL by terraform validate
warehouse_idstring— (required)From terraform-databricks-sql-warehouse output id
parent_pathstringnullForce-new
owner_user_namestringnull
apply_auto_limitbooltrueSecure default
catalogstringnull
schemastringnull
descriptionstringnull
run_as_modestring"VIEWER"Secure default; OWNER | VIEWER
tagslist(string)[]Not map(string) — see Schema notes that bite
parametersmap(object(...)){}Keyed by parameter name; one *_value sub-object per entry
Full variable declarations
variable"display_name" {
type=string
}
variable"query_text" {
type=string
}
variable"warehouse_id" {
type=string
}
variable"parent_path" {
type=stringdefault=null
}
variable"owner_user_name" {
type=stringdefault=null
}
variable"apply_auto_limit" {
type=booldefault=true
}
variable"catalog" {
type=stringdefault=null
}
variable"schema" {
type=stringdefault=null
}
variable"description" {
type=stringdefault=null
}
variable"run_as_mode" {
type=stringdefault="VIEWER"# validation: must be "OWNER" or "VIEWER"
}
variable"tags" {
type=list(string)
default=[]
}
variable"parameters" {
type=map(object({
title =optional(string)
text_value =optional(object({
value =string
}))
numeric_value =optional(object({
value =number
}))
date_value =optional(object({
date_value =optional(string)
dynamic_date_value =optional(string)
precision =optional(string)
}))
date_range_value =optional(object({
dynamic_date_range_value =optional(string)
precision =optional(string)
start_day_of_week =optional(number)
date_range_value =optional(object({
start =string
end =string
}))
}))
enum_value =optional(object({
enum_options =optional(string)
values =optional(list(string))
multi_values_options =optional(object({
prefix =optional(string)
separator =optional(string)
suffix =optional(string)
}))
}))
query_backed_value =optional(object({
query_id =string
values =optional(list(string))
multi_values_options =optional(object({
prefix =optional(string)
separator =optional(string)
suffix =optional(string)
}))
}))
}))
default={}
}

🧾 Outputs

OutputDescriptionSensitive?
idUnique ID of the created queryNo
display_nameName of the saved queryNo

🧠 Architecture Notes

  • Three-way tags shape inconsistency across sibling SQL/BI and compute modules. This module's tags is a plain list(string). terraform-databricks-job and terraform-databricks-pipeline both model tags as map(string). terraform-databricks-sql-warehouse (wrapping databricks_sql_endpoint) models it as a nested tags { custom_tags { key, value } } block. None of these are wrong — each matches its own resource's actual provider schema — but a reader moving between modules should re-check the type rather than assume consistency.
  • databricks_query vs. the legacy databricks_sql_query. This module exclusively uses databricks_query, the provider's current resource for saved SQL queries, which its own documentation says "supersedes" databricks_sql_query. The legacy resource has a visibly different required-argument shape (data_source_id/name/query). The pinned provider's compiled schema does not carry a machine-readable deprecation flag for either resource — treat the "legacy/superseded" framing as house guidance grounded in the provider's own migration-guide prose, not a schema.json-verified fact.
  • parent_path is force-new. Moving a query to a different workspace folder destroys and recreates it — this is a real operational consideration for a saved query with existing viewers or downstream alerts.
  • No for_each, no separate child resources.parameter (and its six value-type sub-blocks) is a repeated nested block on the keystone itself, rendered via a single dynamic "parameter" block keyed by var.parameters' map keys — there is no independent databricks_query_parameter resource in the provider schema.
  • Exactly one *_value sub-object per parameter is a documented convention, not a schema-enforced constraint. This module does not add a validation {} block forcing mutual exclusion among text_value/numeric_value/date_value/date_range_value/enum_value/ query_backed_value — the same judgment call as terraform-databricks-alert's condition.threshold.value oneof and terraform-databricks-cluster's autoscale/num_workers precedence.

🧱 Design Principles

ConcernSecure defaultOpt-out (caller must set explicitly)
Result-set sizeapply_auto_limit = true (1,000-row cap)Set apply_auto_limit = false explicitly for a query that genuinely needs its full, unbounded result set
Run-as identityrun_as_mode = "VIEWER" (runs with the viewer's own permissions, not the owner's)Set run_as_mode = "OWNER" explicitly when the query must run with a consistent, elevated identity regardless of viewer

Both defaults override an undocumented or permissive provider-level ambiguity toward the safer choice, consistent with this library's "the empty call must produce the safe resource" rule.

CANDIDATE Secure-by-default table row (not yet added to the master table — flagging for a future session): databricks_query.apply_auto_limit defaulting to true and run_as_mode defaulting to "VIEWER" are new secure defaults introduced by this module; this library's master table does not yet have rows for either concern.

🚀 Runbook

cd terraform-databricks-sql-query
terraform init -backend=false
terraform validate
terraform fmt -check

Pin consumers to an immutable tag — ?ref=v1.0.0 — never a branch. This module is plan-only; a human applies from CI after review.

🧪 Testing

terraform validate / terraform fmt -check catch: missing display_name/query_text/ warehouse_id, the run_as_mode enum validation, malformed parameter object shapes, and malformed HCL. They do not catch: whether warehouse_id actually exists, whether query_text is valid SQL against the target catalog/schema, whether the applying identity actually has execution access to the warehouse, or any real Databricks SQL API-side constraint. Those require an actual plan/apply against a live workspace, out of scope for this authoring process.

💬 Example Output

$ terraform output
display_name = "Daily Row Count"
id = "01ef8a3b-1c2d-4e5f-9a0b-123456789abc"

🔍 Troubleshooting

SymptomCauseFix
Apply fails with a permissions error even though terraform validate passedApplying identity lacks execution access to warehouse_id, or lacks USE_CATALOG/USE_SCHEMA/SELECT on referenced objectsConfirm the identity holds the required warehouse and Unity Catalog privileges
Apply attempts to replace the query unexpectedlyparent_path was changedparent_path is force-new; treat any change as a deliberate migration, not a routine edit
A copy-pasted example from an older tutorial references data_source_id/name/query and fails to plan against this moduleThe tutorial used the legacy databricks_sql_query resource, not databricks_queryThis module only wraps databricks_query; translate data_source_idwarehouse_id, namedisplay_name, queryquery_text per the provider's own migration guide
A parameter renders with no visible effect in the UI widgetMore than one *_value sub-object was set on the same parameter, or the sub-object's shape doesn't match the intended widget typeSet exactly one *_value sub-object per parameter entry
Other users can't view or run a query this module createdThis module does not manage post-creation access controlPair with terraform-databricks-permissions (sql_query_id = this module's id)

🔗 Related Docs

  • databricks_query provider resource
  • terraform-databricks-sql-warehouse (upstream, provides warehouse_id)
  • terraform-databricks-sql-alert (downstream, consumes this module's id)
  • This module's SCOPE.md

💙 "Infrastructure as Code should be standardized, consistent, and secure."

About

Terraform module: terraform-databricks-sql-query

Topics

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages