We built a modern data stack from scratch and reduced our bill by 70%

These just seems like over engineered solutions trying to guarantee their job security. When the dataflows are so straight forward, just replicate into pick your OLAP, and transform there.

There is something here that doesn&#x27;t sit right.<p>We use BQ and Metabase heavily at work. Our BQ analytics pipeline is several hundred TBs. In the beginning we had data (engineer|analyst|person) run amock and run up a BQ bill around 4,000 per month.<p>By far the biggest things was:-<p>- partition key was optional -&gt; fix: required<p>- bypass the BQ caching layer -&gt; fix: make queries use deterministic inputs [2]<p>It took a few weeks to go through each query using the metadata tables [1] but it worth it. In the end our BQ analysis pricing was down to something like 10 per day.<p>[1] <a href="https:&#x2F;&#x2F;cloud.google.com&#x2F;bigquery&#x2F;docs&#x2F;information-schema-jobs-timeline#jobs_timeline-view" rel="nofollow">https:&#x2F;&#x2F;cloud.google.com&#x2F;bigquery&#x2F;docs&#x2F;information-schema-jo...</a><p>[2] <a href="https:&#x2F;&#x2F;cloud.google.com&#x2F;bigquery&#x2F;docs&#x2F;cached-results#cache-exceptions" rel="nofollow">https:&#x2F;&#x2F;cloud.google.com&#x2F;bigquery&#x2F;docs&#x2F;cached-results#cache-...</a>

I know it&#x27;s easy to be critical, but I&#x27;m having trouble seeing the ROI on this.<p>This is a $20k&#x2F;year savings. Perhaps, I&#x27;m not aware of the pricing in the Indian market (where this startup is), but that simply doesn&#x27;t seem like a good use of time. There&#x27;s an actual cost of doing these implementations. Both in hard financial dollars (salaries of the people doing the work) and the trade-offs of de prioritizing other other.

When working with ETL, it really helps to not conflate the letters or worry about them in the wrong order. A lot of the most insane complexity comes out of moving too quickly with data.<p>If you don&#x27;t have good staging data after running extraction (i.e., a 1:1 view of the source system data available in your database), there is nothing you can do to help with this downstream. You should stop right there and keep digging.<p>Extracting the data <i>should be</i> the most challenging aspect of an ETL pipeline. It can make a lot of sense to write custom software to handle this part. It is worth the investment because if you do the extraction really well, the transform &amp; load stages can happen as a combined afterthought [0,1,2,3] in many situations.<p>This also tends to be one of the fastest ways to deal with gigantic amounts of data. If you are doing things like pulling 2 different tables and joining them in code as part of your T&#x2F;L stages, you are really missing out on the power of views, CTEs, TVFs, merge statements, etc.<p>[0] <a href="https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;sql&#x2F;t-sql&#x2F;statements&#x2F;merge-transact-sql" rel="nofollow">https:&#x2F;&#x2F;learn.microsoft.com&#x2F;en-us&#x2F;sql&#x2F;t-sql&#x2F;statements&#x2F;merge...</a><p>[1] <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;sql-merge.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;sql-merge.html</a><p>[2] <a href="https:&#x2F;&#x2F;docs.oracle.com&#x2F;database&#x2F;121&#x2F;SQLRF&#x2F;statements_9017.htm" rel="nofollow">https:&#x2F;&#x2F;docs.oracle.com&#x2F;database&#x2F;121&#x2F;SQLRF&#x2F;statements_9017.h...</a><p>[3] <a href="https:&#x2F;&#x2F;www.ibm.com&#x2F;docs&#x2F;en&#x2F;db2&#x2F;12.1?topic=statements-merge" rel="nofollow">https:&#x2F;&#x2F;www.ibm.com&#x2F;docs&#x2F;en&#x2F;db2&#x2F;12.1?topic=statements-merge</a>

AKA The Monty Hall Rewrite <a href="https:&#x2F;&#x2F;alexsexton.com&#x2F;blog&#x2F;2014&#x2F;11&#x2F;the-monty-hall-rewrite" rel="nofollow">https:&#x2F;&#x2F;alexsexton.com&#x2F;blog&#x2F;2014&#x2F;11&#x2F;the-monty-hall-rewrite</a>

So you saved just $20k per year? Not sure the context of your company but I’m not sure if this turns out to be a net win given the cost of engineering resources to produce this infra gain

Why is data engineering so complicated?<p>I&#x27;m not a data engineer but was tasked with building an ETL pipeline for a large company. It&#x27;s all just Step Functions, looping through file streams in a Lambda, transforming, then putting the data into Snowflake for the analytics team to view. My pipeline processes billions of rows from many different sources. Each pipeline runs daily on a cron job (maybe that&#x27;s the key differentiator, we don&#x27;t need live streaming data, it&#x27;s a lot of point of sale data).<p>Whenever I hear actual data engineers talk about pipelines there are always a million different tools and complicated sounding processes. What&#x27;s am I missing?

We did have a discussion on Self vs Managed and TCOs associated with it. 1&gt; We have multi regional setup so it came up with Data Sovereignty requirements. 2&gt; Vendor Lock ins - Few of the services were not available in that geographic region 3&gt; With managed services, you often pay for capacity you might not always use. our workloads were often consistent and predictable, so self managed solutions helped in fine tuning our resources. 4&gt; One og the goal was to keep our storage and compute loosely coupled while staying Iceberg-compatible for flexibility. Whether it’s Trino today or Snowflake&#x2F;Databricks tomorrow, we aren’t locked in.

This is a little bit of a word soup. Its hard to see why the various redesigns were done without a set of requirements. I dont get why you&#x27;d trigger Airflow workflows for doing CDC. These things were designed for large scale batch jobs rather than doing CDC on a some Google sheets. Either way without scale numbers its hard to why PG was used or why the shift to BigQuery. Anyways the site uses Hugo, which actually sticks out for me.

How much did this cost in engineering time and how much will it cost to maintain? How about when you need to add a new feature? Seems like you saved roughly 1.5k per month which pays for a couple days of engineering time (ignoring product,mgmt and costs related to maintaining the software)

Good read.<p>I do have a question on the BigQuery. i f you were experiencing unpredictable query costs or customization issues, that sounds like user error. There are ways to optimize or commit slots for reducing the cost. Did you try that ?

Is Debezium the only good CDC tool out there? I have a fairly simple data stack and am looking at integrating a CDC solution but I really dont want to touch Kafka just for this. Are there any easier alternatives?

Why confluent instead of something like MSK, Redpanda or one of the new leaderless, direct to S3 Kafka implementations?

What&#x27;s the database that dbt is connected to in this scenario?

You’d think that pushing all of the data into any ldap database, but especially some of the newer postgres based ones would give you all the performance you need at 10% of the costs? Let alone all the maintenance of the mind boggling architecture drawing.

.. how many engineers?

&gt; &quot;We are a fintech startup helping SMEs raise capital from our platform where we provide diverse financial products ranging from Term Loan, Revenue Based Financing to Syndication, we face one unique data challenge: Our data comes from everywhere.&quot;<p>Reading carefully: the result of this work yields an expected $21,000 USD in annual operating cost savings for infrastructure services.<p>Is this resume driven development?<p>What was the opportunity cost of this work? Is the resulting system more or less maintainable by future employees&#x2F;teammates?