The secret to writing good automated tests is to be a good software designer.
It sounds trivial - but the sad fact is that more often than not the attempt to implement test automation falls due to poor software design where people are struggling with strong dependencies where you cannot test a single class or module without also configuring a whole environment of databases, webservices, populating whatever tables etc.
When the novice experiences this the natural, but incorrect reaction, is to write fewer and fewer tests at a higher and higher level of integration, the limit case being manual end-to-end integration testing through the GUI.
It is a symptom of bad software design, not automated testing being worthless.
In a good project the test suite does not need to be changed when external data changes. In a bad project, the test suite breaks when time passes or every time someone runs the end-of-month job to add interest to the accounts, or when the ticker price changes for a stock.
In a good project, only a few localized changes to tests and production code are needed as for a requirement change. In a bad project, there is impact analysis and lots of similar changes all over the code-base to accomplish this.
In a good project most of the code does not know from where it gets its data - in a bad project, even a simple interest calculation business rule knows about how to look up the database and the interest rate web service.
In a good project there is typically 1-2x as much test code as production code. In a bad project there is often 5x-25x as much production code as test code.
In a good project most tests exercise only a single class or a few functions. In a bad project every tests activates a code-path through many parts and tiers of the application.
In a good project most of the test code is asserting that things went as expected. In a bad project, most of the test code is setting up the environment to run the test.
In a good project there are tests at all levels of the application. In a bad project they are mostly through the UI and often even manual.
In a good project you can change the application without risk. In a bad project change is painful, infrequent, risky and requires executive sign-off.
In a good project you can deploy new code to production safely many times a day. In a bad project you only do it reluctantly every monthly or a few times a year.