Managing Large Datasets Well: Six Habits from Raw Files to Reports

“Big data” is a slippery term. For most analysts the practical definition is: too large for Excel, too messy to fix by hand, and important enough that someone will ask where a number came from. A few million rows of daily positions, prices and transactions across several years meets that bar easily, and it does not need a cluster or a data lake to manage well. It needs a handful of habits applied consistently.

These are the strategies I find matter most, roughly in the order the data flows.

1. 01Land raw, unchanged
2. 02Validate at the boundary
3. 03Load incrementally
4. 04Model for the query
5. 05Keep history
6. 06Serve aggregates

1. Land the raw data unchanged

Whatever arrives from a vendor, an internal system or an API, store a copy exactly as received, with the file name and load timestamp, before any transformation. When a number looks wrong three weeks later, the first question is always “was it wrong in the source or did we break it?” An untouched raw layer answers that in minutes. Storage is cheap; reconstructing a vendor file that was overwritten is not.

2. Validate at the boundary, not in the report

The cheapest place to catch a bad file is the moment it lands. A few checks cover most real failures:

• Volume: is today’s row count within a sensible range of yesterday’s? A file with 40% fewer rows is usually truncated, not a quiet day.
• Completeness: are key columns such as identifiers, dates and prices populated?
• Uniqueness: is there exactly one row per security per date?
• Reconciliation: do totals match a control figure from the source?

Write them as code so they run every time. In SQL, a uniqueness check is a few lines:

-- Any security with more than one price for the same date?
SELECT price_date, security_id, COUNT(*) AS n
FROM   raw.daily_prices
WHERE  load_id = :latest_load
GROUP  BY price_date, security_id
HAVING COUNT(*) > 1;

If the query returns rows, the load stops and someone gets told, before anything downstream picks up the duplicates. Snowflake now has built-in support for this pattern through data metric functions, described in its data quality documentation, but plain SQL or a short Python script works anywhere.

3. Load incrementally

Reloading five years of history every night because one day changed wastes time and money, and makes failures more likely. Load only what is new or changed. The SQL MERGE statement handles this in one step: update rows that already exist, insert the ones that do not (Snowflake MERGE reference).

MERGE INTO core.daily_prices AS t
USING (SELECT * FROM raw.daily_prices WHERE load_id = :latest_load) AS s
  ON  t.security_id = s.security_id
  AND t.price_date  = s.price_date
WHEN MATCHED AND t.close_price <> s.close_price THEN
  UPDATE SET close_price = s.close_price, updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
  INSERT (security_id, price_date, close_price, updated_at)
  VALUES (s.security_id, s.price_date, s.close_price, CURRENT_TIMESTAMP());

The AND t.close_price <> s.close_price condition matters: it means a restated price is captured with a new timestamp, while unchanged rows are left alone. That gives you an audit trail of corrections for free.

4. Model the data for how it is queried

Large analytical tables are read far more often than they are written, and most queries touch a few columns across many rows. Column-oriented storage suits that pattern. Apache Parquet, the common open file format, is column-oriented by design, and Snowflake stores table data in compressed micro-partitions organised by column, each holding 50 to 500 MB of uncompressed data, with metadata that lets the engine skip partitions a query does not need (Snowflake micro-partitions).

The practical lesson is to filter early on the columns the data is naturally ordered by, which in finance is nearly always a date. A query restricted to one month of a ten-year table can skip most of the data; the same query with the date filter buried inside a function may have to scan all of it.

5. Keep history on purpose

“What did this report show on the third of the month?” is a reasonable question from an auditor or a portfolio manager, and “we overwrote it” is a bad answer. There are two layers of protection. Platform features such as Snowflake’s Time Travel let you query a table as it was at an earlier point, though the standard retention is only one day unless configured longer (Snowflake Time Travel). For anything that must be reproducible months later, such as month-end figures, store an explicit snapshot with an as-of date.

6. Push work to the database, serve aggregates

A common anti-pattern is pulling millions of raw rows into pandas or Excel to compute a handful of totals. Do the grouping and joining in SQL, where the engine is built for it, and bring back only the summary the report needs. Python is still the right place for logic SQL handles badly, such as complex validation rules, calling an API or producing a chart, but it should receive thousands of rows, not millions.

The same thinking applies to dashboards. Point Power BI at a curated, aggregated table rather than the raw fact table, and it becomes faster, cheaper and harder to misread.

Ownership beats tooling

Every one of these steps depends on someone knowing what each table means and who to call when it breaks. A short data dictionary, a named owner per source, and a log of known issues do more for data quality than any new platform. The tools above make good habits cheaper; they do not replace them.