There are a few different ways you can organize your catalog. The trick is finding out what works for you best now, what may work down the road, or does a combination of two or more organizational techniques work best for you.
Organize Queries by Subject Area
As a data engineer at heart, I tend to think in terms of data models, so creating folders that mimic the data model makes sense. It helps me to find and organize queries within the entity where they are most closely aligned. So, when working with my retail customers, I create folders for ‘customer’, ‘product’, and ‘sales’.
Organize by Database
I spend a lot of time working in different databases—Snowflake, Redshift, and Netezza, to name the top three—and creating a master folder for the DB type and/or name can be a helpful organizational technique.
Organize by Function
By creating folders for certain functions like insert scripts, update scripts, window analytic examples, and pivoting examples, I can separate my business-oriented scripts from other SQL patterns that I use often but forget how to write, or don’t want to type again.
Organize by Project
Finally, when I embark on certain projects, like a customer re-engagement, I’ll create a folder and store all the related queries with it.
By combining these four organizational techniques, here’s what my catalog looks like:
Reorganizing the catalog every so often helps to keep it clean and manageable. Don’t be afraid to rethink your strategy. It’s easy to move objects between folders merely by dragging and dropping.
I move things between what I use actively every day and work with others to make sure the query is right, providing value, and being used in the applications and teams I support.
#2 – Tag it
After my catalog got big enough, I started to forget where I put stuff, so adding a good description with important keywords made the Search Catalog feature really helpful.
#3 – Add Descriptions to Your Code
Just like tagging things in the description, it can be useful to document your code, which helps with execution as well as capturing intent when I developed it. This documentation shows up in the full text search as well.
#4 – Parameterize it
When writing code in the catalog, I try to parameterize the code as much as possible. This way, when I call and use the catalog assets, I’m prompted for my value just like I would be in a normal query. This allows me to easily reuse my assets when I have a similar use case with different variables.
#5 – Think Object Orientation and Inheritance
One of the coolest features of Aginity Pro and Aginity Team is the ability to cobble together different sections of SQL in the catalog and reuse them. For instance, the example below has common joins, used functions, and then filters as separate and reusable.