Metadata-Version: 2.4
Name: dbt-molinia
Version: 0.1.0
Summary: dbt adapter for Molinia (DuckDB-dialect SQL warehouse)
License-Expression: Apache-2.0
Project-URL: Homepage, https://molinia.eu
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
License-File: NOTICE
Requires-Dist: dbt-core<2.0.0,>=1.7.0
Requires-Dist: requests>=2.31.0
Provides-Extra: dev
Requires-Dist: dbt-core<2.0.0,>=1.7.0; extra == "dev"
Requires-Dist: pytest>=7.4.0; extra == "dev"
Requires-Dist: pytest-mock>=3.12.0; extra == "dev"
Requires-Dist: jinja2>=3.1.3; extra == "dev"
Dynamic: license-file

# dbt-molinia

A [dbt](https://docs.getdbt.com/) adapter for [Molinia](https://molinia.eu) — a DuckDB-dialect managed data warehouse.

Molinia speaks standard DuckDB SQL, so this adapter reuses the dbt-duckdb
macro library (Apache-2.0) with targeted divergences documented below.

---

## Installation

```bash
pip install dbt-molinia
```

**This adapter runs on dbt v1** (`dbt-core` ≥ 1.7.0, < 2.0). dbt v2 — the Rust
rewrite that `pip install dbt` now gives you — reaches warehouses through ADBC
drivers compiled into its own binary and loads no Python adapters at all, so it
cannot use this one. Install into a virtual environment and check what you got:

```bash
dbt --version
```

It must report `dbt-core 1.x` alongside `molinia: <version>`. If it reports 2.x,
a v2 installation is earlier on your `PATH` and every command will fail on
`type: molinia`.

---

## Profile configuration

```yaml
# ~/.dbt/profiles.yml
my_molinia_project:
  target: dev
  outputs:
    dev:
      type: molinia
      host: https://app.molinia.eu              # console + API host (no trailing slash)
      org_id: org_0123456789abcdef01234567      # the organisation's public id (or its legacy integer id)
      token: rd_sa_<your-service-account-token> # service account token
      schema: main                              # DuckDB schema (default: main)
      warehouse_id: null                        # optional dedicated warehouse, numeric id
```

> **`host` is the app host, not the apex.** `molinia.eu` serves the marketing
> site only and answers every `/api/...` path with a 404. Production is
> `https://app.molinia.eu`.

> **Important:** `org_id` is the organisation's **public id** (`org_` followed by
> 24 hex characters), which the console shows under **Settings → General**. The
> legacy integer id (e.g. `123`) is still accepted. Anything else — including the
> DuckDB catalog name `org-123` or a slug — is answered with **404 Organization
> not found**, on purpose: an unknown identifier is indistinguishable from a
> missing organisation.

Obtain a service account token from **Settings → Service Accounts** in the
Molinia UI or via `POST /api/orgs/{orgId}/service-accounts` (org owner/admin only).

**A new service account has no privileges.** It can authenticate but every call
returns 403 until a role is granted to it. For dbt, the role needs at least:

| Privilege | Why |
| --- | --- |
| `api:query` | `POST /query/execute` — every model, seed and test |
| `table:select` | read statements |
| `table:create` | `CREATE TABLE AS` for materialised models |
| `table:insert`, `table:update`, `table:delete` | incremental and snapshot strategies |
| `table:drop` | `dbt run --full-refresh`, which drops before recreating |

Grant it from **Settings → Roles → Grants → Grant to service account**, or:

```bash
curl -X POST "$HOST/api/orgs/$ORG/roles/$ROLE_ID/service-account-grants" \
  -H "Authorization: Bearer $ADMIN_JWT" -H 'Content-Type: application/json' \
  -d '{"serviceAccountId": 42}'
```

A read-only account (`api:query` + `table:select`) works for `dbt docs`,
`dbt ls` and `dbt test` against pre-built models, but not for `dbt run`.

To **revoke** a key, call `PATCH /api/orgs/{orgId}/service-accounts/{id}/deactivate`
(there is no `/revoke` endpoint).

---

## Local development quickstart

```yaml
# ~/.dbt/profiles.yml
my_molinia_local:
  target: dev
  outputs:
    dev:
      type: molinia
      host: http://localhost:8080   # local Molinia server
      org_id: 1                     # numeric org ID of your local org
      token: rd_sa_<local-sa-token>
      schema: main
```

### Verify the connection

```bash
dbt debug
```

Expected output (truncated):

```
Connection:
  host: http://localhost:8080
  org_id: 1
  schema: main
  warehouse_id: None

Connection test: [OK connection ok]

All checks passed!
```

If `dbt debug` reports **404 Organization not found**, `org_id` is neither the
`org_…` public id nor the integer id: check it against **Settings → General**.
A **403** names the privilege the service account is missing.

### Run your models

```bash
dbt run          # compile + execute all models
dbt test         # run schema and data tests
dbt docs serve   # generate and open documentation
```

---

## Rate limits

Molinia throttles two ways, and dbt spends several requests per model, so both
are reachable during an ordinary run:

| Limit | Answer | What the adapter does |
| --- | --- | --- |
| 60 requests per minute per IP | `429` with a `Retry-After` header | waits that long, retries, up to 5 times |
| org-engine queries per minute | `429`, `code: org_engine_rate_limit`, `retryAfterSeconds` in the body | waits that long, retries, up to 5 times |
| daily org-engine compute budget | `429`, `code: org_engine_daily_budget` | fails immediately — waiting cannot clear it |

Nothing else is retried. A 5xx may have executed the statement already, and
re-sending a `CREATE` or `INSERT` that landed would corrupt the model.

If a run still hits the per-minute limit, lower `threads` in your profile, or
point the profile at a dedicated warehouse with `warehouse_id`.

---

## Common errors

| Message | Cause |
| --- | --- |
| `404 Organization not found` | `org_id` is neither the `org_…` public id nor the integer id |
| `403 … lacks 'table:create'` | the service account's role is missing a privilege (see the table above) |
| `400 … LAKE_ENABLED_ORGS` | the organisation has no lakehouse enabled, so it has nowhere durable to write. Reads and plain DDL work; anything that grows data (`CREATE TABLE AS`, `INSERT`, `MERGE`) is refused until an operator enables it |
| `Could not find adapter type molinia` | dbt v2 is running (see [Installation](#installation)) |

---

## Relation naming

Molinia DuckDB catalogs are named `org-{orgId}` (engine path) or
`warehouse-{warehouseId}` (dedicated pool path).  These names contain
hyphens which require special quoting in three-part DuckDB identifiers.

To avoid ambiguity, **dbt-molinia renders all relation identifiers as
two-part names**: `"schema"."identifier"`.  The catalog/database is
determined by the connection and is intentionally omitted from generated SQL.

| dbt field | Maps to | Example |
|---|---|---|
| `database` | DuckDB catalog (omitted from SQL) | `org-123` |
| `schema` | DuckDB schema | `analytics` |
| `identifier` | Table or view name | `orders` |

Generated SQL reference: `"analytics"."orders"` ✓  
(Not: `"org-123"."analytics"."orders"`)

---

## Supported SQL dialect macros

All macros dispatch via `adapter.dispatch` so user projects can override any
of them.

| dbt macro | Molinia / DuckDB implementation |
|---|---|
| `current_timestamp()` | `now()` |
| `dateadd(datepart, interval, from_date)` | `from_date + INTERVAL (interval) datepart` |
| `datediff(datepart, from, to)` | `datediff('datepart', from, to)` |
| `concat(fields)` | `concat(field1, field2, …)` |
| `hash(field)` | `md5(cast(field as varchar))` |
| `last_day(date, datepart)` | `last_day(date)` (month); computed boundary for quarter/year |
| `safe_cast(field, type)` | `TRY_CAST(field AS type)` |
| `snapshot_string_as_time(id)` | `CAST(id AS TIMESTAMP)` |
| `type_bigint()` | `BIGINT` |
| `type_boolean()` | `BOOLEAN` |
| `type_float()` | `DOUBLE` |
| `type_int()` | `INTEGER` |
| `type_numeric()` | `DECIMAL(28, 6)` |
| `type_string()` | `VARCHAR` |
| `type_timestamp()` | `TIMESTAMP` |

---

## `generate_schema_name`

```sql
-- Custom schema set in model config (+schema: reporting)?  Use it directly.
-- Otherwise fall back to target.schema from profiles.yml.
```

Unlike the dbt default, Molinia does **not** prefix the custom schema with the
target schema.  If you set `+schema: reporting` in `dbt_project.yml`, the model
lands in `reporting`, not `<target_schema>_reporting`.

---

## Divergences from dbt-duckdb

The following dbt-duckdb features are **not supported** in dbt-molinia.
Attempting to use them raises a `DbtRuntimeError` with an explanation.

| dbt-duckdb feature | Molinia status | Reason |
|---|---|---|
| `external_table` materialization | **NOT SUPPORTED** | No local filesystem access from dbt; use Molinia ingest API |
| `read_csv()` / `read_parquet()` path references | **NOT SUPPORTED** | Molinia manages storage via S3/ingestion API |
| `ATTACH DATABASE` | **NOT SUPPORTED** | Multi-org isolation is server-managed; use Data Sharing API |
| Extension install macros | **NOT SUPPORTED** | httpfs/azure managed by Molinia server |
| Python UDFs from dbt side | **NOT SUPPORTED** | UDFs are managed via the Molinia UDF API |
| `database` in 3-part relation | Maps to `org-{orgId}` catalog | DuckDB catalog naming — omitted from rendered SQL |

---

## Catalog introspection (`information_schema`)

dbt uses `information_schema` queries extensively for catalog commands (`dbt docs generate`,
`dbt test --store-failures`, source freshness checks).  The behaviour of DuckDB's
`information_schema` through the Molinia query API is documented here.

### Verified query patterns

```sql
-- List user-visible schemas (internal schemas excluded by the adapter)
SELECT schema_name
FROM information_schema.schemata
WHERE catalog_name = current_database()
  AND schema_name NOT IN ('information_schema', 'pg_catalog', 'temp');

-- List tables in a schema
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'main';        -- returns table_type = 'BASE TABLE' or 'VIEW'

-- Get columns for a table
SELECT column_name, data_type, character_maximum_length,
       numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_schema = 'main' AND table_name = 'my_model'
ORDER BY ordinal_position;

-- Check if a relation exists
SELECT count(*)
FROM information_schema.tables
WHERE table_schema = 'main' AND table_name = 'my_model';
```

### DuckDB internal schemas

DuckDB exposes `information_schema`, `pg_catalog`, and `temp` in
`information_schema.schemata` in addition to user-created schemas.  The
`molinia__list_schemas` macro filters these out so dbt's `list_schemas()`
only returns user-visible schemas like `main`, `analytics`, or any custom
schema created by your project.

### RLS temp views and `information_schema`

Molinia's RLS enforcement creates `TEMP VIEW` objects (e.g.
`CREATE OR REPLACE TEMP VIEW "orders" AS SELECT * FROM "org-123".main."orders" WHERE …`).
In DuckDB, temp objects live in the `temp` schema of the current session's
catalog.  Querying `information_schema.tables WHERE table_schema = 'main'`
always returns the **underlying base table** (`table_type = 'BASE TABLE'`),
NOT the temp view — even when an RLS view shadows it at query time.

Consequently:
- `dbt docs generate` sees accurate `table_type` values.
- `get_columns_in_relation` reads columns from the base-table entry; no
  special handling for RLS views is needed.
- Source freshness row-count checks query through the RLS view (correct —
  they should respect access policy), while catalog checks see the real table.

### DuckDB → dbt type mapping

| DuckDB `data_type` | dbt `dtype` |
|---|---|
| `VARCHAR`, `TEXT` | string |
| `INTEGER`, `INT4` | int |
| `BIGINT`, `INT8` | bigint |
| `DOUBLE`, `FLOAT8` | float |
| `DECIMAL(p,s)` | numeric |
| `BOOLEAN` | boolean |
| `TIMESTAMP`, `TIMESTAMPTZ` | timestamp |
| `DATE` | date |
| `BLOB` | binary |

Types not in the table are passed through verbatim.

---

## Development

```bash
pip install -e ".[dev]"
pytest tests/
```

---

## License

Apache-2.0 — see [LICENSE](LICENSE) and [NOTICE](NOTICE).

The SQL macro implementations in `dbt/include/molinia/macros/` are derived from
[dbt-duckdb](https://github.com/duckdb/dbt-duckdb), which is licensed under the
Apache License 2.0. Earlier revisions of this file called that project
MIT-licensed; it never was.
