Transformation & Analytics SQL
Turning raw data into clean, modeled, trustworthy tables.
Backend Engineer track
Junior
Write correct code, ship small changes safely, ask good questions.
Nothing here yet.
Mid-level
Own a feature end to end without hand-holding.
- Common Table Expression (WITH)Named subqueries that make complex SQL readable.
- ETL / ELTExtracting, transforming and loading data between systems.
- Window FunctionsCalculations across related rows, like running totals and rankings.
Senior
Own a system, its failure modes, and its trade-offs.
- dbtTransforming warehouse data with versioned SQL.
Staff
Shape how many teams build, across systems.
Nothing here yet.
Principal
Set technical direction for the organization.
Nothing here yet.
Data Analyst track
Junior
Write correct SQL, build trusted dashboards, ask good questions.
Core: start here
- Common Table Expression (WITH)Named subqueries that make complex SQL readable.
- Exploratory Data AnalysisGetting to know a dataset before modelling it.
1 more junior concepts
- ETL vs ELTTransforming before loading vs loading raw data and transforming in the warehouse.
Mid-level
Own an analysis end to end, from vague question to recommendation.
Core: start here
- dbtTransforming warehouse data with versioned SQL.
- Window FunctionsCalculations across related rows, like running totals and rankings.
11 more mid-level concepts
- Data CleaningFixing types, formats, missing values and duplicates.
- Data ProfilingSummarizing a dataset's columns to understand what's really in it.
- Data TransformationCleaning, joining and reshaping raw data into useful tables.
- Deduplicating with ROW_NUMBERKeeping one row per key using a window function.
- Gaps and IslandsFinding consecutive runs and breaks in sequences with SQL.
- GROUP BY ROLLUP and CUBEComputing subtotals and grand totals in one query.
- Incremental ModelsProcessing only new or changed rows instead of rebuilding a whole table.
- QUALIFYFiltering on window function results without a subquery.
- SamplingWorking on a representative subset to go faster.
- SQL Transformation Models (dbt)Transformations written as versioned SELECT statements that build tables.
- Staging, Intermediate and Mart LayersA conventional way to organize transformation code.
Senior
Own experimentation and metrics design; call out bad numbers.
- Approximate AggregatesFast, nearly exact counts and percentiles on huge data.
Staff
Shape how the organization measures and decides.
Nothing here yet.
Principal
Set measurement strategy across the company.
Nothing here yet.
Data Engineer track
Junior
Build and fix pipelines from clear specs; write correct SQL.
Core: start here
- Common Table Expression (WITH)Named subqueries that make complex SQL readable.
- Data CleaningFixing types, formats, missing values and duplicates.
- Data ProfilingSummarizing a dataset's columns to understand what's really in it.
- Data TransformationCleaning, joining and reshaping raw data into useful tables.
- dbtTransforming warehouse data with versioned SQL.
- Deduplicating with ROW_NUMBERKeeping one row per key using a window function.
- ETL / ELTExtracting, transforming and loading data between systems.
- ETL vs ELTTransforming before loading vs loading raw data and transforming in the warehouse.
- SQL Transformation Models (dbt)Transformations written as versioned SELECT statements that build tables.
- Window FunctionsCalculations across related rows, like running totals and rankings.
2 more junior concepts
- Staging, Intermediate and Mart LayersA conventional way to organize transformation code.
- Standardizing ValuesMaking country codes, casing, units and currencies consistent.
Mid-level
Own pipelines and models end to end, including their quality.
Core: start here
- Incremental ModelsProcessing only new or changed rows instead of rebuilding a whole table.
4 more mid-level concepts
- Gaps and IslandsFinding consecutive runs and breaks in sequences with SQL.
- GROUP BY ROLLUP and CUBEComputing subtotals and grand totals in one query.
- QUALIFYFiltering on window function results without a subquery.
- SamplingWorking on a representative subset to go faster.
Senior
Design the platform's storage, processing and modeling choices.
- Approximate AggregatesFast, nearly exact counts and percentiles on huge data.
Staff
Shape how the whole organization produces and uses data.
Nothing here yet.
Principal
Set data strategy and architecture across the company.
Nothing here yet.