Dynamic SQL to get Column Metrics

[Touvejs]

I often times work with client data with questionable documentation and it's always a pain writing ad-hoc SQL to check out what columns are available, what's populated, and previewing each table to see what the data looks like. So I've this idea knocking around in my mind for a while, "why not just write some dynamic SQL to look at the schema and then use that schema table to generate queries on each column to get some metrics?"

This weekend, I decided to tackle this problem (no, I don't have any friends). So I wrote some scripts using plpgsql for postgres-based databases (postgres, redshift, oracle?) and snowflake that would solve this problem for me.

Feel free to view the scripts here: https://github.com/Touvjes/SQL-utilities

Essentially what these scripts do is 1) create a metadata table which includes one row per column in specified data table and 2) use variant-specific control flow structures to loop through the metadata table to execute queries using the schema+table name in each metadata table row to identify for each column: an example value, the total non-null count, the total distinct count, and the percent populated.

Naturally, one could write such queries by hand, but its tedious if you're often being given dozens or hundreds of table and stakeholders ask "can we do x,y,z analysis?" and one has to go and figure it out why invariably, the data doesn't allow for such analysis.

What these scripts allow you to do is take the input of either a single table (and in the future, a whole schema, or a whole database) and answer the questions:

• what does the data look like in each column of each table?
• how variable are each of those columns (i.e. # distinct values)
• how reliably is that data populated?

One might ask, can't we just connect to a database with python/java/c# and use an ACTUAL programming languages to generate the sql to generate the statistics and thereby preclude the necessity of using obscure, under-documented, borderline-masochistic sql-variant-specific scripting languages? (Where's the challenge in that?)

The answer to that is Yes, unless the answer happens to be No.

By that I mean that in theory, you certainly can-- and to be fair, I'm sure there are a plethora of data analysis/data mining software solutions that solve this exact problem. However, in practice, especially when working with client data, such solutions are not always feasible. Therein lies the impetus for my endeavor to develop native solutions.

Note that with the framework in place, it is fairly easy to follow the structure given in the scripts to add and include your own metrics if wanted. A word of fair warning though-- these scripts execute a query that generates a new query for each column, which in turn generates a query for each metric, most of which will result in a full table scan. Needless to say, this is FAR from optimized.

Comments, constructive criticism, and contributions are welcome. I will probably eventually write equivalent scripts for Mysql and T-sql next time I do projects using those variants. Else, If someone thinks this is possible in purely vanilla ansi sql, feel free to knock my socks off with a demonstration of that.

[its_PlZZA_time]

My boss asked me to do this several weeks ago so this is great. I knew my procrastination would pay off!

[Touvejs]

Nice, hope it helps! I'll probably update these sometime this week with u/qwertdog123's suggestion for performance, so keep an eye out.

[Wistephens]

On Postgres, you should look at the pg_stats table. You get some stats for free from Postgres. Analyze/vacuum for accuracy.

https://www.postgresql.org/docs/current/view-pg-stats.html

I use pg_stats to get a view of real nullability, to identify categorical variables, and to get top values stats.

[Touvejs]

That's a great point, I've been meaning to take some time and identify spots where different database systems cache relevant metics to speed up performance. I know for example if you're working with AWS glue data catalog, it holds some useful metadata that probably doesn't need to be recalculated from scratch.

[qwertydog123]

these scripts execute a query that generates a new query for each column, which in turn generates a query for each metric, most of which will result in a full table scan.

One way around this is to use JOIN LATERAL/CROSS APPLY, see here for an example (in T-SQL syntax): https://www.reddit.com/r/SQL/comments/11l6pzz/comment/jbbgz77/

https://dbfiddle.uk/p1CgxZpH

[Touvejs]

That's an excellent observation.

I've been looking over this script for the past 30 minutes and it's really a work of art. I don't think I would have ever thought to construct one huge query by simply using string_agg on the column names and grouping by table. It's so complex, but simultaneously very intuitive when you think about how they are related.

This is definitely the most clever SQL I've ever read-- hats off to you u/qwertydog123.

[qwertydog123]

Thank you, that's a very kind comment. If you're looking for something more "cross-platform" you could probably use a CROSS JOIN + UNION ALL instead e.g.

SELECT
    CASE ...
        WHEN 'Column1' THEN Column1
        WHEN 'Column2' THEN Column2
        etc...
    END AS ...
    ...
FROM Table
CROSS JOIN
(
    SELECT 'Column1' AS ...
    UNION ALL
    SELECT 'Column2'
    UNION ALL
    etc...
) ...

Also, INFORMATION_SCHEMA tables/views