MARIADB

PROFILES.YML — A COMPLETE WORKING EXAMPLE

The common case: TCP, username and password. Put this in ~/.dbt/profiles.yml — DVT reads the same profiles file dbt does:

my_project:
  target: mariadb_dev
  outputs:
    mariadb_dev:
      type: mariadb
      host: db.internal.example.com
      port: 3306                # optional — 3306 is the driver default
      user: analytics
      password: "{{ env_var('MARIADB_PASSWORD') }}"
      schema: analytics_db      # like MySQL: the schema IS the database
      threads: 4

No database: key — on the MySQL family, schema: alone names the database DVT builds in (the rule is spelled out below). Then let DVT finish the setup:

$ dvt sync     # reads the profile, installs the MariaDB adapter + driver
$ dvt debug    # proves the connection actually works, both layers

dvt sync sees type: mariadb and installs the adapter and its driver (mysql-connector-python— MariaDB speaks MySQL's protocol) into the one DVT environment.

OTHER WAYS TO CONNECT

Unix socket — for a MariaDB server on the same machine, point at the socket file and leave host out (if a host is present, it wins and the socket is ignored):

    mariadb_local:
      type: mariadb
      unix_socket: /var/run/mysqld/mysqld.sock
      user: analytics
      password: "{{ env_var('MARIADB_PASSWORD') }}"
      schema: analytics_db

Plain-TCP servers without TLS — if the handshake trips over a half-configured TLS setup, ssl_disabled switches encryption off for this connection explicitly:

    mariadb_dev:
      type: mariadb
      host: db.internal.example.com
      user: analytics
      password: "{{ env_var('MARIADB_PASSWORD') }}"
      schema: analytics_db
      ssl_disabled: true        # explicit: no TLS on this connection

TLS client certificates (mutual TLS) — if your server authenticates clients with certificates, point the profile at the key material. The same three fields feed both lanes, and paths may start with ~ — DVT expands it. Ordinary server-side TLS still negotiates on its own when the server offers it.

    mariadb_secure:
      type: mariadb
      host: db.internal.example.com
      user: analytics
      password: "{{ env_var('MARIADB_PASSWORD') }}"
      schema: analytics_db
      ssl_ca: ~/.mysql/ca.pem
      ssl_cert: ~/.mysql/client-cert.pem
      ssl_key: ~/.mysql/client-key.pem

ONE NAMESPACE: SCHEMA IS THE DATABASE

MariaDB inherits MySQL's flat namespace — CREATE SCHEMA is a synonym for CREATE DATABASE. So in the profile, schema: names the database you build in, and database: may be omitted (recommended) or set to exactly the same value. Anything else is refused before a query runs:

    schema: analytics_db
    database: other_db
On MariaDB, database must be omitted or have the same value as schema.

Sources follow the same law: declare them with schema: only — a source carrying a different database: is a hard parse error on this family, legal as it may be elsewhere. Reading across two databases on one server needs no configuration: other_db.some_table is ordinary SQL here.

PURE-PYTHON DRIVER BY DEFAULT (USE_PURE)

DVT connects to MariaDB with the driver's pure-Python implementation — use_pure: trueis the default. The reason: the driver's C extension has a crash class where a failed connect can segfault, and a segfault takes the whole interpreter with it — no traceback, nothing to catch, no retry possible. The pure implementation turns driver failures back into ordinary, catchable errors.

And speed isn't the price you'd guess: bulk rows ride DVT's data-movement layer, so this connection carries only DDL, catalog reads and small statements. use_pure: false opts back into the C extension if you need it.

INCREMENTAL MODELS — NO MERGE, AND THAT'S FINE

Like MySQL, MariaDB has no MERGEstatement, and the adapter doesn't pretend otherwise. An incremental model with a unique_key runs as delete+insert— rows matching incoming keys are deleted, then the new rows are inserted. Without a key it's a plain append. There's no incremental_strategy to choose — the key decides. Nothing polices the config either: the materialization never reads incremental_strategy, so a strategy written on a MariaDB model is silently ignored rather than refused, and there is no error text for a wrong one.

{{ config(
    materialized='incremental',
    unique_key='session_id'    -- present: delete+insert; absent: append
) }}

select session_id, user_id, last_seen_at
from {{ source('operational_db', 'sessions') }}
{% if is_incremental() %}
where last_seen_at > (select max(last_seen_at) from {{ this }})
{% endif %}

The federated lane is the one place the strategy matters. When MariaDB is the target of an f_incremental model, DVT computes the model and lands the rows through its data-movement layer, so the strategy picks a load mode rather than compiling into MariaDB SQL. Three names are understood there — append, merge and delete+insert — and merge and delete+insert are the same operation, a key-based upsert on unique_key. insert_overwrite and microbatch are refused by name with DVT025: "has no Sling equivalent — f_incremental supports append, merge and delete+insert". Either upsert without a unique_key warns and falls back to a full refresh rather than failing, as does append without a watermark_column.

The keyless-watermark shape — an f_incremental carrying a watermark_column and no unique_key — is admitted only on the engines DVT has measured end to end: postgres, redshift, mysql, duckdb, sqlite, sqlserver, oracle, clickhouse and trino. MariaDB is on that list: the connection mapper resolves a mariadb profile through the MySQL mapper, so the engine key DVT matches against is mysql. A keyless f_incrementallanding here takes the watermark lane, with the watermark column as Sling's update key. --full-refresh rebuilds via rename-to-backup, build, drop-backup-last.

USING IT LIVE: MARIADB AS A FEDERATION SOURCE

The same profile entry doubles as a live connection: name it from a source's meta.connection and its tables can join anything else your project reaches. Here MariaDB sessions meet a Snowflake accounts table:

# models/sources.yml
sources:
  - name: operational_db
    meta:
      connection: mariadb_dev   # the profiles.yml output above
    schema: analytics_db
    tables:
      - name: sessions

-- models/marts/session_accounts.sql
{{ config(materialized='f_table') }}

select s.session_id, s.user_id, a.plan_tier
from {{ source('operational_db', 'sessions') }} s
join {{ source('sf_billing', 'accounts') }} a
  on s.user_id = a.user_id

When rows flow into MariaDB, loads stage through a temp table — no external staging area, no privileges beyond ordinary read/write. In federation, MariaDB rides the MySQL dialect: DVT renders backtick-quoted, MySQL-shaped SQL when it pushes work here.

QUOTING, CASE, AND OTHER ENGINE MANNERS

Backticks quote identifiers; "double quotes"are string literals by default, not identifiers. Keep names lower-case and unquoted and the dialect never surprises you — table-name case sensitivity follows the server's filesystem via lower_case_table_names. Catalog support is full through information_schema: dvt docs generate, dvt metadata, profiling and dvt generate-sources all see your columns, types and constraints.

Being a current engine, MariaDB runs CTEs, window functions and ephemeral models without ceremony — the portability notes on the mysql5 page exist precisely so the rest of the family can read like this one.

LOCAL DEV IN ONE LINE

A scratch MariaDB to try this page against:

$ docker run -d --name dvt-mariadb \
    -e MARIADB_ROOT_PASSWORD=devpass -e MARIADB_DATABASE=analytics_db \
    -p 3306:3306 mariadb:11

Point the profile at host: 127.0.0.1, user: root, schema: analytics_db, and run dvt debug.

REFERENCE — EVERY PROFILE FIELD

Everything the type: mariadb profile entry reads. Aliases land on the same field — write either spelling, not both.