Table materialization (V2) leads to unavailability of data

未关闭
  • 哪里不对？
#1,102 14 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 6 天内回复

还没有人认领这个 Issue。

评估

难度
5/5
预计耗时
一周以上
新手友好度
42/100
Issue 类型
缺陷
描述清晰度
基本清楚
活跃度
冷清
技术栈
python, sql
领域
databases

调研方向

从 docs/table_flow.md 开始，使用 toto.sql 模型、toto.yml 契约和两个 dbt run 命令重现该故障。将表物化流程与提到的 insert_overwrite 策略进行比较；完成的标准是，失败的受约束运行不会删除有效的现有数据，并且表仍然可用。

由索引模型根据 Issue 内容生成。

描述

bug Stale
Describe the bug

TLDR; stop using create or replace and then insert when table when table already exists (with valid data)

Two problems since materialization V2 for "table":

  • The table materialization does 2 steps (create or replace + insert) that can lead to an unavailability of the data
  • Constraints can be applied before hands (thanks a lot!) but the problem is that if new data arrive violating the said constraints afterward, we recreate the table first, hence removing all data.

The problem is that Delta is not handling transactions across multiple commands... One way to do overcome the second problem would be to add a clause for putting back data using RESTORE but it leaves the first problem.

Then, I thought of using the insert_overwrite incremental strategy that would replace all data in a single transaction, hence not removing existing valid data in a violation constraint scenario but the strategy isn't complete yet (not possible to add columns if I read right) and unavailable for SQL warehouse (even though I don't understand why because here would be a valid case for overwriting completely the table, maybe should have been only a warning in the logs?).

I think using insert_overwrite (without partition predicate) in the table materialization when the relation already exists in the database would be an idea but this may lead to complications (what if a column type changes ?). Happy to discuss !

Steps To Reproduce

Create a model toto.sql :

{{
  config(
    materialized = 'table',
    )
}}

{% if load_relation(this) %} {# meaning it exists in the database #}
  select null as name
  union all
  select 'toto' as name
{% else %} {# meaning it does not exist in the database #}
  select 'toto' as name
{% endif %}

And its corresponding toto.yml

models:
  - name: toto
    config:
      contract:
        enforced: true
    columns:
      - name: name
        data_type: string
        constraints:
          - type: not_null

Then run :
dbt run -s toto --> Succeed + select * from my_catalog.my_schema.toto returns 1 row
dbt run -s toto --> Fails + select * from my_catalog.my_schema.toto returns 0 row

Expected behavior

I want my valid data not to be removed and a 0 downtime of my table.

System information

The output of dbt --version:

Core:
  - installed: 1.10.2
  - latest:    1.10.5 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - databricks: 1.10.4 - Up to date!
  - spark:      1.9.2  - Update available!

  At least one plugin is out of date with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

The operating system you're using:
Mac OS

The output of python --version:
Python 3.13.5

Additional context

Thanks everyone for all the good work !! 🙏 I would be glad to contribute once we decide how to handle this problem ! Don't hesitate to reach out, we can even schedule a call if you have time for this.

主要语言
Python
星标
391
派生
231
平均合并
9 天 7 小时
30 天内合并 PR
29

环境准备

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

databricks/dbt-databricks 的其他 Issue

查看 databricks/dbt-databricks 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。