What a Junior } should know

Writes basic CQL queries to read and insert data into Cassandra tables. Understands partition keys and clustering columns in existing schemas. Follows data modeling guidelines established by the team.

Understands backup importance in data pipelines: checkpoint files, intermediate results. Uses snapshots before destructive operations. Knows how to recover data from staging tables.

Writes analytical queries in ClickHouse: SELECT with GROUP BY, ORDER BY, LIMIT. Understands MergeTree engine and partitioning. Inserts data through INSERT and uses tabular formats.

Designs data warehouse tables: star schema (fact + dimension), SCD types (Type 1, 2). Creates staging, intermediate and mart layers. Understands normalization and denormalization for analytics.

Creates indexes for accelerating extraction queries: B-tree for equality, composite for multi-column WHERE. Uses EXPLAIN to verify index usage in ETL queries.

Performs data warehouse schema migrations: CREATE/ALTER TABLE through SQL scripts. Understands DDL idempotency. Tests migrations on dev environment before production.

Writes SQL queries for data extraction: SELECT with JOIN, GROUP BY, window functions. Works with psycopg2/SQLAlchemy. Understands PostgreSQL types: JSONB, arrays, timestamps with timezone.

Optimizes SQL queries for ETL: avoids SELECT *, uses WHERE for source-side filtering. Understands pushdown predicates. Analyzes EXPLAIN for full table scans.

Understands basic database replication concepts: primary-replica topology, synchronous vs asynchronous replication, and replication lag. Monitors replica health using standard tools and dashboards. Follows team runbooks for failover procedures and connection string configuration.

Understands networking basics for data pipelines: TCP/IP for database connections, DNS for service discovery. Configures connectivity between ETL and data sources through VPN/VPC peering.

Creates Airflow DAGs: PythonOperator, BashOperator, task dependencies. Understands execution date, catchup, schedule interval. Monitors runs in Airflow UI. Debugs failed tasks through logs.

Understands Apache Spark fundamentals for data engineering: RDD/DataFrame APIs, basic transformations and actions, reading/writing Parquet/CSV/JSON. Follows team patterns for PySpark job structure, SparkSession configuration, and cluster resource allocation.

Uses Dagster or Prefect to build and schedule basic ETL pipelines. Understands assets, tasks, and flow concepts. Monitors pipeline runs and handles retries for transient failures.

Uses data catalog for dataset discovery: browses table descriptions, owners, lineage. Documents own tables: field descriptions, business glossary terms.

Understands data contracts: schema definition, SLA (freshness, completeness). Follows established contracts when creating tables. Documents schema of own data products.

Understands medallion architecture principles (bronze/silver/gold). Ingests data into raw landing zones using batch loaders and schema registries. Follows established patterns for Parquet/ORC file layout and catalog metadata.

Reads data lineage graphs: understands where data comes from and where it goes. Uses lineage for impact analysis when sources change. Documents dependencies of own models.

Writes basic data quality checks: NOT NULL, unique constraints, value ranges. Uses dbt tests or Great Expectations for validation. Understands metrics: completeness, accuracy, timeliness.

Sets up basic warehouse tables in Snowflake, BigQuery, or Redshift following team conventions. Implements simple partitioning and clustering strategies for common query patterns. Builds straightforward ELT pipelines that load raw data into staging areas and applies basic transformations for downstream consumption.

Creates dbt models: SELECT queries with ref() and source(), staging and mart models. Writes generic tests (unique, not_null). Understands DAG and dependencies between models.

Understands the fundamentals of Delta Lake / Apache Iceberg. Applies basic practices in daily work. Follows recommendations from the team and documentation.

Processes data through pandas: read_csv/read_parquet, filtering, grouping, merge. Understands DataFrame API. Works with data types and missing values (fillna, dropna).

Writes SQL for ETL: INSERT INTO SELECT, MERGE for upserts, CTE for readable transformations. Uses window functions (ROW_NUMBER, LAG, LEAD) for data processing.

Understands Kafka Streams fundamentals including KStream/KTable duality. Writes simple stream consumers and producers for data pipeline ingestion stages.