Question 1 : You have joined a company where all operational data is stored in PostgreSQL. Business stakeholders complain that reports are taking 30–40 minutes to generate. Why can't we directly use PostgreSQL (or any OLTP database) as a data warehouse?
Explain from the perspectives of:
Database design
Workload
Concurrency
Storage
Indexing
Transactions
Query optimization
Scalability
Answer: A large database will have billions of rows per schema. For PostgresSQL like DBs these are very slow: because they are row wise storage, they read and write row by row, and slow in case of large no of columns.
So for large DBs snowflake or redshift is preferable : these are columnar storage : projection pushdown : only reads the selected columns, these have distributed compute nodes, before select these will apply predicate pushdown (filters where , having).
Redshift has a distribution key : so that for a particular column taken as a dist key : will be storing the same values in the same node or distributing rows based on values in the column : like Name of city.
Snowflake has an in-built distribution feature.Snowflake hides all storage distribution from the user. Everything is automatic.
You never choose:
shard
node
partition
distribution
Workload Difference :
OLTP : INSERT, UPDATE, DELETE, Single-row lookup Primary Key Search
OLAP : Aggregation, JOIN, GROUP BY, Window Functions, Historical Analysis
Different workloads require different storage and execution strategies.
Concurrency : Large analytical queries consume CPU and memory for a long time.
If they run on PostgreSQL,they block business transactions from achieving optimal performance.
Snowflake separates compute from storage, allowing independent scaling of compute resources.
Normalization : OLTP { Highly normalized → avoid redundancy }
OLAP { Denormalized → fewer joins }
Indexes : PostgreSQL → B-tree, Hash, GIN, GiST
Snowflake → No traditional indexes, Uses Micro-partitions, Metadata Pruning, Column statistics
Storage Architecture :
PostgreSQL → Heap Pages, Indexes, WAL
Snowflake → Compressed Column Files, Immutable Micro-partitions, Cloud Storage
"PostgreSQL is an OLTP database optimized for high-frequency transactional workloads, whereas Snowflake is an OLAP platform designed for analytical processing.
Although PostgreSQL can execute analytical queries, its storage format, indexing strategy, concurrency model, and scaling characteristics make it less suitable for large-scale reporting and analytics."
1. What is Snowflake, and how does it differ from traditional data warehouses?
Snowflake is a cloud-based Data Warehouse-as-a-Service (DWaaS) that separates compute and storage, enabling independent scaling. Unlike traditional warehouses (like Oracle, Teradata), Snowflake:
Runs entirely on cloud (AWS, Azure, GCP).
Uses a multi-cluster shared data architecture.
Supports zero-copy cloning and time travel.
Automatically handles maintenance (no DBA overhead).
2. Explain Snowflake’s architecture.
Snowflake has three layers:
Storage Layer – Stores data in micro-partitions (columnar format).
Compute Layer – Virtual warehouses (clusters) process queries independently.
Cloud Services Layer – Manages metadata, security, and query optimization.
3. What are Virtual Warehouses in Snowflake?
Virtual Warehouses (VWs) are compute clusters that execute queries. Key features:
Can be scaled up/down or auto-suspended.
Multiple warehouses can run concurrently without contention.
Support multi-clustering for parallel processing.
4. How does Snowflake handle concurrency?
Snowflake uses:
Multi-cluster warehouses to serve multiple users.
Query queuing when all clusters are busy.
Resource monitors to avoid runaway queries.
5. What is Time Travel in Snowflake?
Time Travel allows accessing historical data within a retention period (1-90 days):
SELECT * FROM TABLE AT(TIMESTAMP => '2023-01-01 12:00:00'::TIMESTAMP);
Supports UNDROP for accidental deletions.
6. Explain Zero-Copy Cloning in Snowflake.
Creates a copy of a table/database without duplicating storage:
CREATE TABLE new_table CLONE original_table;
Uses metadata pointers (no extra storage cost).
Changes in clones don’t affect the original.
7. What are Snowflake stages?
Stages are storage locations for loading/unloading data:
Internal Stages (Snowflake-managed).
External Stages (S3, Azure Blob, GCS).
Example:
COPY INTO table FROM @stage/file.csv;
8. How do you optimize query performance in Snowflake?
Use clustering keys for large tables.
Scale up warehouse size for complex queries.
Materialized views for frequent queries.
Query profiling to identify bottlenecks.
9. What are Snowflake’s security features?
RBAC (Role-Based Access Control).
End-to-end encryption (in transit & at rest).
Network policies (IP whitelisting).
Data masking (dynamic/static).
10. How does Snowflake support semi-structured data?
Snowflake natively supports JSON, XML, Avro, Parquet:
SELECT raw_data:customer.name FROM json_table;
Uses VARIANT data type.
Optimized with automatic schema detection.
11. What is Snowflake’s data sharing feature?
Enables secure sharing without copying data:
Provider shares a database.
Consumer accesses it in real-time (no ETL).
Supports cross-cloud/region sharing.
12. How do you load data into Snowflake?
Bulk Load: COPY INTO from stages.
Snowpipe: Continuous loading (serverless).
ETL Tools: Informatica, Talend, Matillion.
13. Explain Snowflake’s caching mechanism.
Three cache layers:
Result Cache – Stores query results (24h).
Metadata Cache – Statistics for optimization.
Virtual Warehouse Cache – Local disk cache.
14. What is a Snowflake task?
A scheduled SQL operation:
CREATE TASK my_task
WAREHOUSE = my_wh
SCHEDULE = '5 MINUTE'
AS
INSERT INTO table SELECT * FROM stream;
Used with streams for CDC pipelines.
15. How do you monitor Snowflake costs?
Resource Monitors – Set budget limits.
ACCOUNT_USAGE schema – Track storage/compute usage.
Warehouse utilization via QUERY_HISTORY.
16. What are Snowflake streams?
CDC (Change Data Capture) objects that track DML changes:
CREATE STREAM my_stream ON TABLE my_table;
Used with tasks for real-time pipelines.
17. How do you handle large-scale data migrations to Snowflake?
Bulk load via stages for initial load.
Incremental loads using Snowpipe/streams.
Validation scripts to ensure data integrity.
18. What are Snowflake’s limitations?
No row-level locking (optimistic concurrency).
No stored procedures (only JavaScript UDFs).
Cold starts for suspended warehouses.
19. How does Snowflake compare to Redshift/BigQuery?
Feature Snowflake Redshift BigQuery
Compute/Storage Separation Yes No Yes
Auto-scaling Yes Manual Server-less
Pricing Model Per-second Per-hour Pay-per-query
20. Describe a real-time data pipeline in Snowflake.
Ingest data via Snowpipe (S3 → Snowflake).
Track changes using streams.
Process with tasks (merge into target tables).
Analyze with dashboards (Power BI, Tableau).
No comments:
Post a Comment