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
- Big Data specialization: Track-specific focus: Big Data. 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 SQLiteER diagram toolSQL client
Hands-on exercise
Model a small domain and identify duplicate, missing and inconsistent data cases. Context for this route: Track-specific focus: Big Data. 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 Big Data, not merely list tools.
Learning resources
Completion criteria
The database enforces key business rules under invalid writes. Evidence must be reproducible and tied to Big Data.
Next: Query composition & joins
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
- Big Data specialization: Track-specific focus: Big Data. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools
PostgreSQLSQL 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: Big Data. 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 Big Data, 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 Big Data.
Next: CTEs, windows & analytical SQL
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
- Big Data specialization: Track-specific focus: Big Data. 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 functionsExplain output
Hands-on exercise
Build a cohort or ranking report and compare it with a simpler query. Context for this route: Track-specific focus: Big Data. 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 Big Data, 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 Big Data.
Next: Indexes, plans & query optimisation
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
- Big Data specialization: Track-specific focus: Big Data. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools
EXPLAINDatabase statisticsBenchmark fixtures
Hands-on exercise
Capture a plan before and after adding a candidate index, then compare execution. Context for this route: Track-specific focus: Big Data. 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 Big Data, 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 Big Data.
Next: Transactions, concurrency & integrity
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
- Big Data specialization: Track-specific focus: Big Data. 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 toolsConcurrent 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: Big Data. 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 Big Data, not merely list tools.
Learning resources
Completion criteria
The invariants hold under repeated and parallel requests. Evidence must be reproducible and tied to Big Data.
Next: Migrations, backup & operations
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
- Big Data specialization: Track-specific focus: Big Data. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools
Migration toolDatabase backup toolMonitoring
Hands-on exercise
Apply a migration to a copy of a legacy schema and restore from backup. Context for this route: Track-specific focus: Big Data. 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 Big Data, 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 Big Data.
Next: Performance, security & governance
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
- Big Data specialization: Track-specific focus: Big Data. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools
DB rolesQuery statsSecurity checklist
Hands-on exercise
Review an application data path and verify parameter binding and privilege boundaries. Context for this route: Track-specific focus: Big Data. 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 Big Data, not merely list tools.
Learning resources
Completion criteria
No application query relies on interpolated untrusted input. Evidence must be reproducible and tied to Big Data.
Next: Capstone: analytics-ready data model
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
- Big Data specialization: Track-specific focus: Big Data. Use the tools and standards named in this path; avoid treating this route as a generic overview of Data Science & Data Engineering.
Tools
SQL databaseVisualisation toolGit
Hands-on exercise
Build the schema, ingest fixtures, answer analytics questions and test restore. Context for this route: Track-specific focus: Big Data. 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 Big Data, not merely list tools.
Learning resources
Completion criteria
Data correctness, query performance and recovery are independently verifiable. Evidence must be reproducible and tied to Big Data.
End of roadmap: Complete the final project and build your portfolio.