Analytics engineer system design interview questions

As asked

You are joining a SaaS company that has Salesforce, Stripe, and a PostgreSQL application database. Design the full warehouse architecture from raw ingestion to BI consumption, including tools, layers, and governance touchpoints.

Sample answer outline

The architecture starts with an ELT ingestion layer using Fivetran or Airbyte to land raw data into a raw schema in Snowflake or BigQuery. A staging layer in dbt creates clean, typed views on top of raw tables 1:1. An intermediate layer applies business logic and joins. Mart tables serve specific domains like finance_mart and sales_mart. A semantic layer (dbt Semantic Layer or Looker) sits on top of marts for metric consistency. Governance includes row-level security for PII columns, dbt source freshness monitoring, CI/CD for model changes, and a data catalog. A strong answer discusses cost controls, environment isolation (dev/staging/prod schemas), and the decision of which warehouse platform to choose based on workload patterns.

Expect these follow-ups

• How would you handle PII from Stripe (card-holder names, emails) within this architecture?
• At what scale would you consider moving from a single Snowflake account to multi-cluster or multi-account?

As asked

A product team needs a dashboard that shows DAU and conversion funnel metrics updated every 15 minutes. Your current batch pipeline runs daily. How do you redesign the architecture to meet the SLA?

Sample answer outline

Switch from daily batch to event streaming using Kafka or Kinesis feeding into a streaming warehouse layer. For Snowflake, use Snowpipe or dynamic tables with incremental refresh. For BigQuery, use streaming inserts or a Dataflow pipeline. The dbt models serving the funnel metrics become incremental models refreshed every 15 minutes via an orchestrator trigger. Alternatively, use a purpose-built OLAP engine like ClickHouse or Apache Druid for the sub-hour dashboard and keep the batch warehouse for historical analysis. A strong answer discusses the cost and complexity tradeoffs, idempotency of incremental models, and late-arriving data handling.

Expect these follow-ups

• How do you handle duplicate events in a streaming-to-warehouse pipeline?
• What is Snowflake's dynamic tables feature and how does it simplify streaming pipelines?

As asked

Leadership wants business analysts without SQL skills to be able to answer their own questions without always going to the data team. Design the platform architecture that makes this possible while maintaining data quality.

Sample answer outline

The foundation is a well-modeled semantic layer that exposes business-friendly metric names, joins, and filters without requiring the user to understand SQL. BI tools like Looker (with LookML abstractions), Tableau with published data sources, or ThoughtSpot with AI-powered search sit on top. Governance comes from restricting direct warehouse access for business users and channeling all queries through the BI tool's permissioned data sources. A strong answer discusses training and documentation, metric definitions in a data dictionary, how to handle ad-hoc exploration versus curated dashboards, and the risk of query sprawl and compute costs when everyone can run arbitrary queries.

Expect these follow-ups

• How do you prevent a self-serve user from accidentally running a query that scans 10TB and costs $500?
• What is the tradeoff between a governed semantic layer (Looker) and a flexible notebook environment for self-serve analytics?

As asked

You are building analytics for a B2B SaaS company where each enterprise customer wants to see only their own usage data in a shared analytics environment. Design the data model and access control architecture.

Sample answer outline

All fact and dimension tables include a tenant_id column that is the primary partition key. Row-level security policies in Snowflake or BigQuery authorized views ensure each tenant can only query their rows. Each tenant's BI tool connection uses a warehouse role tied to their tenant_id, and the row access policy filters automatically. dbt models include tenant_id in all unique keys and tests. A strong answer discusses the risk of data leakage between tenants, how to test isolation, performance implications of row-level security on large tables, and whether to use physical table separation for very large tenants.

Expect these follow-ups

• What happens to query performance when row access policies are applied to a 1-billion-row table?
• How would you handle a tenant that wants to export all their data for their own analysis?

As asked

Finance requires that when revenue recognition rules change, all historical reported revenue can be restated without manually rerunning every model. How do you design the pipeline to support this?

Sample answer outline

Store the raw transaction data with full history and never aggregate destructively. Revenue recognition logic lives in a dbt model (not hard-coded in the source system export) so changing the model and running a full refresh regenerates all historical revenue correctly. Maintain audit tables that capture what revenue was reported at each reporting period using dbt snapshots or a separate audit schema. A strong answer discusses idempotency as the core design principle, the difference between a full-refresh strategy and an incremental strategy for this case, and how to communicate restatements to downstream consumers like Finance dashboards.

Expect these follow-ups

• How do you prevent downstream consumers from accidentally using the pre-restatement numbers during the reprocessing window?
• What is the difference between a restatement and a revision in accounting terms, and how does that affect your pipeline design?

As asked

Your team of 15 analysts spends too much time asking each other what a table means, where it comes from, and whether it is trustworthy. Design a practical data catalog solution without buying an expensive enterprise tool.

Sample answer outline

Start with dbt docs, which generates a web UI from model and column YAML descriptions automatically. Add data ownership fields in model YAML and propagate them via dbt. For lineage, dbt's built-in DAG plus integrations with open-source tools like OpenLineage or Atlan's free tier cover table and column lineage. For freshness and quality status, embed dbt test results into the catalog via Elementary or dbt Cloud's built-in observability. A strong answer discusses the cultural challenge of keeping documentation up to date, making documentation a PR review requirement, and what an enterprise tool like Alation adds that dbt docs does not.

Expect these follow-ups

• How do you enforce that every new dbt model must have a description and owner before it can merge?
• What is OpenLineage and how does it differ from dbt lineage?

As asked

A product team wants to analyze a five-step user signup funnel where users can drop off at any step and re-enter later. Design the dbt data model to support this analysis.

Sample answer outline

Start with a staging model that cleans the raw event stream. An intermediate model sessionizes events and identifies funnel step completions per user per attempt. A fact_funnel_events model has one row per user per funnel step per attempt, with columns for attempt_id, step_number, step_name, timestamp, and whether the step completed. A dim_funnel_attempts model has one row per attempt with start and end timestamps and the furthest step reached. Analysts query these models to compute step-by-step drop-off rates and time-in-step distributions. A strong answer discusses handling concurrent attempts, what constitutes a new attempt versus continuing the same one, and whether to model completed versus attempted steps separately.

Expect these follow-ups

• How do you handle a user who completes step 3 twice in the same attempt?
• What metrics would you expose in the semantic layer from this funnel model?

As asked

Finance needs a monthly P&L report, a cash flow statement, and a headcount dashboard, all of which need to tie to the same underlying numbers in the general ledger. How do you design the BI and data model layer to ensure consistency?

Sample answer outline

A single fct_journal_entries model serves as the source of truth for all financial figures, with account codes and cost center dimensions. Finance mart models pre-aggregate by account hierarchy and time period for the P&L and cash flow reports. The BI tool (Looker or Tableau) connects to the mart tables via a single published data source per finance domain. All three reports reference the same mart so numbers cannot diverge. A strong answer covers account hierarchy management in a dim_accounts table, handling foreign currency conversion, period locking to prevent retroactive changes, and access controls so only Finance can see GL-level detail.

Expect these follow-ups

• How do you handle the chart of accounts hierarchy in SQL when accounts can nest multiple levels deep?
• What do you do when an accountant notices a discrepancy between the dashboard and the audited financial statements?

As asked

Your pipeline ingests customer data including names, email addresses, and IP addresses. How do you handle PII throughout the warehouse stack to comply with GDPR and CCPA while still enabling analytics?

Sample answer outline

At ingest, raw PII is stored in a restricted raw schema accessible only to a service account. Staging models hash or pseudonymize PII using SHA256 or a salted hash for email and IP addresses, producing analytics-safe identifiers. Original PII is masked using Snowflake column masking policies so analysts who join on the hashed value cannot see the original. For right-to-be-forgotten requests, a delete procedure removes the PII from the raw table and the salt for that user's hash. A strong answer covers the difference between anonymization and pseudonymization under GDPR, data retention policies enforced via a row-level delete job, and the audit trail required to demonstrate compliance.

Expect these follow-ups

• How does hashing an email address help with GDPR right-to-erasure compliance?
• What is the risk of using SHA256 without a salt for email pseudonymization?

As asked

You are joining a SaaS company that has Salesforce, Stripe, and a PostgreSQL application database. Design the full warehouse architecture from raw ingestion to BI consumption, including tools, layers, and governance touchpoints.

Sample answer outline

The architecture starts with an ELT ingestion layer using Fivetran or Airbyte to land raw data into a raw schema in Snowflake or BigQuery. A staging layer in dbt creates clean, typed views on top of raw tables 1:1. An intermediate layer applies business logic and joins. Mart tables serve specific domains like finance_mart and sales_mart. A semantic layer (dbt Semantic Layer or Looker) sits on top of marts for metric consistency. Governance includes row-level security for PII columns, dbt source freshness monitoring, CI/CD for model changes, and a data catalog. A strong answer discusses cost controls, environment isolation (dev/staging/prod schemas), and the decision of which warehouse platform to choose based on workload patterns.

Expect these follow-ups

• How would you handle PII from Stripe (card-holder names, emails) within this architecture?
• At what scale would you consider moving from a single Snowflake account to multi-cluster or multi-account?

As asked

A product team needs a dashboard that shows DAU and conversion funnel metrics updated every 15 minutes. Your current batch pipeline runs daily. How do you redesign the architecture to meet the SLA?

Sample answer outline

Switch from daily batch to event streaming using Kafka or Kinesis feeding into a streaming warehouse layer. For Snowflake, use Snowpipe or dynamic tables with incremental refresh. For BigQuery, use streaming inserts or a Dataflow pipeline. The dbt models serving the funnel metrics become incremental models refreshed every 15 minutes via an orchestrator trigger. Alternatively, use a purpose-built OLAP engine like ClickHouse or Apache Druid for the sub-hour dashboard and keep the batch warehouse for historical analysis. A strong answer discusses the cost and complexity tradeoffs, idempotency of incremental models, and late-arriving data handling.

Expect these follow-ups

• How do you handle duplicate events in a streaming-to-warehouse pipeline?
• What is Snowflake's dynamic tables feature and how does it simplify streaming pipelines?

As asked

Leadership wants business analysts without SQL skills to be able to answer their own questions without always going to the data team. Design the platform architecture that makes this possible while maintaining data quality.

Sample answer outline

The foundation is a well-modeled semantic layer that exposes business-friendly metric names, joins, and filters without requiring the user to understand SQL. BI tools like Looker (with LookML abstractions), Tableau with published data sources, or ThoughtSpot with AI-powered search sit on top. Governance comes from restricting direct warehouse access for business users and channeling all queries through the BI tool's permissioned data sources. A strong answer discusses training and documentation, metric definitions in a data dictionary, how to handle ad-hoc exploration versus curated dashboards, and the risk of query sprawl and compute costs when everyone can run arbitrary queries.

Expect these follow-ups

• How do you prevent a self-serve user from accidentally running a query that scans 10TB and costs $500?
• What is the tradeoff between a governed semantic layer (Looker) and a flexible notebook environment for self-serve analytics?

As asked

You are building analytics for a B2B SaaS company where each enterprise customer wants to see only their own usage data in a shared analytics environment. Design the data model and access control architecture.

Sample answer outline

All fact and dimension tables include a tenant_id column that is the primary partition key. Row-level security policies in Snowflake or BigQuery authorized views ensure each tenant can only query their rows. Each tenant's BI tool connection uses a warehouse role tied to their tenant_id, and the row access policy filters automatically. dbt models include tenant_id in all unique keys and tests. A strong answer discusses the risk of data leakage between tenants, how to test isolation, performance implications of row-level security on large tables, and whether to use physical table separation for very large tenants.

Expect these follow-ups

• What happens to query performance when row access policies are applied to a 1-billion-row table?
• How would you handle a tenant that wants to export all their data for their own analysis?

As asked

Finance requires that when revenue recognition rules change, all historical reported revenue can be restated without manually rerunning every model. How do you design the pipeline to support this?

Sample answer outline

Store the raw transaction data with full history and never aggregate destructively. Revenue recognition logic lives in a dbt model (not hard-coded in the source system export) so changing the model and running a full refresh regenerates all historical revenue correctly. Maintain audit tables that capture what revenue was reported at each reporting period using dbt snapshots or a separate audit schema. A strong answer discusses idempotency as the core design principle, the difference between a full-refresh strategy and an incremental strategy for this case, and how to communicate restatements to downstream consumers like Finance dashboards.

Expect these follow-ups

• How do you prevent downstream consumers from accidentally using the pre-restatement numbers during the reprocessing window?
• What is the difference between a restatement and a revision in accounting terms, and how does that affect your pipeline design?

As asked

Your team of 15 analysts spends too much time asking each other what a table means, where it comes from, and whether it is trustworthy. Design a practical data catalog solution without buying an expensive enterprise tool.

Sample answer outline

Start with dbt docs, which generates a web UI from model and column YAML descriptions automatically. Add data ownership fields in model YAML and propagate them via dbt. For lineage, dbt's built-in DAG plus integrations with open-source tools like OpenLineage or Atlan's free tier cover table and column lineage. For freshness and quality status, embed dbt test results into the catalog via Elementary or dbt Cloud's built-in observability. A strong answer discusses the cultural challenge of keeping documentation up to date, making documentation a PR review requirement, and what an enterprise tool like Alation adds that dbt docs does not.

Expect these follow-ups

• How do you enforce that every new dbt model must have a description and owner before it can merge?
• What is OpenLineage and how does it differ from dbt lineage?

As asked

A product team wants to analyze a five-step user signup funnel where users can drop off at any step and re-enter later. Design the dbt data model to support this analysis.

Sample answer outline

Start with a staging model that cleans the raw event stream. An intermediate model sessionizes events and identifies funnel step completions per user per attempt. A fact_funnel_events model has one row per user per funnel step per attempt, with columns for attempt_id, step_number, step_name, timestamp, and whether the step completed. A dim_funnel_attempts model has one row per attempt with start and end timestamps and the furthest step reached. Analysts query these models to compute step-by-step drop-off rates and time-in-step distributions. A strong answer discusses handling concurrent attempts, what constitutes a new attempt versus continuing the same one, and whether to model completed versus attempted steps separately.

Expect these follow-ups

• How do you handle a user who completes step 3 twice in the same attempt?
• What metrics would you expose in the semantic layer from this funnel model?

As asked

Finance needs a monthly P&L report, a cash flow statement, and a headcount dashboard, all of which need to tie to the same underlying numbers in the general ledger. How do you design the BI and data model layer to ensure consistency?

Sample answer outline

A single fct_journal_entries model serves as the source of truth for all financial figures, with account codes and cost center dimensions. Finance mart models pre-aggregate by account hierarchy and time period for the P&L and cash flow reports. The BI tool (Looker or Tableau) connects to the mart tables via a single published data source per finance domain. All three reports reference the same mart so numbers cannot diverge. A strong answer covers account hierarchy management in a dim_accounts table, handling foreign currency conversion, period locking to prevent retroactive changes, and access controls so only Finance can see GL-level detail.

Expect these follow-ups

• How do you handle the chart of accounts hierarchy in SQL when accounts can nest multiple levels deep?
• What do you do when an accountant notices a discrepancy between the dashboard and the audited financial statements?

As asked

Your pipeline ingests customer data including names, email addresses, and IP addresses. How do you handle PII throughout the warehouse stack to comply with GDPR and CCPA while still enabling analytics?

Sample answer outline

At ingest, raw PII is stored in a restricted raw schema accessible only to a service account. Staging models hash or pseudonymize PII using SHA256 or a salted hash for email and IP addresses, producing analytics-safe identifiers. Original PII is masked using Snowflake column masking policies so analysts who join on the hashed value cannot see the original. For right-to-be-forgotten requests, a delete procedure removes the PII from the raw table and the salt for that user's hash. A strong answer covers the difference between anonymization and pseudonymization under GDPR, data retention policies enforced via a row-level delete job, and the audit trail required to demonstrate compliance.

Expect these follow-ups

• How does hashing an email address help with GDPR right-to-erasure compliance?
• What is the risk of using SHA256 without a salt for email pseudonymization?