Shaping Transport for Greater Manchester's Data Processes

Transport for Greater Manchester (TfGM) is the local government body responsible for delivering Greater Manchester’s transport strategy and commitments. TfGM delivers the transport policies set by the Greater Manchester Mayor and the Greater Manchester Combined Authority.

They invest money in improving transport services and facilities, to support the regional economy. TfGM owns Metrolink (the UK’s largest light rail network), Greater Manchester’s bus stations, stops and shelters and transport interchanges. They subsidise fares to help the elderly, children and disabled people get around. TfGM also pays for bus services at times and in areas with no commercial bus services. They promote and invest in walking and cycling as safe, healthy and sustainable ways to travel, and work closely with bus, tram and train operators to help improve the full journey experience.

The Challenge

With the imminent launch of contactless in the Summer of 2019 on Greater Manchester’s Metrolink network, TfGM needed the ability to use network data in order to understand and make improvements to the network, services and products provided to customers. TfGM needed to ingest data from their service providers, via various methods, and transform this into usable insight for operational and informational purposes. TfGM needed to use contactless data to understand and make improvements to the services and products provided to customers.

TfGM wanted the solution to be expandable, as a service whenever possible. A key requirement was to automate all tasks, including verification of data quality before any ETL processes were run. Another was to be cost-effective at scale. The solution would need to scale to several hundred users without any changes to the architecture to support the new load.

Why Amazon Web Services

TfGM adopted AWS because it has all the services to do the initial tasks. It also allows for any future solutions. The requirement was to fully script environments end to end. This was to allow repeatable patterns. For this, we made use of Terraform to create all the native AWS services and also extend to create all database objects too.

To enable maximum automation, flat file imports would have the row counts, column counts and data types verified. This was done using AWS Lambda functions. Any failures would automatically raise help desk tickets with the supplier without any ETL jobs commencing on the TfGM side. After verification, the ETL environment was initiated using SQS; this orchestrated the instantiation of Matillion as well as other tasks.

Outside of the native AWS tools, other AWS partners were used. This includes Matillion for ETL, Snowflake (for the data warehouse) and Tableau Online (for reporting and visualisation). AWS also host all these services.

The Benefits

synvert TCM (formerly Crimson Macaw) could bootstrap a full production, test and development environment in weeks. This allows TfGM to ingest data feeds, transform the data and create reports. The full scripting of these environments allowed the creation of completed new end-to-end environments in hours rather than days. This enables TfGM to speed up delivery whilst still having certainty over their environments and testing.

TfGM were able to use Snowflake and Tableau to give them new insights into travel patterns and behaviours. This meant TfGM were able to adapt the network and add improvements to contactless services. These new insights came first from the ability to store and analyse any volume of data required in the new Snowflake EDW and secondly from the new visualisation capability delivered via Tableau.

Malcolm Lowe, Head of IT at Transport for Greater Manchester said

We have lots of data and information on the transport network and the challenge is capturing all this information, the dependencies and getting insight from it. When we don’t have to worry about technology, infrastructure, storage or performance, it means we can concentrate and focus on getting insight to improve the transport network and travel of people in Greater Manchester.