SQLFluff: Linting my way through dbt and finding peace (Kinda)

Intro: dbt — Solving Problems, Creating New Ones

TL;DR: The Technical Essentials

Under the hood, SQLFluff uses its own set of rules and checks to enforce formatting consistency, syntax correctness, and style guidelines. It’s like a

Python linter (think Pylint or Black) for your SQL codebase — but with its own unique features and configuration options.

Whether you’re running it locally or integrating it into your CI/CD pipeline, SQLFluff gives you the power to keep your SQL code clean, readable, and

maintainable.

If you’re just here for the technical implementation, here’s what you need to know:

1. Installation: Simple pip install sqlfluff
2. Basic Configuration:

• Start with default settings
• Key configuration in .sqlfluff:

[sqlfluff:rules]
exclude_rules = ST06  # Prevents column reordering

[sqlfluff:rules:capitalisation.keywords]
policy = lower  # Lowercase keywords

max_line_length = 0  # Disable line length restrictions

3. Integration Steps:

• Add to CI/CD pipeline
• Run on PRs
• Avoid auto-fixing pre-commit hooks
• Focus on gradual adoption

Want the full story? Read on for the journey, lessons learned, and detailed implementation:

Ah, dbt. The shining beacon of hope in the ever-chaotic world of data engineering. It promises clean, manageable SQL, model-driven transformations, and a sense of control over your data like you’ve never felt before. But if you’re anything like me, you probably went through that honeymoon phase where you started overusing dbt like a kid with their first set of LEGOs — trying to build a spaceship when all you really needed was a house.

When I first discovered dbt, it was like being handed the keys to the SQL kingdom. “I can transform everything!” I thought. I started creating models for everything — views for things that could’ve been single lines of SQL, custom macros for every other task, and naming conventions that had my future self questioning my life choices.

If you’ve ever added a “user_monthly_report_aggregate_v_final_final” model to your project, congratulations — you’re one of us. We welcome you to the club of enthusiastic dbt misuse, where over-engineering is an art form and debugging those 80-line Jinja templates is a rite of passage.

The Problem with Problem Solvers: Linting in SQL?

Now, while dbt can solve a lot of data transformation issues, it also introduces some delightful (read: horrific) new problems. Like messy SQL code that turns into a spaghetti disaster when you’re three layers deep in CTEs and macros.

Enter SQLFluff. It’s the hero we didn’t know we needed — until our SQL code started looking like a Jackson Pollock painting. But just like every great hero, SQLFluff isn’t perfect; it’s just better than the alternative: going through hundreds of lines of SQL manually while regretting all your life choices.

Photo by Nathan Cima on Unsplash

SQLFluff to the Rescue (Eventually)

So, how did I decide to integrate SQLFluff into my dbt projects? It all started when I noticed that my code was getting out of hand. Imagine a wild west of SQL: inconsistent indentations, random uppercase keywords, and SQL files that looked like they were written by five different people… in five different moods.

I was scrolling through Twitter (as one does when procrastinating real work) when I saw someone mention SQLFluff. “A SQL linter?” I thought. “Sounds like overkill… but also exactly the kind of overkill I’m into.”

After diving into SQLFluff, I was hooked. It was like Marie Kondo for SQL — it sparked joy by ruthlessly calling out every bad formatting decision I ever made. SQLFluff caught things I didn’t even know I should be embarrassed about. I quickly made it a part of my CI/CD pipeline for dbt, and let me tell you, my PR reviews started feeling like a much less stressful version of “SQL Code Nightmares.”

How I Integrated SQLFluff into My Project (Spoiler: Painfully at First)

Let’s talk real implementation. I was working on this dbt project that had slowly spiraled into a mess of tangled models and SQL. I had my fancy Airflow DAGs, my incremental models, and a handful of custom macros that I was overly proud of. But the SQL? Oh boy. It needed more than just a makeover — it needed SQLFluff.

1. Step 1: Installation (aka, My First Mistake)
   I naïvely thought, “I’ll just pip install sqlfluff, and my life will get easier.” Well, that part was true. The harder part was when SQLFluff started pointing out hundreds of issues across files I hadn’t touched in months. Turns out, you can’t ignore linting errors forever.
2. Step 2: Configuring SQLFluff (aka, Taming the Beast)
   SQLFluff comes with a lot of rules, and it took a while to figure out which ones mattered most for my team. We didn’t want to get lost in the weeds of hyper-specific rules (like spaces around commas), but SQLFluff also prevented us from turning our SQL into the wild west again. I tweaked the sqlfluff config, which was like choosing your battles—sure, I’ll fight for consistent casing, but do I really care about trailing commas? Not today.
3. Step 3: The First Run (aka, Facing My Sins)
   Running SQLFluff for the first time on a project you’ve neglected is like stepping into the light after months in a cave. There were hundreds of linting errors. The PR was longer than War and Peace, and the comments section could only be described as a “learning experience.”
4. Step 4: The Payoff (aka, Fewer Late-Night Regrets)
   After the initial pain of cleaning up all the issues (and muttering a few colorful phrases under my breath), I started to see the magic. My SQL was cleaner, more consistent, and — dare I say it — easier to read. The joy of opening a model and not being confused by my own code is a feeling I wouldn’t trade for anything.

Lessons Learned: SQLFluff, The Good Kind of Pain

Integrating SQLFluff into my dbt workflow felt like adding a personal editor for my SQL — one that never sleeps and doesn’t care if you’re on a tight deadline. Sure, the process was painful at first. I had to deal with all the mistakes I’d been sweeping under the rug. But once I got through the initial clean-up phase, SQLFluff became my best friend. I now run it on every PR, and it catches things I would’ve missed while giving me peace of mind that my code won’t come back to haunt me.

Conclusion: More Than Just a Linter

SQLFluff isn’t just a tool — it’s a lifestyle (well, at least in the data world). It brings structure to chaos, turns bad habits into good ones, and saves you from future headaches. Plus, once you get used to it, you’ll start to wonder how you ever survived without it.

So, if you’re out there wrangling messy SQL with dbt, do yourself a favor and give SQLFluff a try. Sure, it might feel like a tough-love intervention at first, but trust me — it’ll make your life (and your code) so much better. Just be ready for that first run — it’s humbling, but worth it.

Bonus 1: How I Sold SQLFluff to My Manager (Fictional, But Totally Plausible)

Picture this: Monday morning, coffee in hand, my manager walks by my desk, grumbling about inconsistent SQL formatting slowing code reviews. I see my chance.

Me: “You know, we could avoid all those formatting wars with one simple tool.”
Manager: “Simple? Does it cost a fortune or need six weeks to implement?”
Me: “Nope. SQLFluff — it’s free, easy to set up, and works like magic.”

I pause dramatically (timing is key).
Me: “Plus, it’s built around SQL best practices, the kind you’re always telling us are the foundation of quality code.”

Now they’re intrigued. I throw in the closer:
Me: “Imagine our SQL looking like it was written by one genius, instead of 15 sleep-deprived developers.”

Sold. By Friday, SQLFluff was in our pipeline, our SQL looked chef’s kiss perfect, and my manager was telling everyone it was a win for “best practices.”

Bonus 2: How We Implemented SQLFluff (A Bit More Technical)

When it came to rolling out SQLFluff, we kept things practical and focused on gradual improvement. Here’s how we did it:

1. Start with Vanilla
   We kicked off with SQLFluff’s default settings. Why? Because someone clearly put a lot of thought into these defaults. They’re simple, no complex rules to wrestle with, and they give you a great starting point. It was like getting a solid recipe before deciding to tweak it to our taste.
2. Check Formatting Doesn’t Break Things
   Of course, we needed to make sure these changes wouldn’t wreak havoc on our queries. One rule, ST06, reordered columns in SELECT statements, which was a no-go for us. We caught it early and added it to our exclude_rules:

[sqlfluff:rules]
exclude_rules = ST06

1. Casing Is King
   Casing turned out to be the hot topic. We argued (politely) that My_Column_Name wasn’t cool. If you already have a delimiter, why not make it easier to read? We went with lowercase for keywords:

[sqlfluff:rules:capitalisation.keywords]
policy = lower # Set keywords to lowercase

And as a side note, row length wasn’t a concern for us, so we ignored it:

max_line_length = 0

1. Automate It
   Once things were configured, we ensured SQLFluff ran in CI/CD pipelines. If the linting failed, the build broke. It kept our codebase pristine and made “linting drama” a thing of the past.
2. Don’t Over-Automate in Dev
   However, we avoided pre-commit hooks that automatically fixed things behind the scenes. We found they disrupted productivity — developers need to understand and own the fixes.

• Pre-commit configurations will be the subject of a future post, as we recognize that some developers may prefer different approaches.

1. Go Gradual (With a Twist)
   Initially, we thought we’d apply SQLFluff only to changed files. Over time, this would clean up the whole codebase. But our architect suggested a different approach: tackle most of it now but leave a few rules for manual review later.

Here’s what we decided to leave for manual fixes as a bulk for each :

• LT02 (Extra Whitespace): “SELECT — too much space here!”
• RF04 (Redundant Quoting): "column" FROM "table";
• RF02 (Redundant Alias): SELECT a.id FROM users AS a;

These can be addressed gradually, so we keep learning and refining.

Still Investigating:
We’re exploring whether pre-commit hooks should warn developers instead of auto-fixing their code. We’re also considering adding a dedicated linting step in CI/CD, separate from the usual end-to-end build.

SQLFluff is a journey, not just a tool. With thoughtful planning, it doesn’t just clean up your SQL — it transforms how your team writes it. 🚀

Resources:

Getting started
https://docs.sqlfluff.com/en/stable/gettingstarted.html

How to
https://medium.com/@lorindawson/optimizing-sql-code-and-enhancing-data-quality-an-in-depth-guide-to-sqlfluff-and-delta-lake-8b18505225db

Why you will need it :
https://benn.substack.com/p/the-case-against-sql-formatting