Two ideas here:

1) The same way you'd write any other tests. Use your favourite testing framework to write fixtures and tests for the SQL queries:

  - connect to the database
  - create tables
  - load test data
  - run the query
  - assert you get the results you expect

For insert or update queries, that assertion step might involve running another query.

2) DBT has support for testing! It's quite good. See https://docs.getdbt.com/docs/build/tests