03. Top DBT Interview Questions
Top DBT Interview Questions
This section covers frequently asked dbt interview questions to help you prepare for certification and job interviews.
Questions include concepts, best practices, and real-world scenarios.
1. What is dbt and why is it used?
- dbt (Data Build Tool) is used for transforming data in the warehouse using SQL.
- Key benefits:
- Modular SQL development
- Version control via Git
- Testing and documentation
- Supports CI/CD pipelines
2. What are dbt models and materializations?
- Models → SQL select statements that define transformations
- Materializations → How models are stored in the warehouse:
table→ Persistent tableview→ Virtual table, recomputed on queryincremental→ Only processes new/updated rowsephemeral→ Inlined subquery, no table created
3. What is a snapshot in dbt?
- Snapshots track historical changes in source data (SCD Type 2)
- Key parameters:
unique_key→ identifies a recordstrategy→check(compare columns) ortimestamp(use updated_at)
- Example:
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='check',
check_cols=['email', 'first_name', 'last_name']
)
}}
select * from {{ source('app', 'customers') }}
{% endsnapshot %}
4. How does dbt handle testing?
- Built-in tests:
unique,not_null,relationships
- Custom tests: SQL-based assertions
- Tests can be added at column-level in YAML files:
columns:
- name: order_id
tests:
- unique
- not_null
5. Explain dbt exposures and lineage.
- Exposures → Downstream consumers of models (dashboards, ML models, reports)
- Lineage → Dependencies between sources, intermediate models, and marts
- Benefits: impact analysis, auditing, understanding data flow
6. What are dbt macros and how are they used?
- Macros are reusable SQL snippets written in Jinja
- Helps to standardize logic across multiple models:
{% macro days_between(start_date, end_date) %}
datediff(day, {{ start_date }}, {{ end_date }})
{% endmacro %}
- Called using:
{{ days_between('order_date', 'current_date') }}
7. How do you optimize incremental models?
- Use
is_incremental()to process only new/updated rows - Define a unique key for merging
- Leverage partitioning and clustering for large datasets
- Example:
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
8. How do you manage multiple environments in dbt?
- Use
profiles.ymlto definedev,staging, andprodtargets - Use environment-specific variables in
dbt_project.yml - Switch environments with:
dbt run --target prod
9. How do you integrate dbt with orchestration tools?
- Common tools: Airflow, Prefect, Dagster, dbt Cloud
- Define DAGs or jobs to:
- Run models
- Execute snapshots
- Run tests
- Trigger notifications
10. Best practices for dbt in production
Use incremental models for large datasets
Implement tests and snapshots
Maintain documentation and lineage
Isolate environments (dev/staging/prod)
Integrate with CI/CD and orchestration tools