shield_lock
金大哥
← 返回 Skill 列表
extension
分类: 开发与工程无需 API Key

dbt-projects-on-snowflake

在Snowflake中使用dbt PROJECT对象和事件表原生地部署、管理、执行和监控dbt项目。当您想要设置dbt开发工作区、将项目部署到Snowflake、安排自动运行、通过事件表监控执行情况或直接在Snowflake中启用团队协作时，请使用此技能。

person作者: jakexiaohubgithub
open_in_new仓库download下载资源包

dbt Projects on Snowflake

Deploy, manage, and monitor dbt projects natively within Snowflake using web-based workspaces, schema-level DBT PROJECT objects, and comprehensive event table telemetry.

Quick Start

Three Ways to Use dbt Projects:

  1. Snowsight Workspaces - Web-based IDE for interactive development
  2. DBT PROJECT Objects - Deployed projects for production execution
  3. Snowflake CLI - Command-line deployment and execution

Setup

Complete setup instructions including prerequisites, external access integration, Git API integration, and event table configuration are in references/SETUP.md.

Deployment Methods

Method 1: Snowflake CLI (Recommended)

# Deploy project
snow dbt deploy my_project --source .

# Execute commands
snow dbt execute my_project run
snow dbt execute my_project build

Method 2: Snowsight

  • Navigate to Projects → My Workspace
  • Create new project from Git repository
  • Configure profiles.yml
  • Deploy as DBT PROJECT object

Method 3: SQL Execution

Execute directly in SQL:

EXECUTE DBT PROJECT <db>.<schema>.<project> args='build';
EXECUTE DBT PROJECT <db>.<schema>.<project> args='build --full-refresh';
EXECUTE DBT PROJECT <db>.<schema>.<project> args='build --select tag:gold';

Scheduling & Automation

For automated scheduling with Snowflake Tasks, see the "Optional: Schedule Automated Runs" section in references/SETUP.md.

Event Table Monitoring

Event Table Configuration

Configure event tables following the Event Table Monitoring Configuration section in references/SETUP.md. This enables OpenTelemetry-based monitoring of dbt project executions.

Monitoring Queries

All monitoring scripts use parameterized event table references. Specify your event table location when running:

# Example: Query recent executions
snow sql -f scripts/recent_executions.sql --enable-templating JINJA \
  -D event_table=MY_DATABASE.MY_SCHEMA.EVENT_LOG

# Example: Check for errors
snow sql -f scripts/execution_errors.sql --enable-templating JINJA \
  -D event_table=LOGS_DB.PUBLIC.DBT_EVENTS

# Example: Performance metrics
snow sql -f scripts/performance_metrics.sql --enable-templating JINJA \
  -D event_table=MY_DATABASE.MY_SCHEMA.EVENT_LOG

Core Monitoring:

  • recent_executions.sql - Lists recent dbt project executions with severity
  • execution_errors.sql - Query ERROR logs to identify failures
  • performance_metrics.sql - Query CPU and memory usage metrics
  • trace_spans.sql - Query execution spans for timing analysis
  • execution_summary.sql - Summarize executions by project with error counts

Advanced Use Cases:

  • alert_failures.sql - Alert trigger for execution failures (returns error count)
  • performance_regression.sql - Week-over-week performance comparison
  • resource_usage.sql - CPU and memory consumption by project
  • audit_trail.sql - Complete execution audit trail for compliance

Event Table Structure

Event tables follow the OpenTelemetry data model with these key columns:

| Column | Description | | ----------------------- | --------------------------------------------------------------------------- | | TIMESTAMP | UTC timestamp when event was created (end of time span for span events) | | START_TIMESTAMP | For span events, the start of the time span | | TRACE | Tracing context with trace_id and span_id | | RESOURCE_ATTRIBUTES | Source identification: database, schema, user, warehouse, etc. | | SCOPE | Event scopes (e.g., class names for logs) | | RECORD_TYPE | Event type: LOG, SPAN, SPAN_EVENT, EVENT, METRIC | | RECORD | JSON object with record-specific data (severity, metric type, span details) | | RECORD_ATTRIBUTES | Event metadata set by Snowflake or code | | VALUE | Actual log message, metric value, or null for spans |

Best Practices

Performance Optimization:

  • Always filter by TIMESTAMP to limit scanned data (reduces cost)
  • Use RESOURCE_ATTRIBUTES for efficient filtering by project/database/schema
  • Archive old event table data (>90 days) to separate tables

Monitoring Strategy:

  1. Set event tables at DATABASE level, not account or schema
  2. Configure appropriate log/trace/metric levels per schema
  3. Always filter by TIMESTAMP to avoid scanning large event tables
  4. Use snow.executable.type = 'DBT_PROJECT' to isolate dbt events
  5. Leverage RESOURCE_ATTRIBUTES for filtering by project/database/schema
  6. Monitor ERROR severity logs for immediate alerts
  7. Use SPAN records to analyze execution timing and bottlenecks

Alerting Priorities:

  • High: Any ERROR in execution, execution >2x historical avg, warehouse credit anomalies
  • Medium: WARNING logs, test/model failures on critical models, performance trending down
  • Low: INFO logs, scheduled job confirmations, performance metrics for analysis

Event Table Troubleshooting

| Issue | Solution | | ----------------------- | -------------------------------------------------------------- | | No events captured | Verify event table set at DATABASE level with ALTER DATABASE | | Too many events | Adjust LOG_LEVEL/TRACE_LEVEL/METRIC_LEVEL per schema | | Slow monitoring queries | Always filter by TIMESTAMP first; consider archiving old data | | Missing metrics | Set METRIC_LEVEL = 'ALL' for schema | | Missing traces | Set TRACE_LEVEL = 'ALWAYS' for schema | | Cannot see project name | Verify snow.executable.type = 'DBT_PROJECT' filter |

Supported dbt Commands

| Command | Workspaces | EXECUTE DBT PROJECT | snow dbt execute | | -------- | ------------------- | ------------------- | ---------------- | | build | ✅ | ✅ | ✅ | | run | ✅ | ✅ | ✅ | | test | ✅ | ✅ | ✅ | | compile | ✅ | ✅ | ✅ | | seed | ✅ | ✅ | ✅ | | snapshot | ✅ | ✅ | ✅ | | deps | ✅ (workspace only) | ❌ | ❌ |

Team Collaboration

Flexibility: Team members can use different development approaches simultaneously:

  • Developer A: dbt Projects on Snowflake workspaces
  • Developer B: dbt Cloud
  • Developer C: Local VS Code with dbt CLI
  • All check into the same Git repository

Key Commands

| Command | Purpose | | ------------------------------- | --------------------------- | | snow dbt deploy <name> | Deploy project to Snowflake | | snow dbt execute <name> run | Run dbt models | | snow dbt execute <name> build | Run and test models | | snow dbt execute <name> test | Run tests only | | snow dbt list | List all dbt projects |

Troubleshooting

For setup and deployment issues, see references/SETUP.md.

For monitoring issues, see the Troubleshooting table in the Event Table Monitoring section above.

Related Skills

Complementary Observability:

  • dbt-artifacts skill - For cross-platform execution logging and historical trend analysis

When to use both together:

  • dbt Projects on Snowflake for real-time monitoring with OpenTelemetry event tables
  • dbt Artifacts for cross-platform historical analysis and long-term metrics

When to use one vs the other:

  • Use dbt Projects on Snowflake alone if you exclusively run dbt within Snowflake
  • Use dbt Artifacts alone if you run dbt outside Snowflake (dbt Cloud, Airflow, local)
  • Use both for comprehensive enterprise monitoring (real-time + historical)

Resources

Local Files

  • Monitoring Scripts: scripts/ - Ready-to-use parameterized SQL scripts for monitoring
    • Core Monitoring: recent_executions.sql, execution_errors.sql, performance_metrics.sql, trace_spans.sql, execution_summary.sql
    • Advanced Monitoring: alert_failures.sql, performance_regression.sql, resource_usage.sql, audit_trail.sql
  • Setup Guide: references/SETUP.md - Complete step-by-step setup including event table configuration and task scheduling

Official Documentation