Databricks DLT にて定義した Streaming table と Materialized view に対する Databricks SQL による REFRESH 操作の実施可否検証 

## 概要

Databricks DLT にて定義した Streaming table と Materialized view に対して、 Databricks SQL による REFRESH 操作の実施できるかどうかの検証結果を共有します。

2025年5月13日時点では、下記のエラーが発生しました。 DLT により定義した Streaming table と Materialized view に対しては Databricks 

> [STREAMING_TABLE_OPERATION_NOT_ALLOWED.REFRESH_DELTA_LIVE_TABLE] The operation REFRESH is not allowed: The operation does not apply to Streaming Tables created from Delta Live Tables, instead refresh the Streaming Table in Delta Live Tables. SQLSTATE: 42601

> Cannot REFRESH a Materialized View created from DLT, instead update the Materialized View by starting the DLT pipeline.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/b6a26e6c-d253-4f92-a4b3-913e5b9195f2.png)


## 事前準備

### 1. ノートブックに Databricks SQL をアタッチ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7687f1fd-2264-49f5-b491-bd5973b6e12a.png)


### 2. カレントカタログの設定

```sql
%sql
USE CATALOG manabian_test_01;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2ddbf400-e27e-4e9c-94de-2d7056c99843.png)

### 3. ソースとなるスキーマとテーブルを作成

```sql
%sql
CREATE SCHEMA IF NOT EXISTS dlt_source_01;
```

```sql
%sql
CREATE OR REPLACE TABLE dlt_source_01.table_01
AS
SELECT
  1 AS id,
  'John' AS string_col,
  CAST('2025-01-01' AS TIMESTAMP) AS ingest_ts
  ;
SELECT * FROM dlt_source_01.table_01;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/eb6019fc-dc77-4b6f-8e23-ecb6d2246dc8.png)

### 4. DLT パイプラインの作成と実行

```sql
from dlt import *
from pyspark.sql.functions import *


catalog = "manabian_test_01"

# table_01
table_01 = "table_01"
@dlt.table(
  name = table_01,
)
def customers_bronze_ingest_flow():
  return (
      spark.readStream.table(f"{catalog}.dlt_source_01.table_01")
  )


# mview_01 の処理
mview_01 = "mview_01"
@dlt.table(
  name = mview_01,
)
def customers_bronze_ingest_flow():
  return (
      spark.read.table(table_01)
  )

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/fe906585-1a89-48e0-91e8-d366751da5b5.png)

### 5. ソーステーブルにデータを追加

```sql
%sql
WITH src AS (
SELECT
  2 AS id,
  'Doe' AS string_col,
  CAST('2025-02-01' AS TIMESTAMP) AS ingest_ts

)
INSERT INTO dlt_source_01.table_01
SELECT * FROM src;

SELECT * FROM dlt_source_01.table_01;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9199db15-0bda-44d7-99da-a0d3c01fdbce.png)

## 検証コードと結果

### Streaming table に対する REFRESH 操作

エラーとなった。

```sql
%sql
REFRESH dlt_test_01.table_01;
```

> [STREAMING_TABLE_OPERATION_NOT_ALLOWED.REFRESH_DELTA_LIVE_TABLE] The operation REFRESH is not allowed: The operation does not apply to Streaming Tables created from Delta Live Tables, instead refresh the Streaming Table in Delta Live Tables. SQLSTATE: 42601

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/1e4cbc18-be91-4a72-87db-869fc882290e.png)


### Streaming table に対する REFRESH 操作

エラーとなった。

```sql
%sql
REFRESH MATERIALIZED VIEW dlt_test_01.mview_01;
```

> Cannot REFRESH a Materialized View created from DLT, instead update the Materialized View by starting the DLT pipeline.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/caaddaee-049f-4635-9758-f627501889b7.png)