Drupal seems to have lots to offer to the non-programmer. Tons of modules and easy installation. But at what point would you say you need to be a programmer? Where does it fall down and require more hands-on attention?
Also, even though they aren't "meant" to be done, I'm interested in any custom tweaks you've had to do to the code base.
I've used drupal without ever need to do any "programming", and if you don't have programming skills you could always use a cheap contractor (e.g. rent-a-coder or similar) to quickly/cheaply modify/create a plug-in for you.
The only time you'd need a programmer would be if there was some functionality that you wanted and current plugins just did not provide what you were looking for. Most Drupal-powered sites can be run by non-programming types. In short, I'd say you would need to be a programmer if you need to develop custom functionality not found in any of the available plugins out for Drupal now.
Slightly off topic, but addressing one point you made:
Also, even though they aren't "meant" to be done, I'm interested in any custom tweaks you've had to do to the code base.
It's open source software. You get to decide what's "meant to be done," based on your needs and desires.
Some of the best features start out as someone changing or using something in a way no one else had anticipated, thereby producing something new, interesting, and useful. Finding out what customizations others have made like this is a great way to broaden your understanding, and nothing to be furtive about.
There are a lot of modules available, but as soon as you want to do something slightly different than the out-of-the-box functionality, you need to be a programmer.
Personally, I have never altered the core codebase. It is really not recommended. I have taken existing modules and either taken parts of them to use in my own modules, or renamed them and modified them as my own.
I strongly disagree with the idea that you need programming skills when you want to do something different than the 'out of the box' functionality, unless "out of the box" includes the myriad of ways one can mix and match the thousands of third-party plugins.
The biggest challenge is this: Drupal's community is moving more and more towards building small, reusable blocks that can be combined in novel ways via configuration, rather than coding. This means that the number of things you can accomplish by combining small pieces via administrative screens is impressive. However, it means that the tools you put together this way lack a lot of the 'smooth edges' that would be there with a single built-to-spec plugin.
It's those places -- the rough edges -- where custom code is most frequently needed. Writing a short hook_form_alter() function to hide some unneeded form fields or change the location a form redirects to when it's completed, building a simple custom sidebar block to give the use some useful links to the different pieces you've assembled, things like that. Custom theming also takes up a lot of that 'smoothing' work.
This is to say that you can do insane amounts of stuff with zero code, but you will reach a point of diminishing returns, especially when trying to build complex rule-based logic ("Under certain circumstances, I want X to appear, otherwise the user should be prompted for Y..."). Knowing at least some PHP, not being afraid to peek at what's going on under the hood, and the willingness to write a (small!) custom module to accomplish certain tweaky goals will definitely serve you well on more complex sites.
Also, it's easy to find plugins that do interesting things but are still in beta/development. Knowing some PHP will help you keep your head above water if you decide to work with 'not quite finished' code.
Curious about the downvote; comments on why would be appreciated. If there's disagreement, the details of the reasons for it would be useful for the original poster.