Keep Your Analytics Lint Free With SQLFluff

Summary The growth of analytics has accelerated the use of SQL as a first class language. It has also grown the amount of collaboration involved in writing and maintaining SQL queries. With collaboration comes the inevitable variation in how queries are written, both structurally and stylistically which can lead to a significant amount of wasted time and energy during code review and employee onboarding. Alan Cruickshank was feeling the pain of this wasted effort first-hand which led him down the path of creating SQLFluff as a linter and formatter to enforce consistency and find bugs in the SQL code that he and his team were working with. In this episode he shares the story of how SQLFluff evolved from a simple hackathon project to an open source linter that is used across a range of companies and fosters a growing community of users and contributors. He explains how it has grown to support multiple dialects of SQL, as well as integrating with projects like DBT to handle templated queries. This is a great conversation about the long detours that are sometimes necessary to reach your original destination and the powerful impact that good tooling can have on team productivity. Announcements Hello and welcome to Podcast.__init__, the podcast about Python and the people who make it great. When you’re ready to launch your next app or want to try a project you hear about on the show, you’ll need somewhere to deploy it, so take a look at our friends over at Linode. With the launch of their managed Kubernetes platform it’s easy to get started with the next generation of deployment and scaling, powered by the battle tested Linode platform, including simple pricing, node balancers, 40Gbit networking, dedicated CPU and GPU instances, and worldwide data centers. Go to pythonpodcast.com/linode and get a $100 credit to try out a Kubernetes cluster of your own. And don’t forget to thank them for their continued support of this show! We’ve all been asked to help with an ad-hoc request for data by the sales and marketing team. Then it becomes a critical report that they need updated every week or every day. Then what do you do? Send a CSV via email? Write some Python scripts to automate it? But what about incremental sync, API quotas, error handling, and all of the other details that eat up your time? Today, there is a better way. With Census, just write SQL or plug in your dbt models and start syncing your cloud warehouse to SaaS applications like Salesforce, Marketo, Hubspot, and many more. Go to pythonpodcast.com/census today to get a free 14-day trial. Your host as usual is Tobias Macey and today I’m interviewing Alan Cruickshank about SQLFluff, a dialect-flexible and configurable SQL linter Interview Introductions How did you get introduced to Python? Can you describe what SQLFluff is and the story behind it? SQL is one of the oldest programming languages that is still in regular use. Why do you think that there are so few linters for it? Who are the target users of SQLFluff and how do those personas influence the design and user experience of the project? What are some of the characteristics of SQL and how it is used that contribute to readability/comprehension challenges? What are some of the additional difficulties that are introduced by templating in the queries? How is SQLFluff implemented? How have the goals and design of the project changed since you first began working on it? How do you handle support of varying SQL dialects without undue maintenance burdens? What are some of the stylistic elements and strategies for making SQL code more maintainable? What are some strategies for making queries self-documenting? What are some signs that you should document it anyway? What are some of the kinds of bugs that you are able to identify with SQLFluff? What are some of the resources/references that you relied on for identifying useful linting rules? What are some methods for measuring code quality in SQL? What are the most interesting, innovative, or unexpected ways that you have seen SQLFluff used? What are the most interesting, unexpected, or challenging lessons that you have learned while working on SQLFluff? When is SQLFluff the wrong choice? What do you have planned for the future of SQLFluff? Keep In Touch alanmcruickshank on GitHub Website LinkedIn Picks Tobias The Nevers Alan Lost Connections: Uncovering the Real Causes of Depression – and the Unexpected Solutions by Johann Hari (affiliate link) The Wim Hof Method by Wim Hof Closing Announcements Thank you for listening! Don’t forget to check out our other show, the Data Engineering Podcast for the latest on modern data management. Visit the site to subscribe to the show, sign up for the mailing list, and read the show notes. If you’ve learned something or tried out a project from the show then tell us about it! Email [email protected]) with your story. To help other people find the show please leave a review on iTunes and tell your friends and co-workers Join the community in the new Zulip chat workspace at pythonpodcast.com/chat Links SQLFluff Tails.com Hypothesis Podcast Episode Project Euler Flake8 Podcast Episode Black dbt Data Engineering Podcast Episode Snowflake Data Engineering Podcast Episode BigQuery SQL Window Functions ANSI SQL PostgreSQL MS SQL Server Oracle DB Airflow SQL Subquery Common Table Expression (CTE) The Rise Of The Data Engineer blog post The Downfall Of The Data Engineer blog post Object-Relational Mapper (ORM) Tableau Fishtown Analytics SQL Styleguide Mozilla SQL Styleguide The Zen of Python dbt Packages yapf Set Theory Flake8 SQL Plugin The intro and outro music is from Requiem for a Fish The Freak Fandango Orchestra / CC BY-SA