Ted Tasker

A Fortune 20 healthcare company migrated on-premise database appliances to Azure, including 4,000 tables with a mix of corporate data warehouse sources of truth and business unit (BU) analytic sandboxes.

I led the architecture approach of a corporate data lake and a metadata-driven system for business units to subscribe to data and compute needs for their own Synapse workspace. Data could be imported or linked as external. DB pool could be sized and scheduled to meet the BU needs.

Designed the metadata repository that controlled all workflow and processing of data from source to corporate data lake to BU analytic marts. I also managed the repository, developed stored procedures that notebooks called, and adjusted the design as needed to meet business needs.

Wrote the generic code to implement DDL for a new or changed subscription. Databricks notebooks would create incremental data sets for loading to business unit Synapse DBS. I created the Synapse stored procedures, which dynamically created the required polybase commands for fast loads. The system had detailed tracking of load capabilities, including storing the generated dynamic SQL for easy debugging.

The new design allows easy tracking of costs and business value of data.

A manufacturing company was failing to meet its data warehouse SLA because of slow-running loads. I applied best practices for SQL on a massively parallel processing system. A stored procedure for loading data that had been taking over four hours was rewritten to run in 20 minutes.

I also reviewed the entire database and made over 40 changes to table distribution models and storage types to align with best practice recommendations from Microsoft. I created scheduled maintenance routines to improve the performance of execution times.

At the end of the project, the customer was able to reduce their computing cost by 33% while getting performance execution times.

The company had an existing UI and back-end system to manage clients' prior medical authorizations. Larger hospital systems did not want to manually create through UI but submit EMR JSON records through a REST API that inserted the JSON as a blob into a staging table.

Different hospitals had different formats; I created an Excel spreadsheet for analysts to map a specific customer's JSON to the transactional system. Once completed, the Excel was imported as metadata to the SQL system.

Initial testing showed the logic for translation could be built correctly as dynamic SQL. However, the performance of dynamic building logic was too slow to keep up with 20,000 EMR JSON blobs an hour.

I pivoted the code from building dynamic SQL within procedures to leveraging the metadata to generate hard-coded procedures with all the logic in place. Once an analyst uploaded a new mapping or corrected one, they could kick off a process that would generate a new stored procedure based on the originator of the JSON.

A master stored procedure fired for every JSON blob that hit the stage, originating hospital also available; the master procedure would dynamically execute the custom proc, passing in the ID of the JSON to be processed.

A corporation wanted to apply and manage standardized auditing practices across its SQL servers. Creating auditing and data masking is often done through SQL server UIs, but it can also be scripted with T-SQL.

I created a system using a low-powered centralized management SQL server. A metadata repository holds information about what audit requirements are for what tables, what server-level auditing, what data masking to apply, and where.

The central server would ensure all other servers had current requirements applied by leveraging linked servers to the centralized management server nightly. If a new server had been added, the nightly job would provision all aspects of setting up the auditing and data masking.

The central server also collected all audit logs from all servers nightly. It combined them into a single high-level report that would list any audit infractions if, for example, the database administrator altered their permissions, any changes to users, and high-level info about the number of users and tables queried the previous day.

The creation of this system allowed a single DBA to manage multiple servers simply and easily, ensuring corporate compliance.