5  Data Stores: SQL and Beyond

5.1 SQL Fundamentals and Setup

SQL (Structured Query Language) is how you get data out of a database. We’ll set up a lightweight database system so you can practise SQL queries locally.

5.1.1 Why SQL for Data Science?

Four reasons SQL earns its place:

  1. Most organisational data lives in relational databases, so SQL is often the only way to reach it
  2. It provides a standard vocabulary that transfers across nearly every database you’ll meet
  3. It’s usually faster than Python or R for filtering and aggregating large tables, because the work happens where the data already is
  4. Much of the transformation in a data pipeline happens in the database, before anything reaches your analysis
NoteAbout the example data in this chapter

The code below refers to files like sales.parquet, sales_2026.csv, and data/sales_*.parquet. These aren’t shipped with the book — they stand in for your own data. When you follow along, point the code at a file of your own, or write a small one first:

import pandas as pd
pd.DataFrame({
    "region": ["North", "South", "North", "South"],
    "quarter": ["Q4", "Q4", "Q3", "Q3"],
    "revenue": [120.0, 95.0, 110.0, 88.0],
}).to_parquet("sales.parquet")

The sample.db SQLite database is the one exception — you create that yourself in the next section.

5.1.2 Installing SQLite

SQLite is a lightweight, file-based database that requires no server setup, making it perfect for learning.

Think of SQLite as a simple filing cabinet for your data that you can easily carry around, unlike larger database systems that require dedicated servers.

5.1.2.1 On Windows

  1. Download the SQLite command-line tools (look for the “Precompiled Binaries for Windows” section) from the SQLite download page
  2. Extract the ZIP to a folder (e.g., C:\sqlite)
  3. Add this folder to your PATH environment variable so you can run sqlite3 from any terminal:
    • Press the Windows key, type “environment variables”, and open “Edit the system environment variables”
    • Click “Environment Variables…”
    • Under User variables, select Path and click “Edit…”
    • Click “New” and paste C:\sqlite (or wherever you extracted the files)
    • Click OK on every dialog box, then open a new terminal window for the change to take effect

5.1.2.2 On macOS

SQLite comes pre-installed, but you can install a newer version with Homebrew:

# Install SQLite
brew install sqlite

5.1.2.3 On Linux

sudo apt update
sudo apt install sqlite3

5.1.2.4 Verifying Installation

Open a terminal or command prompt and type:

sqlite3 --version

You should see the version information displayed.

5.1.3 Creating Your First Database

Let’s create a simple database to verify our setup:

First, in your terminal, create the database file and open a prompt on it:

sqlite3 sample.db

Your prompt changes to sqlite>. You are no longer talking to the shell — everything from here is SQL, terminated by a semicolon:

-- Create a table
CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    city TEXT
);

-- Insert some data
INSERT INTO people (name, age, city) VALUES ('Alice', 28, 'New York');
INSERT INTO people (name, age, city) VALUES ('Bob', 35, 'Chicago');
INSERT INTO people (name, age, city) VALUES ('Charlie', 42, 'San Francisco');

-- Query the data
SELECT * FROM people;

-- Exit SQLite. Commands starting with a dot are instructions to the
-- sqlite3 program itself rather than SQL, and take no semicolon.
.exit

Note the comment syntax. SQL uses --, not the # that shells use; paste a # comment at the sqlite> prompt and you’ll get a syntax error.

This creates a spreadsheet-like table inside a file, adds some rows, and views them.

5.1.4 SQL GUIs for Easier Database Management

The command line is the fastest route once you know it, but a graphical interface makes exploring an unfamiliar database considerably easier:

5.1.4.1 DB Browser for SQLite

This free, open-source tool provides a user-friendly interface for SQLite databases.

  1. Visit the DB Browser for SQLite download page
  2. Download the appropriate version for your operating system
  3. Install and open it
  4. Open the sample.db file you created earlier

DB Browser for SQLite acts like a spreadsheet program for your database, making it easier to view and edit data without typing SQL commands.

5.1.4.2 Using SQL from Python and R

You can also interact with SQLite databases from Python and R. Python needs nothing extra — sqlite3 is part of the standard library — but R needs two packages, and every other database in this chapter needs a driver of its own. Install them into your project environment now:

# DBI is the common interface; the others are drivers for specific databases
install.packages(c("DBI", "RSQLite", "duckdb", "RPostgres"))
# Python: sqlite3 is built in, the rest are not
pip install pandas pyarrow duckdb sqlalchemy "psycopg[binary]"

DBI in R and sqlalchemy in Python are the layers that let you write the same code against different databases; the rest are the per-database drivers underneath. You only need the driver for the database you’re actually connecting to, so install the PostgreSQL ones when you reach that section if you prefer.

5.1.4.2.1 Python
import sqlite3
import pandas as pd

# Connect to the database
conn = sqlite3.connect('sample.db')

# Query data into a pandas DataFrame
df = pd.read_sql_query("SELECT * FROM people", conn)

# Display the data
print(df)

# Close the connection
conn.close()
5.1.4.2.2 R
library(RSQLite)
library(DBI)

# Connect to the database
conn <- dbConnect(SQLite(), "sample.db")

# Query data into a data frame
df <- dbGetQuery(conn, "SELECT * FROM people")

# Display the data
print(df)

# Close the connection
dbDisconnect(conn)

This interoperability between SQL, Python, and R is a fundamental skill for data scientists, allowing you to leverage the strengths of each tool. You can store data in a database, query it with SQL, then analyse it with Python or R — all within the same workflow.

5.2 File Formats: Stop Using CSV for Everything

CSV is the universal interchange format and it will never go away, but it is a poor working format. It has no types (every column is text until something guesses), no compression, no column selection (reading one column means parsing all of them), and no schema.

Parquet fixes all four. It is a compressed, typed binary format, and it is columnar: where a CSV stores row after row, Parquet stores each column’s values together, which is why it can hand you one column without reading the others. It is the default choice for any dataset you’ll read more than once.

import pandas as pd

df = pd.read_csv("large_dataset.csv")

# Write once
df.to_parquet("large_dataset.parquet")

# Read back - types preserved, no parsing, and much faster on large files
df = pd.read_parquet("large_dataset.parquet")

# Read only the columns you need - the rest is never touched on disk
df = pd.read_parquet("large_dataset.parquet", columns=["date", "revenue"])

In R, arrow::write_parquet() and arrow::read_parquet() do the same job.

The file is usually a fraction of the CSV’s size, loads far faster, and — the part that matters most for reproducibility — remembers that your date column was a date. Keep the CSV if you need to hand it to someone in Excel. Work from the Parquet.

5.3 DuckDB: SQL Without a Database Server

SQLite gave you a database in a file. DuckDB gives you an analytical query engine in a file — or in no file at all — and it has changed how a lot of analysis gets done in the few years since it appeared.

The pitch: DuckDB runs SQL directly against CSV and Parquet files on disk, fast enough that work you would once have sent to a cluster now finishes on a laptop, with no server to install, configure, or keep running.

pip install duckdb        # Python
install.packages("duckdb")   # R

5.3.1 Querying Files Directly

No import step, no schema definition, no server:

import duckdb

# Query a CSV as if it were a table
duckdb.sql("SELECT COUNT(*) FROM 'sales_2026.csv'").show()

# Query every Parquet file matching a wildcard pattern, as one table
duckdb.sql("""
    SELECT region, SUM(revenue) AS total
    FROM 'data/sales_*.parquet'
    GROUP BY region
    ORDER BY total DESC
""").show()

That second query reads a directory of files, treats them as a single table, and aggregates — on files larger than your available memory, because DuckDB streams rather than loading everything first. Doing the same thing in pandas means reading each file, concatenating, and hoping it fits in RAM.

5.3.2 Working with pandas and R Data Frames

DuckDB reads data frames in the calling session directly, by name:

import duckdb
import pandas as pd

sales = pd.read_parquet("sales.parquet")

# `sales` is visible to the query without any registration step
result = duckdb.sql("""
    SELECT region, AVG(revenue) AS avg_revenue
    FROM sales
    WHERE quarter = 'Q4'
    GROUP BY region
""").df()          # .df() returns a pandas DataFrame

This makes DuckDB useful even when your data comfortably fits in memory: a GROUP BY with three joins is easier to read as SQL than as chained pandas operations, and you can move between the two freely within a single script.

In R, the same pattern works through DBI, with one extra step — the data frame has to be registered under a name the query can use:

library(duckdb)
library(DBI)
library(arrow)

sales_df <- read_parquet("sales.parquet")

con <- dbConnect(duckdb())
duckdb_register(con, "sales", sales_df)

result <- dbGetQuery(con, "
  SELECT region, AVG(revenue) AS avg_revenue
  FROM sales GROUP BY region
")

dbDisconnect(con, shutdown = TRUE)

5.3.3 When to Use Which

Situation Reach for
Small structured data you need to persist and update SQLite
Analytical queries over files — CSV, Parquet, or a folder of them DuckDB
Data too large for memory but on one machine DuckDB
Multiple people or applications writing concurrently PostgreSQL
Data already in a company database Whatever that is — connect, don’t copy

SQLite and DuckDB complement each other: SQLite is optimised for transactions (many small reads and writes), DuckDB for analytics (scanning and aggregating large volumes). For the work in this book, DuckDB is the one you’ll reach for more often.

5.4 Polars: An Alternative to pandas

Polars is a data frame library with a different design from pandas: it’s written in Rust, parallelises across cores by default, and offers a lazy API that optimises a whole chain of operations before executing any of it.

import polars as pl

# Eager - executes immediately, like pandas
df = pl.read_parquet("sales.parquet")
result = (
    df.filter(pl.col("quarter") == "Q4")
      .group_by("region")
      .agg(pl.col("revenue").sum())
)

# Lazy - builds a plan, optimises it, then runs. Note scan_ rather than read_
result = (
    pl.scan_parquet("sales.parquet")
      .filter(pl.col("quarter") == "Q4")
      .group_by("region")
      .agg(pl.col("revenue").sum())
      .collect()
)

In the lazy version, Polars sees the filter before reading anything and pushes it down into the file scan — so rows for other quarters are never read off disk at all. On large files the difference is substantial.

Should you switch? Not urgently. pandas has had since 2008 to accumulate an ecosystem, and almost every tutorial, Stack Overflow answer, and library integration assumes it. Polars is worth learning when pandas becomes the bottleneck — jobs that take minutes, or datasets that don’t fit comfortably in memory — and worth knowing about now so you recognise it when you meet it.

For most readers of this book, the ordering is: pandas by default, DuckDB when the query is more naturally SQL or the data is large, Polars when you’ve measured pandas being too slow.

5.5 PostgreSQL: When You Need a Real Server

SQLite and DuckDB are files. At some point you’ll need a database that multiple people or applications can write to at once, with user accounts and permissions. That is almost always PostgreSQL.

You are unlikely to install one from scratch as an analyst — more often you’ll be given connection details to an existing one. Connecting is the same shape in both languages:

import os
import pandas as pd
from sqlalchemy import create_engine

# Read credentials from the environment, never from the source file
engine = create_engine(
    f"postgresql+psycopg://{os.environ['DB_USER']}:{os.environ['DB_PASSWORD']}"
    f"@{os.environ['DB_HOST']}:5432/{os.environ['DB_NAME']}"
)

df = pd.read_sql("SELECT * FROM transactions WHERE date >= '2026-01-01'", engine)
library(DBI)
library(RPostgres)

con <- dbConnect(
  Postgres(),
  host     = Sys.getenv("DB_HOST"),
  dbname   = Sys.getenv("DB_NAME"),
  user     = Sys.getenv("DB_USER"),
  password = Sys.getenv("DB_PASSWORD")
)

df <- dbGetQuery(con, "SELECT * FROM transactions WHERE date >= '2026-01-01'")
dbDisconnect(con)
WarningTwo rules for database credentials

Read them from the environment, never write them in the script. Every example above uses os.environ / Sys.getenv for exactly this reason. A connection string with a password in it, once recorded in version control, stays in that project’s history permanently — deleting the line later changes the current file, not the record of what it used to say. The “Where to Put Credentials” section below explains the mechanics.

Never build a query by string-concatenating user input. Use the parameter binding your library provides:

# Wrong - a value containing a quote character can rewrite your query
pd.read_sql(f"SELECT * FROM users WHERE region = '{region}'", engine)

# Right - the driver escapes the value
pd.read_sql("SELECT * FROM users WHERE region = %(region)s", engine,
            params={"region": region})

This matters even for internal tools with trusted users. The failure isn’t always malicious — a region named O'Brien breaks the first version too.

If you do want a local PostgreSQL for development, the least painful route is a container rather than an installer — see the Docker Compose example in Containerisation, which brings up Postgres alongside Jupyter in one command.

5.6 Where to Put Credentials

Since this chapter is where credentials first become unavoidable, here is the whole answer in one place. It applies equally to database passwords, API keys — the token a service issues you so its API, its programmatic interface, knows who is calling — and cloud access tokens.

The rule: secrets live in the environment or a secret store; the code reads them by name; the file holding them is never committed.

NoteA forward reference to Git

This section talks about committing files and about .gitignore, both of which belong to Git, the version control system covered properly in Editors and Version Control. You don’t need that chapter to follow the rules here, only two facts.

Committing a file records a permanent snapshot of its contents in your project’s history. That history is what makes version control useful, and it is also why a password committed once is difficult to remove: later deleting the line changes the current state, not the record of what the file used to contain.

.gitignore is a plain text file listing paths Git should pretend not to see, so they are never committed in the first place. It is the mechanism behind every “add this to .gitignore” instruction below.

5.6.1 Local development: a .env file

Create a .env file in your project root:

DB_HOST=localhost
DB_NAME=analytics
DB_USER=analyst
DB_PASSWORD=the-actual-password
WEATHER_API_KEY=abc123

Add .env to .gitignore before you put anything real in it. Then load it:

from dotenv import load_dotenv   # pip install python-dotenv
import os

load_dotenv()
api_key = os.environ["WEATHER_API_KEY"]

R’s equivalent is .Renviron, which R reads automatically at startup — no library needed:

DB_PASSWORD=the-actual-password
WEATHER_API_KEY=abc123
api_key <- Sys.getenv("WEATHER_API_KEY")

.Renviron also belongs in .gitignore. Commit a .env.example listing the names with dummy values, so a colleague knows what to fill in.

5.6.2 Deployed applications: the platform’s secret store

Every hosting platform has one, and it is always the right answer in production: Render’s Environment tab, GitHub Actions secrets, Fly.io secrets, GCP Secret Manager, AWS Secrets Manager. Your code doesn’t change — it still reads os.environ[...] — only the source of the value does. The Deploying Data Science Projects chapter covers the specifics per platform.

5.6.3 If a secret does leak

Assume it’s compromised and rotate it — issue a new one and revoke the old. Do not try to scrub it from Git history: rewriting history is disruptive, easy to get wrong, and doesn’t help if anyone has already cloned or if the repository was ever public. Rotation is fast, and it is the only step that reliably closes the exposure.

This applies to private repositories too. “It’s private” is an access-control assumption rather than a guarantee, and repositories change visibility more often than people expect.

5.7 Conclusion

By the end of this chapter you should have:

  • SQLite installed, with a sample.db you created and queried from the command line
  • Queried that database from both Python and R
  • DuckDB installed, and run at least one query directly against a CSV or Parquet file
  • Written one dataset to Parquet and noticed the size and load-time difference
  • A .env or .Renviron file holding any credentials you use, listed in .gitignore

The next chapter covers making your working environment reproducible, so the packages these examples depend on are still there — and still the same versions — when you come back to them.