CS 378: Foundations of Data Warehousing

Course Description

Data in an organization arrives from many systems, in many shapes, and almost none of it is ready to answer the questions people actually ask of it. Data warehousing is the discipline of turning that sprawl into a trustworthy, queryable asset. This course provides a hands-on introduction to designing and building a data warehouse end-to-end, and to making that warehouse legible not only to human analysts, but also to the AI agents that increasingly query it on their behalf. Students will move from raw, heterogeneous sources through ingestion, cleaning, modeling, and semantics to a governed warehouse that an agent can reason over reliably.

Topics to include:

• Warehouse fundamentals: OLTP vs. OLAP workloads, the reference architecture, and the warehouse to lake to lakehouse evolution.
• Ingesting data from structured databases, unstructured documents, and third-party APIs.
• Data cleaning and data quality expressed as reproducible, testable code.
• Dimensional modeling, conformed dimensions, and unified schema design across heterogeneous sources.
• Entity resolution, including blocking, matching, clustering, canonicalization, and evaluation.
• Graph data modeling and knowledge graphs layered over the warehouse, and their effect on LLM question-answering accuracy.
• Data governance principles, including lineage, contracts, and PII handling; governance when the consumer is an agent.
• Exposing the warehouse to agents through the Model Context Protocol, and building evaluated agentic pipelines over it.

Learning Outcomes

By the end of this course, students will be able to:

• Explain how analytical workloads differ from transactional ones, and justify the architectural decisions that follow from that difference.
• Build reproducible ingestion pipelines that load structured, unstructured, and API-sourced data into a layered warehouse.
• Diagnose and repair data-quality defects by writing constraints as executable tests and keeping every fix in version-controlled transformation code, so the cleaning is reproducible rather than hand-applied.
• Design a unified schema across heterogeneous sources, using an explicit grain, conformed dimensions, and surrogate keys.
• Implement an entity resolution pipeline and evaluate it against a labeled gold standard.
• Design a property graph data model and construct a knowledge graph over the warehouse, and articulate how graph structure changes what an LLM can answer correctly.
• Apply governance practices to a system whose consumers include autonomous agents.
• Expose warehouse capabilities through an MCP server and build agent workflows against it, with an evaluation set and cost guardrails.

Prerequisites

CS 429 is a required prerequisite. Students should also be comfortable writing Python and SQL and working with Git. Prior coursework in databases is helpful but not assumed.

Textbooks

Required Textbook

There is no required textbook. All assigned readings are research papers and engineering articles, listed under Course Readings below and linked from Canvas.

Recommended Textbooks

• The Data Warehouse Toolkit, 3rd edition, by Ralph Kimball and Margy Ross (Wiley, 2013).
• Designing Data-Intensive Applications by Martin Kleppmann (O'Reilly, 2017).
• Data Mesh by Zhamak Dehghani (O'Reilly, 2022).

Supplementary Materials

Online documentation, tutorials, and videos (e.g. official project reference material, recorded conference talks, and self-paced online courses).

Course Communication Tools

We will be using the following tools throughout the term:

• Canvas for project submissions and grade reporting.
• Ed Discussion for announcements, questions, and discussions.
• Zoom for office hours.

Tech Stack

Our primary Python and data tech stack consists of the following:

• PySpark (Apache Spark): for distributed ingestion, cleaning, and transformation across the warehouse layers.
• SQL: for analytical query execution over warehouse tables, in Spark SQL and in BigQuery via catalog federation.
• Parquet and Apache Iceberg: for storage and ACID layering, written by Spark through the Lakehouse runtime catalog.
• Pydantic: for schema definition, validation, and structured extraction.
• Neo4j AuraDB and Cypher: for graph data modeling, knowledge graph construction, and graph querying.
• Anthropic API and Claude Code SDK: for extraction, tool use, and multi-agent pipelines.
• MCP: for exposing warehouse tools and resources to agents.

Our primary development and deployment infrastructure consists of the following tools:

• GitHub: for version control and code repository.
• Colab and VS Code: for development and testing.
• Google Cloud (BigQuery, GCS, Managed Spark, Cloud Run): for hosting the warehouse and deploying the MCP server.

Term Project

The coursework centers on one semester-long project in which each group builds a working data warehouse from scratch and then makes it usable by AI agents. The project moves in the same order as the lectures: every technique introduced in class is applied to your own data within a week or two of seeing it. More specifically:

• You will work in groups of two, formed during the first week of class.
• Your group will select a primary dataset and related counterparts, and will justify that choice against explicit evaluation criteria.
• You will ingest from structured, unstructured, and API sources; clean and validate; design a unified schema; resolve entities; design a graph data model and build a knowledge graph; and govern the result.
• You will expose the warehouse through an MCP server and build agents that answer real questions over it, measured against an evaluation set you construct.
• You will produce code repositories, schema and graph model documentation, data cards, an MCP server, agent evaluations, and a final presentation to the class.

The project is divided into 11 milestones, each producing a concrete deliverable:

• M1Selected and justified datasets
• M2Ingestion pipelines for structured, unstructured, and API sources
• M3Cleaned data with executable quality constraints
• M4Unified dimensional schema
• M5Entity resolution pipeline and evaluation
• M6Graph data model for the knowledge graph
• M7Knowledge graph over the warehouse
• M8Governance artifacts: lineage, contracts, and PII policy
• M9MCP server over the warehouse
• M10Single agent with an evaluation set
• M11Multi-agent pipeline

Quizzes

There will be quizzes on most weeks, based on the assigned readings. The quizzes will be done in class, and students are expected to take them by themselves.

Exam

A comprehensive exam will be held during the final week of class. It will consist of three components: questions on the assigned readings, questions covering lecture concepts, and an oral assessment related to your term project.

Code Reviews

Students will participate in regular, live code reviews during class. During these sessions, you should be prepared to answer impromptu questions and clearly explain the logic, design, and progress of your project milestones.

Class Participation

Students are expected to actively participate in discussions and complete hands-on exercises, which will usually take the form of interactive code labs.

Grading Breakdown

• 30%Term Project
• 20%Exam
• 20%Quizzes
• 10%Code Reviews
• 10%Participation
• 10%Final Presentation

The final mapping to letter grades will be determined at the end of the term once all coursework has been graded, and will be based on the overall distribution of scores.

Academic Integrity

This course will abide by UTCS' code of academic integrity.

Generative AI Policy

Your primary goal as a student is to master the course content and develop as a capable engineer. This means engaging deeply with the material, working through assignments, and building your projects with genuine understanding. Generative AI tools can support this learning process, but over-reliance on them often backfires, leaving you with a shallow grasp of the concepts and skills that the course is designed to build. This course asks you to build agents yourself, which makes it more important, not less, that you can account for every line of code you submit.

You may use generative AI to:

• Brainstorm schema designs, blocking strategies, or graph data models for your data.
• Look up syntax for PySpark, SQL, Cypher, or specific libraries.
• Troubleshoot and debug errors in your pipelines, MCP server, or agents.
• Generate boilerplate code to set up your project structure.

You may not use generative AI to:

• Engage in blind generation where you generate your pipeline, schema, or agent without understanding what the code does. You must be able to explain every line of code you submit. During code reviews, oral assessments, or class discussions, "the AI wrote it" is not an acceptable answer.
• Generate an entire milestone from a prompt and submit it as your own work without verifying the underlying data logic, modeling decisions, and correctness. Doing so is a direct violation of academic integrity.

Late Submissions, Extensions, and Make-up Quizzes

You will receive a total of 5 slip days for the semester, which can be used for project milestones, quizzes, and participation assignments.

• Each slip day used extends a project milestone deadline by 24 hours with no penalty, up to a maximum of 3 days late.
• Because milestones are group deliverables, a late milestone submission will deduct slip days from both group members. For example, a milestone submitted one day late costs each group member one slip day.
• For quizzes, using a slip day allows you to make up a missed quiz during the instructor's or TA's office hours, either the same week or the following week. One slip day covers one make-up quiz, regardless of when it is taken.
• For participation assignments, one slip day extends an in-class exercise deadline by 24 hours after the class session.
• Slip days are applied automatically. You do not need to notify the teaching staff to use one; they are calculated based on your submission timestamp in Canvas.

Once you have used all 5 slip days, you will need a doctor's note (or equivalent documentation) to make up a missed quiz or receive an extension on a project milestone or participation assignment. Without proper documentation:

• Late project milestones will incur a 10% grade penalty per day, up to a maximum of 3 days late, after which the milestone receives a zero.
• Missed quizzes and participation assignments will receive a zero.

Students with Disabilities

If you are a student with a disability, or think you may have a disability, and need accommodations, please contact Disability and Access (D&A). Contact and more details are available on D&A's website.

If you are already registered with D&A, please share your accommodation letter with me as early as possible in the semester so we can discuss how your approved accommodations will be implemented in this course.

Course Readings

All readings are freely available online or through the UT Libraries. Quizzes draw on these readings, so complete each one before the class session where it appears on the schedule.

1. Chaudhuri, S. and Dayal, U. (1997). An Overview of Data Warehousing and OLAP Technology. SIGMOD Record.
   microsoft.com/en-us/research/wp-content/uploads/2016/02/sigrecord.pdf
2. Anthropic (2024). Building Effective Agents.
   anthropic.com/engineering/building-effective-agents
3. Armbrust, M. et al. (2021). Lakehouse: A New Generation of Open Platforms that Unify Data Warehousing and Advanced Analytics. CIDR.
   cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf
4. Wickham, H. (2014). Tidy Data. Journal of Statistical Software 59(10).
   jstatsoft.org/article/view/v059i10
5. Golfarelli, M., Maio, D. and Rizzi, S. (1998). Conceptual Design of Data Warehouses from E/R Schemes. HICSS. (UT Libraries proxy link)
   ieeexplore-ieee-org.ezproxy.lib.utexas.edu/stamp/stamp.jsp?tp=&arnumber=649228
6. Christophides, V., Efthymiou, V., Palpanas, T., Papadakis, G. and Stefanidis, K. (2020). An Overview of End-to-End Entity Resolution for Big Data. ACM Computing Surveys 53(6), Article 127, summarized on The Morning Paper.
   blog.acolyer.org/2020/12/14/entity-resolution/
7. Angles, R. (2018). The Property Graph Database Model. AMW, CEUR Workshop Proceedings Vol. 2100.
   ceur-ws.org/Vol-2100/paper26.pdf
8. Sequeda, J., Allemang, D. and Jacob, B. (2023). A Benchmark to Understand the Role of Knowledge Graphs on Large Language Model's Accuracy for Question Answering on Enterprise SQL Databases.
   arxiv.org/pdf/2311.07509
9. Edge, D. et al. (2024). From Local to Global: A Graph RAG Approach to Query-Focused Summarization. Microsoft Research.
   arxiv.org/pdf/2404.16130
10. Dehghani, Z. (2019). Data Mesh Principles and Logical Architecture.
    martinfowler.com/articles/data-mesh-principles.html
11. Anthropic (2025). Code Execution with MCP: Building More Efficient Agents.
    anthropic.com/engineering/code-execution-with-mcp
12. Anthropic (2025). Writing Effective Tools for Agents.
    anthropic.com/engineering/writing-tools-for-agents
13. Anthropic (2025). How We Built Our Multi-Agent Research System.
    anthropic.com/engineering/multi-agent-research-system

Week-by-week Schedule

This schedule is tentative and is subject-to-change based on the needs of the class.

Acknowledgments

The design of this course draws from conversations with colleagues as well as feedback from former TAs and students. Cloud computing resources are provided through the generous support of Google.