Orchestrating Complex Logic with dbt and Snowflake Tasks

In the Modern Data Stack, dbt (data build tool) has rightfully won the war for transformations. It brings software engineering practices (testing, version control, CI/CD) to SQL pipelines.

But real-world enterprise data isn’t always just a SELECT statement. Sometimes you need:

• Recursive logic.
• Cross-database dependencies.
• Permission management updates based on data content.
• Calling external APIs.

This is where a pure dbt approach can hit a wall. To build truly robust platforms, we often implement a Hybrid Orchestration Pattern.

The Hybrid Pattern

1. The Core: dbt

We use dbt for 90% of the workload. If it can be expressed as a DAG of SELECT statements, it belongs in dbt. This ensures lineage and documentation are preserved.

2. The Glue: Snowflake Tasks

For scheduling dbt jobs or triggering actions outside of dbt’s scope, Snowflake Tasks are powerful. We can chain tasks to run a dbt build, check the result, and then trigger a cleanup process.

CREATE TASK nightly_cleanup
  WAREHOUSE = compute_wh
  AFTER dbt_run_task
AS
  CALL system$manage_storage();

3. The Logic: Stored Procedures

When declarative SQL isn’t enough, we turn to Snowflake Scripting (SQL) for procedural logic. Example: Iterating through a list of new tenants and creating a secure view for each automatically.

CREATE OR REPLACE PROCEDURE dynamic_view_creation()
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  tenant_cursor CURSOR FOR SELECT tenant_id FROM config.tenants;
  tenant_id STRING;
BEGIN
  OPEN tenant_cursor;
  FOR record IN tenant_cursor DO
    tenant_id := record.tenant_id;
    -- Dynamic SQL execution
    EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW views.tenant_' || tenant_id || ' AS ...';
  END FOR;
  RETURN 'Views updated';
END;
$$;

Conclusion

Don’t be a tool purist. Use dbt for your models. Use Stored Procedures for your systems engineering. And use an orchestrator (like Airflow or Snowflake Tasks) to bind them together.

At Alps Agility, we specialize in these complex, hybrid architectures. Let’s discuss your pipeline challenges.