Data Engineering Bootcamp

You can write complex analytic SQL queries using window functions and CTEs to structure raw data.

1. The data-engineering landscape90 min
   • ETL vs ELT architectures
   • Data pipelines in production
   • Storage formats: CSV, JSON, Parquet
   • Batch versus streaming paradigms
   • Data engineer roles and responsibilities
2. SQL in depth for data2 hrs
   • Window functions (ROW_NUMBER, RANK, DENSE_RANK)
   • Common Table Expressions (CTEs)
   • Aggregations and filtering with HAVING
   • Self-joins and cross joins
   • Handling null values in SQL logic
3. Data modelling and warehousing concepts90 min
   • OLTP vs OLAP workloads
   • Star schema and snowflake schema
   • Fact and dimension tables
   • Surrogate keys vs natural keys
   • Denormalisation for analytical reads
4. Python for data engineering2 hrs
   • File I/O and exception handling
   • Connecting to databases via psycopg2
   • Environment variables and configuration
   • Logging with the logging module
   • Parsing command-line arguments with argparse
5. Working with CSV, JSON and Parquet90 min
   • Reading nested JSON payloads
   • Parquet columnar compression
   • Schema enforcement with Pandas
   • Handling delimiter errors in CSVs
   • Streaming large text files line by line
6. Staging and idempotency basics90 min
   • Idempotent database inserts
   • Truncate-and-load vs upsert patterns
   • Primary key violation handling
   • Staging schema design in PostgreSQL
   • Automating daily ingestion scripts

Hands-on project: Write an extraction and transformation script using raw SQL and Python to parse multi-format logs into a clean staging table with zero duplicate rows.

You can build reusable data transformation scripts with Pandas and robust API connectors.

1. Pandas for transformation2 hrs
   • DataFrames and Series
   • Handling null values and type casting
   • Vectorised operations vs loops
   • Merging, joining, and concatenating
   • Groupby operations and aggregations
2. Working with APIs and sources90 min
   • HTTP requests with the requests library
   • Pagination and rate limiting
   • Handling API token expiry
   • Parsing headers and status codes
   • Implementing exponential backoff on failure
3. Automation and scripting90 min
   • Logging and error catching
   • Command-line arguments with argparse
   • Idempotent script execution
   • Scheduling scripts with cron
   • Managing temporary files securely
4. Testing data code2 hrs
   • Unit testing with pytest
   • Mocking external API responses with unittest.mock
   • Validating dataframes with assertions
   • Fixture setup and teardown
   • Test coverage reporting
5. Packaging and environments90 min
   • Virtual environments with venv
   • Managing dependencies in requirements.txt
   • Structuring a clean Python project
   • Publishing internal modules
   • Using pyproject.toml for configuration
6. Data pipeline logging and monitoring90 min
   • Configuring log levels (DEBUG, INFO, ERROR)
   • Structured JSON logging in Python
   • Capturing stack traces on pipeline crashes
   • Integrating logs with external collectors
   • Setting up email failure notifications

Hands-on project: Build an automated ingestion script that pulls paginated data from a REST API, handles rate limits gracefully, and loads the payload into PostgreSQL.

You can design enterprise dimensional models and implement slowly changing dimensions in Snowflake.

1. Dimensional modelling (star / snowflake)2 hrs
   • Surrogate keys vs natural keys
   • Dimension design best practices
   • Fact table granularity
   • Conformed dimensions across business processes
   • Handling degenerate dimensions
2. OLTP vs OLAP90 min
   • Row-oriented vs column-oriented storage
   • Query optimisation for analytical scans
   • Storage costs and compression
   • Index strategies in analytical databases
   • Memory management during large table scans
3. Slowly changing dimensions2 hrs
   • SCD Type 1 overwrites
   • SCD Type 2 versioning with effective dates
   • Handling late-arriving data
   • Hybrid SCD approaches
   • Writing SQL merge statements for SCD updates
4. Warehouse design90 min
   • Virtual warehouses and sizing
   • Caching strategies
   • Role-based access control
   • Resource monitors and quota limits
   • Managing multi-cluster warehouses
5. Modern warehouses (Snowflake)2 hrs
   • Snowflake copy statements from stage
   • Time Travel and Zero-Copy Clone
   • Clustering keys and micro-partitions
   • Snowpipe for automated ingestion
   • Query profiling and performance tuning
6. Data warehouse performance tuning90 min
   • Identifying slow queries via query history
   • Analyzing execution plans in Snowflake
   • Pruning micro-partitions effectively
   • Avoiding full table scans
   • Optimizing join order and cardinality

Hands-on project: Design and load a star schema in Snowflake for an e-commerce platform, implementing SCD Type 2 for customer history tracking.

You can architect resilient batch extraction, transformation, and loading pipelines with idempotency guarantees.

1. ETL vs ELT90 min
   • When to transform before loading
   • Leveraging cloud compute for ELT
   • Pipeline latency tradeoffs
   • Cost implications of transformation placement
   • Security boundaries in ETL versus ELT
2. Extracting from sources2 hrs
   • Full loads vs incremental loads
   • Watermarking strategies (timestamp and ID based)
   • Handling source schema drift
   • Change Data Capture (CDC) fundamentals
   • Safe extraction from replica databases
3. Transforming and cleaning data2 hrs
   • Deduplication logic in batch jobs
   • Data type standardisation and normalisation
   • Handling corrupted records and quarantine tables
   • Timezone handling across global sources
   • Enriching batch datasets with reference tables
4. Loading into warehouses90 min
   • Bulk loading from object storage
   • Staging tables and atomic swaps
   • Handling insert vs upsert operations
   • Managing constraints and foreign keys
   • Handling loading errors without crashing the run
5. Idempotency and reliability2 hrs
   • Making runs safe to retry
   • Transaction management in batch loads
   • Handling partial pipeline failures
   • State tracking tables for run history
   • Checksum verification on loaded files
6. Batch pipeline auditing and logging90 min
   • Recording row counts at each pipeline stage
   • Detecting record loss during transformation
   • Auditing execution duration and bottlenecks
   • Archiving raw source files post-ingestion
   • Building run audit dashboards

Hands-on project: Implement a full batch ETL pipeline that extracts daily transactions from S3, transforms currencies, and loads cleanly into a target warehouse.

You can process terabyte-scale datasets using Apache Spark DataFrames, optimizing partitions and joins.

1. Distributed processing concepts2 hrs
   • Cluster computing fundamentals
   • Driver and executor architecture
   • Resilient Distributed Datasets (RDDs)
   • Fault tolerance via lineage graphs
   • Memory hierarchy in distributed nodes
2. Spark architecture90 min
   • Lazy evaluation and execution plans
   • Transformations vs actions
   • Catalyst optimizer and logical plans
   • Physical planning and code generation
   • Stage boundaries and wide vs narrow dependencies
3. DataFrames and transformations2 hrs
   • Spark DataFrame API
   • Handling nulls and missing records
   • User Defined Functions (UDFs) and performance cost
   • Working with nested JSON structures in Spark
   • Window functions in PySpark
4. Partitioning and performance2 hrs
   • Understanding shuffles and network I/O
   • Optimising partition counts with coalesce and repartition
   • Broadcast joins for large tables
   • Skew join handling techniques
   • Caching and persist strategies
5. Batch jobs at scale90 min
   • Reading and writing partitioned formats (Parquet/ORC)
   • Checkpointing state for long lineages
   • Debugging executor memory errors (OutOfMemoryError)
   • Configuring driver and executor memory
   • Submitting Spark jobs to cluster managers
6. Spark tuning and debugging90 min
   • Reading the Spark UI for performance bottlenecks
   • Identifying spilled data to disk
   • Garbage collection tuning in JVM executors
   • Optimising file sizes to avoid small file problems
   • Writing efficient custom Spark aggregations

Hands-on project: Build a PySpark batch job that reads multi-gigabyte log datasets from S3, applies distributed transformations, and writes partitioned Parquet files.

You can build real-time event-driven pipelines using Apache Kafka topics, producers, and consumer groups.

1. Streaming vs batch90 min
   • Latency vs throughput tradeoffs
   • Event-driven architecture patterns
   • Stateful vs stateless streaming
   • Lambda and Kappa architecture comparison
   • Operational complexity of streaming systems
2. Kafka fundamentals2 hrs
   • Brokers, topics, and partitions
   • Zookeeper and KRaft metadata management
   • Replication and high availability
   • Log retention policies and compaction
   • Topic creation and partition sizing strategies
3. Producers and consumers2 hrs
   • Producer acknowledgements (acks=0, 1, all)
   • Consumer groups and offset management
   • Partition rebalancing mechanics
   • Manual offset commiting vs auto-commit
   • Handling consumer lag
4. Stream processing2 hrs
   • Event time vs processing time
   • Tumbling and sliding windows
   • Handling late data in streams with watermarks
   • Stateful operations in stream processing
   • Joining streams with reference data
5. Delivery guarantees and windows90 min
   • At-least-once vs exactly-once semantics
   • Idempotent producers configuration
   • Dead letter queues for poison pills
   • Handling broker outages gracefully
   • Transaction APIs in Kafka
6. Kafka monitoring and operations90 min
   • Monitoring consumer lag with JMX metrics
   • Broker disk space management
   • Upgrading Kafka clusters with zero downtime
   • Securing Kafka with SSL and SASL ACLs
   • Troubleshooting partition reassignment failures

Hands-on project: Deploy a Kafka topic producer and consumer service that processes clickstream events in real time and writes rolling aggregates to storage.

You can orchestrate complex DAG dependencies, task retries, and scheduling routines in Apache Airflow.

1. Workflow orchestration90 min
   • Directed Acyclic Graphs (DAGs)
   • Task dependencies and operators
   • Airflow architecture and scheduler internals
   • Metadata database role in Airflow
   • Worker execution models (Celery vs Kubernetes)
2. DAGs, tasks and dependencies2 hrs
   • PythonOperator and BashOperator
   • Passing data via XComs and best practices
   • Branching and conditional paths in DAGs
   • TaskFlow API usage with decorators
   • Dynamic DAG generation patterns
3. Scheduling and backfills90 min
   • Cron expressions and schedule intervals
   • Execution dates and data intervals in Airflow 2+
   • Running historical backfills safely
   • Catchup parameter configuration
   • Handling timezone gotchas in schedules
4. Retries and alerting90 min
   • Configuring task retry delays and exponential backoff
   • Slack and email alert callbacks on failure
   • Handling zombie tasks and task timeouts
   • SLA misses and warning notifications
   • Custom exception handling in operators
5. Monitoring pipelines90 min
   • Airflow webserver UI inspection
   • Log aggregation and debugging task failures
   • Task SLA monitoring and dashboards
   • Clearing and resetting task instances
   • Managing Airflow connections and variables securely
6. Airflow production best practices90 min
   • Avoiding top-level code execution in DAG files
   • Using TaskGroups for clean UI organisation
   • Managing secrets in Airflow backend storage
   • Scaling the Celery executor under heavy load
   • Writing idempotent custom Airflow operators

Hands-on project: Build a production Airflow DAG that chains API extraction, Spark processing, and warehouse loading with custom alerting on task failure.

You can transform raw warehouse data into analytics-ready models using dbt with built-in tests and lineage.

1. Analytics engineering90 min
   • The role of the analytics engineer
   • Modular SQL transformations
   • Staging, intermediate, and mart layers
   • Separating business logic from storage loading
   • Version controlling transformation code
2. Building models with dbt2 hrs
   • Jinja templating in SQL
   • Materialisations: views, tables, ephemerals, incremental
   • Ref function and dependency resolution
   • Config blocks and target environments
   • Macros and custom SQL functions
3. Tests and documentation90 min
   • Schema tests (unique, not_null, relationships, accepted_values)
   • Custom singular tests in dbt
   • Generating automated HTML documentation
   • Documenting column descriptions and metadata
   • Fail severity configuration (warn vs error)
4. Incremental models90 min
   • Optimising large table builds in dbt
   • Incremental strategies (append, merge, delete+insert)
   • Handling late-arriving records in incremental runs
   • Full refresh triggers and conditions
   • Partition clauses in incremental models
5. Data lineage90 min
   • Visualising dependency graphs in dbt cloud/core
   • Impact analysis for upstream schema changes
   • Source freshness checks and warnings
   • Exposure definitions for downstream dashboards
   • Tagging models for selective execution
6. dbt CI/CD workflows90 min
   • Running dbt build on pull requests
   • Slim CI with state:modified selector
   • Testing against ephemeral clone databases
   • Automating documentation publishing
   • Managing production deployments with dbt cloud/runner

Hands-on project: Build a dbt project that transforms raw staging tables into clean dimensional models, complete with unique and not-null tests.

You can design cloud storage data lakes, lakehouse architectures, and optimise warehouse compute costs.

1. Cloud storage and data lakes2 hrs
   • Object storage fundamentals (AWS S3)
   • Directory partitioning strategies by date and category
   • Lifecycle policies and storage tiers (Standard, Glacier)
   • Consistent read-after-write semantics in cloud storage
   • Preventing data lake swamp anti-patterns
2. Lakehouse architecture2 hrs
   • Combining data lakes and data warehouses
   • Open table formats (Delta Lake / Apache Iceberg)
   • ACID transactions on object storage
   • Time travel and schema evolution on data lakes
   • Compacting small files in lakehouse tables
3. Cloud warehouses and compute90 min
   • Separation of storage and compute in modern warehouses
   • Auto-scaling warehouse clusters
   • Concurrency scaling settings
   • Multi-region deployment considerations
   • Warehouse auto-suspend and resume tuning
4. Security and governance90 min
   • IAM roles and permission boundaries
   • Column-level and row-level security policies
   • Data masking and PII handling in warehouses
   • Encryption at rest and in transit
   • Audit logging for data access compliance
5. Cost optimisation90 min
   • Monitoring query spend and storage costs
   • Setting warehouse auto-suspend timeouts
   • Auditing and dropping unused tables and views
   • Analyzing warehouse credit consumption reports
   • Establishing budget alerts in cloud consoles
6. Multi-cloud and hybrid data strategies90 min
   • Comparing AWS, GCP, and Azure data offerings
   • Data egress cost pitfalls between clouds
   • Hybrid storage architectures
   • Cross-cloud data sharing mechanisms
   • Vendor lock-in mitigation strategies

Hands-on project: Configure an AWS S3 data lake and Snowflake warehouse architecture with secure IAM roles and cost-monitoring alerts.

You can implement data contracts, automated validation checks, and proactive pipeline observability.

1. Data validation and testing2 hrs
   • Great Expectations framework introduction
   • Expectation suites and data docs generation
   • Schema, nullability, and range assertions
   • Running validations inside pipeline DAGs
   • Handling validation failures gracefully
2. Observability and monitoring90 min
   • Tracking data freshness and volume metrics
   • Anomaly detection in pipeline row counts
   • Setting up metric alerts for silent failures
   • Dashboarding data health metrics
   • Proactive vs reactive data quality monitoring
3. Handling schema changes90 min
   • Backward and forward compatibility in data schemas
   • Schema evolution vs strict enforcement
   • Mitigating downstream breaks from upstream changes
   • Using schema registries for event streams
   • Detecting column drop or rename events early
4. SLAs and data contracts90 min
   • Defining data SLAs with upstream producers
   • Contract schema enforcement at ingestion
   • Version tracking for dataset definitions
   • Enforcing ownership tags on pipeline assets
   • Legal and compliance requirements for data accuracy
5. Incident response90 min
   • Debugging pipeline failures at 2 AM
   • Root cause analysis for corrupted data loads
   • Post-mortem documentation and action items
   • Rollback and data backfilling strategies after corruption
   • Communicating incidents to downstream business stakeholders
6. Data reliability engineering practices90 min
   • Applying SRE principles to data platforms
   • Error budget calculation for data pipelines
   • Chaos engineering for data systems
   • Automated pipeline health checks
   • Building robust fallback mechanisms

Hands-on project: Implement data quality assertions using Great Expectations on an ingestion pipeline to catch null anomalies before warehouse loading.

You can containerise data pipelines and automate deployments using Git and GitHub Actions.

1. Version control for data90 min
   • Git branching workflows for analytics and data code
   • Managing SQL scripts, DAGs, and configs in git
   • Code review standards for data engineering PRs
   • Handling large data files outside git with LFS
   • Tagging releases for pipeline deployments
2. CI/CD for pipelines2 hrs
   • GitHub Actions workflow syntax and triggers
   • Automated linting with Flake8 and SQLFluff
   • Running unit and integration tests in CI runners
   • Caching dependencies in workflow runs
   • Blocking merges on test failures
3. Containerising with Docker2 hrs
   • Writing Dockerfiles for Python pipeline jobs
   • Multi-stage builds for lean container images
   • Managing environment secrets and configuration
   • Running multi-container setups with Docker Compose
   • Debugging container permission and volume mounting issues
4. Environments and deployment90 min
   • Dev, staging, and production environment separation
   • Configuration management via environment variables
   • Zero-downtime deployment practices for data workers
   • Blue-green deployment strategies for pipelines
   • Rolling back failed service deployments
5. Best practices90 min
   • DRY principles in pipeline codebases
   • Secure secret storage (AWS Secrets Manager / GitHub Secrets)
   • Technical debt management in data platforms
   • Documenting architecture decisions (ADRs)
   • Maintaining reproducible local development setups
6. Infrastructure automation basics90 min
   • Introduction to infrastructure as code for data
   • Provisioning cloud buckets and warehouses via scripts
   • Managing state in infrastructure deployments
   • Automating environment teardown for testing
   • Security scanning of container images in CI

Hands-on project: Write a multi-stage Dockerfile for a Python pipeline service and configure a GitHub Actions workflow to run tests on every pull request.

You can make a warehouse query cheaper and prove who is allowed to read what.

1. Where warehouse money goes90 min
   • Scanned bytes vs compute time billing models
   • Query cost attribution by team and user
   • Identifying idle warehouse spend
   • Analyzing credit burn trends over time
   • Setting up warehouse cost anomaly alerts
2. Making queries cheap90 min
   • Partitioning and clustering strategies in practice
   • Maximising partition pruning efficiency
   • Using materialised views for repetitive aggregations
   • Rewriting correlated subqueries into efficient joins
   • Avoiding SELECT * anti-patterns in analytical scans
3. Access control and privacy90 min
   • Implementing row-level security predicates
   • Column-level security policies for sensitive attributes
   • Dynamic data masking for PII fields
   • Service account hygiene and principle of least privilege
   • Auditing role grants and permission drift
4. Catalogue and lineage90 min
   • Assigning and enforcing table and column ownership
   • Column-level lineage tracking across transformations
   • Deprecating and retiring legacy datasets safely
   • Integrating metadata with data catalogues
   • Tagging datasets for governance compliance
5. Compliance frameworks and data governance90 min
   • GDPR and data deletion requests (right to be forgotten)
   • India DPDP Act compliance requirements for pipelines
   • SOC 2 audit logging requirements for data access
   • Data retention and archival policies
   • Automating compliance reporting on stored data
6. Enterprise FinOps for data platforms90 min
   • Establishing a data FinOps practice
   • Tagging cloud resources for accurate chargeback
   • Forecasting data platform infrastructure spend
   • Negotiating enterprise storage and compute tiers
   • Cost-benefit analysis of real-time vs batch processing

Hands-on project: Find the three most expensive queries in a supplied warehouse, cut their cost with partitioning, clustering and pruning, then implement column-level access control and a data-catalogue entry with lineage for one critical table.

You can build, deploy, and present an end-to-end data platform while preparing for data engineering interviews.

1. End-to-end data-platform capstone4 hrs
   • Architecture design and scoping of the capstone system
   • Implementing API ingestion and batch processing
   • Configuring Snowflake warehousing and dbt transformations
   • Orchestrating workflows with Airflow DAGs
   • Containerising and deploying services via Docker
2. Portfolio and GitHub review2 hrs
   • Writing professional README documentation and architecture diagrams
   • Structuring repository code for recruiter evaluation
   • Highlighting architectural decisions and trade-offs
   • Recording a loom demo of the running platform
   • Cleaning commit histories and adding license files
3. Data-engineering mock interviews3 hrs
   • SQL live-coding problem solving under interview conditions
   • System design interview scenarios (e.g. clickstream ingestion at scale)
   • Debugging and troubleshooting pipeline failure walkthroughs
   • Behavioral questions for engineering roles
   • Explaining technical choices to non-technical stakeholders
4. Resume and LinkedIn optimisation2 hrs
   • Highlighting impact and scale metrics in resume bullet points
   • Positioning projects for senior and mid-level roles
   • Recruiter outreach strategies and networking
   • Optimising LinkedIn profile keywords for data engineering
   • Handling notice periods and salary negotiations
5. Certification and next steps90 min
   • Graduation requirements and final submission review
   • Claiming the Squarebrackets completion certificate
   • Continuing professional development and advanced certifications
   • Contributing to open-source data tools
   • Joining the Squarebrackets alumni engineering network
6. Advanced interview topics and deep dives90 min
   • Deep dive into distributed join algorithms
   • Advanced streaming semantics and exactly-once processing
   • Designing multi-tenant data platforms
   • Scaling metadata databases under heavy Airflow load
   • Preparing for senior engineering leadership loops

Hands-on project: Build and deploy a complete production data platform capstone integrating API ingestion, Spark processing, Snowflake modelling, Airflow orchestration, and Docker deployment.