“From a cost perspective, to accomplish what we’ve done manually, we’d probably need two additional data engineers.”
Being thrown curveballs
Challenges
Data team did not have ownership over the data pipelines
Existing data stack not built for scalability, resulting in dashboards often being down, which harmed team credibility
Data siloed between on-prem systems and the cloud
Initially, the team used SQL Server to store its data on premises, but marketing data shared by MLB was stored in BigQuery on Google Cloud Platform (GCP). “Our data pipelines weren’t centralized within the analytics department. This made troubleshooting difficult because we first had to identify which chain in the process was broken,” explains Dominguez. “So it was a challenge to prioritize what data we needed, how often, and at what cadence. We wanted to own our ELT pipeline and have control over our ingestion and transformation processes.”
As the team started scaling and building more dashboards and analyses, they quickly realized their setup was not built to scale. “I built most of our data products—views, data marts, stored procedures that feed reports—using SQL Server Management Studio,” says Dominguez. “The problem was that troubleshooting was difficult. If something broke, finding exactly what went wrong was a time-consuming process. The job quickly shifted from building things to fixing things that were broken. And in our business, having dashboards that are constantly down or inaccurate affects our credibility. So Brennan and I spent a lot of time looking for a better solution to centralize our data and simplify our ELT processes.”
Another challenge the team faced began after they moved their Tableau instance to the cloud, which meant that they now had on-prem data processes with a cloud reporting platform. “Jobs would overlap, fail, or cause a domino effect where one failure led to multiple failures,” recalls DiChiara. “We wanted to eliminate that extra step and have data flow directly from cloud to cloud. We needed a solution that allowed us to transform the data in the cloud without moving it unnecessarily.”
Coming up with a game plan
Solution
All data in one place with Snowflake to future-proof the new data stack and avoid vendor lock in
Coalesce brought on as data transformation solution
Initially, the team considered going with BigQuery since MLB uses GCP, but after some research, DiChiara and Dominguez decided Snowflake was a better fit for what they were trying to achieve. “Part of our decision to go with Snowflake was to future-proof our stack,” explains DiChiara. “We have data from MLB in GCP, but we also have data from non-league sources. By choosing Snowflake, we ensured that we wouldn’t be locked into a single cloud provider. We wanted a cloud-agnostic platform that could adapt with us.”
Adds Dominguez, “It became clear very quickly that if we were going to use Snowflake, then Coalesce was a tool we needed to explore—if not go all in on. So we brought both on at the same time.” He recalls that after just a few one-hour Coalesce training sessions, they were off and ready to start building. “The Coalesce documentation is fantastic,” he says. “You can see the entire lineage of every node, which makes troubleshooting so much easier. If you can dabble in SQL, you can pick it up quickly.”
“The Coalesce documentation is fantastic. You can see the entire lineage of every node, which makes troubleshooting so much easier. If you can dabble in SQL, you can pick it up quickly.” —Juan Dominguez, Manager of Data Strategy
Dominguez says the team quickly learned best practices, such as avoiding nested queries inside nodes because that could hide issues in the lineage: “Instead, we were advised to break them into separate nodes and join them in a structured way.” With their new solution in place, the team had everything they needed to hit it out of the park.
Becoming an all-star team
Results
6 weeks to build entire reporting infrastructure versus 6 months with previous approach
30 minutes to build new data products instead of days
1 day to transform dynamic ticket sales data instead of weeks manually writing a 1,500-line SQL query
With the new data architecture in place, DiChiara and Dominguez next needed to get all of their data into Snowflake. However, rather than launching a traditional data migration project, they opted to start over again from scratch. “We ingested it directly into Snowflake, bypassing our on-prem SQL Server,” says Dominguez. “We created direct connectors from BigQuery, GCP, Salesforce, S3 buckets—wherever our data lived—and started fresh. We rebuilt our data marts and reporting layers from the ground up, referencing what we had on prem but not migrating any of it directly.”
Dominguez says that while building everything on premises originally took about six months, he was able to rebuild the same infrastructure in just a month and a half using Coalesce: “Reducing a six-month process to six weeks was incredible. Now, if I need to build a new data product, what used to take days now takes maybe half an hour. I can push it to development, test it in Snowflake, and move it to production within minutes.” DiChiara adds that Coalesce has also improved reliability: “We now get automated email notifications if a job fails, and we can fix issues before anyone even notices. That wasn’t possible before.”
“Reducing a six-month process to six weeks was incredible. Now, if I need to build a new data product, what used to take days now takes maybe half an hour. I can push it to development, test it in Snowflake, and move it to production within minutes.” —Juan Dominguez, Manager of Data Strategy
As another example of how drastically Coalesce has cut down the time needed to complete some of his regular tasks, Dominguez recounts his need to transform the data he would regularly receive from one particular partner. “Our dynamic ticket sales partner sends us daily data, but the granularity of their data is very different from what we need,” he says. “Originally I wrote a 1,500-line SQL query to transform it, a process that took me weeks. I had to build it in chunks because it was so complex. But in Coalesce, I built the same process in just one day, using a structured node-based workflow.”
As for future plans for the team, DiChiara says one of his goals is to build out a fully automated predictive analytics workflow to support use cases such as lead scoring and retention modeling: “We’ve been doing predictive analytics, but in a more manual way. For ticket forecasting and dynamic pricing, we work with a partner who models demand and suggests price changes. Internally, for things like lead scoring and season member retention, we’ve been running models manually—pulling data, running it through Python or other software, and then pushing results into our CRM.”
“Our goal now is to make that process more automated,” he explains. “Ideally, someone buys a ticket, enters our ecosystem, and automatically runs through our model without us having to manually feed data in and out. If they hit a certain threshold—say, a 50% likelihood of becoming a season member—they would automatically get pushed into our CRM for a rep to follow up. We’re working to make that a seamless end-to-end process using Snowflake and Coalesce.”
“We now get automated email notifications if a job fails, and we can fix issues before anyone even notices. That wasn’t possible before.” —Brennan DiChiara, VP of Business Strategy and Analytics
Dominguez adds that Coalesce will play a key role in many of the new initiatives the team hopes to focus on this year. “For any new data we bring in, Coalesce will be integral for ELT processing, ensuring data accuracy, and structuring it for reporting,” he says. “And as we start working more with machine learning—lead scoring, retention modeling, and so on—Coalesce will help us integrate those model outputs into our data pipeline.” The team is also excited to implement the Coalesce + Fivetran integration, which will make their processes even more efficient. “That’s something we want to implement soon,” he says.
Now that the team is fully equipped with Snowflake and Coalesce and their data architecture has been moved to the cloud, the whole game has changed. They are not just ready to play ball—they are set up to win.