Microsoft
All bets are off if you are dealing with poorly supported software. I have found Terraform providers to be very well supported, and bugs are fixed quickly, but I have never used an Azure provider. The Terraform AWS provider handles dependencies between CloudFront distributions and their related resources (cache policies, for example) without manual configuration.
As I understand it, modern microservice-oriented architecture should obviate the need for specific VM build or service deployment order. However, there are edge cases and legacy architectures, so sometimes you might need to manually create a dependency. In the thousands of resources we manage with Terraform, I think there are a small handful of such cases. I make heavy use of Terraform's "-target=" option to manage groups of resources in Terraform modules independently.
Terraform states can indeed become large and slow. If they do, they should be broken up, which requires more complexity and different tooling. However, we have not experienced difficulty "managing" them. We put them in S3, keep confidential secrets out of them, and they just work for years, even with frequent additions and changes and Terraform and provider version updates.
I have never encountered a circular dependency. It seems to me that if you did, you should redesign your infra until it goes away.
If your infrastructure is of any significant size (or significant importance), you should certainly have it designed and managed by an experienced cloud/infra engineer, or software developer with similar experience. I don't see how you could or why you should desire to escape that requirement. I have an intermediate software engineering skillset, and it has never been seriously challenged by the lightweight conditional/iteration syntax available in HCL/Terraform. Pulumi, OTOH & IME, requires a deep understanding of complex programming concepts (such as asynchronous programming) to manage even simple resources.
