The Art of Data Team Workflows

A data team workflow is the process that moves a change from an idea to production without anyone touching production directly. In practice it comes down to three environments and a version control process that ties them together. Production is what the business uses, backed by the main branch of your code. Development is a feature branch plus a personal schema where each developer builds and tests in isolation. In between sits a CI environment, where a pull request triggers an automated build and a review before anything merges. Repeat that loop for every change and you have a workflow that protects production and lets several people work at once.

Production: what the business is using

Production is the most straightforward environment. It's what the business is actively using, what reports connect to, the live version of every table and every piece of logic.

In the example database layout I use, that means three clear schemas: staging, warehouse and marts. Your naming may differ. The point is a dedicated set of schemas, or a separate database, that you treat as production.

Code, not stored procedures

Modern data tooling is code based. With dbt, for example, the transformation logic lives in files in a project rather than inside the database.

Historically a lot of teams kept stored procedures or other active code in the database. To make a change you went in, overrode it, and it was live. That habit is what the rest of this workflow replaces.

Best practice is a version controlled process behind your code. GitHub or GitLab store it in a structured way, usually in the cloud, and give you an organized, systematic way to update it.

The main branch

A branch in Git is a version of the code. The main branch, literally named main, is the latest agreed upon version, and it's the version deployed to production.

Open the repository on GitHub and you'll see the project's directories and files under main. Every model and every script is there, backed up and versioned.

Main and production should always match

How you deploy can vary. It can be automatic, it can refresh every day, and ideally it's both. You could even run the statements by hand.

Either way the rule is the same. The code in main is the code running in production, and the two should never drift apart.

Development: never commit straight to main

Now add a dev area beneath production. These are dedicated schemas where individual developers build, test and safely mess around with their changes.

Say you get a request to update a report. The instinct is to open the files in main, rename the models, and save. That change reflects in production immediately.

A good rule of thumb: never save or commit changes directly to the main branch. Doing that defeats the purpose of version control. You skip the functionality and the protection the workflow gives you.

If you catch yourself about to do it, pause. The route below is barely more work.

Feature branches and dev schemas

The alternative is a development branch, often called a feature branch. If Git is new to you, think of it as a clone of main that is yours to change. Nothing you do there touches main, and main is what's tied to production.

You still need to build and test those changes somewhere. That's why the branch goes hand in hand with a development schema.

One branch per feature

You'll have multiple branches over time. The reporting request gets one. You build and deploy it to your schema, finish the work, and create a new branch for the next feature.

Naming the schema

In the database, the dev schema sits right alongside everything else, but it's isolated. A convention I like is dev_ plus first initial and last name, so dev_mkahan. It's clearly dev and it's clear whose it is.

You have total control over it. Drop tables, insert whatever you want. It's separate from production.

Adding another developer

Now assume a second developer joins the team. They follow the exact same approach: their own feature branch cloned from main, and their own dev schema next to yours.

Two developers, two dedicated storage locations, two branches. They work in parallel, nobody overrides anyone, and production isn't impacted.

It's normal to have several branches open at any given time for different features. Each person has a safe place to build and test without stepping on the others.

The CI environment and the pull request

The next question is how dev code gets into main and on to production. Some teams stop here and just move it along. I'd introduce a third environment first.

You'll hear it called CI for continuous integration, test, UAT, or pre-production. I go with CI. Think of it as a test environment, and in the database it's one more schema.

Why a step in the middle

Merging directly from a feature branch into main technically works. I still caution against it. People make mistakes and overlook things, and this is the place to double check the work before it reaches production.

That step is the pull request, or merge request on some platforms. It's a review where the code is checked automatically, by other people on the team, or both, before it's allowed into main.

What a pull request shows

Here's an example. I had a feature branch called customer-dashboard and opened a pull request to merge it into main. The request carries a description of what I was working on.

The files changed tab breaks down every specific change. In my case I was creating new files, but edits and deletions show line by line, so a reviewer can walk through exactly what happened.

The two automations

At the bottom of the pull request were two checks. This is what people mean by CI/CD, and both were scripts I set up in the repository.

• CI build. Triggered when the pull request opens. It runs with a target of ci, builds the changed models into the ci schema, and runs checks there. A separate, safe copy of the work.
• Prod deploy. Triggered when the pull request is closed and merged is true. In other words, the checks passed, teammates approved, and the code is now in main. This job builds main into production. That's the CD, continuous deployment.

Setting this up is its own topic, and it can look like a lot at first. Conceptually it's simple: build once in a separate place to double check, then build into production only after approval.

The full loop

Back in the analytics database, the ci schema is typically shared. You can also customize it to be unique per pull request and drop automatically when the request closes.

One nuance from the example: dev and CI don't split into staging, warehouse and marts. Everything lands in a single schema per environment. That's a dbt customization, and it keeps the non-production side tidy.

Rinse and repeat. The workflow for every change is:

1. Create a feature branch from main.
2. Build and test in your dev schema.
3. Open a pull request. Automation builds to CI and runs checks.
4. A teammate reviews and approves.
5. Merge to main, and the deploy job builds production.

For a lot of teams this is a huge upgrade over saving straight to the database. It takes some reps to get used to. It's widely used, it's highly effective, and tools like dbt are designed with it in mind, so configuring targets for each environment is straightforward.

Related reading

• What Does Data Workflow Even Mean? (3 Examples)
• Modern Data Workflows, Explained
• The 3-Environment Design for Your Database
• Data Automation (CI/CD) with a Real Life Example

Final takeaway

This is the workflow I set up with nearly every small data team I work with, and it's often the biggest single upgrade to how they operate. The code in main is production, nobody commits to main directly, and a pull request with an automated build in CI stands between the two.