Configuring sources in YAML in dbt - Practice Exercises

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the effect of this source configuration in YAML?

Given this YAML snippet configuring a source in dbt, what will be the resulting source name and table name in dbt's catalog?

dbt

version: 2
sources:
  - name: sales_data
    tables:
      - name: transactions
        description: "Table of all sales transactions"
        identifier: sales_txn

A Source name: sales_data, Table name: transactions

B Source name: sales_data, Table name: sales_txn

C Source name: sales_txn, Table name: transactions

D Source name: transactions, Table name: sales_data

💡 Hint

Look at the 'identifier' field in the table configuration.

In dbt source configuration, the 'name' under 'sources' is the source name. The 'name' under 'tables' is the logical table name used in dbt, but the actual physical table name in the database is set by 'identifier'. Here, 'identifier: sales_txn' means the physical table is 'sales_txn'.

❓ data_output

intermediate

How many sources and tables are defined in this YAML?

Count the total number of sources and tables defined in this YAML configuration.

dbt

version: 2
sources:
  - name: marketing
    tables:
      - name: campaigns
      - name: leads
  - name: finance
    tables:
      - name: invoices
      - name: payments
      - name: refunds

A 3 sources, 4 tables

B 3 sources, 5 tables

C 2 sources, 5 tables

D 2 sources, 4 tables

💡 Hint

Count the top-level source entries and sum their tables.

There are two sources: 'marketing' and 'finance'. Marketing has 2 tables, finance has 3 tables, total 5 tables.

🔧 Debug

advanced

Identify the error in this source YAML configuration

What error will dbt raise when parsing this YAML source configuration?

dbt

version: 2
sources:
  - name: customer_data
    tables:
      - name: customers
        identifier: cust
      - identifier: orders

A TypeError: 'tables' must be a dictionary, not a list

B KeyError: 'identifier' missing for first table

C No error, configuration is valid

D SyntaxError: Missing 'name' for the second table

💡 Hint

Check if every table entry has a 'name' key.

Each table in 'tables' must have a 'name' key. The second table entry lacks 'name', causing a parsing error.

🧠 Conceptual

advanced

What is the purpose of the 'loaded_at_field' in a source configuration?

In dbt source YAML configuration, what does the 'loaded_at_field' property specify?

A It defines the timestamp field indicating when the source data was last loaded.

B It sets the default schema for the source tables.

C It specifies the primary key field of the source table.

D It marks the field used for incremental model partitioning.

💡 Hint

Think about tracking freshness of source data.

'loaded_at_field' tells dbt which column holds the timestamp of the last data load, useful for freshness checks.

🚀 Application

expert

Which YAML snippet correctly configures a source with a schema override and a freshness policy?

Select the YAML snippet that correctly sets a source named 'web_logs' with a schema 'analytics', a table 'events', and a freshness policy of 24 hours.

A

version: 2
sources:
  - name: web_logs
    schema: analytics
    tables:
      - name: events
        freshness:
          warn_after: {count: 24, period: hour}

B

version: 2
sources:
  - name: web_logs
    schema: analytics
    freshness:
      warn_after: {count: 24, period: hour}
    tables:
      - name: events

C

version: 2
sources:
  - name: web_logs
    tables:
      - name: events
        schema: analytics
        freshness:
          warn_after: {count: 24, period: hour}

D

version: 2
sources:
  - name: web_logs
    schema: analytics
    tables:
      - name: events
    freshness:
      warn_after: {count: 24, period: hour}

💡 Hint

Freshness is set per table, schema is set per source.

In dbt, 'schema' is set at the source level. 'freshness' is configured per table inside the tables list. Option A correctly places freshness under the table 'events'.

Practice

1. What is the main purpose of configuring sources in a dbt YAML file?

easy

A. To write SQL queries for data transformation

B. To tell dbt where to find raw data tables

C. To create dashboards for data visualization

D. To schedule dbt runs automatically

Solution

1. Step 1: Understand the role of source configuration

   Source configuration in dbt YAML files defines where raw data tables are located in the database.

2. Step 2: Differentiate from other dbt tasks

   Writing SQL queries and scheduling runs are done elsewhere, not in source YAML files.

3. Final Answer:

   To tell dbt where to find raw data tables -> Option B

4. Quick Check:

   Source config = raw data location [OK]

Hint: Sources define raw table locations in YAML [OK]

Common Mistakes:

• Confusing source config with SQL model code
• Thinking sources schedule runs
• Assuming sources create visualizations

2. Which of the following is the correct syntax to define a source in a dbt YAML file?

easy

A. source:
name: raw_data
table:
- customers

B. sources:
name: raw_data
tables:
- customers

C. sources:
- name: raw_data
tables:
- name: customers

D. source:
- raw_data:
tables:
- customers

Solution

1. Step 1: Recall correct YAML source structure

   The correct syntax uses 'sources' as a list with 'name' and nested 'tables' list, each with a 'name'.

2. Step 2: Compare options to syntax

   sources: - name: raw_data tables: - name: customers matches the correct indentation and keys exactly.

3. Final Answer:

   sources: - name: raw_data tables: - name: customers -> Option C

4. Quick Check:

   Correct YAML keys and indentation = sources: - name: raw_data tables: - name: customers [OK]

Hint: Look for 'sources' list with 'name' and 'tables' keys [OK]

Common Mistakes:

• Using singular 'source' instead of 'sources'
• Missing 'name' key for tables
• Incorrect indentation breaking YAML structure

3. Given this YAML snippet, what is the value of the 'loaded_at_field' for the source 'sales_data'?

sources:
  - name: sales_data
    tables:
      - name: transactions
        loaded_at_field: transaction_date

medium

A. transaction_date

B. transactions

C. loaded_at_field

D. sales_data

Solution

1. Step 1: Locate the 'loaded_at_field' key in YAML

   It is nested under the 'transactions' table inside the 'sales_data' source.

2. Step 2: Identify the value assigned

   The value assigned to 'loaded_at_field' is 'transaction_date'.

3. Final Answer:

   transaction_date -> Option A

4. Quick Check:

   loaded_at_field value = transaction_date [OK]

Hint: Find 'loaded_at_field' key's value under table [OK]

Common Mistakes:

• Confusing source name with field value
• Picking table name instead of field value
• Misreading YAML indentation levels

4. Identify the error in this source configuration YAML:

sources:
  - name: marketing_data
    tables:
      - name: leads
        freshness:
          warn_after:
            count: 12
            period: hours
          error_after:
            count: 1
            period: days

medium

A. 'warn_after' and 'error_after' counts are reversed

B. The indentation under 'freshness' is incorrect

C. The 'error_after' period should be less than 'warn_after'

D. The 'period' values must be singular strings

Solution

1. Step 1: Understand dbt freshness period syntax

   dbt freshness requires singular 'period' values like 'hour', 'day', 'minute'. Plural forms ('hours', 'days') are invalid and cause errors.

2. Step 2: Check the YAML periods

   'period: hours' and 'period: days' use plural, which dbt does not recognize.

3. Step 3: Rule out other options

   A: Counts logical (12 hours warn before 1 day/24 hours error). B: Indentation correct. C: Incorrect--error_after time must be *longer* than warn_after.

4. Final Answer:

   The 'period' values must be singular strings -> Option D

5. Quick Check:

   period: hour/day (singular only) [OK]

Hint: dbt freshness periods must be singular (hour, day) [OK]

Common Mistakes:

• Using plural periods ('hours', 'days')
• Incorrect YAML indentation
• Thinking error_after time should be shorter than warn_after

5. You want to add a test to ensure the 'email' column in the 'users' table source is never null. Which YAML snippet correctly adds this test?

hard

A. sources:
- name: app_data
tables:
- name: users
columns:
- name: email
tests:
- not_null

B. sources:
- name: app_data
tables:
- name: users
tests:
- column: email
test: not_null

C. sources:
- name: app_data
tables:
- users:
columns:
- email:
tests:
- not_null

D. sources:
- name: app_data
tables:
- name: users
columns:
- email
test: not_null

Solution

1. Step 1: Recall correct test syntax in source YAML

   Tests are added under 'columns' with 'name' and a 'tests' list containing test names.

2. Step 2: Check each option's structure

   sources: - name: app_data tables: - name: users columns: - name: email tests: - not_null correctly uses 'columns' list with 'name' and 'tests' list containing 'not_null'.

3. Step 3: Identify errors in other options

   sources: - name: app_data tables: - name: users tests: - column: email test: not_null uses wrong keys, sources: - name: app_data tables: - users: columns: - email: tests: - not_null has wrong nesting, sources: - name: app_data tables: - name: users columns: - email test: not_null uses 'test' instead of 'tests'.

4. Final Answer:

   sources: - name: app_data tables: - name: users columns: - name: email tests: - not_null -> Option A

5. Quick Check:

   Tests under columns with 'tests' list = sources: - name: app_data tables: - name: users columns: - name: email tests: - not_null [OK]

Hint: Tests go under columns with 'tests' list [OK]

Common Mistakes:

• Using 'test' instead of 'tests'
• Wrong nesting of columns and tests
• Misnaming keys like 'column' instead of 'name'