[odie5533]

Do you advise then use Pydantic for data mapping to/from raw SQL, to avoid using a full ORM? My thinking is you're almost at an ORM with that method with tools like SQLModel that I'm unsure what the benefit is to the plain Pydantic method.

[halfcat]

In one sense you’re right, but (at least in data projects) the goal is a bit different. We’re often reading not only from SQL databases, but also parquet files, CSV, JSON, APIs, piped input from another process’s STDOUT, and so on.

Basically we don’t always know what the future unknown data source we may be reading from, and also the schema of the source might change, but we can define what we expect on the receiving end (in pydantic), and have it fail loudly when our assumptions change.

[odie5533]

The nice thing about SQLModel is they're still Pydantic models so you can use them with custom data mappers like parquet, csv, json, etc. I think you make a good point about keeping the data model pure so you're not dependent on a data source. But I think SQLModel largely accomplishes that, and so does SQLAlchemy's declarative dataclass mapping (though I've not used the latter).