dbt Models – Tables, Views, Incremental Models, Ephemeral Models, Snapshot Models: 20 Interview Questions

dbt models are the core transformation unit in data build tool. Understanding different materialization strategies is crucial for building efficient, maintainable data pipelines that scale from startup to enterprise.

Understanding dbt Model Fundamentals

A dbt model is simply a .sql file containing a SELECT statement. dbt compiles and executes it, materializing the result as a table, view, or intermediate object. The materialization strategy determines how the model is built, stored, and queried—fundamentally impacting performance, storage, and refresh patterns.

dbt Model Materialization Strategies

Views: Lightweight, Dynamic Transformations

A view is a stored SQL query executed every time referenced. Views consume zero storage but incur execution overhead. Use views for lightweight transformations or rarely queried logic.

Tables: Persistent, Precomputed Results

Tables store materialized results as actual database tables. Every dbt run executes the entire SELECT and overwrites the table. Tables consume storage but are fast to query and ideal for BI tools.

Incremental Models: Append-Only Efficiency

Incremental models build only new data on subsequent runs. This dramatically reduces build time for append-only data like events and logs. On first run, it creates the table. On subsequent runs, it inserts only new rows.

Ephemeral Models: Temporary SQL Snippets

Ephemeral models do not create tables or views. Instead, dbt inserts their SQL directly into dependent models as CTEs. Use ephemeral for intermediate logic that clutters dbt DAGs.

Snapshot Models: Type-2 Slowly Changing Dimensions

Snapshots track SCD Type-2 changes by storing multiple versions with valid_from and valid_to timestamps. Every dbt snapshot run checks for changed rows and inserts new versions.

5 Complete Interview Questions with Solutions

Question 1: Choosing Materialization Strategy

Design materializations for a dbt project with: staging (40 models), intermediate aggregations (10 models), marts (5 models). Consider performance, storage, refresh time. Justify each choice.

Explanation: Staging as views (cost reduction), intermediate as ephemeral (DAG cleanup), marts as tables (BI optimization). This balances storage, performance, and maintenance.

Stripe – Senior Data Engineer

Question 2: Incremental Model Strategy

Build an incremental model for events (10M daily new events). Current table is 2TB. Design incremental logic with 3-day late-arrival buffer and explain –full-refresh scenario.

Explanation: 3-day buffer handles late arrivals. Incremental loads only new events (5 min vs 2 hours full refresh)—24x faster for normal runs.

LinkedIn – Senior Data Engineer

Question 3: Ephemeral Models for DAG Cleanup

Design 3 ephemeral intermediate models that feed a customer 360 mart. Explain why ephemeral is better than tables for this use case.

Explanation: Ephemeral perfect for intermediate aggregations used by single downstream model. Reduces database clutter while keeping logic modular.

Airbnb – Senior Data Engineer

Question 4: Snapshot Models for SCD Type-2

Design a snapshot for subscription plans. Capture version history with valid_from and valid_to timestamps. How would you query all versions or just current version?

Explanation: Snapshots auto-track changes using dbt_valid_from/dbt_valid_to. Compare rows each run, insert only changed rows. Perfect for SCD Type-2 and audit trails.

Google – Senior Data Engineer

Question 5: View Stack Performance Optimization

A pipeline with 10 view layers (stg to int to fct) is slow. Analyze why recomputation happens and propose hybrid materialization. Trade performance versus storage costs.

Explanation: View stacks recompute all dependencies. Strategic table materialization caches expensive intermediates for massive query speed improvement.

Stripe – Senior Analytics Engineer

15 Practice Questions (Test Yourself)

Question 6: When should you choose incremental materialization over full-table refresh? What breaks if incremental models experience schema changes (added/removed columns) between runs?

Google – Senior Analytics Engineer

Question 7: Design slowly changing dimension (SCD) Type-1 and Type-2 implementations in dbt. When is each approach appropriate based on business requirements for tracking historical changes?

Databricks – Senior Data Engineer

Question 8: How does on_schema_change configuration handle column additions/removals in incremental table models? What are the options (fail, ignore, sync_all_columns) and when use each?

Stripe – Senior Analytics Engineer

Question 9: Ephemeral models inline as CTEs. Does query performance differ between explicit CTEs in model SQL versus ephemeral model inlining? When might they diverge?

Google – Senior Data Engineer

Question 10: Snapshot unique_key behavior and impact on change detection. What happens if unique_key is not set or incorrectly identifies duplicates in the source data?

Meta – Senior Data Engineer

Question 11: Compare view materialization versus incremental materialization for append-only event tables. Model 2TB historical plus 10GB daily additions and explain performance implications.

Airbnb – Senior Analytics Engineer

Question 12: Explain the is_incremental() Jinja2 function and its execution context. How does first-run logic differ from subsequent incremental runs and what state does dbt track?

Google – Senior Analytics Engineer

Question 13: Design partitioning and clustering strategies for incremental models with 1TB-plus tables. How would you optimize for both incremental write speed and query performance on large historical data?

Databricks – Senior Data Engineer

Question 14: Explain dbt_valid_from and dbt_valid_to timestamp columns in snapshots. How would you query only current versions versus retrieve full historical lineage of changes?

Stripe – Senior Data Engineer

Question 15: When should you avoid incremental models entirely? Name 3 scenarios where –full-refresh is necessary and would become automatic, including data corrections and dimension changes.

Uber – Senior Analytics Engineer

Question 16: Design a hybrid materialization strategy that dynamically chooses between table, view, and incremental based on data volume and update frequency. How would you implement this pattern?

Google – Senior Data Engineer

Question 17: How does merge statement work in incremental models as an alternative to append/insert-overwrite? When should you use merge and what database systems support it efficiently?

Stripe – Senior Data Engineer

Question 18: Table materialization on large datasets (100GB-plus). What costs increase exponentially and how would you optimize rebuild time without sacrificing data freshness or accuracy?

Databricks – Senior Analytics Engineer

Question 19: Design nested ephemeral model chains where ephemeral models depend on other ephemeral models. What limits the depth of nesting and how does query compilation handle deep dependency graphs?

Meta – Senior Data Engineer

Question 20: Compare timestamp versus check_cols strategy in snapshots for change detection. When would you use each approach and what are the performance implications for large dimension tables?

Airbnb – Senior Data Engineer

Ready to Master dbt Models?

Get our comprehensive Data Analytics: Ace All The Interview Concepts course.