How to Get Historical Stock Prices into Snowflake (3 Ways)

select
    code,
    max_by(adjusted_close, date) / min_by(adjusted_close, date) - 1 as total_return_10y
from security_price_history_vw
where code in ('AAPL', 'VTI', 'VFIAX')
  and exchange_code = 'US'
  and date between '2016-01-01' and '2025-12-31'
group by code;

create table if not exists daily_prices (
    code            varchar,
    date            date,
    open            number(18, 6),
    high            number(18, 6),
    low             number(18, 6),
    close           number(18, 6),
    adjusted_close  number(18, 6),
    volume          number(38, 0)
);

create file format if not exists prices_csv
    type = csv
    skip_header = 1;

create stage if not exists prices_stage
    url = 's3://your-bucket/prices/'
    storage_integration = your_s3_integration
    file_format = prices_csv;

copy into daily_prices
from @prices_stage
pattern = '.*[.]csv';

create pipe if not exists prices_pipe
    auto_ingest = true
as
copy into daily_prices
from @prices_stage;

put file:///path/to/prices.csv @%daily_prices;

copy into daily_prices
from @%daily_prices
file_format = (format_name = 'prices_csv');

merge into daily_prices t
using daily_prices_staging s
    on t.code = s.code and t.date = s.date
when matched then update set
    open = s.open, high = s.high, low = s.low, close = s.close,
    adjusted_close = s.adjusted_close, volume = s.volume
when not matched then insert (code, date, open, high, low, close, adjusted_close, volume)
    values (s.code, s.date, s.open, s.high, s.low, s.close, s.adjusted_close, s.volume);

import os

import snowflake.connector
from snowflake.connector.pandas_tools import write_pandas

conn = snowflake.connector.connect(
    account=os.environ["SNOWFLAKE_ACCOUNT"],
    user=os.environ["SNOWFLAKE_USER"],
    password=os.environ["SNOWFLAKE_PASSWORD"],
    warehouse="LOAD_WH",
    database="MARKET_DATA",
    schema="RAW",
)

# fetch_prices() is your vendor's client: return one row per ticker per day
# with columns CODE, DATE, OPEN, HIGH, LOW, CLOSE, ADJUSTED_CLOSE, VOLUME.
df = fetch_prices(tickers=["AAPL", "VTI", "VFIAX"], start="2000-01-01")

write_pandas(conn, df, "DAILY_PRICES_STAGING", auto_create_table=True, overwrite=True)

select code, date, count(*) as rows_per_day
from daily_prices
group by code, date
having count(*) > 1;

select code, max(date) as latest_date
from daily_prices
group by code
having max(date) < dateadd('day', -5, current_date())
order by latest_date;