Analytics engineers sit between the data engineer who lands the raw data and the analyst who asks the questions. Your job is to turn messy source tables into clean, tested, documented models that everyone can trust, and nearly all of it is written in SQL.
This path follows how a modern analytics team actually builds a warehouse: readable modular SQL, a clean staging layer, star-schema marts, history with slowly changing dimensions, incremental loads that don't rebuild everything nightly, and the testing, version control and documentation habits that make your models trustworthy. Tools like dbt package these ideas, but every one of them is plain SQL underneath, so you can practice them all on Sqlism.
Analytics engineering starts with SQL other people can read. Instead of one 300-line query, break the logic into named steps with WITH: import the sources, transform them, then produce the final select. Each step has one job, so a reviewer can follow it top to bottom.
WITH orders AS (
SELECT * FROM stg_orders
),
customers AS (
SELECT * FROM stg_customers
),
customer_orders AS (
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS lifetime_value
FROM orders
GROUP BY customer_id
)
SELECT c.customer_id, c.customer_name,
COALESCE(co.order_count, 0) AS order_count,
COALESCE(co.lifetime_value, 0) AS lifetime_value
FROM customers c
LEFT JOIN customer_orders co ON co.customer_id = c.customer_id;customer_orders), not how they were built (cte2). Six months later the name is the only documentation anyone reads.The staging layer is a thin, one-to-one view over each raw source table. It renames cryptic columns, casts types, standardizes casing and NULLs, and nothing more: no joins, no business logic. Every model downstream reads from staging, never from raw tables directly.
CREATE VIEW stg_customers AS
SELECT
CAST(cust_no AS INTEGER) AS customer_id,
TRIM(cust_nm) AS customer_name,
LOWER(NULLIF(TRIM(email_addr), '')) AS email,
UPPER(NULLIF(country_cd, 'N/A')) AS country_code,
CAST(created_ts AS TIMESTAMP) AS created_at
FROM raw_crm_customers;Marts are the tables analysts and BI tools query. Model them as a star schema: fact tables of business events at a clearly stated grain, surrounded by dimension tables of descriptive attributes. Decide the grain first ("one row per order line") and every other design choice gets easier.
CREATE TABLE fct_order_lines AS
SELECT
ol.order_line_id, -- grain: one row per order line
o.order_date,
o.customer_id,
ol.product_id,
ol.quantity,
ol.quantity * ol.unit_price AS gross_amount
FROM stg_order_lines ol
JOIN stg_orders o ON o.order_id = ol.order_id;Dimensions change: customers move, products get re-categorized. A snapshot (SCD Type 2) keeps every version of a row with valid_from / valid_to dates, so last year's report still shows last year's truth. Facts then join on the business key and the date range.
SELECT f.order_line_id, f.order_date, d.region
FROM fct_order_lines f
JOIN dim_customer_history d
ON d.customer_id = f.customer_id
AND f.order_date BETWEEN d.valid_from AND d.valid_to;Rebuilding a billion-row fact table every night is slow and expensive. An incremental model only processes rows that arrived or changed since the last run, using a watermark such as updated_at, and merges them into the existing table with an upsert so re-runs never create duplicates.
MERGE INTO fct_orders t
USING (
SELECT * FROM stg_orders
WHERE updated_at > (SELECT MAX(updated_at) FROM fct_orders)
) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount, updated_at)
VALUES (s.order_id, s.status, s.amount, s.updated_at);A model isn't done until it's tested. The core tests are simple SQL queries that return the rows breaking a rule, and a test passes when it returns nothing: primary keys are unique and not null, foreign keys point at real dimension rows, and categorical columns only contain accepted values.
-- Relationship test: every order must belong to a known customer
SELECT f.order_id, f.customer_id
FROM fct_orders f
LEFT JOIN dim_customers d ON d.customer_id = f.customer_id
WHERE d.customer_id IS NULL; -- zero rows = passAnalytics engineers work like software engineers: every model change goes through Git, a pull request and code review. A CI pipeline builds only the changed models in an isolated schema and runs their tests before anything can merge, so broken SQL never reaches the dashboards people rely on.
A model nobody understands won't be trusted. Document each model's purpose and grain, describe its columns, and keep lineage visible so you can answer "if I change this source, which dashboards break?". Define key metrics (revenue, active customer) once, centrally, instead of letting every report invent its own version.
These projects use the modeling, testing and pipeline patterns from this path end to end.
Build staging, transformation and mart layers from raw data, the analytics engineer's core workflow.
Model order data into facts and dimensions and answer real business questions from it.
Lay out Bronze, Silver and Gold layers for a dataset of your choice and document every model.
Analytics engineering interviews mix SQL fluency with modeling judgement: expect window functions, "model this business process" whiteboard questions, and how you would test it.
Window functions, CTEs and the multi-step queries modeling work depends on.
Overlapping question bank for modeling, pipelines and incremental loads.
Practice explaining layered architectures, testing and data contracts.
25 questions covering everything above. Score 80% or higher to earn your Analytics Engineer badge on your dashboard.
25 questions · pass at 80%+ to earn your Analytics Engineer badge on your dashboard. One-time payment, lifetime access.