With Beer, Refactor Snowflake's Code

TL;DR: Proper allocating and releasing of resources during the software lifetime is critical in designing resilient systems. Unhandled unexpected behaviors can lead to disastrous consequences.

Imagine we are hosting a party for our beer-loving friends. At the center of it all is our elegantly engineered home beer refill machine. Guests walk to our beer station, choose their favorite brews, fill up, close the tap and enjoy every sip. The party is a hit, and everyone is loving our beer station. Until one guest forgot to close the tap. Beer is now spilling all over the floor, and our party is quickly turning into a disaster.

In programming, resource spilling happens when we allocate resources but do not release them properly. This can, and most likely will, lead to disastrous consequences like our party example above. To avoid resource spilling in our scripts, we need to make sure that we always have logic to ensure the proper release of resources.

The snowflake's Python Connector API code below, adopted from their documentation, is one example of a disaster in making.

#!/usr/bin/env python
import snowflake.connector

# Gets the version
ctx = snowflake.connector.connect(
    user='<user_name>',
    password='<password>',
    account='<account_identifier>'
    )
cs = ctx.cursor()
try:
    cs.execute("SELECT current_version()")
    one_row = cs.fetchone()
    print(one_row[0])
finally:
    cs.close()
ctx.close()

Say a junior developer adds a line, logger.debug(f"🍻 One row {row[0]}"), just before ctx.close(). Oops 🙊, beer spilled. NameError: name 'row' is not defined. This cause the connection not to be close.

If we don't properly release database connections after we're done with them, and we are running this script in multiple threads, then eventually the server will run out of connections and be unable to service any more requests.

To avoid such situations, it is important to always release resources after we are done using them. In our example above, we would need to close the beer tap after everyone is done filling up their glass. Only then can we avoid a messy situation.

Grab a glass of beer 🍺. Let's refactor.

#!/usr/bin/env python
from snowflake import connector

# Gets the version
with connector.connect(...) as ctx, ctx.cursor() as cs:
    cs.execute("SELECT current_version()")
    one_row = cs.fetchone()
    print(one_row[0])

This refactor would close database connection even if our junior developer had succeeded in spilling our beer. with expression manages our resources by trying to open and finally close|releases no matter what exception we encounter.

So, with a beer 🍺, refactor code to ensure the party stays a hit. If you are not familiar with the with statement, Python 🐍 documentation is your beer refill machine. Pour yourself a glass or two.

FYI: OpenAI’s text complete and I co-authored this article. Image generated by OpenAI’s DALL·E models: cats drinking beer next to a beer machine