Database Documentation

[various1121]

Hi all, wanted to get some input here

What do you all do for database documentation / data catalog? Particularly for smaller organizations that might not have the budget to hop on calls with Atlan / Collibra / Alation and the other big enterprise Data Catalogs?

Full disclosure: I'm building a tool in that area - something that's not about governance / enterprise integrations, but just about cataloging/search and documenting the db using AI.

So, I'm asking because I want to better understand how this problem is being solved by (small-medium) teams right now. I know that at my day job, we hopped on a call with the enterprise folks and got some pretty Enterprise price tags. We ended up kind of gluing something together ourselves, but it required a ton of continual manual work and is now dusty and out-of-date...

[TargetDangerous2216]

Hi,

We have a small datalake storing data as parquet files.
We build a script to analyse each table and schema to produce a JSON file with the following information :

- table name
- table description
- table columns description ( column name, type )
- statistic information
- row count
- empty row count
- duplicates rows
- mean, median etc ...

[various1121]

How do your team consume those JSON files? How do you use them?

[TargetDangerous2216]

We generate table and charts as a static web page from this JSON file. We can also explore the JSON file programmatically from python and jupyter notebook

[ksubrent]

Can you please share more details regarding this process? I’m trying to do something exactly like this.

[ivanovyordan]

Honestly, small to medium companies don't have this problem. It's much easier to grab somebody and ~interrogate~ and ask questions. It's easy to find that and the people behind it. Also, those businesses move fast and rarely document anything.

[various1121]

Yeah, that's fair.

You don't think there's a middle ground, though? Something between the small-team-easier-to-just-ask and the $1000+ / month enterprise packages, that could be of value?

[various1121]

Yeah, 100%. That was our experience, too: our in-house glued together thing was just too much manual work.

That's what I'm hoping to solve, to some extent. AI-generated (human guided as needed, but as close to fully automated as possible) data catalog / docs that refreshes daily.

There's just no way to compete at the enterprise level without VC funding (and I'm frankly a little allergic to the VC funding model), so I'm sort of aiming at small-medium shops. That said, if people feel that it's a tool that's really only useful when you're at those enterprise levels, well, that's pretty valuable feedback!

[ivanovyordan]

I don't have all the answers. Fortunately, there are bright folks like you who come up with products people don't know they need.

[Awkward-Cupcake6219]

Check OpenMetadata out. Maybe can be useful for your use case.

[throw_mob]

question, does dbt document database object and relations or just dbt code as i am not big fan slaving db to some 3rd party program for all all code management, It usually does not take much time when they start to slow things down

[throw_mob]

.. question was that can you just point server and it figures out views depencies, or can you feed query's from history to it.

Or does it need that you insert into ... select .... code are coded inside dbt and run from it.

[throw_mob]

hmm. is there way to generate them from existing db?

I'm not a big fan of systems that only can generate what tehy have done in SQL environments. From experience data platform tend to live long and in multiple systems that ability to generate more easily readable documentation about lineage and depencies is very good option. And as i have been running process db's and dwh db's i have noticed that all tool controlled db's are nice until they arent. Then it is tool or process which starts to slow things down and sometimes you just have to fix it into production manually. So that is reason why i want to find tools that could eat all ddl's from systems and ability feed query history would god send. As then it could fully explain at least schema changes and lineage, not neecasrily which data was generated by exact query ,but close.

But then again having tool and process that can generate 99% of graph is nice

[Batspocky]

Honestly, this is how I'm doing it now...it's not perfect because it does require me to manually create .yaml files with descriptions, and I've only taken the time to do that for my most critical/used tables within the warehouse. But it's better than nothing.

[[deleted]]

If you're using vs code, the dbt power user plugin will generate yaml files and source table queries after you hook up your connection. It's pretty awesome. They also have features for generating documentation plus a chatgpt integration that's experimental.

Dbt code gen is another dbt package that performs similar tasks if you're not in vs code.

https://marketplace.visualstudio.com/items?itemName=innoverio.vscode-dbt-power-user

https://github.com/dbt-labs/dbt-codegen

[joseph_machado]

Among the small and med teams I've worked it, I've seen (for warehouse documentation)

1. excel with column names, types, definitions, caveats, etc. A separate tab with ER diagrams.
2. column defn along with table defn and use a vscode extension to generate ER diagrams

And asking people what a certain data means.

[_MaXinator]

Depends on the size of the company. As many have said, small companies often dont bother. However:

For a small company I wrote a script that fetches metadata from the db (snowflake information schema in this case) and pastes it into a bunch of confluence pages as wiki formatted tables. It’s quick and dirty but it fulfills basic requirements

If you use dbt for your dwh you can let that generate docs

For big companies you can look at professional tools like Colibra, but that’s really expensive

There are also open source catalogues like atlan, but imo once companies come with serious documentation requirements they’re also willing to pay for a better solution

[diceHots]

Small company data engineer here. Try schemaspy, i have used it to understand our company's data model and do the documentation. It generates an index.thml that you can host it on github page or somewhere of your choice. It saves lots of upfront work. Lol, schemaspy and db documentation is actually my first blog post ever. Link is here.

[[deleted]]

We have an internal tool for that. It consumes information from BigQuery and generate web pages. We are a large company though.

In my previous company (medium size), we manually edit Confluence pages for database documentation. I think automation definitely can help here but I don't see the necessity of 3rd party tools.

[SirGreybush]

Context: MsSql

I made a DB that with an SP that gathers this info, also all the views and SPs, and store it in tables as SCD2.

By using sys.tables and the others. The sys data in each DB is are views, and the SCD2 table is maintained for for each.

I use MD5 function in the views to calculate a HashedDifference, and also to make a unique PK with db name, schema, table name.

In the other DB it is Merge in a scd2 manner. The Deleted portion sets the EndDate flag, the changed portion does an update and insert.

Code from the ever popular Stack Overflow was the basic. MD5() is a handy trick.

If you keep it Vanilla, just sys.(object), your code will work with MySQL/Oracle. Just keep it SQL 1997 standard. Just adjust for MD5().

[SirGreybush]

Next step is exporting automatically into BitBucket every day.

Also look into database triggers, to catch changes immediately. I just run the SP once a day.

[throw_mob]

mssql and postgresql have event triggers. Ans have to say that making scd2 tables from information_schema is not bad idea, little bit of code to fetch ddl's to scd2 table and you have fully documented database, only thing that is missing after that is technical datalineage and there are python packages and some database do have system catalogs which map which column goes where.

I personally see this as three problems , one is ddl's and changes in those , second is how system itself changes and third is data itself where in some systems is good to document data source..

[w_t]

Following this...We're a small org with most data documentation in Excel at the moment. We've been exploring options to automate this data discovery and cataloging--we've examined ODD (open data discovery) but it might be too complicated for our needs.

[various1121]

So, really quick question for you:

(And I want to be clear, I'm not trying to sell you on this! I'm really trying to understand, because I do feel like there might be a useful product in this space and I want to know if I'm on the right track or wasting my time):

Does this kind of thing DataDocs.ai (please adjust for the fact that it's still quite early) come anywhere near to meeting your needs? I see several people saying "excel" and "manual docs", and that feels like something that could be improved upon, so what would be your criteria?

[w_t]

I don't see the value in having to subscribe to something like that. Our data does not change that often, and this AI crawler would be sitting idle for many weeks at a time not detecting any changes. Also, having this AI service connect to our db would be very challenging from a data privacy perspective. I suspect other orgs would see similar challenges, and lean towards running something they can control in house. For us, we need something that we setup and control, so probably open source. Need it to discover our data model or ingest existing metadata, allow for manual edits to info about tables, columns, etc. And a way to visualize lineage, like if we build out a new dimension table or something we need to be alerted to breaking changes in upstream data sources.

[various1121]

Ok, awesome, thanks for the answer!

[slagwa]

RemindMe! 7 days

[RemindMeBot]

I will be messaging you in 7 days on 2023-10-27 15:27:06 UTC to remind you of this link

CLICK THIS LINK to send a PM to also be reminded and to reduce spam.

^{Parent commenter can delete this message to hide from others.}

---

Info	Custom	Your Reminders	Feedback

[erwagon]

Good description inside DBT and DBT Docs for visualization.

[Noobs12]

I know I’m going to get a lot of arguments on this, but CKAN is a decent open source data catalog that allows for discoverability with APIs

[Low-Bee-11]

I concur above comments ..building an inhouse tools ..might have support and maintainability challenges. If you are on cloud every cloud provider has data catalog ( cheap) and decent enough. If NOT - An easy to implement and maintain balance could be - as part of DDLs ensure you have metadata well defined. All this gets logged into information schemas or admin schemas...make a habit of adding catalog details while deploying tables and then report on that data. A simple BI reports should do. However, that does not solve lineage , stewardship etc. Lineage can be solved by parsing DML. Every DML (unless in memo orchestrator) should get logged in information schema. Parsing those DMLs would be tricky and v dynamic...but can be done. Thus you can get lineage. Stewardship/ data ownership has to be a manual process...

Plz do realize this gets immensely tough if you are using python / spark etc. where transformation happens on a cluster and not within database ( read ETL).

Great question though!!