For most of the last decade, the data warehouse at an investment management firm has looked roughly the same regardless of the vendor logo on it. A SQL Server or Oracle box, sized for peak load that only happens a few days a month, running overnight batch jobs that pull from custodians, prime brokers, market data vendors, and the OMS, then land everything into a set of tables that the performance and compliance teams query the next morning. If a job fails at 3 a.m., someone gets paged, and the first question in the incident channel is always some version of "will positions be ready by open."
I have spent a fair amount of the last year moving pieces of that stack onto Snowflake, and I want to write about what actually changes, because the marketing version of this story and the operational version of this story are not the same thing. Snowflake does not make your data pipelines good. It removes a specific, painful category of infrastructure work that has nothing to do with your actual data problems, which frees you up to focus on the parts that do.
The Problem It Actually Solves
The core architectural idea in Snowflake is that storage and compute are separate. That sentence gets repeated so often in vendor decks that it stops meaning anything, so here is what it means in practice for a firm running nightly position reconciliation.
In a traditional warehouse, your compute and your storage live on the same box, sized together. If your month-end NAV process needs a large instance for four hours and your intraday reporting needs a small one the other twenty, you either provision for the peak and pay for idle capacity the rest of the time, or you provision for the average and watch month-end jobs run long. I have lived both versions of this at different firms, and neither is good.
In Snowflake, the data sits in cloud object storage, and you spin up independent "virtual warehouses" - just compute clusters - against it, sized for whatever job is running. Month-end NAV gets a large warehouse for four hours and auto-suspends the second it's idle. Intraday compliance queries run on a small warehouse that's usually asleep. They are billed separately, by the second, and they do not contend with each other for resources because they are not the same hardware. That last part matters more than it sounds: the number of "why is my dashboard slow, is it the overnight load" incidents I have investigated over the years, where the actual answer was resource contention between two workloads that had no business sharing a box, is not small.
What a Real Pipeline Looks Like
Here is roughly the shape of what we built, stripped of anything firm-specific. Raw files land first, transforms happen in the warehouse, not before it.
-- Raw custodian file lands as-is, no transformation on the way in
CREATE OR REPLACE STAGE custodian_raw
URL = 's3://firm-data-landing/custodian/'
STORAGE_INTEGRATION = s3_prod_integration
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"');
-- Snowpipe auto-ingests new files as they arrive, no cron job to babysit
CREATE OR REPLACE PIPE custodian_ingest_pipe
AUTO_INGEST = TRUE
AS
COPY INTO raw.custodian_positions
FROM @custodian_raw
FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);That "land it raw, transform it in the warehouse" pattern is the part that took the most convincing internally, because it inverts how a lot of legacy ETL teams think. The instinct with a traditional pipeline is to clean and validate data on the way in, because compute is expensive and you don't want to be reprocessing garbage. With cheap, elastic compute and cheap storage, the calculus flips: land everything exactly as the custodian sent it, keep it, and do your validation and transformation as a queryable, versioned step downstream. When a custodian silently changes a file format at 11 p.m. - and they will - you have the raw file to diff against, instead of a corrupted table and a guessing game.
For the transform layer itself, we run dbt on top of Snowflake, which is a genuinely good combination. Positions, transactions, and performance marts are built as dbt models with tests attached - things like "position quantity should never be null" or "every transaction should map to a known security" - and those tests run as part of every deploy, not as a separate QA pass someone remembers to do occasionally.
The Two Features That Change Daily Operations
Two Snowflake-specific features have had more day-to-day operational impact on my team than anything else, and neither gets much attention in the sales materials.
Zero-copy cloning. You can clone a multi-terabyte production database in seconds, and it does not duplicate the underlying storage - it just creates new metadata pointers, with storage only diverging as data actually changes.
CREATE DATABASE positions_dev CLONE positions_prod;Before this, giving a developer a realistic dataset to test a positions reconciliation fix meant either testing against a stale, scrubbed subset that didn't reproduce the bug, or getting DBA time allocated to carve out a real copy, which took days and ate real storage. Now any engineer can clone full production scale in under a minute, test against it, and drop it. This alone changed how confidently people ship fixes to reconciliation logic, because they can actually reproduce the bug they're fixing before they fix it.
Time travel. Snowflake keeps a queryable history of every table for a configurable retention window, so you can query the exact state of a table as of any past timestamp.
SELECT *
FROM raw.custodian_positions
AT (TIMESTAMP => '2026-09-15 06:00:00'::timestamp);For a compliance team, this is not a convenience feature, it is close to a requirement. "What did the position table say at 6 a.m. on the day of this trade" is a question that comes up during audits and investigations, and answering it used to mean digging through backup tapes or nightly snapshot tables that someone remembered to build. Now it's one query, against live data, going back however many days retention is configured for.
Where the Real Work Still Is
None of this replaces actual data engineering discipline, and I want to be honest about where the effort still goes.
Cost governance is a real job, not a footnote. Auto-suspend and auto-scaling on virtual warehouses are the mechanism, but someone still has to set sensible defaults, review the query history for runaway costs, and catch the analyst who left a large warehouse running against a Cartesian join over the weekend. We built a scheduled job that flags any warehouse running more than fifteen minutes past its typical job duration and pages the data team, because "the bill was unexpectedly high" is a conversation you only want to have once.
Security and access control took longer than the pipeline work itself, which is normal for a regulated firm and shouldn't surprise anyone. Snowflake's row-level security and dynamic data masking are genuinely good primitives - you can mask a client's SSN or account number for anyone outside a specific role without maintaining a second, redacted copy of the table - but designing the actual policy, mapping who should see what across compliance, trading, and client service, is a governance exercise that the tooling does not do for you.
CREATE OR REPLACE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('COMPLIANCE_ADMIN') THEN val
ELSE 'XXX-XX-' || RIGHT(val, 4)
END;And semi-structured data is still messy data. Snowflake's VARIANT type makes it straightforward to land a vendor's nested JSON market data feed without pre-defining a rigid schema, which is genuinely useful given how often those feeds change shape without notice. But "we can ingest it easily" and "we understand what's in it" are different problems, and the second one is still on you.
Is It Worth the Migration
For a firm still running batch ETL against an on-prem warehouse sized for month-end peak, I think the honest answer is yes, with a caveat: the value is concentrated in removing infrastructure toil - the capacity planning, the contention between workloads, the "can I get a realistic test environment" friction - and not in some vague AI-readiness story the vendor decks lean on. If your actual bottleneck is that nobody trusts the data, or the transformation logic has been undocumented tribal knowledge for six years, Snowflake will let you build the fix faster, but it will not build the fix for you.
The team that got the most value out of this migration was not the one that migrated fastest. It was the one that used the migration as a forcing function to finally write down what every transform was actually supposed to do, test it, and put it in version control. Snowflake made that easier to do. It didn't make it optional.
