Week 9 - SQL for Analytics

SQL for Analytics

Joins, CTEs, and Aggregations

Data Validation Queries

OLAP, OLTP & Warehouses

Data Modeling Concepts

Building SQL Views

Practice

Assignment

Gotchas & Pitfalls

Glossary

Career: SQL for Analytics

Going Further

Slides (PDF)

Assignment

The Scenario

The NYC Taxi trips are loaded into PostgreSQL, but the analytics team can't use them yet. The raw table has negative fares, missing zone references, and column names that only make sense if you wrote the ingestion script. Your job: audit the raw data, clean up what you can through views, and hand the team a star schema they can query with confidence.

The Assignment

This assignment focuses on analytics engineering fundamentals: moving from raw data to the data structure used by analysts.

You will audit data quality, design a dimensional model, and implement that model using SQL Views. This layer serves as the "source of truth" for all downstream reporting.

By the end of this assignment, you should be able to:

• Audit raw data: Write defensive SQL to identify dirty data (nulls, duplicates, outliers).
• Dimensional modeling: Translate raw tables into a fact and dimension structure.
• Implement SQL Views: Use CREATE VIEW to abstract complexity for end-users.
• Data documentation: Define the grain and metadata of your analytical models.

<aside> ⚠️ Read the raw data from the shared, read-only nyc_taxi schema (nyc_taxi.raw_trips, nyc_taxi.raw_zones); every query in this assignment already uses that prefix. The views you create must live in your own schema (your personal login already defaults to it), never in nyc_taxi or public.

</aside>

Task 1: Data Quality Audit

Before modeling, you must understand the state of your nyc_taxi.raw_trips data: the same dataset used throughout the week. Write a single SQL script validation_queries.sql that answers the following:

1. Duplicate check: Are there any rows with the exact same vendor_id, pickup_datetime, and dropoff_datetime?
2. Null integrity: Count how many rows have a NULL pickup_location_id or dropoff_location_id.
3. Range validation: Find the minimum and maximum fare_amount. Are there negative values?
4. Relationship check: Are there any pickup_location_id values in the trips table that do not exist in the nyc_taxi.raw_zones table?

Task 2: Design the Star Schema

Organize the NYC Taxi data into a star schema to make it easier to query.

• Fact table: vw_fact_trips (the quantitative events)
• Dimension table: vw_dim_zones (the descriptive attributes)

Create a script schema_setup.sql with the following CREATE OR REPLACE VIEW statements (using CREATE OR REPLACE lets you re-run the script while you iterate, without dropping the view first):

• vw_dim_zones: Create a view based on nyc_taxi.raw_zones. Treat location_id as the primary key.
• vw_fact_trips: Create a view based on nyc_taxi.raw_trips.
• Filter: Exclude rows where fare_amount is less than 0.
• Cast: Explicitly cast pickup_datetime as a TIMESTAMP.
• Join-ready: The view must be joinable to vw_dim_zones on location_id. Verify with a test SELECT COUNT(*) FROM vw_fact_trips f JOIN vw_dim_zones d ON f.pickup_location_id = d.location_id after creating it.

<aside> 💡 Why Views? Views save storage and ensure that if the underlying nyc_taxi.raw_trips data is updated, your star schema reflects those changes instantly without a manual refresh.

</aside>

Task 3: Document the Data Dictionary

Create a file named data_dictionary.md. For both views, document:

• The grain: What does a single row represent? (e.g., "One row per taxi trip".)
• The keys: Identify the primary keys and foreign keys.
• Measures: List the columns that can be aggregated (e.g., fare_amount, passenger_count).

<aside> 💡 Using AI to help: Ask an LLM to review your grain statement and check whether your key definitions are consistent with a proper star schema. Then verify its answer against your actual view columns. (⚠️ no real customer data, no PII)

</aside>

Task 4: Verification Queries

Query your new views to answer these specific questions. Save the queries in verification_results.sql with a comment above each one labeling the question it answers.

<aside> 💡 Borough names and zone names live in vw_dim_zones, not in vw_fact_trips. For borough- or zone-level breakdowns, join on pickup_location_id = location_id.

</aside>

1. Volume: How many total rows are in vw_fact_trips? How many rows per borough? What is the most common pickup/dropoff location combination?
2. Revenue: Which pickup zone (name, not ID) generated the highest total fare_amount? Which pickup zone collected the highest total fare_amount on any single day?
3. Geospatial: What is the total number of trips and average trip_distance for each borough?
4. Time patterns: Which day of the week had the highest total tip_amount? What hour of the day has the highest average tip?

For question 1, take a screenshot of your query result showing the per-borough row counts. Save it as assets/borough_count.png.

Task 5: AI Assistance Log

Create a file named AI_ASSIST.md. While completing tasks 1-4, document one session where you used an LLM to help with a query or a design decision. Fill in these four sections:

• The problem: What were you trying to solve? Paste the relevant SQL or schema fragment.
• The prompt: What did you ask the AI? Include the context you provided.
• The response: What did it suggest? Did it work first try?
• Reflection: Did you understand why the suggestion worked, or did you accept it blindly?

<aside> ⚠️ Never paste real customer data or PII into an LLM. The NYC Taxi dataset used here is public data, safe to paste.

</aside>

Deliverables

Your starter files live in the data-assignment-week-9 repository. Fill in each file (keep the names and the repo-root layout):

data-assignment-week-9/
├── validation_queries.sql       (Task 1: validation queries)
├── schema_setup.sql             (Task 2: CREATE VIEW statements)
├── data_dictionary.md           (Task 3: grain, keys, measures)
├── verification_results.sql     (Task 4: verification queries)
├── assets/
│   └── borough_count.png        (Task 4: screenshot of borough query result)
└── AI_ASSIST.md                 (Task 5: LLM session log)

Technical requirements

• Database: All views must be created in your assigned schema in the Azure PostgreSQL instance.
• Syntax: Use standard PostgreSQL syntax.
• Formatting: SQL keywords capitalized (SELECT, FROM, JOIN), consistent indentation.

How you will be evaluated

Your teacher reviews the whole submission, not a checklist of points. What matters is that the work is real: queries that run, findings that match the actual data, and views that do what they claim. Here is what the teacher looks for in each task:

• Task 1, Data Quality Audit: All four queries run without error, and your findings match what is actually in nyc_taxi.raw_trips. The teacher checks that you reported the issues that genuinely appear in the raw data (negative fares, duplicate trips, null pickup IDs, and orphaned pickup zones) and correctly noted the counts of each.
• Task 2, Star Schema Views: Both views live in your own schema. vw_fact_trips filters out the negative fares and casts pickup_datetime to a timestamp, and the join-readiness test runs cleanly against vw_dim_zones.
• Task 3, Data Dictionary: The grain is stated precisely for each view (one row per trip, one row per zone), the keys are correctly identified, and the measures list is complete.
• Task 4, Verification Queries: Each query runs and returns a result, borough- and zone-level answers join through vw_dim_zones, and the screenshot shows the per-borough row counts.
• Task 5, AI Assist Log: All four sections are filled, and the reflection shows you understood why the suggestion worked rather than accepting it blindly.

Extra reading

• PostgreSQL CREATE VIEW: official syntax reference
• Kimball dimensional modeling techniques: the canonical source for star schema design
• 10 Data Quality Checks in SQL: practical null, duplicate, range, and referential-integrity checks: the same patterns you apply in Task 1

Submission

You work in your copy of the data-assignment-week-9 repository. Once you have given the assignment your best attempt (or the deadline is approaching):

1. Work on a branch and fill in each deliverable file.
2. Commit and push your branch.
3. Open a Pull Request against the repo's main. An automated completeness check runs on the PR (it confirms you submitted all deliverables; it does not run your SQL). Your teacher grades correctness against the rubric.
4. Share the PR URL with your teacher.

Next up: Introduction to dbt Core, where you replace hand-written SQL views with version-controlled dbt models that run, test, and document themselves automatically.

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.