<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Keshav</title>
    <description>The latest articles on DEV Community by Keshav (@keshav07).</description>
    <link>https://dev.to/keshav07</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4209411%2F23249a96-9f60-4a4b-a567-b7669bb79d50.jpg</url>
      <title>DEV Community: Keshav</title>
      <link>https://dev.to/keshav07</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/keshav07"/>
    <language>en</language>
    <item>
      <title>Data Lake vs Data Warehouse vs Lakehouse</title>
      <dc:creator>Keshav</dc:creator>
      <pubDate>Tue, 17 Nov 2026 17:46:18 +0000</pubDate>
      <link>https://dev.to/keshav07/data-lake-vs-data-warehouse-vs-lakehouse-42jh</link>
      <guid>https://dev.to/keshav07/data-lake-vs-data-warehouse-vs-lakehouse-42jh</guid>
      <description>&lt;p&gt;These three terms describe &lt;strong&gt;where analytical data lives, and what guarantees it has&lt;/strong&gt;. A data lake is files in cheap object storage. A data warehouse is a database built for analytical SQL. A lakehouse adds a transactional table format on top of lake files, so they behave like warehouse tables. Most real platforms combine at least two of them, so the useful skill is knowing what each one gives you, and which one a workload needs.&lt;/p&gt;

&lt;h2&gt;
  
  
  The comparison
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Data lake&lt;/th&gt;
&lt;th&gt;Data warehouse&lt;/th&gt;
&lt;th&gt;Lakehouse&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Storage&lt;/td&gt;
&lt;td&gt;Files in object storage (Parquet, JSON, CSV, images, logs)&lt;/td&gt;
&lt;td&gt;Managed storage inside the warehouse&lt;/td&gt;
&lt;td&gt;Files in object storage plus a table format (Delta Lake, Apache Iceberg, Apache Hudi)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Schema&lt;/td&gt;
&lt;td&gt;Applied when reading (schema-on-read)&lt;/td&gt;
&lt;td&gt;Enforced when writing&lt;/td&gt;
&lt;td&gt;Enforced when writing, with controlled evolution&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Transactions&lt;/td&gt;
&lt;td&gt;None by default&lt;/td&gt;
&lt;td&gt;Yes&lt;/td&gt;
&lt;td&gt;Yes, through the table format's log or metadata&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Updates and deletes&lt;/td&gt;
&lt;td&gt;Rewrite files yourself&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;UPDATE&lt;/code&gt;, &lt;code&gt;DELETE&lt;/code&gt;, &lt;code&gt;MERGE&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;UPDATE&lt;/code&gt;, &lt;code&gt;DELETE&lt;/code&gt;, &lt;code&gt;MERGE&lt;/code&gt; through the format&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Data types&lt;/td&gt;
&lt;td&gt;Anything, including unstructured&lt;/td&gt;
&lt;td&gt;Mostly structured and semi-structured&lt;/td&gt;
&lt;td&gt;Structured and semi-structured; other files alongside&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Compute&lt;/td&gt;
&lt;td&gt;Any engine that reads files&lt;/td&gt;
&lt;td&gt;The warehouse's engine&lt;/td&gt;
&lt;td&gt;Multiple engines over the same tables&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Performance tuning&lt;/td&gt;
&lt;td&gt;Up to you (file sizes, partitions)&lt;/td&gt;
&lt;td&gt;Mostly managed&lt;/td&gt;
&lt;td&gt;Partly managed (compaction, clustering, statistics)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Typical users&lt;/td&gt;
&lt;td&gt;Data engineers, data scientists&lt;/td&gt;
&lt;td&gt;Analysts, BI tools&lt;/td&gt;
&lt;td&gt;Both&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fn4r2ej56qjxqs0mc2sv9.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fn4r2ej56qjxqs0mc2sv9.png" alt="From storage to the people who use it" width="799" height="687"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Data lake vs lakehouse
&lt;/h2&gt;

&lt;p&gt;A lakehouse &lt;strong&gt;is&lt;/strong&gt; a data lake with a table format added. The data stays as Parquet files in the same object storage; the difference is a metadata layer (a Delta Lake log, Iceberg metadata or a Hudi timeline) that records which files make up each version of a table. That one layer changes what the files can do:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Data lake (plain files)&lt;/th&gt;
&lt;th&gt;Lakehouse (files + table format)&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;A write that touches 50 files&lt;/td&gt;
&lt;td&gt;Readers can see half of it&lt;/td&gt;
&lt;td&gt;Appears all at once or not at all&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;A file with the wrong columns&lt;/td&gt;
&lt;td&gt;Accepted; queries break later&lt;/td&gt;
&lt;td&gt;Rejected unless schema evolution is allowed&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Update or delete one customer&lt;/td&gt;
&lt;td&gt;Rewrite files yourself&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;UPDATE&lt;/code&gt;, &lt;code&gt;DELETE&lt;/code&gt; or &lt;code&gt;MERGE&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Last week's version&lt;/td&gt;
&lt;td&gt;Gone once files are overwritten&lt;/td&gt;
&lt;td&gt;Time travel to an earlier version&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;This is also the answer to "Delta Lake vs data lake": Delta Lake is not a separate place to keep data. It is a table format that runs on top of your data lake, so an existing folder of Parquet files can be converted into a Delta or Iceberg table in place. See &lt;a href="https://datadank.com/delta-lake/delta-vs-traditional-lake-tables/" rel="noopener noreferrer"&gt;Delta Lake vs traditional lake tables&lt;/a&gt; for the details.&lt;/p&gt;

&lt;h2&gt;
  
  
  Lakehouse vs data warehouse
&lt;/h2&gt;

&lt;p&gt;Both give you transactional tables, schemas and SQL. The differences are about ownership and operations:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Storage:&lt;/strong&gt; a lakehouse keeps open-format files in your own object storage; a warehouse keeps data in its own managed, usually proprietary storage.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Engines:&lt;/strong&gt; many engines (Spark, Trino, DuckDB, warehouses) can share one lakehouse table; a warehouse is queried mainly through its own engine.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Operations:&lt;/strong&gt; in a lakehouse you (or the platform) run compaction, cleanup and catalog upkeep; a warehouse handles most of this for you.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Strengths:&lt;/strong&gt; warehouses shine at high-concurrency BI with little to operate; lakehouses shine when BI, ML and engineering must share one copy of large data.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The two are converging: warehouses now read and write Iceberg tables in your storage, and lakehouse platforms offer warehouse-style SQL engines.&lt;/p&gt;

&lt;h2&gt;
  
  
  Data lake vs data warehouse
&lt;/h2&gt;

&lt;p&gt;A &lt;strong&gt;data lake&lt;/strong&gt; stores raw files in any format, applies a schema only when the data is read, and costs little; it suits engineers and data scientists who need flexibility. A &lt;strong&gt;data warehouse&lt;/strong&gt; stores modelled tables, enforces the schema when data is written, and returns fast, consistent SQL results; it suits analysts and dashboards. They are usually used together: the lake as the landing zone for everything, the warehouse (or a lakehouse layer) for the curated tables people report from.&lt;/p&gt;

&lt;h2&gt;
  
  
  Data lake
&lt;/h2&gt;

&lt;p&gt;Cheap, durable object storage (S3, ADLS, GCS) holding raw and processed files. It is flexible: store anything in any format, and process it with any engine, Spark, Trino, DuckDB, pandas. Storage costs little and scales without limits you will hit.&lt;/p&gt;

&lt;p&gt;The weakness is &lt;strong&gt;reliability&lt;/strong&gt;. A folder of files is not a table: there is no transaction to make a multi-file write atomic, no enforced schema, and no safe way to update a row. Concurrent writes and failed jobs leave readers seeing partial data, upstream type changes break queries months later, and without layers, ownership and a catalog the lake drifts into a "swamp" no one trusts.&lt;/p&gt;

&lt;h2&gt;
  
  
  Data warehouse
&lt;/h2&gt;

&lt;p&gt;A database optimised for analytical SQL: columnar storage, a cost-based optimiser, statistics, caching and concurrency control. It enforces schemas, provides transactions, and is easy for analysts and BI tools. Cloud warehouses such as Snowflake, BigQuery and Redshift separate storage from compute so each scales independently.&lt;/p&gt;

&lt;p&gt;The trade-offs: data must be loaded into the warehouse's storage, which means one more copy and one more pipeline. The storage format is usually proprietary, so other engines cannot read it directly. And workloads that want raw files, such as ML training on images or heavy Python processing, fit less naturally.&lt;/p&gt;

&lt;h2&gt;
  
  
  Lakehouse
&lt;/h2&gt;

&lt;p&gt;A lakehouse adds warehouse-like guarantees to lake storage through an &lt;strong&gt;open table format&lt;/strong&gt;: metadata on top of Parquet files that records which files make up each version of a table. Delta Lake keeps an ordered transaction log of JSON commits; Iceberg keeps a tree of metadata and manifest files with an atomic pointer swap in a catalog; Hudi keeps a timeline of commits. All three give:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;ACID commits&lt;/strong&gt;: a write becomes visible all at once or not at all.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Schema enforcement and evolution&lt;/strong&gt;: writes that do not match the schema fail unless evolution is requested.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Row-level changes&lt;/strong&gt;: &lt;code&gt;MERGE&lt;/code&gt;, &lt;code&gt;UPDATE&lt;/code&gt; and &lt;code&gt;DELETE&lt;/code&gt;, implemented by rewriting files or with deletion vectors.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Time travel&lt;/strong&gt;: read an earlier version for audits, debugging or reproducible ML.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;File-level statistics&lt;/strong&gt; for data skipping, plus maintenance operations (compaction, clustering, cleanup of old files).&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Many engines can read and write the same tables: Spark, Trino, Flink, DuckDB, and the major warehouses, which now read (and in some cases write) Iceberg or Delta tables in your storage. A &lt;strong&gt;catalog&lt;/strong&gt; (Unity Catalog, AWS Glue, Apache Polaris, Hive Metastore and others) maps table names to their metadata and enforces access. The &lt;a href="https://datadank.com/delta-lake/data-lakes-lakehouse-delta/" rel="noopener noreferrer"&gt;Delta Lake lesson&lt;/a&gt; shows the transaction log working.&lt;/p&gt;

&lt;p&gt;The cost of a lakehouse is &lt;strong&gt;operational&lt;/strong&gt;: someone has to run compaction and cleanup, choose layouts, keep catalogs and engines on compatible versions, and decide which engine owns writes to each table.&lt;/p&gt;

&lt;h2&gt;
  
  
  Seeing the difference: files vs a transactional table
&lt;/h2&gt;

&lt;p&gt;The example uses DuckDB, which can both query a folder of Parquet files (a lake) and manage its own transactional tables (a warehouse), so the two behaviours sit side by side. It runs in Python with &lt;code&gt;pip install duckdb&lt;/code&gt;.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;os&lt;/span&gt;
&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;duckdb&lt;/span&gt;

&lt;span class="n"&gt;con&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;duckdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;connect&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;os&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;makedirs&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"lake/orders"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;exist_ok&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="kc"&gt;True&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="c1"&gt;# Lake: a job writes files into a folder. Readers see whatever files exist.&lt;/span&gt;
&lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"""COPY (SELECT * FROM (VALUES (1, 'c1', 120.50), (2, 'c2', 80.00)) t(order_id, customer_id, amount))
               TO 'lake/orders/part-0001.parquet' (FORMAT parquet)"""&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="c1"&gt;# The next job should write two files but crashes after the first one&lt;/span&gt;
&lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"""COPY (SELECT * FROM (VALUES (3, 'c1', 15.00)) t(order_id, customer_id, amount))
               TO 'lake/orders/part-0002.parquet' (FORMAT parquet)"""&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;partial&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"SELECT count(*), sum(amount) FROM 'lake/orders/*.parquet'"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;fetchone&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"lake after failed job:"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;partial&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;          &lt;span class="c1"&gt;# half of the failed job is visible&lt;/span&gt;
&lt;span class="k"&gt;assert&lt;/span&gt; &lt;span class="n"&gt;partial&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="mi"&gt;3&lt;/span&gt;

&lt;span class="c1"&gt;# Upstream starts sending amount as text; nothing stops the file landing&lt;/span&gt;
&lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"""COPY (SELECT * FROM (VALUES (5, 'c3', 'n/a')) t(order_id, customer_id, amount))
               TO 'lake/orders/part-0003.parquet' (FORMAT parquet)"""&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;try&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"SELECT sum(amount) FROM 'lake/orders/*.parquet'"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;fetchone&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;raise&lt;/span&gt; &lt;span class="nc"&gt;AssertionError&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"expected the read to fail"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;except&lt;/span&gt; &lt;span class="n"&gt;duckdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;ConversionException&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"lake read now fails:"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="kt"&gt;str&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;e&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;splitlines&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;&lt;span class="mi"&gt;90&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="c1"&gt;# Warehouse: a typed table and a transaction. A failure rolls everything back.&lt;/span&gt;
&lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id TEXT NOT NULL, amount DECIMAL(10,2) NOT NULL)"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"INSERT INTO orders VALUES (1, 'c1', 120.50), (2, 'c2', 80.00)"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;try&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"BEGIN TRANSACTION"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"INSERT INTO orders VALUES (3, 'c1', 15.00)"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"INSERT INTO orders VALUES (5, 'c3', 'n/a')"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;     &lt;span class="c1"&gt;# rejected by the schema&lt;/span&gt;
    &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"COMMIT"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;except&lt;/span&gt; &lt;span class="n"&gt;duckdb&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;ConversionException&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"ROLLBACK"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;committed&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"SELECT count(*), sum(amount) FROM orders"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;fetchone&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"warehouse after failed job:"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;committed&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;   &lt;span class="c1"&gt;# nothing from the failed job&lt;/span&gt;
&lt;span class="k"&gt;assert&lt;/span&gt; &lt;span class="n"&gt;committed&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The lake showed half of a failed job to every reader, and then a bad file broke every query on the folder long after the job that wrote it. The warehouse rejected the bad row at write time and rolled back the whole job. A lakehouse table format gives lake files the second behaviour: the failed job's files are never committed to the log, so readers never see them, and a write with the wrong schema fails before it commits.&lt;/p&gt;

&lt;h2&gt;
  
  
  How to choose
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Who uses the data?&lt;/strong&gt; Mostly SQL analysts and dashboards favour a warehouse. Mixed BI, ML and engineering workloads favour a lakehouse.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;What data?&lt;/strong&gt; Large volumes of semi-structured or unstructured data push toward lake storage.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Openness.&lt;/strong&gt; Open table formats reduce lock-in and let several engines share one copy. If several engines must read the same tables, that requirement often decides the design.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Team skills and operations.&lt;/strong&gt; A managed warehouse has fewer moving parts. A lakehouse needs people who understand file layout, compaction and catalogs.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Cost profile.&lt;/strong&gt; Compare storage, compute and data movement for your workload rather than relying on general claims. Copying data into a warehouse costs pipelines and storage; running a lakehouse costs maintenance and engineering time.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Latency and concurrency.&lt;/strong&gt; High-concurrency BI with tight latency targets is where warehouses are strongest; both can serve it with the right engine.&lt;/li&gt;
&lt;/ol&gt;

&lt;h3&gt;
  
  
  Decision table
&lt;/h3&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Scenario&lt;/th&gt;
&lt;th&gt;Leaning&lt;/th&gt;
&lt;th&gt;Reasoning&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Startup with a few sources, analysts writing SQL, dashboards&lt;/td&gt;
&lt;td&gt;Warehouse&lt;/td&gt;
&lt;td&gt;Fastest path to trusted SQL; little to operate&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Clickstream or IoT at large volume, Spark processing, ML features&lt;/td&gt;
&lt;td&gt;Lakehouse&lt;/td&gt;
&lt;td&gt;Cheap storage, many engines, streaming writes with transactions&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Raw logs, images, documents kept for future use&lt;/td&gt;
&lt;td&gt;Lake (raw zone)&lt;/td&gt;
&lt;td&gt;No need for tables yet; keep it cheap and organised&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Regulated reporting with strict access control and auditing&lt;/td&gt;
&lt;td&gt;Warehouse or governed lakehouse&lt;/td&gt;
&lt;td&gt;Both work if the catalog and access controls are strong; choose the one the team can govern&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Several engines (Spark, Trino, a warehouse) must share one copy&lt;/td&gt;
&lt;td&gt;Lakehouse with one owning catalog per table&lt;/td&gt;
&lt;td&gt;Open formats avoid copies; one writer per table avoids conflicts&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Existing warehouse that works, plus a new ML team&lt;/td&gt;
&lt;td&gt;Keep the warehouse, add a lake or lakehouse for ML&lt;/td&gt;
&lt;td&gt;Migrations are expensive; share curated tables through an open format if needed&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Many organisations combine them: raw data in a lake, curated tables in a lakehouse format, and a warehouse or SQL endpoint for BI, often organised as bronze, silver and gold layers.&lt;/p&gt;



&lt;p&gt;Warehouses increasingly read and write open table formats such as Iceberg in your storage, and lakehouse platforms offer warehouse-style SQL engines. Focus on the properties you need (transactions, schema enforcement, openness, performance, governance) rather than the label.&lt;/p&gt;



&lt;h2&gt;
  
  
  Trade-offs to name
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;One copy vs convenience.&lt;/strong&gt; A lakehouse avoids copying data into a warehouse; a warehouse avoids running table maintenance.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Openness vs integration.&lt;/strong&gt; Open formats let you change engines; a single vendor's storage is better integrated with its optimiser, caching and governance.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Flexibility vs trust.&lt;/strong&gt; Schema-on-read accepts anything quickly and fails later; schema-on-write fails early and protects readers.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Multi-engine writes.&lt;/strong&gt; Several engines can read a table safely; letting several engines write it through different catalogs is how tables get corrupted.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Common mistakes
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Treating a lake as a dumping ground with no layers, schemas, ownership or catalog.&lt;/li&gt;
&lt;li&gt;Choosing a lakehouse "because it is modern" when the team only needs BI on structured data.&lt;/li&gt;
&lt;li&gt;Copying the same data into a lake and a warehouse without a clear source of truth.&lt;/li&gt;
&lt;li&gt;Assuming a lakehouse needs no maintenance; small files and old versions accumulate.&lt;/li&gt;
&lt;li&gt;Pointing two writers at the same table through different catalogs.&lt;/li&gt;
&lt;li&gt;Saying "a lakehouse is just Parquet"; plain Parquet files have no transactions.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  In an interview
&lt;/h2&gt;

&lt;p&gt;See the &lt;a href="https://datadank.com/interview/data-engineering/lake-warehouse-lakehouse/" rel="noopener noreferrer"&gt;interview answer&lt;/a&gt;. Strong answers name the deciding properties rather than reciting definitions: "A lake gives cheap, open storage but no transactions. A warehouse gives transactions, schema enforcement and fast SQL in managed storage. A lakehouse adds a table format to lake files to get most of both, at the cost of running maintenance." Then recommend for the scenario and mention how the layers fit together. Common follow-ups: "How does a table format make a multi-file write atomic?" (the commit to the log or catalog pointer is the atomic step), "Delta or Iceberg?" (&lt;a href="https://datadank.com/interview/delta-lake/delta-vs-iceberg-vs-hudi/" rel="noopener noreferrer"&gt;Delta vs Iceberg vs Hudi&lt;/a&gt;) and "Snowflake or Databricks?" (&lt;a href="https://datadank.com/snowflake/snowflake-vs-databricks/" rel="noopener noreferrer"&gt;Snowflake vs Databricks&lt;/a&gt;).&lt;/p&gt;

&lt;h2&gt;
  
  
  Key takeaway
&lt;/h2&gt;

&lt;p&gt;A lake is flexible storage, a warehouse is reliable SQL, and a lakehouse adds reliability to lake storage through an open table format. Choose by users, data types, openness, operations and measured cost, and be explicit about which system owns each table.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://datadank.com/data-warehousing/lake-vs-warehouse-vs-lakehouse/" rel="noopener noreferrer"&gt;DataDank&lt;/a&gt;, a free Data Engineering learning site with a &lt;a href="https://datadank.com/planner/90-day-plan/" rel="noopener noreferrer"&gt;90-day interview plan&lt;/a&gt; and &lt;a href="https://datadank.com/interview/questions/" rel="noopener noreferrer"&gt;900+ interview questions&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>dataengineering</category>
      <category>database</category>
      <category>cloud</category>
      <category>architecture</category>
    </item>
    <item>
      <title>Parquet vs Avro vs ORC for Data Engineering</title>
      <dc:creator>Keshav</dc:creator>
      <pubDate>Tue, 10 Nov 2026 14:53:41 +0000</pubDate>
      <link>https://dev.to/keshav07/parquet-vs-avro-vs-orc-for-data-engineering-op3</link>
      <guid>https://dev.to/keshav07/parquet-vs-avro-vs-orc-for-data-engineering-op3</guid>
      <description>&lt;p&gt;The first question is &lt;strong&gt;row or column?&lt;/strong&gt; Parquet and ORC store data by column; Avro stores it by row. That determines what each is good at: columnar formats for scanning a few columns of many rows, row formats for writing and reading whole records one at a time, as in a message stream.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Parquet&lt;/th&gt;
&lt;th&gt;ORC&lt;/th&gt;
&lt;th&gt;Avro&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Layout&lt;/td&gt;
&lt;td&gt;Columnar: row groups, then column chunks&lt;/td&gt;
&lt;td&gt;Columnar: stripes, then column streams&lt;/td&gt;
&lt;td&gt;Row-based: blocks of records&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Schema&lt;/td&gt;
&lt;td&gt;In the file footer&lt;/td&gt;
&lt;td&gt;In the file footer&lt;/td&gt;
&lt;td&gt;JSON schema in the file header (or in a schema registry for messages)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Statistics for skipping&lt;/td&gt;
&lt;td&gt;Min/max/null count per row group and page; optional bloom filters&lt;/td&gt;
&lt;td&gt;Min/max per file, stripe and every 10,000 rows; optional bloom filters&lt;/td&gt;
&lt;td&gt;None&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Splittable&lt;/td&gt;
&lt;td&gt;Yes, at row groups&lt;/td&gt;
&lt;td&gt;Yes, at stripes&lt;/td&gt;
&lt;td&gt;Yes, at sync markers between blocks&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Schema evolution&lt;/td&gt;
&lt;td&gt;Add columns; readers match by name (or field ID)&lt;/td&gt;
&lt;td&gt;Add columns; readers match by name or position&lt;/td&gt;
&lt;td&gt;Explicit reader/writer schema resolution rules&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Typical ecosystem&lt;/td&gt;
&lt;td&gt;Spark, Delta Lake, Iceberg, Hudi, warehouses, DuckDB, pandas&lt;/td&gt;
&lt;td&gt;Hive, Spark, Trino&lt;/td&gt;
&lt;td&gt;Kafka with a schema registry, Hadoop-era ingestion&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Setup
&lt;/h2&gt;

&lt;p&gt;The Avro data source ships as a separate Spark module, so the session adds the &lt;code&gt;spark-avro&lt;/code&gt; package matching the Spark version.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;glob&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;json&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;os&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;tempfile&lt;/span&gt;
&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="n"&gt;pyarrow&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;parquet&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;pq&lt;/span&gt;
&lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="n"&gt;pyspark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sql&lt;/span&gt; &lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="nc"&gt;SparkSession&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;functions&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="no"&gt;F&lt;/span&gt;

&lt;span class="n"&gt;spark&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nc"&gt;SparkSession&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;builder&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;master&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"local[2]"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;appName&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"file-formats"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
         &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"spark.jars.packages"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"org.apache.spark:spark-avro_2.13:4.2.0"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
         &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;config&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"spark.sql.shuffle.partitions"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"2"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;getOrCreate&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;spark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;sparkContext&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;setLogLevel&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"ERROR"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;base&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;tempfile&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;mkdtemp&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;spark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;range&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;200_000&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;select&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
    &lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;col&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"order_id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;col&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;%&lt;/span&gt; &lt;span class="mi"&gt;5000&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"customer_id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;element_at&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;array&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="o"&gt;*&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;lit&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;c&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;c&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"UK"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"FR"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"DE"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"ES"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"IT"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
                 &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;col&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;%&lt;/span&gt; &lt;span class="mi"&gt;5&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;cast&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"int"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"country"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;round&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="no"&gt;F&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;rand&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;42&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="mi"&gt;500&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;alias&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"amount"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;size_kb&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;path&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nb"&gt;sum&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;os&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;path&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;getsize&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="n"&gt;glob&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;glob&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{path}/part-*"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;//&lt;/span&gt; &lt;span class="mi"&gt;1024&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Inside a Parquet file
&lt;/h2&gt;

&lt;p&gt;A Parquet file is laid out as:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Row groups&lt;/strong&gt;: horizontal slices of the rows. Spark's writer targets 128 MB per row group by default (&lt;code&gt;parquet.block.size&lt;/code&gt;). A row group is the unit of parallelism and of skipping.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Column chunks&lt;/strong&gt;: inside each row group, the values of one column stored together.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Pages&lt;/strong&gt;: inside each chunk, the unit of encoding and compression (around 1 MB by default).&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Footer&lt;/strong&gt;: at the end of the file, the schema plus, for every column chunk, its offset, encodings, compressed size and &lt;strong&gt;statistics&lt;/strong&gt; (min, max, null count).&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;A reader fetches the footer first, decides which row groups and column chunks it needs, then reads only those byte ranges. Write a small file with deliberately small row groups and read its footer with PyArrow:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="n"&gt;path&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{base}/orders_parquet"&lt;/span&gt;
&lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;write&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;option&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"parquet.block.size"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;1024&lt;/span&gt; &lt;span class="o"&gt;*&lt;/span&gt; &lt;span class="mi"&gt;1024&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;parquet&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;path&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;meta&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;pq&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;ParquetFile&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;glob&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;glob&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{path}/part-*.parquet"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;metadata&lt;/span&gt;
&lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"row groups:"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;meta&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;num_row_groups&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"rows:"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;meta&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;num_rows&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;col&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;meta&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;schema&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;names&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;index&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"order_id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;g&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="nb"&gt;range&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;meta&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;num_row_groups&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;s&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;meta&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;row_group&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;g&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;column&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;col&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;statistics&lt;/span&gt;
    &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"group {g}: order_id {s.min}..{s.max}"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;c&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;meta&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;row_group&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;column&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;meta&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;schema&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;names&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;index&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"country"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"country:"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;compression&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;encodings&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_uncompressed_size&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"-&amp;gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;c&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;total_compressed_size&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"bytes"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;row groups: 3 rows: 200000
group 0: order_id 0..75378
group 1: order_id 75379..150757
group 2: order_id 150758..199999
country: SNAPPY ('BIT_PACKED', 'PLAIN_DICTIONARY') 28581 -&amp;gt; 1675 bytes
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Two things to read from this:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Row-group skipping.&lt;/strong&gt; A query with &lt;code&gt;WHERE order_id &amp;gt;= 180000&lt;/code&gt; only needs group 2: the footer proves the other groups cannot match. This works because &lt;code&gt;order_id&lt;/code&gt; is sorted. For an unsorted column every group spans nearly the full range and nothing is skipped, which is why layout techniques such as sorting, Z-ordering and liquid clustering matter.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Encoding before compression.&lt;/strong&gt; &lt;code&gt;country&lt;/code&gt; has five distinct values, so Parquet stores a small dictionary and then each value as a few bits (dictionary plus bit-packed/RLE encoding). The chunk shrinks to a few percent of its raw size before the codec does much. Low-cardinality columns are almost free to store.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Spark reports pushed filters and the pruned read schema in the plan:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="n"&gt;spark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;read&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;parquet&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;path&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;where&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"order_id &amp;gt;= 180000"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;select&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"amount"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;explain&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;== Physical Plan ==
*(1) Project [amount#...]
+- *(1) Filter (isnotnull(order_id#...) AND (order_id#... &amp;gt;= 180000))
   +- *(1) ColumnarToRow
      +- FileScan parquet [order_id#...,amount#...] ... PushedFilters: [IsNotNull(order_id), GreaterThanOrEqual(order_id,180000)], ReadSchema: struct&amp;lt;order_id:bigint,amount:double&amp;gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;(Plan shortened.) Only &lt;code&gt;order_id&lt;/code&gt; and &lt;code&gt;amount&lt;/code&gt; are read (&lt;strong&gt;column pruning&lt;/strong&gt;), and the filter is passed to the Parquet reader (&lt;strong&gt;predicate pushdown&lt;/strong&gt;), which uses the statistics above. Spark still applies the &lt;code&gt;Filter&lt;/code&gt; afterwards, because statistics only rule groups out; they do not select individual rows.&lt;/p&gt;

&lt;h2&gt;
  
  
  Inside ORC and Avro
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;ORC&lt;/strong&gt; (Optimized Row Columnar) splits a file into &lt;strong&gt;stripes&lt;/strong&gt; (64 MB by default), each holding column streams plus an index with min/max statistics for every 10,000 rows, and a file footer with stripe-level statistics. Its finer-grained index and built-in bloom filters can skip more data than Parquet's default statistics on some selective queries. ORC is also the format behind Hive ACID tables. In Spark 4 its default codec is zstd.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Avro&lt;/strong&gt; writes a header containing the full schema as JSON, then &lt;strong&gt;blocks&lt;/strong&gt; of rows separated by a 16-byte &lt;strong&gt;sync marker&lt;/strong&gt;. The marker lets a reader start at any byte offset and find the next block, so large Avro files are splittable even when blocks are compressed. Each record is written whole, with no column statistics, so there is nothing to skip: reading one column means reading every row.&lt;/p&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fee18p7hwhpugobbez6q4.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fee18p7hwhpugobbez6q4.png" alt="How each format lays out a file" width="800" height="708"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;h2&gt;
  
  
  Sizes, measured fairly
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;fmt&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"json"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"csv"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"parquet"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"orc"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"avro"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;write&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;format&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;fmt&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;option&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"header"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"true"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;save&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{base}/{fmt}"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;write&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;option&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"compression"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"zstd"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;parquet&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{base}/parquet_zstd"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="n"&gt;k&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="n"&gt;spark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;conf&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"spark.sql.{k}.compression.codec"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;k&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"parquet"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"orc"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"avro"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;fmt&lt;/span&gt; &lt;span class="k"&gt;in&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"json"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"csv"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"avro"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"parquet"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"parquet_zstd"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"orc"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{fmt:13}{size_kb(f'{base}/{fmt}'):&amp;gt;7} KB"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;{'parquet': 'snappy', 'orc': 'zstd', 'avro': 'snappy'}
json           13457 KB
csv             4082 KB
avro            2541 KB
parquet         2006 KB
parquet_zstd     964 KB
orc              647 KB
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Each writer uses its own default codec (Snappy for Parquet and Avro, zstd for ORC in Spark 4), so a naive size table compares codecs as much as formats. Switching Parquet to zstd halves its size here, which is a fairer comparison with ORC. ORC is still smaller on this synthetic data, largely because the &lt;code&gt;order_id&lt;/code&gt; column is a perfect sequence. ORC's integer run-length encoding stores it as runs with a fixed step. Spark's default Parquet writer stores it with plain encoding (the footer lists the encodings used per column chunk). On real data the gap is usually smaller and can go either way. Both columnar formats are several times smaller than JSON or CSV; Avro sits between them, compact binary but without columnar encoding. Treat any ratio as specific to your data and measure your own before deciding.&lt;/p&gt;

&lt;h2&gt;
  
  
  Avro schema evolution
&lt;/h2&gt;

&lt;p&gt;Avro defines precise rules for reading data written with one schema (the &lt;strong&gt;writer schema&lt;/strong&gt;) using another (the &lt;strong&gt;reader schema&lt;/strong&gt;). Fields are matched by name. A field missing from the data takes the reader's &lt;strong&gt;default&lt;/strong&gt;, and a field present in the data but absent from the reader schema is ignored. That is how a new consumer can read old records after a field is added:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="n"&gt;v1&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;spark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;createDataFrame&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Ana"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"Ben"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"id INT, name STRING"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;v1&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;coalesce&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;write&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;format&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"avro"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;save&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{base}/customers_v1"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

&lt;span class="n"&gt;reader_schema&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;json&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;dumps&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="s2"&gt;"type"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"record"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"name"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"topLevelRecord"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="s2"&gt;"fields"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="s2"&gt;"name"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"type"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"int"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"null"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="s2"&gt;"name"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"name"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"type"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="s2"&gt;"string"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"null"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="s2"&gt;"name"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"tier"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"type"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"string"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="s2"&gt;"default"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s2"&gt;"standard"&lt;/span&gt;&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;   &lt;span class="c1"&gt;# new field with a default&lt;/span&gt;
    &lt;span class="p"&gt;]&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;spark&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;read&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nb"&gt;format&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"avro"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;option&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"avroSchema"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;reader_schema&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;load&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;f&lt;/span&gt;&lt;span class="s2"&gt;"{base}/customers_v1"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; \
     &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;orderBy&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"id"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;show&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;+---+----+--------+
| id|name|    tier|
+---+----+--------+
|  1| Ana|standard|
|  2| Ben|standard|
+---+----+--------+
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Adding a field &lt;strong&gt;without&lt;/strong&gt; a default would make old data unreadable with the new schema, which is exactly what a schema registry's &lt;code&gt;BACKWARD&lt;/code&gt; compatibility check rejects. Renaming a field is only safe with an alias in the reader schema; changing a type is allowed only for Avro's defined promotions (for example &lt;code&gt;int&lt;/code&gt; to &lt;code&gt;long&lt;/code&gt;, &lt;code&gt;float&lt;/code&gt; to &lt;code&gt;double&lt;/code&gt;). Parquet and ORC handle added columns too (readers return nulls for files without them, and Spark can merge Parquet schemas with &lt;code&gt;mergeSchema&lt;/code&gt;), but they have no defaults and no aliases; table formats such as Delta and Iceberg add that management on top.&lt;/p&gt;

&lt;h2&gt;
  
  
  Choosing
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Situation&lt;/th&gt;
&lt;th&gt;Format&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Lakehouse and analytical tables&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;Parquet&lt;/strong&gt;, usually inside Delta, Iceberg or Hudi&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Existing Hive-centric platform, Hive ACID tables&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;ORC&lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Kafka messages and record-at-a-time ingestion with a schema registry&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;Avro&lt;/strong&gt; (or Protobuf), then convert to Parquet for analytics&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Landing data from external systems&lt;/td&gt;
&lt;td&gt;Whatever arrives (JSON, CSV), converted early, as in &lt;a href="https://datadank.com/delta-lake/json-vs-parquet/" rel="noopener noreferrer"&gt;JSON vs Parquet&lt;/a&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Common mistakes
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Keeping analytical data in CSV or JSON long-term.&lt;/li&gt;
&lt;li&gt;Writing thousands of tiny Parquet files: each has a footer to read and row groups too small to compress or skip well.&lt;/li&gt;
&lt;li&gt;Using Avro for wide analytical tables, where every query reads every column.&lt;/li&gt;
&lt;li&gt;Comparing format sizes without noticing the writers used different codecs.&lt;/li&gt;
&lt;li&gt;Expecting predicate pushdown to help on a column whose values are randomly spread across row groups.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Practice questions
&lt;/h2&gt;

&lt;p&gt;What is a Parquet row group and why does its size matter?&lt;/p&gt;

&lt;p&gt;A horizontal slice of the rows, stored as one column chunk per column, with statistics in the footer. It is the unit Spark splits and skips. Too large and you lose parallelism and skipping granularity; too small and compression worsens and the footer and per-group overhead grow. Spark's writer targets 128 MB.&lt;/p&gt;

&lt;p&gt;Why can a filter on order_id skip data but the same filter on a random column cannot?&lt;/p&gt;

&lt;p&gt;Skipping compares the filter with each row group's min/max. Sorted or clustered data gives narrow, non-overlapping ranges, so most groups are ruled out; randomly distributed values give every group almost the full range, so none can be ruled out.&lt;/p&gt;

&lt;p&gt;Avro and Parquet both support schema evolution. What is different?&lt;/p&gt;

&lt;p&gt;Avro resolves a writer schema against a reader schema with explicit rules: defaults for missing fields, aliases for renames and defined type promotions. Parquet only records each file's own schema; a reader can add missing columns as nulls or merge file schemas, but renames and defaults have to be handled by a table format or by the reader.&lt;/p&gt;

&lt;p&gt;Is Avro splittable? Is gzip-compressed CSV?&lt;/p&gt;

&lt;p&gt;Avro is: blocks are separated by sync markers, so a reader can start mid-file and seek to the next block, even with compressed blocks. A gzip-compressed CSV is not, because a gzip stream can only be decompressed from the beginning.&lt;/p&gt;

&lt;h2&gt;
  
  
  Key takeaways
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;Columnar (Parquet, ORC) for analytics; row-based (Avro) for messages and record-at-a-time ingestion.&lt;/li&gt;
&lt;li&gt;A Parquet footer holds the schema and per-row-group statistics; engines read it first and fetch only the column chunks and row groups they need.&lt;/li&gt;
&lt;li&gt;Statistics skip data only when the data is sorted or clustered on the filtered column.&lt;/li&gt;
&lt;li&gt;Compare sizes with the same codec; the defaults differ between writers.&lt;/li&gt;
&lt;li&gt;Avro's reader/writer schema resolution (defaults, aliases, promotions) is what makes registry-based compatibility checks work.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://datadank.com/delta-lake/parquet-vs-avro-vs-orc/" rel="noopener noreferrer"&gt;DataDank&lt;/a&gt;, a free Data Engineering learning site with a &lt;a href="https://datadank.com/planner/90-day-plan/" rel="noopener noreferrer"&gt;90-day interview plan&lt;/a&gt; and &lt;a href="https://datadank.com/interview/questions/" rel="noopener noreferrer"&gt;900+ interview questions&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>dataengineering</category>
      <category>bigdata</category>
      <category>database</category>
      <category>performance</category>
    </item>
    <item>
      <title>Snowflake vs BigQuery vs Redshift: How to Compare Them</title>
      <dc:creator>Keshav</dc:creator>
      <pubDate>Sat, 07 Nov 2026 18:29:14 +0000</pubDate>
      <link>https://dev.to/keshav07/snowflake-vs-bigquery-vs-redshift-how-to-compare-them-jc5</link>
      <guid>https://dev.to/keshav07/snowflake-vs-bigquery-vs-redshift-how-to-compare-them-jc5</guid>
      <description>&lt;p&gt;Snowflake, Google BigQuery and Amazon Redshift are the three cloud data warehouses that appear most often in data engineer job descriptions. All three store data in columns, run SQL in parallel, separate storage from compute (at least in their modern versions), read open table formats and offer row and column security. What differs is &lt;strong&gt;how you run them&lt;/strong&gt;: who sizes the compute, how you are billed, which design levers you control, and how tightly each is tied to one cloud.&lt;/p&gt;

&lt;p&gt;This page gives you a neutral framework for the comparison. It does not repeat the Snowflake versus Databricks material, which is in &lt;a href="https://datadank.com/snowflake/snowflake-vs-databricks/" rel="noopener noreferrer"&gt;Snowflake vs Databricks&lt;/a&gt;, or the general warehouse and lakehouse trade-offs in &lt;a href="https://datadank.com/data-warehousing/lake-vs-warehouse-vs-lakehouse/" rel="noopener noreferrer"&gt;Lake vs warehouse vs lakehouse&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Architecture
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Snowflake&lt;/strong&gt; has three layers: storage, compute and cloud services. Table data is stored once, in compressed columnar micro-partitions, in object storage on whichever cloud hosts the account (AWS, Azure or Google Cloud). Compute is &lt;strong&gt;virtual warehouses&lt;/strong&gt;: clusters you size in T-shirt sizes, which suspend and resume and can all read the same data. The services layer handles metadata, optimisation, transactions and security. You provision warehouses, but you never manage nodes, files or vacuuming. The details are in &lt;a href="https://datadank.com/snowflake/architecture-virtual-warehouses/" rel="noopener noreferrer"&gt;Snowflake architecture&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;BigQuery&lt;/strong&gt; is serverless. You create datasets and tables and run queries; BigQuery decides how many &lt;strong&gt;slots&lt;/strong&gt; (virtual compute units) each query stage uses, and you cannot set that per query. Storage and compute are separate services, which is also what lets BigQuery share datasets without copying them. You choose a &lt;em&gt;billing model&lt;/em&gt; for compute (on-demand or capacity, covered below), not a cluster size.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Redshift&lt;/strong&gt; comes in two deployment options:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Provisioned clusters&lt;/strong&gt;: a leader node that plans queries and compute nodes that run them. With &lt;strong&gt;RA3&lt;/strong&gt; node types (and the newer Graviton-based &lt;strong&gt;RG&lt;/strong&gt; node types), table data lives in &lt;strong&gt;Redshift Managed Storage&lt;/strong&gt;: local SSDs act as a cache and Amazon S3 holds the durable copy, so compute and storage are sized and billed independently. The older DC2 nodes keep data on local SSDs, so adding storage means adding nodes.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Redshift Serverless&lt;/strong&gt;: no clusters to manage. A workgroup has a &lt;strong&gt;base capacity&lt;/strong&gt; in Redshift Processing Units (RPUs) and scales up from it under load. AWS also offers AI-driven scaling and optimization, where you set a price-performance target and Serverless adjusts capacity to meet it.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;a href="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fvnwqs9yn2gdr430r6to0.png" class="article-body-image-wrapper"&gt;&lt;img src="https://media2.dev.to/dynamic/image/width=800%2Cheight=%2Cfit=scale-down%2Cgravity=auto%2Cformat=auto/https%3A%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Farticles%2Fvnwqs9yn2gdr430r6to0.png" alt="How a query runs on each platform" width="800" height="668"&gt;&lt;/a&gt;&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Snowflake&lt;/th&gt;
&lt;th&gt;BigQuery&lt;/th&gt;
&lt;th&gt;Redshift&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Storage and compute&lt;/td&gt;
&lt;td&gt;Separate&lt;/td&gt;
&lt;td&gt;Separate&lt;/td&gt;
&lt;td&gt;Separate on RA3, RG and Serverless; coupled on DC2&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Compute you size&lt;/td&gt;
&lt;td&gt;Virtual warehouses (sizes, multi-cluster)&lt;/td&gt;
&lt;td&gt;Nothing per query; optional slot reservations&lt;/td&gt;
&lt;td&gt;Nodes (provisioned) or base and maximum RPUs (Serverless)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Serverless option&lt;/td&gt;
&lt;td&gt;Serverless features (Snowpipe, Automatic Clustering, serverless tasks and others) alongside warehouses&lt;/td&gt;
&lt;td&gt;The whole service&lt;/td&gt;
&lt;td&gt;Redshift Serverless&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Clouds&lt;/td&gt;
&lt;td&gt;AWS, Azure, Google Cloud&lt;/td&gt;
&lt;td&gt;Google Cloud; BigQuery Omni queries data in AWS and Azure (Enterprise edition or on-demand)&lt;/td&gt;
&lt;td&gt;AWS only&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Pricing models
&lt;/h2&gt;

&lt;p&gt;Never compare these warehouses by headline unit price. The units measure different things, and every vendor's rate depends on edition, region and commitment. Compare the &lt;strong&gt;shape&lt;/strong&gt; of each model, then measure your own workloads. Current rates are on the &lt;a href="https://www.snowflake.com/en/pricing-options/" rel="noopener noreferrer"&gt;Snowflake pricing page&lt;/a&gt;, the &lt;a href="https://cloud.google.com/bigquery/pricing" rel="noopener noreferrer"&gt;BigQuery pricing page&lt;/a&gt; and the &lt;a href="https://aws.amazon.com/redshift/pricing/" rel="noopener noreferrer"&gt;Amazon Redshift pricing page&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Snowflake: credits.&lt;/strong&gt; A running warehouse consumes credits at a rate set by its size. Billing is per second, with a 60-second minimum each time a warehouse starts or resumes. Serverless features consume credits on their own meters, and storage is billed per compressed terabyte per month (Time Travel and Fail-safe retention add to it). The price of a credit depends on edition, region and contract. What drives the bill is warehouses left running and oversized warehouses; see &lt;a href="https://datadank.com/snowflake/cost-optimization/" rel="noopener noreferrer"&gt;Snowflake cost optimization&lt;/a&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;BigQuery: on-demand or capacity.&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;On-demand&lt;/strong&gt; (the default) charges for the bytes each query processes, per TiB. Because storage is columnar, you pay for the columns a query reads, so &lt;code&gt;SELECT *&lt;/code&gt; on a wide table is expensive, and on a non-clustered table &lt;code&gt;LIMIT&lt;/code&gt; does not reduce the bytes read. Cached results and failed queries are not charged. A project on on-demand gets a shared pool of slots, generally up to about 2,000 concurrent slots.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Capacity&lt;/strong&gt; pricing charges for slots over time (slot-hours) through &lt;strong&gt;reservations&lt;/strong&gt; in one of three &lt;strong&gt;editions&lt;/strong&gt;: Standard, Enterprise and Enterprise Plus. Reservations can have always-on &lt;strong&gt;baseline&lt;/strong&gt; slots, &lt;strong&gt;autoscaling&lt;/strong&gt; slots up to a maximum, and optional one- or three-year commitments on Enterprise and Enterprise Plus. Autoscaled capacity is billed per second with a one-minute minimum by default.&lt;/li&gt;
&lt;li&gt;You can mix the two models across projects. Storage is billed separately, with a lower long-term rate for tables or partitions not modified for 90 consecutive days, and a choice of logical (uncompressed) or physical (compressed) storage billing, set per dataset.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;strong&gt;Redshift: nodes or RPUs.&lt;/strong&gt;&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Provisioned&lt;/strong&gt; cost depends on node type, node count and region, with discounts for reserved nodes. On RA3 and RG you pay for managed storage separately. Features such as concurrency scaling and Spectrum queries on S3 (for RA3 clusters) have their own charges.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Serverless&lt;/strong&gt; bills RPU-hours per second, with a 60-second minimum, for both base and scaled capacity; storage is billed separately per GB-month. You can buy Serverless reservations for steady use, and you should set a &lt;strong&gt;maximum RPU-hours&lt;/strong&gt; limit so a busy month cannot surprise you.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The trap in every model is the same: idle or oversized compute. On BigQuery on-demand it is the other way round: badly filtered queries cost money even if they finish quickly.&lt;/p&gt;

&lt;h2&gt;
  
  
  Performance tuning levers
&lt;/h2&gt;

&lt;p&gt;All three skip data using metadata (minimum and maximum values per block or micro-partition). The levers you control decide how well that skipping works, and on Redshift also where rows sit for joins.&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Snowflake&lt;/th&gt;
&lt;th&gt;BigQuery&lt;/th&gt;
&lt;th&gt;Redshift&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Main lever&lt;/td&gt;
&lt;td&gt;Clustering key (&lt;code&gt;CLUSTER BY&lt;/code&gt;) on large tables&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;PARTITION BY&lt;/code&gt; (time-unit column, ingestion time or integer range) plus &lt;code&gt;CLUSTER BY&lt;/code&gt; up to four columns&lt;/td&gt;
&lt;td&gt;Sort key (compound or interleaved, or &lt;code&gt;AUTO&lt;/code&gt;) and distribution style (&lt;code&gt;AUTO&lt;/code&gt;, &lt;code&gt;EVEN&lt;/code&gt;, &lt;code&gt;KEY&lt;/code&gt;, &lt;code&gt;ALL&lt;/code&gt;)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Who maintains it&lt;/td&gt;
&lt;td&gt;Automatic Clustering, a serverless service billed in credits&lt;/td&gt;
&lt;td&gt;Automatic reclustering in the background at no charge&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;AUTO&lt;/code&gt; keys and automatic table sort; &lt;code&gt;VACUUM&lt;/code&gt; and &lt;code&gt;ANALYZE&lt;/code&gt; still matter for heavily changed tables&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Cost guardrail&lt;/td&gt;
&lt;td&gt;Warehouse size and auto-suspend&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;require_partition_filter&lt;/code&gt;, dry-run cost estimates, maximum bytes billed, custom daily query quotas&lt;/td&gt;
&lt;td&gt;WLM queues, Serverless maximum RPU-hours&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Other accelerators&lt;/td&gt;
&lt;td&gt;Result cache, search optimization, query acceleration, materialized views&lt;/td&gt;
&lt;td&gt;Cached results, BI Engine, materialized views, search indexes&lt;/td&gt;
&lt;td&gt;Materialized views, concurrency scaling&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Three differences come up in interviews:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Snowflake&lt;/strong&gt; asks you for the fewest physical decisions. Micro-partitioning is automatic; a clustering key is only worth it on large, frequently filtered tables, because reclustering consumes credits. See &lt;a href="https://datadank.com/snowflake/micro-partitions-clustering-pruning/" rel="noopener noreferrer"&gt;micro-partitions, clustering and pruning&lt;/a&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;BigQuery&lt;/strong&gt; partitioning gives an accurate cost estimate before the query runs; clustering alone does not, because the number of blocks scanned is known only afterwards. Partition on the date column most queries filter by, then cluster by the next most common filters, in that order.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Redshift&lt;/strong&gt; is the only one of the three where you think about &lt;strong&gt;co-locating join keys&lt;/strong&gt;. A &lt;code&gt;KEY&lt;/code&gt; distribution on the join column of two large tables avoids redistributing rows at query time; &lt;code&gt;ALL&lt;/code&gt; copies a small dimension to every node. With &lt;code&gt;AUTO&lt;/code&gt; (the default), Redshift chooses and can change the style as the table grows. Details are in &lt;a href="https://datadank.com/aws/redshift/#distribution-styles" rel="noopener noreferrer"&gt;Redshift distribution styles&lt;/a&gt; and &lt;a href="https://datadank.com/aws/redshift/#sort-keys" rel="noopener noreferrer"&gt;sort keys&lt;/a&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  The same table and query on each platform
&lt;/h2&gt;

&lt;p&gt;Here is one fact table, tuned the idiomatic way on each warehouse, and one query that benefits. The tables use only syntax shown in each vendor's CREATE TABLE documentation.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Snowflake: cluster a large table by the columns most queries filter on.&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;OR&lt;/span&gt; &lt;span class="k"&gt;REPLACE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="n"&gt;order_id&lt;/span&gt;    &lt;span class="n"&gt;NUMBER&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="n"&gt;NUMBER&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;order_date&lt;/span&gt;  &lt;span class="nb"&gt;DATE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;amount&lt;/span&gt;      &lt;span class="n"&gt;NUMBER&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;12&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;CLUSTER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;order_date&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- BigQuery: partition by day on the date column, then cluster within each partition.&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;sales&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="n"&gt;order_id&lt;/span&gt;    &lt;span class="n"&gt;INT64&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="n"&gt;INT64&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;order_date&lt;/span&gt;  &lt;span class="nb"&gt;DATE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;amount&lt;/span&gt;      &lt;span class="nb"&gt;NUMERIC&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;order_date&lt;/span&gt;
&lt;span class="k"&gt;CLUSTER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;
&lt;span class="k"&gt;OPTIONS&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;require_partition_filter&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;TRUE&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;





&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- Redshift: distribute on the join key, sort on the filter column.&lt;/span&gt;
&lt;span class="k"&gt;CREATE&lt;/span&gt; &lt;span class="k"&gt;TABLE&lt;/span&gt; &lt;span class="n"&gt;sales&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="n"&gt;order_id&lt;/span&gt;    &lt;span class="nb"&gt;BIGINT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;customer_id&lt;/span&gt; &lt;span class="nb"&gt;BIGINT&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;order_date&lt;/span&gt;  &lt;span class="nb"&gt;DATE&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="n"&gt;amount&lt;/span&gt;      &lt;span class="nb"&gt;DECIMAL&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;12&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;DISTSTYLE&lt;/span&gt; &lt;span class="k"&gt;KEY&lt;/span&gt;
&lt;span class="n"&gt;DISTKEY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="n"&gt;COMPOUND&lt;/span&gt; &lt;span class="n"&gt;SORTKEY&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;order_date&lt;/span&gt;&lt;span class="p"&gt;);&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;These blocks need a Snowflake account, a Google Cloud project and a Redshift cluster or workgroup, so they were checked against each vendor's documentation rather than run.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- The same query runs unchanged on all three (in Snowflake, drop the "sales." schema prefix&lt;/span&gt;
&lt;span class="c1"&gt;-- or create the table in a SALES schema).&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="k"&gt;SUM&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;amount&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;revenue&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;sales&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;orders&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;order_date&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="s1"&gt;'2026-09-01'&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;order_date&lt;/span&gt; &lt;span class="o"&gt;&amp;lt;&lt;/span&gt; &lt;span class="s1"&gt;'2026-10-01'&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;
&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;revenue&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;
&lt;span class="k"&gt;LIMIT&lt;/span&gt; &lt;span class="mi"&gt;10&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;What the date filter does on each:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Snowflake&lt;/strong&gt; prunes micro-partitions whose &lt;code&gt;order_date&lt;/code&gt; range falls outside September. The clustering key keeps those ranges narrow as the table grows.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;BigQuery&lt;/strong&gt; reads only the September partitions, so the cost estimate covers about one month of the &lt;code&gt;customer_id&lt;/code&gt;, &lt;code&gt;order_date&lt;/code&gt; and &lt;code&gt;amount&lt;/code&gt; columns. Without a date filter, the query is rejected because of &lt;code&gt;require_partition_filter&lt;/code&gt;. The &lt;code&gt;LIMIT 10&lt;/code&gt; applies after the aggregation, so it does not reduce the bytes read.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Redshift&lt;/strong&gt; skips blocks using zone maps on the sorted &lt;code&gt;order_date&lt;/code&gt; column. If the query joined to a &lt;code&gt;customers&lt;/code&gt; table also distributed on &lt;code&gt;customer_id&lt;/code&gt;, the join would run without moving rows between nodes.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Concurrency
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Snowflake&lt;/strong&gt; isolates workloads by giving each its own warehouse (ELT, BI, data science). For many simultaneous BI users, a &lt;strong&gt;multi-cluster warehouse&lt;/strong&gt; (Enterprise Edition or higher) adds clusters when queries start to queue and removes them when load drops. See &lt;a href="https://datadank.com/snowflake/virtual-warehouses-scaling/" rel="noopener noreferrer"&gt;warehouse scaling and concurrency&lt;/a&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;BigQuery&lt;/strong&gt; shares slots fairly: within a reservation, slots are divided between projects with running queries and then between jobs in each project, and work queues when demand exceeds the slots available. You isolate workloads with separate reservations, for example one for ETL and one for dashboards, and idle baseline slots can be borrowed by other reservations of the same edition in the same administration project. On-demand projects share their slot pool.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Redshift provisioned&lt;/strong&gt; manages concurrency with &lt;strong&gt;workload management&lt;/strong&gt; (WLM) queues and &lt;strong&gt;concurrency scaling&lt;/strong&gt;, which adds temporary cluster capacity for bursts of read queries and common write operations such as ETL statements. &lt;strong&gt;Redshift Serverless&lt;/strong&gt; scales RPUs above the base capacity instead. See &lt;a href="https://datadank.com/aws/redshift/#workload-management-wlm" rel="noopener noreferrer"&gt;WLM&lt;/a&gt; and &lt;a href="https://datadank.com/aws/redshift/#concurrency-scaling" rel="noopener noreferrer"&gt;concurrency scaling&lt;/a&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Semi-structured data
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Snowflake&lt;/th&gt;
&lt;th&gt;BigQuery&lt;/th&gt;
&lt;th&gt;Redshift&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;
&lt;code&gt;VARIANT&lt;/code&gt;, &lt;code&gt;OBJECT&lt;/code&gt; and &lt;code&gt;ARRAY&lt;/code&gt; types; path notation (&lt;code&gt;v:customer.id&lt;/code&gt;) and &lt;code&gt;FLATTEN&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;Native nested and repeated columns (&lt;code&gt;STRUCT&lt;/code&gt; and &lt;code&gt;ARRAY&lt;/code&gt;) and a &lt;code&gt;JSON&lt;/code&gt; type&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;SUPER&lt;/code&gt; type queried with PartiQL; materialized views can shred it into columns&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The practical difference: BigQuery encourages you to keep nested, repeated data in the table design itself, which avoids joins; Snowflake and Redshift load JSON into a flexible type and you extract hot fields into typed columns for performance. Snowflake's side is covered in &lt;a href="https://datadank.com/snowflake/table-types-semi-structured/" rel="noopener noreferrer"&gt;table types and semi-structured data&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Open table formats and Iceberg
&lt;/h2&gt;

&lt;p&gt;All three now read &lt;strong&gt;and&lt;/strong&gt; write Apache Iceberg, which matters if you want other engines to use the same data without copying it. The question to answer in a design is still &lt;strong&gt;which catalog owns each table&lt;/strong&gt;.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Snowflake&lt;/strong&gt;: Iceberg tables keep Parquet data and Iceberg metadata in your bucket through an &lt;strong&gt;external volume&lt;/strong&gt;. With &lt;strong&gt;Snowflake as the catalog&lt;/strong&gt;, Snowflake manages writes and maintenance and other engines can read the tables. With an &lt;strong&gt;external catalog&lt;/strong&gt; (AWS Glue, Snowflake Open Catalog or another Iceberg REST catalog), Snowflake reads the tables and can also write to tables in REST catalogs, including through a catalog-linked database.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;BigQuery&lt;/strong&gt;: &lt;strong&gt;Apache Iceberg managed tables&lt;/strong&gt; (formerly BigLake tables for Apache Iceberg in BigQuery) store Parquet files in your Cloud Storage bucket while BigQuery handles DML, streaming, compaction, clustering and garbage collection. BigQuery exports Iceberg metadata so engines such as Spark can read the files. Do not write to those files from outside BigQuery: untracked files are garbage collected. Managed Iceberg tables also have limits that standard tables do not, such as no row-level security.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Redshift&lt;/strong&gt;: queries Iceberg tables registered in the &lt;strong&gt;AWS Glue Data Catalog&lt;/strong&gt; (on RA3 through Spectrum, on RG and Serverless on its own compute). It can also &lt;strong&gt;create and write&lt;/strong&gt; Iceberg tables in Amazon S3 and S3 table buckets with &lt;code&gt;CREATE TABLE ... USING ICEBERG&lt;/code&gt;, &lt;code&gt;INSERT&lt;/code&gt;, &lt;code&gt;DELETE&lt;/code&gt;, &lt;code&gt;UPDATE&lt;/code&gt; and &lt;code&gt;MERGE&lt;/code&gt; (&lt;a href="https://docs.aws.amazon.com/redshift/latest/dg/iceberg-writes.html" rel="noopener noreferrer"&gt;writing to Iceberg tables&lt;/a&gt;).&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The native table format of each warehouse remains proprietary and is not readable as files by other engines. For background on Iceberg, Delta and Hudi, see &lt;a href="https://datadank.com/interview/delta-lake/delta-vs-iceberg-vs-hudi/" rel="noopener noreferrer"&gt;Delta vs Iceberg vs Hudi&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Governance and security
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Snowflake&lt;/th&gt;
&lt;th&gt;BigQuery&lt;/th&gt;
&lt;th&gt;Redshift&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Access model&lt;/td&gt;
&lt;td&gt;Role-based access control inside Snowflake; roles granted to users and other roles&lt;/td&gt;
&lt;td&gt;Google Cloud IAM roles on projects, datasets and tables&lt;/td&gt;
&lt;td&gt;Database users and role-based access control (RBAC); IAM roles for access to S3 and other AWS services&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Row and column controls&lt;/td&gt;
&lt;td&gt;Row access policies, masking policies, tag-based masking (Enterprise Edition or higher)&lt;/td&gt;
&lt;td&gt;Row-level security, column-level access control, dynamic data masking (Enterprise and Enterprise Plus editions or on-demand)&lt;/td&gt;
&lt;td&gt;Row-level security and dynamic data masking policies&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Lake governance&lt;/td&gt;
&lt;td&gt;Policies on Iceberg tables; Horizon catalog&lt;/td&gt;
&lt;td&gt;Policies on Iceberg managed tables (column-level only)&lt;/td&gt;
&lt;td&gt;AWS Lake Formation fine-grained permissions on data lake tables&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Network and keys&lt;/td&gt;
&lt;td&gt;Private connectivity options depend on edition&lt;/td&gt;
&lt;td&gt;VPC Service Controls and CMEK (not on Standard edition)&lt;/td&gt;
&lt;td&gt;Clusters and workgroups run in your VPC&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The useful observation: BigQuery and Redshift govern through their cloud's identity system (Google Cloud IAM, AWS IAM and Lake Formation), which is convenient if the rest of your platform lives in that cloud. Snowflake keeps its own role model, which is the same on every cloud it runs on. Read &lt;a href="https://datadank.com/snowflake/security-access-control/" rel="noopener noreferrer"&gt;Snowflake security and access control&lt;/a&gt; and &lt;a href="https://datadank.com/aws/lake-formation/" rel="noopener noreferrer"&gt;Lake Formation&lt;/a&gt; for the details.&lt;/p&gt;

&lt;h2&gt;
  
  
  Data sharing
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Snowflake Secure Data Sharing&lt;/strong&gt; shares live tables with other Snowflake accounts without copying data; consumers pay for their own compute. Reader accounts serve consumers without a Snowflake account, and listings on the Snowflake Marketplace reach external buyers. Sharing across regions or clouds requires replication. See &lt;a href="https://datadank.com/snowflake/data-sharing-marketplace/" rel="noopener noreferrer"&gt;Snowflake data sharing&lt;/a&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;BigQuery sharing&lt;/strong&gt; (formerly Analytics Hub) publishes datasets as listings in data exchanges; subscribers get a read-only &lt;strong&gt;linked dataset&lt;/strong&gt; in their own project, with no copy, and pay for their own queries. Publishers can restrict exports from linked datasets.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Redshift data sharing&lt;/strong&gt; shares live data across clusters, Serverless workgroups, AWS accounts and AWS Regions, for both reads and writes. Data products can be listed in &lt;strong&gt;AWS Data Exchange&lt;/strong&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;All three avoid copying data inside their own platform. Sharing &lt;strong&gt;across&lt;/strong&gt; platforms (Snowflake to BigQuery, for example) means open tables in a shared bucket, exports, or a replication tool.&lt;/p&gt;

&lt;h2&gt;
  
  
  Comparison at a glance
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Dimension&lt;/th&gt;
&lt;th&gt;Snowflake&lt;/th&gt;
&lt;th&gt;BigQuery&lt;/th&gt;
&lt;th&gt;Redshift&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Operating model&lt;/td&gt;
&lt;td&gt;Managed warehouse; you size and schedule warehouses&lt;/td&gt;
&lt;td&gt;Serverless; optional slot reservations&lt;/td&gt;
&lt;td&gt;Provisioned clusters or Serverless&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Compute billing&lt;/td&gt;
&lt;td&gt;Credits per second (60-second minimum)&lt;/td&gt;
&lt;td&gt;Per TiB processed, or slot-hours by edition&lt;/td&gt;
&lt;td&gt;Node-hours, or RPU-hours per second (60-second minimum)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Main tuning DDL&lt;/td&gt;
&lt;td&gt;&lt;code&gt;CLUSTER BY&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;PARTITION BY&lt;/code&gt; plus &lt;code&gt;CLUSTER BY&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;DISTSTYLE&lt;/code&gt;, &lt;code&gt;DISTKEY&lt;/code&gt;, &lt;code&gt;SORTKEY&lt;/code&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Concurrency&lt;/td&gt;
&lt;td&gt;Separate warehouses; multi-cluster warehouses&lt;/td&gt;
&lt;td&gt;Fair scheduling of slots; reservations per workload&lt;/td&gt;
&lt;td&gt;WLM and concurrency scaling; Serverless RPU scaling&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Semi-structured&lt;/td&gt;
&lt;td&gt;&lt;code&gt;VARIANT&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Nested &lt;code&gt;STRUCT&lt;/code&gt; and &lt;code&gt;ARRAY&lt;/code&gt;, &lt;code&gt;JSON&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;SUPER&lt;/code&gt; with PartiQL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Iceberg&lt;/td&gt;
&lt;td&gt;Read and write (Snowflake or external REST catalog)&lt;/td&gt;
&lt;td&gt;Iceberg managed tables in your bucket&lt;/td&gt;
&lt;td&gt;Read and write through AWS Glue Data Catalog&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Clouds&lt;/td&gt;
&lt;td&gt;AWS, Azure, Google Cloud&lt;/td&gt;
&lt;td&gt;Google Cloud (Omni for AWS and Azure)&lt;/td&gt;
&lt;td&gt;AWS&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Sharing&lt;/td&gt;
&lt;td&gt;Secure Data Sharing, Marketplace&lt;/td&gt;
&lt;td&gt;BigQuery sharing, linked datasets&lt;/td&gt;
&lt;td&gt;Data sharing, AWS Data Exchange&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  When to choose each
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;If your situation is mostly...&lt;/th&gt;
&lt;th&gt;Leaning&lt;/th&gt;
&lt;th&gt;Why&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Multi-cloud or cloud-neutral strategy; many teams with separate compute budgets; SQL and dbt ELT&lt;/td&gt;
&lt;td&gt;Snowflake&lt;/td&gt;
&lt;td&gt;Same product on three clouds; warehouses give simple workload isolation and chargeback&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Platform already on Google Cloud; spiky or unpredictable ad hoc analysis; small platform team&lt;/td&gt;
&lt;td&gt;BigQuery&lt;/td&gt;
&lt;td&gt;Nothing to size; on-demand suits irregular use, reservations suit steady use&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Platform already on AWS; data lake on S3 with Glue and Lake Formation; steady, well-understood workloads&lt;/td&gt;
&lt;td&gt;Redshift&lt;/td&gt;
&lt;td&gt;Tight AWS integration; Serverless for variable load, provisioned RA3 or RG for steady load with reservations&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Need open tables that several engines read&lt;/td&gt;
&lt;td&gt;Any of the three, designed deliberately&lt;/td&gt;
&lt;td&gt;All write Iceberg now; choose the owning catalog first&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Existing estate and skills strongly on one side&lt;/td&gt;
&lt;td&gt;The existing one&lt;/td&gt;
&lt;td&gt;Migration and retraining usually cost more than the feature differences&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Two honest caveats. First, the cloud you already run on usually decides more than the warehouse features do: egress costs, identity, networking and procurement all favour staying in one cloud. Second, each warehouse can be made cheap or expensive by how you use it, so a proof of concept on your own workloads beats any published benchmark.&lt;/p&gt;

&lt;h2&gt;
  
  
  How to run a fair evaluation
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Pick three to five real workloads: a large nightly transformation, a dashboard query set under concurrency, an incremental load, and an ad hoc exploration pattern.&lt;/li&gt;
&lt;li&gt;Implement each idiomatically: clustering keys on Snowflake, partitioning and clustering on BigQuery, distribution and sort keys on Redshift. Do not port one platform's design unchanged.&lt;/li&gt;
&lt;li&gt;Measure runtime, total cost (including serverless meters, storage, Spectrum or data lake scans and concurrency scaling), and the operational work: sizing, monitoring, failure recovery.&lt;/li&gt;
&lt;li&gt;Test both billing models where they exist: BigQuery on-demand against a reservation, and Redshift Serverless against provisioned capacity.&lt;/li&gt;
&lt;li&gt;Check governance against your requirements: row and column security, auditing, and how each fits your identity provider.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Common mistakes
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Comparing a credit, a TiB scanned and an RPU-hour as if they were the same unit.&lt;/li&gt;
&lt;li&gt;Running &lt;code&gt;SELECT *&lt;/code&gt; or unfiltered queries on BigQuery on-demand and being surprised by the bill; on non-clustered tables &lt;code&gt;LIMIT&lt;/code&gt; does not help, while partition filters, column selection and maximum bytes billed do.&lt;/li&gt;
&lt;li&gt;Leaving Snowflake warehouses or Redshift provisioned clusters running all night with nothing to do.&lt;/li&gt;
&lt;li&gt;Adding clustering keys to small Snowflake tables, or partitioning BigQuery tables into many tiny partitions.&lt;/li&gt;
&lt;li&gt;Choosing &lt;code&gt;KEY&lt;/code&gt; distribution on a skewed column in Redshift, so one slice does most of the work.&lt;/li&gt;
&lt;li&gt;Letting two engines write the same Iceberg table through different catalogs.&lt;/li&gt;
&lt;li&gt;Repeating out-of-date claims such as "Redshift cannot scale storage separately from compute" (RA3, RG and Serverless can) or "BigQuery has no fixed-cost option" (capacity reservations exist).&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  In an interview
&lt;/h2&gt;

&lt;p&gt;If asked "Snowflake, BigQuery or Redshift?", start with the context (which cloud, workload shape, team skills, governance needs), then map it to each platform's operating model and billing model, and end with a recommendation for the scenario stated. A strong shape: "We are on AWS with a Glue-catalogued S3 lake and steady nightly ELT, so I would start with Redshift and compare Serverless against RA3 with reservations. If the company were multi-cloud, I would look at Snowflake. If it were on Google Cloud with irregular analyst queries, I would choose BigQuery on-demand with partition filters required." Expect follow-ups on the tuning levers (clustering keys, partitioning and clustering, distribution and sort keys), on cost control, and on Iceberg interoperability. Practise with the &lt;a href="https://datadank.com/interview/aws/redshift-distribution-sort-keys/" rel="noopener noreferrer"&gt;Redshift distribution and sort keys question&lt;/a&gt; and &lt;a href="https://datadank.com/interview/snowflake/when-to-use-clustering-keys/" rel="noopener noreferrer"&gt;when to use clustering keys in Snowflake&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Key takeaway
&lt;/h2&gt;

&lt;p&gt;All three are mature, columnar, separated-storage warehouses that read and write Iceberg. Snowflake gives you sized warehouses on any of three clouds, BigQuery gives you serverless compute billed by data scanned or by slots, and Redshift gives you AWS-native provisioned or serverless capacity with distribution and sort keys to tune. Decide by your cloud, your workload shape and measured cost, not by headline prices.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://datadank.com/snowflake/snowflake-vs-bigquery-vs-redshift/" rel="noopener noreferrer"&gt;DataDank&lt;/a&gt;, a free Data Engineering learning site with a &lt;a href="https://datadank.com/planner/90-day-plan/" rel="noopener noreferrer"&gt;90-day interview plan&lt;/a&gt; and &lt;a href="https://datadank.com/interview/questions/" rel="noopener noreferrer"&gt;900+ interview questions&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>dataengineering</category>
      <category>snowflake</category>
      <category>bigquery</category>
      <category>aws</category>
    </item>
    <item>
      <title>Airflow vs Dagster vs Prefect: How to Choose</title>
      <dc:creator>Keshav</dc:creator>
      <pubDate>Mon, 26 Oct 2026 19:21:04 +0000</pubDate>
      <link>https://dev.to/keshav07/airflow-vs-dagster-vs-prefect-how-to-choose-211o</link>
      <guid>https://dev.to/keshav07/airflow-vs-dagster-vs-prefect-how-to-choose-211o</guid>
      <description>&lt;p&gt;Airflow, Dagster and Prefect are all open-source Python orchestrators. All three schedule pipelines, retry failed steps, keep a run history, show it in a web UI and run on Kubernetes or a cloud container service. "Which one is best?" has no general answer. What differs is the &lt;strong&gt;unit you model&lt;/strong&gt; (a task, a data asset, or a Python function call), how much infrastructure you operate, and how far your existing code and skills already lean one way.&lt;/p&gt;

&lt;p&gt;This lesson compares them as a decision framework. It assumes you know Airflow's basics from &lt;a href="https://datadank.com/airflow/dag-fundamentals-taskflow/" rel="noopener noreferrer"&gt;Dag fundamentals and TaskFlow&lt;/a&gt; and &lt;a href="https://datadank.com/airflow/dags-scheduling-retries/" rel="noopener noreferrer"&gt;scheduling and assets&lt;/a&gt;, and does not re-teach them.&lt;/p&gt;

&lt;h2&gt;
  
  
  Versions compared
&lt;/h2&gt;

&lt;p&gt;As of October 2026: &lt;strong&gt;Apache Airflow 3.x&lt;/strong&gt; (3.3.2 used here), &lt;strong&gt;Dagster 1.x&lt;/strong&gt; (1.13.26) and &lt;strong&gt;Prefect 3.x&lt;/strong&gt; (3.8.8). Older comparisons are often wrong now. Airflow 3 renamed datasets to &lt;strong&gt;assets&lt;/strong&gt; and added event-driven scheduling; in Prefect 3 &lt;strong&gt;workers&lt;/strong&gt; are standard, replacing the agents that were deprecated during Prefect 2. Dagster stays on 1.x and its documentation now scaffolds and runs projects with the &lt;code&gt;create-dagster&lt;/code&gt; and &lt;code&gt;dg&lt;/code&gt; command-line tools.&lt;/p&gt;

&lt;h2&gt;
  
  
  The same pipeline in all three
&lt;/h2&gt;

&lt;p&gt;A tiny extract, transform, load job: three order rows go in, revenue per country comes out, and it is written to SQLite with &lt;code&gt;INSERT OR REPLACE&lt;/code&gt;, so running it twice gives the same table. In each tool the business logic is the same; only the wrapping changes.&lt;/p&gt;

&lt;h3&gt;
  
  
  Airflow: a Dag of tasks
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;
&lt;span class="kn"&gt;from&lt;/span&gt; &lt;span class="n"&gt;airflow.sdk&lt;/span&gt; &lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;dag&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;task&lt;/span&gt;

&lt;span class="n"&gt;DB&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;orders.db&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;


&lt;span class="nd"&gt;@dag&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;schedule&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;@daily&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;catchup&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="bp"&gt;False&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;orders_pipeline&lt;/span&gt;&lt;span class="p"&gt;():&lt;/span&gt;
    &lt;span class="nd"&gt;@task&lt;/span&gt;
    &lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;extract&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;]:&lt;/span&gt;
        &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
            &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;IN&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;120.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
            &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;US&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;80.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
            &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;IN&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;30.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
        &lt;span class="p"&gt;]&lt;/span&gt;

    &lt;span class="nd"&gt;@task&lt;/span&gt;
    &lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;transform&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;]:&lt;/span&gt;
        &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{}&lt;/span&gt;
        &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;],&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
        &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;

    &lt;span class="nd"&gt;@task&lt;/span&gt;
    &lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;load&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="bp"&gt;None&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;connect&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;DB&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
            &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;CREATE TABLE IF NOT EXISTS revenue (country TEXT PRIMARY KEY, total REAL)&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
            &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;executemany&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;INSERT OR REPLACE INTO revenue VALUES (?, ?)&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;items&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
        &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sa"&gt;f&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;loaded &lt;/span&gt;&lt;span class="si"&gt;{&lt;/span&gt;&lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="si"&gt;}&lt;/span&gt;&lt;span class="s"&gt; rows: &lt;/span&gt;&lt;span class="si"&gt;{&lt;/span&gt;&lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="si"&gt;}&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;

    &lt;span class="nf"&gt;load&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;transform&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;extract&lt;/span&gt;&lt;span class="p"&gt;()))&lt;/span&gt;


&lt;span class="n"&gt;pipeline&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;orders_pipeline&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;

&lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;__name__&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;__main__&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;pipeline&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;test&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;dag.test()&lt;/code&gt; executes a real Dag run, so the file has to sit in the Dags folder and the metadata database must exist (&lt;code&gt;airflow db migrate&lt;/code&gt;). Selected lines of the output (the full log is about 50 structured lines):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;loaded 2 rows: {'IN': 150.0, 'US': 80.0}
... DagRun Finished: dag_id=orders_pipeline, ... run_duration=7.226455, state=success, run_type=manual, ...
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The return values travel between tasks as XComs, which is fine for a small dict and wrong for a DataFrame; see &lt;a href="https://datadank.com/airflow/xcom-variables-connections/" rel="noopener noreferrer"&gt;XCom, Variables and Connections&lt;/a&gt;.&lt;/p&gt;

&lt;h3&gt;
  
  
  Dagster: three software-defined assets
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;
&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;dagster&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;dg&lt;/span&gt;

&lt;span class="n"&gt;DB&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;orders.db&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;


&lt;span class="nd"&gt;@dg.asset&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;raw_orders&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;]:&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;IN&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;120.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;US&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;80.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;IN&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;30.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
    &lt;span class="p"&gt;]&lt;/span&gt;


&lt;span class="nd"&gt;@dg.asset&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;country_revenue&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;raw_orders&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;]:&lt;/span&gt;
    &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{}&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;raw_orders&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;],&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;


&lt;span class="nd"&gt;@dg.asset&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;revenue_table&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;country_revenue&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;dg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;MaterializeResult&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;connect&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;DB&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;CREATE TABLE IF NOT EXISTS revenue (country TEXT PRIMARY KEY, total REAL)&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;executemany&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;INSERT OR REPLACE INTO revenue VALUES (?, ?)&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;country_revenue&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;items&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;dg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nc"&gt;MaterializeResult&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;metadata&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;rows&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;country_revenue&lt;/span&gt;&lt;span class="p"&gt;)})&lt;/span&gt;


&lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;__name__&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;__main__&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;result&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;dg&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;materialize&lt;/span&gt;&lt;span class="p"&gt;([&lt;/span&gt;&lt;span class="n"&gt;raw_orders&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;country_revenue&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;revenue_table&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;success:&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;result&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;success&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country_revenue:&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;result&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;output_for_node&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country_revenue&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;))&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;There is no explicit dependency wiring: the parameter name &lt;code&gt;raw_orders&lt;/code&gt; &lt;em&gt;is&lt;/em&gt; the dependency on the asset of that name. Selected output lines:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;... raw_orders - Writing file at: /tmp/tmpd69noogr/storage/raw_orders using PickledObjectFilesystemIOManager...
... raw_orders - ASSET_MATERIALIZATION - Materialized value raw_orders.
... revenue_table - STEP_SUCCESS - Finished execution of step "revenue_table" in 252ms.
... RUN_SUCCESS - Finished execution of run for "__ephemeral_asset_job__".
success: True
country_revenue: {'IN': 150.0, 'US': 80.0}
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Note the line about the IO manager: Dagster stored each returned value (pickled, in a temporary directory) and loaded it for the downstream asset. In production you choose an IO manager that writes to your warehouse or object store, or you write the data yourself, as &lt;code&gt;revenue_table&lt;/code&gt; does, and return only metadata.&lt;/p&gt;

&lt;h3&gt;
  
  
  Prefect: a flow calling tasks
&lt;/h3&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight python"&gt;&lt;code&gt;&lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;
&lt;span class="kn"&gt;from&lt;/span&gt; &lt;span class="n"&gt;prefect&lt;/span&gt; &lt;span class="kn"&gt;import&lt;/span&gt; &lt;span class="n"&gt;flow&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;task&lt;/span&gt;

&lt;span class="n"&gt;DB&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;orders.db&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;


&lt;span class="nd"&gt;@task&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;extract&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;]:&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="p"&gt;[&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;IN&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;120.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;US&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;80.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
        &lt;span class="p"&gt;{&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;IN&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="mf"&gt;30.0&lt;/span&gt;&lt;span class="p"&gt;},&lt;/span&gt;
    &lt;span class="p"&gt;]&lt;/span&gt;


&lt;span class="nd"&gt;@task&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;transform&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;list&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;]:&lt;/span&gt;
    &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="p"&gt;{}&lt;/span&gt;
    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt; &lt;span class="ow"&gt;in&lt;/span&gt; &lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]]&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;get&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;country&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;],&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="o"&gt;+&lt;/span&gt; &lt;span class="n"&gt;r&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;amount&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;


&lt;span class="nd"&gt;@task&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;load&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nb"&gt;dict&lt;/span&gt;&lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="nb"&gt;str&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;float&lt;/span&gt;&lt;span class="p"&gt;])&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;int&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="k"&gt;with&lt;/span&gt; &lt;span class="n"&gt;sqlite3&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;connect&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;DB&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;as&lt;/span&gt; &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
        &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;execute&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;CREATE TABLE IF NOT EXISTS revenue (country TEXT PRIMARY KEY, total REAL)&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
        &lt;span class="n"&gt;con&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;executemany&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;INSERT OR REPLACE INTO revenue VALUES (?, ?)&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;items&lt;/span&gt;&lt;span class="p"&gt;())&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="nf"&gt;len&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;totals&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;


&lt;span class="nd"&gt;@flow&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;log_prints&lt;/span&gt;&lt;span class="o"&gt;=&lt;/span&gt;&lt;span class="bp"&gt;True&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;def&lt;/span&gt; &lt;span class="nf"&gt;orders_pipeline&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt; &lt;span class="o"&gt;-&amp;gt;&lt;/span&gt; &lt;span class="nb"&gt;int&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="n"&gt;rows&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nf"&gt;load&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;transform&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="nf"&gt;extract&lt;/span&gt;&lt;span class="p"&gt;()))&lt;/span&gt;
    &lt;span class="nf"&gt;print&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="sa"&gt;f&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;loaded &lt;/span&gt;&lt;span class="si"&gt;{&lt;/span&gt;&lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="si"&gt;}&lt;/span&gt;&lt;span class="s"&gt; rows&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
    &lt;span class="k"&gt;return&lt;/span&gt; &lt;span class="n"&gt;rows&lt;/span&gt;


&lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="n"&gt;__name__&lt;/span&gt; &lt;span class="o"&gt;==&lt;/span&gt; &lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="s"&gt;__main__&lt;/span&gt;&lt;span class="sh"&gt;"&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="nf"&gt;orders_pipeline&lt;/span&gt;&lt;span class="p"&gt;()&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Full output:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;07:22:44.864 | INFO    | prefect - Starting temporary server on http://127.0.0.1:8282
See https://docs.prefect.io/v3/concepts/server#how-to-guides for more information on running a dedicated Prefect server.
07:22:51.455 | INFO    | Flow run 'fierce-kagu' - Beginning flow run 'fierce-kagu' for flow 'orders-pipeline'
07:22:51.533 | INFO    | Task run 'extract-d55' - Finished in state Completed()
07:22:51.538 | INFO    | Task run 'transform-5d2' - Finished in state Completed()
07:22:51.549 | INFO    | Task run 'load-185' - Finished in state Completed()
07:22:51.550 | INFO    | Flow run 'fierce-kagu' - loaded 2 rows
07:22:52.532 | INFO    | Flow run 'fierce-kagu' - Finished in state Completed()
07:22:52.563 | INFO    | prefect - Stopping temporary server on http://127.0.0.1:8282
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Calling the flow is just calling a Python function. With no server configured, Prefect started a temporary local API server for the run and stopped it afterwards. Data passed between tasks as ordinary return values in one process.&lt;/p&gt;

&lt;p&gt;What the three snippets show: the Airflow and Prefect versions look almost identical, but Airflow parses the file into a Dag the scheduler stores and runs task by task, while Prefect discovers the graph as the function executes. Dagster's version names &lt;strong&gt;things that exist&lt;/strong&gt; (&lt;code&gt;raw_orders&lt;/code&gt;, &lt;code&gt;country_revenue&lt;/code&gt;, &lt;code&gt;revenue_table&lt;/code&gt;) instead of &lt;strong&gt;steps that run&lt;/strong&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Programming model
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Airflow: Dags and tasks.&lt;/strong&gt; A Dag declares tasks and their dependencies; the Dag processor parses the file and stores a serialized version, and the scheduler creates runs of it. The graph is fixed when the file is parsed, with dynamic task mapping for fan-out at run time. Since Airflow 3, assets are first-class: a task can declare &lt;code&gt;outlets&lt;/code&gt;, a Dag can be scheduled on assets, and the &lt;code&gt;@asset&lt;/code&gt; decorator is shorthand for a Dag with one task that updates one asset. Assets are still attached to tasks; the task is the primary unit.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Dagster: software-defined assets.&lt;/strong&gt; The Dagster docs define an asset as an object in persistent storage (a table, file or model) and an asset definition as code describing what should exist and how to produce it. Dependencies are declared on the assets, so the asset graph exists before anything runs, and "run this pipeline" becomes "materialize this selection of assets". Dagster also has ops, graphs and jobs for task-style work, but you do not need them to use assets.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Prefect: flows and tasks.&lt;/strong&gt; Flows and tasks are decorated Python functions, and dependencies are whatever your code does: function calls, loops, &lt;code&gt;if&lt;/code&gt; statements. Calling a task runs it in the caller's thread by default; &lt;code&gt;.submit()&lt;/code&gt; and &lt;code&gt;.map()&lt;/code&gt; send it to a task runner for concurrency (a thread pool by default, process pools, or Dask and Ray through extras). The graph is recorded at run time rather than declared up front, which makes data-dependent branching natural and makes "what will this run do?" harder to answer before it runs.&lt;/p&gt;

&lt;h2&gt;
  
  
  Scheduling and event triggers
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Airflow 3&lt;/th&gt;
&lt;th&gt;Dagster 1.x&lt;/th&gt;
&lt;th&gt;Prefect 3&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Time-based&lt;/td&gt;
&lt;td&gt;Cron and presets, timetables, data intervals with catchup and backfill&lt;/td&gt;
&lt;td&gt;Schedules (cron) on jobs or asset selections; time-partitioned assets&lt;/td&gt;
&lt;td&gt;Schedules (cron, interval, RRule) on deployments&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Data-aware&lt;/td&gt;
&lt;td&gt;Dags scheduled on asset updates, combined with AND and OR conditions&lt;/td&gt;
&lt;td&gt;Declarative Automation: conditions on assets such as &lt;code&gt;on_cron&lt;/code&gt;, &lt;code&gt;eager&lt;/code&gt;, &lt;code&gt;on_missing&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;Deployment triggers: run a deployment when matching events arrive, for example another flow run completing&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;External events&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;AssetWatcher&lt;/code&gt; on an asset with an event trigger, for example on a message queue (added in 3.0)&lt;/td&gt;
&lt;td&gt;Sensors (polling with cursors and run keys); pushing through the GraphQL API&lt;/td&gt;
&lt;td&gt;Automations on events; webhooks in Prefect Cloud&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Absence of events&lt;/td&gt;
&lt;td&gt;Deadline Alerts on Dag runs (experimental since 3.1)&lt;/td&gt;
&lt;td&gt;Freshness policies (still under active development) with freshness alerts&lt;/td&gt;
&lt;td&gt;Proactive triggers: fire when an expected event does &lt;em&gt;not&lt;/em&gt; happen&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Two details catch people out. Airflow's event-driven scheduling only accepts triggers that inherit from &lt;code&gt;BaseEventTrigger&lt;/code&gt;, not every deferrable trigger. Dagster's Declarative Automation only acts once the default automation condition sensor is switched on in the UI. For Airflow's interval semantics (logical date, data interval, catchup), see &lt;a href="https://datadank.com/airflow/dags-scheduling-retries/" rel="noopener noreferrer"&gt;scheduling and assets&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Data awareness and lineage
&lt;/h2&gt;

&lt;p&gt;This is where the tools differ most.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Dagster&lt;/strong&gt; puts the asset graph at the centre of the UI: each asset shows its upstreams, last materialization, metadata and &lt;strong&gt;asset checks&lt;/strong&gt; (data-quality tests attached to an asset). The dbt integration loads each dbt model as an asset, so dbt models and Python assets appear in one graph and can be selected together. Dagster+ adds an asset catalog with column-level lineage.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Airflow&lt;/strong&gt; tracks assets by URI and shows which Dags produce and consume them; asset events can carry extra metadata (for example a row count). For lineage across systems, the OpenLineage provider emits lineage events to an OpenLineage-compatible backend.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Prefect&lt;/strong&gt; models assets with the &lt;code&gt;@materialize&lt;/code&gt; decorator and a URI key, infers dependencies from how materializations feed each other, and tracks them across workflows. Its documentation marks assets as a Prefect Cloud feature, so check availability before planning around it on a self-hosted server.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;If lineage and "is this table fresh and tested?" are the questions your stakeholders ask most, that favours Dagster's model. If they ask "did last night's run succeed?", all three answer it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Local development and testing
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Airflow&lt;/strong&gt;: &lt;code&gt;dag.test()&lt;/code&gt; runs a whole Dag in one process for debugging, but it needs an initialised metadata database and a Dag inside a Dag bundle (the Dags folder by default). Unit tests usually call the task's Python function or load the &lt;code&gt;DagBag&lt;/code&gt; to check that files import. The full approach is in &lt;a href="https://datadank.com/airflow/best-practices-testing-cicd/" rel="noopener noreferrer"&gt;testing and CI/CD&lt;/a&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Dagster&lt;/strong&gt;: assets can be called directly in unit tests with plain inputs and mocked resources, and &lt;code&gt;materialize()&lt;/code&gt; runs a selection in process, as in the example. &lt;code&gt;dg dev&lt;/code&gt; starts the local UI on a project scaffolded with &lt;code&gt;create-dagster&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Prefect&lt;/strong&gt;: flows and tasks are called like functions; &lt;code&gt;prefect_test_harness()&lt;/code&gt; runs them against a temporary database in tests. The example ran with nothing installed beyond &lt;code&gt;pip install prefect&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Prefect and Dagster have the shorter loop from "edit" to "see it run". Airflow's loop got shorter with &lt;code&gt;dag.test()&lt;/code&gt;, but you still carry a metadata database and the parse-then-run model, which matters for tests that build Dags in memory.&lt;/p&gt;

&lt;h2&gt;
  
  
  Deployment and scaling
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Airflow 3&lt;/th&gt;
&lt;th&gt;Dagster OSS&lt;/th&gt;
&lt;th&gt;Prefect 3&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Long-running services&lt;/td&gt;
&lt;td&gt;Scheduler, Dag processor, API server, metadata database; triggerer for deferrable tasks; workers depending on executor&lt;/td&gt;
&lt;td&gt;Webserver, daemon (schedules, sensors, run queue), code location servers, database&lt;/td&gt;
&lt;td&gt;Prefect server (API, UI) with its database; workers for hybrid work pools&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;How work is placed&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;Executor&lt;/strong&gt; in the scheduler: Local, Celery, Kubernetes, Edge, AWS ECS and others; several executors at once since 2.10&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;Run launcher&lt;/strong&gt; starts a run worker (process, Docker, Kubernetes job, ECS task); an &lt;strong&gt;executor&lt;/strong&gt; inside the run runs steps (multiprocess by default, or a pod or task per step)&lt;/td&gt;
&lt;td&gt;
&lt;strong&gt;Work pool&lt;/strong&gt; holds infrastructure config; a typed &lt;strong&gt;worker&lt;/strong&gt; polls it and starts each flow run (process, Docker, Kubernetes, ECS, Cloud Run and others)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Granularity of isolation&lt;/td&gt;
&lt;td&gt;Per task&lt;/td&gt;
&lt;td&gt;Per run, or per step with a step executor&lt;/td&gt;
&lt;td&gt;Per flow run; tasks run inside it&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;User code isolation&lt;/td&gt;
&lt;td&gt;Task SDK: tasks talk to the API server, not the metadata database&lt;/td&gt;
&lt;td&gt;Code locations run in their own processes, separate from the webserver and daemon&lt;/td&gt;
&lt;td&gt;Flow code is pulled from an image, Git or storage by the worker&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;The isolation row matters for cost and reliability. Airflow isolates tasks (with the KubernetesExecutor each task is its own pod); Prefect isolates flow runs, and tasks inside one run share its process and memory; Dagster defaults to a process per step inside a run and can go to a pod per step. In all three, heavy computation should still run in Spark, the warehouse or a container the task launches, not in the orchestrator's workers. Scaling the Airflow side is covered in &lt;a href="https://datadank.com/airflow/executors-scaling/" rel="noopener noreferrer"&gt;executors, pools and scaling&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Managed offerings
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Airflow&lt;/strong&gt;: no first-party managed service from the Apache project. The Airflow ecosystem page lists Astronomer's Astro, Google Cloud Composer, Amazon MWAA and Azure's managed Airflow, among others. Each runs its own release schedule and supported versions, so check whether the Airflow 3 version you need is available.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Dagster+&lt;/strong&gt;: run by the Dagster company in two forms, &lt;strong&gt;Serverless&lt;/strong&gt; (your code runs in Dagster's environment) and &lt;strong&gt;Hybrid&lt;/strong&gt; (Dagster+ runs the control plane, your agent and code run in your infrastructure). It adds Insights (cost and performance trends), alerting, RBAC and audit logs, the asset catalog and branch deployments.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Prefect Cloud&lt;/strong&gt;: a hosted control plane that, per the self-hosting docs, adds organisation features such as RBAC, audit logs and SSO on top of what a self-hosted server provides. Besides hybrid work pools with your own workers, it offers &lt;strong&gt;push&lt;/strong&gt; work pools (runs submitted straight to serverless cloud infrastructure, no worker) and &lt;strong&gt;Prefect Managed&lt;/strong&gt; pools (runs on Prefect's infrastructure).&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;All three can be self-hosted. The real question is who upgrades, scales and secures the control plane, and whether your company already buys one of these services.&lt;/p&gt;

&lt;h2&gt;
  
  
  Ecosystem and providers
&lt;/h2&gt;

&lt;p&gt;Airflow's ecosystem is the largest and oldest: more than 80 community-maintained provider packages (cloud services, databases, warehouses, dbt Cloud, Kafka, Spark and more) that bundle hooks, operators, sensors and asset URI schemes, plus third-party ones. Dagster's integration libraries (&lt;code&gt;dagster-dbt&lt;/code&gt;, &lt;code&gt;dagster-k8s&lt;/code&gt;, &lt;code&gt;dagster-aws&lt;/code&gt; and many more) are built around assets, for example loading dbt models as assets. Prefect's integrations are PyPI packages (&lt;code&gt;prefect-aws&lt;/code&gt;, &lt;code&gt;prefect-gcp&lt;/code&gt;, &lt;code&gt;prefect-kubernetes&lt;/code&gt;, and others) that add blocks, tasks and worker types; because a Prefect task is any function, a plain SDK call is often all you need.&lt;/p&gt;

&lt;p&gt;If you depend on a specific operator (a vendor's transfer operator, a mature sensor), check that it exists in the other tool before assuming parity.&lt;/p&gt;

&lt;h2&gt;
  
  
  Observability
&lt;/h2&gt;

&lt;p&gt;All three UIs show run history, per-step logs and states, and retries. The difference is the default view: Airflow centres on &lt;strong&gt;Dags and task instances&lt;/strong&gt; (grid and graph views per run); Dagster on the &lt;strong&gt;asset graph and asset health&lt;/strong&gt;, with runs underneath; Prefect on &lt;strong&gt;flow runs and their states&lt;/strong&gt;, with automations that turn state changes (or a run stuck in Running) into notifications or actions. For alerting design that applies to any of them, see &lt;a href="https://datadank.com/etl-elt/pipeline-observability/" rel="noopener noreferrer"&gt;pipeline observability&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Comparison table
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;Airflow 3&lt;/th&gt;
&lt;th&gt;Dagster 1.x&lt;/th&gt;
&lt;th&gt;Prefect 3&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Unit you model&lt;/td&gt;
&lt;td&gt;Task in a Dag&lt;/td&gt;
&lt;td&gt;Data asset&lt;/td&gt;
&lt;td&gt;Python function (flow, task)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Graph defined&lt;/td&gt;
&lt;td&gt;At parse time (plus dynamic mapping)&lt;/td&gt;
&lt;td&gt;Before run, from asset dependencies&lt;/td&gt;
&lt;td&gt;At run time, by your code&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Data passing&lt;/td&gt;
&lt;td&gt;XCom for small values; data in external storage&lt;/td&gt;
&lt;td&gt;IO managers, or write yourself and return metadata&lt;/td&gt;
&lt;td&gt;Return values in the flow's process&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Data-aware scheduling&lt;/td&gt;
&lt;td&gt;Assets, asset expressions, asset watchers&lt;/td&gt;
&lt;td&gt;Declarative Automation, asset sensors&lt;/td&gt;
&lt;td&gt;Event triggers on deployments&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Lineage&lt;/td&gt;
&lt;td&gt;Asset graph; OpenLineage provider&lt;/td&gt;
&lt;td&gt;Asset graph, asset checks; column lineage in Dagster+&lt;/td&gt;
&lt;td&gt;Assets (Prefect Cloud)&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Local run&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;dag.test()&lt;/code&gt; with a metadata DB&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;materialize()&lt;/code&gt;, &lt;code&gt;dg dev&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;Call the flow&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Execution placement&lt;/td&gt;
&lt;td&gt;Executors (Local, Celery, Kubernetes, Edge, ECS)&lt;/td&gt;
&lt;td&gt;Run launchers plus executors&lt;/td&gt;
&lt;td&gt;Work pools and workers; push and managed pools in Cloud&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Managed options&lt;/td&gt;
&lt;td&gt;Astro, Cloud Composer, MWAA and others&lt;/td&gt;
&lt;td&gt;Dagster+ Serverless and Hybrid&lt;/td&gt;
&lt;td&gt;Prefect Cloud&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Ecosystem&lt;/td&gt;
&lt;td&gt;Largest provider ecosystem&lt;/td&gt;
&lt;td&gt;Strong on dbt and the modern data stack&lt;/td&gt;
&lt;td&gt;Python packages; any SDK works in a task&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Migration cost
&lt;/h2&gt;

&lt;p&gt;Switching orchestrators is rarely about the decorator syntax; it is everything around it.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Rewriting definitions.&lt;/strong&gt; Each Dag, operator and sensor needs an equivalent. Task-to-task moves (Airflow to Prefect) map fairly directly; Prefect's migration guide maps Dags to flows, operators to tasks, executors to work pools and workers, and XComs to return values. Moving to Dagster means remodelling tasks as assets, which is more work and also where the benefit is.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Scheduling semantics.&lt;/strong&gt; Airflow's data intervals, catchup and backfills by logical date have no one-to-one equivalent; Dagster uses partitions, Prefect passes parameters. Any task that relies on &lt;code&gt;data_interval_start&lt;/code&gt; must be re-parameterised, and that is where silent bugs come from.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Connections and secrets.&lt;/strong&gt; Airflow Connections and Variables become Dagster resources or Prefect blocks and settings.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Operations.&lt;/strong&gt; New deployment, CI/CD, alerting, access control and on-call runbooks.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Running both.&lt;/strong&gt; Dagster documents an &lt;code&gt;AirflowInstanceComponent&lt;/code&gt; that peers with an existing Airflow instance so Airflow Dags appear in Dagster while you migrate incrementally. Running two orchestrators for a while is normal; plan the cut-over per pipeline, not as one big switch.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Staying put and upgrading is a legitimate option too: Airflow 2 to 3 brings assets, event-driven scheduling and the Task SDK without changing tools (see the &lt;a href="https://datadank.com/interview/airflow/airflow-2-to-3-migration/" rel="noopener noreferrer"&gt;Airflow 2 to 3 migration question&lt;/a&gt;).&lt;/p&gt;

&lt;h2&gt;
  
  
  When to choose each
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;If your situation is mostly...&lt;/th&gt;
&lt;th&gt;Leaning&lt;/th&gt;
&lt;th&gt;Why&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Many teams, many systems, lots of time-partitioned batch jobs&lt;/td&gt;
&lt;td&gt;Airflow&lt;/td&gt;
&lt;td&gt;Mature scheduling semantics, largest provider ecosystem, managed options on every major cloud&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;An analytics platform built around tables, dbt models and their freshness and quality&lt;/td&gt;
&lt;td&gt;Dagster&lt;/td&gt;
&lt;td&gt;Assets, asset checks and lineage are the core model; dbt models become assets&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Python-heavy, dynamic workflows (ML, API-driven jobs, branching on data), small platform team&lt;/td&gt;
&lt;td&gt;Prefect&lt;/td&gt;
&lt;td&gt;Plain functions, graph built at run time, low ceremony to go from script to scheduled deployment&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;A large, working Airflow estate&lt;/td&gt;
&lt;td&gt;Airflow (upgrade to 3)&lt;/td&gt;
&lt;td&gt;Migration cost usually outweighs feature differences; adopt assets incrementally&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;A handful of jobs that all live on one platform&lt;/td&gt;
&lt;td&gt;That platform's scheduler&lt;/td&gt;
&lt;td&gt;Databricks jobs, dbt Cloud or &lt;a href="https://datadank.com/aws/step-functions/" rel="noopener noreferrer"&gt;Step Functions&lt;/a&gt; may be enough&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Common mistakes
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Comparing Airflow 2 or Prefect 2 (with agents) against current Dagster. Compare current versions.&lt;/li&gt;
&lt;li&gt;Treating Dagster as "Airflow with a different decorator" and then writing one asset per step; you lose the asset graph's value.&lt;/li&gt;
&lt;li&gt;Assuming Prefect tasks are isolated like Airflow tasks on Kubernetes; by default they share the flow run's process.&lt;/li&gt;
&lt;li&gt;Passing large data between steps (XComs in Airflow, pickled values through Dagster's default IO manager, big in-memory objects in Prefect) instead of writing to storage.&lt;/li&gt;
&lt;li&gt;Choosing on the developer loop alone and ignoring who will operate the control plane, upgrades and on-call.&lt;/li&gt;
&lt;li&gt;Forgetting that idempotency is your job in all three: retries and reruns are only safe if each step can run twice (&lt;a href="https://datadank.com/etl-elt/idempotency-in-data-pipelines/" rel="noopener noreferrer"&gt;idempotency in pipelines&lt;/a&gt;).&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  In an interview
&lt;/h2&gt;

&lt;p&gt;For "Airflow, Dagster or Prefect?", give the framework before the answer: what you model (tasks, assets, functions), how runs are triggered, lineage and data quality needs, how the team develops and tests, who operates it, the ecosystem you depend on, and migration cost. Then commit to a recommendation &lt;strong&gt;for the scenario given&lt;/strong&gt;. For example: "For a dbt-centred analytics platform where freshness and tests matter most, I would lean Dagster for its asset graph and checks; if the company already runs 300 Airflow Dags, I would upgrade to Airflow 3 and use assets instead of migrating." Expect follow-ups on Airflow's data intervals versus partitions, how each isolates work, and what you would never run inside an orchestrator worker. A shorter version of this answer is the &lt;a href="https://datadank.com/interview/data-engineering/choose-an-orchestrator/" rel="noopener noreferrer"&gt;choosing an orchestrator interview question&lt;/a&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Key takeaway
&lt;/h2&gt;

&lt;p&gt;Airflow orchestrates &lt;strong&gt;tasks&lt;/strong&gt;, Dagster orchestrates &lt;strong&gt;data assets&lt;/strong&gt;, Prefect orchestrates &lt;strong&gt;Python functions&lt;/strong&gt;. All three are production-ready in their current versions. Choose by the model that matches how your team thinks about its pipelines, the operations you can carry or buy, the integrations you depend on, and what it would cost to move what you already run.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://datadank.com/airflow/airflow-vs-dagster-vs-prefect/" rel="noopener noreferrer"&gt;DataDank&lt;/a&gt;, a free Data Engineering learning site with a &lt;a href="https://datadank.com/planner/90-day-plan/" rel="noopener noreferrer"&gt;90-day interview plan&lt;/a&gt; and &lt;a href="https://datadank.com/interview/questions/" rel="noopener noreferrer"&gt;377+ interview questions&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>dataengineering</category>
      <category>airflow</category>
      <category>python</category>
      <category>devops</category>
    </item>
  </channel>
</rss>
