/using-dbt-for-analytics-engineering

Discovering Data with dbt show

Use dbt show to interactively explore raw data, understand table structures, and document findings for downstream model development.

When to Use

• Onboarding to a new dbt project with unfamiliar source data
• Investigating data quality issues reported by stakeholders
• Planning new models and need to understand source grain/structure
• Mapping relationships between tables before building joins

The Iron Rule

Complete all 6 steps for every table you will build models on.

Rationalizations That Mean STOP

Red Flags - You're About to Skip Steps

Stop if you catch yourself:

• Running only SELECT * without grain analysis
• Saying "the join worked" without checking orphan counts
• Noting "some nulls" without quantifying null rates
• Planning to "document later"
• Feeling time pressure and reaching for shortcuts
• Treating a large table count as permission to be less thorough

All of these mean: slow down, follow all 6 steps.

Large Scope Strategy

When facing many tables (20+), the answer is NOT abbreviated discovery. The answer is:

1. Scope ruthlessly first - Which tables will you actually build models on? Only those need discovery now.
2. Full methodology on scoped tables - Every table in scope gets all 6 steps. No exceptions.
3. Explicit deferral for out-of-scope - Document which tables you're NOT discovering and why. "Not needed for current project" is valid. "Too many tables" is not.

Wrong approach: "I'll do light discovery on all 47 tables" Right approach: "I'll do full discovery on the 8 tables needed for this project"

Core Method: Iterative Discovery

Step 1: Inventory relevant objects

Sources

When discovering new raw data, list all tables from the new source. E.g. listing all ecom source tables:

# quoting is critical when selecting sources
dbt ls --select "source:ecom.*" --output json

Review the existing YAML file at original_file_path to understand what's already documented.

Models

When previewing existing models, use standard node selection syntax:

# quoting is critical when selecting multiple nodes
dbt ls --select "my_first_model my_second_model" --output json

Review existing YAML files (normally colocated with the model's original_file_path) to understand what's already documented.

Step 2: Sample Raw Data

Preview rows from each source table:

dbt show --inline "SELECT * FROM {{ source('source_name', 'table_name') }}" --limit 50 --output json

Document immediately:

• Column names and warehouse-native data types
• Which columns appear to be identifiers vs attributes
• Obvious nulls, low-cardinality values, and values which are not obvious from their column name

Run standard EDA

Continue to use dbt show to run standard exploratory data analysis queries such as:

• Identify the grain of the table
• Check for duplicate/null primary keys
• Validate data ranges make sense (e.g. event timestamps are in the past)
• Profile key columns
• Identify potential foreign key relationships
• Inconsistent data types in a column

Documenting Findings

Create a discovery report that other agents can consume. Place in a data_discovery.md file alongside the SQL/YAML files. Do not use Jinja in these discovery files to avoid them being mistaken for doc blocks.

Discovery Report Template

## Source: {source_name}.{table_name}

### Overview
- **Row count**: X
- **Grain**: One row per [entity] per [time period]
- **Primary key**: column_name (verified unique)

### Column Analysis
| Column | Type | Nulls | Notes |
|--------|------|-------|-------|
| id | integer | 0% | Primary key |
| status | string | 2% | Values: active, inactive, pending |
| created_at | timestamp | 0% | UTC timezone |

### Data Quality Issues
- [ ] `status` has 15 rows with value "unknown" - clarify with stakeholder
- [ ] `amount` has negative values - confirm if valid or error

### Relationships
- `user_id` → `users.id` (5 orphan records found)
- `product_id` → `products.id` (clean join)

### Recommended Staging Transformations
1. Filter out `status = 'unknown'` rows or map to valid value
2. Cast `created_at` to consistent timezone
3. Add surrogate key if natural key unreliable

Previewing Data Efficiently

When using dbt show --inline to preview data, push LIMIT clauses as early as possible in CTEs to minimize data scanning. Never add a LIMIT at the end of the query - dbt show always adds an additional limit and you will cause a syntax error.

-- ✅ GOOD: Limit pushed early, minimizes scanning
with orders as (
    select * from {{ source('ecom', 'orders') }} limit 100
),
customers as (
    select * from {{ source('ecom', 'customers') }} limit 100
)
select ... from orders join customers ...

-- ❌ BAD: Full table scan before limit applied
with orders as (
    select * from {{ source('ecom', 'orders') }}
),
customers as (
    select * from {{ source('ecom', 'customers') }}
)
select ... from orders join customers ...
limit 100  -- Too late, and redundant with --limit flag

Common Mistakes

Assuming column names reflect content. Always verify with sample data; customer_id might contain account IDs

Not documenting findings. Discovery without documentation wastes effort; write it down immediately

Testing relationships on sampled data only. Orphan records may exist outside your sample; run full counts

Ignoring soft deletes. Check for deleted_at, is_active, or status columns that filter valid records