Yes, you can specify a filter to be applied when opening a database, the filter is saved in the model, along with all the other filters, but the fact that it is applied on opening the database is saved in the project file, IIRC.
To help get started, it might be useful to use Excel to make a small subset (100 records, say), of your data, and to build your model and filter using this. Then save the project with the filter applied. With the project open in Monarch, go to File, Database and change from the Excel to the ODBC source, and it should be OK. Save the project again to remember the new data location.
Doesn't the "Change..." button found on the sample data screen in the Database wizard allow you to define the selection criteria to retrieve from the database to limit the data brought into Monarch? Or is that the mechanism that you're referring to Olly?
It doesn't try to retrieve the entire database first, and then import the only the data satisfying the filter criteria. I believe.
This prevents the entire database from being retrieved, and then having the active Table window filter being applied to that entire selection.
To retrieve the entirety of a large database, you'd need to use multiple projects/models with appropriate filters and one way or another ensure that in the end you've retrieved every record.