Analytics engineer technical fundamentals interview questions

As asked

Explain the tradeoff between a normalized data model and a denormalized wide table in an analytics warehouse context. When would you choose each?

Sample answer outline

Normalization reduces redundancy and storage, makes updates easy, and is appropriate for OLTP workloads where rows are updated frequently. Denormalization pre-joins tables into wide flat tables, which speeds up read-heavy analytical queries because fewer joins are needed at query time. In analytics warehouses, storage is cheap and reads are the dominant workload, so some denormalization of mart tables is appropriate. However, over-denormalization creates maintenance problems when the same column appears in dozens of tables and needs updating everywhere. A strong answer discusses the Kimball star schema as the standard balance: normalized dimension tables and a denormalized fact with foreign keys.

Expect these follow-ups

• What is a star schema and how does it differ from a snowflake schema?
• When would you build a flat wide table instead of a star schema?

As asked

What is the difference between an OLAP system and an OLTP system, and how does this difference affect how you design a data model in each context?

Sample answer outline

OLTP systems are optimized for concurrent, high-frequency, small transactional reads and writes: normalized schemas to avoid update anomalies, row-oriented storage, and indexes on primary and foreign keys. OLAP systems (analytical warehouses) are optimized for large sequential scans, aggregations over millions of rows, and infrequent writes: columnar storage, denormalized star schemas, partitioning and clustering for scan pruning. Designing for OLAP means accepting some data redundancy to avoid joins, choosing appropriate materialization, and thinking in terms of aggregate query patterns rather than single-row lookups.

Expect these follow-ups

• How does columnar storage make aggregations faster than row-oriented storage?
• What is an HTAP system and when would you use one?

As asked

What does it mean for a data pipeline to be idempotent, and how do you design your dbt models to be idempotent?

Sample answer outline

An idempotent pipeline produces the same output whether it is run once or ten times on the same input. For dbt, table and view materializations are inherently idempotent because dbt uses CREATE OR REPLACE. Incremental models are not idempotent by default because each run appends or merges additional rows. Making incremental models idempotent requires ensuring the is_incremental() filter is deterministic, the unique key handles deduplication on merge, and a full-refresh rebuilds the table from scratch correctly. Non-idempotent pipelines cause duplicate data on retry, which is a common source of data quality incidents.

Expect these follow-ups

• How do you make a pipeline that appends to a table idempotent?
• What is the relationship between idempotency and the ability to safely retry a failed pipeline run?

As asked

What is the difference between ETL and ELT, and why did the analytics engineering discipline emerge from the shift toward ELT?

Sample answer outline

In ETL, data is transformed before it is loaded into the warehouse using a separate compute layer (Informatica, Talend). In ELT, raw data is loaded first and transformed inside the warehouse using SQL. Cloud warehouses like Snowflake and BigQuery have cheap, elastic compute that makes in-warehouse transformation fast and affordable. This made ELT practical and gave rise to tools like dbt that manage in-warehouse transformations as code. The analytics engineer role emerged to own the T in ELT: transforming raw data into analytics-ready models using software engineering practices like version control, testing, and CI/CD.

Expect these follow-ups

• Is there still a use case for ETL rather than ELT?
• What does the rise of dbt mean for traditional data engineering tools?

As asked

Explain how columnar storage works in a warehouse like Snowflake or BigQuery and why it makes aggregation queries faster than row-oriented storage.

Sample answer outline

In row-oriented storage, all columns for a row are stored contiguously on disk, so a full row scan is efficient but a single-column aggregation reads many unnecessary bytes. In columnar storage, all values for a single column are stored together, so aggregating one column out of 100 reads only the relevant data. Columnar storage also enables much higher compression ratios because values in a column tend to have similar types and ranges. Snowflake uses micro-partitions (16MB blocks) that store columnar data and metadata about min/max values for partition pruning. A strong answer mentions late materialization and predicate pushdown as additional benefits.

Expect these follow-ups

• Why is columnar storage worse for workloads that select many or all columns per row?
• What is a micro-partition in Snowflake and how does it enable partition pruning?

As asked

Walk me through the Git branching strategy you use for a dbt project. How do you handle feature development, hotfixes, and environment promotion?

Sample answer outline

A typical strategy is a main branch that represents production, with feature branches for each model change. PRs require at least one review and must pass CI (dbt build --select state:modified+ plus tests) before merging. Hotfixes for broken production models branch off main, are fixed, tested, and merged directly. Some teams use a develop or staging branch as an intermediate environment for integration testing. The dbt Cloud deployment environment maps main to production. A strong answer discusses the importance of small PRs for dbt changes (one model per PR is ideal), naming conventions for model change PRs, and the challenge of coordinating large refactors across multiple PRs.

Expect these follow-ups

• How do you handle a situation where two people are working on models that depend on each other?
• What is the risk of a single long-lived feature branch in a dbt project?

As asked

What is the logical order of execution for a SQL SELECT statement, and give me an example of a common mistake that results from misunderstanding this order?

Sample answer outline

The logical order is FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. A common mistake is trying to filter on an alias defined in SELECT inside a WHERE clause, which fails because WHERE is evaluated before SELECT. Another is using a window function alias in a WHERE clause for the same reason. The correct fix is to wrap the query in a CTE or subquery and filter in the outer WHERE. A strong answer also mentions that HAVING filters after GROUP BY and is the correct place to filter on aggregate values like HAVING COUNT(*) > 10.

Expect these follow-ups

• Why can you use a column alias in ORDER BY but not in WHERE?
• In what order does a modern warehouse actually execute SQL, versus the logical order?

As asked

How do dbt variables and environment variables work, and how do you use them to make a model behave differently in development versus production?

Sample answer outline

dbt vars are defined in dbt_project.yml or passed via --vars at run time and accessed in Jinja with var('my_var'). They are useful for parameterizing things like lookback windows or feature flags. Environment variables are OS-level and accessed via env_var('MY_ENV_VAR') in Jinja. A common pattern is to limit data volume in development by using a variable like is_dev that controls a WHERE clause filter, so developers run models on 90 days of data instead of 5 years. A strong answer also covers the security implication of not putting secrets in dbt vars (which appear in compiled SQL) versus using env_var for credentials.

Expect these follow-ups

• How would you limit a development model to only process the last 30 days of data?
• What is the security risk of putting an API key into a dbt var?

As asked

Explain the tradeoff between a normalized data model and a denormalized wide table in an analytics warehouse context. When would you choose each?

Sample answer outline

Normalization reduces redundancy and storage, makes updates easy, and is appropriate for OLTP workloads where rows are updated frequently. Denormalization pre-joins tables into wide flat tables, which speeds up read-heavy analytical queries because fewer joins are needed at query time. In analytics warehouses, storage is cheap and reads are the dominant workload, so some denormalization of mart tables is appropriate. However, over-denormalization creates maintenance problems when the same column appears in dozens of tables and needs updating everywhere. A strong answer discusses the Kimball star schema as the standard balance: normalized dimension tables and a denormalized fact with foreign keys.

Expect these follow-ups

• What is a star schema and how does it differ from a snowflake schema?
• When would you build a flat wide table instead of a star schema?

As asked

What is the difference between an OLAP system and an OLTP system, and how does this difference affect how you design a data model in each context?

Sample answer outline

OLTP systems are optimized for concurrent, high-frequency, small transactional reads and writes: normalized schemas to avoid update anomalies, row-oriented storage, and indexes on primary and foreign keys. OLAP systems (analytical warehouses) are optimized for large sequential scans, aggregations over millions of rows, and infrequent writes: columnar storage, denormalized star schemas, partitioning and clustering for scan pruning. Designing for OLAP means accepting some data redundancy to avoid joins, choosing appropriate materialization, and thinking in terms of aggregate query patterns rather than single-row lookups.

Expect these follow-ups

• How does columnar storage make aggregations faster than row-oriented storage?
• What is an HTAP system and when would you use one?

As asked

What does it mean for a data pipeline to be idempotent, and how do you design your dbt models to be idempotent?

Sample answer outline

An idempotent pipeline produces the same output whether it is run once or ten times on the same input. For dbt, table and view materializations are inherently idempotent because dbt uses CREATE OR REPLACE. Incremental models are not idempotent by default because each run appends or merges additional rows. Making incremental models idempotent requires ensuring the is_incremental() filter is deterministic, the unique key handles deduplication on merge, and a full-refresh rebuilds the table from scratch correctly. Non-idempotent pipelines cause duplicate data on retry, which is a common source of data quality incidents.

Expect these follow-ups

• How do you make a pipeline that appends to a table idempotent?
• What is the relationship between idempotency and the ability to safely retry a failed pipeline run?

As asked

What is the difference between ETL and ELT, and why did the analytics engineering discipline emerge from the shift toward ELT?

Sample answer outline

In ETL, data is transformed before it is loaded into the warehouse using a separate compute layer (Informatica, Talend). In ELT, raw data is loaded first and transformed inside the warehouse using SQL. Cloud warehouses like Snowflake and BigQuery have cheap, elastic compute that makes in-warehouse transformation fast and affordable. This made ELT practical and gave rise to tools like dbt that manage in-warehouse transformations as code. The analytics engineer role emerged to own the T in ELT: transforming raw data into analytics-ready models using software engineering practices like version control, testing, and CI/CD.

Expect these follow-ups

• Is there still a use case for ETL rather than ELT?
• What does the rise of dbt mean for traditional data engineering tools?

As asked

Explain how columnar storage works in a warehouse like Snowflake or BigQuery and why it makes aggregation queries faster than row-oriented storage.

Sample answer outline

In row-oriented storage, all columns for a row are stored contiguously on disk, so a full row scan is efficient but a single-column aggregation reads many unnecessary bytes. In columnar storage, all values for a single column are stored together, so aggregating one column out of 100 reads only the relevant data. Columnar storage also enables much higher compression ratios because values in a column tend to have similar types and ranges. Snowflake uses micro-partitions (16MB blocks) that store columnar data and metadata about min/max values for partition pruning. A strong answer mentions late materialization and predicate pushdown as additional benefits.

Expect these follow-ups

• Why is columnar storage worse for workloads that select many or all columns per row?
• What is a micro-partition in Snowflake and how does it enable partition pruning?

As asked

Walk me through the Git branching strategy you use for a dbt project. How do you handle feature development, hotfixes, and environment promotion?

Sample answer outline

A typical strategy is a main branch that represents production, with feature branches for each model change. PRs require at least one review and must pass CI (dbt build --select state:modified+ plus tests) before merging. Hotfixes for broken production models branch off main, are fixed, tested, and merged directly. Some teams use a develop or staging branch as an intermediate environment for integration testing. The dbt Cloud deployment environment maps main to production. A strong answer discusses the importance of small PRs for dbt changes (one model per PR is ideal), naming conventions for model change PRs, and the challenge of coordinating large refactors across multiple PRs.

Expect these follow-ups

• How do you handle a situation where two people are working on models that depend on each other?
• What is the risk of a single long-lived feature branch in a dbt project?

As asked

What is the logical order of execution for a SQL SELECT statement, and give me an example of a common mistake that results from misunderstanding this order?

Sample answer outline

The logical order is FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. A common mistake is trying to filter on an alias defined in SELECT inside a WHERE clause, which fails because WHERE is evaluated before SELECT. Another is using a window function alias in a WHERE clause for the same reason. The correct fix is to wrap the query in a CTE or subquery and filter in the outer WHERE. A strong answer also mentions that HAVING filters after GROUP BY and is the correct place to filter on aggregate values like HAVING COUNT(*) > 10.

Expect these follow-ups

• Why can you use a column alias in ORDER BY but not in WHERE?
• In what order does a modern warehouse actually execute SQL, versus the logical order?

As asked

How do dbt variables and environment variables work, and how do you use them to make a model behave differently in development versus production?

Sample answer outline

dbt vars are defined in dbt_project.yml or passed via --vars at run time and accessed in Jinja with var('my_var'). They are useful for parameterizing things like lookback windows or feature flags. Environment variables are OS-level and accessed via env_var('MY_ENV_VAR') in Jinja. A common pattern is to limit data volume in development by using a variable like is_dev that controls a WHERE clause filter, so developers run models on 90 days of data instead of 5 years. A strong answer also covers the security implication of not putting secrets in dbt vars (which appear in compiled SQL) versus using env_var for credentials.

Expect these follow-ups

• How would you limit a development model to only process the last 30 days of data?
• What is the security risk of putting an API key into a dbt var?