Analytics Engineer interview questions

An analytics engineer turns raw warehouse data into tested, documented models that people can use consistently.

• Data engineers usually build ingestion and platform infrastructure that delivers data to the warehouse.
• Data analysts usually interpret prepared data to answer questions and create reports.
• Analytics engineers own the modeling layer between them through SQL, dbt, tests, documentation, and version control.

Dimensional modeling organizes analytical data around measurable business events and their descriptive context.

• Facts represent events or processes such as orders, payments, or page views.
• Dimensions describe entities such as customers, products, channels, and dates.
• The resulting structure supports understandable and consistent filtering, grouping, and aggregation.

A star schema places a central fact table around directly connected dimension tables.

• The fact table stores rows at a declared grain together with measures and dimension keys.
• Dimension tables provide descriptive attributes used to filter and group facts.
• Its simple join structure is usually easy for analysts and BI tools to understand.

A snowflake schema normalizes dimensions into related tables, while a star schema keeps dimensions more denormalized.

• Snowflaking can reduce repeated attributes and represent hierarchies explicitly.
• It also introduces more joins and can be harder for consumers to navigate.
• A star commonly favors analytical simplicity, while a snowflake applies normalization to selected dimensions.

A fact table contains business events or process observations at one consistent grain.

• Dimension keys connect each event to context such as customer, product, and date.
• Measures capture values such as quantity, revenue, duration, or balance when appropriate.
• An identifier such as an order number may remain in the fact table when it has no descriptive dimension.

A dimension table contains descriptive attributes that give business meaning to facts.

• A customer dimension might include segment, country, acquisition channel, and status.
• A dimension usually has a key referenced by one or more fact tables.
• Its attributes support labels, filters, groups, and hierarchies rather than event measures.

The grain states exactly what one row in a fact table represents.

• Examples include one row per order line, account per day, or support ticket.
• Every measure and dimension key must be valid at that same level of detail.
• A clear grain prevents mixed levels that cause duplication or incorrect aggregates.

A measure is a numeric property of a fact, and it is additive when summing it across relevant dimensions remains meaningful.

• Order quantity and line revenue are commonly additive across products, customers, and dates.
• A balance is often semi-additive because it can be summed across accounts but not across time.
• Ratios are usually non-additive and should often be calculated from component measures.

A natural key comes from the source business domain, while a surrogate key is generated for the analytical model.

• An email or source customer ID can change in value or format.
• A surrogate key gives the warehouse a stable identifier independent of source changes.
• Surrogate keys support multiple historical versions of one dimension member.

A conformed dimension is shared with consistent keys and definitions across multiple fact tables or processes.

• A common customer dimension can support both sales and support facts.
• Shared attributes make filters and groups mean the same thing across reports.
• Conformance enables reliable comparisons without redefining business concepts in every data mart.

A date dimension provides a consistent calendar vocabulary for analyzing facts over time.

• It can include day, week, month, quarter, year, weekday, and holiday attributes.
• Fiscal periods and business-specific calendars can be defined once and reused.
• Facts join to it through a date key so reports apply the same time groupings.

A transaction fact table stores one row for each business event at its most atomic useful grain.

• Examples include an order line, payment, shipment, or product click.
• It usually contains event measures and keys to dimensions valid when the event occurred.
• Atomic facts support flexible analysis because consumers can aggregate them differently.

A periodic snapshot fact table records the state of a process at regular intervals.

• One row might represent an account balance per day or inventory per product per week.
• Its grain includes the observed entity and the snapshot period.
• Snapshot measures are often semi-additive because summing repeated states across time is misleading.

An accumulating snapshot fact table tracks one process instance through several milestones.

• A row can represent an order from placement through payment, shipment, and delivery.
• Milestone dates and status fields are updated as the process advances.
• Its grain is one row per process instance rather than one row per milestone.

Type 1 overwrites a changed attribute, while Type 2 preserves history by creating a new versioned row.

• Type 1 suits corrections or attributes whose prior values are not analytically important.
• Type 2 uses effective dates, a current indicator, or similar fields to define versions.
• Facts can reference the surrogate key valid when the event occurred.

The useful logical order is FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and LIMIT.

• FROM and JOIN establish input rows before row-level filtering.
• GROUP BY forms groups, and HAVING filters them before final projection.
• Window functions are evaluated after grouping and HAVING but before the final result ordering.
• Aliases created in SELECT may be unavailable to earlier logical stages.

WHERE filters rows before aggregation, while HAVING filters groups after aggregation.

• A date or status condition on source rows normally belongs in WHERE.
• A condition such as COUNT(*) greater than ten belongs in HAVING.
• Filtering with WHERE changes which rows contribute to each aggregate.

Each output group must have one unambiguous value for every selected expression that is not aggregated.

• GROUP BY defines which input rows collapse into one result row.
• A selected column that varies within a group has no single value to return.
• Explicit grouping is clearer and more portable than relying on dialect-specific exceptions.

Most SQL aggregates ignore NULL values, while COUNT(*) counts rows regardless of NULLs.

• COUNT(column) counts only rows where that column is not NULL.
• SUM(column) and AVG(column) use non-NULL values and can return NULL when none exist.
• Replacing NULL with zero is valid only when zero has the correct business meaning.

Join fanout occurs when a join creates multiple output rows for one row at the measure's grain.

• Joining an order to several items repeats order-level columns once per item.
• Summing repeated order revenue then overstates the true total.
• Join cardinality and both input grains must be understood before aggregation.