Revisiting SQL for Data Analytics: A Senior Developer’s Practice Journal

Let’s treat this as a proper mentoring session.

When I help a developer improve their SQL, I do not begin by handing them a list of commands to memorise. Syntax is easy to look up. The harder and more valuable skill is learning how to reason about data: what a row represents, how relationships change the grain of a result, whether an aggregate is telling the truth and whether a query will remain dependable at production scale.

I want to take you from database fundamentals into that deeper analytical way of thinking. We will work through modelling, SELECT, transformations, joins, aggregation, window functions, performance, semi-structured data, statistics and analytical delivery. At each stage, I will explain not only how the SQL works, but what I look for when reviewing it in a real system.

Because SQL is not just a language.

SQL is where business truth lives.

Your application can have a beautiful React frontend, clean ASP.NET Core APIs, CQRS handlers, EF Core repositories, Azure pipelines, and perfect UI components — but if your data model is weak, your joins are wrong, your aggregations duplicate rows, or your query scans millions of records unnecessarily, the whole system becomes unreliable.

So this article is not about memorising SQL commands. It is about understanding the analytical mindset behind SQL.

---

1. Data is how we describe the real world

I begin with the meaning of the data itself.

Data is a recorded description of something real.

A customer. A sale. A product. A dealership. A loan application. A user login. A payment. A support ticket.

Each real-world event or object becomes a unit of observation. Each measurement about that observation becomes a variable or feature.

For example, in a loan platform:

Observation:
One loan application

Variables:
ApplicationId
CustomerId
LoanProductId
RequestedAmount
AnnualIncome
Status
SubmittedDate
ApprovedDate
RejectedReason

This is the first mindset shift.

It is easy to see only a table. I want you to see a model of reality.

Before writing SQL, ask:

What real-world thing does each row represent? What does one row mean? What is the grain of this table? Does one row mean one customer, one sale, one payment, one email, one product, or one application status change?

This matters because most bad analytics comes from misunderstood grain.

If one row in sales means “one customer buying one product”, then counting rows gives sales events. But if one row in sales_items means “one item within an order”, counting rows gives line items, not orders.

That one misunderstanding can destroy a dashboard.

---

2. Data modelling: conceptual, logical, physical

I separate data modelling into three levels: conceptual, logical and physical.

Let me explain it like this.

The conceptual model is the business conversation.

You sit with stakeholders and say:

“We have customers, products, dealerships, salespeople and sales. Customers buy products. Salespeople work in dealerships. Sales are handled by salespeople.”

No database yet. No indexes. No PostgreSQL. Just reality.

The logical model turns that reality into structured entities and relationships:

Customer
Product
Dealership
Salesperson
Sale

Now we define relationships:

A customer can have many sales. A product can appear in many sales. A salesperson can create many sales. A dealership can have many salespeople.

The physical model is where we finally decide implementation:

PostgreSQL tables. Column data types. Primary keys. Foreign keys. Indexes. Constraints. Schemas.

For example:

CREATE TABLE customers (
    customer_id BIGSERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT,
    state CHAR(2),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY,
    model TEXT NOT NULL,
    product_type TEXT NOT NULL,
    base_msrp NUMERIC(12,2) NOT NULL,
    production_start_date DATE,
    production_end_date DATE
);

CREATE TABLE sales (
    sales_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    sale_amount NUMERIC(12,2) NOT NULL CHECK (sale_amount >= 0),
    sale_date DATE NOT NULL
);

Now we are not just creating tables. We are protecting truth.

PRIMARY KEY says each row has identity. FOREIGN KEY says relationships must be valid. NOT NULL says the value is required. CHECK says invalid business values cannot enter quietly. NUMERIC(12,2) says money-like values need exact decimal handling.

My mentoring principle is simple: model before query. If the model is weak, SQL becomes a long apology.

---

3. Relational databases and SQL: tables, keys and integrity

A relational database stores data in tables: rows and columns.

A row is one record. A column is one attribute. A table is one collection of similar records.

But the power is not just rows and columns. The power is relationships.

Primary key:

customer_id BIGSERIAL PRIMARY KEY

This uniquely identifies a customer.

Foreign key:

customer_id BIGINT NOT NULL REFERENCES customers(customer_id)

This says every sale must point to a valid customer.

Without foreign keys, the application may insert sales for customers that do not exist. Then reports fail, dashboards become suspicious, and someone says, “SQL is wrong.” SQL is not wrong. The model was weak.

A natural key is a real-world value, such as an email address or national insurance number. A surrogate key is an artificial database-generated value, such as customer_id.

In business systems, I generally prefer surrogate keys as primary keys because they are stable, small, and efficient for joins. Natural keys can change. Emails change. Business identifiers get corrected. People make mistakes.

Use natural keys for uniqueness where appropriate:

ALTER TABLE customers
ADD CONSTRAINT uq_customers_email UNIQUE (email);

But use surrogate keys for internal relational identity.

My rule of thumb: business identity and database identity are related, but they are not always the same thing.

---

4. CRUD: the lifecycle of data

CRUD means:

Create. Read. Update. Delete.

CRUD is worth establishing early because all database work sits somewhere in this lifecycle.

Create a table:

CREATE TABLE customer_segments (
    customer_segment_id INTEGER PRIMARY KEY,
    segment_description VARCHAR(100) NOT NULL,
    segment_creation_date DATE NOT NULL
);

Insert data:

INSERT INTO customer_segments (
    customer_segment_id,
    segment_description,
    segment_creation_date
)
VALUES (
    1,
    'High value customers with repeated purchases',
    '2026-01-01'
);

Read data:

SELECT
    customer_segment_id,
    segment_description,
    segment_creation_date
FROM customer_segments;

Update data:

UPDATE customer_segments
SET segment_description = 'High value repeat customers'
WHERE customer_segment_id = 1;

Delete rows:

DELETE FROM customer_segments
WHERE customer_segment_id = 1;

Drop table:

DROP TABLE customer_segments;

Here is the production warning I give developers.

DELETE removes rows but keeps the table.

DROP TABLE removes the table definition and its data.

That is a major difference.

In production, never casually run:

DROP TABLE customers;

That is not “cleaning data.” That is destroying a database object.

Best practice: in real production systems, destructive operations should require migration scripts, reviews, backups, approvals, and rollback thinking.

---

5. Data types: boring topic, expensive mistakes

Data types are not boring. They are business rules disguised as storage choices.

Use NUMERIC or DECIMAL for exact financial values:

sale_amount NUMERIC(12,2) NOT NULL

Do not use floating-point types for money.

Bad:

sale_amount DOUBLE PRECISION

Why? Because floating-point values are approximate. That is fine for scientific calculation, but not for invoices, loan amounts, payments, tax, or financial analytics.

Use DATE when you only need a date:

sale_date DATE NOT NULL

Use TIMESTAMP when you need date and time:

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

Use BOOLEAN for true/false values:

email_verified BOOLEAN NOT NULL DEFAULT FALSE

Use constrained text carefully:

status VARCHAR(30) NOT NULL

But if status has fixed allowed values, add a check:

ALTER TABLE sales
ADD CONSTRAINT ck_sales_status
CHECK (status IN ('Pending', 'Completed', 'Cancelled', 'Refunded'));

My rule of thumb: a column type is not just storage; it controls what operations are valid and what bad data can enter.

---

6. COPY and \COPY: real data moves in and out

In real analytics work, data does not always begin inside your database. It may arrive from CSV files, vendors, Excel exports, legacy systems, CRM tools, finance systems, or data feeds.

This is where PostgreSQL COPY and \COPY become useful.

COPY works from the database server side.

\COPY works from the client workstation through psql.

Export:

COPY (
    SELECT
        customer_id,
        first_name,
        last_name,
        email,
        state
    FROM customers
)
TO '/tmp/customer_export.csv'
WITH CSV HEADER;

Client-side export using psql:

\COPY (
    SELECT customer_id, first_name, last_name, email, state
    FROM customers
) TO 'C:\Users\Public\customer_export.csv' WITH CSV HEADER;

Import:

\COPY customer_segments (
    customer_segment_id,
    segment_description,
    segment_creation_date
)
FROM 'C:\Users\Public\segments.csv'
WITH CSV HEADER;

Why does this matter?

Because analytics teams constantly exchange data.

But file import is also a risk point.

Before importing, ask:

Does the file have headers? What delimiter is used? How are nulls represented? Is encoding correct? Are dates in the expected format? Are duplicate rows possible? Should data land in a staging table first? Should quality checks run before inserting into production tables?

Best practice: load raw files into staging tables first, validate them, then load clean data into final tables.

---

7. Python, SQLAlchemy and pandas: SQL plus programmatic analytics

Python can complement PostgreSQL through tools such as psycopg2, SQLAlchemy and pandas.

This is important because SQL is excellent for set-based querying, but Python is excellent for automation, dataframes, visualisation, modelling and machine learning workflows.

Basic SQLAlchemy connection style:

from sqlalchemy import create_engine, text
import pandas as pd

connection_string = (
    "postgresql+psycopg2://{username}:{password}@{host}:{port}/{database}"
)

engine = create_engine(
    connection_string.format(
        username="postgres",
        password="your_password",
        host="localhost",
        port=5432,
        database="sqlda"
    )
)

Read SQL into pandas:

query = """
SELECT
    state,
    COUNT(*) AS customer_count
FROM customers
GROUP BY state
ORDER BY customer_count DESC;
"""

customers_by_state = pd.read_sql_query(query, engine)

print(customers_by_state.head())

Write result back:

customers_by_state.to_sql(
    "customers_by_state_summary",
    engine,
    if_exists="replace",
    index=False
)

Now you have a workflow:

Database stores source data. SQL prepares analytical dataset. Python loads it into memory. pandas analyses or visualises. Result can be saved back to PostgreSQL.

My advice is to use SQL where set-based filtering, joins and aggregation are strong, then use Python where exploratory analysis, modelling, visualisation or advanced data processing is easier.

Do not pull 50 million rows into pandas because you were too lazy to write a WHERE clause.

---

8. SELECT: the most important SQL command

I spend serious time on SELECT because it is the foundation of analytical SQL.

Basic:

SELECT
    product_id,
    model,
    base_msrp
FROM products;

Avoid:

SELECT *
FROM products;

SELECT * is acceptable for quick exploration, but poor for production queries.

Why?

It returns unnecessary columns. It increases network traffic. It hides the contract. It can expose sensitive fields. It can break consumers when schema changes.

Expressions:

SELECT
    model,
    base_msrp,
    base_msrp * 0.90 AS discounted_price
FROM products;

Alias:

SELECT
    model AS product_name,
    base_msrp AS original_price,
    base_msrp * 0.90 AS discounted_price
FROM products;

Sorting:

SELECT
    model,
    production_start_date
FROM products
ORDER BY production_start_date DESC;

Limit:

SELECT
    product_id,
    model
FROM products
ORDER BY product_id
LIMIT 5;

One detail I always point out: LIMIT without ORDER BY gives you “some rows”, not necessarily meaningful rows.

Filtering:

SELECT
    product_id,
    model,
    product_type,
    base_msrp
FROM products
WHERE product_type = 'scooter'
  AND base_msrp < 1000
ORDER BY base_msrp DESC;

Filtering is where SQL begins to become analytical. You are no longer just reading data; you are asking questions.

---

9. NULL: the silent troublemaker

NULL means missing or unknown. It is not zero. It is not empty string. It is not false.

Bad:

SELECT *
FROM customers
WHERE email = NULL;

Correct:

SELECT *
FROM customers
WHERE email IS NULL;

Find customers with email:

SELECT *
FROM customers
WHERE email IS NOT NULL;

Use COALESCE to replace nulls:

SELECT
    customer_id,
    COALESCE(email, 'No email provided') AS email_display
FROM customers;

Use NULLIF to avoid divide-by-zero:

SELECT
    product_type,
    total_sales,
    total_customers,
    total_sales / NULLIF(total_customers, 0) AS sales_per_customer
FROM product_type_summary;

My rule of thumb: every analytical query must account for nulls.

If you ignore nulls, your counts, averages, rates and labels may be misleading.

---

10. Transforming data: CASE, casting and functions

Analytics is not only selecting data. It is shaping meaning.

CASE WHEN lets you create categories:

SELECT
    customer_id,
    total_purchase_amount,
    CASE
        WHEN total_purchase_amount >= 10000 THEN 'High Value'
        WHEN total_purchase_amount >= 2500 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS customer_segment
FROM customer_purchase_summary;

Casting:

SELECT
    customer_id,
    created_at::date AS created_date
FROM customers;

String cleanup:

SELECT
    customer_id,
    LOWER(TRIM(email)) AS normalized_email
FROM customers;

Date transformation:

SELECT
    customer_id,
    DATE_TRUNC('month', created_at) AS created_month
FROM customers;

Update bad data carefully:

UPDATE customers
SET email = LOWER(TRIM(email))
WHERE email IS NOT NULL;

I never run a broad UPDATE without checking its effect first.

Do this first:

SELECT
    customer_id,
    email,
    LOWER(TRIM(email)) AS cleaned_email
FROM customers
WHERE email IS NOT NULL
LIMIT 20;

Then update.

Best practice: preview destructive transformations before applying them.

---

11. Derived datasets: subqueries, CTEs and views

Creating datasets from existing datasets is where SQL starts to feel like a data pipeline.

Subquery:

SELECT
    customer_id,
    total_sales
FROM (
    SELECT
        customer_id,
        SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY customer_id
) customer_totals
WHERE total_sales > 5000;

Common Table Expression:

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_sales
FROM customer_totals
WHERE total_sales > 5000;

A CTE makes complex SQL easier to read.

View:

CREATE VIEW vw_customer_sales_summary AS
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.state,
    COUNT(s.sales_id) AS sale_count,
    SUM(s.sale_amount) AS total_sales
FROM customers c
LEFT JOIN sales s
    ON s.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.first_name,
    c.last_name,
    c.state;

Then:

SELECT *
FROM vw_customer_sales_summary
WHERE total_sales > 5000;

My rule of thumb: CTEs are excellent for readability and views are useful for reusable query definitions, but neither automatically guarantees performance. I still check execution plans for important queries.

---

12. Joins: where data relationships become answers

Joins combine related tables.

Inner join:

SELECT
    s.sales_id,
    c.first_name,
    c.last_name,
    p.model,
    s.sale_amount
FROM sales s
JOIN customers c
    ON c.customer_id = s.customer_id
JOIN products p
    ON p.product_id = s.product_id;

Left join:

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    s.sales_id
FROM customers c
LEFT JOIN sales s
    ON s.customer_id = c.customer_id;

This keeps customers even if they have no sales.

The question I ask after every join is: what is the grain now?

If one customer has five sales, the customer appears five times.

That may be correct. Or it may accidentally inflate counts.

Bad analytics:

SELECT
    COUNT(c.customer_id) AS customer_count
FROM customers c
JOIN sales s
    ON s.customer_id = c.customer_id;

This counts customer-sales rows, not unique customers.

Better:

SELECT
    COUNT(DISTINCT c.customer_id) AS customer_count
FROM customers c
JOIN sales s
    ON s.customer_id = c.customer_id;

My rule of thumb: joins can multiply rows, so aggregation after a join must respect the resulting grain.

---

13. Set operations: UNION, INTERSECT, EXCEPT

Set operations combine result sets.

UNION combines and removes duplicates:

SELECT customer_id FROM online_customers
UNION
SELECT customer_id FROM dealership_customers;

UNION ALL keeps duplicates:

SELECT customer_id FROM online_customers
UNION ALL
SELECT customer_id FROM dealership_customers;

Use UNION ALL when duplicates matter or when you know there are none and want better performance.

INTERSECT finds overlap:

SELECT customer_id FROM email_campaign_customers
INTERSECT
SELECT customer_id FROM customers_with_sales;

EXCEPT finds one set minus another:

SELECT customer_id FROM email_campaign_customers
EXCEPT
SELECT customer_id FROM customers_with_sales;

I regularly use set operations for cohort analysis, campaign targeting, eligibility rules and reconciliation.

---

14. Aggregation: turning rows into insight

Aggregation answers business questions.

How many customers? How many sales? Total revenue? Average sale amount? Top states? Best products?

Example:

SELECT
    product_type,
    COUNT(*) AS sale_count,
    SUM(sale_amount) AS total_sales,
    AVG(sale_amount) AS average_sale
FROM sales s
JOIN products p
    ON p.product_id = s.product_id
GROUP BY product_type
ORDER BY total_sales DESC;

WHERE filters rows before grouping.

HAVING filters groups after grouping.

SELECT
    customer_id,
    COUNT(*) AS sale_count,
    SUM(sale_amount) AS total_sales
FROM sales
GROUP BY customer_id
HAVING SUM(sale_amount) > 5000;

Be careful with AVG(): it can hide distribution. Two customers can have the same average but completely different behaviours.

Best practice: combine aggregate metrics:

SELECT
    product_type,
    COUNT(*) AS sale_count,
    MIN(sale_amount) AS min_sale,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sale_amount) AS median_sale,
    AVG(sale_amount) AS avg_sale,
    MAX(sale_amount) AS max_sale
FROM sales s
JOIN products p
    ON p.product_id = s.product_id
GROUP BY product_type;

Now you see shape, not just average.

---

15. Window functions: analytics without collapsing rows

Window functions are one of the most important advanced SQL topics.

Aggregation collapses rows.

Window functions calculate across related rows while keeping row detail.

Example: rank sales by customer.

SELECT
    customer_id,
    sales_id,
    sale_date,
    sale_amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS customer_sale_number
FROM sales;

PARTITION BY customer_id means restart numbering for each customer.

Use LAG to compare current row with previous row:

SELECT
    customer_id,
    sale_date,
    sale_amount,
    LAG(sale_amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS previous_sale_amount,
    sale_amount - LAG(sale_amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
    ) AS difference_from_previous
FROM sales;

Rolling total:

SELECT
    customer_id,
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (
        PARTITION BY customer_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

I reach for window functions when I need to compare a row with its neighbours, rank it within a group, calculate a running total or produce a metric without losing row detail.

This is where SQL becomes powerful for analytics.

---

16. Performance: query planner, scans and indexes

Performance work brings query planning, database scans, index scans, B-tree indexes, hash indexes and effective index use into the discussion. This is where SQL knowledge becomes production judgement.

When you write SQL, PostgreSQL does not blindly execute it line by line like a script. It creates a query plan.

Use:

EXPLAIN
SELECT
    customer_id,
    email
FROM customers
WHERE state = 'CA';

Better:

EXPLAIN ANALYZE
SELECT
    customer_id,
    email
FROM customers
WHERE state = 'CA';

EXPLAIN estimates. EXPLAIN ANALYZE actually runs and measures.

Index:

CREATE INDEX idx_customers_state
ON customers(state);

Now queries filtering by state may become faster.

But indexes are not free.

They take storage. They slow inserts and updates. They must be maintained. They may not be used if the query is not selective.

Composite index:

CREATE INDEX idx_sales_customer_date
ON sales(customer_id, sale_date);

Good for:

SELECT *
FROM sales
WHERE customer_id = 123
ORDER BY sale_date DESC;

My rule of thumb: create indexes for real query patterns, not simply because “indexes make things faster.”

Bad performance smells:

SELECT * on large tables. No WHERE clause. No pagination. Filtering after loading into application. Functions around indexed columns. Leading wildcard searches like LIKE '%abc'. Joining large tables without indexes. Sorting huge results unnecessarily. Aggregating after accidental row multiplication.

---

17. JSON and arrays: relational databases can handle semi-structured data

Modern PostgreSQL supports JSON, JSONB and arrays.

This matters because real data is not always perfectly relational.

Example JSONB column:

CREATE TABLE customer_events (
    event_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    event_payload JSONB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Insert:

INSERT INTO customer_events (
    customer_id,
    event_type,
    event_payload
)
VALUES (
    1001,
    'application_submitted',
    '{"loanProductId": 12, "requestedAmount": 25000, "channel": "online"}'
);

Query JSONB:

SELECT
    event_id,
    event_payload ->> 'channel' AS channel,
    (event_payload ->> 'requestedAmount')::numeric AS requested_amount
FROM customer_events
WHERE event_payload ->> 'channel' = 'online';

Arrays:

CREATE TABLE products_with_tags (
    product_id BIGINT PRIMARY KEY,
    model TEXT NOT NULL,
    tags TEXT[]
);

Query:

SELECT *
FROM products_with_tags
WHERE 'electric' = ANY(tags);

My rule of thumb: JSONB and arrays are useful, but I do not use them to avoid proper modelling. If a value is central to filtering, joining, validation or reporting, I consider making it a real column or related table.

---

18. Advanced data types: date, text and geospatial

Date/time analytics is everywhere.

Monthly sales:

SELECT
    DATE_TRUNC('month', sale_date) AS sale_month,
    SUM(sale_amount) AS total_sales
FROM sales
GROUP BY DATE_TRUNC('month', sale_date)
ORDER BY sale_month;

Days between sale and customer creation:

SELECT
    s.sales_id,
    s.sale_date,
    c.created_at::date AS customer_created_date,
    s.sale_date - c.created_at::date AS days_from_signup_to_sale
FROM sales s
JOIN customers c
    ON c.customer_id = s.customer_id;

Text processing:

SELECT
    customer_id,
    LOWER(TRIM(email)) AS normalized_email
FROM customers;

Pattern matching:

SELECT *
FROM customers
WHERE email LIKE '%@gmail.com';

Geospatial thinking might involve latitude and longitude:

SELECT
    dealership_id,
    dealership_name,
    latitude,
    longitude
FROM dealerships
WHERE latitude IS NOT NULL
  AND longitude IS NOT NULL;

Date, text and location data are often messy. Before analysing them, I clean and validate them, then establish how the system handles time zones, formats, casing, missing values and duplicates.

---

19. Inferential statistics using SQL

Inferential statistics brings population versus sample, parameters versus statistics, confidence intervals, hypothesis testing, correlation and regression into the SQL workflow.

This is where SQL supports data science.

SQL can prepare the dataset:

SELECT
    customer_id,
    COUNT(*) AS purchase_count,
    SUM(sale_amount) AS total_sales,
    AVG(sale_amount) AS average_sale
FROM sales
GROUP BY customer_id;

Correlation:

SELECT
    CORR(requested_amount, annual_income) AS income_amount_correlation
FROM loan_applications
WHERE requested_amount IS NOT NULL
  AND annual_income IS NOT NULL;

Regression-style preparation:

SELECT
    annual_income,
    requested_amount
FROM loan_applications
WHERE annual_income > 0
  AND requested_amount > 0;

My rule of thumb: SQL is excellent for preparing clean analytical datasets. Python or R may be better for advanced modelling, but if the SQL dataset is wrong, the model will be wrong.

In data science, garbage in does not become intelligence. It becomes confident nonsense.

---

20. Final case study mindset: staging, quality checks, star schema, delivery

The final step is bringing these skills together in an analytics system: dimensional modelling, data-warehouse architecture, staging, quality checks, loading a star schema and delivering analysis.

This is the professional analytics workflow.

Raw data comes in.

It lands in staging.

Quality checks run.

Clean data loads into analytical structures.

Business users query/report from curated tables.

A star schema usually has fact and dimension tables.

Fact table:

FactSales

Dimensions:

DimCustomer
DimProduct
DimDate
DimDealership

Example:

SELECT
    d.calendar_month,
    p.product_type,
    SUM(f.sale_amount) AS total_sales,
    COUNT(*) AS sale_count
FROM fact_sales f
JOIN dim_date d
    ON d.date_key = f.sale_date_key
JOIN dim_product p
    ON p.product_key = f.product_key
GROUP BY
    d.calendar_month,
    p.product_type
ORDER BY
    d.calendar_month,
    total_sales DESC;

The distinction I want you to remember is this: OLTP systems are designed to run the business; analytical systems are designed to understand the business.

Do not confuse the two.

---

21. The mentoring case study: can we trust the lending portfolio report?

The earlier sections teach the vocabulary. Now let us use it under production pressure.

At 08:30 on Monday, the head of lending opens a dashboard. It says approvals fell 18% last week and average requested amount rose sharply. Finance sees a different total in its monthly extract. Operations says applications are missing. Your task is not to make the chart green. Your task is to determine which question each number answers and whether the underlying data supports it.

Junior: Shall I compare the three SQL queries and find the wrong one?

>

Senior: Yes, but first define the business meaning, grain, time boundary and source of truth. Three syntactically correct queries can answer three different questions.

Write the metric contract:

Weekly approval rate is the count of distinct applications whose first final decision occurred during the reporting week and was Approved, divided by all distinct applications whose first final decision occurred during that week, excluding test tenants and withdrawn-before-decision applications. Reporting time uses the business timezone, while stored event timestamps remain instants.

This definition raises questions immediately. Can an application be decided more than once? What is “first final”? Does a later appeal replace the original outcome? When does a week begin? Are imported historical decisions included? The query cannot settle product policy on its own.

Identify grain before joins

Suppose the operational model contains:

loan_application       one row per application
decision_event         one row per decision event
application_applicant  one row per applicant relationship
document               one row per uploaded document

If you join all four and count rows, an application with two applicants, three documents and two decision events can produce twelve joined rows. COUNT(*) then measures join combinations, not applications.

Write the grain beside each relation:

-- Grain: one row per application with its first final decision.
WITH ranked_decisions AS (
    SELECT
        d.application_id,
        d.outcome,
        d.decided_at,
        ROW_NUMBER() OVER (
            PARTITION BY d.application_id
            ORDER BY d.decided_at, d.decision_event_id
        ) AS decision_sequence
    FROM decision_event AS d
    WHERE d.outcome IN ('Approved', 'Declined')
)
SELECT
    application_id,
    outcome,
    decided_at
FROM ranked_decisions
WHERE decision_sequence = 1;

The secondary ID makes ordering deterministic when timestamps tie. Confirm that this rule matches the domain; perhaps the event stream already has an authoritative sequence.

Junior: Could I use COUNT(DISTINCT application_id) after the large join?

>

Senior: It may repair one count, but other aggregates can still be multiplied and the query does unnecessary work. Establish one row per required fact before joining dimensions.

22. Design the reporting model around facts

Operational schemas optimise business transactions and integrity. Analytical schemas optimise stable business questions over history. Do not point every dashboard at a mutable OLTP graph and hope indexing solves semantics.

For decisions, define a fact at the event grain:

FactLoanDecision
  DecisionKey
  ApplicationBusinessKey
  DecisionDateKey
  TenantKey
  ProductKey
  ApplicantBandKey
  OutcomeKey
  RequestedAmount
  VerifiedIncome
  ProcessingSeconds
  IsFirstFinalDecision
  SourceEventId
  LoadedAt

Measures must match their additivity. RequestedAmount repeated on every decision event cannot be safely summed unless the query filters to an application-level row. Ratios such as approval rate should be calculated from additive numerator and denominator counts, not averaged across precomputed group percentages.

Dimensions give descriptive context:

DimDate
DimTenant
DimProduct
DimApplicantBand
DimOutcome

Surrogate keys allow a warehouse to preserve historical dimension versions. The operational product ID remains a business key used for matching.

Slowly changing dimensions

If a product moves from “Standard” to “Specialist,” should old decisions appear under the old or new category? For historically accurate analysis, a type-2 dimension records effective versions:

CREATE TABLE dim_product (
    product_key       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id        uuid NOT NULL,
    product_name      text NOT NULL,
    product_category  text NOT NULL,
    valid_from        timestamptz NOT NULL,
    valid_to          timestamptz,
    is_current        boolean NOT NULL,
    UNIQUE (product_id, valid_from)
);

This is PostgreSQL syntax. SQL Server would use different identity and timestamp type syntax. The modelling choice is portable: each fact links to the dimension version effective when the event occurred.

Type 1 overwrites descriptive values and suits corrections where history should use the corrected value. Type 2 preserves history and increases storage and loading complexity. Choose per attribute, not per entire dimension by habit.

23. Build an idempotent ingestion path

Data pipelines rerun. Networks fail after a batch commits. A source can resend yesterday's file. Treat replay as normal.

Land raw data immutably with metadata:

CREATE TABLE staging_decision_event (
    ingestion_batch_id uuid NOT NULL,
    source_event_id     uuid NOT NULL,
    source_payload      jsonb NOT NULL,
    source_occurred_at  timestamptz NOT NULL,
    ingested_at         timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (ingestion_batch_id, source_event_id)
);

A second uniqueness rule may be needed on the source event ID across batches if it is globally stable. Do not infer identity from every payload field when the source can provide a durable identifier.

Separate steps:

1. Register the batch and checksum.
2. Load raw records.
3. Validate schema and business rules.
4. Quarantine invalid records with reason codes.
5. Merge valid records into curated facts/dimensions.
6. Reconcile source, accepted, rejected and target counts.
7. Mark the batch complete only after checks pass.

INSERT INTO fact_loan_decision (
    source_event_id,
    application_business_key,
    outcome_key,
    decision_date_key,
    requested_amount,
    loaded_at)
SELECT
    s.source_event_id,
    (s.source_payload ->> 'applicationId')::uuid,
    o.outcome_key,
    TO_CHAR(s.source_occurred_at AT TIME ZONE 'Europe/London', 'YYYYMMDD')::int,
    (s.source_payload ->> 'requestedAmount')::numeric(18,2),
    now()
FROM staging_decision_event AS s
JOIN dim_outcome AS o
  ON o.outcome_code = s.source_payload ->> 'outcome'
ON CONFLICT (source_event_id) DO NOTHING;

This is illustrative PostgreSQL SQL. DO NOTHING prevents a duplicate insert, but it can hide a changed payload under the same ID. Store and compare a canonical payload hash; quarantine a conflicting duplicate instead of silently accepting it.

Reconciliation is part of correctness

For every batch, persist:

• source record count;
• distinct source identifiers;
• accepted and rejected counts;
• inserted, updated and unchanged target counts;
• monetary control totals where meaningful;
• minimum and maximum event times;
• checksum or file identity;
• pipeline version.

The equation should balance. A successful process exit without balanced counts is weak evidence.

24. Data quality rules belong near their meaning

Data quality has several dimensions:

• validity: values conform to allowed shape/range;
• completeness: required facts are present;
• uniqueness: one identity is not duplicated;
• consistency: related values agree;
• timeliness: data arrives within the needed window;
• referential integrity: relationships resolve;
• accuracy: data matches the real-world fact, which often needs external evidence.

SQL can prove that income is non-negative; it cannot prove the applicant reported it truthfully. State what each check actually establishes.

SELECT
    COUNT(*) FILTER (WHERE requested_amount <= 0) AS invalid_amounts,
    COUNT(*) FILTER (WHERE outcome IS NULL) AS missing_outcomes,
    COUNT(*) - COUNT(DISTINCT source_event_id) AS duplicate_events,
    MAX(ingested_at - source_occurred_at) AS maximum_arrival_delay
FROM curated_decision_event
WHERE ingestion_batch_id = :batch_id;

PostgreSQL supports aggregate FILTER. In SQL Server, conditional SUM(CASE WHEN ... THEN 1 ELSE 0 END) is a common equivalent.

Do not delete invalid rows to make a dashboard pass. Quarantine them, record reason and ownership, and decide whether the batch can partially publish. A missing optional demographic category differs from an unparseable decision outcome.

Use database constraints for invariant data at rest: NOT NULL, CHECK, UNIQUE, foreign keys and appropriate types. Pipeline tests and monitoring catch cross-row, freshness and source-contract problems. Application validation improves feedback; it does not replace storage constraints.

25. Write the approval-rate query without lying

After establishing first final decision grain, calculate numerator and denominator together:

WITH first_final AS (
    SELECT
        d.application_id,
        d.outcome,
        d.decided_at,
        ROW_NUMBER() OVER (
            PARTITION BY d.application_id
            ORDER BY d.decided_at, d.decision_event_id
        ) AS rn
    FROM decision_event AS d
    JOIN loan_application AS a
      ON a.application_id = d.application_id
    WHERE d.outcome IN ('Approved', 'Declined')
      AND a.is_test = false
), weekly AS (
    SELECT
        DATE_TRUNC('week', decided_at AT TIME ZONE 'Europe/London') AS week_start,
        COUNT(*) AS decided_count,
        COUNT(*) FILTER (WHERE outcome = 'Approved') AS approved_count
    FROM first_final
    WHERE rn = 1
    GROUP BY DATE_TRUNC('week', decided_at AT TIME ZONE 'Europe/London')
)
SELECT
    week_start,
    approved_count,
    decided_count,
    approved_count::numeric / NULLIF(decided_count, 0) AS approval_rate
FROM weekly
ORDER BY week_start;

Why NULLIF? Division by zero should produce absence rather than crash; product owners must decide how the chart displays a period with no decisions. Why convert timezone before truncating? A Sunday-night UTC instant can belong to Monday in another business timezone. Daylight-saving rules make fixed offsets unsafe.

The query still needs a bounded date predicate for performance and repeatability. Reporting “last week” should be resolved to explicit start/end instants by the job, stored with the report, and queried with a half-open range:

WHERE decided_at >= :start_instant
  AND decided_at <  :end_instant

Half-open intervals avoid double counting at adjacent boundaries. Do not wrap the indexed timestamp column in a function if an equivalent range predicate can preserve index use.

26. Joins, missing data and denominator decisions

An inner join removes facts without a matching dimension. That can make a dashboard look cleaner while understating totals.

Use an “Unknown” dimension member for late or invalid relationships where publishing is allowed. The fact remains countable, and data-quality metrics reveal unknown usage.

SELECT
    COALESCE(p.product_category, 'Unknown') AS product_category,
    COUNT(*) AS decisions
FROM fact_loan_decision AS f
LEFT JOIN dim_product AS p
  ON p.product_key = f.product_key
GROUP BY COALESCE(p.product_category, 'Unknown');

Be careful: a left join followed by WHERE p.is_current = true effectively removes null matches. Put the intended dimension condition in the join or explicitly retain nulls.

Many-to-many relationships require a bridge table and allocation rules. If one application has two applicants, counting applications by applicant characteristic can legitimately place it in two groups, but those group counts will not sum to the application total. State this in the metric definition. Alternatively assign a primary applicant or fractional weighting if the business agrees; SQL cannot choose the ethical/statistical policy.

27. Window functions as an analytical toolkit

Window functions preserve row grain while calculating across related rows.

Running portfolio total

SELECT
    decision_date,
    approved_amount,
    SUM(approved_amount) OVER (
        ORDER BY decision_date, decision_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_approved_amount
FROM daily_approved_decision;

Specify ROWS deliberately. Default frames with tied ordering values can surprise you. Add a deterministic tie-breaker.

Compare with previous period

WITH weekly AS (
    SELECT week_start, approved_count
    FROM weekly_approval_summary
)
SELECT
    week_start,
    approved_count,
    LAG(approved_count) OVER (ORDER BY week_start) AS previous_count,
    approved_count - LAG(approved_count) OVER (ORDER BY week_start) AS change
FROM weekly;

If weeks with no rows are absent, LAG means previous observed week, not previous calendar week. Join to DimDate or a generated calendar series first when continuity matters.

Percentiles and outliers

Average processing time can hide a long tail. PostgreSQL ordered-set aggregates can calculate percentiles:

SELECT
    product_key,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY processing_seconds) AS median_seconds,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY processing_seconds) AS p95_seconds
FROM fact_loan_decision
GROUP BY product_key;

Use a platform-appropriate equivalent on SQL Server. Confirm whether interpolation is acceptable and how nulls/failed workflows are handled.

28. Query plans: read evidence, not folklore

The optimiser estimates cardinality and chooses access/join/aggregate strategies. A plan is shaped by statistics, indexes, parameter values, data distribution, memory and server configuration.

For PostgreSQL, use EXPLAIN for estimates and EXPLAIN (ANALYZE, BUFFERS) when it is safe to execute the statement. ANALYZE runs the query; never use it casually on a destructive statement or expensive production path. SQL Server provides estimated/actual execution plans and runtime statistics through its tooling.

Ask:

• Are estimated and actual row counts far apart?
• Where are most time and reads spent?
• Is a scan appropriate for the proportion returned?
• Did a join spill or receive insufficient memory?
• Is a nested loop repeating expensive inner work?
• Are sorts and hashes operating at expected scale?
• Is implicit conversion preventing index use?
• Is blocking or I/O, rather than the plan, the delay?

A sequential/table scan is not automatically bad. Reading most of a small table through an index can cost more. An index seek is not automatically good if repeated millions of times.

Junior: The plan says 80% cost on a sort. Should I add an index?

>

Senior: Estimated percentages are not elapsed-time measurements. Check actual rows, reads, spills, required ordering and whether the query returns more data than needed before changing schema.

Capture representative parameter values. Skew can produce a plan excellent for one tenant and poor for another. Address statistics, query shape, recompilation/plan options or partitioning only after diagnosing the platform-specific behaviour.

29. Indexes are workload trade-offs

An index can accelerate reads while consuming storage, cache and write work. Design from predicates, joins, ordering and projection.

For the weekly decision query, a possible PostgreSQL index is:

CREATE INDEX ix_decision_event_final_time_application
ON decision_event (decided_at, application_id)
INCLUDE (outcome, decision_event_id)
WHERE outcome IN ('Approved', 'Declined');

This partial index serves final decisions and a time range, but whether column order is right depends on actual selectivity and query shapes. SQL Server calls the analogous concept a filtered index and has different syntax/optimizer behaviour.

Composite index order matters. An index on (tenant_id, decided_at) supports tenant-plus-time access well; it may not serve a global time-only query efficiently. One index cannot optimise every report.

After adding an index, measure read improvement and write/storage effect. Remove redundant indexes only after checking other workloads, constraints and operational uses. A unique constraint may rely on an index even if query telemetry shows few seeks.

Keep predicates sargable where possible:

-- Often prevents a simple timestamp range index from being used effectively.
WHERE DATE(decided_at) = DATE '2026-07-30'

-- Prefer explicit boundaries.
WHERE decided_at >= TIMESTAMPTZ '2026-07-30 00:00:00+01'
  AND decided_at <  TIMESTAMPTZ '2026-07-31 00:00:00+01'

Use correct business-timezone boundaries rather than copying the literal example.

30. Transactions and isolation for trustworthy extracts

A report reading several tables can observe changes at different moments depending on database isolation. If the report must represent one consistent snapshot, define that requirement and use an appropriate database feature/isolation level.

Higher isolation can increase blocking, version-store work or serialization failures. “Use serializable” is not a free correctness switch. Analytical replicas or a warehouse often isolate reporting load from OLTP transactions.

Long-running readers can retain old row versions or interfere with maintenance. Keep transactions bounded. Do not open a transaction while a user inspects a dashboard.

For an incremental pipeline, store a high-water mark only after the target batch commits and reconciles. Timestamp-only watermarks can miss rows with equal timestamps or late arrivals. Use a compound cursor such as (occurred_at, event_id), ingestion sequence, change-data-capture position or intentional overlap with idempotent merge.

Late events require a policy. Reprocess a rolling window, update affected aggregates and record when a previously published period changes. Financial close may freeze a period and route late adjustments separately. The database cannot infer governance.

31. SQL from .NET: parameterisation and cancellation

Use parameters for values, not string interpolation:

const string sql = """
    SELECT application_id, outcome, decided_at
    FROM reporting.first_final_decision
    WHERE decided_at >= @Start
      AND decided_at < @End
      AND tenant_id = @TenantId
    ORDER BY decided_at, application_id;
    """;

await using var command = connection.CreateCommand();
command.CommandText = sql;
AddParameter(command, "Start", start);
AddParameter(command, "End", end);
AddParameter(command, "TenantId", tenantId);

await using var reader = await command.ExecuteReaderAsync(cancellationToken);

Parameters prevent SQL injection for values and improve type handling. They cannot parameterise arbitrary table names or sort directions. Map those from a fixed allow-list.

Match .NET and database types deliberately. Sending a string for a numeric or timestamp column can cause conversion errors or poor plans. Use decimal with agreed precision for money-like values; define rounding at business boundaries.

Stream large results rather than calling ToListAsync on millions of rows. But streaming holds a connection and reader open; bound the operation, apply backpressure and dispose on cancellation. For downloadable reports, an asynchronous export job to durable object storage may be more reliable than a long HTTP request.

With EF Core, project required fields and inspect generated SQL. Raw SQL is appropriate for some analytical queries, but parameterise it and keep its contract tested. A repository method that hides every query behind GetAll() is actively harmful here.

32. Privacy, security and ethical analytics

Lending data is sensitive. Grant the reporting identity access only to approved views/columns. Separate direct identifiers from analytical attributes. Encrypt transport and storage according to policy, and audit access to high-risk datasets.

Pseudonymisation replaces identifiers but does not make data anonymous. Rare combinations of postcode, age and product can re-identify a person. Minimise attributes and apply disclosure controls to small groups.

Do not export production data to a developer laptop because pandas is convenient. Use governed environments, masked/synthetic data and approved retention. CSV files copied into chat or email become uncontrolled databases.

Metrics can encode unfair policy. Compare approval rates only with appropriate denominators and context; correlation does not prove discrimination or causation, and aggregate differences can reverse within subgroups. Involve domain, risk, legal and responsible-AI expertise where decisions affect people.

Logs and query histories may retain literals. Parameterisation helps but does not remove every exposure. Review database monitoring, BI caches, extracts, notebooks and backups as part of the data lifecycle.

Junior: Can we hash the applicant ID and call the dataset anonymous?

>

Senior: No. A stable unsalted or reversibly linked identifier remains personal data in many threat models, and the surrounding attributes may identify someone. Define the attacker, linkage and governance rather than relying on a label.

33. Diagnose the Monday dashboard incident

Now return to the three conflicting totals.

Step one: freeze definitions and parameters

Capture query text/version, data-source environment, timezone, start/end instants, tenant filters, refresh time and dashboard cache state. Do not rerun “last week” after the boundary moves and compare it with an earlier extract.

Step two: reconcile by grain

Produce counts at each boundary:

SELECT
    COUNT(*) AS event_rows,
    COUNT(DISTINCT application_id) AS applications,
    COUNT(DISTINCT source_event_id) AS source_events,
    MIN(decided_at) AS earliest,
    MAX(decided_at) AS latest
FROM first_final_decision
WHERE decided_at >= :start_instant
  AND decided_at <  :end_instant;

Compare source events, staging, curated fact and published aggregate. Break differences into missing, duplicate, changed and excluded identifiers using EXCEPT or anti-joins.

Step three: test likely hypotheses

• Finance groups by application creation date while the dashboard uses decision date.
• Operations includes referred applications; the rate denominator does not.
• An applicant/document join multiplied requested amounts.
• Sunday UTC events fall into a different local week.
• A late batch arrived after one report refreshed.
• Test tenants or withdrawn cases differ between filters.
• A type-2 dimension join matched two overlapping versions.

Test each with a focused query. Do not patch totals with a constant.

Step four: repair and prevent

Suppose the primary defect is a join to application_applicant before summing requested amount, plus a refresh-time difference. Correct grain, publish a metric contract, add a uniqueness test for fact grain, reconcile refresh watermarks and display data-as-of time on the dashboard.

Backfill affected periods with approval and audit. Tell consumers what changed and why. Data incidents require communication, not only a corrected query.

34. Test SQL as production code

Keep small fixture datasets that expose edge cases:

• two decisions at the same timestamp;
• no decisions in a week;
• two applicants and several documents;
• a late-arriving event;
• duplicate event ID with same payload;
• duplicate event ID with changed payload;
• missing dimension member;
• event on a daylight-saving transition;
• null optional data;
• overlapping dimension validity windows.

Assert rows and grain, not only a total. Property-style checks are valuable:

-- Must return no rows: one first-final fact per application.
SELECT application_business_key, COUNT(*)
FROM fact_loan_decision
WHERE is_first_final_decision
GROUP BY application_business_key
HAVING COUNT(*) > 1;

Run migration and query tests on the actual database engine/version. SQLite is not PostgreSQL or SQL Server: types, null ordering, isolation, optimiser and functions differ.

For performance, retain representative scale and distribution. A plan on 100 uniform rows proves little about 100 million skewed rows. Set regression thresholds around reads, time and plan characteristics carefully; shared CI infrastructure introduces noise.

Review SQL like application code: formatting, naming, parameter contracts, transaction scope, permissions, rollback and observability. Version views and transformations. Peer review metric definition with the business owner.

35. Operate the analytical pipeline

Useful pipeline signals include:

• ingestion success and duration;
• source-to-curated reconciliation difference;
• data freshness/maximum event age;
• rejected rows by bounded reason code;
• unknown dimension rate;
• duplicate/conflicting event rate;
• fact load and report query latency;
• warehouse storage and spill/resource pressure;
• published dataset version and as-of watermark.

Avoid putting batch IDs or tenant IDs with unbounded cardinality into metric labels. Put high-cardinality identifiers in structured logs and traces.

Alerts should map to user impact. A pipeline ten minutes late may be acceptable overnight and critical before a regulatory submission. Define service-level objectives for freshness and correctness, not only job completion.

Create a runbook: how to pause publication, inspect quarantine, restart a batch, replay from a watermark, rebuild a dimension, validate backfill and notify consumers. Make replay idempotent and practise it.

Separate production publication from development exploration. Analysts need freedom to investigate, but a notebook cell should not silently become the official metric. Promote reviewed transformations into a versioned pipeline with ownership.

36. Statistics: describe uncertainty honestly

The dashboard's 18% fall may mean several things. It could be a real policy or population change, ordinary variation, a small denominator, delayed data or a definition defect. SQL can calculate summaries; interpretation requires statistical and domain judgement.

Always show counts beside rates:

SELECT
    product_key,
    COUNT(*) AS decided_count,
    COUNT(*) FILTER (WHERE outcome_code = 'Approved') AS approved_count,
    COUNT(*) FILTER (WHERE outcome_code = 'Approved')::numeric
        / NULLIF(COUNT(*), 0) AS approval_rate
FROM decision_analysis
GROUP BY product_key;

An approval rate changing from 50% to 25% means something different when the denominator is four rather than forty thousand. Apply small-number disclosure rules where data is sensitive.

Mean, median and distribution

Average requested amount can rise because a few very large applications arrived. Report median, percentiles and a distribution or bands where useful. Inspect invalid values before calculating them.

Do not remove outliers solely because they are inconvenient. An outlier may be fraud, a currency/unit error or a valid specialist product. Create a documented rule, preserve excluded counts and compare results with/without exclusions.

Correlation is not causation

CORR(verified_income, requested_amount) describes linear association among included rows. It does not prove income causes an amount, explain selection into the dataset or account for confounders. Missingness can bias the sample. Aggregated correlation may differ from within-product relationships.

Before presenting an inference:

1. State population and sampling mechanism.
2. Inspect missing-data pattern.
3. Define exposure, outcome and confounders.
4. Visualise distributions and nonlinear relationships.
5. Report effect size and uncertainty, not only a p-value.
6. Validate assumptions and sensitivity.
7. Separate exploratory from confirmatory analysis.

Use Python/R or a validated statistics platform for models when appropriate, but generate the analytical cohort reproducibly. Version SQL, parameters and source watermark. A sophisticated model trained on a duplicated join is still wrong.

Cohort and selection bias

Approval rate among completed decisions excludes abandoned applications. Processing-time analysis excludes cases still open unless censored-duration methods are used. Comparing products without controlling for applicant mix can attribute selection effects to policy.

The metric contract should name exclusions and limitations. A dashboard footnote is not an embarrassment; it is part of scientific honesty.

Junior: Can SQL tell us whether the policy caused the fall?

>

Senior: SQL can construct and summarise evidence. Causal attribution needs a credible design—randomisation or justified observational methods—and domain review. A before/after chart alone does not provide it.

37. Materialised summaries, caching and freshness

The weekly query may be correct but too expensive to run interactively over years of event data. Choose among live query, indexed view/materialized view, aggregate table, semantic-model cache or pre-generated export according to freshness and workload.

A materialized view stores query results and needs refresh:

CREATE MATERIALIZED VIEW reporting.weekly_decision_summary AS
SELECT
    decision_date_key / 100 AS decision_month_key,
    tenant_key,
    product_key,
    COUNT(*) FILTER (WHERE is_first_final_decision) AS decided_count,
    COUNT(*) FILTER (
        WHERE is_first_final_decision
          AND outcome_code = 'Approved') AS approved_count,
    SUM(requested_amount) FILTER (
        WHERE is_first_final_decision) AS requested_amount
FROM reporting.fact_loan_decision
GROUP BY decision_date_key / 100, tenant_key, product_key;

That grouping expression is only illustrative; a proper date-dimension month key is clearer. Refresh behaviour and concurrent-read options are PostgreSQL-specific details to verify. SQL Server indexed views impose particular determinism and schema requirements; an aggregate table maintained by the pipeline may be simpler.

Precomputation introduces another source of truth. Store build watermark, transformation version and row/control totals. Rebuild from facts when logic changes rather than hand-editing summary rows.

Cache keys must contain meaning

A result cache key may need tenant, metric version, timezone, date range, product filters, user authorisation scope and source watermark. Omitting a security dimension can leak data; omitting definition version can serve old logic after release.

Define expiration and invalidation. Time-to-live alone may be acceptable for exploratory dashboards but not for a close report that promises a fixed snapshot. Display “data as of” so users understand freshness.

Avoid cache stampedes when many users request an expired expensive report. Coordinate refresh, serve a bounded stale result when the product permits, or generate asynchronously. Instrument hit rate, load time and staleness.

38. Partitioning and retention at scale

Partitioning divides one logical table into physical sections, often by date. It can support pruning, retention and maintenance, but it adds design constraints and does not repair an inefficient query by itself.

For event facts, monthly range partitions may align with common time predicates and retention. Queries must include partition-compatible bounds. Too many tiny partitions increase planning and administration overhead; one enormous active partition can remain a hotspot.

Choose keys from workload and lifecycle:

• Date partitioning supports period queries and dropping expired data.
• Tenant partitioning may isolate very large tenants but creates skew and many partitions.
• Hash partitioning distributes keys but is less convenient for time retention.

Primary/unique constraints and foreign keys can have engine-specific restrictions on partitioned tables. Verify against the exact PostgreSQL or SQL Server version before designing identity guarantees.

Retention is not DELETE eventually

Define retention for raw landing data, staging, curated facts, dimensions, aggregates, quarantine, audit logs, extracts, backups and downstream BI caches. Dropping an old partition can be efficient, but only after legal hold and business requirements are checked.

Deletion requests in analytical systems are difficult because identifiers are copied and transformed. Maintain lineage so affected records can be found. Decide whether aggregates must be recomputed and how deletion is represented in immutable audit evidence.

Archive is not deletion. Encryption key destruction, object lifecycle rules and backup expiry may be part of the policy, with legal/security approval.

Partition maintenance

Create future partitions before data arrives. Alert on writes falling into a default partition. Gather statistics and maintain indexes. Test attach/detach and recovery. A partitioning scheme without automation becomes a calendar-triggered incident.

39. JSON: flexible landing, governed curation

JSON is useful when source contracts evolve or raw payload preservation supports replay. It should not become an excuse to avoid modelling stable analytical fields.

Extract frequently queried facts into typed columns. Keep the raw payload for lineage under retention controls:

SELECT
    source_event_id,
    source_payload ->> 'outcome' AS outcome_text,
    NULLIF(source_payload ->> 'requestedAmount', '')::numeric(18,2)
        AS requested_amount
FROM staging_decision_event
WHERE source_payload ? 'applicationId';

The cast can fail for malformed data; a production pipeline needs safe validation/quarantine before typed conversion. PostgreSQL JSON operators differ from SQL Server JSON functions.

Validate schema version and required fields. Unknown optional fields can be preserved, while unknown decision outcomes should not default to Approved or disappear. Record parsing error codes without logging complete sensitive payloads.

JSON indexes can accelerate containment/path queries but are larger and workload-specific. If every report repeatedly extracts the same property, that property belongs in a typed curated column. Types, constraints and statistics make data quality and optimisation more reliable.

Arrays can represent a genuinely atomic multi-valued attribute for some PostgreSQL workloads, but many-to-many business relationships usually deserve a bridge table. Ask whether individual values need constraints, joins, history or independent attributes. If yes, normalise.

40. Semantic layers and metric ownership

When every analyst defines approval rate in a dashboard formula, disagreement is guaranteed. Create a governed semantic layer or curated views where metric logic, dimensions and ownership are explicit.

One approach is a stable view:

CREATE VIEW reporting.v_first_final_decision AS
SELECT
    f.application_business_key,
    f.decision_date_key,
    f.tenant_key,
    f.product_key,
    f.outcome_code,
    f.requested_amount,
    f.processing_seconds,
    f.loaded_at
FROM reporting.fact_loan_decision AS f
WHERE f.is_first_final_decision;

Grant consumers the view rather than base tables where appropriate. Document grain, filters, freshness, owner and compatibility. A view provides abstraction, not automatic performance or version safety.

Metrics need namespaced versions when meaning changes. If “approval rate” begins excluding manual overrides, either backfill history under the new definition or publish a new metric version. Do not silently splice two definitions into one time series.

Maintain a catalogue with:

• business description and formula;
• grain and dimensions;
• owner and approver;
• source lineage;
• refresh objective;
• exclusions and known limitations;
• data classification;
• tests and reconciliation;
• change history and deprecation.

Junior: Isn't this bureaucracy for a SQL query?

>

Senior: It is lightweight governance for a number used to make decisions. The cost is small compared with executives acting on incompatible definitions.

Self-service remains possible. Provide trusted building blocks and a sandbox for exploration. Label exploratory outputs clearly and require review before they become operational KPIs.

41. Backup, restore and reproducibility

Backups are proven only by restoration. Define recovery point and recovery time objectives for warehouse data, metadata, transformation code and published reports.

If curated facts can be rebuilt from immutable raw events, test the rebuild duration and whether source retention supports it. Dimensions may contain corrections or mappings not reproducible from source; back them up and audit changes. Pipeline configuration, schema migrations, secrets references and orchestration state also matter.

Run restore exercises into an isolated environment:

1. Restore the database or recreate from raw sources.
2. Apply the exact transformation version.
3. Reconcile counts and control totals.
4. Validate permissions and masking.
5. Rebuild materialised summaries/caches.
6. Compare known reports at a fixed watermark.
7. Record actual recovery time and gaps.

A restored database that cannot reproduce the published regulatory extract is incomplete recovery. Keep report parameters, dataset version and output checksum where policy requires.

Disaster recovery can create duplicated ingestion if both sites process the same source. Stable event IDs and idempotent merge make failover safer. Decide which site owns publication and how split-brain is prevented.

42. Review query safety before execution

Senior developers inspect operational impact, especially for ad hoc production SQL.

For reads:

• add a bounded predicate and row limit while exploring;
• estimate/inspect the plan before a large execution;
• understand replica lag and whether stale data is acceptable;
• set an appropriate statement timeout;
• avoid holding a transaction open in the query tool;
• protect sensitive result exports.

For changes:

• identify exact rows with a SELECT first;
• capture counts and a recoverable plan;
• use a transaction only when its duration/locking is safe;
• batch large updates/deletes;
• monitor log/WAL growth, replication lag and locks;
• reconcile after commit;
• never assume rollback remains cheap after touching millions of rows.

Parameterised application queries remain preferable to manual production changes. If an emergency correction is required, peer review the script, record approval, make it restartable where possible and convert the lesson into a supported tool or migration.

Never run EXPLAIN ANALYZE on an UPDATE or DELETE merely to see the plan unless you fully understand that it executes the statement and have a safe transaction/rollback design. Use estimated plans or a controlled copy first.

43. Communicate an analytical incident

A good incident update separates fact, impact, uncertainty and action:

“The weekly approval dashboard published at 07:00 overcounted requested amount for applications with multiple applicants. Approval counts were unaffected. Finance extracts use a separate application-grain dataset and remain correct. We paused dashboard refresh, identified reports using the affected view, and are rebuilding weeks beginning 6 and 13 July from source event watermark X. The next update is at 11:00.”

Avoid saying “the database was wrong” when transformation logic was wrong. Avoid announcing a corrected percentage before reconciliation is complete.

After recovery, publish:

• affected metrics, periods and consumers;
• detection and incident timeline;
• technical and semantic causes;
• reconciliation evidence;
• corrected dataset version;
• prevention in tests, governance and monitoring;
• remaining uncertainty.

Data trust is earned through transparent correction. Hiding a small discrepancy teaches users to maintain shadow spreadsheets.

44. Mentoring exercises

Exercise one: find the join explosion

Create five applications with different applicant, document and decision counts. Write the tempting four-table query, predict its row count before running it, then correct the grain. Explain why SUM(DISTINCT requested_amount) is not a valid general repair—two applications can request the same amount.

Exercise two: build an idempotent batch

Load the same source file twice. Prove target counts and control totals remain unchanged. Then alter one payload under the same event ID and prove the pipeline quarantines the conflict instead of hiding it.

Exercise three: inspect a plan

Generate skewed tenant data, run a bounded weekly query and capture the actual plan. Record estimates, actual rows, reads and time. Add one candidate index, repeat the measurement, and calculate its storage/write cost. Keep it only if the workload benefits.

Exercise four: model history

Change one product category halfway through the dataset. Implement type-1 and type-2 alternatives and show how the same historical report changes. Ask the product owner which answer is intended.

Exercise five: incident game day

Inject a late batch, one duplicated event and an overlapping dimension version. Give a colleague the dashboard discrepancy and runbook. They must identify each cause, prevent publication, repair/replay and provide reconciliation evidence.

45. A worked metric code review

Imagine this pull request:

SELECT
    DATE(created_at) AS day,
    AVG(CASE WHEN d.outcome = 'Approved' THEN 1 ELSE 0 END) AS approval_rate,
    SUM(a.requested_amount) AS requested_amount
FROM loan_application a
JOIN decision_event d ON d.application_id = a.application_id
JOIN application_applicant aa ON aa.application_id = a.application_id
WHERE created_at >= CURRENT_DATE - 30
GROUP BY DATE(created_at);

It is compact, plausible and unsuitable for approval without revision.

Review one: meaning

The title says approval rate, but the date is application creation rather than first final decision. Every decision event enters the denominator, including referrals and repeated decisions. CURRENT_DATE - 30 creates a moving window whose timezone and inclusive boundary are unclear. Ask the product owner to approve the metric contract before refining syntax.

Review two: grain

The applicant join duplicates an application for joint applicants. Multiple decision events multiply it again. Requested amount is repeated and summed at a lower grain. The average expression averages event/applicant join rows, not applications.

Remove unrelated joins. Build a first-final-decision relation with one row per application. Join only dimensions required for the output. Put a grain comment and an automated uniqueness assertion beside it.

Review three: null and category behaviour

An inner join excludes applications with no decision. That might be correct if the metric is “among decided applications,” but it must be stated. An outcome outside Approved/Declined contributes zero to the original average, silently counting it as not approved. Filter the denominator explicitly.

If requested_amount can be null, SUM ignores it while counts may include the row. Report missing amount count so totals do not imply completeness.

Review four: time

Resolve a reporting week to explicit UTC instants using the approved business timezone. Filter the raw indexed timestamp with >= start and < end. Group through a date dimension or a timezone-aware derived local date. Include the resolved boundaries in report metadata.

Review five: performance

After correcting semantics, capture the actual plan with representative tenant and date distribution. Look for row-estimate error, repeated joins, sorting, spills and reads. Consider an index only after seeing stable predicates. Do not optimise the incorrect query because it is faster.

Review six: security and publication

Confirm the reporting identity can see only permitted tenants and columns. Parameterise bounds and tenant. Prevent a dashboard user from supplying arbitrary order-by SQL. Display data watermark and metric version. Cache by authorisation scope.

Review seven: test evidence

Create fixtures with one joint application, repeated decisions, a referral, a null amount, a decision exactly at each boundary and a week with no decisions. Assert the first-final rowset, numerator, denominator and amount separately. Run on the real database engine.

The review comment can now be precise:

“This query currently has event-by-applicant grain, so joint applications and repeated decisions multiply both the rate and requested amount. The agreed metric requires one row per application's first final decision using decision time in the business timezone. Please isolate that grain in a tested CTE/view, use explicit half-open parameters, expose missing-amount count, and attach the actual plan from representative data.”

That comment identifies consequence, desired contract and evidence. It teaches more than “fix join.”

46. Senior review checklist

Before approving an analytical change, ask:

• Is the business metric written in plain language?
• Is the row grain explicit at every CTE and table?
• Do joins preserve or intentionally change that grain?
• Are numerator, denominator and exclusions visible?
• Are time boundaries explicit, half-open and timezone-correct?
• Are null and unknown members handled deliberately?
• Are source identifiers and replays idempotent?
• Do batch counts and control totals reconcile?
• Does the database enforce stable invariants?
• Has SQL been tested on the real engine and representative data?
• Does the actual plan support the performance claim?
• Is the index justified across reads and writes?
• Are privacy, least privilege and retention addressed?
• Can operators pause, replay, reconcile and explain a correction?
• Does the dashboard display dataset version and freshness?

If the answer to “what does one row mean?” is vague, stop. Every aggregate rests on that answer.

47. Continue the learning path

Read SQL Server and Relational Databases for deeper platform administration and query behaviour, EF Core Best Practices for application access, and How to Investigate Slow Angular, ASP.NET Core and SQL Server Applications for end-to-end performance diagnosis. The HTTP and Web APIs guide helps when delivering datasets, while Web Security develops the trust-boundary analysis used for exports and identifiers.

PostgreSQL and SQL Server share relational foundations but differ in types, functions, plans, indexing features, bulk loading and operational tooling. Keep the concepts portable and label code by dialect. Verify syntax and behaviour against the exact engine/version used in production.

Teach it back before you move on

Give a colleague the portfolio dashboard without showing your SQL. In five minutes, explain:

1. the business definition of approval rate;
2. the grain of the authoritative fact;
3. why the time interval is half-open and timezone-aware;
4. why requested amount cannot be summed after an applicant join;
5. how duplicate and late events are reconciled;
6. what an actual plan would prove—and what it would not;
7. how a metric correction reaches dashboard users;
8. how privacy limits dimensions and small groups.

Then let the colleague challenge one assumption. Perhaps appeals should replace original decisions, a reporting week follows a different calendar, or joint applicants require a different population definition. Trace that change through the metric contract, warehouse grain, transformation, tests, historical backfill, semantic version and communication. A mature design makes the cost and consequence visible.

Run one final evidence exercise. Select a fixed reporting interval and record:

• source event count and checksum;
• staging accepted/rejected/conflicting counts;
• curated fact count at the declared grain;
• aggregate numerator, denominator and control total;
• source and publication watermarks;
• query/transformation version;
• actual plan summary and runtime;
• permission identity used to publish.

Hand those records to someone who did not build the pipeline. They should be able to reproduce the number or identify exactly which dependency prevents reproduction. If they must ask which hidden spreadsheet filter you used, the metric is not yet governed.

This teach-back is the senior threshold: not merely producing an answer, but making its meaning, lineage, uncertainty, performance and recovery inspectable by another professional. The goal is not to make every query complicated. It is to ensure that an important simple-looking number remains truthful when data is late, duplicated, incomplete, large and used by people who cannot see the SQL behind it.

Keep a short decision log beside the metric. Record rejected alternatives too: why application creation date was not used, why first final decision was selected, why a particular timezone governs, and why small cohorts are suppressed. Future maintainers then distinguish intentional policy from accidental SQL. Revisit the log when a product, regulation, source contract or population changes. A metric that was correct last year can become misleading when its business context changes, even if every test and query still passes. Ownership includes recognising when yesterday's definition no longer answers today's question responsibly.

---

What I want you to take away

If I had to summarise this mentoring session in one message, I would say this:

SQL for analytics is not just writing SELECT statements. It is the discipline of modelling reality, protecting data integrity, moving data safely, transforming messy records into meaningful datasets, joining tables without corrupting grain, aggregating without lying, using window functions to understand row relationships, indexing for performance, handling semi-structured data when needed, preparing datasets for statistics, and delivering business insight through reliable analytical systems.

Knowing how to write a query is only the starting point. I want you to ask: What question am I answering? What is the grain? Which data is relevant? What relationships exist? What quality problems might be hidden here? Which transformations are justified? Will this query still work when the table contains 50 million rows?

If I were mentoring you through these topics, I would revise them in this order:

Data modelling first. Table design second. SELECT and filtering third. Transformations fourth. Joins fifth. Aggregation sixth. Window functions seventh. Performance eighth. JSON, arrays, dates, text and geospatial ninth. Statistics and case-study analytics last.

When you can code review SQL from all those angles, you are no longer just “someone who knows SQL.”

You become the developer who protects the truth of the system.