Cost and correctness.
Everything else
is vocabulary.

Current GCP data engineer postings converge on a short list. Five services appear in nearly every one: BigQuery, Dataflow, Pub/Sub, Cloud Storage and Composer. Dataproc appears in roughly two thirds. Python and SQL are stated as non negotiable. A second tier shows up in senior postings: dbt, Terraform, CI/CD, IAM, and cost optimisation named as an achievement rather than a skill.

Many postings also pair Google Cloud with Databricks or Snowflake or both, which is why this guide covers all three. If a posting mentions only one, read that platform's modules and skim the others, because the comparison questions still come.

One posting put the bar better than any study guide: "Ability to explain real world architecture, scalability, and performance optimization scenarios, not just tool definitions." That is the whole exam.

Where the weight actually sits

Compare that to the official Professional Data Engineer blueprint, which weights its five domains 22 / 25 / 20 / 15 / 18. The certification spreads evenly; real interviews do not. If you are optimising for a job rather than a badge, weight your hours toward cost and correctness.

The three questions underneath everything

1. Where does the data live and in what shape. Partition, cluster, format, open or native.
2. Who is paying and how do you know. Bytes scanned, slot seconds, credits, DBUs, dollars per run.
3. What happens when it is wrong. Idempotency, backfill, replay, late data, dedup.

Every case in module 21 is one of those three wearing a costume.

The five rounds

How to use this guide

• Follow the path. The left nav is ordered. Each module has a read time, retrieval cards and a checkpoint that unlocks once you mark it read.
• Recall mode (press r) blurs the answers so you have to produce them rather than recognise them.
• The retrieval cards are the part that works. Reading a module moves almost nothing. Grading a card honestly schedules the next review, and grading everything Easy just deletes the feature.
• The session timer (press space) is there so practice is timed. Interview answers are length constrained and untimed practice does not teach that.
• Everything is stored in this browser only. Export your progress before clearing site data.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Dense, keyword rich resumes pass the applicant tracking system and then create a problem in the room. Every named feature is a question you have invited. If a resume says liquid clustering, Search Optimization Service, Change Data Feed or Dynamic Tables, any of those can be picked out and probed for five minutes. The ATS rewards breadth; the interview punishes breadth without depth.

The three way audit

The claims that get probed hardest

Separating contribution from outcome

If you have no production experience on one of the platforms

This is common and it is survivable. What is not survivable is pretending. The four sentence answer:

What genuinely transfers, and what does not

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

These are not stories to tell. They are shapes to fill. Write your version of each into a document, once, and then rehearse from that rather than improvising. Improvised answers ramble, bury the number, and explain the org chart.

Rules for all twelve

• Two minutes maximum. Time yourself. Almost everyone runs long and nobody notices while speaking.
• The number lands in the last fifteen seconds. Front loading it wastes it; burying it past two minutes loses it.
• Never explain the org chart. If your story needs three sentences of context to make sense, start at the problem instead.
• One concrete decision with a rejected alternative beats four vague accomplishments.
• Contribution separated from outcome, said by you, before being asked.

The two minute open, which is not one of the twelve

The availability question

If you are between roles you will be asked why. One flat sentence, then stop. "My position was eliminated in a reorganisation." Do not explain, do not editorialise about the former employer, do not fill the silence. Every additional sentence makes it worse. If they follow up, answer plainly and stop again.

Questions to ask them, which are graded

1. "What does the data platform cost, and who watches that number?" Almost nobody asks this and it lands every time, because it signals you treat the bill as an engineering concern.
2. "Where does a metric definition live, and what happens when two teams disagree about one?"
3. "What is the on-call rotation, and what was the last serious incident?"
4. "What would you want the person in this role to have changed in six months?"

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

The conversions worth having automatic

A worked example, said out loud

"100 TB a day is about 3 PB a month.
 Stored in BigQuery active logical at roughly $0.023 per GiB,
 3 PB is about 3,000,000 GiB, so around $70,000 a month,
 falling as partitions age past 90 days into long term.
 If queries scan 5 percent of a day 200 times a day, that is
 1 PB scanned daily, 30 PB a month, which on demand is
 30,000 TiB x $6.25, about $187,500. At that steadiness I would
 reserve: $187,500 divided by ($0.06 x 730) is roughly 4,300 slots."

That is the whole skill. Nothing in it is hard; it is entirely about having done it before so it comes out fluently rather than haltingly.

The cost unit per platform

Defending a percentage

The platform numbers to have cold

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Capacitor is BigQuery's columnar format: each column stored and compressed independently with per block metadata so the engine skips blocks without reading them. If you have read ORC or Parquet footers you have the model. Two consequences to state instantly: you are billed on columns referenced, computed from declared types, and LIMIT does not reduce bytes billed. " + tag("c") + "

Partitioning

Granularity hour, day, month or year. Practical ceiling around 10,000 partitions per table, which is why hourly plus multi year retention does not work. Likely

Clustering, and the rule

Logical versus physical storage billing

Rate card breakeven is 1.74x, not the 2x everyone repeats. The real breakeven is higher because time travel and fail safe are free under logical and billed under physical, so a high churn table carries a fat invisible tail.

SELECT table_name,
  SUM(total_logical_bytes)/POW(1024,4)  AS logical_tib,
  SUM(total_physical_bytes)/POW(1024,4) AS physical_tib,
  SAFE_DIVIDE(SUM(total_logical_bytes), SUM(total_physical_bytes)) AS ratio,
  SUM(time_travel_physical_bytes)/POW(1024,4) AS tt_tib
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_schema = 'your_dataset'
GROUP BY table_name ORDER BY logical_tib DESC

Nested and repeated fields

ARRAY and STRUCT let a one to many relationship live inside the parent row. Nesting wins when the child is always read with the parent, because it removes a shuffle entirely: the join happened at write time. It loses when the child is queried independently, and updating one element rewrites the parent row. The phrase to use is "where the access pattern justified them", which is exactly the right framing.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

The two models

Sizing a reservation, which is what you claim you did

-- average slots consumed per hour, last 30 days
SELECT TIMESTAMP_TRUNC(creation_time, HOUR) hr,
       SUM(total_slot_ms)/(1000*60*60) AS avg_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type='QUERY' AND statement_type != 'SCRIPT'
GROUP BY hr ORDER BY hr;

Baseline at the p50, autoscale max near the p95. Baseline at peak means paying for peak all night. Baseline at zero means every morning starts cold. Commitments are 50 slot minimum in 50 slot increments, one or three year, regional and shareable org wide. The 100 slot minimum you may have read is legacy flat rate, closed to new purchase in July 2023. Certain

The breakeven, out loud

100 slots Enterprise PAYG = 100 x 0.06 x 730 = $4,380 / month
Same money on demand      = $4,380 / $6.25    = 700 TiB / month
On a 3yr Resource CUD at $0.036, the line moves to ~420 TiB.

Then the answer that actually works in practice: run both. Predictable pipelines on the reservation, spiky ad hoc on demand. Projects without a reservation assignment fall back to on demand automatically, so a hybrid needs no extra machinery.

Autoscaling and the billing granularity that changed

Billing is per second with a one minute minimum by default, and you can now opt into BigQuery fluid scaling at the reservation level for per second billing with no minimum. Certain That matters because the classic trap was a four second burst billed as sixty. Autoscaling bills slots allocated, not slots consumed, which makes many short concurrent queries (exactly a BI workload) the worst possible shape.

The eight leaks, and the query that finds them

SELECT DATE(creation_time) d, user_email,
  COUNT(*) jobs,
  SUM(total_bytes_billed)/POW(1024,4)      AS tib,
  SUM(total_bytes_billed)/POW(1024,4)*6.25 AS est_usd,
  SUM(total_slot_ms)/1000/3600             AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type='QUERY' AND state='DONE'
GROUP BY d, user_email ORDER BY tib DESC

Group by user_email first. A service account at the top is an automated leak and worth ten times a human at the top.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Current state, so you do not quote something dead

• Runner v2 is the recommended runner, required for multi language pipelines, custom containers and GPUs. Certain
• Dataflow Prime is GA and Google managed templates default to it. Adds vertical autoscaling, right fitting via Beam resource hints, dynamic thread scaling. Certain
• Streaming Engine is default for Python and Go streaming: state and shuffle move off worker VMs into the service backend. Certain
• Exactly once is the default streaming mode. At least once is opt in and forces resource based billing. Certain
• Dataflow SQL is dead, shut down January 2025. Do not mention it. Certain

The four knobs, and the one that produces the bug

Operating it

Drain stops reading, closes windows and lets in flight data finish, so use it for a code change. Cancel stops immediately and loses in flight data, so use it only for a runaway. Update does in place replacement with a compatibility check on the fused graph.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Delivery semantics, stated exactly

Pricing trap: throughput is $40 per TiB with the first 10 GiB free, but export subscriptions are $50 per TiB with no free tier. Removing Dataflow to save money can raise the bill. Certain

The four mechanisms worth claiming

Pub/Sub versus Kafka, which you ran both of

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Dataproc, and the claim to be able to defend

The right answer is ephemeral job scoped clusters with autoscaling policies, initialization actions and preemptible workers, keeping long lived clusters only where interactive exploration justifies them. The reason is billing: you pay for cluster uptime, not for the cluster's existence as a concept. The blueprint asks the persistent versus ephemeral question explicitly.

Composer, and why the Airflow bridge is your strongest

Managed Airflow is Airflow. Same software. Your DAGs, operators, hooks, sensors, XComs, pools and SLA callbacks port with no translation. What differs is the environment model, the fact that generation upgrades are side by side rather than in place, and that Google auto upgrades infrastructure components but not your Airflow version.

The four orchestrators and their boundaries

If a resume says Cloud Workflows and Eventarc triggering Cloud Functions and Dataflow on arrival rather than on a clock. That sentence is the senior answer: not everything belongs in Airflow, and knowing why is the point.

Idempotency, which is what the question is actually about

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

How it actually works, in one paragraph

A Delta table is Parquet data files plus a _delta_log directory of ordered JSON commits, checkpointed to Parquet every ten commits. Each commit lists files added and removed. A reader resolves the current version by replaying the log, so "delete a row" means write a new file without it and commit a remove. That single design gives you ACID, time travel, and the fact that a delete is a rewrite, not an edit, which is the source of most Delta cost questions.

The maintenance trio, and what each is for

MERGE, which is the operation everything else leans on

MERGE INTO gold.orders t
USING (
  SELECT * EXCEPT(rn) FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY source_seq DESC) rn
    FROM silver.orders_delta)
  WHERE rn = 1                       -- dedupe the source FIRST, always
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Two things to say unprompted. One: the source must be deduplicated, because MERGE errors when more than one source row matches a target row and every CDC batch has that. Two: MERGE rewrites every file containing a matched row, so a MERGE that touches every partition rewrites the table. That is why the join predicate should include a partition or clustering predicate where possible, so the rewrite is bounded.

Schema enforcement versus evolution

• Enforcement is the default: a write with a mismatched schema fails. That is a feature, and it is why Delta is safer than raw Parquet.
• Evolution is opt in via mergeSchema or autoMerge. Adds columns, and can widen types. It does not silently reconcile an incompatible retype.
• The change no check catches is a column that keeps its type and changes meaning. Only a distribution check finds that.

Change Data Feed

delta.enableChangeDataFeed = true makes the table emit row level changes with _change_type of insert, update_preimage, update_postimage or delete, readable with readChangeFeed. The use that matters is downstream propagation: it turns a gold table into a source for the next hop without a full rescan. Cost: extra data written per commit, so enable it where something actually consumes it.

The medallion, and the honest version

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

The three level namespace, which is the whole model

catalog.schema.table, with the metastore above the catalog and shared across workspaces in a region. That last part is the point: before Unity Catalog, permissions were per workspace and drifted. Unity Catalog puts identity and grants above the workspace so one policy covers everything.

Fine grained access

System tables, which almost nobody mentions

system.access.audit, system.billing.usage, system.query.history, system.lakeflow and the lineage tables. This is the Databricks equivalent of INFORMATION_SCHEMA.JOBS and it is where cost attribution actually comes from. If you are asked how you tracked Databricks spend per team, the answer is system.billing.usage joined to cluster tags, and cluster policies are what force the tags to exist. Cluster policies enforcing tags, runtime version and maximum spend are what make this work, so the two connect directly.

SELECT usage_metadata.cluster_id,
       custom_tags.team,
       SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY 1, 2 ORDER BY dbus DESC;

Delta Sharing

An open protocol for sharing live Delta tables across organisations without copying: the provider shares, the recipient reads current data through a short lived credential. Works with non Databricks recipients too. Compare it to Snowflake Secure Data Sharing, which does the same job inside Snowflake's own boundary and is tighter within it. That comparison is a good answer because it shows you know both.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Auto Loader

cloudFiles, a Structured Streaming source that incrementally discovers new files in cloud storage. Two discovery modes and the difference is a real question:

Schema inference plus cloudFiles.schemaLocation gives evolution, and a rescued data column captures fields that did not fit the schema instead of failing or dropping them. That rescued column is the detail that shows hands on use: it is how you avoid choosing between a failed load and silent data loss.

Delta Live Tables

Declarative pipelines: you define the tables and the dependencies are inferred from the queries. What it gives you beyond a notebook:

• Expectations, which are the quality gates: EXPECT (log and pass), EXPECT ... ON VIOLATION DROP ROW, and EXPECT ... ON VIOLATION FAIL UPDATE. That three way choice is the same tiering argument as blocking versus alerting checks, and it is how quality becomes a gate rather than a dashboard.
• Streaming tables versus materialized views. A streaming table processes each input row once and is append oriented; a materialized view recomputes a result and can handle arbitrary changes upstream. Choosing wrong is a correctness bug, not a performance one.
• APPLY CHANGES INTO, which handles CDC ordering and out of order records for you, including SCD Type 1 and Type 2.
• Automatic dependency resolution, retries, and lineage in the pipeline graph.

Cluster types, which decides half the bill

Photon, and what 'evaluated against cost per job' should mean

Photon is a vectorized C++ execution engine. It carries a DBU multiplier, so it must earn its premium per workload. Directionally: Likely

• Helps most on scan heavy SQL, aggregations, joins, and Delta MERGE and writes.
• Helps least on Python UDF heavy work, RDD operations, and anything not expressible in the vectorized operators, because it falls back.
• The measurement: run the same job with and without, and compare DBU consumed times rate, plus VM hours, not wall clock. A job that finishes twice as fast at more than twice the rate has cost you money.

That last sentence is exactly what "evaluated against cost per job" means, and it is the answer to give.

Asset Bundles

Databricks Asset Bundles define jobs, pipelines, clusters and permissions as YAML in the repo, deployed through CI. The point is that they go into the same CI/CD path as the Dataflow and Dataproc jobs: one deployment discipline across three engines rather than three ways to ship.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Micro-partitions

Snowflake automatically divides a table into micro-partitions of roughly 50 to 500 MB of uncompressed data, stored columnar and compressed, immutable once written. Certain For each micro-partition it keeps metadata: the range of values per column, the number of distinct values, and null counts. A query with a predicate reads that metadata and skips any micro-partition whose range cannot satisfy it. That is pruning, and it is the entire performance story.

Natural clustering and clustering keys

Data arriving in a natural order is already well clustered on that column, which is why most tables need no clustering key at all. A clustering key is worth defining when: the table is large (multi terabyte), queries filter or join on a column that is not the natural load order, and SYSTEM$CLUSTERING_INFORMATION shows significant overlap.

SELECT SYSTEM$CLUSTERING_INFORMATION('sales.fact_orders', '(order_date, region)');
-- read average_overlaps and average_depth.
-- high depth means many micro-partitions share a value range, so pruning is poor.

Table types, which controls your storage bill

The lever is permanent, transient and temporary table classification controlling Time Travel and Fail-safe storage cost, and here is the number behind it: a permanent table carries up to 90 days of Time Travel plus 7 days of Fail-safe you cannot turn off or query. On a high churn staging table that tail can be several times the table itself. Making staging tables transient is one of the highest value single changes in a Snowflake estate.

Reading the Query Profile

Query Profile analysis isolating full table scans, spill to disk and exploding joins is a common claim. Be able to name what you look at:

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Warehouse sizing, and the counterintuitive part

The two axes people conflate

Scaling policy STANDARD starts another cluster as soon as a query queues, favouring latency. ECONOMY waits until there is enough backlog to keep a new cluster busy for several minutes, favouring cost. Naming that difference is a good, precise answer.

Auto-suspend, auto-resume and the minimum

Billing is per second with a 60 second minimum each time a warehouse resumes. Certain That produces the classic mistake: AUTO_SUSPEND = 60 on a warehouse queried every two minutes means you pay the 60 second minimum on every resume, repeatedly. For bursty interactive use a longer auto-suspend is often cheaper than a shorter one, which is the opposite of the instinct.

Resource monitors, which are the real control

CREATE RESOURCE MONITOR rm_bi WITH
  CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 75  PERCENT DO NOTIFY
    ON 90  PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;

SUSPEND lets running queries finish; SUSPEND_IMMEDIATE kills them. Monitors attach to an account or to specific warehouses. This is Snowflake's answer to the same question as BigQuery custom quotas, and the same distinction applies: NOTIFY informs, SUSPEND stops.

Caching, three layers

The metadata cache is a nice detail to drop: SELECT COUNT(*) on a Snowflake table can return with the warehouse suspended, because it is answered from metadata.

Search Optimization Service

A background maintenance service building a search access path for selective point lookups on high cardinality columns, plus substring and VARIANT search. It costs both storage and compute to maintain. The boundary: clustering suits range scans, SOS suits needle in a haystack equality lookups. If a query filters on a high cardinality id and returns a handful of rows out of billions, SOS. If it filters a date range, clustering.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Snowpipe

Continuous micro batch loading from an external stage, triggered by cloud storage event notifications (auto ingest) or by a REST call. For Snowpipe auto-ingest from GCS external stages the mechanism is a GCS notification into Pub/Sub, and the pipe consuming it.

And note Snowpipe Streaming is a different thing: a rowset API writing rows directly with lower latency and no file staging at all, which is the closer analogue to BigQuery's Storage Write API.

Streams, which are the part most people describe wrongly

A Stream is not a copy of the changes. It is an offset: a bookmark on the table plus the metadata to compute what changed since that bookmark. Querying it shows the change set; consuming it inside a DML statement advances the offset. That is why reading a stream twice without a DML gives the same rows, and why two consumers need two streams.

Columns: METADATA$ACTION, METADATA$ISUPDATE, METADATA$ROW_ID. The ISUPDATE flag is how you tell a genuine delete from the delete half of an update pair, and getting that wrong is a real correctness bug.

Tasks

Scheduled or triggered SQL, arranged in a DAG through AFTER dependencies, with a root task and children. Two compute modes: a user managed warehouse, or serverless where Snowflake sizes it and bills per second. The idiomatic CDC pattern:

CREATE TASK t_merge_orders
  WAREHOUSE = wh_etl
  SCHEDULE  = '2 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('str_orders')   -- do not burn credits on nothing
AS
  MERGE INTO gold.orders t USING str_orders s ON t.id = s.id
  WHEN MATCHED AND s.METADATA$ACTION='DELETE'
                AND s.METADATA$ISUPDATE=FALSE THEN DELETE
  WHEN MATCHED THEN UPDATE SET ...
  WHEN NOT MATCHED AND s.METADATA$ACTION='INSERT' THEN INSERT ...;

Dynamic Tables, and the boundary question

Declarative: you state the query and a TARGET_LAG, and Snowflake works out the refresh, incrementally where it can and by full refresh where it cannot. It replaces the Stream plus Task plus MERGE pattern for most cases.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

RBAC, and the thing that makes it different

Privileges are granted to roles, and roles are granted to other roles, forming a hierarchy where a parent role inherits everything its children have. Users get roles, never privileges directly. The system roles from the top: ORGADMIN, ACCOUNTADMIN, then SYSADMIN and SECURITYADMIN, then USERADMIN, then PUBLIC.

Masking and row access

CREATE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('PII_READER') THEN val
       ELSE REGEXP_REPLACE(val, '.+@', '*****@') END;

ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY mask_email;

CREATE ROW ACCESS POLICY rap_region AS (region STRING) RETURNS BOOLEAN ->
  EXISTS (SELECT 1 FROM sec.role_region m
          WHERE m.role_name = CURRENT_ROLE() AND m.region = region);

ALTER TABLE sales ADD ROW ACCESS POLICY rap_region ON (region);

Both are policy objects attached to columns or tables, so one policy governs many objects and a change applies everywhere at once. That centralisation is the point, and it is why this beats a view per audience.

Time Travel and Fail-safe

SELECT * FROM orders BEFORE (STATEMENT => '01a2b3c4-...');   -- before that bad DML
CREATE TABLE orders_recovered CLONE orders AT (OFFSET => -3600);
UNDROP TABLE orders;

The GDPR consequence to volunteer: a deleted row remains recoverable for Time Travel plus 7 days of Fail-safe, and Fail-safe cannot be shortened or queried. Legal needs to be told that number, and knowing to tell them is the senior part.

Zero-copy cloning

CREATE TABLE ... CLONE creates a new object pointing at the same micro-partitions. Instant, and free until one side changes, at which point only the changed micro-partitions are new storage. Clone a table, a schema or an entire database.

The use that impresses: clone production into a dev database to test a migration against real data with real volume, at no storage cost and no load time. That is a genuinely different capability from anything BigQuery or Databricks offers as cleanly, and it is worth naming as a reason to have Snowflake in the stack.

Secure Data Sharing

A provider creates a Share containing objects and grants it to a consumer account. The consumer sees a read only database over the provider's storage. No copy, no ETL, no staleness, and the consumer pays for their own compute. Across regions or clouds it requires replication, which does copy.

Reader accounts let you share with an organisation that has no Snowflake account, with you paying for their compute. That is the detail that shows you have actually used sharing rather than read about it.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

What dbt actually is

SQL SELECT statements in files, with ref() between them. dbt builds the dependency graph from the ref() calls, works out the execution order, and wraps each model in the right DDL. You never write CREATE TABLE. That is the whole idea and it is why the answer to "what does dbt give you over scheduled queries" is: a graph, a materialization strategy, and tests, in version control.

The four materializations

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}

SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

And know --full-refresh: it rebuilds the model from scratch, ignoring the incremental predicate. It is the escape hatch when the logic changes, and it is why an incremental model still needs to be correct as a full build.

Snapshots, which is how dbt does SCD Type 2

{% snapshot snap_customers %}
{{ config(target_schema='snapshots', unique_key='customer_id',
          strategy='timestamp', updated_at='updated_at') }}
SELECT * FROM {{ source('crm','customers') }}
{% endsnapshot %}

dbt maintains dbt_valid_from and dbt_valid_to for you. Two strategies: timestamp (use a reliable updated_at) and check (compare a list of columns, for sources with no reliable timestamp). Snapshots run against the source, not against a model, because they have to capture state before transformation loses it. That ordering is a good detail to know.

Tests as gates

Severity is the tiering mechanism: severity: error fails the run, severity: warn logs. That maps exactly onto the blocking versus alerting split: uniqueness and referential integrity error; volume and distribution warn. Enforced as deployment gates with CI promotion from development to production means the test run is in the CI job, not in a dashboard.

The rest of the surface, briefly

• Sources with freshness thresholds, so a stale upstream fails before the models run.
• Macros, Jinja functions for repeated logic. The discipline: a macro that hides business logic makes the SQL unreadable, so keep macros structural.
• Seeds, small CSVs in the repo for lookup tables.
• Exposures, declaring downstream dashboards so lineage extends past dbt.
• docs generate, which produces the lineage graph people actually use.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

The live locator

Answer what you know. It narrows a candidate set, it does not decide for you, and saying that out loud is part of the answer.

The consolidation question, which always comes

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Declared grain, which is the whole discipline

Fact types

Slowly changing dimensions

The question that separates people: at scale, is MERGE the right way to maintain Type 2? Often not. MERGE rewrites every partition it touches, and dimension changes spread across all partitions, so a daily MERGE rewrites most of the table. Append only with a current view writes only the changed rows and turns point in time into the same query with one extra predicate:

SELECT * EXCEPT(rn) FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY valid_from DESC) rn
  FROM dim.customer_history
  WHERE valid_from <= @as_of)          -- omit for current state
WHERE rn = 1 AND NOT is_deleted;

Materialize the current view if reads are hot. On Snowflake this is exactly what a dbt snapshot plus a view gives you; on Databricks it is a Delta table plus a gold view.

Star, snowflake and Data Vault

Conformed dimensions and the semantic layer

A conformed dimension means the same dimension table is used identically by multiple facts, so a metric sliced by it is comparable across subject areas. That is what makes "revenue by region" and "cost by region" addable. Canonical metric definitions consumed as reusable data marts is the same idea stated as a metric layer. The three platform risk is exactly here: three engines means three places a definition can drift, and dbt owning the definitions is the mitigation.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Exactly once is a property of a boundary, not a system

Three places dedup can live, and how to choose

Late data, and the design that avoids the wall

The naive answer to "data can be 48 hours late" is allowed_lateness = 48 hours. That is the trap. Streaming Engine caps open window state at 60 GB per key, so a hot key plus long lateness is a hard wall rather than a slope.

The design: handle a bounded window in the streaming path, say six hours, which covers the overwhelming majority of real lateness, and reconcile the rest with a daily batch over the archive that replaces the affected partitions. Bounded state, predictable cost, and the batch path reuses data you already have.

Say the reasoning: "I would rather pay for a predictable daily batch than for streaming state held on the chance that a small fraction of events are very late."

The idempotent write, per platform

# BigQuery: partition decorator, replaces atomically
bq load --replace 'project:ds.table$20260821' ...

# Delta: idempotent foreachBatch
def upsert(microBatchDF, batchId):
    microBatchDF.sparkSession.conf.set("spark.databricks.delta.commitInfo.userMetadata", str(batchId))
    (DeltaTable.forName(spark, "gold.orders").alias("t")
       .merge(microBatchDF.alias("s"), "t.id = s.id")
       .whenMatchedUpdateAll().whenNotMatchedInsertAll().execute())
# plus .option("txnAppId", "orders_job").option("txnVersion", batchId)
# so a replayed batch is recognised and skipped

-- Snowflake: MERGE from a stream, offset advances only on success
MERGE INTO gold.orders t USING str_orders s ON t.id = s.id ...;

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Claiming window functions, complex CTEs, grouping sets, UDFs, partition pruning, join strategy selection and execution plan tuning across several engines is a wide claim, and it invites a live coding round. These twelve patterns are the ones that come up.

-- BigQuery and Snowflake and Databricks SQL all support QUALIFY
SELECT * FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY event_id ORDER BY ingested_at DESC) = 1;

SELECT d, k, x,
  SUM(x) OVER (PARTITION BY k ORDER BY d
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
  AVG(x) OVER (PARTITION BY k ORDER BY d
               RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS ma7
FROM daily;

WITH flagged AS (
  SELECT *, IF(TIMESTAMP_DIFF(ts, LAG(ts) OVER (PARTITION BY user_id ORDER BY ts),
                             MINUTE) > 30 OR LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL,
               1, 0) AS is_new
  FROM events)
SELECT *, SUM(is_new) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM flagged;

SELECT d FROM UNNEST(GENERATE_DATE_ARRAY('2026-01-01','2026-08-24')) AS d
LEFT JOIN facts f ON f.load_date = d
WHERE f.load_date IS NULL;

SELECT * FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) <= 3;

MERGE INTO dw.orders t
USING (
  SELECT * EXCEPT(rn) FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY source_lsn DESC) rn
    FROM stg.orders_delta)
  WHERE rn = 1
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount
WHEN NOT MATCHED AND s.op != 'D' THEN INSERT ROW;

SELECT f.*, d.segment
FROM fact_orders f
JOIN dim_customer_hist d
  ON f.customer_id = d.customer_id
 AND f.order_ts >= d.valid_from
 AND f.order_ts <  COALESCE(d.valid_to, TIMESTAMP '9999-12-31');

-- conditional aggregation works everywhere and handles a dynamic set badly
SELECT region,
  SUM(IF(quarter='Q1', revenue, 0)) AS q1,
  SUM(IF(quarter='Q2', revenue, 0)) AS q2
FROM sales GROUP BY region;
-- PIVOT exists on all three but needs a literal value list

SELECT o.order_id, item.sku, item.qty
FROM orders o, UNNEST(o.items) AS item WITH OFFSET AS pos;

SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS keys,
       COUNT(*) - COUNT(DISTINCT order_id) AS dupes
FROM dw.fact_orders
WHERE order_date = CURRENT_DATE() - 1;

SELECT COALESCE(a.k, b.k) AS k, a.amt AS src_amt, b.amt AS tgt_amt,
       a.amt - b.amt AS diff
FROM (SELECT k, SUM(amount) amt FROM source GROUP BY k) a
FULL OUTER JOIN (SELECT k, SUM(amount) amt FROM target GROUP BY k) b USING (k)
WHERE a.amt IS DISTINCT FROM b.amt;

SELECT APPROX_COUNT_DISTINCT(user_id) FROM events;   -- BigQuery, Databricks
SELECT APPROX_COUNT_DISTINCT(user_id) FROM events;   -- Snowflake: HLL

Dialect differences that cause incidents

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Fixed shape for all six. After three of them it becomes automatic under pressure:

1. Restate and clarify. Two or three questions, then stop. Endless clarification reads as stalling.
2. Constraints out loud, with arithmetic. Do the division in the room.
3. Draw, with a reason per box and a rejected alternative for the important ones.
4. Failure modes, unprompted. Naming how your own design breaks is the strongest single move available.
5. End on cost. Never wait to be asked.

producers
   |
   v
Pub/Sub (schema on topic, Avro, backward compatible)  --> DLT + replay
   |
   +--> Dataflow (Beam, event time windows, exactly once)
   |        --> BigQuery via Storage Write API   [analytics, near real time]
   |
   +--> GCS raw zone, Hive partitioned dt=/hour=  [replay, archive, ML]
            |
            +--> Auto Loader --> bronze --> DLT/Structured Streaming --> silver
            |         --> gold Delta, MERGE upserts     [Databricks: ML compute]
            |
            +--> BigLake / Iceberg tables               [one copy, many readers]

BigQuery  ->  Snowpipe from GCS ->  Snowflake + dbt   [governed consumption:
                                                       finance, exec reporting]

Cloud Composer + Cloud Workflows orchestrate. Dataplex catalogs and lineages.
Terraform provisions. One CI/CD path: Dataflow, Dataproc, Asset Bundles, dbt.

-- BigQuery
SELECT DATE(creation_time) d, user_email,
       SUM(total_bytes_billed)/POW(1024,4)*6.25 usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 DAY)
GROUP BY d, user_email ORDER BY usd DESC;

-- Databricks
SELECT usage_date, custom_tags.team, SUM(usage_quantity) dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 60 DAYS
GROUP BY 1,2 ORDER BY dbus DESC;

-- Snowflake
SELECT DATE_TRUNC('day', start_time) d, warehouse_name,
       SUM(credits_used) credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time > DATEADD(day,-60,CURRENT_TIMESTAMP())
GROUP BY 1,2 ORDER BY credits DESC;

1. Shadow table, identical schema, partitioning and clustering.
2. Backfill partition by partition, OLDEST FIRST, WRITE_TRUNCATE per partition.
     bq load --replace 'ds.fact_orders_v2$20260101' ...
   Idempotent by construction: rerunning a partition replaces it.
3. Live pipeline dual writes to both from the moment step 2 starts.
4. Verify per partition: row counts, key cardinality, aggregate fingerprints,
   with the expected delta explained rather than tolerated.
5. Swap by redefining the view consumers already read through.
6. Keep v1 for a defined window, then drop.

events -> Pub/Sub -> Dataflow (sliding window, 24h, 5min period)
                        |
                        +--> Bigtable   key = entityId#reverseTimestamp
                        |                [serving: sub second single key read]
                        |
                        +--> BigQuery / Delta
                                         [offline: training, backfill, audit]

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Technical candidates under-prepare this round and then lose offers in it. The content is not hard; the failure is always the same three things: answers that run long, answers with no number, and answers that describe a team rather than a person.

The question map

Almost every behavioral question maps to one of the twelve scaffolds in module 02. If you wrote those, this round is already done.

Leadership, evidenced rather than claimed

The four failure modes, and their fixes

Handling the questions candidates dread

Questions to ask, which are graded

1. "What does the data platform cost, and who watches that number?" Almost nobody asks this and it lands every time.
2. "Where does a metric definition live, and what happens when two teams disagree about one?"
3. "What is the on-call rotation, and what was the last serious incident?"
4. "What would you want the person in this role to have changed in six months?"
5. "What is the split between building new pipelines and maintaining existing ones?" The honest answer to this predicts your job satisfaction better than anything else you can ask.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Twenty four traps

An error teaches you. A trap does not: the query runs, the pipeline is green, and something is quietly wrong or quietly expensive. Interviewers love these because they are not learnable from documentation, only from having been burned.

Rapid fire: forty facts, fifteen seconds each

Cover the right column. If you hesitate on any of these they are not ready. Ten minutes end to end.

Three mock rounds

Grade yourself on these five, every time

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Where you are

Retention by module

Filled by retrieval, not by reading. A dark cell means the cards in that module are holding at a week or more.

The ledger

Practice reps

These need no cloud account. They are the reps that actually move an interview outcome. Tick one when you have done it out loud, against a clock.

Every rate on one screen

The twelve comparators, compressed

Cost formulas

BigQuery on demand   = TiB_scanned x 6.25          (first 1 TiB free)
BigQuery capacity    = slot_hours x edition_rate   (per second, 1 min min)
BigQuery reserve above = monthly_TiB > (slots x rate x 730) / 6.25
Snowflake            = credits_per_hour x hours    (XS=1, doubling per size)
Snowflake size up    = cost neutral while the query still scales linearly
Databricks           = DBUs x DBU_rate  +  VM_hours x VM_rate

Name index, current as of 2026

About this guide

Self contained. One HTML file, no build step, no account, no network calls except the web fonts. All progress, spaced repetition scheduling and the error log live in this browser's local storage only. Nothing is sent anywhere. Use Export before clearing site data or switching machines, and Import to restore.

Prices, limits and product names are current as of the 2026 rate cards and release notes and will drift. Verify any figure you intend to quote in an interview against the vendor pricing page on the day. Where a claim was uncertain it carries a confidence tag rather than a confident assertion.

The guide assumes you are preparing for a data engineering role that names Google Cloud, and possibly Databricks or Snowflake alongside it. It does not assume you have run any of them in production; module 01 covers the honest framing if you have not.

The night before

1. The forty rapid fire facts. Ten minutes.
2. The name index above. Two minutes. Cheapest credibility available.
3. The two minute open, out loud, twice.
4. The why three platforms answer, plus the consolidation answer.
5. Your scale numbers, derived out loud.
6. Case 01, drawn on paper from memory.
7. Stop. Sleep is worth more than the seventh hour of revision.

Retrieval cards

Answer out loud, then open and grade honestly. Grading is what schedules the next review, so grading everything Easy just deletes the feature.

Interleaved warm up

Pulls up to ten due cards, round robin across modules so no two consecutive cards come from the same one. Interleaving is harder than blocking, which is why it works.

Error log

Record what you got wrong, but state the broken assumption before the fix. A log of corrections teaches you nothing. A log of the beliefs that turned out false rewires how you predict.