5 ms·

>To achieve this, our Data Warehouse team was tasked with identifying every foreign-key relationship between every table in the data warehouse to backfill all t

>To achieve this, our Data Warehouse team was tasked with identifying every foreign-key relationship between every table in the data warehouse to backfill all the ID columns with corresponding UUIDs. Given the decentralized ownership of our tables, this was not a simple endeavor. The most promising solution was to crowdsource the information by scraping all the SQL queries submitted to the warehouse and observing which columns were joined together. Am I missing something, or does scraping SQL queries to effectively get an ER diagram sound a bit strange? If you have SQL and a relational database, don’t you already have a formal model of the relations encoded somewhere? E.g. in the table definitions, annotations in your code, or at least as a Word document? The tool sounds great, but isn’t it a failure having to write it in the first-place?

bastawhiz 9y ago
(former Uber engineer) Data lived in many databases across the company. Vertica, hive, schemaless, Cassandra, etc. The value in a column might refer to an id in an entirely different system. When you have (tens of?) thousands of tables owned by hundreds of teams, documentation is neither homogeneous nor centralized.
- jmiserez 9y ago
  I see. Very cool approach to have a tool replace people reading documentation, and certainly more accurate and up-to-date. Also I re-read only now that this is for a DWH, in which case it makes even more sense.
- fusiongyro 9y ago
  So, why does it make sense in this situation ("you can all do what you please with your data") to mandate something technical ("but starting today you all must use UUIDs from now on") across all groups? What's the value of this change if using UUIDs is now literally the only thing they all have in common?
  - jhoechtl 9y ago
    Process mining to identify yet unknown relationships. This is more sort of a big data "what relates to each-other yet we don't know it" analysis
  - dllthomas 9y ago
    The change to UUIDs was part of a larger effort to make sure everything that generated entities could do it without synchronization across datacenters. (And sure, there are other approaches, I'm just adding some context :) )
threeseed 9y ago
> or does scraping SQL queries to effectively get an ER diagram sound a bit strange It's very common in data governance tools to do this e.g. Informatica MDM, Cloudera Navigator. > don’t you already have a formal model of the relations encoded somewhere Good companies will have this. But not all. And they definitely don't have it spanning across all of their data stores.
- NegativeLatency 9y ago
  Know of any resources to make this easier? Or more info about how to track those relationships?
chacham15 9y ago
Often, formalizing constraints as part of the table schema is not done because of the performance penalties of the checks required to enforce the constraints. I.e. every insert to the database would also have to check that the foreign key relation existed. Some databases have options to turn off the checking, but a lot of people have gotten in the habit of just not doing it. Furthermore, if you have data in multiple places (redis/cassandra/mysql/hive/etc) this just becomes impossible even if you wanted to.
- deleted 9y ago
  [deleted]

bastawhiz 9y ago

(former Uber engineer) Data lived in many databases across the company. Vertica, hive, schemaless, Cassandra, etc. The value in a column might refer to an id in an entirely different system. When you have (tens of?) thousands of tables owned by hundreds of teams, documentation is neither homogeneous nor centralized.

jmiserez 9y ago
I see. Very cool approach to have a tool replace people reading documentation, and certainly more accurate and up-to-date. Also I re-read only now that this is for a DWH, in which case it makes even more sense.
fusiongyro 9y ago
So, why does it make sense in this situation ("you can all do what you please with your data") to mandate something technical ("but starting today you all must use UUIDs from now on") across all groups? What's the value of this change if using UUIDs is now literally the only thing they all have in common?
- jhoechtl 9y ago
  Process mining to identify yet unknown relationships. This is more sort of a big data "what relates to each-other yet we don't know it" analysis
- dllthomas 9y ago
  The change to UUIDs was part of a larger effort to make sure everything that generated entities could do it without synchronization across datacenters. (And sure, there are other approaches, I'm just adding some context :) )

threeseed 9y ago

> or does scraping SQL queries to effectively get an ER diagram sound a bit strange It's very common in data governance tools to do this e.g. Informatica MDM, Cloudera Navigator. > don’t you already have a formal model of the relations encoded somewhere Good companies will have this. But not all. And they definitely don't have it spanning across all of their data stores.

NegativeLatency 9y ago
Know of any resources to make this easier? Or more info about how to track those relationships?

chacham15 9y ago

Often, formalizing constraints as part of the table schema is not done because of the performance penalties of the checks required to enforce the constraints. I.e. every insert to the database would also have to check that the foreign key relation existed. Some databases have options to turn off the checking, but a lot of people have gotten in the habit of just not doing it. Furthermore, if you have data in multiple places (redis/cassandra/mysql/hive/etc) this just becomes impossible even if you wanted to.

deleted 9y ago
[deleted]