SQL and BI

Databricks SQL and BI

guideazure2 min read4 days old

Design the SQL layer as a governed product surface, not as a pile of dashboards attached to an unowned warehouse. The semantic definition, data permissions, compute policy, refresh behavior, and business owner all need a home.

Surface map#

Need Start with Guardrail
Interactive SQL and BI connectivity SQL warehouse Size, autoscaling, timeout, tags or budget policy, and owner
Governed reusable business metrics Curated views or metric views One definition, reviewed dimensions, tests, and lineage
Managed internal reporting AI/BI dashboards Certified source, refresh owner, subscriptions, and access review
Natural-language exploration Genie Curated data, instructions, representative questions, and answer evaluation
External BI tool Native connector to a SQL warehouse SSO or service principal, network path, query limits, and import/direct mode

Production baseline#

  1. Publish a small, documented semantic layer instead of exposing every bronze and silver table.
  2. Grant access to groups at the catalog or schema boundary and narrow it only where required.
  3. Separate interactive, scheduled, and heavy transformation workloads when one can starve the others or make attribution ambiguous.
  4. Set a warehouse owner, cost attribution, idle timeout, scaling bounds, and query monitoring.
  5. Test dashboards and semantic definitions as code where possible: schema, row counts, key metrics, freshness, and permission expectations.
  6. Evaluate Genie with real business questions and known answers. Treat confident unsupported answers as failures, not as a user-training problem.
  7. Review sharing, export, download, and subscription paths; read access is not the only egress.

Review questions#

  • Which definition is authoritative when two dashboards calculate the same metric differently?
  • Can every dashboard tile be traced to a governed object and an accountable owner?
  • Which workload pays for an expensive query, and what prevents accidental unbounded scans?
  • Does the connection preserve the end user's identity, or does everyone inherit one service principal's access?
  • What alerts when a dashboard is fresh but wrong because an upstream contract changed?

Official sources#

Referenced by