Sql developer data modeler _ the data warrior

On a recent customer call (for Snowflake), the data architects were asking if Snowflake provided a data model diagramming tool to design and generate data warehouse tables or to view a data model of an existing Snowflake data warehouse. Database management systems Or if we knew of any that would work with Snowflake.

If you have read my previous posts on using JSON within the Snowflake, you also know that we have a new data type called VARIANT for storing semi structured data like JSON, AVRO, and XML.

In this post I will bring it together and show you the steps to customize SDDM to allow you to model and generate table DDL that contain columns that use the VARIANT data type.


P.S. 7 data recovery review If you are in Austin, Texas this weekend, I will be speaking at Data Day Texas (#DDTX16). Mode s database Snowflake will have a booth there too, so come on by and say howdy!

I had a 3rd party database that had PKs defined on every table but no FKs in the database. Windows 8 data recovery The question I posed (on the Data Modeler Forum) was:

So the root column name (CUSTNUM) is standard but in every table the column name has a different 3 character “prefix” that is effectively the table short name. Java 8 database Is there way to get the utility to ignore the first three characters of the column names?

Well, the ever helpful Philip was very kind and wrote me a slick custom Transformation Script that did the trick! (Check the post if you want to see the code.) But wait there’s more!

In his response he mentioned a feature coming in 4.1.888 – the ability to include a table prefix as part of a FK column naming template (just like this app had done).

Well with the template in place it turns out that you can have the FK Discovery utility search based on the Naming Template model rather than just look for exact matching column names. Database tools Using the Custom Naming Template

So recently (today in fact) I was trying to add FKs to the Snowflake DB model I reverse engineered a few weeks back ( Jeff pointed out they were missing). Drupal 7 database api I noticed the model had that pattern of a prefix on both the FK and PK column names.

In the CUSTOMER table the PK is C_CUSTKEY. Raid 6 data recovery In the ORDER table it is O_CUSTKEY. Database architecture Nice simple pattern (see the diagram below for more). Iphone 4 data recovery software That reminded me of the previous issue and Philip’s script.

With the note that {table abbr} would be the equivalent of what I called the table prefix. Database java So first I went to the table properties and put in the prefixes using the Abbreviation property:

Then all I had to do was modify his example to account for the underscore and the fact that the main column text would start at character #3 instead of #4:

Note that the utility also suggested some FKs based on the unique constraints (UKs) as well. Data recovery android I did not want those, so I unchecked them before I hit “OK”.

So I can happily report that Philip’s little enhancement works just fine in 4.1.3. C database tutorial WooHoo! I can see this being very useful for a lots of cases in the future.

In a future post (early next year), I will continue with showing how we implemented Referential Integrity constraints in Snowflake DB and if I can generate the DDL from #SQLDevModeler.

banner