**Where the metadata lives:**

- `models/**/*.sql` → transformation logic
- `models/**/schema.yml` → tests, descriptions, sometimes sources/relationships
- `models/**/sources.yml` → source declarations
- `macros/*.sql` → reusable Jinja macros

## Typical SQLMesh project

**Where the metadata lives:**

- `models/*.sql` → `MODEL(...)` header + SELECT body
- `macros/*.sql` → macros; audits are referenced in headers

> **_Takeaway:_** _dbt favors_ **_YAML + Jinja_** _around SQL files; SQLMesh favors_ **_SQL + header DSL_**_. Either way, files are the unit of work._

## 3) What “model” means — and why naming hurts

In both tools, a **model** is _a transformation node_. That’s pragmatic for engineers, but it blurs the line for modelers:

- A dbt model can be a **view, table, CTE, or incremental table** — the _name_ “model” doesn’t reveal its logical role.
- Logical intent (is this a **Source** entity? **Intermediate** integration? **Delivery** exposure?) is implicit in folders and naming conventions.

**Recommendation:** keep the engines’ terminology internally, but **present and store your source‑of‑truth in Entity‑First terms**:

- **Entity** (name + subject area) with **role** (Source/Intermediate/Delivery)
- **Pattern** (Core/Characteristic/Associative/Reference)
- **Step type** (derive/filter/aggregate/union/translate)
- **Schema** (columns, PK/FKs/constraints) and **relationships**

Then **generate** dbt or SQLMesh files from that metadata. The engines keep “models,” the business sees **entities**.

## 4) Mapping Entity‑First → dbt

> _Goal: Emit dbt‑ready SQL + YAML while preserving business semantics._

**Entity YAML (source of truth)** → generate:

- `models/<layer>/<subject_area>/<entity>.sql`
- Uses `ref()`/`source()` for lineage
- Materialization set via `{{ config(materialized='...') }}` or project config
- `models/<layer>/<subject_area>/schema.yml`
- Column descriptions, PK/UK/NOT NULL tests, relationships
- Exposure definitions where applicable
- Optional `macros/*.sql` for **translate** / **derive** functions

**Layering convention** (map your roles):

**Examples** (shortened):

## 5) Mapping Entity‑First → SQLMesh

**Entity YAML** → generate **one file per model** with a `MODEL(...)` header:

- Use `kind` to express materialization.
- Attach audits for constraints.
- Macros implement reusable transformations (e.g., `translate_code(domain, expr)`).

**Note on lineage:** SQLMesh parses SQL for dependencies; explicit `ref()` keeps things clear and portable.

## 6) Representing step types (derive/filter/aggregate/union/translate)

Your Entity‑First metadata should **constrain** what’s allowed per step and drive generation:

- **derive** → column‑level mappings/expressions, joins via relationships
- **filter** → boolean predicate
- **aggregate** → `group_by`, aggregation functions, post‑derivations, PK for the aggregated grain
- **union** → list of aligned inputs
- **translate** → code/label lookups (reference entities) or macro calls

This yields **predictable SQL** that’s easier to audit and test.

## 7) Tests, DQ, and profiling — auto‑generated

From the entity metadata:

- **PK/UK/NOT NULL/FK** → dbt tests or SQLMesh audits
- **Row‑level quality** → templated queries emitted into `/tests/dq/`
- **Profiling** → simple selects, top‑N, null ratios for sources & intermediates

Keep these generated; hand‑written DQ lives in a separate `tests/` area.

## 8) Naming improvements (what we wish dbt had out‑of‑the‑box)

If dbt started today with a modeling lens, you might see:

But we don’t need dbt to change — we can **generate** this into classic dbt structure at build time.

## 9) Migration & adoption checklist

1. **Define the metamodel** (Entities, Roles, Patterns, Steps, Relationships).
2. **Author entity YAMLs** for a small subject area.
3. **Generate dbt/SQLMesh** models + tests; commit to Git.
4. **Run** and compare outputs to existing hand‑written SQL.
5. **Add diagram generation** (draw.io/Mermaid) from entity metadata.
6. **Train the team** on naming & layering rules; enforce with CI validation.
7. **Iterate**: add SCD2, incremental patterns, platform‑specific optimizations via macros.

## 10) Where alternative frameworks fit

- **Dagster (assets)** — asset‑centric DAG aligns with “entity” thinking.
- **Semantic layers (LookML/MetricFlow)** — don’t replace transforms, but inform entity & relationship naming.
- **Orchestrators (Airflow/Prefect)** — schedule & ops; pair with your generator output.
- **Catalog/Lineage (OpenMetadata, DataHub)** — ingest dbt/SQLMesh artifacts and your entity metadata for discovery.

## 11) Tying back to the business‑friendly series

This technical guide sits directly under the business vision introduced in [**A1 — Why We Need a Business‑Friendly Data Transformation Model**](https://medium.com/business-friendly-data-mapping/why-we-need-a-business-friendly-data-transformation-model-63ef3f9579c8). In short:

- **Entities** carry **roles** (Source / Intermediate / Delivery) and **patterns** (Core / Characteristic / Associative / Reference).
- **Step types** (derive, filter, aggregate, union, translate) make intent explicit and **auditable**.
- Everything is modeled in **Git + VSCode**, with diagrams and docs **generated** from metadata and always current.

> _For additional context from my Medium archive:_
> 
> [**Business‑Oriented Data Modeling — Unlock Your Business’s True Potential**](https://medium.com/@jacovanderlaan/unlock-your-businesss-true-potential-the-power-of-business-oriented-data-modeling-651a639c40e5)
> 
> [**Business‑Friendly Mapping Meets dbt — Virtualizing Logical Building Blocks into SQL**](https://medium.com/p/1b7d5775a8f6)

## What’s next (B2 & A2)

- **B2 (Technical):** _Designing an Entity‑First Transformation Framework: YAML → SQL Generator_ — schema, validation, CI checks, and a runnable mini‑project.
- **A2 (Functional):** _From Sources to Delivery: Entities, Subject Areas & Transformation Steps_ — diagrams and plain‑language walkthroughs.

We’ll cross‑link these posts: B2 will reference this article for the conceptual mapping; A2 will reference this one for implementation details.

## Appendix: tiny side‑by‑side snippet

**Entity YAML (conceptual)**

**Emitted dbt model**

**Emitted SQLMesh model**