dbt production deployment requires orchestration frameworks to schedule runs, manage dependencies, and ensure reliability. Understanding dbt in enterprise CI/CD pipelines is critical for senior data engineers.
Understanding dbt Execution Models
dbt can run locally (dbt run), on dbt Cloud (managed service), or via external orchestrators (Airflow, Dagster, GitHub Actions). Each model has trade-offs: local is simple but manual, dbt Cloud is managed but costly, orchestrators offer maximum control but complexity.
dbt Cloud: Managed Scheduling and Execution
dbt Cloud is the official SaaS platform for dbt. Provides job scheduling, email alerts, API access, and collaboration features. Models are stored in git repo; dbt Cloud pulls code and executes models on a hosted environment. Ideal for teams wanting managed infrastructure.
CI/CD Integration with GitHub Actions
GitHub Actions enable CI/CD without dbt Cloud. Run dbt tests on every PR to catch issues before merge. Full integration: lint, parse, test, build, then deploy to prod. Free for public repos, minimal cost for private repos.
External Orchestration: Airflow and Dagster
Enterprise orchestrators execute dbt as part of larger data pipelines. dbt operators allow orchestrators to trigger runs, monitor progress, and handle failures. Maximum control over execution, but requires managing infrastructure.
5 Complete Interview Questions with Solutions
Question 1: Design dbt Cloud Job Strategy
Design dbt Cloud job schedule for analytics pipeline. Staging daily 6 AM, marts 8 AM, tests 9 AM. Handle time-zones, retries, and alerts across all time zones.
Command: dbt run –select tag:staging
Schedule: 0 6 * * * (6 AM UTC daily)
Retries: 2 retries with 5-min delay
Notifications: Slack #data-alerts (on failure)
— Job 2: Marts (8 AM UTC)
Command: dbt run –select tag:marts
Schedule: 0 8 * * * (depends on staging)
Retries: 1 retry with 10-min delay
Notifications: PagerDuty (on failure)
— Job 3: Tests (9 AM UTC)
Command: dbt test
Schedule: 0 9 * * * (depends on marts)
Retries: 0 (don’t retry failed tests)
Notifications: Slack #data-quality (always)
Explanation: Sequential job execution respects dependencies. Retries handle transient failures. Notifications alert appropriate teams based on severity. Timeouts prevent infinite hangs.
Stripe – Senior Data Engineer
Question 2: CI/CD Pipeline with GitHub Actions
Build GitHub Actions workflow. Lint on PR, test on PR, deploy to prod on main merge. Block PR if tests fail. Design multi-stage validation pipeline.
on:
pull_request:
push:
branches: [main]
jobs:
lint:
runs-on: ubuntu-latest
steps:
– uses: actions/checkout@v3
– name: Lint SQL (sqlfluff)
run: sqlfluff lint dbt/models/
test:
needs: lint
steps:
– name: Run dbt tests
run: dbt test
deploy:
if: github.ref == ‘refs/heads/main’ && success()
needs: test
steps:
– name: dbt build (prod)
run: dbt build
Explanation: Multi-stage pipeline: lint catches syntax, test validates logic, deploy pushes to production. Failure blocks merge. Docs auto-publish after successful deploy.
LinkedIn – Senior Data Engineer
Question 3: Airflow DAG for Complex dbt Pipeline
Design Airflow DAG orchestrating: raw data extraction, dbt staging, dbt marts, tests, alerts. Handle retries and failures with proper error notifications and recovery.
extract_salesforce = BashOperator(
task_id=’extract_salesforce’,
bash_command=’python scripts/extract_salesforce.py’,
retries=2
)
dbt_staging = BashOperator(
task_id=’dbt_staging’,
bash_command=’cd /dbt && dbt run –select tag:staging’,
retries=1
)
dbt_marts = BashOperator(
task_id=’dbt_marts’,
bash_command=’cd /dbt && dbt run –select tag:marts’,
retries=1
)
dbt_tests = BashOperator(
task_id=’dbt_tests’,
bash_command=’cd /dbt && dbt test’,
retries=0
)
[extract_salesforce] >> dbt_staging >> dbt_marts >> dbt_tests
Explanation: Parallel extraction, sequential dbt stages, failure alerts. Retries handle transient errors. Queue assignment spreads load across compute resources.
Airbnb – Senior Data Engineer
Question 4: Managing dbt Secrets in Production
Securely manage database credentials across local, CI/CD, and production environments. Design secret rotation strategy to prevent credential leaks and unauthorized access.
analytics:
outputs:
dev:
account: “{{ env_var(‘SNOWFLAKE_ACCOUNT’) }}”
user: “{{ env_var(‘SNOWFLAKE_USER’) }}”
password: “{{ env_var(‘SNOWFLAKE_PASSWORD’) }}”
prod:
account: “{{ env_var(‘SNOWFLAKE_PROD_ACCOUNT’) }}”
user: “{{ env_var(‘SNOWFLAKE_PROD_USER’) }}”
password: “{{ env_var(‘SNOWFLAKE_PROD_PASSWORD’) }}”
# GitHub Actions (use secrets manager)
env:
SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
Explanation: Environment variables prevent secrets in version control. Separate dev/prod credentials. Use orchestrator secret managers. Rotate quarterly. Monitor usage.
Google – Senior Data Engineer
Question 5: Handling Failed Runs and Recovery
dbt run fails mid-execution (1000 models, 500 completed). Design recovery strategy. Resume versus full-refresh decision framework based on failure cause.
$ dbt retry # Resume from failed model
— Scenario 2: Data error
$ dbt run –select stg_orders+ # Rebuild affected DAG
— Scenario 3: Schema change
$ dbt run –full-refresh # Rebuild from scratch
— Production failure handling:
# 1. Alert on-call engineer
# 2. Stop downstream jobs
# 3. Investigate root cause
# 4. Execute recovery (retry/targeted rebuild/full refresh)
# 5. Validate data quality
# 6. Resume downstream
# 7. Post-mortem
Explanation: dbt retry resumes from failed model (fast). dbt run –select targeted rebuilds. dbt run –full-refresh full rebuild. Choose based on root cause and impact scope.
Stripe – Senior Analytics Engineer
15 Practice Questions (Test Yourself)
Question 6: Compare dbt Cloud versus GitHub Actions versus Airflow. When choose each based on cost, complexity, team expertise, and control requirements?
Google – Senior Analytics Engineer
Question 7: Backfill historical data in dbt efficiently. Load 2 years of missing data without rebuilding entire pipeline. Design efficient backfill strategy and timeline.
Databricks – Senior Data Engineer
Question 8: Monitor dbt runs in production. Track execution time, failures, data volume changes. Design observability stack with metrics and alerting.
Stripe – Senior Analytics Engineer
Question 9: Maintain environment parity between dev and prod. Ensure dbt behaves identically across environments. Design promotion strategy and validation checks.
Google – Senior Data Engineer
Question 10: Cost optimization in dbt production. Reduce compute and storage while maintaining data freshness. Design cost monitoring and optimization strategy.
Meta – Senior Data Engineer
Question 11: Scale dbt to 1000+ models. Performance optimization strategies, parallel execution limits, and query optimization for large projects.
Airbnb – Senior Analytics Engineer
Question 12: Design pull request workflows for dbt. How test changes before merge? Configure staging database for safe code review and validation.
Google – Senior Analytics Engineer
Question 13: Rollback strategy when model changes break downstream. Maintain data continuity during deploys. Design zero-downtime migration pattern.
Databricks – Senior Data Engineer
Question 14: Implement dbt run hooks for pre/post-actions. Backup tables, cleanup temporary data, send notifications. Design comprehensive hook strategy.
Stripe – Senior Data Engineer
Question 15: Cross-database dbt execution. Staging in Postgres, marts in Snowflake. Manage multi-database pipelines and handle data transfer between systems.
Uber – Senior Analytics Engineer
Question 16: Disaster recovery for dbt pipelines. Data corruption detected in prod. Implement rapid recovery without losing data. Design rollback and recovery procedures.
Google – Senior Data Engineer
Question 17: Implement dynamic scheduling based on source data freshness. Auto-trigger dbt when new data arrives. Event-driven orchestration pattern design.
Stripe – Senior Data Engineer
Question 18: Multi-tenant dbt deployment. Isolate analytics per customer. Separate schemas/databases strategy for data isolation and compliance.
Databricks – Senior Analytics Engineer
Question 19: dbt state and selection optimization. Use state:modified+ for faster CI. Manage state artifacts and improve pipeline performance with selective runs.
Meta – Senior Data Engineer
Question 20: Compliance and audit logging for dbt. Track all runs, code changes, and data access for regulatory requirements. Design audit-ready system.
Airbnb – Senior Data Engineer
Master dbt in Production
Get our comprehensive Data Analytics: Ace All The Interview Concepts course.