This week, we’re moving forward with our previous challenge from Week 65 and are showing
our preference for semi-structured data and will output our very own Object!
This week, we’re again diving into the “Cybersyn US Patent Grants” dataset from the Market Place and using our own functions to interact with it.
The fascinating aspect of semi-structured data and VARIANT column types in Snowflake is that they offer a flexible way to handle diverse data formats. They act as a dynamic repository, allowing you to store and analyze multi-faceted information without being restricted to a rigid schema. Moreover, combining these with Snowflake’s capability to store and retrieve objects empowers the entire team with a scalable data model. And here’s a bonus: you can leverage these features on public datasets, like the one from Cybersyn.
So, here’s the game plan:
- Grab the Cybersyn US Patent Grants dataset from the marketplace.
- Use Cybersyn’s SQL query to dig up all patents related to Nvidia (we’ve put a limit of 10 in there so as not to overwhelm you with the results
- Create a function that builds an Object in a VARIANT column, comparable to the screenshot given beneath. The function from week 65 hasn’t changed much: Calculate The gap between
PUBLICATION_DATEcan be 365 days for “Reissue” patents and 2 years for “design” ones. Have a true or false designate if we’re WITHIN the parameter set for either patent
- As a last objective; Get the inside_of_projection values, into a different column (meaning that we want you to get it out of the object)