DevOps meets DataOps: Streamlining analytics with dbt and Snowflake

For years, deploying data changes to production meant running manual processes based on imperative data pipelines. These processes were often error-prone and lacked even basic quality checks.

In the software world, DevOps changed the way that software engineers deploy applications. The use of version control, testing, and automation brought a new level of consistency and quality to deployments, resulting in fewer production bugs and less downtime.

A similar transformation is happening in the data world. DataOps adopts DevOps to data workflows, using techniques such as:

• Version control to track changes
• Automated deployment pipelines driven by code review approvals
• Automatically run tests to verify transformation logic in pre-production environments

I’ll dive into how DevOps principles are transforming data teams, changing the way the industry does analytics. I’ll also look at how you can use dbt and Snowflake to achieve faster time to value with declarative data transformations and automated deployments.

DevOps + DataOps: A new era of data management

Traditional data workflows are typically written in imperative languages, often using different languages and workflow tools for different workflows. Code often isn’t stored in a central location or deployed in a regular manner.

This scattershot approach hampers the scalability of data workflows in a number of ways:

• No one knows where the code for a given workflow resides
• There’s little code reuse across projects
• Analytics code changes aren’t adequately reviewed or tested prior to deployment, resulting in report downtime and additional dev work
• Changes can’t be easily reverted if a problem is detected in production

A DataOps approach to analytics code addresses these issues by adopting a mature analytics workflow, based on DevOps principles, that involves all data stakeholders. It treats every analytics code change as a software deliverable that involves planning, development, testing, deployment, and operationalization.

DevOps practices integrate once-siloed teams across the software development life cycle, from Dev to QA to Ops, resulting in both faster innovation and improved product quality. Used together, dbt and Snowflake support a DevOps approach to analytics workflows that results in higher quality and faster time to value.

dbt is the industry standard for data transformation at scale. It acts as a data control plane for your company’s data estate, providing a single, cross-vendor solution for modeling data transformations declaratively using just SQL and YAML.

Snowflake is a powerful AI Data Cloud that allows you to build data-intensive applications without the operational overhead. Snowflake supports a DevOps approach to accelerating development lifecycles through a number of features, such as API automation support to manage schema changes, dynamic tables, and more.

You could opt to use other, more imperative approaches in Snowflake to transform your data— e.g., Snowflake stored procedures. But these bring in more technical complexity and are less easy to test and deploy.

Because it uses only SQL and YAML, dbt provides a significant productivity boost, offering a declarative and easy-to-understand model for data transformation. In speaking with Snowflake and dbt users, we have heard universally that dbt’s declarative approach complements Snowflake’s DevOps support, providing a higher time-to-value than building imperative pipelines.

Hands-on: Deploying a DevOps-driven data pipeline

Let me show how this works in practice. In my last article, I showed how to model a Profit & Loss data pipeline using dbt and Snowflake. In this article, I’ll narrow in on how dbt and Snowflake features support a DevOps approach to analytics workflow deployments.

You can access the source code for this project on Github. We also have an end-to-end quickstart you can follow if you’re new to either dbt or Snowflake.

Prerequisites

• A Snowflake account, plus a user with the ACCOUNTADMIN permissions
• A GitHub account into which you can import (fork) the example code. You can join GitHub for free if you don’t have an account you can use for this walkthrough.

Setting up a development environment

A hallmark of the DevOps process is committing all code to a source control repository such as GitHub. This means committing any SQL Data Definition Language (DDL) commands for creating data warehouse schemas as well as all data transformation code.

This enables anyone to find data transformation code and contribute changes. That accelerates data pipeline development while also providing detailed tracking for all proposed and approved code changes.

Another DevOps feature is developing and testing in pre-production environments. This prevents changes from going live to production, not just until development is complete, but until:

• Another engineer on the team can perform a code review; and
• The changes are thoroughly tested against sample data

In Snowflake, you can use the EXECUTE IMMEDIATE command to run a SQL script directly from your GitHub repository. This enables you or any developer to create your own isolated environment in which you can load data, develop, and test changes:

USE ROLE ACCOUNTADMIN;

USE DATABASE SANDBOX;



ALTER GIT REPOSITORY DEMO_GIT_REPO FETCH;

EXECUTE IMMEDIATE FROM @DEMO_GIT_REPO/branches/main/scripts/deploy_environment.sql USING (env => 'DEV');

Once you’ve created an environment, you can create a dbt project and begin creating data transformation models. The GitHub project already has a sample dbt project, which you can run after configuring your project to access your Snowflake instance.

Besides simplifying data transformation development, dbt also supports DevOps deployments via support for testing and documentation. Developers can write data tests alongside their models and run them before, during, and after deployment. Documentation generated from model metadata gives data consumers details on what data means, where it comes from, and how to use it.

Automating deployments with dbt and Snowflake

Deploying your development pipeline with dbt is simple—all you need is to issue a dbt run command:

cd dbt_project

dbt run

Similarly, you can deploy your dbt documentation to end users with a few simple commands:

dbt docs generate

dbt docs serve

You can create as many stages as you need in your DevOps pipeline. For example, you can use the same script above with a target of PROD to create a production environment:

USE ROLE ACCOUNTADMIN;

USE DATABASE SANDBOX;



ALTER GIT REPOSITORY DEMO_GIT_REPO FETCH;

EXECUTE IMMEDIATE FROM @DEMO_GIT_REPO/branches/main/scripts/deploy_environment.sql USING (env => 'PROD');

You can also control how data is materialized by dbt based on environment. By default, dbt will use a CREATE TABLE AS SELECT query to re-create an entire table with each deployment. This probably isn’t what you want in a production deployment. Instead, for prod, you can switch to incremental materialization, which only creates new rows according to filter criteria that you control.

I already discussed how to deploy a new database schema using EXECUTE IMMEDIATE. But what if you need to make more complicated changes and migrations to existing data as part of a deployment? For that, you can write Python scripts that use the Snowflake API to instrument creating or altering tables, performing table operations, swapping table names, and other operations.

Monitoring and quality control

The job isn’t over once your changes are deployed. You need to monitor your pipeline for performance, as well as check incoming data to ensure that your pipeline anticipates data edge cases and transforms fields correctly.

The easiest way to do this is to run your dbt tests on incoming production data. You can automate this using CI/CD features in dbt Cloud, the commercial version of dbt built to support DevOps workflows. You can start with basic tests, expanding your test suite over time to become more proactive, avoid “alert fatigue” with meaningful and well-crafted alerts, and decrease time to issue detection and resolution.

Dynamic tables for near-real-time analytics

Snowflake is regularly adding new features to streamline the DevOps deployment process. One of my favorites is dynamic tables. With dynamic tables, you can tell Snowflake to use a query to transform data from one or more base objects. You can do this by making a simple dbt project configuration change:

You can fine-tune dynamic tables to specify a target freshness, or lag. This ensures that your data remains up to date within a specific freshness threshold.

For example, you could specify a lag of five minutes to ensure your destination table is never more than five minutes behind your base table. You can lower this value further to deliver near-real-time analytics using a few simple project configuration changes as opposed to writing a bunch of imperative logic.

Conclusion: The shift to DevOps-style automation

As AI increases the demand for high-quality data, data engineering teams need to move faster than ever. That’s why dbt and Snowflake are focused on streamlining analytics workflows with easy, declarative tooling and support for DevOps best practices.

Going forward, we’re looking at ways to make the analytics lifecycle even shorter and faster without sacrificing quality. dbt Copilot, for example, is leveraging AI to reduce the time required to generate models, documentation, and metrics. And Snowflake is working hand-in-hand with dbt on deeper integrations that will reduce cycles even further.

See for yourself how dbt and Snowflake can accelerate your analytics workflows by signing up for a free dbt Cloud account and exploring Snowflake’s DevOps capabilities.