Client success stories

Surf Life Saving Australia engaged AGER BI to review and modernise the data ingestion framework supporting its SurfGuard reporting environment.

Operational data from SurfGuardMemberships and SurfGuardAdmin was held within Microsoft SQL Server and transferred through Azure Data Factory into Snowflake for downstream reporting and analytics. Although the existing environment already used dynamic Azure Data Factory processing, configuration and operational behaviour remained embedded within Data Factory, making the platform increasingly difficult to manage, extend and support across several hundred source tables.

AGER BI designed and implemented a new metadata-driven Azure Data Factory ingestion framework centred on a Snowflake CONTROL configuration layer. The production framework supports approximately 340 source tables, with approximately 344 ultimately configured for nightly processing across the SurfGuardMemberships and SurfGuardAdmin environments. The solution was successfully validated, deployed to Production and accepted by the SLSA data team. It restored the nightly SurfGuard ingestion process, introduced centralised metadata configuration, established a clean Snowflake production environment and provided SLSA with detailed technical and operational documentation enabling the platform to be supported and extended internally.

Following implementation, SLSA’s Business Intelligence Team Leader confirmed that the process had got the “SurfGuard reporting back on track” and advised that AGER BI’s details had been retained for future work.

Surf Life Saving Australia is the national organisation supporting the surf lifesaving movement across Australia, coordinating activities spanning lifesaving, member services, education, sport and community safety.

A key part of SLSA’s reporting environment depends on operational data held within its SurfGuard SQL Server systems. This information is transferred into Snowflake, where it becomes available for reporting and analytics.

As the environment developed, the ingestion process had become an increasingly important dependency for SurfGuard reporting.

The existing Azure Data Factory environment already incorporated dynamic processing, but decisions about which tables were processed, their execution behaviour and processing sequence were embedded within the Data Factory implementation itself.

There was no central configuration repository that provided SLSA with a single view of the tables in scope, their source and destination, whether they were enabled, their processing sequence or their intended load behaviour.

At the same time, the existing Snowflake reporting schema had drifted from the SQL Server source structure, increasing the importance of establishing a clean and controlled production environment.

AGER BI was engaged to review the existing Azure Data Factory and Snowflake architecture and develop a simpler, reliable and maintainable approach capable of supporting several hundred SurfGuard tables.

Surf Life Saving Australia’s challenge was not simply moving data between SQL Server and Snowflake.

The previous implementation already contained generic Azure Data Factory pipelines capable of dynamically processing multiple tables. The more significant issue was that operational configuration remained embedded within those pipelines rather than being centrally managed as metadata.

At the scale of several hundred source tables, this made ongoing administration and troubleshooting increasingly difficult.

Determining what was configured to run required inspection of Azure Data Factory. Changing processing scope required modification of the Data Factory implementation. Tables could not be centrally enabled or disabled through configuration, and execution sequence and load behaviour were controlled within pipelines rather than through a governed metadata layer.

The existing Snowflake PUBLIC schema had also drifted from the structure of the source SQL Server environment. This introduced additional reconciliation and maintenance considerations for the reporting platform.

SLSA therefore needed more than a replacement pipeline. It required an ingestion architecture that could be operated, understood and extended without requiring developers to continually modify pipeline internals.

Key requirements included:

AGER BI undertook an architecture-led review of SLSA’s existing Microsoft Azure, Azure Data Factory, SQL Server and Snowflake environment before designing and implementing a new metadata-driven ingestion framework.

Rather than replacing components that were already working, AGER BI reused existing Azure connections, integration infrastructure and Snowflake capabilities where appropriate while redesigning the orchestration and configuration model.

At the centre of the new architecture is the Snowflake CONTROL.TABLE_CONFIG table.

This configuration layer determines which tables are processed and defines their source, destination, status, processing sequence and load behaviour. Azure Data Factory reads this metadata dynamically at runtime and uses reusable parameterised pipelines to process the configured tables.

The resulting framework supports approximately 340 source SQL Server tables without requiring individual pipelines for each table. New tables can be introduced primarily through configuration rather than redevelopment of the ingestion architecture.

Key deliverables included:

AGER BI redesigned the ingestion architecture around the principle of configuration over implementation.

Under the previous approach, processing scope and behaviour were defined inside Azure Data Factory. Under the new architecture, Azure Data Factory determines what to process by querying centrally managed Snowflake metadata.

The production pattern uses a reusable parent pipeline to retrieve active table configuration and invoke a generic child processing pipeline for each configured table.

At runtime, the framework resolves the required source database, source schema, source table, target schema, target table and other processing characteristics from metadata.

This means that Azure Data Factory does not require hundreds of table-specific pipelines or datasets.

The same reusable framework services both SurfGuardMemberships and SurfGuardAdmin, with metadata identifying the source system and the pipeline dynamically routing processing accordingly.

This shifted the management of the ingestion platform away from repeated development changes and towards a controlled configuration model.

Adding another table became primarily a metadata operation rather than a pipeline-development task.

The framework therefore transformed scalability from a development problem into a configuration problem.

A central Snowflake CONTROL schema was established to provide the operational control layer for the ingestion framework. At the centre of this layer, the CONTROL.TABLE_CONFIG table stores the configuration required to determine how each source table should be processed.

This provides SLSA with a single, queryable location for understanding and managing the ingestion estate without needing to inspect Azure Data Factory pipeline internals. Source systems, source and target table mappings, processing status, execution sequence and load type can all be centrally defined and controlled through metadata.

The approach improves operational transparency while simplifying the onboarding of additional tables. Tables can be introduced, enabled, disabled or reconfigured through metadata rather than requiring changes to the underlying Azure Data Factory pipelines.

The CONTROL framework also establishes a scalable foundation for future enhancements. Additional load patterns, including incremental processing, can be introduced through new metadata configuration and reusable processing components without redesigning the core Bulk Load Framework.

This allows the ingestion platform to evolve as SLSA’s requirements change while preserving the simplicity and maintainability of the production architecture.

AGER BI implemented the Bulk Load Framework as the production ingestion architecture.

The framework performs a controlled full refresh of each configured target table and runs nightly at 12:00 AM.

Each configured table is truncated and then reloaded from SQL Server into the Snowflake PROD schema. Azure Blob Storage is used transiently by the Snowflake V2 connector as part of the copy process but does not operate as a persistent business-data landing zone.

The Bulk framework provides a straightforward and supportable processing model while servicing hundreds of tables through the same reusable architecture.

Rather than designing unnecessary complexity into the initial production release, AGER BI deliberately separated Bulk and incremental processing into independent frameworks sharing the same metadata foundation.

This allowed SLSA to put a reliable production process in place first while retaining a defined pathway for further optimisation.

An important part of the engagement was establishing a clean Snowflake production environment.

A new PROD schema was created directly from the SQL Server source schema rather than continuing to inherit structures that had accumulated within the previous Snowflake PUBLIC environment.

This provided a cleaner production target aligned with the source systems.

AGER BI deliberately left the existing PUBLIC schema and previous Azure Data Factory processes unchanged during cutover.

This provided SLSA with a known rollback environment while the new framework was tested, reconciled and introduced into Production.

The approach reduced deployment risk by making the transition reversible rather than requiring an immediate destructive replacement of the previous architecture.

Following validation and reconciliation, the new PROD environment became the production ingestion target.

The framework was developed iteratively and validated before being rolled across the wider SurfGuard environment.

AGER BI first established and tested the underlying architecture using representative tables before progressively expanding the metadata configuration.

The solution progressed through proof of concept, development, testing, reconciliation and production deployment.

The new Snowflake PROD environment was brought online and the Bulk framework configured to load into it. SLSA then performed reconciliation against the resulting data before the solution became the production ingestion process. Outlook correspondence records SLSA undertaking reconciliation following deployment and confirming progress through the implementation.

The architecture and implementation were formally documented as the solution “built, tested and approved”, with the production framework validated through User Acceptance Testing and successfully deployed.

AGER BI treated supportability as a core architectural requirement rather than an activity to be completed after development.

A comprehensive 33-page Technical Solution Design was produced documenting the solution architecture, metadata framework, Azure Data Factory components, Snowflake structures, load patterns, deployment approach, monitoring, error handling, security, operational procedures, key design decisions and future roadmap.

The document was deliberately designed so that an engineer who had not participated in the original implementation could understand, operate, troubleshoot and extend the solution without relying on knowledge held by the original developer.

AGER BI also produced a dedicated Troubleshooting and Support Guide providing practical guidance for the day-to-day operation of the SurfGuard data platform. Together, the documentation explains how the solution is structured, how metadata configuration controls processing, how Azure Data Factory pipelines and Snowflake components operate, and how additional tables can be onboarded into the framework.

Operational guidance also covers monitoring nightly processing, identifying and diagnosing errors, recovering from failed loads, managing production deployment, maintaining security and preparing the environment for future enhancements.

This level of documentation was particularly important for an environment supporting hundreds of nightly table loads. By combining a reusable architecture with detailed technical and operational guidance, AGER BI provided SLSA with a solution designed to be maintained and supported internally over the long term.

Following the successful implementation of the Bulk Load Framework, SLSA asked AGER BI to assess options for introducing incremental processing and managing records deleted from the source systems.

AGER BI evaluated three architectural approaches. These included daily incremental MERGE processing combined with periodic full reconciliation, incremental MERGE processing with automated delete detection, and an incremental MERGE approach incorporating soft-delete processing.

After assessing the relative complexity, reliability, supportability and implementation effort of each option, AGER BI recommended daily incremental MERGE processing with periodic full reconciliation. This approach provided a practical balance between improving daily processing efficiency and maintaining a straightforward operational model.

The proposed architecture would use per-table watermarks to identify new and changed records, stage incremental extracts through Azure Blob Storage and a Snowflake STAGE schema, and use metadata-defined business keys to perform Snowflake MERGE / UPSERT processing. Watermarks would only advance following successful processing, supporting safe recovery and restartability.

The existing Bulk Load Framework would remain an important part of the architecture by providing periodic reconciliation between the source and Snowflake environments. This would ensure that records deleted from the source could be brought back into alignment without introducing the additional complexity of daily automated delete detection.

The incremental architecture was designed as a future enhancement rather than functionality already deployed into Production. This allowed SLSA to retain the proven Bulk Load Framework while having a clearly defined pathway for introducing incremental processing when required.

After reviewing the available options, SLSA elected to continue with the existing Bulk Load Framework and reassess incremental processing when its SQL Server environment is migrated to a managed SQL service.

This decision demonstrated an important strength of the overall architecture. The production solution was not dependent on the future incremental phase and could continue operating as a reliable, maintainable and supportable ingestion platform, while retaining a defined roadmap for future optimisation.

The engagement provided Surf Life Saving Australia with a substantially more structured and maintainable foundation for its SurfGuard reporting environment.

Key outcomes included:

The client subsequently confirmed that the engagement had got “the SurfGuard reporting back on track” and that AGER BI was now on SLSA’s radar for future work.

Surf Life Saving Australia required a simpler, more scalable way to manage its large data ingestion environment.

AGER BI combined Azure Data Factory, SQL Server and Snowflake expertise to redesign the platform around centralised metadata and reusable pipelines while retaining existing infrastructure where appropriate.

The solution was progressively validated and deployed with the previous environment retained for rollback, reducing implementation risk. Comprehensive technical and troubleshooting documentation also enabled SLSA’s team to operate and extend the platform internally.

The engagement demonstrates AGER BI’s ability to simplify business-critical data environments and deliver reliable, scalable and maintainable enterprise data solutions.