It's pretty cool. My record so far is to consume 400Mbps, using 4 computers downloading from about 10 places, all wired through gigabit switches.
In practice, though, it doesn't make much difference compared to a 30Mbps cable modem for most consumers:
most streaming video is < 10 Mbps;
large file downloads are generally limited by a server (or somewhere else in the network?), so it's hard to exceed 30-40Mbps download speed;
web browsing feels about the same, because it's limited by round trips of DNS and http requests, not by bandwidth (spdy will help here?);
many consumer-grade NAT boxes (linksys and friends) are capable of only 100-200 Mbps
The one place it's made a big difference so far is uploads. For example, backup to a cloud backup service (backblaze) often goes 50Mbps or higher. But I did have to try several backup services because some were limited on the server side to a few Mbps.
Running services from home could be a use case too, but then you get into reliability of power/etc, and the fact that so far you can't get a static IP address through google fiber.
So for now google fiber is mostly a fast cable modem from a "don't be evil" provider. I think the real disruption will come with new services that don't really exist yet. What kind of new things can be built if there's enough audience?