Database Systems for Data Engineers Training Course

Course overview

Data engineers are expected to make data reliable, available and economical to use, yet many pipelines fail because the underlying database design, workload pattern or operational model is poorly understood. A table structure that works for a reporting prototype can create costly joins at scale; an untested replication setup can leave downstream consumers with stale data; and weak transaction or access-control decisions can compromise both data quality and compliance. This course equips engineers to make defensible database-system choices across operational, analytical and streaming workloads.

Participants examine how relational database engines store, query and protect data, then apply those principles to data-engineering architecture. They design schemas for ingestion and analytics, write and tune SQL, interpret execution plans, select indexes and partitions, manage transactions and concurrency, and plan replication, backup and recovery. The course also addresses change data capture, data modelling trade-offs, database observability, role-based access control and cost-performance decisions for production data platforms. PostgreSQL is used as the primary relational engine, with supporting workflows in dbt, Apache Airflow and Docker.

Instruction combines technical briefings with guided labs based on a retail-order data platform. Participants investigate slow queries, model source and warehouse structures, orchestrate incremental loads, simulate failures and document operational controls. They leave with a reviewed database engineering design pack: logical and physical models, DDL, indexing rationale, pipeline design, monitoring checks, recovery runbook and an implementation plan suitable for adapting to their own environment.

The course is designed for practising data engineers and adjacent technical professionals who already work with SQL and need stronger command of the database systems beneath their pipelines, marts and data products.

Course objectives

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

• Design logical and physical schemas using normalization, denormalization and dimensional modelling criteria
• Write PostgreSQL DDL and SQL queries that enforce keys, constraints and data-quality rules
• Diagnose query bottlenecks using EXPLAIN ANALYZE, execution plans and index statistics
• Select indexing, partitioning and storage strategies for ingestion, lookup and analytical workloads
• Implement transaction, isolation and concurrency controls that preserve pipeline data consistency
• Design incremental ingestion using change data capture, watermarks and idempotent loading patterns
• Create backup, recovery, replication and failover runbook requirements for a production database
• Produce a database engineering design pack covering models, DDL, performance tests and operational controls

Benefits of attending

Target competencies

Who should attend

• Data Engineers — who build ingestion, transformation and serving pipelines on relational data platforms
• Senior Data Engineers — who must make architecture and performance trade-offs for production workloads
• Analytics Engineers — who need to design dependable warehouse models and incremental transformations
• Database Developers — who write SQL and need stronger skills in tuning, operations and data-pipeline integration
• Data Platform Engineers — who operate shared database services, replication and access-control patterns
• Technical Leads — who review database designs and need to assess reliability, scalability and recoverability

Requirements and prerequisites

Participants should be comfortable writing multi-table SQL queries using SELECT, JOIN, GROUP BY and common filtering conditions, and should understand rows, tables, primary keys and foreign keys. Experience building or supporting an ETL/ELT pipeline, reporting database or application database is strongly recommended. Familiarity with a command line, Git and Python is useful but not essential; examples are guided and do not require software-development expertise. Prior PostgreSQL administration, cloud certification, advanced statistics and prior use of dbt or Apache Airflow are not required. Participants should be prepared to read SQL, interpret basic logs and work through hands-on troubleshooting tasks.

Training methodology

The instructor uses short architecture briefings followed by PostgreSQL labs in an isolated Docker environment. Participants work from a realistic order-management source system through ingestion, warehouse modelling and operational support. Exercises include reading EXPLAIN ANALYZE output, comparing index choices, resolving concurrent-update failures, designing CDC loads and restoring data after a simulated incident. Small groups review one another’s design decisions against stated service-level requirements. On the final day, each participant consolidates their work into a database engineering design pack and implementation plan for peer and instructor feedback.

Course outline

Day 1: Database systems and data engineering workloads

• Operational, analytical and streaming workload characteristics
• Database engine architecture: processes, memory and storage
• OLTP, OLAP and lakehouse design boundaries
• Data lifecycle from source capture to serving layer
• CAP trade-offs and consistency requirements
• Service-level objectives for freshness, latency and recovery
• PostgreSQL environment setup with Docker

Workshop: Map a retail-order platform into source, ingestion, warehouse and serving workloads, producing an initial database architecture diagram.

Day 2: Data modelling for durable pipelines

• Entity relationship modelling and business keys
• Normalization through third normal form
• Denormalization decisions for read-intensive workloads
• Surrogate keys and natural key trade-offs
• Slowly changing dimension design patterns
• Fact table grain and dimensional modelling
• DDL constraints for referential and domain integrity

Workshop: Create an ERD and PostgreSQL DDL for customer, order and product data, including documented key and grain decisions.

Day 3: SQL engineering and query execution

• Set-based SQL patterns for transformation logic
• Common table expressions and window functions
• Join algorithms and join-order implications
• PostgreSQL EXPLAIN and EXPLAIN ANALYZE
• Cardinality estimates and planner statistics
• Predicate pushdown and projection reduction
• Query profiling for transformation workloads

Workshop: Investigate three intentionally slow transformation queries and produce revised SQL with evidence from execution plans.

Day 4: Indexes, partitions and storage design

• B-tree, hash, GIN and BRIN index selection
• Composite indexes and column-order rules
• Covering indexes and index-only scans
• Table partitioning by range, list and hash
• Partition pruning and maintenance operations
• Vacuum, autovacuum and table bloat
• Storage sizing and retention policies

Workshop: Design and benchmark indexing and monthly partitioning for an order-events table, producing a performance comparison report.

Day 5: Transactions, concurrency and data correctness

• ACID properties in pipeline and application workflows
• Transaction boundaries and commit behaviour
• Read committed, repeatable read and serializable isolation
• Locks, deadlocks and blocking-session analysis
• Upserts using INSERT ON CONFLICT
• Idempotency patterns for retry-safe ingestion
• Duplicate detection and reconciliation queries

Workshop: Resolve a concurrent order-loading scenario and produce an idempotent SQL loading pattern with reconciliation checks.

Day 6: Ingestion, CDC and orchestration

• Batch, micro-batch and streaming ingestion patterns
• Watermarks and high-water-mark extraction
• Change data capture concepts and log-based capture
• Late-arriving data and backfill handling
• dbt incremental models and unique keys
• Apache Airflow DAG dependencies and retries
• Schema evolution and contract checks

Workshop: Build an incremental order-loading workflow using dbt and an Airflow DAG, including watermark and retry logic.

Day 7: Reliability, replication and recovery

• Recovery point objective and recovery time objective
• Logical and physical backup strategies
• Point-in-time recovery concepts
• Read replicas and replication lag
• Failover decision criteria and runbooks
• Data validation after restore operations
• Capacity planning and failure-mode analysis

Workshop: Develop a backup and recovery runbook for the retail platform and validate it through a timed restore simulation.

Day 8: Security, governance and observability

• Role-based access control in PostgreSQL
• Least-privilege permissions for pipeline service accounts
• Encryption in transit and credential handling
• Personally identifiable information classification
• Audit logging and access-review evidence
• Database health metrics and alert thresholds
• Data lineage and operational metadata

Workshop: Configure role permissions and monitoring checks for a restricted customer-data pipeline, producing an access and alerting matrix.

Day 9: Architecture trade-offs and production design

• Database selection criteria for data products
• Workload isolation and resource governance
• Cost-performance modelling for storage and compute
• Multi-tenant schema and database strategies
• Migration planning and zero-downtime changes
• Technical design review checklists
• Operational acceptance criteria

Workshop: Conduct a team architecture review of a proposed analytics platform and produce prioritised design recommendations with risks.

Day 10: Applied database engineering capstone

• Requirements decomposition into database decisions
• Logical-to-physical model traceability
• Performance test planning and success measures
• Pipeline failure scenario design
• Recovery and support ownership model
• Design-pack documentation structure
• Implementation roadmap and stakeholder briefing

Workshop: Complete and present a database engineering design pack containing models, DDL, tuning evidence, pipeline controls and a 90-day implementation plan.

Tools & standards covered

PostgreSQL, Apache Airflow, dbt, Docker

A typical training day

08:30 – 10:30	First session
10:30 – 10:45	Refreshment break
10:45 – 12:30	Second session
12:30 – 13:30	Lunch and networking
13:30 – 15:00	Third session
15:00 – 15:15	Refreshment break
15:15 – 16:30	Workshop and daily review

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions