Course Description
CAP4774 is the data warehousing course. The Statewide Course Numbering System titles it Data Warehousing and defines it as "an introduction to data warehousing concepts, requirements gathering, design and implementation. Students learn about operational database integration, extraction, transformation, loading of data to historical database system such as operational data store and data warehouse." The statewide prerequisites are a database systems course and, at some institutions, an information systems project course.
Three Florida public institutions carry it, all at 3 credits — ⚠ and one of them has pointed it somewhere noticeably broader:
| Institution | Its title | Credits |
|---|
| Gulf Coast State College | Data Warehousing | 3 |
| Polk State College | Data Warehouse Design and Creation | 3 |
| University of West Florida | Databases for Data Science | 3 |
The subject sits at a point most computing students have not thought about. A transactional database is designed to record events quickly and without contradiction — normalised, write-optimised, answering "what is true right now". ⚠ A data warehouse is designed for the opposite job: answering "what has happened over time, aggregated in ways nobody specified in advance". That difference in purpose drives every design decision in this course, including the deliberate denormalisation that would be a mistake in an operational schema.
Learning Outcomes
Required Outcomes
- Explain why analytical workloads need a separate system from transactional ones, and describe what goes wrong when reporting runs against a production database.
- Distinguish OLTP from OLAP in purpose, schema design, indexing and workload characteristics.
- Gather requirements for an analytical system: identify the business questions, the measures and the grain before designing anything.
- Apply dimensional modelling: facts, dimensions, grain, and the discipline of declaring grain first.
- Design a star schema, and explain when a snowflake schema is justified and when it is not.
- Handle slowly changing dimensions — types 1, 2 and 3 — and choose the right type for a given attribute.
- Design and implement an ETL process: extraction from source systems, transformation and cleansing, loading, and incremental versus full loads.
- Address data quality: profiling, validation, deduplication, conformed dimensions, and reconciling sources that disagree.
- Explain the role of an operational data store, a staging area and a data mart, and how each differs from the warehouse.
- Write analytical SQL: aggregation, window functions, grouping sets, and queries over large fact tables.
- Apply performance techniques appropriate to analytics — partitioning, indexing strategy, materialised aggregates, columnar storage.
- Describe metadata, lineage and governance, and explain why an unexplained number destroys trust in a warehouse.
- Connect a reporting or visualisation tool and validate that its output matches the source.
Optional Outcomes
- Compare the Kimball and Inmon architectural approaches and argue for one in a given context.
- Work with cloud warehouse platforms — Snowflake, BigQuery, Redshift, Synapse.
- Address data lakes, lakehouse architecture and the ELT pattern.
- Address real-time and streaming ingestion.
- Address big data platforms and distributed processing.
- Address data privacy and regulatory constraints on retained data.
- Build an end-to-end project from source system to dashboard.
Major Topics
Required Topics
- Why warehouses exist — the analytical workload, the cost of reporting against production, history that transactional systems discard.
- Architecture — source systems, staging, ODS, warehouse, data marts, presentation layer.
- Requirements — business questions, measures, grain, and the interviews that surface them.
- Dimensional modelling — facts and dimensions, grain declaration, additive and non-additive measures, degenerate dimensions.
- Schema design — star, snowflake, conformed dimensions, bridge tables, junk dimensions.
- Slowly changing dimensions — the types, surrogate keys, effective dating.
- ETL — extraction methods, change data capture, transformation and cleansing, loading strategies, error handling and restartability.
- Data quality — profiling, rules, deduplication, master data, reconciliation.
- Analytical SQL — aggregation, window functions, rollup and cube, query patterns over fact tables.
- Performance — partitioning, indexing for analytics, aggregate tables, columnar storage and compression.
- Metadata and governance — lineage, definitions, ownership, and why a metric needs one agreed definition.
- Delivery — connecting BI tools and validating results against the source.
Optional Topics
- Kimball versus Inmon.
- Cloud warehouse platforms and their cost models.
- Data lakes, lakehouses and ELT.
- Streaming and near-real-time ingestion.
- Distributed processing frameworks.
- Privacy, retention and regulated data.
- A full end-to-end project.
Resources & Tools
- ⚠ The Data Warehouse Toolkit by Ralph Kimball and Margy Ross is the field's standard work, and dimensional modelling as taught in this course is essentially Kimball's method. It is worth owning.
- Building the Data Warehouse by W. H. Inmon presents the competing architectural view, and a course that contrasts the two teaches better than one that presents either as settled.
- Star Schema: The Complete Reference by Christopher Adamson is the practical modelling reference.
- Database platforms: PostgreSQL, SQL Server or MySQL for a teaching warehouse; ⚠ cloud platforms — Snowflake, BigQuery, Redshift — all have free tiers or student credits, and experience on one of them is what job postings actually ask for.
- ETL tooling: SQL Server Integration Services, Talend, Pentaho, or increasingly dbt with Python — ⚠ dbt has become the common vocabulary of modern analytics engineering and is free to learn.
- Reporting: Power BI and Tableau both have free student licensing; Metabase and Apache Superset are open source.
- ⚠ Public data sets make far better projects than invented ones. Florida's own open data — the Department of Education, FDOT's traffic counts, the Department of Health — is messy in exactly the ways real source systems are, which is the point.
Career Pathways
- Database Architect (SOC 15-1243) and Data Warehouse Developer — the direct destinations.
- Data Engineer — ⚠ the fastest-growing title in this area, and this course is its foundation; the modern job is largely pipelines and modelling.
- Business Intelligence Analyst / Developer and Data Analyst (SOC 15-2051 adjacent).
- Database Administrator (SOC 15-1242).
- Florida employers: healthcare systems (AdventHealth, Orlando Health, BayCare, HCA), which run substantial analytics operations under regulatory constraint; Publix and the large retail and logistics operations; financial services in South Florida; the theme park and hospitality groups, which are heavy analytics users; state agencies, including the Department of Education's data systems; and the defence and simulation sector.
- ⚠ A realistic note: data engineering pays well and is in demand, but it is an infrastructure discipline — the work is pipelines, reliability and data quality rather than modelling or machine learning. Students drawn to this area by data science should know which job they are preparing for; both are good, and they are not the same.
Special Information
Offering Notes — offerings and hours, school by school
| Institution | Its title | Credits | Contact hours |
|---|
| Gulf Coast State College | Data Warehousing | 3 | not published |
| Polk State College | Data Warehouse Design and Creation | 3 | not published |
| University of West Florida | Databases for Data Science | 3 | not published |
Two Florida College System institutions and one State University System institution, so statewide numbering guarantees transfer between them. ✅ All three carry it at 3 credits.
⚠ The 45 contact hours at the top of this guide are derived — the Florida convention for a 3-credit lecture course. No institution publishes an hour figure.
⚠⚠ "Databases for Data Science" is a broader course than "Data Warehousing"
Gulf Coast and Polk State both name data warehousing explicitly, matching the statewide title and description. The University of West Florida's "Databases for Data Science" points at something wider.
⚠ Two agreeing catalogs plus the statewide definition make warehousing the subject of this number, so UWF is the outlier — but the difference is a genuine one and worth understanding:
- A data warehousing course is about dimensional modelling and ETL: designing a star schema, handling slowly changing dimensions, building a pipeline. It is an engineering course.
- A databases-for-data-science course is likely to cover getting data out for analysis — query patterns, working with NoSQL and semi-structured data, feeding data into Python or R workflows. It is a preparation-for-analysis course.
The practical consequence: ⚠ if your programme expects you to be able to design a star schema, confirm that your version teaches it. And if you are transferring, carry the syllabus — a receiving department reading "CAP4774" will expect dimensional modelling, because that is what the statewide definition says.
⚠ The prerequisite chain is real, and one branch of it is unusual
The statewide prerequisite names a database systems course — unsurprising, since you cannot design a warehouse without understanding a normalised schema and writing SQL fluently.
⚠ The second branch is more informative: an information systems senior project course. That is not a technical prerequisite; it is a signal that this course expects requirements-gathering ability — talking to people about what they need and turning it into a design. The hardest part of a warehouse project is usually not the SQL; it is discovering what question the business actually wants answered, and a course gated on a project course expects you to have done that at least once.
Position in the curriculum
A 4000-level course late in an information systems, information technology or computer science pathway, after database systems. ⚠ It pairs naturally with data visualisation and with big data analytics (CAP4786), and the three together make a coherent analytics concentration.
Workload
Budget eight to twelve hours a week, more where a full project runs. ⚠ ETL work is where the time actually goes, and students consistently underestimate it — real source data is dirty, inconsistent and incomplete, and the cleansing takes longer than the modelling. That ratio is not an artefact of the course; it is the job. Practitioners routinely report that most of the effort in an analytics project is spent getting the data into a usable state.
AI Integration
Data work is an area where these tools are genuinely productive and where a specific failure mode is expensive.
Genuinely useful: writing and explaining SQL, which is real professional practice and a legitimate accelerant; explaining a modelling concept a second way — slowly changing dimensions are the standard sticking point; generating test data; explaining an unfamiliar platform's syntax; drafting documentation and data dictionaries, which teams chronically neglect; and reviewing a schema you have designed and suggesting what you may have missed.
⚠⚠ Where it fails, and the central one is specific to this subject:
- Generated SQL that runs and returns the wrong number. ⚠ This is the dangerous case. A join that fans out a fact table, a filter that silently drops nulls, an aggregate at the wrong grain — the query succeeds, produces a plausible figure, and nobody notices until a decision has been made on it. A wrong number that looks right is worse than a query that errors.
- Grain confusion. Declaring the grain is the first discipline of dimensional modelling, and generated models frequently mix grains in one fact table, which corrupts every aggregate built on it.
- Platform syntax mixed between SQL dialects, and functions that do not exist in yours.
- No knowledge of your source systems. Which of two systems is authoritative, which field is actually populated, which code means what — that is institutional knowledge held by people, and it is where warehouse projects succeed or fail.
⚠ The professional habit that catches it: reconcile every result against the source. Row counts, control totals, a known figure you can verify independently. That is standard warehouse practice for exactly this reason, it applies to your own SQL as much as to generated SQL, and it is the discipline this course should leave you with.
Worth knowing about the trajectory: natural-language-to-SQL tools are being built into every analytics platform, and they will handle routine querying. ⚠ What they cannot do is decide what the right question is, know which source to trust, or notice that a number is implausible. Modelling and data quality judgement are the durable parts of this discipline.
Academic integrity: read your syllabus. Policies on AI-assisted code vary sharply, and a design project is usually defended, which exposes work a student cannot explain.