Automated or Manual/Explorative Tests?
A good automated test, often a unit test, is a piece of software. To be a good test, it must be programmed well -- readable, maintainable, reasonably bug-free, fast enough to run it often.
Some people argue that a good test makes few or no assumptions about the internal working of the system under test (often a class) and only test public methods, but few classes function in complete isolation. In order to provide mocks, the tester has to understand how the methods work. The automated test should cover the contract of the system under test and a reasonable selection of the paths through the methods. Covering all of them is usually too expensive.
A good test automation engineer has the training and mindset to look at the code and to select the data which provides a good enough coverage. Many programmers who are told "write tests for your classes" simply bring unit test coverage to 100% -- each instruction executed at least once.
A good manual test makes good use of (expensive) human labor. It covers the most critical parts of the system from a business viewpoint, and it uses the professional judgement of the tester.
For instance, I found writing automated test for responsive web design impractical. So instead I made a note that we need a manual test, "call the site on all test devices and resize the browser window where applicable. Decide if it looks acceptable, otherwise document the device and settings." And some poor schmuck got paid to do it. But not with every release, because that would be too expensive.
A good manual test tests many things at once. Where an automated test might log an user in and then close the browser, and the next test might log another user in and out and then close the browser, the manual test would cover login and logout as part of some other test case which requires a logged-in user.