Course Introduction
The DuckDB for Local and Embedded Analytics Training Course provides a practical and comprehensive learning experience for professionals who need to perform fast analytical queries directly within local environments, Python applications, notebooks, and embedded data-processing workflows. The course introduces DuckDB as an analytical SQL engine and develops practical skills for querying structured data without requiring a traditional client-server database architecture for every analytical task.
This DuckDB training course focuses on using SQL to analyze CSV, Parquet, JSON, DataFrames, and other analytical data sources efficiently. DuckDB's Python API supports direct querying of files and can work with pandas DataFrames, Polars DataFrames, and Arrow tables, making it particularly useful for modern Python analytics workflows. Participants learn how to combine SQL-based analysis with Python to create flexible, reproducible, and efficient local analytics solutions.
A major focus of the training is DuckDB's embedded architecture and in-process analytical capabilities. Participants learn how to create in-memory and persistent DuckDB databases, manage connections, create tables and views, execute analytical SQL, and integrate database operations into Python applications. DuckDB's Python DB-API follows the DB-API 2.0 specification and supports both in-memory and persistent database connections, providing a familiar programming model for Python professionals.
The course places particular emphasis on analytical work with Parquet and columnar data. Participants learn how to query Parquet files directly, work with multiple files and file patterns, use partition-aware datasets, select required columns, and produce Parquet outputs for downstream analytical workflows. DuckDB's relational API provides direct Parquet integration and supports options such as Hive partitioning, filename metadata, row numbers, and union-by-name processing.
Another important component is the integration of DuckDB with the broader Python data ecosystem. Participants learn how to move between DuckDB relations, pandas, Polars, and Arrow while choosing appropriate representations for different analytical tasks. DuckDB can return query results to pandas, Polars, Arrow, and other formats, enabling SQL-based transformation and aggregation to become part of broader data science, reporting, and data engineering pipelines.
By the end of this local analytics workshop, participants will be able to design and implement efficient DuckDB solutions for exploratory analysis, reporting, data preparation, business intelligence, research, financial analysis, data engineering, and embedded analytics applications. They will understand SQL optimization, profiling, file-based analytics, database design, Python integration, extensions, reusable analytical queries, and production considerations, enabling them to build practical local and embedded analytical solutions with DuckDB.
Duration
5 days
Who Should Attend
-
Data analysts who need fast SQL-based analytics in local and Python environments.
-
Data scientists working with CSV, Parquet, pandas, Polars, or Arrow datasets.
-
Data engineers developing lightweight analytical and data-processing solutions.
-
Python developers embedding analytical database capabilities directly into applications.
-
Analytics engineers building reusable SQL transformation and reporting workflows.
-
Business intelligence professionals performing local analysis without relying exclusively on centralized databases.
-
Research analysts processing large structured datasets for exploratory and statistical analysis.
-
Financial analysts working with transactional, market, portfolio, and historical datasets.
-
Reporting specialists developing automated analytical reports from local data files.
-
SQL professionals seeking practical experience with modern embedded analytical databases.
-
Machine learning practitioners preparing and aggregating datasets for modeling workflows.
-
Technical leads evaluating DuckDB for local analytics, prototyping, embedded applications, and data engineering.
Course Objectives
-
Develop comprehensive practical skills for installing, configuring, connecting to, and using DuckDB for local and embedded analytical workloads.
-
Write advanced analytical SQL queries using filtering, aggregation, joins, subqueries, common table expressions, window functions, and reusable query patterns.
-
Query CSV, Parquet, JSON, and other supported data sources directly while designing efficient file-based analytical workflows.
-
Create and manage in-memory and persistent DuckDB databases, tables, views, schemas, and reusable database objects for analytical applications.
-
Integrate DuckDB with Python, pandas, Polars, and Arrow to create flexible workflows that combine SQL analytics with DataFrame-based processing.
-
Optimize analytical queries by understanding execution plans, profiling information, data access patterns, joins, aggregations, and unnecessary computational work.
-
Design efficient Parquet-based analytical architectures using partitioning, selective data access, column projection, and appropriate file organization techniques.
-
Apply DuckDB extensions and advanced features appropriately to expand analytical capabilities while maintaining maintainable and reproducible solutions.
-
Build reliable embedded analytics applications that incorporate parameterized queries, connection management, data validation, error handling, and reusable analytical logic.
-
Complete an end-to-end DuckDB analytics project that demonstrates local data ingestion, SQL transformation, optimization, reporting, Python integration, and production-oriented practices.
Comprehensive Course Outline
Module 1: Introduction to DuckDB and Embedded Analytics
-
Understanding DuckDB's architecture, analytical focus, embedded execution model, and role in modern local data analytics.
-
Installing DuckDB through Python and other supported environments while establishing a practical development and analytical workspace.
-
Exploring in-memory databases, persistent database files, connections, schemas, tables, views, and fundamental DuckDB database concepts.
-
Comparing embedded analytical workflows with traditional client-server database architectures and identifying appropriate use cases for each approach.
Module 2: SQL Fundamentals for DuckDB Analytics
-
Writing DuckDB SQL queries using SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT, and other foundational analytical operations.
-
Applying expressions, functions, aliases, conditional logic, type conversions, NULL handling, and calculated fields to analytical datasets.
-
Using subqueries, common table expressions, set operations, and reusable query structures to organize increasingly complex analytical logic.
-
Developing practical SQL patterns for business reporting, exploratory analysis, operational metrics, financial calculations, and research data analysis.
Module 3: Local Data Ingestion and File-Based Analytics
-
Reading CSV, JSON, and other supported data formats directly into DuckDB while understanding automatic schema detection and data-type inference.
-
Querying local data files directly with SQL and designing analytical workflows that minimize unnecessary intermediate loading and transformation.
-
Managing multiple files, file patterns, metadata columns, schema variations, and other practical challenges encountered in file-based analytics.
-
Designing reliable ingestion workflows with validation, type control, error handling, and repeatable processing procedures for local analytical datasets.
Module 4: Parquet Analytics with DuckDB
-
Understanding columnar storage and why Parquet is particularly useful for analytical workloads involving selective columns, filtering, and large structured datasets.
-
Querying individual Parquet files and collections of files directly while applying filters, aggregations, joins, and analytical transformations.
-
Working with Hive-style partitioning, filename metadata, row numbers, schema alignment, and union-by-name functionality available through DuckDB's Parquet interfaces.
-
Writing optimized analytical results back to Parquet while considering compression, partitioning, file sizes, interoperability, and downstream consumption requirements.
Module 5: Advanced DuckDB SQL and Analytical Processing
-
Implementing advanced joins, including inner, left, right, full, semi, and anti joins, while maintaining data quality and controlling unexpected row multiplication.
-
Applying window functions, ranking, partitioning, running calculations, lag and lead operations, and other advanced SQL analytics techniques.
-
Using date, timestamp, interval, string, numerical, statistical, and conditional functions to create sophisticated analytical transformations.
-
Building complex analytical queries with common table expressions, nested logic, reusable views, and modular SQL structures for maintainable solutions.
Module 6: DuckDB Python Integration
-
Using the DuckDB Python API to execute SQL queries, manage connections, create relations, and integrate analytical database operations into Python applications.
-
Working with DuckDB relations and understanding how relational objects can represent SQL queries before execution within Python workflows.
-
Querying pandas DataFrames, Polars DataFrames, and Arrow tables directly while understanding interoperability considerations and appropriate workflow boundaries.
-
Fetching DuckDB results into pandas, Polars, Arrow, NumPy, CSV, Parquet, and other outputs for downstream analytics and application processing.
Module 7: Query Performance, Profiling, and Optimization
-
Understanding DuckDB query execution and identifying inefficient scans, joins, aggregations, sorting operations, and unnecessary intermediate processing.
-
Using query profiling and available profiling information to investigate execution behavior and identify measurable opportunities for performance improvement.
-
Designing efficient analytical queries through selective column access, appropriate filtering, efficient joins, sensible aggregation strategies, and effective data organization.
-
Benchmarking alternative SQL implementations using representative workloads, execution measurements, and reproducible testing procedures to validate optimization decisions.
Module 8: DuckDB Extensions and Advanced Analytics
-
Understanding the DuckDB extension architecture and identifying when additional extensions can provide specialized functionality for analytical workloads.
-
Installing, loading, managing, and using appropriate DuckDB extensions while considering reproducibility, compatibility, security, and deployment requirements.
-
Exploring advanced analytical capabilities for spatial, JSON, full-text, remote data, or other specialized processing requirements where supported by the DuckDB ecosystem.
-
Designing extension-aware analytical solutions that remain maintainable, documented, portable, and suitable for development, testing, and production environments.
Module 9: Embedded Analytics Application Design
-
Designing Python applications that embed DuckDB for local analytics, data preparation, reporting, desktop tools, notebooks, and lightweight analytical services.
-
Managing database connections, persistent storage, transactions, temporary objects, views, schemas, and application-level database lifecycle considerations.
-
Applying parameterized SQL appropriately and designing secure, reusable query interfaces for applications that receive dynamic analytical inputs.
-
Building maintainable embedded analytics components with configuration management, validation, logging, error handling, testing, and reproducible deployment practices.
Module 10: End-to-End DuckDB Local Analytics Project
-
Designing an end-to-end analytical solution that ingests raw CSV or Parquet datasets, creates an appropriate DuckDB environment, and develops a reusable analytical workflow.
-
Implementing advanced SQL transformations, joins, aggregations, window calculations, data-quality checks, and analytical outputs against realistic business or operational data.
-
Integrating the DuckDB workflow with Python and DataFrame technologies while benchmarking performance and documenting important architectural and optimization decisions.
-
Presenting the completed embedded analytics solution with SQL scripts, database structures, performance findings, reporting outputs, documentation, and recommendations for future scalability.
Training Approach
This course will be delivered by our skilled trainers who have vast knowledge and experience as expert professionals in the fields. The course is taught in English and through a mix of theory, practical activities, group discussion and case studies. Course manuals and additional training materials will be provided to the participants upon completion of the training.
Tailor-Made Course
This course can also be tailor-made to meet organization requirement. For further inquiries, please contact us on: Email: training@upskilldevelopment.com Tel: +254 721 331 808
Training Venue
The training will be held at our Upskill Training Centre. We also offer training for a group (at a discount of 10% to 50%) at requested location all over the world. The Onsite course fee covers the course tuition, training materials, two break refreshments, buffet lunch, airport transfers, Upskill gift package, and guided tour.
Visa application, travel expenses, dinners, accommodation, insurance, and other personal expenses are catered by the participant
Certification
Participants will be issued with Upskill certificate upon completion of this course.
Airport Pickup and Accommodation
Airport pickup and accommodation is arranged upon request. For booking contact our Training Coordinator through Email: training@upskilldevelopment.com, +254 721 331 808
Terms of Payment:
Unless otherwise agreed between the two parties’ payment of the course fee should be done 3 working days before commencement of the training so as to enable us to prepare better.
