Teradata run_result.json has rows_affected = 0 always when data are inserted into target table, and code shows success instead of insert or update

[cindyzh-ibm]

Describe the bug

I have a flow using dbt to insert data to target teradata table, the run_results.json rows_affected = 0 always for insert.
In addition, the code = SUCCESS, which i cannot tell it is insert or update.

{
  "metadata": {
    "dbt_schema_version": "https://schemas.getdbt.com/dbt/run-results/v4.json",
    "dbt_version": "1.4.99",
    "generated_at": "2024-02-21T19:01:57.345194Z",
    "invocation_id": "bc98fe82-eba7-4ea4-949b-4d379f371f82",
    "env": {
      
    }
  },
  "results": [
    {
      "status": "success",
      "timing": [
        {
          "name": "compile",
          "started_at": "2024-02-21T19:01:53.009946Z",
          "completed_at": "2024-02-21T19:01:53.021875Z"
        },
        {
          "name": "execute",
          "started_at": "2024-02-21T19:01:53.026227Z",
          "completed_at": "2024-02-21T19:01:54.870080Z"
        }
      ],
      "thread_id": "Thread-1 (worker)",
      "execution_time": 1.9478518962860107,
      "adapter_response": {
        "_message": "OK",
        "code": "SUCCESS",
        "rows_affected": 0
      },
      "message": "OK",
      "failures": null,
      "unique_id": "model.DBT_RUN_MATRIX_TEST_0.RUN_MATRIX_TGT_1"
    },
    {
      "status": "success",
      "timing": [
        {
          "name": "compile",
          "started_at": "2024-02-21T19:01:54.971190Z",
          "completed_at": "2024-02-21T19:01:54.980113Z"
        },
        {
          "name": "execute",
          "started_at": "2024-02-21T19:01:54.984741Z",
          "completed_at": "2024-02-21T19:01:56.808962Z"
        }
      ],
      "thread_id": "Thread-1 (worker)",
      "execution_time": 1.9312188625335693,
      "adapter_response": {
        "_message": "OK",
        "code": "SUCCESS",
        "rows_affected": 2
      },
      "message": "OK",
      "failures": null,
      "unique_id": "model.DBT_RUN_MATRIX_TEST_0.RUN_MATRIX_TGT_2"
    }
  ],
  "elapsed_time": 5.979313611984253,
  "args": {
    
  }
}

Steps To Reproduce

As long as you have teradata as target, using dbt run, and get run_results.json. The issue is there.

Expected behavior

I expect code = INSERT, UPDATE, or DELETE instead of SUCCESS or FAIL
In addition, I expect rows_affected = rows either insert to target db, update in target db, or delete from target db.

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

The output of dbt --version:

<output goes here>

The operating system you're using:

The output of python --version:

Additional context

Add any other context about the problem here.

[cindyzh-ibm]

More details

1,

sh-4.4$ dbt --version
15:41:36  oracle adapter: Running in cx mode
15:41:36  oracle adapter: [WARNING]: cx_oracle is no longer maintained, use python-oracledb

To switch to python-oracledb set the environment variable ORA_PYTHON_DRIVER_TYPE=thin 

Read the guideline here: https://docs.getdbt.com/reference/warehouse-setups/oracle-setup#configure-the-python-driver-mode

Documentation for python-oracledb can be found here: https://oracle.github.io/python-oracledb/
Core:
  - installed: 1.4.99
  - latest:    1.7.9  - 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:
  - bigquery:  1.4.1  - Update available!
  - oracle:    1.4.8  - Update available!
  - postgres:  1.4.99 - Update available!
  - redshift:  1.4.1  - Update available!
  - snowflake: 1.4.5  - Update available!
  - teradata:  1.4.99 - Update available!

2, We don't have "target" relation returned in the end.

3, Here is the SQL for write mode = insert, table action = drop table then create the new table then insert.
it's a dbt materialization, the table will be recreated

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

SELECT "ID", "NAME", "HIRE_DATE", "SALARY"
FROM (SELECT "ID", "NAME", "HIRE_DATE", "SALARY"
        FROM "TM_DS_DB_1"."RUN_MATRIX_SRC") AS "t"
WHERE "ID" <= 3

4, Here is the SQL for write mode = update
It is custom materialization

{{
  config(
    materialized='datastage'
    
    , write_mode='update'
    , key_columns=['ID']
    
    
    , force_commit='false'
    
    , use_adapter_drop_statement='false'
  )
}}

SELECT "ID", "NAME", "HIRE_DATE", "SALARY"
FROM (SELECT "ID", "NAME", "HIRE_DATE", "SALARY"
        FROM "TM_DS_DB_1"."RUN_MATRIX_SRC") AS "t"
WHERE "ID" >= 4 AND "ID" <= 8

[sunilkmallam]

We're looking into this, @cindyzh-ibm. We'll share an update as soon as it's fixed.

FYI @VarunSharma15

[Daniel-Itzul]

Looking at this just as another user.
I feel this is caused by the fact that dbt and its artifacts are designed to work in a certain way, around the following materializations, which are rebuild by default every time dbt runs:

• View,
• Table,
• and incremental, which perform inserts on an existing table according to certain predefined conditions defined by the incremental strategies.
  Not sure if the artifacts are designed to support custom materializations that perform adhoc inserts to tables outside of a dbt DAG. It looks to me that in the case of your custom materializations there is no source or ref statement that will allow dbt to build a DAG.
  In my case the artifact run_result.json does reflect the rows affected in an incremental materialization.

{
  "metadata": {
    "dbt_schema_version": "https://schemas.getdbt.com/dbt/run-results/v5.json",
    "dbt_version": "1.7.4",
    "generated_at": "2024-03-07T09:30:16.129149Z",
    "invocation_id": "d63d4efb-25b5-4468-b8cd-91442b783502",
    "env": {}
  },
  "results": [
    {
      "status": "success",
      "timing": [
        {
          "name": "compile",
          "started_at": "2024-03-07T09:30:09.455345Z",
          "completed_at": "2024-03-07T09:30:09.462441Z"
        },
        {
          "name": "execute",
          "started_at": "2024-03-07T09:30:09.462441Z",
          "completed_at": "2024-03-07T09:30:15.952677Z"
        }
      ],
      "thread_id": "Thread-1 (worker)",
      "execution_time": 6.660082817077637,
      "adapter_response": {
        "_message": "OK",
        "code": "SUCCESS",
        "rows_affected": 1000
      },
      "message": "OK",
      "failures": null,
      "unique_id": "model.teddy_retailers_contract.stg_customers",
      "compiled": true,
      "compiled_code": "\n\nwith source as (\n\n    select * from \"teddy_retailers\".\"source_customers\"\n\n),\n\nrenamed as (\n\n    select\n        id as customer_id,\n        name as first_name,\n        surname as last_name,\n        email\n    \n    from source\n\n)\n\nselect * from renamed",
      "relation_name": "\"teddy_retailers\".\"stg_customers\""
    }
  ],
  "elapsed_time": 12.77868103981018,
  "args": {
    "static_parser": true,
    "select": [
      "stg_customers"
    ],
    "favor_state": false,
    "strict_mode": false,
    "introspect": true,
    "project_dir": "C:\\Users\\jh255095\\development\\demos\\teddy-retailers-dbt-temporal-tables",
    "send_anonymous_usage_stats": true,
    "show_resource_report": false,
    "write_json": true,
    "use_colors_file": true,
    "indirect_selection": "eager",
    "print": true,
    "quiet": false,
    "exclude": [],
    "profiles_dir": "C:\\Users\\jh255095\\.dbt",
    "log_format_file": "debug",
    "cache_selected_only": false,
    "enable_legacy_logger": false,
    "defer": false,
    "log_level": "info",
    "log_level_file": "debug",
    "partial_parse": true,
    "partial_parse_file_diff": true,
    "printer_width": 80,
    "log_file_max_bytes": 10485760,
    "which": "run",
    "populate_cache": true,
    "log_path": "C:\\Users\\jh255095\\development\\demos\\teddy-retailers-dbt-temporal-tables\\logs",
    "version_check": true,
    "warn_error_options": {
      "include": [],
      "exclude": []
    },
    "log_format": "default",
    "use_colors": true,
    "vars": {},
    "invocation_command": "dbt run --select stg_customers",
    "macro_debugging": false
  }
}

[Daniel-Itzul]

Having said that @cindyzh-ibm, if you provide us more insights in the use-case on the dbt-teradata slack channel. Maybe we could figure out a way to help you reach your desired outcome with a more traditional dbt structured project, that might generate the artifacts you are looking for.
https://getdbt.slack.com/archives/C027B6BHMT3

[cindyzh-ibm]

Thanks @Daniel-Itzul

1, The code in the run_results.json is SUCCESS. We would like to have either INSERT, UPDATE or DELETE, etc.

2, I attach a zip file which include our model, project, log. Hopefully it helps for you to debug.
One model is insert, one model is update.

files.zip

Note

• INSERT in our case uses dbt materialization: materialized='table'. And run_result.json doesn't give correct number of rows inserted. 4 is expected but 0 is returned.
• UPDATE in our case uses custom materialization: materialized='datastage'. run_result.json returns the right value for number of rows updated.

fyi @sunilkmallam @VarunSharma15