Hi, I’m Kayla. I’ve built and run data stacks at three companies. I’ve used Snowflake, BigQuery, and Redshift. I’ve shipped with dbt, Fivetran, Airflow, and Looker. Some choices made my team fast and calm. Others? They cost us sleep and cash. Here’s my honest take.
Quick outline
- My setup and stack
- What worked well with real numbers
- What broke and why
- Tool-by-tool thoughts
- My go-to checklist
My setup (so you know where I’m coming from)
- Company A: Snowflake + Fivetran + dbt + Tableau. Heavy sales data. Many SaaS sources.
- Company B: BigQuery + Airbyte + dbt + Looker. Event data. High volume. Spiky loads.
- Company C: Redshift + custom CDC + Airflow + Power BI. Lots of joins. Finance heavy.
If you need a side-by-side rundown of Snowflake, BigQuery, and Redshift, this concise comparison helped me ground my choices: Snowflake vs BigQuery vs Redshift.
I’m hands-on. I write SQL. I watch costs. I get the 2 a.m. alerts. You know what? I want things that are boring and safe. Boring is good when your CFO is watching.
What actually worked (with real examples)
1) Simple models first, then get fancy
I like star schemas. Clean hubs and spokes. Facts in the middle. Dimensions on the side. It sounds old school. It still works. For more thoughts on how various modeling patterns compare, check out my take after trying different data warehouse models.
- Example: At Company A, our “orders” fact had 300M rows. We split customer, product, and date into easy dimension tables. Queries went from 9 minutes to under 50 seconds in Snowflake. Same logic. Better shape.
I do use wide tables for speed in BI. But I keep them thin. I treat them like fast lanes, not the whole road.
2) ELT with small, steady loads
I load raw tables first. I model later. Tiny batches help a lot. If you’re still deciding between using an ODS first or jumping straight into a warehouse, I’ve broken down when each one shines.
- Company B used BigQuery. We pulled CDC from Postgres through Airbyte every 5 minutes. We partitioned by event_date and clustered by user_id. Our daily rollup dropped from 3 hours to 28 minutes. Cost fell by 37% that quarter. Not magic—just smaller scans.
For Snowflake, I like micro-batching plus tasks. I set warehouses to auto-suspend after 5 minutes. That alone saved us $19k in one quarter at Company A.
3) Guardrails on cost (or you’ll feel it)
Do you like surprise bills? I don’t.
- In BigQuery, we set table partitions, clusters, and cost controls. We also used “SELECT only the columns you need.” One team ran a SELECT * on an 800 GB table. It stung. We fixed it with views that hide raw columns.
- In Snowflake, we used resource monitors. We tagged queries by team. When a Friday 2 a.m. job spiked, we saw the tag, paused it, and fixed the loop. No more mystery burns.
- In Redshift, we reserved bigger jobs for a separate queue. Concurrency scaling helped a lot.
4) Testing and CI for data, not just code
We added dbt tests for nulls, duplicates, and relationships. Nothing wild. Just enough. The detailed testing playbook I landed on is here: the data-warehouse testing strategy that actually worked.
I also like a small smoke test after each model run. Count rows. Check max dates. Ping Slack when counts jump 3x. Not fancy. Very useful. Putting those safeguards in place was what finally let me go to bed without dreading a 2 a.m. page—exactly the story I tell in what actually helped me sleep while testing data warehouses.
5) Handle slowly changing things the simple way
People change jobs. Prices change. Names change. For that, I use SCD Type 2 where it matters.
- We tracked customer status with dbt snapshots. When a customer moved from “free” to “pro,” we kept history. Finance loved it. Churn metrics finally matched what they saw in Stripe.
6) Permissions like neat labels on a garage
PII gets tagged. Then masked. Row-level rules live in the warehouse, not in the BI tool.
- In Snowflake, we masked emails for analysts. Finance could see full data; growth could not. In BigQuery, we used row access policies and column masks. It sounds strict. It made people move faster because trust was high.
7) Docs where people actually look
We hosted dbt docs and linked them right in Looker/Tableau. Short notes. Clear owners.
- After that, “What does revenue mean?” dropped by half in our Slack. Saved time. Saved sighs.
8) Clear landing times and owners
We set “data ready by” times. If a CSV from sales was late, we had a fallback.
- One quarter, we set 7 a.m. availability for daily sales. We also set a “grace window” to 8 a.m. for vendor delays. No more 6:59 a.m. panic.
What broke (and how we fixed it)
- One giant “master” table with 500+ columns. It looked easy. It got slow and messy. BI broke on small schema changes. We went back to a star and thin marts.
- Bash-only cron jobs with no checks. Silent failures for two days. We moved to Airflow with alerts and simple retries.
- Letting BI users hit raw prod tables. Costs spiked, and columns changed under them. We put a governed layer in front.
- Not handling soft deletes. We doubled counts for weeks. Then we added a deleted_at flag and filtered smart.
I’ll admit, I like wide tables. But I like clean history more. So I use both, with care.
Tool thoughts (fast, honest, personal)
Snowflake
- What I love: time travel, virtual warehouses, caching. It feels smooth. Running a full hospital analytics stack on Snowflake pushed those strengths—and a few weaknesses—to their limits; I wrote up the gritty details in how it really went.
- What to watch: cost when a warehouse sits running. Auto-suspend is a must. We set 5 minutes and saved real money.
- Neat trick: tasks plus streams for small CDC. It kept loads calm.
BigQuery
- What I love: huge scans feel easy. Partitions and clusters are gold.
- What to watch: queries that scan too much. Select only what you need. Cost follows bytes.
- Neat trick: partition by date, cluster by the field you filter on the most. Our 90-day event dashboards popped.
Redshift
- What I love: strong for big joins when tuned well.
- What to watch: sort keys, dist styles, vacuum/analyze. It needs care.
- Neat trick: keep a fast queue for BI and a slow lane for batch.
Real scenes from my week
- “Why is the orders job slow?” We found a new UDF in Looker pulling all columns. We swapped to a narrow view. Run time fell from 14 minutes to 2.
- “Why did cost jump?” An analyst ran a cross join by mistake. We added a row limit in dev. And a guard in prod. No harm next time.
- “Which revenue is real?” We wrote a single metric view. Finance signed off. Every dashboard used that. The noise dropped.
My go-to checklist (I stick this in every project)
- Start with a star. Add thin marts for speed.
- Micro-batch loads. Keep partitions tight.
- Add dbt tests for nulls, uniques, and joins.
- Set auto-suspend, resource monitors, and cost alerts.
- Mask PII. Use row-level rules in the warehouse.
- Document models where people work.
- Keep dev, stage, and prod separate. Use CI.
- Track freshness. Page someone if data is late.
- Keep raw, staging, and mart layers clean and named.
Final take
Enterprise data can feel loud and messy. It doesn’t have to. Small choices add up—like labels on bins, like setting the coffee pot timer.
Looking for an end-to-end template of a production-ready data warehouse? Check out BaseNow