Uncategorized

dbt Tests, Documentation, Freshness Checks, Data Quality Validation: 20 Interview Questions

📅

✍️ TheDataMonk

⏱️ 6 min read

👁️ 5,374 views

dbt testing and documentation transform data pipelines from fragile scripts into trustworthy, auditable systems. Understanding dbt’s data validation framework is essential for production data engineering.

Understanding dbt Testing Philosophy

dbt testing ensures data quality at pipeline build time, not discovery time. Tests run after models execute, validating assumptions about data. Two types: generic tests (built-in, reusable) and singular tests (custom SQL).

Generic Tests: Built-In Data Quality Checks

dbt provides 4 generic tests: unique (no duplicates), not_null (required columns), accepted_values (enum validation), and relationships (foreign keys). Define tests in schema.yml, dbt runs them automatically. These cover 80% of common data quality needs.

Singular Tests: Custom SQL Validation

Singular tests are custom SQL files in tests/ directory. Write any validation logic in SQL, dbt runs query and fails if it returns rows (violations). Perfect for business logic validation beyond structural checks.

dbt Documentation: YAML Schema Files

dbt documentation lives in schema.yml files alongside models. Describe tables, columns, tests, and lineage. dbt generates documentation site from YAML. Docs are version-controlled and serve as single source of truth.

Freshness Checks: Source Data Monitoring

Source freshness checks verify that raw data is being loaded on schedule. Configure freshness with loaded_at_field and thresholds (warn_after, error_after). Alerts when data ingestion falls behind expected cadence.

5 Complete Interview Questions with Solutions

Question 1: Designing Comprehensive Test Strategy

Design test coverage for dim_customers (100M rows). Include unique, not_null, relationships, and custom business logic tests. Prioritize tests by impact.

models:

  – name: dim_customers

    columns:

      – name: customer_id

        tests:

          – unique

          – not_null

      – name: region_id

        tests:

          – relationships:

              to: ref(‘dim_regions’)

              field: region_id

Explanation: Layered testing: structural (unique, not_null), referential (relationships), and business logic. Catches data quality issues before downstream use.

Stripe – Senior Data Engineer

Question 2: Custom Singular Tests for Complex Business Logic

Create singular test validating that revenue by customer equals sum of individual orders. Design reconciliation check to catch calculation errors in aggregations.

— tests/fct_orders_revenue_reconciliation.sql

SELECT c.customer_id

FROM calculated_revenue c

LEFT JOIN dim_revenue d USING (customer_id)

WHERE ABS(c.revenue – COALESCE(d.revenue, 0)) > 0.01

Explanation: Singular tests validate complex business invariants. This reconciliation test ensures consistency across models.

LinkedIn – Senior Data Engineer

Question 3: Freshness Checks and Source Monitoring

Configure freshness checks for multi-source pipeline. Events load hourly, customers daily. Set warn/error thresholds and explain alert strategy.

sources:

  – name: raw_data

    tables:

      – name: events

        loaded_at_field: event_ingested_at

        freshness:

          warn_after: {count: 1, period: hour}

          error_after: {count: 2, period: hour}

      – name: customers

        loaded_at_field: customer_synced_at

        freshness:

          warn_after: {count: 24, period: hour}

          error_after: {count: 48, period: hour}

Explanation: Freshness checks alert when source data stops updating. Prevents cascading failures from stale upstream data. Critical for production monitoring.

Airbnb – Senior Data Engineer

Question 4: Comprehensive Documentation Strategy

Design documentation for analytics warehouse. Include source descriptions, model lineage, column metadata, and business definitions. Make docs maintainable and discoverable.

models:

  – name: dim_accounts

    description: Cleaned account dimension

    columns:

      – name: account_id

        description: Primary key

        tests: [unique, not_null]

      – name: annual_revenue_usd

        description: |

          Revenue in USD. Null for non-public companies.

Explanation: Comprehensive documentation (source/model/column level) enables analysts to understand data lineage and trustworthiness. Version-controlled and always in sync.

Google – Senior Data Engineer

Question 5: Testing Trade-offs (Coverage vs Execution Time)

100M row table: add 20 tests or run nightly only? Design tiered testing strategy balancing coverage and performance. Fast tests block builds, heavy tests run weekly.

— Fast tests (< 1 sec)
columns:
  - name: customer_id
    tests:
      - unique
      - not_null

-- Heavy tests (run weekly)
model_tests:
  - name: reconcile_revenue
    tags: [weekly]

$ dbt test --select tag:weekly  # Run once/week

Explanation: Layered testing: fast tests block builds, medium tests run daily, heavy tests run weekly. Balances quality and execution speed.

Stripe – Senior Analytics Engineer

15 Practice Questions (Test Yourself)

Question 6: When would you use dbt_expectations package versus built-in generic tests? What are the trade-offs in complexity, reusability, and execution speed for advanced validations?

Google – Senior Analytics Engineer

Question 7: Design test severity levels (warn vs error). When should you mark test as warn to alert only versus error to block dbt run? What’s the decision framework?

Databricks – Senior Data Engineer

Question 8: Freshness check failing in production. When should you skip blocking the pipeline versus hard-fail and halt downstream jobs? Decision framework for data staleness.

Stripe – Senior Analytics Engineer

Question 9: How do you test slowly changing dimensions? Validate SCD Type-2 logic with tests to ensure version tracking and date ranges are correct.

Google – Senior Data Engineer

Question 10: Custom macro test for outlier detection. Create test flagging rows beyond N standard deviations from mean. How would you make it generic and reusable?

Meta – Senior Data Engineer

Question 11: Integrate dbt tests with CI/CD pipeline. Block PR merge if tests fail. Design GitHub Actions workflow for automated quality gates on all pull requests.

Airbnb – Senior Analytics Engineer

Question 12: Test flakiness in CI pipelines. Query returns different results on re-run. How would you design stable, deterministic singular tests that don’t depend on timing?

Google – Senior Analytics Engineer

Question 13: Reconciliation test between raw and refined models. Ensure no data loss during transformations. How would you compare row counts and aggregates to validate completeness?

Databricks – Senior Data Engineer

Question 14: Documentation maintenance at scale. How do you prevent docs from becoming stale? What governance model keeps descriptions accurate as code evolves?

Stripe – Senior Data Engineer

Question 15: Combine generic plus singular tests in strategy. When use each? Design coverage matrix optimizing for completeness without over-testing.

Uber – Senior Analytics Engineer

Question 16: Automate test report generation for stakeholders. Summarize test results, failures, and data quality metrics. How would you surface insights to non-technical users?

Google – Senior Data Engineer

Question 17: Incremental model testing. How do you test new rows without re-testing historical data? Design efficient test execution for append-only models.

Stripe – Senior Data Engineer

Question 18: Sensitive data in test documentation. Hide PII columns from dbt docs without removing tests. How would you document without exposing confidential information?

Databricks – Senior Analytics Engineer

Question 19: Audit trail for data changes. Use tests to verify that all changes are logged and tracked for compliance. Design audit-ready validation checks.

Meta – Senior Data Engineer

Question 20: dbt test performance at 5B row table scale. Optimize test queries for speed without sacrificing coverage. Design fast validation that doesn’t timeout on huge datasets.

Airbnb – Senior Data Engineer

Master dbt Testing and Documentation

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

dbt Tests, Documentation, Freshness Checks, Data Quality Validation: 20 Interview Questions

Understanding dbt Testing Philosophy

Generic Tests: Built-In Data Quality Checks

Singular Tests: Custom SQL Validation

dbt Documentation: YAML Schema Files

Freshness Checks: Source Data Monitoring

5 Complete Interview Questions with Solutions

Question 1: Designing Comprehensive Test Strategy

Question 2: Custom Singular Tests for Complex Business Logic

Question 3: Freshness Checks and Source Monitoring

Question 4: Comprehensive Documentation Strategy

Question 5: Testing Trade-offs (Coverage vs Execution Time)

15 Practice Questions (Test Yourself)

Master dbt Testing and Documentation

📚 Browse All Content

Quick Links

dbt Tests, Documentation, Freshness Checks, Data Quality Validation: 20 Interview Questions

Understanding dbt Testing Philosophy

Generic Tests: Built-In Data Quality Checks

Singular Tests: Custom SQL Validation

dbt Documentation: YAML Schema Files

Freshness Checks: Source Data Monitoring

5 Complete Interview Questions with Solutions

Question 1: Designing Comprehensive Test Strategy

Question 2: Custom Singular Tests for Complex Business Logic

Question 3: Freshness Checks and Source Monitoring

Question 4: Comprehensive Documentation Strategy

Question 5: Testing Trade-offs (Coverage vs Execution Time)

15 Practice Questions (Test Yourself)

Master dbt Testing and Documentation

Related Interview Questions

📚 Browse All Content

Quick Links