Data Engineering Foundations in Python/

...

/

Query Life Cycle

Query Life Cycle

Learn all the steps inside a query life cycle.

We'll cover the following...

Both data engineers and data users write queries every day, but not everyone is familiar with how query works internally. While we trust the database engine to do all the magic and return us the expected result, it's still worth understanding what goes on behind the scenes, and the knowledge can further help us enhance query performance.

Imperative language vs. declarative language

There are two programming paradigms: imperative and declarative. The most commonly used programming languages, like Python and Java, are imperative. They instruct the computer on how the code should run by giving a sequence of steps.

For example, we want to find an item from a list, and the Python implementation can look like the following. The program loops over the dictionary and compares each item with the target. With imperative languages, we have full control over the implementation.

Press + to interact
fruits_price = {"apple":1.0, "banana":2.0, "pear":2.5, "strawberry":3.0, "watermelon":5.0}
def search(target):
    for f in fruits_price:
        if f == target:
            return fruits_price[f]
print(search("pear"))

On the other hand, when using a declarative programming language like SQL, we instruct the computer what kind of result we expect, such as the conditions and transformation tasks that need to be performed, and the language figures out how to execute the operations to achieve that result.

The following is the SQL solution for the same example. The query tells the computer that it looks for the price of a pear, and that's it. SQL will determine the implementation details behind the scenes, which is different from the imperative language, where the code outlines the step-by-step procedure for the computer.

Press + to interact
select price
from fruits_price
where fruit = 'pear'

Both language models have their ...