Prerequisites Basic computer literacy and a willingness to document experiments.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn Entities, keys and relationships Normal forms and controlled denormalisation Nullability and uniqueness ER diagrams and constraints Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools PostgreSQL or SQLite ER diagram tool SQL client
Hands-on exercise Model a small domain and identify duplicate, missing and inconsistent data cases. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A schema with constraints and sample data. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria The database enforces key business rules under invalid writes. Evidence must be reproducible and tied to Data Warehousing.
Next: Query composition & joins
✓ Mark this node as complete
Prerequisites Completion of the previous stage and a working local practice environment.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn SELECT, WHERE and ORDER BY Inner/outer joins and set operations GROUP BY and HAVING NULL and three-valued logic Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools PostgreSQL SQL client
Hands-on exercise Answer a set of business questions using joins and verify totals against hand-calculated cases. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A query notebook of documented analytical questions. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria Queries return correct results on edge cases and NULL values. Evidence must be reproducible and tied to Data Warehousing.
Next: CTEs, windows & analytical SQL
✓ Mark this node as complete
Prerequisites Completion of the previous stage and a working local practice environment.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn CTEs and recursive queries where applicable Window functions and ranking Conditional aggregation Date/time and cohort analysis Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools PostgreSQL window functions Explain output
Hands-on exercise Build a cohort or ranking report and compare it with a simpler query. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A reusable analytical query pack with expected results. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria The query logic is readable and tested against known fixtures. Evidence must be reproducible and tied to Data Warehousing.
Next: Indexes, plans & query optimisation
✓ Mark this node as complete
Prerequisites Completion of the previous stage and a working local practice environment.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn B-tree and composite index trade-offs EXPLAIN/ANALYZE Selectivity and cardinality Sorts, scans and join strategies Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools EXPLAIN Database statistics Benchmark fixtures
Hands-on exercise Capture a plan before and after adding a candidate index, then compare execution. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A query tuning report with plan screenshots and workload notes. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria Optimisation claims include the data size and measured plan changes. Evidence must be reproducible and tied to Data Warehousing.
Next: Transactions, concurrency & integrity
✓ Mark this node as complete
Prerequisites Completion of the previous stage and a working local practice environment.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn ACID and isolation levels Locks and deadlocks Optimistic vs pessimistic concurrency Idempotent writes and recovery Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools Database transaction tools Concurrent test script
Hands-on exercise Run concurrent update tests and demonstrate a race condition prevented by a constraint or transaction. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A transactional workflow with concurrency tests. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria The invariants hold under repeated and parallel requests. Evidence must be reproducible and tied to Data Warehousing.
Next: Migrations, backup & operations
✓ Mark this node as complete
Prerequisites Completion of the previous stage and a working local practice environment.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn Additive migrations and version history Backup and restore validation WAL/checkpoint basics where relevant Access control and audit Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools Migration tool Database backup tool Monitoring
Hands-on exercise Apply a migration to a copy of a legacy schema and restore from backup. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A versioned migration set and verified recovery procedure. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria The migration is repeatable, preserves existing rows and has a rollback/restore plan. Evidence must be reproducible and tied to Data Warehousing.
Next: Performance, security & governance
✓ Mark this node as complete
Prerequisites Completion of the previous stage and a working local practice environment.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn Least-privilege roles Parameterised statements and injection prevention Connection limits and maintenance Retention and sensitive-data handling Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools DB roles Query stats Security checklist
Hands-on exercise Review an application data path and verify parameter binding and privilege boundaries. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A database operations checklist and access matrix. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria No application query relies on interpolated untrusted input. Evidence must be reproducible and tied to Data Warehousing.
Next: Capstone: analytics-ready data model
✓ Mark this node as complete
Prerequisites Completion of the previous stage and a working local practice environment.
Learning objectives Explain the stage-specific model and its limits Apply the listed concepts using a controlled exercise Record results, failure cases and a reproducible evidence trail
Topics to learn Business questions and dimensional choices Schema and query design Concurrency and performance evidence Migrations and disaster recovery Data Warehousing specialization: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools SQL database Visualisation tool Git
Hands-on exercise Build the schema, ingest fixtures, answer analytics questions and test restore. Context for this route: Track-specific focus: Data Warehousing. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Suggested project A documented database project with schema, query pack and benchmark report. The outcome must directly demonstrate Data Warehousing, not merely list tools.
Learning resources Completion criteria Data correctness, query performance and recovery are independently verifiable. Evidence must be reproducible and tied to Data Warehousing.
End of roadmap: Complete the final project and build your portfolio.
✓ Mark this node as complete