How AI Decision-Making is Improving Enterprise Outcomes
Blog
Share 
Snowflake: Handling Semi-structured Data with Variant and Flatten
23 February 2023 
Team Beinex Semi-structured Formats with Snowflake Support
The number of sources that produce semi-structured data has increased exponentially in recent years. The arrival of Snowflake Data Cloud has made it effortless to process complex datasets. Snowflake supports storing and processing semi-structured data. It supports semi-structured formats enlisted below:- • JSON
- • Avro
- • ORC
- • Parquet
- • XML
- • Variant
- • Flatten
Variant
A variant is a datatype which can hold semi-structured data in a single field. Snowflake stores semi-structured data in the column format when semi-structured data is loaded into a VARIANT column. A single row can contain other underlying data. Let’s demonstrate with the help of examples:
The stored data can be easily retrieved and structured from the above table by a simple query as below:
select
v:time::timestamp as observation_time,
v:city.id::int as city_id,
v:city.name::string as city_name,
v:city.country::string as country,
v:city.coord.lat::float as city_lat,
v:city.coord.lon::float as city_lon,
v:clouds.all::int as clouds,
(v:main.temp::float)-273.15 as temp_avg,
(v:main.temp_min::float)-273.15 as temp_min,
(v:main.temp_max::float)-273.15 as temp_max,
v:weather[0].main::string as weather,
v:weather[0].description::string as weather_desc,
v:weather[0].icon::string as weather_icon,
v:wind.deg::float as wind_dir,
v:wind.speed::float as wind_speed
from json_weather_data
Note: ‘v’ is the Field (Column) name, and the JSON tags are arranged alongside to retrieve datasets.
Flatten
Compound values are flattened into multiple rows with the use of the Snowflake Flatten Command. A tool called Snowflake FLATTEN is used to transform semi-structured data into a relational structure. Relatively complex JSON structures such as nested JSON structures can be structured with FLATTEN function in Snowflake. Here is an example of the same:
The above JSON file stores a single set of conversations between two texters in a single row. If the requirement is to get every individual message as a separate row a FLATTEN function can be used to produce the following result:
Here is the query to generate the above table from semi-structured data:
(SELECTb.value:message_date::TIMESTAMP AS Time_Of_Message,
b.value:conversation_number::STRING AS Conversation_num,
b.value:message:message_text:msg_txt::STRING AS Message,
ROW_NUMBER() OVER ( PARTITION BY b.value:dialogId::STRING ORDER BY
b.value:message_date::TIMESTAMP ASC) as Message_order
FROM customer_messages t,LATERAL FLATTEN(input => t.v) b);
Summing Up
The ability of Snowflake’s assistance is undeniable. It aids you to make better decisions and offers a better overall experience when attempting to get the most out of your data, through features like Flatten and Variant. If you want to be a part of Snowflake’s incredible platform, let us help you. Beinex’s partnership with Snowflake enables us to offer you advanced features like automated tuning and elastic computing, along with analytics modernization services, to help your organisation realise exponential Return on Investment.
There are instances in which you really get overwhelmed by tons of data that you may spend hours exploring, finally leaving you with heaps of unresolved data sets. Companies have been handling giant, complex data ecosystems and pipelines to transform complex data sets in semi-structured formats like JSON, XML, etc. All these intricate data sets can be converted into manageable and insightful data formats by adopting cloud-based technology. Snowflake’s out-of-the-box features to handle complicated semi-structured data formats with variant and Flatten help to handle the demanding needs of enterprises.
Related Articles
Coming Soon: Tableau 2022.1 – What Does It Offer New
- List the unused data sources: Data sources imported in a workbook but not used are highlighted by the new feature. The developer can remove these data sources from the data source list to make the workbook faster.
- List of unused fields or data columns: Just like the data sources, workbook optimizer also highlights the data columns not used across the workbook. Removing these at the data source level can help improve the overall performance of the workbook.
- List of sheets not used in the dashboard: It is a common practise that developers tend to create sheets not used in the final dashboard. This creates unnecessary clutter and makes the dashboard slower. The workbook optimiser feature gives a list of such sheets which the user can delete to optimise the performance of the workbook further.
- Highlights lengthy calculations: The feature provides a list of calculations which are too complex and in turn reduce the performance of the dashboard. Simplifying a few of these can improve performance to a great extent.
Ask Data Phrase Builder: This feature is available on Tableau Server and Tableau Online
Add field would look like as shown in the below screenshot:
Customize View Data: This feature is available on Tableau Server, Desktop and Tableau Online
This feature enables to reshape the tabular data behind your visualisation in the View data interface. One can create new columns, remove columns from the default view, change the order and sort the data using this feature. This reshaped data can also be exported as csv file to be shared with the team.
Change the root table: This feature is available on Tableau Server, Desktop and Tableau Online
Managing multiple data tables becomes easier and flexible with this new feature. One can swap any table to be the root table with a single click. This allows one to change the layout of the table quickly, reshape the data with a different root table and delete a specific table without deleting child nodes. For e.g., let us assume a user had to create a data source for an analysis using 3 tables namely ‘customers’, ‘orders’ and ‘returns’. The user creates the data model such that ‘customers’ is the root table followed by ‘orders’ and ‘returns’. But after performing some analysis the user realises that ‘orders’ should be the main root table. In such cases the user would have to re-create the data again from scratch but with the new ‘Swap with root table’ feature user can do the changes with a few clicks.
Parameter Enhancements in Tableau Prep
In version 2022.1 Tableau Prep is adding even more places where one can use parameters in the flow as well as user enhancements. Now, one can:
- Get a list of all the parameters in one place rather than finding them in the flow. One can delete these parameters directly from the parameter window rather than to find it in the flow first.
- Include parameter names in exported output files.
- Include parameters in SQL scripts that you run before or after writing the flow output to a database. Include parameters in worksheet names when writing the flow output to Microsoft Excel.
- 1. Improvements in Esri Data Connector
- 2. Addition of new Accelerators
- 3. Added connectors to connect to more data sources
Beinex Achieves Snowflake Select Tier Partner Status
Benefits: Enhanced Data Cloud Capabilities
The partnership will let Beinex turbocharge services on the AI-ML, analytics fronts by utilising storage and compute scalability unlocked by the unique collaboration. It awards Beinex and its clients the capability to flourish in terms of cost leadership, domain leadership and added utilisation of potential in sync with market conditions.
Data marketplace enhancement
The partnership also means that acquiring and testing third-party data is now easier which also entails the Snowflake users to imbibe the expanded third-party data into their environment, attach it to their first-party data and evaluate the data efficacy vis-à-vis customer experience along with the impact it can create.
There is little doubt that the capability is very much in demand as Beinex clients are into delivering powerful customer/ user experience as a part of their service efforts
Features:
- Privacy-safe
- Secure sharing platform
- No need to set up extra secure portals to support sharing of Personally Identifiable Information
The power of partnership
Beinex partnership with Snowflake enables it to offer clients advanced features like automated tuning and elastic compute with unlimited decoupled computing capability, along with the analytics modernization services, to help organisations realise exponential Return on Investment. This upgrade in status will take business to the next level for both Beinex and its esteemed client line-up.
Partnerships are what make Beinex stronger. The company has strong partnerships with some of the leading technology firms, research labs, and universities around the globe.
Businesses can leverage the power of our partner ecosystem to maximize the value of their end-to-end analytics journey.
Beinex is ecstatic to receive this recognition as a Snowflake select services tier partner and is grateful to Snowflake for acknowledging its client services.
Reining in the Horses is So Simple! Leverage the Sales Growth with Intent Data(Infographic)
An Ultimate Guide on Amazon Bedrock: How Businesses Can Turn Generative AI into Real Business Value
What is Amazon Bedrock?
Amazon Bedrock is a fully managed AWS service designed to help businesses quickly build and deploy generative AI applications. It offers access to a variety of high-performing foundation models from leading AI companies, including AI21 Labs, Anthropic, Cohere, and Stability AI, through a single, unified API.
What makes Bedrock especially appealing to enterprises and fast-growing businesses is that it takes the heavy lifting off their shoulders. There’s no need to manage model training infrastructure, scale servers, or worry about data exposure. You can just focus on building applications; AWS handles the rest.
Source: AWS
Key Features of Amazon Bedrock
Let’s break down the key features powering AWS Bedrock:1. Access to Foundation Models
Amazon Bedrock offers ready-to-use models that are pre-trained, reliable, and production-ready, and are capable of tasks like:
- • Conversational AI and chatbots
- • Image generation
- • Text generation and summarization
- • Content classification and analysis
2. Secure Model Customization
Businesses can customize foundation models using their own proprietary data—without that data ever leaving their AWS environment. This is especially critical for industries, such as finance, healthcare, and government, where data governance and compliance matter.
3. Serverless Infrastructure
As Bedrock is completely serverless, there is no need to provision, manage, or scale infrastructure. Applications can also start small and scale instantly with demand, thereby making them ideal for both startups and large enterprises.
4. Single API Integration
Bedrock integrates seamlessly with existing AWS services and enterprise systems via a single API. This feature simplifies development and speeds up time-to-market.
5. Model Playground
AWS provides a Bedrock Playground, a visual interface where users can experiment with text, image, and chat models before deploying them into applications, making it helpful for teams evaluating use cases or testing outputs.
Top Benefits of Choosing Amazon Bedrock for Your Business
Amazon Bedrock stands out because it makes generative AI practical, not just impressive. Here are the top benefits organizations gain by adopting Amazon Bedrock:
Access AI Faster
Teams can start building generative AI applications without deep machine learning expertise, as it removes technical barriers.
Increased Efficiency
Businesses can prototype and deploy solutions faster, thereby accelerating innovation cycles instead of spending months building models from scratch.
Affordable AI Implementation
The cost of building AI capabilities can be significantly reduced by using pre-trained models, especially when compared to developing custom models.
Built for Scale
Bedrock supports enterprise-scale workloads while maintaining performance and reliability powered by AWS infrastructure.
Flexible Across Use Cases
From customer engagement to analytics and creative design, Bedrock supports a wide range of business needs on a single platform.
Real-World Use Cases for Amazon Bedrock
As you are now familiar with Amazon Bedrock, let’s understand how your business is going to benefit through some real-life use cases of Bedrock:
1. Customer Service Automation
Businesses can build AI-powered chatbots to handle order tracking, FAQs, and troubleshooting. It reduces response time, improves customer experience, and allows support teams to focus on complex issues.
2. Marketing and Content Creation
Marketing teams can generate blog drafts, social media copy, email campaigns, and product descriptions more quickly, freeing up time for strategy and creativity.
3. Product and Experience Personalization
Bedrock-powered applications can deliver personalized product recommendations and content, increasing engagement and conversion rates by analyzing customer behavior.
4. Analytics and Business Insights
Amazon Bedrock can summarize complex datasets, generate executive summaries, and highlight trends, making data easier to interpret for leadership teams.
5. Design and Creative Workflows
Design teams can use generative AI for image creation, branding concepts, and campaign visuals, speeding up ideation and iteration without replacing creative control.
Summing Up
If you are looking to move beyond experimentation and into real-world AI impact, Amazon Bedrock offers a practical, future-ready foundation. It can support you by providing:
- • Enterprise-grade security and compliance
- • Rapid deployment for competitive markets
- • Scalable solutions without operational complexity
For a free demo, connect with us: https://beinex.com/beinex-amazon-web-services/
Data Calculations in Tableau: Sum and Running Sum
[sc name="quote" quote="“The functions, even as they may look simple, are pretty useful for any Tableau Consultant especially if they are working on finance-related projects,”" author="Joseph John, Senior Consultant, Analytics, Beinex"][/sc]
What is Tableau Sum and Running Sum?
Sum
SUM is one of the commonly performed functions in Tableau. The Tableau Sum function seeks out the Sum of records under consideration. It is the total of the values present in a field. The screenshot provided below exhibits the total sum of sales for each of the three categories as given in Sheet 1.
In the sample dataset shown below, the sum of sales is shown corresponding to each of the corresponding values in the dimension "Category". For example, "Furniture" has a total sale of 754,748, which could be comprising of furniture related products such as Tables, Chairs etc.
Running Sum
A RUNNING SUM is a cumulative total in a row or column from the first value to the final value in the respective row or column. For instance, in the example given below, the cumulative values of Furniture and Office Supplies stand at 1,486,641 and that value when added to Technology’s value of 839,893 gives 2,326,534.
You can summarise or modify the granularity of your data using aggregate functions. An aggregate part combines the values of multiple lines to provide a single value. Examples of aggregate functions apart from sum are measurements based on Count, Count Distinct, Fixed Calculations, and other standard integration functions.
Every time you include a measure in your view, an aggregate is automatically applied to that measure. Depending on the context of the view, different aggregation techniques are used. Analysts can well utilise these features to simplify the whole complex process of data analysis, and organisations can harness them for insightful decisions.