Can prefect runs be triggered by some event rather just scheduled for a set time?

Yes. I could share more if you could tell more about what you're trying to do or solve

Loading data from our s3 Data Lake into our rds Data Warehouse, so is triggering by s3 object creation possible?

yup, check out https://medium.com/the-prefect-blog/event-driven-data-pipelines-with-aws-lambda-prefect-and-github-actions-b3d9f84b1309

Super useful thank you. Have you tried this with AWS CDK or even SST.dev?

please share once you post it, I'd appreciate that. I like sls so I stick with that :)