From 0 To Data Solution In 5 Phases To Land Contracts & Wow Bosses

Currently job searching? Give yourself an edge by developing a personal project using my free 5-page project ideation guide.

In my mind, no data solution that begins with code doesn’t end in frustration or, at best, an overhaul six-ish months down the road. As I’ve gained proficiency, expertise and speed with conceiving, developing and implementing data solutions at varying scales, I’ve had few opportunities to develop from scratch — until I built my own reporting infrastructure.

In a jet-lagged (I had just flown from France) conversation with a fellow data professional seeking advice on building a data analytics solution I half-ranted, half-outlined steps I consider essential to building a data solution that isn’t just ready for use, but also one that is sustainable as it scales.

Perhaps I listened to myself talk for too long, but I believe there’s value to sharing the exchange with a wider audience. If only for the reason of keeping this train of thought coherently on the tracks.

The phases I present were gleaned through lessons, mistakes and observations at work and in my own tinkering. While they don’t necessarily represent a comprehensive roadmap to implementing a data warehouse-based solution, if I may say, it’s not a bad starting point.

It doesn’t take a close read to notice a common thread between all the phases: Every step you take to implement a data solution must be done in service of your stakeholder’s needs and, by extension, deliver tangible, impactful business value.

Phase 1: Discovery

Whether you’re bringing in a new source to your data warehouse or starting from scratch, every task begins the same: A specific, comprehensive conversation with the requestor.

Before even beginning to discuss the “fun stuff” like common keys, building views, etc. you have to start with basic inquiries, which center on two questions:

• What data do you have (and where is it)?
• What data do you want (and why)?

If you’ve been brought on as a “hired gun” contractor tasked with building a data warehouse, your client most likely has a bunch of data they have no awareness of or plan for.

You need to have an honest and thorough conversation about why this data is important and, most critically, where it is currently stored.

Tangled in a web of spreadsheets, platform dashboards and legacy databases is likely useful, revenue-driving insights. But they will need to be mined and exposed.

When ingesting your data initially, I suggest you don’t immediately rush to a programmatic approach and think deeply about what is the simplest, lowest maintenance method to implement.

This might mean using a no-code or low-code tool that features an out-of-the-box integration with your target API instead of dedicating hours to developing a from-scratch pipeline.

To execute this phase I suggest making a written list with these columns:

• What data is needed
• What data you (or the client) have
• Where available data lives
• The lowest-effort way to extract that data

And, most importantly, review the first 3 points with your stakeholder or client.

Phase 2: Design

To paraphrase Abraham Lincoln: A data warehouse built thoughtlessly and without attention to scale will not stand. Though it is true that you can prop up such infrastructure with more compute power — but this often produces diminishing returns.

To avoid costly and annoying rebuilds down the road, it’s important to take time and truly think through not just your ingestion methods, but also how you will ultimately store your data.

Outlining a data warehouse, whereby you store data from separate sources in a central location, is a popular solution and, honestly, not a bad starting point if a client hands you a ton of structured data with little more than a shrug.

If you’re working with a higher volume of data or you happen to ingest unstructured data, you may consider setting up a data lake or data lakehouse (a term made popular by Databricks).

Though this might sound basic, it’s critical to consider your data’s resting form. Simply importing CSVs to BigQuery isn’t going to be a universally sustainable solution, especially if you’re not willing or able to pay monthly short-term and long-term storage costs.

At the same time you’re considering how to store data, you’ll also want to start thinking about your data’s most useful final form.

You’re most likely not going to need every field or even every day’s worth of data in a dashboard. So, you’ll want to think about how to separate your source data from the chunk of data you want your stakeholders to be able to access.

Converting unwieldy tables to cleaner tables or views can both reduce processing and reduce clutter.

At this phase it’s useful to take two concrete steps.

Sketch your ideal data warehouse

• Identify approximately how many datasets you’ll need to create/maintain (if possible, of course)
• Rank your data sources from most important (primary) to least important (secondary)
• Arrive at a ballpark estimate of resource costs

Create a view wishlist

• Note any common keys that can be used to facilitate JOINs and create aggregate tables
• Jot down dimensions, separating them into a “must-have” and “nice-to-have” columns on a sheet of paper
• Find opportunities to create materialized views to further reduce compute operations

Even though, at this phase, you may not possess a complete understanding of the scope of your data, you at least have a starting point to make storage and access less intimidating.

Phase 3: Ingestion

Creating robust and efficient pipelines is how data engineers earn their paychecks — and often save organizations thousands in monthly infrastructure costs.

If you’re a one-person data team or are starting out in a less mature data org, chances are the task of creating these ingestion pipelines is going to be assigned to you.

Since I’ve written extensively about the “how” of building extract load (EL), extract load transform (ELT) and extract transform load (ETL) pipelines, I’ll focus on what to look out for.

Like many aspects of technical work, building a data pipeline can be as simple or complex as you make it.

Without a doubt, if you’re a small operation, this phase can take 6–12 months. And the thing is, ingestion should take that long. If you’re working on contract one of the best things you can do for yourself (or your team) is to be realistic in your estimate for completing ingestion work.

If it helps, share these steps with your stakeholder — hopefully they’ll be able to understand why data engineering, when done correctly, takes so long:

• Gather requirements
• Locate source data
• Review documentation for APIs or necessary platforms
• Gain authorization/access tokens
• Code
• Code
• Code
• Conduct QA
• Create production tables
• Deploy to production
• Create (or add to) monitoring infrastructure

Repeat ad infinitum.

Even though pipelining involves a lot of steps and can require a lot of patience, it’s so important to get right since it’s unlikely you can dashboard your way around problematic or nonexistent data.

Phase 4: Downstream Build

The bad news, if you don’t have access to a dedicated data analyst/data visualization engineer, is that you’re going to be building a lot of dashboard tiles.

The very good news is that if you’ve correctly and thoroughly completed phases 1–3, this should be easier than you think. Depending on your level of interest in presenting and visualizing data, this may even be enjoyable.

To be successful in this phase, you want to consider who your ultimate audience will be.

• Are these visualizations going to have a narrow appeal to only the most senior decision makers?
• Or are they going to be widely used throughout the org?

If you’re building for an executive audience, in my experience, you’ll likely be creating a dashboard populated with aggregate measures and “top line” items.

While some decision makers might pore over the data for hours at a time, it’s likely that they might just want a health check of the business they can review with morning coffee.

If you’re developing for a wider audience, in your chosen visualization platform you’ll want to ensure that you both create a clean, aggregate dashboard and expose dimensions your users can manipulate and examine in their own builds.

In either case, this is where performance becomes increasingly important.

Slow loading or broken dashboards are a bad look and can negatively impact revenue (without available data, leadership is unable to make informed, profitable decisions).

Which is why you want to employ every optimization strategy at your disposal before the data is accessible downstream.

You’ll also want to have a monitoring system in place to alert you to missing data or slow loading displays. Ideally, your alerting system would inform you before you receive a stakeholder complaint.

Phase 5: Quality Assurance & Ongoing Tasks

Although you should be employing quality assurance measures throughout your process, it’s essential to build in time to double and triple-check data availability, accuracy and applicability well before your scheduled release.

QA shouldn’t happen in a vacuum. To ensure transparency and get a proper buy-in from your stakeholders, it’s important to make your QA results and documentation available when ready.

Be sure you’re able to explain which calculations you used to validate your data; thankfully, it doesn’t have to be complex math.

With many upstream sources not providing real-time data and some of the larger platforms even delaying data availability, it’s helpful to establish a realistic threshold for data accuracy, since 100% parity is sometimes not possible.

I’d suggest a variance threshold in the 3–5% range, but your stakeholder might be more flexible.

After your initial data analytics solution is approved, you might be tempted to breathe a sigh of relief and end your contract on favorable terms.

Unfortunately, the work doesn’t stop here. The 5 phases I describe simply outline a baseline approach.

There are many stages of data maturity and this approach barely goes past organizational adolescence.

Assuming your stakeholder or client is happy with your work and doesn’t require a more mature operation just yet, they’ll likely still have ongoing tasks for you that could include:

• Building new pipelines and dashboards
• Bringing in new dimensions to existing views/dashboards
• Backfilling historic data
• Creating a monitoring function to trigger downtime alerts
• Being on-call for any downtime/fire drills
• Writing documentation to serve as a foundation for future design/iteration
• Training new users and helping troubleshoot dashboards
• Exploring opportunities for optimization and cost savings

Based on that list, you should have very little concern about automating yourself out of a job.

Thinking deeply about how I’d build a data analytics suite was a spontaneous and interesting exercise when I initially answered the “how would you do this” question.

Make no mistake: Even though these steps are neatly laid out, each phase is incredibly time consuming. My small scale project took me 2–3 months.

Professionally, my org took 1.5–2 years to develop baseline infrastructure before I was hired.

For the past 3 years, with the data warehouse built out, I’ve been bouncing consistently between phases 3 and 5.

And I’ve taken on many, many of the ongoing tasks listed.

I need your help. Take a minute to answer a 3-question survey to tell me how I can help you outside this blog. All responses receive a free gift.