Snowflake/DBT

What is Snowflake and what problem does it solve?

A cloud-native data warehouse SaaS that does OLAP while solving scalability, concurrency, and maintenance headaches. Separates storage and compute so you can scale each independently.

How does Snowflake differ from traditional on-site data warehouses?

No hardware to manage, zero maintenance, auto-scaling, separate storage/compute pricing, and built-in features like Time Travel and Zero-Copy Cloning.

What makes Snowflake a "SaaS" platform?

Fully managed—no infrastructure setup, patching, tuning, or backup management. You just load data and run queries.

What are Snowflake's three main architectural layers?

• Storage: Columnar micro-partitions in cloud object storage (S3/Azure/GCS).

• Compute: Virtual Warehouses—isolated clusters that execute queries.

• Cloud Services: Metadata, security, optimization, and transaction management.

Why is separating storage and compute important?

Scale each independently. Multiple warehouses query the same data without interfering. Pay separately for storage vs. compute usage.

What is a Database in Snowflake?

A logical container for schemas, tables, views, and other objects. The top-level organizational unit.

What is a Schema?

A namespace within a database. Contains tables, views, UDFs, stages, and other objects. Organizes data logically.

What are Stages in Snowflake?

Named storage locations (internal or external cloud storage) where you stage data files before loading with COPY INTO. Internal stages include User, table, and named stages and external stages are external source. (S3, GCP, Azure)

What are File Formats in Snowflake?

Variable that define our file structure (CSV, JSON, Parquet, Avro, ORC, XML) for loading/unloading. Reusable across multiple COPY INTO operations.

What is a Table in Snowflake?

Physical storage of data in columnar micro-partitions. Supports standard DML (INSERT, UPDATE, DELETE, MERGE).

What is a View and how is it different from a Table?

A View stores only the SQL query definition—no physical data. Runs fresh each time you query it.

What is a Materialized View and when would you use it?

Stores complex query results so we can avoid repeating them. Also auto-refreshes on base table changes (incrementally). Use for fast lookups on frequently aggregated data—costs storage.

What is a Secure View and what's the trade-off?

Hides view definition and table structure from users. Trade-off: slower performance because it cant be optimized by query optimizer

What is a Temporary Table?

Exists only for the session. Drops automatically on logout. Great for work that is like a draft to develop without messing with other tables.

What is a Transient Table?

Persists beyond session but can be dropped anytime. Skips Fail-Safe (7-day recovery), so cheaper than permanent tables.

What is Zero-Copy Cloning?

CREATE TABLE clone CLONE original; copies metadata only. Both tables share micro-partitions until changes occur. New partitions written only for modified rows—saves storage.

What is CREATE OR REPLACE vs. CREATE IF NOT EXISTS on Objects?

• CREATE OR REPLACE: Drops and recreates. Grants are lost.

• CREATE IF NOT EXISTS: No-op if exists. Grants are preserved.

What is the COPY INTO command?

Bulk loads data from a stage into a table. Supports file formats, validation, and error handling. Can load multiple files in parallel.

What file formats does Snowflake support for loading?

CSV, JSON, Avro, Parquet, ORC, XML. Also supports compressed formats (gzip, snappy, zstd).

What are the best practices for bulk data loading?

• Compress files to 100-250 MB (gzip/snappy).

• Use COPY INTO with VALIDATION_MODE to test before loading.

• Use PURGE = TRUE to auto-delete staged files after load.

• Use ON_ERROR = 'CONTINUE' or 'SKIP_FILE' for error handling.

• use Force to force the COPY command to load all files regardless of whether the load status is known

How does Snowflake handle semi-structured data like JSON?

Uses the VARIANT data type which is used to represent semi-structure data. Can store JSON, Avro, Parquet natively. We also have Array’s and Object’s (pairs of key: values). Query with dot notation (json_col:key) or FLATTEN to explode arrays.

What is micro-partitioning and how does pruning work?

Tables auto-split into 50-200 MB chunks. Each chunk stores min/max metadata. WHERE filters skip chunks that don't match—pruning reduces scan time.

What is the Query Result Cache?

Results cached for 24 hours. Re-running the same SQL returns cached results instantly if underlying data hasn't changed. Shared across sessions.

What is EXPLAIN and how do you use it?

EXPLAIN <query> or EXPLAIN USING TABULAR <query> shows the execution plan (DAG of operators) without running the query. Used for performance debugging.

How does Time Travel work?

Query historical data with AT (OFFSET => -300) or BEFORE (TIMESTAMP => '...'). Reads from persistent undo logs (1-90 days). Doesn't block current data.

What is a UDF (User-Defined Function) in Snowflake?

Custom function written in SQL, JavaScript, or Java.

• Scalar: Returns single value per row.

• Table: Returns multiple rows/columns. Called with TABLE() in FROM.

• SQL UDFs are inlined (fast); JavaScript runs row-by-row (slower).

What's the syntax for creating a table in Snowflake?

CREATE TABLE my_table (
    id INT,
    name STRING,
    created_at TIMESTAMP
);

What is SnowSQL?

Snowflake's command-line client. Used to run queries, load data, and manage objects from terminal.

What is Snowpipe?

Serverless, continuous data ingestion service. Automatically loads new files as they land in external stages—near-real-time (1-2 minutes). No warehouse needed.

How does Snowpipe differ from bulk loading with COPY INTO?

• Bulk COPY INTO: Manual execution. Requires running warehouse. Batch loads.

• Snowpipe: Automated. Serverless. Triggers on file arrival. Near-real-time. Skips duplicate loaded files.

What is a Stream in Snowflake?

Tracks our DML changes to a table. Records inserts, updates, and deletes since the stream was last consumed. Uses offsets—doesn't trigger execution itself.

What is a Task in Snowflake?

Scheduled job that executes SQL code, or a stored precedure. Runs on a cron-based schedule or can be triggered manually. Provides the execution trigger.

How do Streams and Tasks work together?

Task checks if Stream has new data (SYSTEM$STREAM_HAS_DATA). If true, Task executes SQL to process the new data. Builds automated, incremental pipelines.

What is dBT (Data Build Tool)?

Open-source transformation tool that applies software engineering best practices (version control, testing, CI/CD) to SQL analytics. Transforms data in-warehouse using SELECT statements. You write a SELECT statement defining your transformation. dBT compiles it into a table or view in your warehouse. No manual DDL required.

What are the benefits of using dBT?

• Version control for SQL.

• Automated testing (not null, unique, custom).

• Documentation generation.

• Dependency management (ref() function).

• Reusable code via macros.

What is a Model in dBT?

A .sql file containing a SELECT statement. Defines a transformation. dBT converts it to a table or view in the warehouse.

What is a Seed in dBT?

Static .csv files loaded into the warehouse. Used for small reference data (country codes, lookup tables). Loaded with dbt seed.

What is a Snapshot in dBT?

Captures the state of a mutable table over time.

What are Tests in dBT?

SQL assertions that validate data quality. Types: not_null, unique, accepted_values, relationships. Custom tests can also be written.

Use cases for DBT?

• Staging layer transformations (cleaning raw data).

• Core dimension/fact table generation.

• Data quality testing and monitoring.

• Incremental models for large datasets.

• When building ELT pipelines in Snowflake.

• For Medallion Architecture implementation (Bronze → Silver → Gold).

What is a Staging Model in dBT?

First transformation layer that cleans and prepares raw data. Typically maps to a single source table. Performs renaming, casting, and simple cleansing.

What is an Intermediate Model?

Sits between staging and marts. Performs joins, unions, or complex transformations. Not necessarily business-facing.

What is a Mart Model?

Business-facing models with aggregated, denormalized data. Serves dashboards and reports. Typically in the Gold layer.

What is an Incremental Model?

Processes only new or changed rows—not the entire table. Uses incremental materialization. Filters using is_incremental() macro.

What is a Source in dBT?

Defines raw data tables in the warehouse. Declared in models/sources.yml. Allows us to see the lineage graph.

Why use source() instead of hardcoding table names?

• Enables documentation and lineage tracking.

• Allows freshness tests on source tables.

• Centralizes source definitions.

• Makes testing and swapping easier.

What is the ref() function in dBT?

References another model. Creates a Directed Acyclic Graph (DAG) of dependencies. Ensures models execute in the correct order.

What is a DAG in dBT?

Directed Acyclic Graph—visual representation of model dependencies. Built automatically from ref() relationships. dBT executes in topological order.

What is the difference between ref() and source()?

• source(): References raw tables in the data warehouse (external).

• ref(): References other models (internal transformations).

What is Kafka?

Kafka is a distributed event streaming platform that collects, stores, and delivers real time data between applications. It is designed to handle large amount of streaming data quickly and reliably.

Kafka use cases?

Some kind of real-time data streams. EX. Netflix show reccomendations in real time. Uber uses Kafka to gather user, taxi, and trip data in real-time to compute and forecast demand, and computesurge pricing. LinkedIn uses Kafka to prevent spam and collect user interactions to make better connection recommendationsin real time​.​

what is a kafka topic?

Where data (events) are stored. They are written in by a producer, and stored on partitions. The topic is then read from by a consumer.

Like a table in a database, can have many of them.

Kafka Topics are immutable: once data is written to them, it cannot be changed.​

Topics contain partitions.​

What is a data stream within a topic?

The ordered data (events) that will be written to a partition or read from a partition.

multiple producers can send data to the same topic, and multiple consumers can read data from that topic at the same time.

Kafka Partitions?

Topics are split into partitions, messages within each partition are ordered​. Each message gets an incremental ID, called its offset. Order is guaranteed only within a partition (not across partitions)​. Data is assigned randomly to a partition unless a key is provided. Partitions allow us to speed up our processing by doing it in parallel.​

Kafka producers

• Producers write data (events) to topics

• Producers know which partition to write to (and which Kafka broker has it)​

• In case of Kafka broker failures, Producers will automatically recover​​

Producer Message Keys

• Producers can choose to send a message key (string, number, binary, ect.)​

• If key = null, data is sent round robin (partition 0, then partition 1, etc.)​

• If key != null, then all messages for that key will always go to the same partition (uses hashing strategy)​

• A key is typically sent if you need message ordering for a specific field (example: truck_id)

What is a Kafka Broker?

A server for our partitions. A Kafka cluster is composed of multiple brokers (servers). Each broker contains certain topic partitions.

what is kafka message serialization?

Message serialization means transforming objects/Data into bytes

kafka consumer?

• Consumers read data from a topic (identified by name) – pull model

• Before reading, it will serialize the bytes into a datatype it can understand, like an int or a string​

• Consumers automatically know which broker to read from​

• In case of broker failures, consumers know how to recover​

Message Deserialization?

When reading from a topic we transform bytes into objects/data

Consumer Groups

Consumer groups are consumers that belong to the same application. All consumers in an application read data as a consumer group. Each consumer in a consumer group reads partitions exclusively, so that each partition is read by only one consumer per group. Can have multiple groups on one topic.

Consumer Offset

When a consumer in a group has processed data received from Kafka, it should periodically commit the offsets (Like a bookmark). By default, Java consumers will automatically commit offsets (at least once).

Kafka Broker Discovery

You only need to connect to one (any) broker, and that broker will send back to the client the metadata of allother brokers, topics, and partitions. Every Kafka broker is also called a ‘bootstrap server’​.

Topic Replication Factor

Even if a broker is down, we can still serve data through another broker. So a factor of 3 means we have 3 brokers with replicas of our topic.

Partition Leader?

Producers can only send data to the broker that is leader of a partition​. At any time only ONE broker can be a leader for a given partition​. The other brokers will replicate the data​. Therefore, each partition has one leader and multiple ISR (in-sync-replica)​

Kafka Cluster?

Our broker manager. Selects leader brokers. Will tell kafka if brokers fail. We need more than 1 broker for scalibility and reliability.

Kafka Broker?

Acts as a server where our topic partitions are stored. Handle producer/consumer requests and dictates where our data goes.

ZooKeeper is primarily used on older versions of Kafka. ZooKeeper shows scaling issues when there are a very large number of partitions. For security, Kafka clients should be connected to brokers, not ZooKeeper. KRaft (Kafka Raft metadata mode) replaces ZooKeeper by moving metadata management into Kafka itself, improving scalability, stability, and security.