dbt-mysql model builds and data tests

Versions and prerequisites

Reference versions are dbt-core 1.7.20 and dbt-mysql 1.7.0. This pinned combination reproduces historical validation; dbt-core 1.7 no longer receives regular maintenance. Select maintained production versions and requalify adapter/SQL compatibility. Validation ran real table models and data tests with dbt build, rather than parse alone. It did not certify every incremental materialization, delete propagation or retry semantic. Complete the fixtures, install dbt-core==1.7.20 dbt-mysql==1.7.0 and create the following files in a dedicated directory. Keep this adapter’s server/username keys rather than using another adapter’s host/user.

Project and profile

Save dbt_project.yml. Table materialization rebuilds this project’s output models: use a dedicated database and model names, rather than targeting business tables.

name: lake_demo_project
version: '1.0'
config-version: 2
profile: matrixone_demo
model-paths: ['models']
test-paths: ['tests']
models:
  lake_demo_project:
    +materialized: table

Save profiles.yml in the same directory and select it with --profiles-dir .. Keep the password as an environment reference. This profile is for the isolated local connection. For remote TLS, use parameters supported by your selected dbt-mysql release and qualify them separately rather than copying another adapter’s SSL fields.

matrixone_demo:
  target: mo
  outputs:
    mo:
      type: mysql
      server: "{{ env_var('MO_HOST') }}"
      port: "{{ env_var('MO_PORT') | int }}"
      username: "{{ env_var('MO_USER') }}"
      password: "{{ env_var('MO_PASSWORD') }}"
      schema: "{{ env_var('MO_DATABASE') }}"
      charset: utf8mb4
      collation: utf8mb4_bin
      threads: 2

Model and acceptance

Save models/lake_projection.sql. Explicit string representations for decimal and binary prevent display tools from silently converting them to float. These are explicit mappings, not validation of default inference.

SELECT id, CAST(amount AS CHAR) AS amount_text, HEX(payload) AS payload_hex,
       CONCAT(label, '→dbt') AS label, rev
FROM lake_demo
WHERE id IN (2, 9007199254740993)

Save tests/lake_projection_keys.sql. A dbt data test passes when it returns zero rows; this checks duplicate and NULL keys. Then verify amount_text, payload_hex and Unicode labels through a fresh connection. Key tests do not replace all-field fidelity checks.

SELECT id
FROM {{ ref('lake_projection') }}
GROUP BY id
HAVING COUNT(*) <> 1 OR id IS NULL
dbt debug --profiles-dir .
dbt build --profiles-dir . --select lake_projection
# Retain target/run_results.json and target/manifest.json.

Incremental, delete, schema and recovery

Start from a full table model and qualify each incremental strategy, unique_key, generated SQL and schema-change policy separately. A source DELETE does not automatically remove a destination row through is_incremental(): use snapshots, explicit delete events or a qualified merge. Renames and narrowing need checks beyond added columns.

After interruption, inspect intermediate tables, destination tables and run_results before rebuilding or resuming. A successful model does not make the entire DAG atomic. Avoid same-name staging objects for parallel models in a shared database. Related historical generated SQL reports are cross-database correlated DELETE #29876 and reflected BOOL DDL #29879. Check released fixes before choosing workarounds; these probes do not imply the whole dbt tool is unsupported.