Not sure about op but you have to put forth quite a bit of effort to get declarative infra with Ansible. Some of it is declarative out of the box but a lot is imperative.

The main difference, if I revoke a DB privilege, I have to add a line to Ansible with a REVOKE in most cases versus Terraform you just delete the config line and the tool realizes during its diff stage and performs the removal change (it's stateful and declarative)

Does terraform has any drifts for example if I remove a db from terraform is it going to.remove the db from prod? Can I trust it's always the same or do I have to do cleanups?

Terraform is declarative, meaning: The stuff you write into the terraform files (TF) is exactly the stuff you get configured.

Example: you add a new database and a database user to the TF files, run terraform plan & apply, you do have the database and user configured. You remove the user and run plan & apply, terraform does remove the user.

It goes the other way too: if you add additional port rules to a network security group (e.g. in AWS) by hand, terraform will remove them when plan & apply because they are not defined in the TF files.

So in conclusion: for your declared state, there will be no drift.

As nijave said, the declarative style is the difference. I can read a terraform file and already know the exact state my system will have.