Modern Data Workflows - Explained

A modern data workflow is the day to day process a data team follows to get a change into production: separate environments, logic kept in code under version control, a branch per developer, and automated checks before anything goes live. The older approach is to edit a stored procedure in the database and have it take effect the moment you save, with no testing beyond whatever the developer did on their own. The modern approach keeps the logic in a tool like dbt, backed up on GitHub, so every change is visible line by line and goes through a pull request before it merges. Each developer builds into their own schema, a QA schema tests the merge, and production is refreshed from the main code every day. The result is production data that stays safe and quality checked without slowing anyone down.

Why workflow matters

Picture a simple architecture. Batch loads move data in, and somewhere in the middle there's transformation. For many teams that transformation means changes made directly in the database. Some stored procedures, a few edits, save.

The moment you save, it's production. It's live. The only testing that happened is whatever that developer did on their own, and none of it was automated.

I'll admit those can be fun times, and it feels like you can execute incredibly fast. It comes at the cost of quality and unnecessary errors.

The modern alternative

With the modern approach, and this is more than a tooling choice, the logic lives in code in a project. You can open the repository and read exactly what the code is.

When somebody wants to push a change, it's not a direct save. They create a branch, check out their own development version of the code, and test it there. You see every change line by line, and you can put automated testing in front of production.

It's a completely different way of thinking about the work. It catches errors early and protects the long term stability of the project.

The design and process

Let's say the whole thing runs on Snowflake, transformation is dbt, and the code is backed up on GitHub. How do we test changes along the way?

Production

Start with the production version of everything. For simplicity that's staging, warehouse and marts schemas, all in one database.

All of the code behind that logic is stored in the GitHub repository. The main branch is the live version of it.

A developer's side version

If you want to make changes, you create your own development version of the code on a branch. Done correctly, you also set up a separate database schema to test those changes in.

The goal is a safe space. Nothing stops you from selecting from the production raw data, since that's only a read. It's the transformed results that land in your own schema, so nothing in production is overridden.

Now imagine four developers. Each has their own branch and their own schema, working separately.

The pull request and QA

At some point you want that work in the main version. You open a pull request, or a merge request depending on the platform, through your version control tool.

That triggers a build into a separate schema or database called QA, or UAT, or whatever you name it. It takes the changes you're trying to move forward and tests them there, automatically.

So the flow for a change is:

1. A developer gets the code and makes changes on a branch.
2. They build into their own dev schema.
3. They push the branch and open a pull request.
4. Automated tests run against the QA schema and a teammate reviews the code.
5. Once it's good to go, it merges to main and deploys to production.

All of that happens behind the scenes to keep production safe, quality checked, and free of accidental saves.

Living below the surface

One more way to see it. The pipeline runs across Snowflake, dbt and GitHub. Dev and QA live underneath production, still reading the same raw data, because dbt only selects and creates new results. Nothing upstream is inserted or deleted.

On GitHub that shows up as several branches, three in my example, with pull requests and automation in place to validate code and keep the team on good practices.

What it looks like in Snowflake

Here's a sample Snowflake environment. In an analytics database there's the raw data, then staging, warehouse and marts. Those three are the production schemas.

The developer schemas

Behind the scenes a developer, say me or someone named John Doe, has separate schemas. Same source, deployed separately.

It can feel redundant to store that data twice. Storage isn't the cost it once was, so it's usually not a big deal, and you can limit the scope to a subset of the data if you don't need all of it.

The tradeoff is a bit more storage in dev and QA for a lot more confidence. That beats going straight to production and having stakeholders find the errors.

From dev to QA to production

You change the code and deploy only to your dev schema. In GitHub you open a pull request to merge into main. It's automatically checked and deployed to the QA, UAT or CI schema, whichever name you use. The naming isn't the important part.

That schema takes the changes from your branch, deploys them, and tests them. Only then do you merge to production.

From that point the production code is updated, and every daily run of staging, warehouse and marts is based on the code you tested.

Where to start

You don't have to adopt all of this at once. Some teams need to introduce the whole workflow. Others already have most of it and only need to tweak one or two areas.

Three areas to check

• Environments. Does every developer have a schema that isn't production, and is there a QA schema in between? If changes still save straight to the live tables, start here.
• Automated checks. Does opening a pull request build and test the change somewhere, or does that depend on someone remembering to do it?
• Naming conventions. With the same models existing in dev, QA and production, can you tell at a glance which schema is which and whose it is?

Pick the piece that's missing from your current process and add that first. For most teams it's a dev schema or an automated check on pull requests, and either one is a quick win.

Related reading

• What Does Data Workflow Even Mean? (3 Examples)
• The Art of Data Team Workflows
• The 3-Environment Design for Your Database
• 5 Ways To Improve Your Data Workflow

Final takeaway

Most of the teams I work with arrive with the tools already in place and the workflow missing. Separate schemas for development, a pull request that builds to QA, and a main branch that deploys production are what I set up first, because they protect everything else.