Snowflake: architecture, daily work and platform translation

Snowflake separates storage, compute and cloud services. A virtual warehouse provides compute for supported operations; it is not a folder containing your tables. Databases and schemas organise objects. Different workloads can use separate warehouses to manage concurrency and costs. Account, region and cloud choices remain relevant to connectivity and governance.

Use fully qualified names in shared evidence. A warehouse may need to resume before a query runs, with associated compute costs. Ask for the approved warehouse and timeout settings.

SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(),
       CURRENT_DATABASE(), CURRENT_SCHEMA();

SHOW TABLES;

-- Inspect an approved table:
-- DESCRIBE TABLE approved_database.approved_schema.approved_table;

S3 files can be loaded through an external stage and COPY INTO after the required integration, format and permissions are configured. Snowpipe and other ingestion options serve different latency requirements. Do not choose a continuously running pattern for a daily batch without a reason.

Streams track table changes through an offset, rather than being a permanent historical archive of every business event. Tasks can schedule processing. The design must account for consumption, retention and staleness. An event log or explicit history model is still needed when the business requires reproducible historical states.

An infrastructure move and an analytics-platform move can overlap, but they are not the same scope. Snowflake can itself run on AWS.

• HDFS files become a storage-design decision, possibly S3 plus Snowflake-managed tables.
• Hive/Impala SQL becomes reviewed Snowflake SQL, with dialect and behavioural tests.
• Hive metastore entries require target catalogue and schema mapping.
• Existing security policies need equivalent, tested access in the new platform.
• Schedulers and scripts require explicit ownership in the target orchestration.
• Existing history needs deliberate preservation, not merely a latest-state load.

In standard Snowflake tables, do not rely on declared primary, unique or foreign keys to reject bad data. Their enforcement differs from hybrid tables; verify the current constraint documentation for the exact table type. Implement explicit quality tests.

A MERGE with several source rows matching one target row can be nondeterministic or fail. Deduplicate according to an approved ordering rule, or reject ambiguous input. A source duplicate with no target match can also cause multiple inserts. Never assume MERGE automatically implements Slowly Changing Dimension Type 2 history: closing the old version and creating the new one needs an explicit, tested design.

Use the query profile to distinguish scanning, joins, spills and queueing. Filter early where semantically valid, avoid unnecessary columns, and check join cardinality. Increasing warehouse size is a cost decision, not a universal fix. Evaluate clustering only with measured workload evidence.