can i deploy a metric view using DABs

[Areqio]

I am trying to deploy metric view using DABs but I cant find a resource for it so i ended up with a SQL file but is there a way to automatically run the SQL for it

[balajij8]

Hi Areqio, You can use a SQL file with Metric view definitions or Notebooks with Metric view definitions as the standard path. You can then wrap the SQL file or notebooks in a DABs job resource using a sql file task or notebook task.

You can have the target catalog or schema as input variables and plan it to be environment aware (dev, staging, or prod). You can pass the environment parameters from the DAB config directly into the notebook as widgets, read them and use EXECUTE IMMEDIATE or other options to dynamically construct and execute the statements for the correct environment.

More details here

[binlogreader]

@Areqio there is no metric view resource in Asset Bundles today. Your SQL file is the intended shape for anything the bundle doesn't model yet. The missing piece is a job that runs it, so deployment becomes deploy then run.

You can with just two scripts: a `databricks.yml` (or a resources file) and a job that executes your SQL on a warehouse and passes per-target values as parameters:

```yaml
variables:
warehouse_id:
catalog:
default: main
schema:
default: analytics

resources:
jobs:
metric_views_ddl:
name: deploy-metric-views-${bundle.target}
tasks:
- task_key: create_metric_view
sql_task:
file:
path: ./sql/metric_view.sql
warehouse_id: ${var.warehouse_id}
parameters:
catalog: ${var.catalog}
schema: ${var.schema}
```

And `sql/metric_view.sql`, as small as a metric view can be:

```sql
USE CATALOG IDENTIFIER(:catalog);
USE SCHEMA IDENTIFIER(:schema);

DECLARE OR REPLACE VARIABLE qry_str STRING;

SET VARIABLE qry_str = "
CREATE OR REPLACE VIEW metv_orders_minimal
WITH METRICS
LANGUAGE YAML
AS $$
version: 0.1

source: " || :catalog || "." || :schema || ".orders

dimensions:
- name: Order Date
expr: order_date

measures:
- name: Total Orders
expr: COUNT(1)
$$";

EXECUTE IMMEDIATE qry_str;
```

For testing:

```bash
databricks bundle deploy -t dev
databricks bundle run metric_views_ddl -t dev
```

Then confirm it behaves as a metric view:

```sql
SELECT `Order Date`, MEASURE(`Total Orders`) FROM metv_orders_minimal GROUP BY ALL;
```