The Data Warrior

Changing the world, one data model at a time. How can I help you?

Archive for the tag “schema-on-read”

Schema-on-what? How to model JSON

It seems hard to believe, but all year, around the world, I continue to have this conversation on whether or not we still need data modeling.

I know! Crazy!

Thought we were past that…

As I have said before,

Schema-on-read has the word SCHEMA in it!

So instead of continuing to rant about it, I decided to put together a talk to show people, graphically, what I meant by decomposing, step by step, a few JSON documents into real data models. For the sake of the talk I decided to go with 3NF and Data Vault styles to make my point.

This talk has been very well received so I decided I would share it a bit more publicly by posting it here on my blog.

 

Now that you can see how to model JSON, check out my Snowflake ebook on how to easily analyze JSON using SQL.

If you know any meet-ups or conferences that I should be giving this talk at, please let me know. Or check out my speaking schedule for 2019 and join me at one of the events already on my calendar. (1st up is ITOUG in Milano!)

Ciao!

Kent

The Data Warrior & Chief Evangelist at Snowflake

P.S. There was no magic, or built-in wizard, to creating the models. I did it all by hand using Oracle Sql Developer Data Modeler.

 

Advertisements

One more time: Do we still need Data Modeling?

More specifically do we still need to worry about data modeling in the NoSQL, Hadoop, Big Data, Data Lake, world?

This keeps coming up. Today it was via email after a presentation I gave last week. This time the query was about the place of data modeling tools in this new world order.

Bottom line: YES, YES, YES! We still need to do data modeling and therefore need good data modeling tools and skills.

Snowflake with RI

A picture can say so much!

 

In order to get any business value out of the data, regardless of where or how it is stored, you have to understand the data, right?

That means you have to understand the model of the data. Even if the model (or schema) is not needed upfront to store the data (schema-on-write), you must discern the model in order to use it (schema-on-read).

It is (mostly) impossible to get repeatable, auditable metrics, KPIs, dashboard, or reports that bring value to the business without understanding the semantics of the data – which means you at least need a conceptual or logical model.

And if you want/need to join data from multiple source then you really have to understand each source or there is no way to properly join it all together to get meaningful results.

There are a few data cleansing, discovery,and “virtualization” tools out there that will help you figure out those relationships but they are expensive and mostly rely on standard data profiling techniques to find similar data objects across the sets and propose “relationships”. Some allow for the definition of fairly sophisticated matching rules including customizations. But a human still needs to figures those out, test, and validate the results.

In the end you still have to know your data.

One of the best ways to do that, in my opinion, is to model that data. Otherwise your data lake will likely become a data swamp!

So keep your data modeling tool and keep building your data dictionary with your business folks.

Final Stage Table

A good modeling tool can act as a visual data dictionary too!

If you agree with me, please share on social media!

#LoveYourData

Kent

The Data Warrior

P.S. If you need a good modeling tool, check out Oracle SQL Developer Data Modeler. And check out my books and training offering for SDDM on the blog sidebar.

Snowflake SQL: Making Schema-on-Read a Reality (Part 2)

This is the 2nd of my articles on the Snowflake blog.

In the first article of this series, I discussed the Snowflake data type VARIANT, showed a simple example of how to load a VARIANT column in a table with a JSON document, and then how easy it is to query data directly from that data type. In this post I will show you how to access an array of data within the JSON document and how we handle nested arrays. Then finally I will give you an example of doing an aggregation using data in the JSON structure and how simple it is to filter your query results by referring to values within an array.

Check out the rest of the post here:

Snowflake SQL: Making Schema-on-Read a Reality (Part 2) – Snowflake

Enjoy!

Kent

The Data Warrior

Snowflake SQL: Making Schema-on-Read a Reality (Part 1) 

This is my 1st official post on the Snowflake blog in my new role as their Technical Evangelist. It discusses getting results from semi-structured JSON data using our extensions to ANSI SQL.

Schema? I don’t need no stinking schema!

Over the last several years, I have heard this phrase schema-on-read used to explain the benefit of loading semi-structured data into a Big Data platform like Hadoop. The idea being you could delay data modeling and schema design until long after the data was loaded (so as to not slow down getting your data while waiting for those darn data modelers).

Every time I heard it, I thought (and sometimes said) – “but that implies there is a knowable schema.”  So really you are just delaying the inevitable need to understand the structure in order to derive some business value from that data. Pay me now or pay me later.

Why delay the pain?

Check out the rest of the post here:

Snowflake SQL: Making Schema-on-Read a Reality (Part 1) – Snowflake

Enjoy!

Kent

The Data Warrior

Post Navigation

%d bloggers like this: