Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

Running SQL queries written with traditional single-node RDBMS habits against billion-row data lakes often triggers massive network shuffles, out-of-memory failures and multi-hour batch jobs. This course teaches how the Apache Spark SQL engine executes distributed queries and how to build fast analytical queries and ETL pipelines.

It covers the unified Spark SQL and DataFrame architecture, the Catalyst Optimizer and Tungsten execution engine, window functions and nested JSON/Array processing, Parquet and Lakehouse formats (Delta Lake / Apache Iceberg), partitioning and Z-Ordering, Broadcast vs Sort-Merge Joins, Adaptive Query Execution (AQE), and diagnosing bottlenecks in the Spark UI.

Who Should Attend?

  • Data engineers working on data lakes and Lakehouse platforms (Databricks, EMR, Fabric, Synapse or on-prem Spark)
  • SQL developers and data analysts writing analytical queries over large-scale datasets
  • Data platform engineers tuning slow or resource-heavy Spark SQL jobs
  • Prerequisite: working knowledge of SQL querying

Key Takeaways

  • Read logical and physical execution plans (EXPLAIN) and understand Catalyst Optimizer rules
  • Write idiomatic Spark SQL using window functions, higher-order array/JSON functions and built-in expressions instead of slow UDFs
  • Configure partitioning, small-file compaction and Z-Ordering/clustering on Parquet, Delta Lake and Iceberg
  • Minimise shuffle overhead and resolve data skew using Broadcast Joins, AQE and join hints
  • Diagnose disk spill, GC pressure and straggler tasks via the Spark UI

Curriculum

Spark SQL Architecture, Catalyst Optimizer and Tungsten
  • Drivers, Executors, the Job/Stage/Task hierarchy and narrow vs wide (shuffle) transformations
  • Unresolved, Analyzed and Optimized Logical Plans through to Physical Plan selection
  • Predicate pushdown, column pruning and Tungsten whole-stage code generation
Advanced Analytical Spark SQL and Semi-Structured Data
  • CTEs, Window functions (ROW_NUMBER, LAG/LEAD, rolling frames) and GROUPING SETS
  • Querying nested Structs, Arrays and JSON with EXPLODE, TRANSFORM and FILTER higher-order functions
  • The serialisation cost of custom UDFs and replacing them with native expressions or vectorised UDFs
Storage Formats, Partitioning and Lakehouse Tables
  • Columnar Parquet internals: row groups, dictionary encoding and min/max statistics
  • Partitioning, bucketing, avoiding the small-file problem and target file sizing
  • Delta Lake and Apache Iceberg: ACID transactions, MERGE INTO, Time Travel, OPTIMIZE and Z-Ordering
Join Strategies, AQE and Data Skew Mitigation
  • When Spark picks Broadcast Hash Join, Sort-Merge Join or Shuffle Hash Join
  • Adaptive Query Execution (AQE): coalescing post-shuffle partitions, dynamic join switching and skew handling
  • When to use — and when to avoid — CACHE TABLE and persistence levels
Diagnosing Bottlenecks in the Spark UI and Tuning Lab
  • Reading the Spark UI SQL tab, stage DAGs, Memory/Disk Spill metrics and straggler tasks
  • Hands-on lab: diagnosing and refactoring slow Spark SQL workloads

Other Courses in This Category

  • PostgreSQL for Developers: From Fundamentals to Advanced SQL

    PostgreSQL data types, advanced SQL (CTEs, window functions, LATERAL), JSONB document modelling, full-text search, B-Tree/GIN/GiST/BRIN indexes, PL/pgSQL, MVCC/locking and partitioning — for application developers.

    Databases Corporate
    Live Course
    5 modules
    View
  • PostgreSQL Database Administration, High Availability and Operations

    PostgreSQL architecture, memory and WAL tuning, MVCC/Autovacuum and bloat control, PgBouncer connection pooling, PITR backups with pgBackRest, Streaming/Logical Replication and Patroni high availability.

    Databases Corporate
    Live Course
    5 modules
    View
  • MySQL and MariaDB Development, Tuning and Administration

    InnoDB storage engine and clustered index architecture, query and index optimisation, transaction and lock analysis, backups, GTID replication and high availability — for developer and DBA teams running MySQL and MariaDB.

    Databases Corporate
    Live Course
    5 modules
    View
  • Oracle Database Architecture, SQL Tuning and Administration

    Oracle Multitenant (CDB/PDB) architecture, SGA/PGA memory and background processes, tablespace/segment management, B-Tree/Bitmap indexes, partitioning, AWR/ASH tuning, RMAN backups and Data Guard essentials.

    Databases Corporate
    Live Course
    5 modules
    View
  • MongoDB Document Data Modelling, Aggregation and Operations

    BSON and document schema design patterns (embedding vs referencing), the Aggregation Pipeline, compound/multikey indexes and the ESR rule, multi-document ACID transactions, Change Streams, Replica Sets and Sharding.

    Databases Corporate
    Live Course
    5 modules
    View
  • In-Memory Data Structures, Caching and Messaging with Redis

    Redis data structures (Strings, Hashes, Lists, Sets, Sorted Sets, Streams, HyperLogLog), caching patterns (cache-aside, stampede protection), distributed locks, rate limiting, Lua/pipelining, RDB/AOF persistence and Redis Cluster.

    Databases Corporate
    Live Course
    5 modules
    View

Get in touch