Case Study: Snowflake Streams & Tasks
Written by Jared Hillam
As one of the world’s top cruise lines, the Client is a household name. Their world class ships sail around the world and service multiple market segments from exclusive luxury cruising brands to large scale ships offering entertainment options.
Intricity is a team of specialized Data Management, Data Warehousing, and Business Intelligence experts. The team members at Intricity have been handpicked over the course of 20 years, and represent the top talent globally in data-oriented disciplines.
Challenge and Wins
The cruise industry faces a difficult set of unique challenges due to the remote nature of how data is ultimately acquired off ships. This requires satellite connections that continuously stream data. The timing of data is critical as the ships have a limited upload window so customer’s bandwidth is not interrupted. Not only do ships produce data remotely, they also cross time zones on a frequent basis, which adds a complex paradigm for onboard events.
The Client had developed a data-to-information backbone which leveraged the Microsoft stack of on-premise products. At the time, this was a modern architecture which dealt with some of their complex data onboarding requirements. However, over time the solution began to show the typical Microsoft SQL Server deployment shortcomings. Multiple platforms over time had been experimented with and even purchased including SAP HANA’s in-memory appliances. HANA turned out to be far too expensive to scale to the Client’s needs. The Client attended a workshop hosted by Intricity which outlined the power of Snowflake. With further exploration, the Client’s team knew they found a solution that would scale with cost efficiency and provide massive performance improvements.
Though the Client had a pre-existing relationship with a local service provider that had some Snowflake experience, they could see that their needs for expertise were surpassing the resources available. Snowflake recommended a meeting with Intricity to further explore a fit and the Client was aware of some of the local deployment success stories. The immediate need was the ability to get deep into the definition of a reference architecture which the Client could get behind. Once this future plan was defined, they wanted Intricity to execute on the plan
- The Client already had a significant investment in the current data-to-information process and a large volume of BI reports and analytics were going to be impacted by the new architecture.
- A true-up event was eminent with Microsoft where a renewal of their extensive SQL Server landscape was coming up. The Client wanted to reduce their reliance on Microsoft before that true-up event
- The Client’s SSIS skills were solid, but taking on a new integration tool under the short timeframe of the true-up was a concern.
- Solving for time zones had always been a critical challenge for managing analytical content. Many models were attempted but never made headway towards creating an agreed standard.
- The Client team didn’t just want to recreate their existing data warehouse. They wanted to redesign it to better conform the dimensions and measures for cross functional analytics.
- While the decision to use Snowflake was made, the solution architecture around Snowflake was still up in the air. Determining a phased approach for introducing the supporting the new technologies needed to be accounted.
Win 1: Full ELT Using Snowflake Streams and Tasks
In May 2019, Snowflake announced the availability of Streams and Tasks as a viable approach for forming integration and transformation code. While this was a fairly new feature, the power of natively running the transformation routines directly in Snowflake showed great promise. Intricity presented Streams and Tasks to the Client team. After comparing the various options for how integration could occur, the Client gave the green light to leverage Streams and Tasks as the transformation engine. Streams and Tasks proved to be a scalable and low-cost approach for continuously loading the data 24/7 directly into the conformed Snowflake Data Warehouse. It eliminated the cost of adding an ETL/ELT tool by directly orchestrating and processing the data in Snowflake.
“The streaming of data into the Data Warehouse for the Client is basically as real time as you can get, which in the cruising industry is gold,” said Arkady Kleyner, Intricity Cofounder.
Win 2: Throwing Out the Water, Not the Baby
The Client had a significant amount of orchestration logic which onboarded the data from the ships to the datacenter. Rather than immediately remove these working assets, Intricity sought to phase the wins so that the benefits of using Snowflake could most rapidly be realized without adding the complexity of replacing the data onboarding logic. Additionally, the old MS SQL Integration Services (SSIS) ecosystem was undergoing a major true-up event which would require the Client to buy more licenses. The date for this true-up was too soon to consider risking a full redesign which would require a parallel deployment to appropriately ensure parity.
Balancing these two needs, Intricity leveraged the logic for onboarding data from ships but removed the transformation tasks from SSIS, stripping down the processing requirements in SSIS to a minimal state, and placing those transformation tasks in Snowflake. By using this approach, the Client was able to downscale their SQL Server while still leveraging the complex data onboarding logic which had been proven to work. Additionally, their staff which already knew how to manage and run SSIS were enabled to leverage Snowflake without the arduous task of learning a new integration stack out of the gate.
“When we’re doing these Snowflake deployments, it's important that we don’t just throw the baby out with the bathwater. Sometimes there’s really thoughtful code behind the existing deployment, and using a “do no harm” approach can better phase organizations into the new world,” said Glenn Hillam, Sr. Solution Architect at Intricity.
This approach provided breathing room for the Client to massively downscale their licensing in time for the Microsoft true-up event and also reap the benefits of Snowflake’s compute. Additionally Intricity architected the Snowflake warehouse so that it was ready for the advent of newer data synchronization tooling later down the road.
Win 3: Bridging the Old and the New
The Client built a significant footprint of BI logic on the old data warehouse. While the new data warehouse addressed a great number of gaps which the old data warehouse struggled to deliver, the analytics and reports had to adapt to the new logic.
“Sure it would have been nice if we could have just told their hundreds of users that we’re just going to flip the switch to the new logic set. But doing this would have been extremely disruptive to the Client’s critical analytics,” said Hillam.
To help the Client gradually transition the solution, Intricity developed a layer which could provide the same logical assumptions of the old data warehouse and still service those tactical analytics, while the new content got transitioned to the broader analytics user community.
Win 4: Time Zones
Imagine your entire store is moving over time zones, often multiple times per day. By leveraging the timezone aware features in Snowflake, Intricity was able to deal with these transitions in an elegant way. Despite the constant time zone transitions in the streaming data, the data warehouse provided a powerful time dimension which could deliver context in their analysis.
Call to Action
To schedule a time to discuss your landscape with an Intricity specialist, go to https://www.intricity.com/contact-us and register to talk with a specialist or call the office number near you.
Who is Intricity?
Intricity is a specialized selection of over 100 Data Management Professionals, with offices located across the USA and Headquarters in New York City. Our team of experts has implemented in a variety of Industries including, Healthcare, Insurance, Manufacturing, Financial Services, Media, Pharmaceutical, Retail, and others. Intricity is uniquely positioned as a partner to the business that deeply understands what makes the data tick. This joint knowledge and acumen has positioned Intricity to beat out its Big 4 competitors time and time again. Intricity’s area of expertise spans the entirety of the information lifecycle. This means when you’re problem involves data; Intricity will be a trusted partner. Intricity's services cover a broad range of data-to-information engineering needs:
What Makes Intricity Different?
While Intricity conducts highly intricate and complex data management projects, Intricity is first a foremost a Business User Centric consulting company. Our internal slogan is to Simplify Complexity. This means that we take complex data management challenges and not only make them understandable to the business but also make them easier to operate. Intricity does this through using tools and techniques that are familiar to business people but adapted for IT content.
Intricity authors a highly sought after Data Management Video Series targeted towards Business Stakeholders at https://www.intricity.com/videos. These videos are used in universities across the world. Here is a small set of universities leveraging Intricity’s videos as a teaching tool:
Talk With a Specialist
If you would like to talk with an Intricity Specialist about your particular scenario, don’t hesitate to reach out to us. You can write us an email: email@example.com
(C) 2023 by Intricity, LLC
This content is the sole property of Intricity LLC. No reproduction can be made without Intricity's explicit consent.
Intricity, LLC. 244 Fifth Avenue Suite 2026 New York, NY 10001
Phone: 212.461.1100 • Fax: 212.461.1110 • Website: www.intricity.com