Welcome back 😃 After handing in your mid-track project, navigating the complexities of Docker and automating your first CI/CD pipelines, it’s now time to actually make sense of the data you’ve been moving!
In this chapter, we transition from building data pipelines to working directly with data in a structured and analytical way. While Python is commonly used to orchestrate and process data workflows, SQL is the primary language for querying and transforming data at scale within modern data systems.
This shift moves the focus from how data is moved and prepared, to how it is explored, shaped, and turned into insights, which is the ultimate goal!
By the end of this chapter, you should be able to explain why SQL is the right tool for in-database analytics, distinguish operational (OLTP) from analytical (OLAP) SQL, recognize the common analytical question patterns, and run your first aggregate query against the shared NYC taxi data.
In the tech world, things move fast, sometimes too fast. Tools like Airflow might get replaced in a decade. But SQL? SQL was invented in the early 1970s and it is still the undisputed champion of the data world.
<aside> 🤓 Curious Geek: The Name "SEQUEL"
SQL was originally developed at IBM and was first called SEQUEL (Structured English Query Language) because it was designed to read almost like English. Due to trademark issues, the name was shortened to SQL, but many people still pronounce it "sequel" today.
</aside>
In a world where everyone uses different programming languages, everyone still speaks SQL! It is really the universal language that connects Data Engineers, Analysts, and Business Stakeholders.
<aside>
📘 Core program connection: You already met the basics of SQL (SELECT, WHERE, JOIN, GROUP BY) in the Core program. This week builds on that foundation for analytics at scale. For a quick refresher, see SQL Basics: DDL and DML.
</aside>
In this section, we explore the distinction between operational SQL, which focuses on day-to-day system behavior, and analytical SQL, which is designed for aggregating, transforming, and analyzing data to generate insights.
We can summarise the main differences between OLTP and OLAP as:
| Aspect | OLTP (Operational) | OLAP (Analytical) |
|---|---|---|
| Purpose | Run the business (the now) | Understand the business (the trends) |
| Query type | Insert, update, delete single records | Aggregate, filter, join across history |
| Rows touched | A few rows per query | Thousands to millions per query |
| Example | "Add this taxi trip to the table" | "What was the average fare per borough last month?" |
You’ve just spent weeks getting good at Python, so it’s natural you might think, "I'll just pd.read_sql() everything into a DataFrame and do my work there."
Although tempting, this is a quick path to performance issues, increased resource costs (CPU, memory, network overhead) and operational inefficiencies. Databases are highly optimised systems designed to do exactly what SQL is asking them to do: filter, join, aggregate, and transform data directly where it lives. When you pull large datasets into Python unnecessarily, you:
<aside> 💡 The principle of data locality: Moving data is computationally expensive (wasting network bandwidth, memory, and time). If your dataset is stored in an Azure PostgreSQL instance and you want to compute the average fare, SQL performs this computation inside the database and returns a single aggregated result. Python, on the other hand, can easily end up pulling large portions of the dataset into memory first.
</aside>
Choosing between SQL and Python depends on both where the transformation runs and what transformation you are aiming at.
Use SQL when:
Use Python when:
<aside> 💡 Rule of thumb: If the data is already in a database and the operation is relational, use SQL. Otherwise, use Python.
</aside>
Let’s now zoom out a bit to understand SQL’s role: in a modern data pipeline, SQL is most often used in the transformation and reporting layers, sitting between raw data ingestion and business-facing outputs.
As you’ve seen in previous chapters, a typical flow looks like this:
SQL is particularly powerful in the transformation stage because it allows these operations to happen directly inside the database, close to the data.
A common workflow is:
SQL prepares clean datasets → Python processes or models them → BI tools visualise the results.
When the modeling steps are not needed, BI tools like Tableau or Power BI connect directly to the output of your SQL datasets. Most BI tools also allow you to connect to your SQL database directly and write custom SQL queries within the BI tool interface itself!
The goal of the analytical exploration can be often grouped into a small number of recurring patterns. Learning to recognise them helps you translate business questions into SQL more effectively:
Common analytical question patterns: ranking, trends over time, filtering, and aggregation
In the next chapters, we’ll look into how to answer such questions using SQL.
Although SQL is a standard language, different database systems implement their own dialects. These dialects share a common core but differ in functions, syntax details, and performance features.
Today, most companies use cloud-based set-ups such as Azure, Google BigQuery or Snowflake. Despite the differences each SQL type has, most core concepts and functions remain transferable.
In this course we use PostgreSQL, which is considered one of the most standards-aligned SQL dialects.
<aside> 🤓 Curious Geek: One SQL, many dialects
SQL became an ANSI standard in 1986 and an ISO standard a year later. That shared core is why a SELECT ... GROUP BY you write for PostgreSQL reads almost identically in BigQuery or Snowflake. Vendors extend the standard with their own functions, but they rarely break it, so the query skills you build this week travel with you across employers and decades.
</aside>
Time to run something. The shared class database keeps the raw NYC taxi data in a schema named nyc_taxi: the nyc_taxi.raw_trips table (one row per ride) and nyc_taxi.raw_zones (the location lookups). This schema is shared and read-only, so everyone queries the same tables, and you always refer to them with the nyc_taxi. prefix. Start with two of the simplest analytical queries there are: a row count and an average.
Connect with the personal Postgres login your teacher gives you (your own username and password), the same connection-string pattern you set up in Azure PostgreSQL, over sslmode=require. Use any SQL client: psql, DBeaver, or VS Code SQLTools.
Your login is scoped on purpose. Its session already defaults to your own dev_<name> schema, so any view you create later this week lands there automatically, with no SET search_path needed. You can read the shared nyc_taxi tables but not change them, and you cannot touch another student's schema. Do not use a shared admin login: it would let you accidentally edit or drop the class dataset everyone depends on.
<aside>
⌨️ Hands on: Connect to the shared Postgres database and run SELECT COUNT(*) FROM nyc_taxi.raw_trips;. You should see around 57,000 rows. Then run SELECT AVG(fare_amount) FROM nyc_taxi.raw_trips; to get the average fare across every trip in one number.
</aside>
When a query throws an error you do not understand, an LLM is a fast way to decode the message.
<aside>
💡 Using AI to help: Paste a SQL error message into an LLM and ask what it means (⚠️ Ensure no PII or sensitive company data is included!). The NYC taxi data used in this course is public, so sample rows from nyc_taxi.raw_trips are safe to share, but never paste real customer data from a job.
</aside>
Want to check your understanding before moving on? Try the interactive quiz for this chapter.