Data Engineering Career Foundations

Trace MySQL's logical SELECT processing order to explain and debug a query result.

Refactor a multistep SQL transformation into readable common table expressions.

Use ranking window functions to solve top-N and deduplication problems.

Use LAG, LEAD, and windowed aggregates to analyze time-ordered data.

Handle missing values and conditional business rules with NULL-aware SQL and CASE expressions.

Use transactions and an appropriate isolation level to protect a multi-statement data change.

Diagnose a slow query with EXPLAIN ANALYZE and select an effective index.

Implement an incremental upsert with MySQL INSERT ... ON DUPLICATE KEY UPDATE.

Determine the grain, entities, and relationships required by a data use case.

Normalize a relational schema to third normal form.

Select primary, foreign, natural, and surrogate keys for a relational model.

Choose between operational and analytical data models for a stated workload.

Design a star schema with correctly grained fact and dimension tables.

Implement a type-2 slowly changing dimension with effective dates and current-row indicators.

Model late-arriving facts and dimensions without corrupting historical results.

Evaluate a data model against representative queries and revise it to reduce ambiguity or inefficiency.

Extract records from a paginated REST API with timeout, retry, and rate-limit handling.

Normalize source timestamps into a consistent timezone and precision.

Implement high-water-mark extraction for an incrementally updated source.

Make a batch load restartable and idempotent with checkpoints and stable record keys.

Detect a source schema change and quarantine incompatible records.

Compute summary statistics and use them to identify suspicious data changes.

Store secrets outside source code and load configuration for separate environments.

Assemble an audited extract-transform-load pipeline that records row counts and run status.

Package a Python data pipeline as a reproducible Docker image.

Use Docker Compose to run a pipeline and MySQL as a local multi-container stack.

Explain how Airflow's scheduler, executor, workers, and metadata database coordinate a DAG run.

Represent pipeline steps and dependencies as an Apache Airflow DAG.

Configure Airflow schedules, data intervals, catchup, and start dates correctly.

Apply retries, timeouts, and trigger rules to predictable workflow failures.

Backfill a date range without duplicating previously loaded data.

Test and execute an Airflow DAG that orchestrates the earlier batch pipeline.

Explain how dbt separates data transformation from ingestion and why analytics engineering uses version-controlled SQL models.

Configure a dbt project with a MySQL-compatible adapter to connect to the course database.

Separate raw sources, staging models, intermediate transformations, and marts in a dbt project.

Build an explicit transformation dependency graph with dbt source and ref functions.

Apply generic and custom SQL tests to enforce model assumptions.

Configure an incremental dbt model with a correct unique key and update strategy.

Use a dbt snapshot to preserve changing source records over time.

Generate dbt documentation and inspect model lineage.

Select CSV, JSON, or Parquet for a data workload based on schema, compression, and access patterns.

Partition a Parquet dataset so query engines can prune unnecessary files.

Explain how Spark's driver, executors, lazy evaluation, and stages execute a DataFrame job.

Implement column, filter, group, and aggregation transformations with the PySpark DataFrame API.

Choose between broadcast and shuffle joins for a Spark workload.

Diagnose excessive shuffling or skew and select repartitioning, coalescing, or caching as a remedy.

Build a PySpark batch job that reads raw data and writes a partitioned analytical dataset.

Compare Delta Lake, Apache Iceberg, and Apache Hudi for ACID updates and table maintenance.

Distinguish event-stream processing from scheduled batch processing for a stated use case.

Explain how Kafka brokers, topics, partitions, and replication store and distribute events.

Publish keyed JSON events to Kafka with a Python producer.

Consume Kafka records with consumer groups and manage committed offsets.

Choose an event key that preserves required ordering while distributing load.

Relate at-most-once, at-least-once, and effectively-once delivery to failure scenarios.

Process event-time windows with Spark Structured Streaming and a watermark.

Evolve an event schema while maintaining compatibility with existing consumers.

Map the local course stack to corresponding managed services on AWS and their common Azure equivalents.

Write a least-privilege IAM policy for a data pipeline component.

Design S3 object keys, partitions, storage classes, and lifecycle rules for an analytical dataset.

Use boto3 against a local S3-compatible service to upload, list, and retrieve pipeline objects.

Select among AWS Glue, Athena, EMR, and Redshift for a batch analytics scenario.

Select among AWS Lambda, Kinesis, and Amazon MSK for a streaming scenario.

Add encryption and network boundaries to an AWS data architecture diagram.

Estimate the major cost drivers of an AWS pipeline and identify no-cost ways to practise each component.

Define measurable freshness, completeness, and availability objectives for a data product.

Emit and interpret pipeline metrics that reveal latency, throughput, failures, and stale data.

Turn data-quality failures into actionable alerts with clear ownership and severity.

Write an integration test that verifies data movement from a source fixture to a destination table.

Create a GitHub Actions workflow that runs Python and dbt quality checks on every change.

Describe S3 and IAM resources in Terraform and validate the configuration without deploying it.

Classify sensitive fields and apply appropriate masking, retention, and access controls.

Use logs, metrics, and a runbook to diagnose a failed pipeline and write a concise post-incident review.

Define a capstone data product with a specific user, source, output, and measurable success criteria.

Create an architecture diagram and data contract for the capstone pipeline.

Build a reproducible local development stack for the capstone with Docker Compose.

Implement a restartable incremental ingestion path for the capstone source.

Transform the ingested data into tested analytical models with dbt.

Orchestrate ingestion, transformation, and quality checks with Airflow.

Run controlled failure and recovery tests to demonstrate idempotency and observability.

Publish a recruiter-friendly project README with setup steps, design trade-offs, evidence, and diagrams.

Analyze entry-level data engineering job listings from Pune and the wider Indian market to identify recurring skill requirements.

Write evidence-based résumé bullets that communicate the impact and technical scope of course projects.

Organize project repositories so a recruiter can quickly find code, documentation, tests, and results.

Deliver a five-minute technical walkthrough of the capstone using clear architecture and trade-off explanations.

Solve a timed SQL interview problem involving CTEs and window functions.

Solve a timed Python data-transformation and debugging problem.

Design and defend a batch or streaming pipeline under stated scale, reliability, and cost constraints.

Answer behavioral interview questions with concise STAR stories drawn from project work.