When there is no Terraform provider: a Power BI integration that would not go quietly

Some infrastructure-as-code projects are clean. The provider exists, the API is well-documented, the resources line up neatly, the deployment is uneventful. You finish, you move on, you write the article about the elegant abstraction you came up with.

This is not one of those articles.

This is the article about a Power BI ↔ Databricks integration where, when I built it in mid-2025, Microsoft had no Terraform provider for the relevant API surface, the API itself was partially documented in places where it wasn’t outright wrong, and the only honest path between “I want a Power BI connection to my Databricks SQL warehouse” and “I have one, in code, repeatable, with rotating credentials” ran through a number of design choices that would make a Hashicorp engineer wince. I’m writing it a year later because the patterns are useful, the limits are instructive, and the workaround — for all its sins — has been in production the entire time.

(Microsoft has since shipped a GA microsoft/fabric provider, in early 2026. It changes some of what follows. I’ll come back to it before the end. The short version: it would have spared us about half this article. The other half is still relevant — and so is the meta-point.)

The framing I’d want anyone reading this to keep in mind: this is what serious infrastructure work looks like when the tooling hasn’t caught up with the use case yet. The choice was always between writing what we have and not having the integration at all — and “not having the integration” wasn’t a choice the business was offering. Waiting a year for Microsoft to do the right thing would have meant a year of someone clicking through the Power BI dashboard for every new BI team, and forgetting to rotate credentials, and an outage, and an incident write-up.

The integration in one paragraph

The data warehouse is on Databricks. The reporting layer is Power BI. Several BI teams need their own credentialled connection from Power BI to a shared Databricks SQL warehouse, and we want each connection to be:

1. Created as code, with a deterministic name, owned by an Entra ID group representing the consuming team.
2. Authenticated via a Databricks service principal with read-only access to the right Unity Catalog schemas.
3. Wired up so the credentials rotate automatically — every day the Databricks SP gets a new OAuth2 secret, and Power BI’s stored credential gets updated to match.

That’s the requirement. None of it is exotic.

The first wall: no provider for Microsoft Fabric

Power BI’s connection objects live in the Microsoft Fabric API. Terraform providers existed for some of Microsoft’s surface — the AzureRM provider for Azure resources, the AzureAD provider for identity — but there was none for Fabric. A community provider wrapped a subset of the Power BI Service API, but it didn’t cover connection objects, which were the resource we actually needed to manage.

So the choice, in 2025, was:

• Option A: Write a Terraform provider for the Fabric API ourselves. The right answer in the abstract — but a multi-month project, a permanent maintenance burden, and the kind of thing Microsoft’s own engineers would eventually ship (and, in fact, eventually did — see the postscript at the end of this article).
• Option B: Find a way to drive the API from inside Terraform without writing a provider, and accept the cost.

We took Option B. I half-joke that I didn’t want to steal Microsoft’s engineers’ jobs; the more honest reason was that we needed the integration to ship in weeks, not quarters.

A short saga of three dead ends

“Option B” sounds like we marched straight at the Fabric API. We did not. There was a fortnight of trying not to.

First we tried doing the whole thing from the Databricks side, because Databricks already has a Power BI connector and a “publish to Power BI” workflow task. No Microsoft API to befriend, no provider to invent — just the trusty databricks/databricks provider doing what it does. The block was undocumented and drifted on every apply, which we tolerated, until we admitted it was solving the wrong problem entirely: it pushes from Databricks out, and the analysts wanted to pull from Power BI in.

Then we tried it harder. Same approach, with more for_each and better naming, in the time-honoured tradition of refusing to accept that Attempt 1 was wrong. Same drift, same wrong direction, plus an opaque permissions error in the actual data fetch that took a day to track down. Pulling on a broken thread does not unbreak it.

Then we pivoted to the Power BI side, but timidly — through a community Python wrapper, authenticating with Databricks Personal Access Tokens. It got one team working end-to-end, which felt like victory for about three days, until the wrapper turned out to cover the wrong half of Microsoft’s APIs and the PATs turned out to be the wrong primitive for service accounts at scale.

By the end of the second week we’d thrown all three away, deleted the wrapper, deleted the PATs, and were calling the Fabric API directly with requests and OAuth2 client credentials. That’s the script the rest of this article describes. It is unglamorous and it is exactly the script we should have written on day one — except, of course, that on day one we didn’t yet know that the three obvious things wouldn’t work.

The lesson, free of charge: the obvious path is often solving a different problem from the one in front of you. Dead ends are cheap if you notice them quickly; expensive if you spend a quarter on each.

The second wall: the API was half-documented

Even granting Option B, you’d have hoped the API itself was straightforward to drive. It wasn’t. The endpoints were documented; the payloads were documented in the optimistic sense of “an example exists somewhere, possibly”; the response shapes were documented even less. Most of what I learned about how the API actually wanted to be called, I learned by hitting it with curl and parsing the error messages.

The auth chain was the cleanest part:

AWS Secrets Manager
  └── Power BI service principal credentials (Azure AD client_id + secret)
        ↓ OAuth2 client-credentials flow against Azure AD
  └── access_token
        ↓ Bearer auth against api.fabric.microsoft.com
  └── Fabric API endpoints we actually want

That part worked. The interesting failures were downstream of “I have a token” — payloads the API silently accepted but didn’t actually honour, response shapes that changed between minor API versions, error messages that pointed you at the wrong field. The Azure-AD-SP-vs-Databricks-SP confusion described in the previous section was one such failure; it took a day to track down because the API gave no signal that anything was wrong until the data fetch itself failed in Power BI’s runtime, far from any Terraform plan.

The architecture we landed on: every BI team gets its own Databricks service principal, the SP’s OAuth2 client credentials are what get stored in Power BI’s connection, and the rotation pipeline rotates those credentials. Once the right credential lived in the right place, the data fetch worked first try. Two days, one commit (286b33a).

The horrible things, in detail

When you have no provider, you reach for Terraform’s escape hatches: null_resource (deprecated in spirit), terraform_data (the modern equivalent), the external data source, and the local-exec provisioner that runs an arbitrary command. We used all of them. None of it was pretty — and most of it is, at the time of writing, still running in production, because nothing has gone badly enough to justify the rewrite yet. So the snippets below are present-tense descriptions of code that was written in 2025 and has not yet been retired.

Horrible thing #1: a terraform_data that calls a Python script

resource "terraform_data" "pbi_connection" {
  for_each = local.bi_teams

  provisioner "local-exec" {
    command = <<-EOT
      python3 ${path.module}/scripts/manage_powerbi_connection.py create \
        --tenant_id          "${local.pbi_sp.tenant_id}" \
        --client_id          "${local.pbi_sp.client_id}" \
        --connection_name    "${local.connection_names[each.key]}" \
        --databricks_host    "${var.databricks_workspace_url}" \
        --databricks_path    "${data.databricks_sql_warehouse.warehouse.odbc_params[0].path}" \
        --databricks_oauth_id "${each.value.app_id}" \
      > "${path.module}/out/create_${each.key}.json"
    EOT
    environment = {
      PBI_CLIENT_SECRET = local.pbi_sp.client_secret
      DBX_OAUTH_SECRET  = databricks_service_principal_secret.team[each.key].secret
    }
  }
}

This block runs Python from inside Terraform, passes non-secret identifiers on the command line and secrets via the provisioner’s environment block (do not put --client_secret … on the command line — ps, process accounting, and Terraform’s debug log will all see it), and dumps the JSON response to a file because Terraform’s local-exec can’t capture stdout into the resource’s state. The Python script (manage_powerbi_connection.py, around 950 lines) does the work the provider would have done, had it existed at the time: building the right payload, calling the API, parsing the response, surfacing errors.

If the script fails, Terraform fails. If the script succeeds but Terraform’s state is corrupted, the connection still exists in Power BI but Terraform doesn’t know about it. State is now split across Terraform, the filesystem, the SSM Parameter Store (next horrible thing), and the actual Power BI tenant. The number of places “the truth” lives has gone from one to four.

Horrible thing #2: SSM Parameter Store as cross-destroy state

The Fabric API returns a connection ID on creation. We need that ID later — to update credentials, to share the connection with an Entra group, to (eventually) delete the connection. Terraform can’t naturally pass values from local-exec outputs into other resources because those outputs aren’t tracked in state.

The workaround: persist the connection ID into AWS SSM Parameter Store, then read it back via a data source.

resource "aws_ssm_parameter" "pbi_connection_id" {
  for_each = local.bi_teams
  name     = "/pbi/${each.key}/connection_id"
  type     = "SecureString"
  value    = data.external.read_create_output[each.key].result.connection_id

  depends_on = [terraform_data.pbi_connection]
}

data "external" "read_create_output" {
  for_each = local.bi_teams
  program = [
    "bash", "-c",
    "cat ${path.module}/out/create_${each.key}.json 2>/dev/null || echo '{}'"
  ]
  depends_on = [terraform_data.pbi_connection]
}

We’re storing infrastructure state outside Terraform’s state file, on purpose, because Terraform can’t carry it across runs without somewhere to put it. This is exactly the pattern Terraform was designed not to need. We did it anyway.

Horrible thing #3: rotation as a triggers_replace

Every day, a time_rotating resource flips and Databricks issues a new OAuth2 secret for each team’s service principal. The Power BI connection has to be told about the new secret, or it’ll start failing on the next data refresh.

resource "time_rotating" "secret_rotation" {
  for_each      = local.bi_teams
  rotation_days = 1
}

resource "databricks_service_principal_secret" "team" {
  for_each             = local.bi_teams
  service_principal_id = each.value.databricks_sp_id

  triggers = {
    rotated_at = time_rotating.secret_rotation[each.key].rotation_rfc3339
  }
}

resource "terraform_data" "update_pbi_credentials" {
  for_each = local.bi_teams

  provisioner "local-exec" {
    command = <<-EOT
      python3 ${path.module}/scripts/manage_powerbi_connection.py update \
        --connection_id "${data.aws_ssm_parameter.pbi_connection_id[each.key].value}" \
        --databricks_oauth_id     "${each.value.app_id}" \
        --databricks_oauth_secret "${databricks_service_principal_secret.team[each.key].secret}"
    EOT
  }

  triggers_replace = [
    databricks_service_principal_secret.team[each.key].id,
  ]
}

The triggers_replace ties this resource’s lifecycle to the secret’s identity. New secret means new resource means provisioner re-runs means Power BI gets the new credential. It works. It is also load-bearing on a chain of three resources and an external script, and any of them silently failing means a stale credential. A real provider would treat this as one connection-with-rotating-credential resource, atomically. We don’t have that, so the choreography is in Terraform.

Horrible thing #4 (the unsolved one): destroy-time

Creating a Power BI connection works. Updating it works. Deleting it on terraform destroy does not work.

Terraform’s destroy-time provisioner can only reference attributes of self — the resource being destroyed. We need the connection ID to delete the connection. The connection ID lives in SSM. SSM data sources don’t fire during destroy.

# This block does not work and is left here as a note to future me.
#
# resource "terraform_data" "pbi_connection" {
#   ...
#   provisioner "local-exec" {
#     when = destroy
#     command = <<-EOT
#       python3 .../manage_powerbi_connection.py delete \
#         --connection_id "${data.aws_ssm_parameter.pbi_connection_id[each.key].value}"
#     EOT
#   }
# }

The accepted solution, for now, is: when a team is decommissioned, run the Python script manually with the connection ID from SSM, then terraform apply to clean up the rest. It’s a TODO comment in the code. Commit d1dcfa5 (“prototype destroy implementation”) is where I tried and gave up.

If I were doing this again I’d probably solve it by storing the connection ID in a terraform_data resource’s output attribute as JSON-encoded state, which can be referenced via self.output.connection_id at destroy time. The reason we didn’t: the same output attribute can’t be repopulated on subsequent applies without re-creating the resource, which would re-create the Power BI connection, which would defeat the purpose. The provider gap shows up as a state-management gap. There isn’t a clean answer.

What this generalises to

The pattern — Terraform orchestrates an external script that calls an undocumented API, with state persisted in a sidecar — is uglier than people pretend. It’s also incredibly common in real infrastructure codebases. Anywhere a vendor ships a SaaS product before they ship a Terraform provider, you’ll find this shape: SaaS dashboards that have to be wired up at deploy time, automation done with whatever escape hatch the IaC tool offers.

The lesson I’d most want to carry into the next one of these:

1. Recognise the shape early. If the provider doesn’t exist, you’re not having a “Terraform problem” — you’re having a missing provider problem, and the only ways to solve it are (a) write the provider, or (b) bridge to the API yourself. Picking (b) consciously is much better than picking it accidentally.
2. Treat the bridge as a real piece of code. The Python script in this project is 950 lines of considered work. It validates inputs, surfaces meaningful errors, returns structured output, retries 429s and 503s with exponential backoff (Microsoft APIs throttle on no particular schedule, and a transient throttle should never fail a Terraform run), and behaves the way a Terraform provider would if there were one. If you write your bridge as a one-line shell command and a local-exec, it’ll bite you in production.
3. Document the gaps, loudly. The destroy-time problem is a known limitation. It’s commented in the code, called out in the README, and known to the team. Hidden gotchas are dangerous; acknowledged gotchas are operational reality.

Postscript: Microsoft did, eventually, ship the provider

Roughly a year after this code went live, Microsoft published a GA Terraform provider for Fabric — microsoft/fabric on the Registry, currently at v1.9.1 (April 2026). It exposes a generic fabric_connection resource that drives the same Fabric REST API surface this article wrestles with. The connection-creation path — the one our 950-line Python script existed to handle — is now a few lines of HCL. The half-documented API is, presumably, less half-documented from the inside than the outside, and the provider papers over the rougher edges with proper schema validation and clearer error messages.

So: was the work obsoleted? Partially. Specifically:

• Connection creation, update, and (importantly) destroy. All of it is now first-class. The “horrible thing #4” — the unsolved destroy-time problem — is solved by the simple expedient of having a real provider that tracks state. If I were starting today, the Python script would be gone and so would two of the four horrible things.
• What’s not solved: the rotation orchestration loop. terraform apply is fundamentally not the right tool for “every day, generate a new SP secret and patch the connection that consumes it.” That’s a scheduled job, not an apply. The provider gives you the building block (you can manage the connection’s credential as code); it doesn’t replace the cron-shaped thing that walks the rotation cycle. Whatever orchestrator you reach for — a Lambda, a GitHub Actions cron, a small operator — still has to exist. So triggers_replace and time_rotating may go away; the discipline of “something has to refresh credentials on a schedule and patch them in” doesn’t.

Net: a fresh implementation in 2026 is meaningfully cleaner — maybe a third of the line count, half the moving parts, and no destroy-time TODO. The integration would still need a rotation pipeline. The shape of the problem doesn’t go away just because one of its harder edges did.

What this lets me say about realism

Here is the lesson I’d most want to land if you’re reading this and weighing the same trade-off:

The provider that would have saved you 950 lines of Python existed nowhere a year ago, and exists today. If we had waited for it, five BI teams would have spent a year clicking through the dashboard, the credentials would have rotted, someone would have been paged at 3am over a stale token, and the team that needed the integration would have routed around us by doing it manually anyway. We didn’t get that year back by being patient.

The corollary: shipping the ugly thing is not a moral failure. Acknowledged ugly is operational reality. The Python script, the SSM-as-state pattern, the destroy-time TODO comment — all of these were the price of having the integration in production in 2025 instead of in 2026. The bill has now arrived, and it’s a refactor I’m happy to pay.

If you take one thing from this: the right answer to “the tooling doesn’t exist yet” is rarely “wait.” It’s “build the bridge, write it down, document the gaps, and quietly look forward to the day someone replaces it.” That day will come; in the meantime, the business needs the thing now.

A footnote on plausible-sounding fixes

A well-known LLM from a large company in Mountain View reviewed a draft and confidently proposed two fixes that turn out to be the exact dead ends we’d already walked into. Worth recording, because they sound so obvious you’ll probably reach for them too.

“Store the connection ID in terraform_data.input and reference it via self.input at destroy.” Already discussed at the end of Horrible thing #4: the moment the value re-evaluates on a later apply, the resource is replaced, the Power BI connection is destroyed and recreated, and the rotation pipeline panics. We have the abandoned branch.

“Use provisioner with triggers for in-place updates instead of triggers_replace.” Not a thing. terraform_data and null_resource only fire provisioners on create or destroy; both triggers and triggers_replace work by forcing a replacement, which is how the provisioner gets to run again. True in-place updates require a real provider — which Microsoft eventually shipped, see the postscript.

Treat confident reviewer suggestions — LLM or human — as hypotheses to verify, not findings to act on. “Obvious” and “tried” are not always the same set.

Was it worth it?

Five BI teams have working, automatically-rotating Power BI connections to Databricks. The configuration of all five lives in code, in one repo, version-controlled. New teams get added with a 5-line YAML entry. The credential rotation has run dozens of times without incident, for a year, while we waited for the provider to exist.

The cost was a couple of weeks of investigation, ~1300 lines of Terraform and Python combined, and a permanent open issue around destroy-time cleanup. Compared to “the platform team manually creates connections in the Power BI dashboard every time a new team needs one, and someone forgets to rotate credentials, and a downtime happens”, that’s a trade I’d make every time.

I’m glad Microsoft eventually shipped the provider. I’m gladder we didn’t wait for them.