Establishment of a standardized and integrated data platform Part 2: Data Ingestion – TEG Case Study

Through their integrated operating model, TEG (Ticketek) bring the best live content, ticketing and technology to their partners and create memories for fans that last a lifetime.

A key differentiator in their approach is being a truly digital company first, enabling data and analytics to help shape the customer experience.

Introduction

In this part of the series, we’ll explore how Altis and TEG achieved real time data ingestion into the data platform using AWS and Snowflake components.

Altis supplied two metadata driven frameworks to simplify and expedite the development process:

  • Snowstream: for streaming operational data into Snowflake staging
  • DLA (Data Load Accelerator): for populating the data warehouse dimensional model

Each section of the data ingestion solution are discussed in more detail below.

Source System to Cloud Storage

AWS Kinesis & Lambda are used to process and transform source system data, making it available in Simple Storage Service (S3) before it is ingested into Snowflake.

Each logical streaming object has an individual instance of these products to maximise performance and scalability. Read more about this in the following case study.

Cloud Storage to Snowflake Staging

Altis Snowstream is a purpose-built tool for ingesting and integrating streaming data into Snowflake with CICD support. The tool is built using native Snowflake SQL components requiring no additional software. Watch a 35-minute webinar on Snowstream here.

Snowstream provides a repeatable pattern to accelerate the development process. It is a metadata driven approach and ensures the platform can be supported with minimal skills.

The following is a summary of the Snowflake components used by Snowstream and a high-level description of their purpose:

  • Storage Integration: Provides a secure connection between Snowflake and AWS S3
  • External Stage: JSON files for each object in S3 are referenced via a Snowflake external stage.
  • Snowpipe: When objects are created in S3, event notifications trigger Snowpipe to execute a copy command from the external stage to a landing table.
  • Table streams: Simplifies how new data in landing is detected (change data capture)
  • Tables:
    • Object definition metadata is stored in standard database tables. Both simple and complex JSON (e.g., multiple nested arrays) can be accommodated. Several load patterns (e.g., append, merge) are supported.
    • Audit Log tables record processing activity
  • Stored Procedures: SQL code that can be called to process data from landing to staging schemas according to the metadata definition.
  • Tasks: Execute SQL on a schedule. Can be configured to only execute if streams have data. Tasks can be chained together to aid data processing orchestration.

Snowflake Staging to Presentation 

After operational data has been streamed into Snowflake staging, the Altis DLA framework is used to control the flow and transformation of data into a dimensional model. This presentation layer is accessed by the BI tool (Tableau) where reports and dashboards are served to TEG’s business users.

The DLA provides pre-built logic and data orchestration templates to reduce development time and allow developers to focus on value-adding business requirements.

The process is orchestrated using Snowflake tasks which call SQL stored procedures. The DLA stored procedures reference SQL views which contain the data transformation business logic and populate the data warehouse star schema.

Do you want to find out more about establishing an integrated data platform?

Connect with Altis today to find out how we can help maximise your business performance.

Share

Facebook
Twitter
LinkedIn
WhatsApp

Leave a Reply

Your email address will not be published.

Recent

Connect with us

If you’d like to be kept in the loop on courses, events and other related topics, simply complete your details and we’ll add you to our list.

We use cookies to improve your experience and support our mission.
Read more about it here. By using our sites, you agree to our use of cookies.