<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ignacy Janiszewski on Medium]]></title>
        <description><![CDATA[Stories by Ignacy Janiszewski on Medium]]></description>
        <link>https://medium.com/@ijaniszewski?source=rss-722a326ed2be------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*2qPKqE9PV-r2V20gIA6CDQ.jpeg</url>
            <title>Stories by Ignacy Janiszewski on Medium</title>
            <link>https://medium.com/@ijaniszewski?source=rss-722a326ed2be------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 05:55:04 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@ijaniszewski/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Apache Iceberg: A Lightweight Local Lab]]></title>
            <link>https://ijaniszewski.medium.com/apache-iceberg-a-lightweight-local-lab-517cbf6a88f5?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/517cbf6a88f5</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[parquet]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Wed, 08 Jul 2026 18:24:46 GMT</pubDate>
            <atom:updated>2026-07-08T18:24:46.736Z</atom:updated>
            <content:encoded><![CDATA[<h4>Iceberg sounds like a big platform topic. Catalogs, snapshots, manifests, metadata files, table formats. A lot of words before we even see a row.</h4><p>So in this article I want to do it differently:</p><ol><li>explain why Iceberg exists,</li><li>create a tiny local Iceberg table,</li><li>inspect what changed on disk,</li><li>compare Iceberg with Delta Lake,</li><li>show how this idea maps to a real production-style setup.</li></ol><p><a href="https://github.com/ijaniszewski/data-engineering-field-notes/tree/main/articles/01_iceberg_lightweight">Here’s the code.</a></p><p>If you prefer running this interactively, open articles/01_iceberg_lightweight/notebook.py in VS Code. It uses # %% cells, so you can run the article step by step and see results directly below each cell.</p><h3>Why Iceberg exists</h3><p>Imagine we write analytics data as Parquet files:</p><pre>orders/<br>├── part-0001.parquet<br>├── part-0002.parquet<br>└── part-0003.parquet</pre><p>This is fine until real life starts:</p><ul><li>one job appends files while another job reads,</li><li>a schema changes,</li><li>we need to know which files belong to a successful commit,</li><li>we want to roll back,</li><li>multiple engines need to read the same table consistently.</li></ul><p>Plain Parquet is a file format. It stores rows efficiently, but it does not define table history, transactions, snapshots, or schema evolution rules.</p><p>Iceberg adds that missing table layer:</p><pre>catalog  -&gt; pointer to the current table metadata<br>metadata -&gt; schema, snapshots, manifests, table history<br>data     -&gt; Parquet files with actual rows</pre><p>That is the mental model for the whole article. Iceberg is not replacing Parquet. <strong>Iceberg makes Parquet files behave like a reliable analytical table.</strong></p><h3>Setup</h3><p>Install dependencies:</p><pre>python3 -m venv .venv<br>source .venv/bin/activate<br>pip install -r requirements.txt</pre><p>We will use this script:</p><pre>articles/01_iceberg_lightweight/iceberg_playground.py</pre><p>It exposes a few small steps:</p><pre>python articles/01_iceberg_lightweight/iceberg_playground.py reset<br>python articles/01_iceberg_lightweight/iceberg_playground.py create<br>python articles/01_iceberg_lightweight/iceberg_playground.py append<br>python articles/01_iceberg_lightweight/iceberg_playground.py append-more<br>python articles/01_iceberg_lightweight/iceberg_playground.py show</pre><h3>Step 1: create an empty Iceberg table</h3><p>Run:</p><pre>python articles/01_iceberg_lightweight/iceberg_playground.py reset<br>python articles/01_iceberg_lightweight/iceberg_playground.py create</pre><p>The important code is:</p><pre>catalog = load_catalog(<br>    &quot;local&quot;,<br>    type=&quot;sql&quot;,<br>    uri=f&quot;sqlite:///{CATALOG_DB}&quot;,<br>    warehouse=f&quot;file://{WAREHOUSE}&quot;,<br>)</pre><pre>table = catalog.create_table(TABLE_ID, schema=schema)</pre><p>In production, the catalog could be Snowflake, AWS Glue, Hive Metastore, a REST catalog, or another service. Locally, SQLite is enough to show the idea: the catalog stores where the current table metadata is.</p><p>Now inspect the local warehouse:</p><pre>find local_warehouse -maxdepth 5 -type f | sort</pre><p>You should see a metadata JSON file:</p><pre>local_warehouse/demo/orders/metadata/00000-....metadata.json</pre><p>Read it with jq:</p><pre>cat local_warehouse/demo/orders/metadata/*.metadata.json | jq &#39;{<br>  location,<br>  current_snapshot_id: .&quot;current-snapshot-id&quot;,<br>  schemas,<br>  partition_specs: .&quot;partition-specs&quot;<br>}&#39;</pre><p>At this moment:</p><ul><li>the table exists,</li><li>the schema is stored,</li><li>there is no current snapshot yet,</li><li>no Parquet data file belongs to the table yet.</li></ul><p>This is the first Iceberg idea: even before data exists, the table has metadata.</p><h3>Step 2: create data with Polars</h3><p>Now we create a small batch:</p><pre>first_batch = pl.DataFrame(<br>    {<br>        &quot;order_id&quot;: [1, 2, 3],<br>        &quot;customer&quot;: [&quot;Alice&quot;, &quot;Bob&quot;, &quot;Charlie&quot;],<br>        &quot;amount&quot;: [100.0, 200.0, 300.0],<br>    }<br>)</pre><p>This is where Polars fits naturally. In a real pipeline, this is where I want to:</p><ul><li>parse files,</li><li>cast columns,</li><li>clean weird source values,</li><li>join lookup data,</li><li>add metadata columns,</li><li>prepare the final table shape.</li></ul><p>Iceberg is not the transformation engine. Polars does the data work. Iceberg does the table-state work.</p><p>PyIceberg writes Arrow data, so the boundary is explicit:</p><pre>table.append(first_batch.to_arrow().cast(schema))</pre><p>That small line is the handoff:</p><pre>Polars DataFrame -&gt; Arrow Table -&gt; Iceberg commit</pre><h3>Step 3: append data and inspect files</h3><p>Run:</p><pre>python articles/01_iceberg_lightweight/iceberg_playground.py append</pre><p>You should see:</p><pre>After first append<br> 1  Alice     100.00<br> 2  Bob       200.00<br> 3  Charlie   300.00</pre><p>Now inspect the table directory:</p><pre>find local_warehouse/demo/orders -maxdepth 4 -type f | sort</pre><p>Now there are data files and metadata files:</p><pre>data/*.parquet<br>metadata/*.metadata.json<br>metadata/*.avro</pre><p>The Parquet file stores rows. The metadata files describe that this Parquet file is part of a committed table snapshot.</p><p>Inspect the latest metadata JSON:</p><pre>latest_metadata=$(ls local_warehouse/demo/orders/metadata/*.metadata.json | sort | tail -1)<br>cat &quot;$latest_metadata&quot; | jq &#39;{<br>  current_snapshot_id: .&quot;current-snapshot-id&quot;,<br>  snapshots,<br>  snapshot_log: .&quot;snapshot-log&quot;<br>}&#39;</pre><p>Now the table has a current snapshot. That snapshot points to manifest files, and those manifests point to data files.</p><p>The reader does not need to list the folder and guess what is valid. It follows Iceberg metadata.</p><h3>Step 4: inspect the Parquet file</h3><p>Parquet is binary, so this will not work:</p><pre>cat some_file.parquet | jq</pre><p>jq is useful for Iceberg JSON metadata. For Parquet, use a Parquet-aware tool. Here is a tiny PyArrow inspection:</p><pre>python - &lt;&lt;&#39;PY&#39;<br>from pathlib import Path<br>import pyarrow.parquet as pq<br><br>for parquet_file in sorted(Path(&quot;local_warehouse/demo/orders/data&quot;).rglob(&quot;*.parquet&quot;)):<br>    table = pq.read_table(parquet_file)<br>    print(parquet_file)<br>    print(table.schema)<br>    print(table.to_pylist())<br>PY</pre><p>This makes the split visible:</p><pre>Parquet -&gt; actual rows<br>Iceberg metadata -&gt; which files are part of the table<br>Catalog -&gt; pointer to the current metadata file</pre><h3>Step 5: append again and see snapshots</h3><p>Run:</p><pre>python articles/01_iceberg_lightweight/iceberg_playground.py append-more<br>python articles/01_iceberg_lightweight/iceberg_playground.py show</pre><p>The second batch is again created with Polars:</p><pre>second_batch = (<br>    pl.DataFrame(<br>        {<br>            &quot;order_id&quot;: [4, 5],<br>            &quot;customer&quot;: [&quot;Diana&quot;, &quot;Eve&quot;],<br>            &quot;amount&quot;: [400.0, 500.0],<br>        }<br>    )<br>    .with_columns((pl.col(&quot;amount&quot;) * 1.0).alias(&quot;amount&quot;))<br>    .select(&quot;order_id&quot;, &quot;customer&quot;, &quot;amount&quot;)<br>)</pre><p>Now inspect snapshot history:</p><pre>latest_metadata=$(ls local_warehouse/demo/orders/metadata/*.metadata.json | sort | tail -1)<br>cat &quot;$latest_metadata&quot; | jq &#39;{<br>  current_snapshot_id: .&quot;current-snapshot-id&quot;,<br>  snapshots_count: (.snapshots | length),<br>  snapshots: [.snapshots[] | {<br>    snapshot_id: .&quot;snapshot-id&quot;,<br>    operation: .summary.operation,<br>    added_files: .summary.&quot;added-data-files&quot;,<br>    added_records: .summary.&quot;added-records&quot;<br>  }]<br>}&#39;</pre><p>You should see two snapshots:</p><ul><li>first append added 3 records,</li><li>second append added 2 records.</li></ul><p>This is the core mechanism. Each successful write creates a new snapshot. Readers use a stable snapshot instead of seeing half-written table state.</p><h3>Iceberg vs Delta Lake</h3><p>Iceberg and Delta Lake solve a similar problem: reliable analytical tables on object storage.</p><p>Both give you:</p><ul><li>ACID-style commits,</li><li>schema handling,</li><li>time travel,</li><li>safer reads while writes happen,</li><li>metadata around Parquet files.</li></ul><p>The difference is mainly how metadata is organized.</p><p>Delta Lake uses a transaction log:</p><pre>table/<br>├── _delta_log/<br>│   ├── 00000000000000000000.json<br>│   ├── 00000000000000000001.json<br>│   └── ...<br>└── part-....parquet</pre><p>Each commit adds a new JSON log file. Readers reconstruct table state from the log, often helped by checkpoints.</p><p>Iceberg uses metadata files, manifests, snapshots, and a catalog pointer:</p><pre>table/<br>├── metadata/<br>│   ├── 00000-....metadata.json<br>│   ├── 00001-....metadata.json<br>│   ├── snap-....avro<br>│   └── ....avro<br>└── data/<br>    └── ....parquet</pre><p>The catalog points to the current metadata file. That metadata describes the current snapshot and table history.</p><p>Short version:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/739/1*nHtjKYh4A-leLLikRP2qsQ.png" /></figure><p>This does not mean one is always better. The useful question is: which table contract does your platform support best?</p><h3>How this maps to production</h3><p>A production setup can look like this conceptually:</p><pre>Extractor -&gt; Serializer -&gt; Polars DataFrame -&gt; Loader -&gt; table format -&gt; query engine</pre><p>For example:</p><pre>frame = serializer.deserialize(...)<br>frame = frame.with_columns(...)<br>loader.write(frame)</pre><p>The Loader decides what the write boundary is.</p><p>For a native Iceberg writer, the boundary may look like:</p><pre>table.append(frame.to_arrow().cast(schema))</pre><p>For a Delta-based writer, it may look like:</p><pre>frame.write_delta(path, mode=&quot;append&quot;)</pre><p>In some real platforms, the write side can use Delta files because the Python tooling and merge path are convenient, while the read side exposes the table through an Iceberg-compatible interface in the warehouse/query engine.</p><p>Conceptually:</p><pre>Python + Polars writes table files<br>  ↓<br>table metadata is refreshed<br>  ↓<br>warehouse/query engine exposes a stable analytical table<br>  ↓<br>dbt / BI / analysts query it with SQL</pre><p>The important part is not the exact vendor or storage account. The important part is the separation of responsibilities:</p><pre>Polars        -&gt; shape the data<br>Delta/Iceberg -&gt; track table commits and files<br>Warehouse     -&gt; serve the table to consumers</pre><p>That is why table formats matter. They turn a lake folder into a table contract that multiple tools can understand.</p><h3>Final mental model</h3><p>If you remember only one thing:</p><pre>Parquet stores rows.<br>Polars prepares rows.<br>Iceberg tracks table state.<br>The catalog points readers to the current state.</pre><p>That is Apache Iceberg in its lightweight, practical form.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=517cbf6a88f5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-Time Game Analytics: Building a Star Schema Data Warehouse with Kafka]]></title>
            <link>https://ijaniszewski.medium.com/real-time-game-analytics-building-a-star-schema-data-warehouse-with-kafka-a413f8bc556d?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/a413f8bc556d</guid>
            <category><![CDATA[kafka]]></category>
            <category><![CDATA[data-modeling]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[star-schema]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Sun, 29 Mar 2026 11:21:20 GMT</pubDate>
            <atom:updated>2026-03-29T11:21:47.725Z</atom:updated>
            <content:encoded><![CDATA[<p>Here’s <a href="https://github.com/ijaniszewski/kafka-playground/tree/main/04_game_analytics">the full code</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2korHAsiHGrsNAL1F9El7Q.png" /><figcaption><a href="https://www.geeksforgeeks.org/dbms/star-schema-in-data-warehouse-modeling/">https://www.geeksforgeeks.org/dbms/star-schema-in-data-warehouse-modeling/</a></figcaption></figure><h3>Intro</h3><p>Every second, millions of mobile game players tap, swipe, and make purchases. Each interaction generates an event. A puzzle completed. A power-up bought. An ad watched for extra lives. But here’s the challenge: how do you turn this tsunami of events into answers that drive business decisions?</p><p>Traditional databases struggle with this. By the time you’ve aggregated yesterday’s data, your players have moved on. You need real-time analytics that can answer questions like: <em>Why did players quit at puzzle level 15? Which country has the highest refund rate? Is our Valentine’s Day promotion working right now?</em></p><p>This is where event streaming meets dimensional modeling. In this hands-on guide, we’ll build a complete analytics pipeline for <strong>Crystal Quest</strong>, a fictional puzzle game. You’ll learn how to design a star schema that handles real-time game events, tricky edge cases like refunds and device changes, and historical tracking with SCD Type 2.</p><p><strong>What you’ll build:</strong></p><ul><li>Two Kafka topics streaming player activity and purchases</li><li>A star schema data warehouse optimized for analytics</li><li>Real-time ETL pipeline from Kafka to PostgreSQL</li><li>SQL queries that calculate revenue, retention, and conversion rates</li></ul><p><strong>Prerequisites:</strong> Docker, Python, basic SQL knowledge.<br><strong>Time:</strong> ~30 minutes to set up, lifetime of insight to gain.</p><p>Let’s dive in.</p><h3>The Business Problem</h3><p>Meet <strong>Crystal Quest</strong> — a puzzle game with millions of daily players. The product team needs to answer three critical questions:</p><p><strong>1. Player Engagement:</strong> How long do players stay? Which puzzles cause drop-offs?</p><p><strong>2. Monetization:</strong> What’s our conversion rate? Revenue per user? Refund rate by item?</p><p><strong>3. User Behavior:</strong> Do iOS users spend more than Android? What happens when players upgrade devices?</p><p>The data arrives as two event streams:</p><pre>// puzzle_game_activity<br>{<br>  &quot;event_type&quot;: &quot;puzzle_complete&quot;,<br>  &quot;user_id&quot;: &quot;u_102334&quot;,<br>  &quot;session_id&quot;: &quot;play_a1b2c3d4e5f6&quot;,<br>  &quot;event_ts&quot;: &quot;2026-02-07T10:15:30Z&quot;,<br>  &quot;metadata&quot;: {<br>    &quot;puzzle_id&quot;: 15,<br>    &quot;result&quot;: &quot;solved&quot;,<br>    &quot;duration_ms&quot;: 45000<br>  }<br>}<br><br>// puzzle_game_transactions<br>{<br>  &quot;purchase_id&quot;: &quot;tx_774400a1&quot;,<br>  &quot;user_id&quot;: &quot;u_102334&quot;,<br>  &quot;item_id&quot;: &quot;com.crystal_quest.coin_pack_medium&quot;,<br>  &quot;price_usd&quot;: 4.99,<br>  &quot;is_refund&quot;: false,<br>  &quot;transaction_ts&quot;: &quot;2026-02-07T10:18:42Z&quot;<br>}</pre><p><strong>The challenge:</strong> These are raw events. To answer business questions, we need to:</p><ul><li>Aggregate events into sessions</li><li>Join purchases to sessions (time-based)</li><li>Track user profile changes over time (device upgrades)</li><li>Handle refunds correctly (don’t just UPDATE the original transaction)</li></ul><p>Enter: <strong>Star Schema</strong> + <strong>Event Sourcing</strong>.</p><h3>Architecture Overview</h3><p>Here’s what we’re building:</p><pre>┌─────────────────┐<br>│  Game Clients   │  Events generated on mobile devices<br>│  (iOS/Android)  │<br>└────────┬────────┘<br>         │<br>         ↓<br>┌─────────────────────────────────────┐<br>│          Apache Kafka               │<br>│  ┌─────────────────────────────┐    │<br>│  │ puzzle_game_activity        │    │  Partitioned by user_id<br>│  │ puzzle_game_transactions    │    │  (guarantees ordering)<br>│  └─────────────────────────────┘    │<br>└────────┬────────────────────────────┘<br>         │<br>         ↓<br>┌─────────────────┐<br>│  Python ETL     │  Consumes events, aggregates sessions<br>│   Consumer      │  Handles SCD Type 2, links purchases<br>└────────┬────────┘<br>         │<br>         ↓<br>┌─────────────────────────────────────┐<br>│        PostgreSQL                   │<br>│  Star Schema:                       │<br>│  • fact_session (1 row = 1 session) │<br>│  • fact_purchase (1 row = 1 tx)     │<br>│  • dim_user (SCD Type 2)            │<br>│  • dim_game, dim_item, dim_device   │<br>└────────┬────────────────────────────┘<br>         │<br>         ↓<br>    Analytics!</pre><p><strong>Key decisions:</strong></p><ol><li><strong>Partitioning by user_id</strong>: Events for the same player always go to the same Kafka partition. This guarantees ordering — critical when session_start must arrive before puzzle_complete.</li><li><strong>Separate fact tables</strong>: Sessions and purchases have different grain (1 session has many events, 1 purchase = 1 transaction). Keeping them separate simplifies queries.</li><li><strong>Event Sourcing for refunds</strong>: Instead of UPDATE, we insert a new row with the same purchase_id but is_refund=true. Immutable history = easier debugging.</li><li><strong>SCD Type 2 for users</strong>: When a player upgrades from Android to iPhone, we create a new row in dim_user with a new effective_start_date. Now we can analyze &quot;purchases made on old device vs new device.&quot;</li></ol><h3>The Star Schema</h3><p>Let’s design the data model. The most critical question: <strong>What’s the grain?</strong> (What does one row represent?)</p><h4>Fact Tables</h4><p><strong>fact_session<br></strong>Grain: <strong>1 row per play session</strong></p><pre>CREATE TABLE fact_session (<br>    session_sk BIGSERIAL PRIMARY KEY,<br>    session_id VARCHAR(100) UNIQUE NOT NULL,<br>    <br>    -- Foreign keys to dimensions<br>    user_sk BIGINT NOT NULL,<br>    game_sk BIGINT NOT NULL,<br>    device_sk BIGINT NOT NULL,<br>    start_date_sk INTEGER NOT NULL,<br>    <br>    -- Measures (additive)<br>    duration_seconds INTEGER,<br>    puzzles_started INTEGER DEFAULT 0,<br>    puzzles_completed INTEGER DEFAULT 0,<br>    puzzles_solved INTEGER DEFAULT 0,<br>    max_puzzle_reached INTEGER,<br>    rewards_claimed INTEGER DEFAULT 0,<br>    <br>    -- Timestamps<br>    session_start_ts TIMESTAMP NOT NULL,<br>    session_end_ts TIMESTAMP<br>);</pre><p><strong>fact_purchase<br></strong>Grain: <strong>1 row per transaction</strong> (including refunds!)</p><pre>CREATE TABLE fact_purchase (<br>    purchase_sk BIGSERIAL PRIMARY KEY,<br>    purchase_id VARCHAR(100) NOT NULL,  -- NOT UNIQUE! Same ID for refund<br>    <br>    -- Foreign keys<br>    user_sk BIGINT NOT NULL,<br>    item_sk BIGINT NOT NULL,<br>    session_sk BIGINT,  -- NULL if purchase outside gameplay<br>    <br>    -- Measures<br>    amount_local DECIMAL(10, 2) NOT NULL,<br>    amount_usd DECIMAL(10, 2) NOT NULL,  -- Normalized currency<br>    <br>    -- Critical flag<br>    is_refund BOOLEAN NOT NULL DEFAULT FALSE,<br>    <br>    transaction_ts TIMESTAMP NOT NULL<br>);</pre><p><strong>Why not UNIQUE on purchase_id?</strong> Because refunds reuse the same ID:</p><pre>-- Original purchase<br>INSERT INTO fact_purchase (purchase_id, amount_usd, is_refund) <br>VALUES (&#39;tx_123&#39;, 4.99, FALSE);<br><br>-- Refund (5 hours later)<br>INSERT INTO fact_purchase (purchase_id, amount_usd, is_refund) <br>VALUES (&#39;tx_123&#39;, 4.99, TRUE);  -- Same ID, different row!</pre><h4>Dimension Tables</h4><p><strong>dim_user<br></strong>SCD Type 2 for device changes</p><pre>CREATE TABLE dim_user (<br>    user_sk BIGSERIAL PRIMARY KEY,<br>    user_id VARCHAR(100) NOT NULL,  -- Natural key<br>    device_sk BIGINT,<br>    country_code VARCHAR(3),<br>    <br>    -- SCD Type 2 fields<br>    effective_start_date TIMESTAMP NOT NULL,<br>    effective_end_date TIMESTAMP,  -- NULL = current record<br>    is_current BOOLEAN NOT NULL DEFAULT TRUE<br>);</pre><p><strong>Example. </strong>User upgrades phone:</p><pre>-- Before upgrade (closed)<br>user_sk=1, user_id=&#39;u_100001&#39;, device_sk=5 (Android), <br>effective_start_date=&#39;2026-01-01&#39;, effective_end_date=&#39;2026-02-05&#39;, is_current=FALSE<br><br>-- After upgrade (current)<br>user_sk=2, user_id=&#39;u_100001&#39;, device_sk=8 (iPhone), <br>effective_start_date=&#39;2026-02-05&#39;, effective_end_date=NULL, is_current=TRUE</pre><p><strong>4. Other dimensions</strong> (straightforward lookup tables):</p><ul><li>dim_game - Game catalog</li><li>dim_item - IAP items (coin packs, subscriptions)</li><li>dim_device - Device models (iPhone15,2, Pixel 8 Pro)</li><li>dim_date - Time dimension for aggregations</li></ul><h3>Generating Events &amp; Kafka Producer</h3><p>Let’s simulate realistic game events. Our producer sends to two topics, partitioned by user_id:</p><pre>from confluent_kafka import Producer<br><br>class GameAnalyticsProducer:<br>    GAMEPLAY_TOPIC = &quot;puzzle_game_activity&quot;<br>    PURCHASE_TOPIC = &quot;puzzle_game_transactions&quot;<br>    <br>    def send_gameplay_event(self, event):<br>        self.producer.produce(<br>            topic=self.GAMEPLAY_TOPIC,<br>            key=event[&#39;user_id&#39;].encode(&#39;utf-8&#39;),  # Partition by user!<br>            value=json.dumps(event).encode(&#39;utf-8&#39;)<br>        )</pre><p><strong>Why partition by user_id?</strong> Ensures all events for player u_102334 go to the same partition → guaranteed ordering. Session_start always arrives before puzzle_complete.</p><h4>Generating a realistic session</h4><pre>def generate_session(user_id, start_time):<br>    events = []<br>    session_id = f&quot;play_{uuid.uuid4().hex[:12]}&quot;<br>    <br>    # 1. Session start<br>    events.append({<br>        &quot;event_type&quot;: &quot;session_start&quot;,<br>        &quot;user_id&quot;: user_id,<br>        &quot;session_id&quot;: session_id,<br>        &quot;event_ts&quot;: start_time.isoformat()<br>    })<br>    <br>    # 2. Multiple puzzles (1-5 per session)<br>    for puzzle_id in range(1, random.randint(2, 6)):<br>        events.append({<br>            &quot;event_type&quot;: &quot;puzzle_complete&quot;,<br>            &quot;puzzle_id&quot;: puzzle_id,<br>            &quot;result&quot;: &quot;solved&quot; if random.random() &lt; 0.8 else &quot;failed&quot;,<br>            &quot;duration_ms&quot;: random.randint(30000, 180000)<br>        })<br>    <br>    # 3. Sometimes claim reward (watch ad)<br>    if random.random() &lt; 0.3:<br>        events.append({&quot;event_type&quot;: &quot;reward_claim&quot;})<br>    <br>    return events</pre><p>Run it:</p><pre>python producer.py --users 20 --sessions 3 --mode batch<br># Generated 414 gameplay events, 10 purchases (3% refund rate)</pre><h3>The ETL Consumer</h3><p>The consumer reads from Kafka and loads into PostgreSQL. Two main challenges: <strong>aggregating events into sessions</strong> and <strong>handling SCD Type 2</strong>.</p><pre>class GameAnalyticsConsumer:<br>    def __init__(self):<br>        self.consumer = Consumer({<br>            &#39;bootstrap.servers&#39;: &#39;localhost:9092&#39;,<br>            &#39;group.id&#39;: &#39;game-analytics-consumer&#39;,<br>            &#39;auto.offset.reset&#39;: &#39;earliest&#39;<br>        })<br>        self.consumer.subscribe([&#39;puzzle_game_activity&#39;, &#39;puzzle_game_transactions&#39;])<br>        <br>        # In-memory session aggregation<br>        self.active_sessions = {}  # session_id -&gt; metrics</pre><p><strong>Aggregating gameplay events</strong></p><pre>def process_gameplay_event(self, event):<br>    session_id = event[&#39;session_id&#39;]<br>    <br>    # Initialize session on first event<br>    if session_id not in self.active_sessions:<br>        self.active_sessions[session_id] = {<br>            &#39;user_id&#39;: event[&#39;user_id&#39;],<br>            &#39;start_ts&#39;: event[&#39;event_ts&#39;],<br>            &#39;puzzles_solved&#39;: 0,<br>            &#39;rewards_claimed&#39;: 0<br>        }<br>    <br>    # Update metrics<br>    session = self.active_sessions[session_id]<br>    if event[&#39;event_type&#39;] == &#39;puzzle_complete&#39;:<br>        if event[&#39;metadata&#39;][&#39;result&#39;] == &#39;solved&#39;:<br>            session[&#39;puzzles_solved&#39;] += 1<br>    elif event[&#39;event_type&#39;] == &#39;reward_claim&#39;:<br>        session[&#39;rewards_claimed&#39;] += 1</pre><p><strong>Handling SCD Type 2 automatically</strong></p><pre>def get_or_create_user_sk(self, user_id, device_sk, event_ts):<br>    # Check current record<br>    cur.execute(&quot;&quot;&quot;<br>        SELECT user_sk, device_sk FROM dim_user <br>        WHERE user_id = %s AND is_current = TRUE<br>    &quot;&quot;&quot;, (user_id,))<br>    <br>    result = cur.fetchone()<br>    if result and result[1] != device_sk:  # Device changed!<br>        # Close old record<br>        cur.execute(&quot;&quot;&quot;<br>            UPDATE dim_user <br>            SET is_current = FALSE, effective_end_date = %s<br>            WHERE user_sk = %s<br>        &quot;&quot;&quot;, (event_ts, result[0]))<br>        <br>        # Create new record<br>        cur.execute(&quot;&quot;&quot;<br>            INSERT INTO dim_user (user_id, device_sk, effective_start_date, is_current)<br>            VALUES (%s, %s, %s, TRUE) RETURNING user_sk<br>        &quot;&quot;&quot;, (user_id, device_sk, event_ts))</pre><p>Run it:</p><pre>python consumer.py --max-messages 500<br># Processed 500 messages, flushed 60 sessions</pre><h3>The Tricky Parts</h3><p>Three gotchas that will bite you if you’re not careful.</p><h4>Gotcha #1: Refunds in Revenue Queries</h4><pre>-- WRONG - Counts refunds as revenue!<br>SELECT SUM(amount_usd) FROM fact_purchase;<br>-- Result: $149.94 (includes $10 refund)<br><br>-- CORRECT - Net revenue<br>SELECT SUM(CASE WHEN is_refund THEN -amount_usd ELSE amount_usd END)<br>FROM fact_purchase<br>WHERE NOT is_sandbox;<br>-- Result: $139.94 (gross $149.94 - refunds $10)</pre><p>Always filter<strong> </strong>is_refund or your CFO will have questions.</p><h4>Gotcha #2: SCD Type 2 Joins</h4><pre>-- WRONG - Gets current user record only<br>SELECT u.device_sk, p.amount_usd<br>FROM fact_purchase p<br>JOIN dim_user u ON p.user_sk = u.user_sk<br>WHERE u.is_current = TRUE;<br>-- Problem: Historical purchases show today&#39;s device!<br><br>-- CORRECT - Point-in-time join<br>SELECT u.device_sk, p.amount_usd<br>FROM fact_purchase p<br>JOIN dim_user u ON p.user_sk = u.user_sk<br>WHERE p.transaction_ts &gt;= u.effective_start_date<br>  AND (u.effective_end_date IS NULL OR p.transaction_ts &lt; u.effective_end_date);<br>-- Now we see purchases were made on Android, before iPhone upgrade</pre><h4>Gotcha #3: Duplicate purchase_id</h4><pre>-- This fails because purchase_id isn&#39;t unique:<br>ALTER TABLE fact_purchase ADD CONSTRAINT unique_purchase UNIQUE (purchase_id);<br>-- ERROR: duplicate key value violates unique constraint<br><br>-- Correct: Composite key with is_refund<br>SELECT purchase_id, COUNT(*) <br>FROM fact_purchase <br>GROUP BY purchase_id <br>HAVING COUNT(*) &gt; 1;<br>-- tx_123 appears twice (original + refund) ✓</pre><h3>Querying the Data</h3><p>Now for the payoff — answering those business questions:</p><p><strong>Daily Revenue by Country</strong></p><pre>SELECT <br>    DATE(transaction_ts) as date,<br>    country_code,<br>    COUNT(*) FILTER (WHERE NOT is_refund) as purchases,<br>    COUNT(*) FILTER (WHERE is_refund) as refunds,<br>    SUM(CASE WHEN is_refund THEN -amount_usd ELSE amount_usd END) as net_revenue<br>FROM fact_purchase p<br>JOIN dim_user u ON p.user_sk = u.user_sk<br>WHERE NOT is_sandbox<br>GROUP BY DATE(transaction_ts), country_code<br>ORDER BY date DESC, net_revenue DESC;</pre><p><strong>Conversion Rate (Sessions → Purchases)</strong></p><pre>SELECT <br>    d.date_actual,<br>    COUNT(DISTINCT s.session_sk) as total_sessions,<br>    COUNT(DISTINCT p.session_sk) as sessions_with_purchase,<br>    ROUND(100.0 * COUNT(DISTINCT p.session_sk) / <br>          COUNT(DISTINCT s.session_sk), 2) as conversion_rate_pct<br>FROM fact_session s<br>JOIN dim_date d ON s.start_date_sk = d.date_sk<br>LEFT JOIN fact_purchase p ON p.session_sk = s.session_sk <br>    AND NOT p.is_refund<br>GROUP BY d.date_actual<br>ORDER BY d.date_actual DESC;</pre><p><strong>Revenue per Session (joining fact tables)</strong></p><pre>SELECT <br>    s.session_id,<br>    s.duration_seconds / 60 as duration_min,<br>    s.puzzles_solved,<br>    COALESCE(SUM(p.amount_usd) FILTER (WHERE NOT p.is_refund), 0) as revenue<br>FROM fact_session s<br>LEFT JOIN fact_purchase p ON p.session_sk = s.session_sk<br>GROUP BY s.session_sk, s.session_id, s.duration_seconds, s.puzzles_solved<br>HAVING COALESCE(SUM(p.amount_usd) FILTER (WHERE NOT p.is_refund), 0) &gt; 0<br>ORDER BY revenue DESC<br>LIMIT 5;</pre><h3>Key Takeaways</h3><p>You’ve built a real-time analytics pipeline that handles millions of events. Here’s what we covered:</p><p><strong>Design Patterns:</strong></p><ul><li><strong>Event Sourcing</strong> — Refunds as new events, not UPDATEs</li><li><strong>SCD Type 2</strong> — Historical tracking for user profile changes</li><li><strong>Surrogate Keys</strong> — Decoupled from business keys</li><li><strong>Separate Grain</strong> — Different fact tables for different business processes</li></ul><p><strong>Key Lessons:</strong></p><ol><li><strong>Partition by entity</strong> (user_id) for guaranteed ordering</li><li><strong>Always filter is_refund</strong> in revenue queries</li><li><strong>Point-in-time joins</strong> for SCD Type 2 dimensions</li><li><strong>Aggregate in consumer</strong>, not in database (sessions from events)</li></ol><p><strong>When to use this approach:</strong></p><ul><li>High-volume event streams (thousands/sec)</li><li>Need for historical analysis (“what device was used?”)</li><li>Complex business logic (refunds, cancellations)</li><li>Real-time + batch analytics</li></ul><p><strong>When NOT to use:</strong></p><ul><li>Simple CRUD operations</li><li>No need for historical tracking</li><li>Low event volume (&lt; 100/sec)</li></ul><h3>Conclusion: Turning Telemetry into Strategy</h3><p>Building a data warehouse isn’t just about moving bytes from a game client to a database; it’s about <strong>structuring data to tell a story.</strong> By combining the real-time streaming power of <strong>Kafka</strong> with the analytical rigor of a <strong>Star Schema</strong>, you’ve moved beyond simply knowing “what happened” to understanding “why it happened.” This architecture ensures that when a product manager asks why revenue dipped or where players are getting stuck, you aren’t digging through logs. You’re running a single, high-performance SQL query.</p><p>The path from raw, chaotic event streams to clean, actionable insights is the backbone of any successful modern game. Now that the pipeline is flowing, the real quest begins: using that data to build a better experience for your players.</p><p><strong>Happy querying!</strong></p><p>Check out the previous article in this series:</p><ul><li><a href="https://ijaniszewski.medium.com/cdc-explained-streaming-database-changes-with-kafka-and-debezium-cff3e03afae7?postPublishedType=initial"><strong>CDC Explained: Streaming Database Changes with Kafka and Debezium</strong></a></li><li><a href="https://medium.com/codex/query-streams-explained-transforming-kafka-data-in-real-time-with-ksqldb-4002ff3c5239"><strong>Query Streams explained: Transforming Kafka data in Real-Time with ksqlDB</strong></a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a413f8bc556d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Query Streams explained: Transforming Kafka data in Real-Time with ksqlDB]]></title>
            <link>https://medium.com/codex/query-streams-explained-transforming-kafka-data-in-real-time-with-ksqldb-4002ff3c5239?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/4002ff3c5239</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[kafka]]></category>
            <category><![CDATA[ksqldb]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Tue, 03 Feb 2026 10:40:52 GMT</pubDate>
            <atom:updated>2026-02-04T12:13:16.121Z</atom:updated>
            <content:encoded><![CDATA[<p>Here’s <a href="https://github.com/ijaniszewski/kafka-playground">the full code</a>.</p><h4>The game plan:</h4><p>First, we will explain the theory: why traditional database concepts do not always work for streaming data and how ksqlDB addresses this. Then, we will move to a hands-on demonstration: setting up the environment, generating a stream of events, and using SQL to filter or aggregate messages in real-time.</p><h3>What is Stream Processing?</h3><p>Traditional databases handle <strong>Data at Rest</strong>: you save a record, and it sits there passively until someone asks for it. Stream Processing handles <strong>Data in Motion</strong> — an infinite, continuous flow of events (clicks, sensors, payments) that never stops.</p><h4>Why “Database Thinking” Fails</h4><p>You cannot apply standard database logic to a stream.</p><p><strong>The Old Way:</strong> In a database, data is <strong>passive</strong> and the query is <strong>active</strong>. You run a query, it scans the static data, returns a snapshot, and stops.</p><p><strong>The Problem:</strong> A stream has no beginning and no end. You cannot “scan” the table because it is infinite; a standard query would never finish calculating.</p><h4>The Solution: ksqlDB and “The Inversion”</h4><p>ksqlDB solves this by <strong>inverting the model.</strong><br>Instead of the query visiting the data, the <strong>query becomes persistent</strong> and the data flows through it. You write standard SQL, but it doesn’t run once — it stays active forever. Every time a new Kafka message arrives, it flows through the query logic and updates the results in real-time.</p><h3>Environment Setup</h3><p>Let’s get our hands dirty. Spin up the infrastructure:</p><p>We just launched the necessary components:</p><ul><li><strong>Broker</strong>: The storage layer (Kafka) holding our data.</li><li><strong>ksqldb-server</strong>: The engine that executes the queries against the stream.</li><li><strong>ksqldb-cli</strong>: The command-line tool we will use to write SQL.</li></ul><h4>Let’s <strong>start the producer first</strong>.</h4><p>Since ksqlDB processes data in motion, we need that “motion” to exist. We want to simulate a busy application pushing raw events into Kafka so that when we open ksqlDB, we immediately see the challenge: a flood of unstructured JSON data that we need to organize.</p><p><strong>Generating the Stream. </strong>We’ll use a small Python script to simulate user activity (clicks, page views, purchases) on our website.</p><pre>python 01_producer/producer.py </pre><p>You should see logs indicating that events like User user_9 performed checkout are being sent to the user_actions topic.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sGkP2jgQx_CNTdaNSn_VBQ.png" /></figure><p><em>Leave this running in the background terminal.</em></p><h3>Connecting to ksqlDB and Defining the Stream</h3><p>Now that data is flowing, we need to tell ksqlDB how to read it. Kafka topics are just raw bytes; ksqlDB needs a schema to make sense of the JSON.</p><p><strong>Connect to the CLI:<br></strong>In a new terminal window, access the interactive SQL terminal inside the container:</p><pre>docker exec -it ksqldb-cli ksql http://ksqldb-server:8088</pre><p><strong>Create the Stream:<br></strong>We wrap the raw user_actions topic with a defined schema. Run this SQL in the ksqlDB prompt:</p><pre>CREATE STREAM user_actions_stream (<br>  user_id VARCHAR,<br>  action VARCHAR,<br>  timestamp VARCHAR<br>) WITH (<br>  KAFKA_TOPIC=&#39;user_actions&#39;,<br>  VALUE_FORMAT=&#39;JSON&#39;<br>);</pre><p><strong>Verify the Data:</strong><br>Check if it works by “tapping” into the stream. Note the EMIT CHANGES clause—this tells the query to stay open and push new rows as they arrive.</p><pre>SELECT * FROM user_actions_stream EMIT CHANGES;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*M-ZcqvV9dJ8V0UzSuO3E-A.png" /></figure><p>You should see the live events (generated by your Python producer) scrolling on the screen. Press Ctrl+C to stop the query.</p><h3>Real-Time Transformation and Aggregation</h3><p>Now that we have the raw pipe connected, let’s do something useful. We will turn ksqlDB into a real-time ETL engine and a live counter.</p><h4>Filter the Stream (ETL)</h4><p>The raw stream contains noise (views, clicks, purchases). Let’s say a downstream team only wants a clean stream of <strong>“Page Views”</strong>. Instead of writing a Python script to filter this, we write one line of SQL.</p><p>When you run this, ksqlDB effectively creates a new Kafka topic and continuously pipes filtered data into it.</p><pre>CREATE STREAM pageviews_stream AS<br>SELECT user_id, timestamp<br>FROM user_actions_stream<br>WHERE action = &#39;view&#39;;</pre><h4>Stateful Aggregation (The “Magic”)</h4><p>This is the most powerful part. Aggregating an infinite stream (e.g., “How many actions did user X perform?”) is notoriously hard in code because you have to manage state (memory).</p><p>ksqlDB handles the state for you with a <strong>Table</strong>.</p><pre>CREATE TABLE user_action_counts AS<br>SELECT user_id, COUNT(*) AS total_actions<br>FROM user_actions_stream<br>GROUP BY user_id<br>EMIT CHANGES;</pre><p>a now, <strong>see the results — </strong>query your innovative “Live Table”. As your Python producer keeps running in the background, this query will update the numbers on your screen in real-time.</p><pre>SELECT * FROM user_action_counts EMIT CHANGES;</pre><p>You are now looking at a persistent, materialized view that updates milliseconds after an event occurs.</p><h3>Summary</h3><p>We have just built a fully functioning stream processing pipeline without writing a single line of complex application code.</p><p>By shifting our mindset from “Passive Data” (waiting to be queried) to “Active Queries” (constantly processing flowing data), we unlocked real-time capabilities that are usually very expensive to build.</p><p><strong>To recap, we:</strong></p><ol><li>Acknowledged that traditional database queries don’t work on infinite streams.</li><li>Used <strong>ksqlDB</strong> to invert the model: persistent queries that process data as it arrives.</li><li>Replaced what would have been ~50 lines of Python consumer code (handling offsets, loops, state) with <strong>3 simple SQL statements</strong>.</li></ol><h3>Where to go from here?</h3><p>This article focused on processing data once it is <em>already</em> in Kafka. But how do you get your core business data — from PostgreSQL or MySQL — into Kafka in the first place without dual-writes?</p><p>Check out the previous article in this series: <a href="https://ijaniszewski.medium.com/cdc-explained-streaming-database-changes-with-kafka-and-debezium-cff3e03afae7?postPublishedType=initial"><strong>CDC Explained: Streaming Database Changes with Kafka and Debezium</strong></a>.</p><p>Happy Streaming!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4002ff3c5239" width="1" height="1" alt=""><hr><p><a href="https://medium.com/codex/query-streams-explained-transforming-kafka-data-in-real-time-with-ksqldb-4002ff3c5239">Query Streams explained: Transforming Kafka data in Real-Time with ksqlDB</a> was originally published in <a href="https://medium.com/codex">CodeX</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[CDC Explained: Streaming Database Changes with Kafka and Debezium]]></title>
            <link>https://ijaniszewski.medium.com/cdc-explained-streaming-database-changes-with-kafka-and-debezium-cff3e03afae7?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/cff3e03afae7</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[debezium]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[kafka]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Mon, 02 Feb 2026 17:43:12 GMT</pubDate>
            <atom:updated>2026-02-02T17:43:12.542Z</atom:updated>
            <content:encoded><![CDATA[<p>Here’s <a href="https://github.com/ijaniszewski/kafka-playground">the full code</a>.</p><p>We’ll build a simple CDC pipeline to see how database changes flow in real-time.</p><h4>The game plan:</h4><p>Quick explanation what CDC is and why (for demo purposes) we’ll use Kafka with <a href="https://debezium.io/">Debezium</a>. Then, we’ll setup the environment and run some demo queries on Postgres to see how it works.</p><h3>What is CDC?</h3><p>CDC (Change Data Capture) is about tracking what’s changing in your database (INSERT, UPDATE, and DELETE) as it happens.</p><p>Traditional approach? You’d query the database periodically to find what changed. Maybe check timestamps, compare snapshots. It works, but it’s slow and puts load on your database.</p><p>CDC does it differently. It reads the database transaction log — the same log your database uses internally to recover from crashes and replicate data. Every change is already written there, so instead of querying tables, we just read this log.</p><p>Why this matters:</p><ul><li><strong>No database load</strong> — we’re reading logs, not querying production tables</li><li><strong>Real-time</strong> — changes appear immediately, not after next polling interval</li><li><strong>Everything captured</strong> — we get the complete history, including what was deleted</li><li><strong>No schema changes</strong> — don’t need to add “updated_at” columns or triggers</li></ul><p>The database is already writing everything down. CDC just taps into that stream.</p><h4>CDC Across Different Databases</h4><p>Yes, each database does it a bit differently.</p><p><strong>PostgreSQL</strong> — Uses Write-Ahead Log (WAL). Postgres writes every change to WAL before applying it to tables.</p><p><strong>MySQL</strong> — Has binlog (binary log) that works similarly. You enable it and read from there.</p><p><strong>MongoDB</strong> — Uses oplog (operations log) from replica sets. Same idea, different format.</p><p><strong>SQL Server</strong> — Actually has built-in CDC features you can enable per table.</p><p>The concept is the same everywhere — read the internal log instead of querying tables. But the implementation details differ because each database writes its logs in its own format.</p><h3>Why Kafka and Debezium?</h3><p>Now we need something to read those database logs and something to handle the stream of changes.</p><p><strong>Debezium</strong> is a CDC connector that reads database transaction logs. It supports multiple databases and knows how to parse their logs and turn changes into events. Think of it as a translator between your database and the outside world.</p><p><strong>Kafka</strong> is where those events go. It’s a distributed log that stores the stream of changes. Other services can then consume these events at their own pace — build a search index, update a cache, trigger notifications, whatever you need.</p><p><strong>Kafka Connect</strong> is the framework where Debezium runs. It’s designed for moving data in and out of Kafka — you plug in connectors (like Debezium) and it handles the heavy lifting of reading data and writing it to Kafka topics reliably.</p><p>So in our setup: Postgres WAL → Debezium connector (running in Kafka Connect) → Kafka topics → your consumers. All running in Docker containers to keep things simple.</p><h3><strong>Setting Up the Environment</strong></h3><p>Everything runs in Docker. Just docker-compose up -d and you&#39;re ready.</p><p>Here’s what we’re spinning up:</p><p><strong>broker</strong> — The Kafka server where all change events will be stored.</p><p><strong>postgres</strong> — Our source database with wal_level=logical enabled. That&#39;s what makes CDC possible.</p><p><strong>connect</strong> — Kafka Connect running Debezium. This bridges Postgres and Kafka.</p><p><strong>schema-registry</strong> — Tracks data schemas so everyone agrees on the event format.</p><p>Once everything is up, we need to configure the Debezium connector to start capturing changes.</p><h4>Configuring Debezium</h4><p>With everything running, we register the Debezium connector by POSTing a config to Kafka Connect’s REST API. Here’s what we tell it:</p><pre>config = {<br>    &quot;name&quot;: &quot;users-connector&quot;,<br>    &quot;config&quot;: {<br>        &quot;connector.class&quot;: &quot;io.debezium.connector.postgresql.PostgresConnector&quot;,<br>        &quot;database.hostname&quot;: &quot;postgres&quot;,<br>        &quot;database.port&quot;: &quot;5432&quot;,<br>        &quot;database.user&quot;: &quot;postgres&quot;,<br>        &quot;database.password&quot;: &quot;postgres&quot;,<br>        &quot;database.dbname&quot;: &quot;playground&quot;,<br>        &quot;topic.prefix&quot;: &quot;dbserver1&quot;,<br>        &quot;plugin.name&quot;: &quot;pgoutput&quot;,<br>        &quot;table.include.list&quot;: &quot;public.users&quot;,<br>    },<br>}</pre><p>Key parts:</p><ul><li><strong>connector.class</strong> — which Debezium connector to use (PostgreSQL in our case)</li><li><strong>database.</strong>* — connection details to our Postgres container</li><li><strong>topic.prefix</strong> — all topics will start with “dbserver1” (so users table becomes dbserver1.public.users)</li><li><strong>plugin.name</strong> — pgoutput is Postgres&#39;s built-in logical replication output plugin</li><li><strong>table.include.list</strong> — we only want to track the public.users table</li></ul><p>Run the setup script and it creates the database table, registers the connector, and checks if everything is running.</p><pre>python 02_cdc/setup.py</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1002/1*3u4qdwg-sYfOHFlbtY1mDg.png" /></figure><p>we created table and insert some records into it</p><pre>INSERT INTO users (name, email) VALUES <br>    (&#39;Alice&#39;, &#39;alice@example.com&#39;),<br>    (&#39;Bob&#39;, &#39;bob@example.com&#39;),<br>    (&#39;Charlie&#39;, &#39;charlie@example.com&#39;);</pre><p>Now, as we have our table created, let’s see what Postgres actually writes to its transaction log. The WAL is a binary file, but we can use logical decoding to view it in a readable format.</p><p>First, connect to the database:</p><pre>docker exec -it postgres psql -U postgres -d playground</pre><p>Create a temporary replication slot with the test_decoding plugin:</p><pre>SELECT * FROM pg_create_logical_replication_slot(&#39;peek_slot&#39;, &#39;test_decoding&#39;);</pre><p>This creates a slot at the current WAL position. Now insert a new record so we have something to see:</p><pre>INSERT INTO users (name, email) VALUES (&#39;David&#39;, &#39;david@example.com&#39;);</pre><p>And peek at what was written to the WAL:</p><pre>SELECT * FROM pg_logical_slot_peek_changes(&#39;peek_slot&#39;, NULL, NULL);</pre><p>and you will see something like:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KiRMNEH8EdUccb_bfG7CDQ.png" /></figure><p>This is it the raw transaction log. Every change gets recorded: BEGIN transaction, the actual INSERT with all column values, and COMMIT. This is exactly what Debezium reads and converts into Kafka events.</p><p>Good practice to clean this up — replication slots hold onto WAL segments until they’re consumed, so an unused slot can fill up your disk.</p><pre>SELECT pg_drop_replication_slot(&#39;peek_slot&#39;);</pre><p>Debezium manages its own slot automatically, but our temporary peek slot needs manual cleanup.</p><h4>Seeing CDC in Action</h4><p>Now that Debezium is reading the WAL and sending changes to Kafka, let’s consume those events.</p><p>Start the consumer to watch for changes:</p><pre>python 02_cdc/consumer_cdc.py</pre><p>This subscribes to the <em>dbserver1.public.users</em> topic — that’s where Debezium sends all changes from the users table.</p><p>In another terminal, generate some data:</p><pre>python 02_cdc/generate_data.py</pre><p>This inserts random users every 2 seconds:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yunG5K7z3vycoxokw4UdKA.png" /></figure><p>Watch the consumer — you’ll see each INSERT appear immediately:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R0nV3hj5hIoUmgf0zGO3dg.png" /></figure><pre>Operation: c, Data: {&#39;id&#39;: 5, &#39;name&#39;: &#39;Emma Wilson&#39;, &#39;email&#39;: &#39;emma@example.com&#39;, &#39;created_at&#39;: 1738502415000000}<br>Operation: c, Data: {&#39;id&#39;: 6, &#39;name&#39;: &#39;Michael Brown&#39;, &#39;email&#39;: &#39;michael@example.com&#39;, &#39;created_at&#39;: 1738502417000000}</pre><p>The op field tells you what happened:</p><ul><li>c = create (INSERT)</li><li>u = update (UPDATE)</li><li>d = delete (DELETE)</li></ul><p>The after field has the new row data. For updates, you also get before with the old values.</p><p>That’s CDC — database changes flowing to Kafka in real-time, without touching your production tables.</p><h4>Try it Yourself: Updates and Deletes</h4><p>We’ve been watching INSERTs, but CDC captures everything.</p><p>Try it yourself! Connect to Postgres and delete or update a record. Then check how it’s shown by consumer. You will notice:</p><ul><li><strong>DELETE events</strong> (op: d) include the deleted row data in the before field - you need to know what was removed</li><li><strong>UPDATE events</strong> (op: u) contain both before (old values) and after (new values) - you can see exactly what changed</li></ul><p>This is powerful for audit logs or keeping derived data stores in sync.</p><h3><strong>What’s Next?</strong></h3><p>Now that you have database changes flowing through Kafka, you can:</p><ul><li><strong>Sync to Elasticsearch</strong> — keep your search index up-to-date automatically</li><li><strong>Invalidate caches</strong> — clear Redis/Memcached entries when data changes</li><li><strong>Build materialized views</strong> — maintain denormalized tables in other databases</li><li><strong>Audit logging</strong> — track who changed what and when</li><li><strong>Trigger workflows</strong> — kick off processes when specific changes happen</li></ul><p>The pattern is always the same: consume the CDC topic, react to the events. No polling, no database load, real-time.</p><h3><strong>Wrapping Up</strong></h3><p>CDC gives you a continuous stream of database changes without impacting your production workload. Postgres writes to its WAL anyway — we’re just reading it. Debezium translates those changes into Kafka events, and from there you can fan out to as many consumers as you need.</p><p>The setup looks involved with all the Docker services, but once it’s running, it just works. Changes flow automatically, reliably, in order. That’s the power of tapping into the transaction log instead of polling tables.</p><p>Full code is on <a href="https://github.com/ijaniszewski/kafka-playground">GitHub</a> — try it out, break things, see how it behaves. CDC is one of those patterns that seems complex until you see it working, then it clicks.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cff3e03afae7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Proces ludzkości]]></title>
            <link>https://ijaniszewski.medium.com/proces-ludzko%C5%9Bci-c27c98705793?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/c27c98705793</guid>
            <category><![CDATA[anxiety]]></category>
            <category><![CDATA[facebook]]></category>
            <category><![CDATA[social-media]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Tue, 23 Sep 2025 10:55:08 GMT</pubDate>
            <atom:updated>2025-09-23T13:45:22.499Z</atom:updated>
            <content:encoded><![CDATA[<p>Sala sądowa drżała od poruszenia. Takiej rozprawy jeszcze nie było. Na <strong>ten proces</strong> skierowane były dzisiaj oczy całego świata.</p><p>Na ławie oskarżonych siedziało czterech zupełnie różnych bohaterów tego widowiska.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*pXJ4OTSHpBX6Tzuk.jpg" /><figcaption><a href="https://pixabay.com/pl/photos/m%C5%82ot-aukcji-prawo-sprawiedliwo%C5%9B%C4%87-7354618/">https://pixabay.com/pl/photos/m%C5%82ot-aukcji-prawo-sprawiedliwo%C5%9B%C4%87-7354618/</a></figcaption></figure><p><strong>Oskarżony numer jeden</strong> — młody chłopak o rozbieganych oczach — nie potrafił usiedzieć w miejscu. W szerokiej bluzie, z dużymi słuchawkami zwisającymi na szyi, szeptał coś do siebie, by nagle wybuchnąć głośnym, głupkowatym śmiechem. Nerwowo podrygiwał nogą i widać było jego rozgoryczenie — każda kolejna minuta w tym miejscu była dla niego zmarnowana! <strong>Klasyczny przypadek FOMO</strong> — ciało rwało się do akcji, do kolejnego bodźca, do czegokolwiek, byle tylko nie trwać w miejscu, a już na pewno nie w ciszy. Niby dużo emocji, ale zapewne gdzieś indziej dzieje się coś ciekawszego!</p><p>Obok niego pyszniła się <strong>oskarżona numer dwa</strong>. Kobieta o nienagannej sylwetce, gładkiej twarzy, widać dużo pracy włożonej w końcowy wygląd. Jej uśmiech był tak perfekcyjny, że aż lodowaty — przeszywał każdą napotkaną osobę. Nawet drgnięcie rzęs zdawało się wyćwiczone pod aplauz i flesze — których dzisiaj znowu nie brakowało. Doskonała (o ile ktoś nie jest koneserem naturalnego piękna) — żywy billboard. <strong>Próżna influencerka</strong>.</p><p>Dalej siedział <strong>oskarżony numer trzy</strong> — mężczyzna w ciężkim, staromodnym garniturze. Jego spojrzenie było jednocześnie zmęczone i podejrzanie czujne. Powoli przekładał papiery, mamrocząc pod nosem, jakby układał kolejną intrygę. W oczach miał cynizm kogoś, kto widział już wszystko i wie, że zawsze da się kogoś przekonać, złamać albo kupić. <strong>Polityk-manipulator</strong>, który nigdy nie przestaje planować.</p><p>Na końcu ławy rozsiadł się <strong>oskarżony numer cztery</strong> — chudy mężczyzna z ironicznym uśmiechem przyklejonym do twarzy. Od czasu do czasu parskał pod nosem, rzucając półgłosem kąśliwe komentarze, jakby cała ta uroczysta scena była dla niego żałosną farsą.<strong> Troll w ludzkiej postaci</strong>, żywiący się szyderstwem i pogardą.</p><p>Razem tworzyli groteskowy orszak — hałaśliwy, próżny, cyniczny i przewrotny. To właśnie oni mieli dziś odpowiedzieć za swoje czyny przed całym światem.</p><p>Z sali sądowej wezwano pierwszego świadka — młodą dziewczynę w szkolnym mundurku. Stanęła przed ławą, wciąż ściskając w dłoni telefon.</p><p>– <em>Miałam przyjaciół… </em>— zaczęła cicho. — <em>Spotykaliśmy się, rozmawialiśmy. A teraz… siedzimy obok siebie na ławce, każdy w swoim ekranie. Gdy próbuję coś powiedzieć, słyszę tylko ‘czekaj, muszę odpisać’. Czasem mam wrażenie, że jestem mniej ważna niż powiadomienie, które im miga na ekranie.</em></p><p>Na sali zrobiło się cicho.</p><p>Kolejny świadek — młody chłopak w bluzie z kapturem — patrzył gdzieś w bok, jakby opowiadał coś zbyt wstydliwego.</p><p>– <em>Na imprezie byłem z dziewczyną, którą lubię. A ona wciąż wysyłała zdjęcia tym, którzy nie przyszli. Wrzucała stories, tagowała, dopisywała śmieszne emotki. Wróciłem do domu i… zamiast napisać do niej, odpaliłem aplikację i oglądałem, jak bawiła się beze mnie — na moich oczach. Nie wiem, po co w ogóle tam byłem.</em></p><p>Zeznawała też matka, zgaszona, zmęczona.</p><p>– <em>Mój syn wraca ze szkoły i zamyka się w pokoju. Mówi, że ‘rozmawia z kolegami’. Ale jak wejdę, widzę tylko niekończący się strumień filmików, śmiechy i lajki. Ale on biedy ogląda to w smutku. Ja też próbowałam. I to wciąga. Człowiek nagle budzi się po godzinie, dwóch… i nie pamięta, na co poszedł ten czas. To nie jest rozmowa. To jest… ucieczka.</em></p><p>Na koniec wszedł starszy pan. Jego głos był spokojny, ale w nim było coś przejmującego.</p><p>– <em>Wiecie, co jest najgorsze?</em>— powiedział. — <em>Że oni mówią: ‘podtrzymujemy relacje’. A prawda jest taka, że to są ochłapy. Kawałki życia, okruchy chwil — wszystko sprzedane za jakieś nierealne polubienia. Spotykasz się z kimś, ale on już myśli o tym, jak napisze do kogoś innego. A potem będąc z tym innym pisze do Ciebie. I tak w koło Macieju. Niby jesteś tutaj ciałem, ale naprawdę cię nie ma. I tak dzień po dniu, człowiek przestaje chcieć być… realnie.</em></p><p>Sala słuchała w milczeniu. Nawet oskarżeni na ławie na moment przestali się wiercić i uśmiechać.</p><p>Świadkowie zeszli z mównicy, sala ciężko oddychała — jakby każdy na widowni zobaczył w ich słowach własne odbicie. Sędzia skinął głową w stronę ławy obrony.</p><p>Adwokat powstał powoli, z nieśpieszną gracją. Był ubrany elegancko, ale bez przesady, miał spojrzenie zimne, wyrachowane. Kiedy zaczął mówić, jego głos był miękki i przekonujący, jak miód kapiący w ciszę.</p><p>– <em>Szanowny sądzie, wysoka ławo przysięgłych…</em>— zaczął. — <em>Oskarżenie maluje tu obraz potworów, które wciągają dzieci, zatruwają rodziny i odbierają ludziom chęć życia. Ale czy to naprawdę my?</em></p><p>Podszedł krok bliżej, wskazując na oskarżonych — niespokojnego chłopaka, próżną kobietę, starego polityka i cynicznego szydercę.</p><p>– <em>Czy te postacie, które tu widzicie, przyszły na świat same? Czy same zbudowały swoje imperium? Nie. One powstały dlatego, że </em><strong><em>ludzie ich chcieli</em></strong><em>. To nie my wpychaliśmy telefony w ich dłonie. To nie my kazaliśmy im scrollować do świtu. To oni przyszli do nas, karmili nas swoją uwagą, godzinami, swoimi najintymniejszymi emocjami.</em></p><p>Zrobił pauzę, przeciągając ciszę.</p><p>– <em>Mówicie, że sprzedali się za paczkę lajków. Tak, sprzedali się — ale to nie my byliśmy kupującymi. To oni sami wymyślili wartość lajka. To oni nadali mu znaczenie większe niż spojrzenie w oczy drugiego człowieka.</em></p><p>W jego głosie pojawił się cień triumfu.</p><p>– <em>My jesteśmy tylko lustrem. Odbijamy ludzkie pragnienia, tęsknoty, lęki. Bez człowieka — nie istniejemy. Jeśli więc chcecie ogłosić wyrok… bądźcie sprawiedliwi. Bo prawdziwy winny nie siedzi na tej ławie. Prawdziwy winny siedzi tam…</em> — i adwokat obrócił się w stronę publiczności. —<em> …między wami.</em></p><p>Na sali rozległ się szmer, ktoś kaszlnął, ktoś inny nerwowo poprawił telefon w kieszeni.</p><p>Sędzia uniósł młotek. W sali zapanowała cisza tak gęsta, że słychać było tylko brzęczenie neonowych lamp pod sufitem.</p><p>– <em>Po wysłuchaniu świadków i obrony </em>— głos zabrzmiał donośnie — <strong><em>ogłaszam wyrok!</em></strong></p><p>Publiczność wstrzymała oddech. Każdy czekał na słowa: <em>„wszystkiemu winni czterej oskarżeni!”.</em></p><p>– <strong><em>Winny jest </em></strong>— zawiesił głos. — <strong><em>człowiek</em></strong>. <em>Bo sam oddał swoją wolną wolę w zamian za iluzję uwagi. Bo pozwolił, by rozmowę zastąpiło powiadomienie, a spojrzenie — ikona serduszka. Bo zgodził się, by ciszę i spotkanie z drugim człowiekiem wymazał niekończący się strumień obrazków. Zapomniał, że życie toczy się poza ekranem — w dotyku, w spojrzeniu, w prawdziwym słowie.</em></p><p>Wśród zgromadzonych rozległy się szepty, poruszenie, niedowierzanie. Oskarżeni na ławie uśmiechnęli się tylko i zaczęli wstawać — wolni, nietknięci.</p><p>Ale wtedy sędzia opuścił młotek i dodał:</p><p>–<em> I jeszcze jedno. To tylko proces, tylko opowieść. </em><strong><em>Prawdziwy wyrok jeszcze nie zapadł</em></strong><em>. Bo on zapada codziennie — w naszych domach, przy naszych stołach, w rozmowach z naszymi dziećmi i przyjaciółmi. Jeszcze nie jest za późno. </em><strong><em>Jeszcze możemy wybrać inaczej</em></strong><em>.</em></p><p><em>Oskarżony numer jeden — </em><strong><em>TikTok</em></strong><em> — nie jest winny. Winna jest nasza własna skłonność do sięgania po łatwe, błahostkowe treści, które połykamy bez wysiłku i bez refleksji, a które zjadają godziny naszego życia.</em></p><p><em>Oskarżona numer dwa — </em><strong><em>Instagram </em></strong><em>— nie jest winna. Winna jest nasza potrzeba udawania, że mamy inne życie niż naprawdę, i jednocześnie pragnienie, by zazdrościć życia innym — choć wiemy, że jest ono równie udawane jak nasze.</em></p><p><em>Oskarżony numer trzy — </em><strong><em>Facebook </em></strong><em>— nie jest winny. Winni jesteśmy my, bo zamiast żyć własnym życiem, wolimy wpatrywać się w to, co robią inni, często obcy ludzie, karmiąc się cudzymi emocjami, a nie swoimi.</em></p><p><em>Oskarżony numer cztery — </em><strong><em>Twitter </em></strong><em>— nie jest winny. Winni jesteśmy my, bo wolimy krzyczeć, prowokować i kłócić się w przestrzeni, w której nigdy nie spotkamy drugiego człowieka twarzą w twarz.</em></p><p>Sędzia spojrzał surowo, ale nie bez życzliwości.</p><p>– <em>A może by tak… posprzeczać się z kimś obok, ale naprawdę, twarzą w twarz? Bez ekranu, bez filtrów, bez sztucznych masek? Może zamiast lajkować — zapytać, co u kogoś słychać? Może zamiast pisać — usiąść, spojrzeć w oczy, pośmiać się i pokłócić jak ludzie?</em></p><p>Oskarżeni spojrzeli po sobie niespokojnie, jakby po raz pierwszy stracili pewność. Sala rozbrzmiała cichym pomrukiem nadziei.</p><p><strong><em>— Nam się wydaje, że kiedy przewijamy ekran, to odpoczywamy</em></strong><em>. Że dobrze nam to robi, że pozwala się odstresować, że to nasz sposób na relaks. Ale to kłamstwo. To tylko kolejne godziny wycięte z życia. Godziny, które moglibyśmy poświęcić na rozmowę, na ciszę, na prawdziwe spotkanie.</em></p><p>Wydaje się też, że ludzie boją się ciszy, nudy. I przez telefony, social media straciliśmy możliwość nudzenia się — bo wiadomo, ten kto się nudzi to pewnie leń i głupek. A to właśnie nie do końca tak. Człowiek potrzebuje czasem kompletnej ciszy i leniuchowania. Oni starają się w tym przeszkodzić.</p><p><em>— Mówimy też, że to narzędzia do utrzymywania kontaktów. Ale to również jest jedno wielkie kłamstwo. To, co oferują, to ochłapy — sztuczne namiastki bliskości, które z czasem tylko pogłębiają pustkę. Zamiast rozmowy dostajemy powiadomienie. Zamiast spojrzenia — ikonę serduszka. Zamiast obecności — cyfrową fatamorganę.</em></p><p><em>No tak, nie raz złapałem się na wysyłaniu “hahaha” w ogóle się nie uśmiechając — </em>pomyślał narrator. Ten sam, który jeszcze przed chwilą czuł wibracje telefonu w kieszeni — spojrzał na ekran… i tym razem odłożył go, nie sprawdzając. Pomyślał tylko:</p><p><em>Może kiedyś naprawdę odbędzie się taki proces. Może te firmy faktycznie staną przed sądem i odpowiedzą — nie za aplikacje, ale za świadome projektowanie uzależnienia.</em></p><p><em>I nie chodzi tutaj tylko o badania społeczne, które jasno wskazują problem. Chodzi o bardzo wątpliwe moralnie działania i eksperymenty.</em></p><p><em>Facebook, </em><a href="https://www.theguardian.com/technology/2014/jun/29/facebook-users-emotions-news-feeds"><strong><em>manipulował News Feedem setek tysięcy użytkowników</em></strong></a><em> (np. ograniczając pozytywne albo negatywne posty) żeby sprawdzić jak to wpływa na ich emocje. To przykład tego, jak psychologia / badania psychologiczne są używane „w tle”.</em></p><p><em>Albo tworzy </em><a href="https://www.tuw.edu/psychology/psychology-in-business-companies-are-employing-psychologists-to-increase-engagement/"><strong><em>zespoły psychologów, których celem jest zwiększanie zaangażowania użytkowników</em></strong></a><em>. Robią to poprzez projektowanie aplikacji w taki sposób, aby użytkownik został dłużej, przewijał więcej i wracał częściej. Jak? Oczywiście bazując na naszych najsłabszych instynktach. Szybkiej łatwej nagrody w postaci zastrzyku dopaminy. A co otrzymujemy? Smutek, depresję i rozdrażnienie. A najgorsze, że paradoksalnie jesteśmy bardziej samotni.</em></p><p><em>Że odpowiedzą za to, a także za mechanizmy nagrody i kary, które działają jak w automatach hazardowych. Za wmawianie dzieciom i dorosłym, że lajki to miara wartości. Za to, że wiedząc, jak niszczą psychikę, wciąż pchają w rynek nowe sztuczki, byśmy przewijali jeszcze dłużej.</em></p><p><em>To nie jest niewinna zabawa — </em><strong><em>to morderstwo duszy z premedytacją</em></strong><em>.</em></p><p>I oby kiedyś ktoś naprawdę poniósł za to odpowiedzialność. Nie wiem jak Wy, ale ja widzę tutaj dużą przestrzeń do oskarżenia. Ale prawnikiem nie jestem… :) Więc póki co, jedyne co mogę zrobić to samemu starać się walczyć z tym Goliatem.</p><ul><li><strong>Ponad 1 na 10 nastolatków (11%) wykazuje symptomy problematycznego używania mediów społecznościowych, nie potrafią kontrolować czasu spędzanego online, doświadczają negatywnych konsekwencji.</strong><br><a href="https://www.who.int/europe/news/item/25-09-2024-teens--screens-and-mental-health">WHO — „Teens, screens and mental health”</a></li><li><strong>Związek między korzystaniem z mediów społecznościowych (powyżej ~3 godzin dziennie) a zwiększonym ryzykiem symptomów depresji i lęku.</strong><br><a href="https://www.hhs.gov/surgeongeneral/reports-and-publications/youth-mental-health/social-media/index.html">The United States Surgeon General — raport o wpływie mediów społecznościowych na zdrowie psychiczne dzieci i młodzieży</a></li><li><strong>Badania (n = ok. 232) łączą intensywne korzystanie z Facebooka z różnymi efektami: lękiem, depresją, poczuciem samotności, zaburzeniami snu, problemami z samooceną itp.</strong><br><a href="https://www.frontiersin.org/journals/psychology/articles/10.3389/fpsyg.2023.1141663/full">Negative psychological and physiological effects of social media: A review</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c27c98705793" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Budżet Obywatelski w liczbach. Warszawa 2026]]></title>
            <link>https://ijaniszewski.medium.com/bud%C5%BCet-obywatelski-w-liczbach-warszawa-2026-860e55db3c4c?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/860e55db3c4c</guid>
            <category><![CDATA[participatory-budgeting]]></category>
            <category><![CDATA[budżet-partycypacyjny]]></category>
            <category><![CDATA[warszawa]]></category>
            <category><![CDATA[dane]]></category>
            <category><![CDATA[budżet-obywatelski]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Thu, 24 Jul 2025 15:12:19 GMT</pubDate>
            <atom:updated>2025-07-24T20:28:19.962Z</atom:updated>
            <content:encoded><![CDATA[<p>W dniach od 1 do 15 czerwca 2025 r. w Warszawie <a href="https://um.warszawa.pl/waw/bo/harmonogram-12-edycja">odbyło się głosowanie</a> na projekty Budżetu Obywatelskiego. Użycie przyszłego roku w nazwie może być trochę mylne — głosowanie co prawda odbyło się w 2025, ale projekty zostaną zrealizowane w 2026 roku. To ważna kwestia, bo często prowadzi do nieporozumień. Niektóre miasta nazywają rokiem głosowania, inne (jak Warszawa) rokiem fundowania projektów.</p><p>Wszystkie dane zostały pobrane ze strony <a href="https://pabulib.org/">pabulib.org</a>, natomiast cały kod można znaleźć <a href="https://github.com/pabulib/pabulib_process_data/blob/main/src/analytics/wwa_2026_statistics.ipynb">na repozytorium Pabuliba</a>.</p><h3><strong>Budżet obywatelski</strong></h3><p>(inaczej: <strong>budżet partycypacyjny</strong>) to mechanizm, dzięki któremu mieszkańcy danego miasta, gminy lub dzielnicy mogą <strong>współdecydować o wydatkach części budżetu samorządu</strong>.</p><p><strong>Mieszkańcy zgłaszają pomysły na inwestycje lub działania</strong>, a następnie <strong>głosują</strong>, które z nich mają zostać zrealizowane. Zwycięskie projekty są potem finansowane i wdrażane przez miasto.</p><p>To sposób na zwiększenie udziału obywateli w życiu publicznym i lepsze dostosowanie działań władz lokalnych do potrzeb społeczności.</p><p>Niewątpliwie, Warszawa przekazuje na ten cel najwięcej pieniędzy z pośród wszystkich polskich miast — w tym roku niemalże <a href="https://um.warszawa.pl/waw/bo/kwoty-12-edycja"><strong>121 milionów złotych</strong></a>. Wynika to z faktu, że zgodnie z ustawą, miasta na prawach powiatu są zobowiązane przekazywać na ten cel <a href="https://lexlege.pl/ustawa-o-samorzadzie-gminnym/art-5a/#:~:text=W%20gminach%20b%C4%99d%C4%85cych%20miastami%20na,przed%C5%82o%C5%BConym%20sprawozdaniu%20z%20wykonania%20bud%C5%BCetu">co najmniej 0,5% swojego budżetu</a>. W praktyce większość samorządów ogranicza się do tego minimum — rzadko zdarza się, by przeznaczały znacząco więcej. A że to właśnie Warszawa dysponuje największym budżetem w kraju, to i kwota przekazywana na budżet obywatelski jest tu najwyższa.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rIDeF9TjQJA_-AHM20WBOg.png" /><figcaption>opracowanie własne, źródła w kodzie źródłowym</figcaption></figure><p>Z tego powodu głosowanie w Warszawie jest niezwykle interesujące do analizy — duży budżet do podziału, wiele projektów, oraz ciekawy system głosowania, o czym za chwilę.</p><p>Ustawa o obowiązkowych budżetach obywatelskich w polskich miastach <a href="https://gov.legalis.pl/budzet-obywatelski-na-nowych-zasadach/">weszła w życie od 2019</a>, zobaczmy czy widać to w danych:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*fHH3kk92FWRRe3YWXweJOg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*DQAhJIhb49lGoeBhtcV0EQ.png" /></figure><p>Wprowadzenie ustawy niejako “zmusiło” miasta do przeznaczania na BO więcej środków.</p><p><strong>Czy wszystkie miasta stosują się do ustawowego minimum 0,5%?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/451/1*RdqhDnko6_OHqz31ZSggRw.png" /></figure><p>Na pierwszy rzut oka mogłoby się wydawać, że nie. Z zatwierdzonych budżetów miast na rok 2024 oraz kwot przeznaczonych na budżety obywatelskie w 2025 roku wynika, że <strong>Warszawa, Wrocław, Poznań i Gdańsk przeznaczają na BO mniej niż wymagane 0,5%</strong> swoich wydatków.</p><p>Czy to oznacza złamanie przepisów? <strong>Niekoniecznie.</strong></p><p>Jak poinformował nas Wydział Budżetu Obywatelskiego Urzędu m.st. Warszawy, <strong>12. edycja BO (projekty na rok 2026)</strong> została rozpoczęta w styczniu 2025 roku — a środki z reguły wyznaczane są około 2–3 miesiące wcześniej (czyli w listopadzie 2024). W takim przypadku podstawą do wyliczenia 0,5% były wydatki wykazane w ostatnim dostępnym sprawozdaniu z wykonania budżetu, czyli za 2023 rok — wynoszące 24,18 mld zł. Przy tej podstawie wymóg został spełniony.</p><h3>Zmiana zasad</h3><p>W głosowaniu budżetu obywatelskiego w 2025 roku (dotyczącym projektów realizowanych od 2026 roku) w Warszawie wprowadzono istotne zmiany. Wcześniej istniało łącznie 19 „instancji” — jedna ogólnomiejska oraz 18 dzielnicowych. Obecnie dodano również obszary lokalne (choć nie wszystkie dzielnice zdecydowały się na ich utworzenie — np. Rembertów i Wola zrezygnowały z wyznaczania takich obszarów), przez co liczba instancji wzrosła do 75.</p><p>Na przykład dzielnica <a href="https://bo.um.warszawa.pl/pages/obszary-i-kwoty">Ursus została podzielona na</a>:</p><ul><li>projekty dzielnicowe</li><li>projekty lokalne: Gołąbki, Szamoty, Niedźwiadek</li><li>projekty lokalne: Czechowice, Skorosze</li></ul><p>Środki przeznaczone na projekty lokalne zostały wydzielone z puli dzielnicowej, a cały budżet obywatelski podzielono na trzy poziomy:</p><ul><li>miejski (20% środków),</li><li>dzielnicowy (30%),</li><li>lokalny (50%).</li></ul><p>To w pewnym sensie <strong>powrót do modelu obowiązującego do 2018 roku</strong>, kiedy również istniał podział na projekty dzielnicowe i lokalne.</p><p>Zmieniły się również zasady zgłaszania projektów:</p><ul><li>projekty lokalne wymagają 5 podpisów poparcia,</li><li>dzielnicowe — 10,</li><li>miejskie — 15.</li></ul><p>Zmodyfikowano też zasady głosowania. Wcześniej można było wybrać maksymalnie 15 projektów w jednej dzielnicy oraz do 10 projektów na poziomie ogólnomiejskim. Od 2025 roku każdy mieszkaniec może oddać <strong>maksymalnie 5 głosów na każdym z trzech poziomów</strong>: lokalnym, dzielnicowym i miejskim.</p><p>Co ciekawe, <strong>nie trzeba głosować na projekty z dzielnicy ani obszaru, w którym się mieszka</strong>. Można wybrać dowolny rejon miasta, np. ten, w którym się pracuje czy spędza najwięcej czasu. Głosować można jednak wyłącznie na projekty z <strong>jednego</strong> wybranego rejonu.</p><h3>Kryzys?</h3><p>Widać wyraźnie, że liczba projektów dopuszczanych do głosowania systematycznie spada.</p><p>Samo w sobie nie musi to jednak oznaczać niczego złego — być może działa skuteczniejsza preselekcja, a miasto sprawniej odrzuca propozycje niespełniające kryteriów.</p><p>Z drugiej strony, wykres przedstawiający liczbę <strong>zgłoszonych projektów</strong> w porównaniu do liczby <strong>projektów dopuszczonych do głosowania</strong> sugeruje coś innego: obie linie mają bardzo podobny kształt. Nie wygląda na to, że to selekcja decyduje o spadku liczby projektów — po prostu mieszkańcy zgłaszają ich coraz mniej.</p><p>Czy to powód do niepokoju? A może wręcz przeciwnie — może mieszkańcy są już tak zadowoleni, że po prostu nie mają więcej pomysłów? 😊</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*OgJzpeZgk5klYnN1jCaSRw.png" /></figure><p>Pierwsza reforma (między 2018 a 2019 rokiem) wpłynęła negatywnie na liczbę zgłaszanych projektów — częściowo dlatego, że liczba obszarów głosowania spadła z 90 w 2018 roku do zaledwie 19 w 2019. Tak duże ograniczenie siłą rzeczy zmniejszyło liczbę pomysłów.</p><p>Z kolei tegoroczna reforma, która ponownie zwiększyła liczbę obszarów (do 75), przyniosła tylko <strong>nieznaczny wzrost</strong> liczby projektów.</p><p>W połączeniu z malejącą liczbą głosujących wydaje się to niepokojącym trendem, który może świadczyć o spadającym zaangażowaniu mieszkańców:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*59QQ8aKH5VvO2F3fiJ07_A.png" /></figure><p>Co prawda po tegorocznej reformie liczba oddanych głosów wzrosła, ale nie można tego bezpośrednio porównywać do lat wcześniejszych. W 2024 roku każdy mieszkaniec mógł zagłosować maksymalnie dwa razy — na jeden projekt ogólnomiejski i jeden dzielnicowy.</p><p>W 2025 roku dopuszczono już <strong>trzy głosy</strong>: na poziomie lokalnym, dzielnicowym i miejskim. To naturalnie zwiększyło łączną liczbę głosów, ale nie musi oznaczać większego zaangażowania.</p><p>Dlatego właściwszym wskaźnikiem aktywności społecznej jest nie liczba głosów, ale <strong>liczba głosujących</strong>. Jak ona wygląda?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*V0iHNVHbs5AQOk-mhPCVsw.png" /></figure><p><strong>Poprzednia reforma przyczyniła się do wzrostu liczby głosujących, jednak obecna nie przyniosła już podobnego efektu.</strong></p><p>Wydawałoby się, że zejście na niższy poziom — wprowadzenie podziału na obszary lokalne — powinno zmotywować więcej osób do głosowania. W końcu projekty lokalne pozwalają się z nimi silniej utożsamiać.</p><p>Dlaczego obserwujemy taki trend? Skąd ten “kryzys”? Przecież pieniędzy w budżecie partycypacyjnym jest coraz więcej, świadomość społeczna wydaje się rosnąć, a głosowanie online staje się coraz prostsze — narzędzia do głosowania są coraz lepiej zaprojektowane. Mimo to coraz mniej osób angażuje się w wybór projektów, co jest niepokojące.</p><p>Jeżeli przyjmiemy, że w Warszawie <a href="https://um.warszawa.pl/statystyka-warszawy-2025">mieszka około 1,8mln osób</a>, a prawo głosu przysługuje każdemu mieszkańcowi (nawet noworodkom), to <strong>frekwencja na poziomie zaledwie 4% w 2025 roku</strong> — przy 72 524 głosujących — jest wręcz dramatyczna.</p><p>To oznacza, że prawie <strong>96% mieszkańców</strong> nie skorzystało z możliwości wpływu na wydatkowanie miejskich środków.</p><p>Co więcej, tylko <strong>54 238 osób wykorzystało pełnię przysługujących im głosów</strong> — oddając głos na projekt ogólnomiejski, dzielnicowy i rejonowy. 8 370 mieszkańców zagłosowało tylko w jednej kategorii, a 9 916 w dwóch.</p><h3><strong>Sposób głosowania</strong></h3><h4><strong>approval</strong></h4><p>W Warszawie stosuje się <strong>sposób głosowania aprobatowego</strong> (tzw. approval), który pozwala wyborcom wskazać dowolną liczbę projektów (do ustalonego limitu — w tym przypadku pięciu), na których realizacji zależy im najbardziej. Jest to jeden z najczęściej spotykanych formatów głosowania w Polsce. Głosowanie aprobatowe stosowane jest w 6 z 9 miast, dla których posiadamy dane — m.in. w Warszawie, Łodzi, Świeciu i Wieliczce. W innych miastach, takich jak Poznań czy Wrocław, używa się natomiast formatu <em>choose-1</em>, będącego szczególnym przypadkiem głosowania aprobatowego, w którym wyborca może wskazać tylko jeden projekt.</p><p>Wyborcy mogli zatem wybrać do pięciu projektów. Zobaczmy, ilu z nich wykorzystało tę możliwość w pełni:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*8x2DBodVPmgheiPbA4T8ug.png" /></figure><p>Widać, że zdecydowania większość głosów to wybranie maksymalnej liczby projektów (przy założeniu, że głosujący na projekty ogólnomiejskie, dzielnicowe i lokalne to 3 osobne głosy). <strong>Średnia liczba aprobowanych projektów wyniosła 4,43</strong>.</p><p>Głosujący lubią wykorzystywać głos w pełni, biorąc pod uwagę, że były instancje, gdzie głosujący nie mogli wskazać pięciu projektów, bo nawet ich tylu nie było.</p><p>Taki przypadek miał na przykład miejsce w lokalnym głosowaniu Mokotów — Sielce, gdzie… był tylko jeden projekt do wybrania:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WEUe7fGlJK7VN4gb8qSWKQ.png" /><figcaption><a href="https://pabulib.org/?city=Warszawa&amp;year=2025">pabulib.org</a></figcaption></figure><p>Wydawać by się mogło, że takie głosowanie nie ma sensu, ale żeby projekt został ufundowany musi zdobyć minimalną liczbę głosów. I tak, 30 dla obszarów lokalnych, 150 dla dzielnicowych i 3000 dla ogólnomiejskich. W takim wypadku, nawet jeśli projekt nie miał konkurencji, nie może być pewien ufundowania :)</p><p>Jeżeli jest ustalony górny limit na projekty które można zagłosować w systemie aprobatowym, większość wyborców zagłosuje na maksimum projektów. To nie dziwi, patrząc na rok poprzedni. W głosowaniu ogólnomiejskim limit był 10 głosów:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*mUGr1bspfr_DQJcsLvQ_dw.png" /></figure><p><strong>Prawie 70% głosujących zagłosowało na 10 projektów</strong>. Średnia długość głosu 8,55.</p><p>A jak to wyglądało w głosowaniach na projekty dzielnicowe (wszystkie razem)?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/844/1*zMQ2E7xG_1AZaGm5OfatmQ.png" /></figure><p>W Wieliczce (głosowanie w 2023 roku) przeprowadzono osobną edycję głosowania w ramach tzw. <strong>Green Budget</strong>, w której nie obowiązywał żaden limit liczby projektów, na które można było oddać głos. Teoretycznie można więc było zaznaczyć wszystkie dostępne projekty — pytanie tylko, czy taki głos miałby jakikolwiek sens… :)</p><blockquote><strong>Fun fact</strong>:<br>W tej edycji do wyboru były aż 64 projekty. I choć to może zaskakiwać — aż 8 z 6586 głosów zostało oddanych na wszystkie dostępne propozycje!</blockquote><p>A więc, co jak tego ograniczenia nie będzie?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/794/1*64raZnuyRH949EMik9QwgQ.png" /></figure><p>W takiej sytuacji większość głosujących wybierze tylko jeden głos!</p><p>Tymczasem przy identycznym sposobie głosowania — jak np. w Warszawie, gdzie obowiązuje limit maksymalnie pięciu projektów — głosujący często chcą “wykorzystać” swój głos do maksimum i zaznaczają więcej propozycji. Gdy brak takiego limitu, wiele osób unika „rozwadniania” swojego głosu, wybierając tylko jeden projekt. To bardzo ciekawe z psychologicznego punktu widzenia, jak sama obecność ograniczenia wpływa na strategię głosowania.</p><p>Dla porównania warto spojrzeć na przykład Assen (Holandia), gdzie zamiast limitu maksymalnego wprowadzono <strong>dolny limit</strong> — głosujący musieli wybrać <strong>co najmniej dwa projekty</strong>.</p><p>Jak myślicie — na ile projektów zagłosowała większość głosujących?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*yFyWgcfaO2PVO77-GphjQQ.png" /></figure><h4><strong>cumulative</strong></h4><p>Natomiast w Częstochowie oraz Katowicach zastosowano sposób głosowania <strong>cumulative</strong>.</p><p>W tym formacie głosujący otrzymuje określoną liczbę punktów (np. 10), które może dowolnie rozdzielić pomiędzy wybrane projekty. Przykładowo: można przyznać jednemu projektowi wszystkie 10 punktów, rozdzielić je po równo między pięć projektów (po 2 punkty każdy), albo wskazać aż dziesięć projektów po 1 punkcie.</p><p>To sposób głosowania, który daje dużą elastyczność i — przynajmniej w teorii — pozwala precyzyjnie oddać preferencje głosujących. Jeśli ktoś szczególnie ceni jeden projekt, może przydzielić mu wszystkie punkty. Świetnie, prawda?</p><p>Niestety, ten system ma dwa istotne problemy.</p><p>Po pierwsze: co zrobić, jeśli dwa projekty podobają mi się równie mocno, ale jeden z nich jest dwa razy droższy? Czy powinienem przyznać im tyle samo punktów, czy proporcjonalnie więcej temu droższemu?</p><p>Po drugie: w praktyce…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*2zi19H4sS3pO8s85A900pw.png" /></figure><p>głosujący i tak decydują się na jeden projekt! W Częstochowie, gdzie głosujący mają do dyspozycji 10 punktów widać wyraźnie, że i tak wybierają jeden i (z reguły) dają mu 10 punktów.</p><blockquote><strong>Fun fact</strong>:<br>Napisałem „z reguły”, bo są sytuacje — prawdopodobnie wynikające z niezrozumienia zasad głosowania albo trudności technicznych — w których głosujący <strong>nie wykorzystują wszystkich dostępnych punktów</strong>. Z punktu widzenia efektywności głosu jest to zupełnie niezrozumiałe: oddając mniej punktów, niejako <strong>osłabiają siłę własnego głosu</strong>.</blockquote><p>Jak to było natomiast w Katowicach, gdzie do dyspozycji głosującego są 3 punkty? Nie zgadniecie…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*SWapzGw1lkyXSDS0Ef1C0g.png" /></figure><h4><strong>ordinal</strong></h4><p>W Krakowie stosowany jest sposób głosowania <strong>ordinal</strong> — polegający na <strong>szeregowaniu projektów w kolejności preferencji</strong>: od najbardziej do najmniej pożądanego. Głosujący tworzą więc <strong>ranking projektów</strong>, a nie tylko wybierają je na zasadzie „tak/nie” lub przydzielają punkty w sposób dowolny.</p><p>W krakowskim wariancie tego systemu głosujący <strong>muszą wskazać dokładnie trzy projekty</strong>, co automatycznie przekłada się na przypisanie im odpowiednio 3, 2 i 1 punktu. Długość głosu jest więc stała — zawsze wynosi 3. Co ważne, ordinal sam w sobie nie musi oznaczać przypisywania punktów — ale akurat w Krakowie właśnie tak działa jego implementacja.</p><h3>System liczenia głosów</h3><p>Skoro mamy już sposób głosowania, <strong>czas je policzyć</strong>!</p><h4>greedy</h4><p>We wszystkich (no, prawie wszystkich — o tym za chwilę…) polskich miastach stosowany jest ten sam mechanizm wyboru projektów: <strong>metoda zachłanna</strong>, znana też jako <em>greedy</em>.</p><p>Na czym to polega? Projekty są sortowane według liczby zdobytych głosów — od najwyższej do najniższej — i finansowane w tej kolejności, aż do wyczerpania budżetu. Jeśli dany projekt się nie mieści (bo np. przekracza pozostałą pulę środków), przechodzimy do kolejnego, który się mieści, i tak aż do momentu rozdysponowania całości środków.</p><p>W Warszawie działa dodatkowo <strong>próg minimalnej liczby głosów</strong>, który projekt musi osiągnąć, żeby w ogóle mógł zostać sfinansowany. W przypadku głosowania ogólnomiejskiego ten próg wynosi <strong>3000 głosów</strong>. Co to oznacza? Nawet jeśli w budżecie zostałyby jeszcze środki, ale dany projekt zdobył np. 2900 głosów — <strong>nie zostanie zrealizowany</strong>.</p><p>Choć metoda <em>greedy</em> jest prosta i zrozumiała, <strong>budzi też pewne wątpliwości</strong>. Przede wszystkim może faworyzować duże, popularne projekty kosztem mniejszych, lokalnych inicjatyw. Ale o tym — za chwilę.</p><h4>wątpliwości</h4><p>Przypuśćmy, że mamy bardzo specyficzny przypadek. Miasto składa się z dwóch dzielnic:</p><ul><li>Dzielnica A — reprezentuje 51% populacji,</li><li>Dzielnica B — 49%.</li></ul><p>Do rozdysponowania w budżecie partycypacyjnym jest 500 000 PLN. Istnieje 6 projektów, podzielonych równo pomiędzy obie dzielnice:</p><ul><li><strong>Dwa duże projekty</strong> po 300 000 PLN</li><li><strong>Dwa średnie projekty</strong> po 150 000 PLN</li><li><strong>Dwa małe projekty </strong>po 50 000 PLN</li></ul><p>(wybaczcie moje umiejętności w paincie… :D )</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/822/1*zF51Tkf0Snpb6O4SZ3l4qw.png" /><figcaption><a href="https://www.ijaniszewski.pl/pabulib">https://www.ijaniszewski.pl/pabulib</a></figcaption></figure><p><strong>Scenariusz głosowania:</strong></p><p>W tym skrajnym przypadku wszyscy wyborcy w każdej dzielnicy głosują tylko na projekty w swojej dzielnicy.</p><p>Ponieważ większa dzielnica (51% populacji) ma większość głosów, tylko projekty w tej dzielnicy zostaną sfinansowane.</p><p>Mniejsza dzielnica, mimo posiadania własnych projektów, nie otrzyma żadnych funduszy, ponieważ nie ma wymaganej większości głosów.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/827/1*naD4VeF2xzHz5YjaTAix-g.png" /><figcaption><a href="https://www.ijaniszewski.pl/pabulib">https://www.ijaniszewski.pl/pabulib</a></figcaption></figure><p><strong>To wynik, który ukazuje kluczową wadę zachłannej metody wybierania projektów:</strong> większa dzielnica może zdominować proces, a realizowane będą tylko jej projekty.</p><p>Co możemy w takim razie z tym zrobić? O tym pod koniec artykułu.</p><h3>Garść statystyk</h3><p>Jak wyglądał przeciętny głosujący?</p><h4>wiek</h4><p>Najwięcej głosujących było w przedziale wiekowym 30–40:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*miz4CceeMzKUAXdsY1hzfA.png" /></figure><p>A czy średni wiek się zmienia?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*XkzOOssdC49dkYyD01e26w.png" /></figure><h4><strong>płeć</strong></h4><p>Zdecydowana większość głosujących to kobiety. Prawie 2/3!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/685/1*XtBeccuhZkEBTLZTBA_JXg.png" /></figure><p>I tak jest od lat!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*oEtcSoikDI7223lE_J2KXQ.png" /></figure><h4><strong>metoda głosowania</strong></h4><p>głosować można było papierowo lub przez internet. Ile głosów zostało oddanych papierowo? 852, co daje 0,45% wszystkich głosów. Przez internet oddano 190 064 głosów.</p><p>Czy udział głosów “na papierze” spada?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/989/1*m9N7jPhnIGJfYU_ttJS5zA.png" /></figure><p>Wydaje się, że spadł już lata temu (2019) i pewnie utrzyma się na takim samym poziomie.</p><h4>kategorie</h4><p>Projekty z jakiej kategorii cieszą się największym powodzeniem? Tutaj jakie były dostępne do głosowania:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kyl879gKrJuBwa-bvfU8Dw.png" /></figure><p>Zdecydowanie przestrzeń publiczna. A jakie z nich zostały wybrane?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*f9bybhELDDvE9eoYl12dtg.png" /></figure><p>Widać tylko, że zieleń miejska jest chętniej wybierana niż zgłaszana :)</p><h4>ufundowane projekty</h4><p>Ogólnie ufundowano 551 z 1373 projektów (40.13%):</p><ul><li>ogólnomiejskie: 17 z 83 projektów (20.48%)</li><li>dzielnicowe: 156 z 424 projektów (36.79%)</li><li>lokalne: 378 z 866 projektów (43.65%)</li></ul><h3>Pabulib</h3><p>OK, ale skąd my w ogóle mamy te dane? Z <a href="https://pabulib.org/">pabulib.org</a>.</p><p>Pabulib (od <strong>Pa</strong>rticipatory <strong>Bu</strong>gdeting <strong>Lib</strong>rary) to otwarta baza danych z głosowań w Budżetach Obywatelskich.</p><p>Po zakończonym głosowaniu w danym mieście, wysyłamy prośbę o udostępnienie wyników. Dane te dostajemy w przeróżnym formacie (czasem nawet w PDFach), chociaż na szczęcie już się to zmienia. Przekształcamy je do jednolitego formatu i umieszczamy na stronie. Do tej pory (z wiadomych względów) najwięcej plików mamy z Polski, ale powoli rośnie nam “baza” danych z zagranicy.</p><p>Dzięki temu, naukowcy z całego świata są w stanie przeprowadzać badania i np. pracować nad lepszymi metodami liczenia głosów. Jedną z nich jest <a href="https://pl.wikipedia.org/wiki/Metoda_r%C3%B3wnych_udzia%C5%82%C3%B3w"><strong>metoda równych udziałów</strong></a>, o której można posłuchać tutaj:</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FRYK3heBDKJ4%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DRYK3heBDKJ4&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FRYK3heBDKJ4%2Fhqdefault.jpg&amp;type=text%2Fhtml&amp;schema=youtube" width="640" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/e8f7dc59ee913112fee58f9f7906178c/href">https://medium.com/media/e8f7dc59ee913112fee58f9f7906178c/href</a></iframe><p>Została już ona wdrożona w życie <a href="https://glos24.pl/zielony-milion-rusza-nowatorski-projekt-bo-w-wieliczce-wideo">w Wieliczce (Zielony Budżet)</a> oraz <a href="https://swiecie.eu/budzet-obywatelski-na-nowych-zasadach-rada-miejska-w-swieciu-przyjela-zmienione-zasady-glosowania/">w Świeciu</a>. W tym roku metodą tą również <a href="https://bip.um.pruszkow.pl/artykul/750/9170/bo2026-x-edycja">zostaną wybrane projekty w Pruszkowie</a>!</p><p>Nie da się ukryć, że takie decyzje powodują wielkie chęci w dalszej, mozolnej pracy zbierania danych Pabulibowych :)</p><p>W momencie pisania tego tekstu (lipiec 2025), mamy na stronie już <strong>1399</strong> plików <strong>.pb</strong>! Oprócz Polski, mamy także dane z Holandii, Szwajcarii i Francji (oraz z USA i z Kanady):</p><ul><li>Amsterdam</li><li>Assen</li><li>Częstochowa</li><li>Gdańsk</li><li>Gdynia</li><li>Katowice</li><li>Kraków</li><li>Łódź</li><li>Poznań</li><li>Świecie</li><li>Toulouse</li><li>Warszawa</li><li>Wieliczka</li><li>Wrocław</li><li>Zabrze</li><li>Zurich</li></ul><p>Co łącznie daje <strong>31 542 projektów</strong>, na które można było zagłosować, oraz <strong>6 585 129 głosów</strong>!</p><p>Mam nadzieję, że to tylko chwilowy “kryzys” i osób zaangażowanych w Budżet Obywatelski będzie coraz więcej. Może pomogą w tym lepsze metody liczenia głosów? Jeżeli możemy się do tego przyczynić danymi z Pabuliba, to jest to największa motywacja do rozwijania tego projektu.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=860e55db3c4c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Getting Started with Apache Iceberg: Local Setup with MinIO, PyIceberg, and REST Catalog]]></title>
            <link>https://ijaniszewski.medium.com/getting-started-with-apache-iceberg-local-setup-with-minio-pyiceberg-and-rest-catalog-7a2428c54e6e?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/7a2428c54e6e</guid>
            <category><![CDATA[tutorial]]></category>
            <category><![CDATA[lakehouse-architecture]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[pyiceberg]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Mon, 07 Jul 2025 08:15:36 GMT</pubDate>
            <atom:updated>2025-07-09T06:03:17.902Z</atom:updated>
            <content:encoded><![CDATA[<h3>Getting started with Apache Iceberg: local setup with MinIO, PyIceberg, and REST Catalog</h3><p>Here’s <a href="https://github.com/ijaniszewski/iceberg-local-setup?tab=readme-ov-file">the full code</a>.</p><h3>Introduction</h3><p>Apache Iceberg is a high-performance table format for large analytic datasets. It brings <strong>ACID transactions</strong>, <strong>schema evolution</strong>, and <strong>time travel</strong> to object storage systems like S3 — transforming them into reliable, queryable Lakehouse platforms.</p><p>In this guide, we’ll walk through a <strong>local setup</strong> that demonstrates both manual and fully managed approaches to working with Iceberg:</p><ul><li><strong>MinIO</strong>: a self-hosted, S3-compatible object store</li><li><strong>Iceberg REST Catalog</strong>: lightweight metadata service for managing Iceberg tables</li><li><strong>PyIceberg</strong>: Python library for programmatically reading and writing Iceberg tables</li><li><strong>StarRocks</strong>: a modern MPP database with native support for Iceberg and full Lakehouse capabilities</li><li>and <strong>Jupyter</strong> <strong>Notebook</strong> for interactive experimentation.</li></ul><p>We will also briefly compare and discuss the <strong>Delta table</strong> format as an alternative Lakehouse solution</p><p>Whether you’re exploring Iceberg for the first time or prototyping a data lakehouse locally, this tutorial will give you a working environment in minutes — no cloud or heavy setup (Spark) needed.</p><h3>Why to use Apache Iceberg?</h3><p>At its core, Apache Iceberg treats your table not as a directory of files, but as a <strong>versioned series of snapshots</strong>, each representing a complete view of the table at a point in time. When you write data, Iceberg doesn’t overwrite files — it creates <strong>new metadata</strong> pointing to newly written files, and updates the snapshot pointer atomically. All data files are tracked through <strong>manifest files</strong>, which list their schema, partitions, and stats. This structure enables <strong>ACID transactions</strong>, <strong>schema evolution</strong>, and <strong>time travel</strong> — all without rewriting existing data. The actual data stays in Parquet or ORC, but Iceberg maintains a powerful metadata (JSON or Avro file) layer that makes it behave like a fully-featured database table, accessible from engines like Spark, Trino, or StarRocks.</p><p>It is a modern table format that brings <strong>reliability and performance</strong> to data lakes:</p><ul><li><strong>ACID Transactions</strong>: safe concurrent writes and consistent reads — no more corrupt tables</li><li><strong>Time Travel</strong>: query historical versions of your data using snapshots.</li><li><strong>Schema Evolution</strong>: add, drop, or rename columns without breaking downstream tools</li><li><strong>Hidden Partitioning</strong>: no need to manually filter by partition columns — Iceberg handles it</li><li><strong>Engine Agnostic</strong>: works with Spark, Trino, Flink, Python, and more</li><li><strong>Cloud-Optimized</strong>: designed to work seamlessly with S3, GCS, and object stores</li><li><strong>Open Format</strong>: 100% open-source under the Apache Foundation — no vendor lock-in</li></ul><blockquote>Iceberg turns your data lake into a <strong>Lakehouse</strong> — combining warehouse reliability with open data and cheap storage.</blockquote><h4><strong>But Let’s Be Real…</strong></h4><p>It’s fair to ask whether tools like Apache Iceberg are overkill — especially when many data stacks could be replaced with a database and a bash script. But in real-world projects, data is messy, teams grow, and requirements change fast. Choosing something like Iceberg isn’t about chasing trends — it’s about being ready when simple stops being enough.</p><blockquote><em>Time travel seems like a demo feature… until your CFO asks why Q1 revenue changed last week. 😉</em></blockquote><h3>Iceberg vs Delta</h3><h4>What’s the catalog?</h4><p>It handles transactions in a way that keeps changes reliable, even when several processes are updating data at the same time. This means queries always return the correct data. To keep things in sync, every read and write to an Iceberg table — no matter which engine is doing the work — goes through the catalog.</p><h4>Delta Lake: easy to start, great for local development</h4><p>Delta Lake is often the simplest way to get started with a lakehouse. You don’t need a separate catalog service, and you can write directly from Python using Pandas or Polars. The <a href="https://pypi.org/project/deltalake/">deltalake</a> package handles schema changes, versioning, and transactions automatically.</p><h4>Apache Iceberg: more control, more flexibility</h4><p>Apache Iceberg separates <em>table metadata</em> from <em>data files</em>. The catalog handles everything about your table structure — schemas, snapshots, partitions, and history — but not the actual data itself. That part is still up to you. In a pure Python setup with PyIceberg, we need to create the data (e.g., using Pandas or PyArrow), configure a catalog, and then load the Iceberg table and call .append() to add the data. So while we don’t need to manually write Parquet files and register them, we still have to manage data creation and interaction through the API.</p><p>This low-level approach is flexible and educational — you get to see how the pieces fit together — but it’s not plug-and-play.</p><p>To make things more practical, we introduce <strong>StarRocks</strong>, a high-performance SQL engine that can connect directly to Iceberg catalogs. With StarRocks, we we can simply use INSERT statements—just like in a traditional database. It handles the serialization, file writing, and Iceberg metadata updates under the hood. It helps explore our Iceberg tables using SQL, and even handle full read/write operations — all while pointing at the same MinIO-backed data lake and catalog. This gives us the best of both worlds: Iceberg’s versioned table format and StarRocks’ familiar query experience.</p><p>Now, you might be wondering — wouldn’t it be easier if we could just write a DataFrame and let the table format handle everything?</p><p>Yes — and that’s exactly what <strong>Delta Lake</strong> does. In the Delta ecosystem, you don’t need a separate catalog service. With Python (via the <a href="https://pypi.org/project/deltalake/">deltalake</a> package), you can directly write Pandas or Polars DataFrames to Delta tables with built-in support for schema evolution, time travel, and transaction logs — all in one step. It’s incredibly convenient for local development or lightweight pipelines.</p><p>But we’ll save that for the <strong>next article</strong> — where we explore DeltaLake with Polars, and compare it to our Iceberg-based stack.</p><h4>What we actually chose in our company (and why)</h4><p>Apache Iceberg is known for its open design and strong future potential. But when choosing between Iceberg and Delta Lake, what matters most isn’t just the tech — it’s <strong>what actually works for your team today</strong>.</p><p>In our case, we ended up choosing Delta for a few practical reasons:</p><ul><li>works out of the box without needing a separate catalog service</li><li>handles automatic schema detection better, which helps when ingesting messy files like CSVs from external systems</li><li>it was already supported in tools like Synapse, so we didn’t need to change much of our setup</li></ul><p>Yes, <strong>Delta and Iceberg both use Parquet under the hood</strong>, and yes, there’s ongoing work toward cross-compatibility. But in the real world, small differences — like catalog support, schema evolution, and ecosystem integration (Snowflake, StarRocks, Dremio, etc.) — often matter more than the big-picture promises.</p><p>For example, in Snowflake (as we use it):</p><ul><li>Delta can be exposed through a special integration layer (delta_catalog_integration).</li><li>Real Iceberg is only supported via <a href="https://www.snowflake.com/en/blog/introducing-polaris-catalog/">Polaris</a> or the built-in catalog, both of which come with extra setup and governance.</li><li>If you’re using tools like PyIceberg, you’ll need proper catalog access — which might not be possible (or easy) with all Snowflake catalog types.</li></ul><h4>Verdict</h4><p>Your choice of table format is usually shaped by the tools you already use, your team’s experience, and what works reliably in your day-to-day setup — not just the technical feature list.</p><p>If you’re looking for quick wins and simplicity, <strong>Delta Lake is hard to beat</strong>. It’s Python-friendly, handles schema evolution automatically, and just works for many local or lightweight use cases.</p><p>But if you’re aiming for a more open, flexible setup that can grow with your architecture — then <strong>Iceberg is worth the extra setup</strong>.</p><p>That’s exactly why this article exists: to show what Iceberg offers. It’s a hands-on way to understand how the pieces fit together — and to decide for yourself whether that effort pays off.</p><h3>Stack Overview</h3><p>Now that we know why Iceberg is powerful and worth exploring, let’s see how we can set it up locally — starting with the storage layer:</p><h4><strong>MinIO</strong></h4><p>a lightweight, S3-compatible object storage system you can run locally with Docker. In our setup, it acts as the <strong>data lake</strong> where Apache Iceberg stores all its metadata and data files. Why to use it?</p><ul><li><strong>S3 API compatible</strong> — Iceberg treats it like real Amazon S3</li><li><strong>Local &amp; fast</strong> — No cloud setup, no costs</li><li><strong>Safe for testing</strong> — Use throwaway credentials and data</li><li><strong>Web UI included</strong> — Browse files at <a href="http://localhost:9001/">http://localhost:9001</a></li></ul><blockquote>MinIO lets us simulate a cloud-based data lake entirely on your laptop — perfect for learning and prototyping.</blockquote><p>With storage handled by MinIO, we now need a way to manage Iceberg tables and interact with them — that’s where the <strong>Iceberg REST Catalog</strong> (for metadata) and <strong>PyIceberg</strong> (for Python access) come in. Together, they let us create, read, and write tables from a simple Python notebook.</p><h4>Iceberg REST Catalog</h4><p>a lightweight service that stores and serves metadata about Iceberg tables. It allows engines and clients (like PyIceberg) to create, list, and load tables over HTTP — no Hive or Spark needed.</p><h4>PyIceberg</h4><p>the official Python client for Apache Iceberg. It lets you interact with Iceberg catalogs and tables directly from Python — perfect for notebooks, scripting, and lightweight data tasks.</p><h4>StarRocks</h4><p>a high-performance MPP SQL engine that can connect to Iceberg catalogs and query Iceberg tables stored (in our case) in MinIO. It gives you the power of SQL on top of your data lake — no manual Parquet writing or catalog juggling required.</p><p><strong>Why include it?</strong></p><ul><li><strong>Native Iceberg support</strong> — read and write directly from catalogs</li><li><strong>Fast, familiar SQL</strong> — no need to learn a new language</li><li><strong>Great for exploration</strong> — query your Iceberg tables immediately after writing them</li></ul><p>StarRocks transforms Iceberg from a low-level table format into a full-featured Lakehouse experience — without needing Spark or Flink.</p><h4>Jupyter notebook</h4><p>I hope there’s no need to explain this one 🙂 — but just to fulfill my journalistic duty: interactive Python environments where you can write, run, and document code in cells — ideal for quick experimentation and data exploration.</p><p>Together, they give us a minimal and cloud-free way to work with Iceberg tables locally — from manual control in PyIceberg, to full SQL access in StarRocks — all from the comfort of a Jupyter notebook.</p><p>While you could use heavier engines like Spark or Flink to work with Iceberg, StarRocks gives us a much lighter and faster experience for local development and interactive exploration.</p><p><strong>Alright, that’s enough theory — let’s get some hands-on experience! 🛠️</strong></p><h3>⚙️ Setup Time!</h3><p>Now that we’ve covered the theory and the tools, let’s spin everything up and get our local Iceberg environment running.</p><h4>Step 1: Clone the project &amp; set the credentials</h4><pre>git clone https://github.com/ijaniszewski/iceberg-local-setup<br>cd iceberg-local-setup<br>cp .env.example .env</pre><p>This .env file contains test credentials and configuration needed for Iceberg, MinIO, and the REST Catalog. You can leave them as-is for local use.</p><blockquote>📝 <strong>Note:</strong> The .env file contains example credentials and configuration for local testing (like admin / password).<br>These are <strong>not secure</strong> and should only be used in a local or demo environment — never in production.</blockquote><h4>Step 2: Start the stack</h4><pre>docker compose up</pre><p>While the stack is building, here’s what’s happening under the hood:</p><p><strong>MinIO</strong> is being initialized with test credentials (admin / password) and exposed via localhost:9000 (API) and localhost:9001 (web UI)</p><pre>  minio:<br>    image: minio/minio:RELEASE.2025-05-24T17-08-30Z<br>    container_name: minio<br>    environment:<br>      - MINIO_ROOT_USER=${MINIO_ROOT_USER}<br>      - MINIO_ROOT_PASSWORD=${MINIO_ROOT_PASSWORD}<br>      - MINIO_DOMAIN=minio<br>    ports:<br>      - 9000:9000<br>      - 9001:9001<br>    command: [ &quot;server&quot;, &quot;/data&quot;, &quot;--console-address&quot;, &quot;:9001&quot; ]<br>    networks:<br>      iceberg-net:<br>        aliases:<br>          - warehouse.minio</pre><p>In the docker-compose.yml, we define a custom network named iceberg-net so that all containers can talk to each other by name.</p><pre>aliases:<br>  - warehouse.minio</pre><p>We are adding also a <strong>hostname alias</strong> for the minio container. It means other containers (like the Iceberg REST catalog) can access MinIO at http://warehouse.minio:9000 — which mimics the format of a real S3 endpoint (s3://warehouse/...).</p><blockquote><em>✅ This alias makes the environment feel more like a real cloud setup while still running entirely locally.</em></blockquote><p>The mc container uses the <strong>MinIO Client</strong> to wait for the MinIO server to be ready, then deletes and recreates the warehouse bucket, setting it to public. This ensures a clean S3-like storage location is available for Iceberg to use every time the stack starts.</p><pre>  mc:<br>    depends_on:<br>      - minio<br>    image: minio/mc:RELEASE.2025-03-12T17-29-24Z<br>    container_name: mc<br>    entrypoint: |<br>      /bin/sh -c &quot;<br>        until (/usr/bin/mc config host add minio http://minio:9000 ${MINIO_ROOT_USER} ${MINIO_ROOT_PASSWORD})<br>        do<br>          echo &#39;...waiting...&#39; &amp;&amp; sleep 1;<br>        done;<br>        /usr/bin/mc rm -r --force minio/warehouse;<br>        /usr/bin/mc mb minio/warehouse;<br>        /usr/bin/mc policy set public minio/warehouse;<br>        tail -f /dev/null<br>      &quot;<br>    environment:<br>      - AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}<br>      - AWS_SECRET_ACCESS_KEY=${AWS_SECRET_ACCESS_KEY}<br>      - AWS_REGION=${AWS_REGION}<br>    networks:<br>      - iceberg-net</pre><p><strong>The REST Catalog</strong> sets up the Iceberg catalog to read/write metadata and data files into your MinIO-backed warehouse bucket</p><pre>  rest:<br>    image: tabulario/iceberg-rest:1.5.0<br>    container_name: iceberg-rest<br>    ports:<br>      - 8181:8181<br>    environment:<br>      - AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}<br>      - AWS_SECRET_ACCESS_KEY=${AWS_SECRET_ACCESS_KEY}<br>      - AWS_REGION=${AWS_REGION}<br>      - CATALOG_WAREHOUSE=s3://warehouse/<br>      - CATALOG_IO__IMPL=org.apache.iceberg.aws.s3.S3FileIO<br>      - CATALOG_S3_ENDPOINT=http://minio:9000<br>    networks:<br>      iceberg-net:<br>        aliases:<br>          - iceberg-rest.minio</pre><p><strong>PyIceberg container</strong> is built from a custom Dockerfile based on python:3.11-slim.</p><pre>FROM python:3.11-slim<br><br>RUN pip install --no-cache-dir \<br>    pandas \<br>    pyarrow \<br>    jupyterlab \<br>    boto3 \<br>    pyiceberg[nessie,aws] \<br>    mysql-connector-python<br><br>WORKDIR /notebooks<br><br># Disable token for dev<br>RUN mkdir -p /root/.jupyter &amp;&amp; \<br>    echo &quot;c.ServerApp.token = &#39;&#39;&quot; &gt;&gt; /root/.jupyter/jupyter_server_config.py &amp;&amp; \<br>    echo &quot;c.ServerApp.password = &#39;&#39;&quot; &gt;&gt; /root/.jupyter/jupyter_server_config.py &amp;&amp; \<br>    echo &quot;c.ServerApp.allow_origin = &#39;*&#39;&quot; &gt;&gt; /root/.jupyter/jupyter_server_config.py<br><br>EXPOSE 8888<br><br>CMD [&quot;jupyter&quot;, &quot;lab&quot;, &quot;--ip=0.0.0.0&quot;, &quot;--port=8888&quot;, &quot;--allow-root&quot;, &quot;--no-browser&quot;]</pre><p>It installs everything needed to work with Iceberg in Python — including:</p><ul><li>pandas for data manipulation in-memory — converting to/from DataFrames and Arrow tables</li><li>pyarrow library for working with Arrow data structures and Parquet files. Needed for Iceberg I/O</li><li>jupyterlab</li><li>boto3 AWS SDK for Python — used to interact with S3-compatible storage like MinIO</li><li>pyiceberg[aws] Python client for Apache Iceberg with support for AWS-related features (e.g., S3, Glue, etc.)</li><li>mysql-connector-pythonMySQL client for Python — required to connect to StarRocks’ SQL interface on port 9030</li></ul><p>We mount the local ./notebooks folder so your work is saved outside the container. For convenience, we disable Jupyter&#39;s password/token setup, making it accessible at <a href="http://localhost:8888/">localhost:8888</a> without login — ideal for local development (but not for production!).</p><p><strong>StarRocks: Frontend + Backend</strong></p><p>StarRocks is our SQL engine — it connects directly to the Iceberg REST Catalog and queries tables stored in MinIO. I’ve moved StarRocks into a separate docker-compose.override.yml to keep the base stack minimal and focused on Iceberg itself. This makes it easy to swap in other engines like Spark or Flink, or run just the Python-based setup without any SQL layer.</p><p><strong>starrocks-fe (frontend)</strong></p><p>FE node is the brain of the cluster — it handles SQL parsing, query planning, metadata management, and coordinates all Backend nodes. It listens on port 9030 for SQL queries (MySQL protocol) and exposes a web UI on port 8030 for monitoring and administration.</p><pre>  starrocks-fe:<br>    image: starrocks/fe-ubuntu:3.3.5<br>    container_name: starrocks-fe<br>    hostname: starrocks-fe<br>    user: root<br>    command: |<br>      bash /opt/starrocks/fe/bin/start_fe.sh --host_type FQDN<br>    ports:<br>      - 8030:8030  # Web UI<br>      - 9020:9020  # RPC communication<br>      - 9030:9030  # MySQL protocol (used for SQL queries)<br>    environment:<br>      - AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}<br>      - AWS_SECRET_ACCESS_KEY=${AWS_SECRET_ACCESS_KEY}<br>      - AWS_REGION=${AWS_REGION}<br>    networks:<br>      - iceberg-net<br>    healthcheck:<br>      test: &#39;mysql -u root -h starrocks-fe -P 9030 -e &quot;SHOW FRONTENDS\G&quot; |grep &quot;Alive: true&quot;&#39;<br>      interval: 10s<br>      timeout: 5s<br>      retries: 3</pre><p><strong>starrocks-be (backend)</strong></p><p>BE node is the execution engine of StarRocks — it handles data storage, query execution, and caching. It connects to the Frontend node and does the heavy lifting behind each SQL query.</p><pre>  starrocks-be:<br>    image: starrocks/be-ubuntu:3.3.5<br>    container_name: starrocks-be<br>    hostname: starrocks-be<br>    user: root<br>    command:<br>      - /bin/bash<br>      - -c<br>      - |<br>        ulimit -n 65535<br>        echo &quot;# Enable data cache&quot; &gt;&gt; /opt/starrocks/be/conf/be.conf<br>        echo &quot;block_cache_enable = true&quot; &gt;&gt; /opt/starrocks/be/conf/be.conf<br>        echo &quot;block_cache_mem_size = 536870912&quot; &gt;&gt; /opt/starrocks/be/conf/be.conf<br>        echo &quot;block_cache_disk_size = 1073741824&quot; &gt;&gt; /opt/starrocks/be/conf/be.conf<br>        sleep 15s<br>        mysql --connect-timeout 2 -h starrocks-fe -P 9030 -u root -e &quot;ALTER SYSTEM ADD BACKEND \&quot;starrocks-be:9050\&quot;;&quot;<br>        bash /opt/starrocks/be/bin/start_be.sh<br>    ports:<br>      - 8040:8040<br>    environment:<br>      - HOST_TYPE=FQDN<br>    depends_on:<br>      starrocks-fe:<br>        condition: service_healthy<br>    networks:<br>      - iceberg-net<br>    healthcheck:<br>      test: &#39;mysql -u root -h starrocks-fe -P 9030 -e &quot;SHOW BACKENDS\G&quot; |grep &quot;Alive: true&quot;&#39;<br>      interval: 10s<br>      timeout: 5s<br>      retries: 3</pre><p>The command:</p><pre>ALTER SYSTEM ADD BACKEND &quot;starrocks-be:9050&quot;</pre><p>tells the <strong>StarRocks Frontend</strong> to register a new <strong>Backend node</strong> at the specified address (starrocks-be:9050).</p><p>This step is required because StarRocks doesn’t auto-discover BE nodes — you must explicitly tell the FE which BE(s) to add to the cluster.</p><h4>OK, is it up and running?</h4><p>your local Apache Iceberg stack should now be up and running!</p><p>Here’s a quick checklist to confirm:</p><ul><li><strong>MinIO</strong> is accessible at <a href="http://localhost:9001/">http://localhost:9001</a><br> <em>(Login: </em><em>admin / </em><em>password)</em></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uxfCk8wKDNe6UW999YvRrA.png" /></figure><ul><li><strong>JupyterLab</strong> is live at <a href="http://localhost:8888/">http://localhost:8888</a> <em>(no token or password needed)</em>, with already prepared intro-to-iceberg notebook</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JZvhwetLW-40fvZ5R4Pe7w.png" /></figure><ul><li><strong>Iceberg REST Catalog</strong> is listening at <a href="http://localhost:8181/">http://localhost:8181</a></li><li><strong>StarRocks </strong>cluster is available at <a href="http://localhost:8030/">http://localhost:8030</a> via its web UI (login with root and empty password), but for most of this guide, we’ll interact with it through the <strong>MySQL-compatible command line interface</strong> on port 9030</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bPfS8eDhRRbndE3jsDKRtQ.png" /></figure><p><em>✅ You now have a fully functional local data lakehouse setup — ready to create, store, and query Iceberg tables directly from a Jupyter notebook. Let’s test it!</em></p><h3>🎉 Party time!</h3><p>Let’s jump into Jupyter and start using Iceberg: open <a href="http://localhost:8888/">http://localhost:8888</a> in your browser — you’ll land in JupyterLab.</p><p>From there, open the file: notebooks/intro-to-pyiceberg.ipynb</p><h4>MinIO explained in practice</h4><p>Before we dive into tables and queries, let’s talk about <strong>MinIO</strong> in practice — our local, S3-compatible storage backend. This gives us a “mocked” version of AWS S3, great for experimentation without touching the cloud. <a href="https://min.io/">MinIO</a> is an open-source object storage server that’s <strong>API-compatible with AWS S3</strong>. That means any tool or library that talks to S3 (like boto3 or Apache Iceberg) can also talk to MinIO — <strong>no code changes required</strong>.</p><p>This acts like your own mini version of AWS S3 — but on your laptop.</p><p>Let’s try it!</p><ol><li>Connect to MinIO</li></ol><pre>import boto3<br>import os<br><br># These come from your docker-compose env vars<br>aws_access_key_id = os.environ[&quot;AWS_ACCESS_KEY_ID&quot;]     # &quot;admin&quot;<br>aws_secret_access_key = os.environ[&quot;AWS_SECRET_ACCESS_KEY&quot;]  # &quot;password&quot;<br>aws_region = os.environ[&quot;AWS_REGION&quot;] # us-east-1<br><br># Mocked S3 client that connects to local MinIO<br>s3 = boto3.client(<br>    &quot;s3&quot;,<br>    endpoint_url=&quot;http://minio:9000&quot;,  # Local MinIO service<br>    aws_access_key_id=aws_access_key_id,<br>    aws_secret_access_key=aws_secret_access_key,<br>    region_name=aws_region<br>)</pre><p>2. Create a new bucket</p><pre>s3.create_bucket(Bucket=&quot;poc&quot;)</pre><p>3. Upload a blob (file)</p><pre>s3.put_object(Bucket=&quot;poc&quot;, Key=&quot;demo.txt&quot;, Body=b&quot;Hello, Iceberg!&quot;)</pre><p>4. List the blobs</p><pre>response = s3.list_objects_v2(Bucket=&quot;poc&quot;)<br>for obj in response.get(&quot;Contents&quot;, []):<br>    print(f&quot;Found object: {obj[&#39;Key&#39;]}&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/960/1*lriVJ7EPTispaCA2ikdOjw.png" /></figure><p>5. Read the file</p><pre>response = s3.get_object(Bucket=&quot;poc&quot;, Key=&quot;demo.txt&quot;)<br>print(response[&quot;Body&quot;].read().decode())</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ae1gv2iYzVONsIn21WKytA.png" /></figure><p>Check it also on <a href="http://localhost:9001/">http://localhost:9001/</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EPgzl6pcmvf0gfdvryNpPA.png" /></figure><p><strong>OK, looks like our connection to the local S3 (MinIO) is working, which will be essential for working with Iceberg.</strong></p><p>Now, let’s clean things up by deleting the test object and the bucket:</p><pre>s3.delete_object(Bucket=&quot;poc&quot;, Key=&quot;demo.txt&quot;)<br>s3.delete_bucket(Bucket=&quot;poc&quot;)</pre><h3>Iceberg Time!</h3><p>Now that our S3-compatible storage (MinIO) is ready, we can shift our focus to <strong>Apache Iceberg</strong>.</p><h4><strong>Picking a catalog</strong></h4><p>Before we can create or query Iceberg tables, we need to configure a <strong>catalog</strong> — Iceberg’s way of managing <strong>table metadata, namespaces, and schemas</strong>. The catalog acts like a registry that tracks where your tables live and how they evolve.</p><p>We’ll connect to the <strong>Iceberg REST catalog</strong>, which acts as the metadata service for managing tables, schemas, and namespaces — all while storing actual data in our local MinIO buckets.</p><p>Here’s how we initialize the catalog connection:</p><pre>catalog = load_rest(<br>    name=&quot;rest&quot;,<br>    conf={<br>        &quot;uri&quot;: &quot;http://rest:8181/&quot;,<br>        &quot;s3.endpoint&quot;: &quot;http://minio:9000&quot;,<br>        &quot;s3.access-key&quot;: aws_access_key_id,<br>        &quot;s3.secret-key&quot;: aws_secret_access_key<br>    }<br>)</pre><ul><li>uri: Points to the REST catalog service (running locally in Docker).</li><li>s3.endpoint: Tells Iceberg to store table data in MinIO, not AWS.</li><li>Credentials: Match those used in your local environment.</li></ul><p>With this setup, Iceberg can now read from and write to our local S3 — no cloud required.</p><p>Since we already have our s3 client from the previous MinIO setup, we can now use it to <strong>peek behind the scenes</strong> and observe how Iceberg interacts with S3.</p><p>Every time we create a table, append data, or modify metadata, Iceberg writes files to S3 (MinIO). To track those changes, here are two simple helper functions:</p><pre>def list_blobs(bucket=None):<br>    &quot;&quot;&quot;<br>    Lists blobs (objects) in a specific S3 bucket or all buckets if none is specified.<br>    &quot;&quot;&quot;<br>    if bucket:<br>        print(f&quot;\nObjects in bucket: {bucket}&quot;)<br>        _print_bucket_objects(bucket)<br>    else:<br>        buckets = s3.list_buckets()[&quot;Buckets&quot;]<br>        for b in buckets:<br>            bucket_name = b[&quot;Name&quot;]<br>            print(f&quot;\nObjects in bucket: {bucket_name}&quot;)<br>            _print_bucket_objects(bucket_name)<br><br>def _print_bucket_objects(bucket_name):<br>    response = s3.list_objects_v2(Bucket=bucket_name)<br>    if &quot;Contents&quot; in response:<br>        for obj in response[&quot;Contents&quot;]:<br>            print(f&quot; - {obj[&#39;Key&#39;]}&quot;)<br>    else:<br>        print(&quot; (Empty)&quot;)</pre><h3>Welcome to the RideShare Platform (with Iceberg!)</h3><p>To make our Apache Iceberg journey more engaging (and realistic), let’s imagine we’re building the backend data platform for a fictional ride-hailing service — think Uber, but cooler (and 100% open source :D) .</p><p>We’ll use a <strong>single powerful table</strong> to demonstrate Iceberg’s best features like t<strong>ime travel or evolutions: </strong>of<strong> schema </strong>(e.g. adding a new column) and of <strong>partition.</strong></p><h4><strong>Creating a Namespace</strong></h4><p>In Iceberg, a <strong>namespace</strong> is like a logical schema or database — it groups related tables under a common name. Think of it as the folder structure for your data lake.</p><p>We need to create a namespace before we can define any tables inside it.</p><pre>from pyiceberg.exceptions import NamespaceAlreadyExistsError<br><br>namespace = &quot;rideshare&quot;<br><br>try:<br>    catalog.create_namespace(namespace)<br>except NamespaceAlreadyExistsError:<br>    pass  # Already exists? Perfect.</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S7Aq93u211qVkbE8rziX-w.png" /></figure><h4>Defining Our Data Model</h4><p>In a typical ride-sharing platform, you’d model several tables — rides, drivers, payments, and so on. But for this demo, we&#39;ll keep things simple with just <strong>one</strong> table — should be enough to explore the core features.</p><p>The rides table will store trip data for our fake app. It’s the kind of table that grows quickly with new data, changes over time as business needs evolve and needs to handle real-world scenarios like:</p><ul><li>Adding new columns (e.g. tip_amount)</li><li>Canceled rides or data corrections</li><li>Queries for recent trips</li><li>Historical audits and debugging</li></ul><h4>Setting up the table</h4><p>Now that we know what our rides table should look like, let’s create it using <strong>PyIceberg</strong> and the <strong>REST catalog</strong>.</p><p>We’ll define the schema, set up partitioning, and create the table in our catalog (backed by MinIO).</p><pre># First, imports<br>from pyiceberg.schema import Schema<br>from pyiceberg.types import (<br>    NestedField, StringType, DateType, DoubleType<br>)<br>from pyiceberg.partitioning import PartitionSpec, PartitionField</pre><h4>Define the table schema</h4><p>When creating tables, you define the schema using NestedField objects — one for each column. NestedField describes:</p><ul><li>field_id: a unique, stable numeric ID (required)</li><li>name: column name</li><li>field_type: type of data (e.g. StringType(), UUIDType())</li><li>required: whether the column is required (NOT NULL)</li></ul><p>Iceberg needs every field to have a fixed field_id — it might feel a bit verbose at first, but it makes sense. Even if a column is renamed or moved, Iceberg uses field_id as the canonical identifier — and that’s what makes safe schema changes possible.</p><p>Here’s what it looks like in practice:</p><pre>rides_schema = Schema(<br>    NestedField(field_id=1, name=&quot;ride_id&quot;, field_type=StringType(), required=True),<br>    NestedField(field_id=2, name=&quot;driver_id&quot;, field_type=StringType()),<br>    NestedField(field_id=3, name=&quot;customer_id&quot;, field_type=StringType()),<br>    NestedField(field_id=4, name=&quot;pickup_time&quot;, field_type=TimestampType()),<br>    NestedField(field_id=5, name=&quot;dropoff_time&quot;, field_type=TimestampType()),<br>    NestedField(field_id=6, name=&quot;fare&quot;, field_type=DoubleType()),<br>    NestedField(field_id=7, name=&quot;pickup_location&quot;, field_type=StringType()),<br>    NestedField(field_id=8, name=&quot;dropoff_location&quot;, field_type=StringType()),<br>    NestedField(field_id=9, name=&quot;status&quot;, field_type=StringType())<br>)</pre><p>To keep things cleaner, you can define a small helper function:</p><pre>def field(id, name, type, required=False):<br>    return NestedField(field_id=id, name=name, field_type=type, required=required)</pre><p>Then use it like this:</p><pre>Schema(<br>    field(1, &quot;driver_id&quot;, StringType(), required=True),<br>    field(2, &quot;full_name&quot;, StringType()),<br>    field(3, &quot;city&quot;, StringType())<br>)</pre><p>This makes your schema definition easier to read.</p><h4><strong>Define partitioning</strong></h4><p>Partitioning is an important feature in Apache Iceberg. It helps speed up queries by splitting data into folders based on column values — so the engine can skip over files it doesn’t need.</p><p>For our rides table, we’ll partition by pickup_time. That way, if we run queries for recent trips (like the last 7 days), they’ll be faster and more efficient.</p><p>In PyIceberg, we define partitioning using a PartitionSpec that contains one or more PartitionField objects.</p><p>Each PartitionField includes:</p><ul><li>source_id: the field_id of the column you&#39;re partitioning by (in our case, pickup_time is field_id=4)</li><li>field_id: a unique ID for the partition field (just make sure it doesn’t clash with any column field_id)</li><li>transform: how to split the values (identity, day, bucket, etc.)</li><li>name: the name of the partition field</li></ul><pre>rides_partition_spec = PartitionSpec(<br>    fields=[<br>        PartitionField(<br>            source_id=10,      # 👈 pickup_day&#39;s field ID<br>            field_id=1000,     # unique ID for this partition<br>            transform=&quot;identity&quot;,<br>            name=&quot;pickup_day&quot;<br>        )<br>    ]<br>)</pre><h4>Create the table</h4><p>Now that we have our schema and partitioning defined, we can finally create the table.</p><p>We’ll use the catalog we loaded earlier (connected to Iceberg via REST), and call create_table with the schema and partition spec we just built.</p><pre>catalog.create_table(<br>    identifier=f&quot;{namespace}.rides&quot;,<br>    schema=rides_schema,<br>    partition_spec=rides_partition_spec<br>)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/491/1*XxQQ_vFArDkIz3fEB_2omg.png" /></figure><h4>Iceberg Metadata: What’s Stored Behind the Scenes?</h4><p>Now, if you check object storage (MinIO), you’ll see <strong>a folder structure is already there</strong> — even though we haven’t written any data yet:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/995/1*HPHV4PZSyMAfkb5cAFEaug.png" /><figcaption>Screenshot: MinIO shows the rideshare/rides/metadata/ folder with a metadata.json file.</figcaption></figure><p>That’s because Apache Iceberg <strong>manages table metadata separately</strong> from your actual data files. It tracks everything about the table — schema, partitioning, snapshots, settings — in JSON files like this one.</p><p>You can also (obviously) read the metadata from Notebook:</p><pre>import json<br><br>response = s3.list_objects_v2(Bucket=&quot;warehouse&quot;, Prefix=&quot;rideshare/rides/metadata/&quot;)<br><br>for obj in response.get(&quot;Contents&quot;, []):<br>    obj_name = obj[&quot;Key&quot;]<br>    print(f&quot;Found file: {obj_name}&quot;)<br>    <br>    if obj_name.endswith(&quot;metadata.json&quot;):<br>        response = s3.get_object(Bucket=&quot;warehouse&quot;, Key=obj_name)<br>        content = response[&quot;Body&quot;].read().decode()<br>        metadata = json.loads(content)<br>        <br>        print(&quot;\n--- Parsed metadata.json ---\n&quot;)<br>        print(json.dumps(metadata, indent=2))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/896/1*CGBD-7hB4JB5ooWGjMwKLA.png" /></figure><p>So, what’s in metadata.json file?</p><pre>{<br>  &quot;format-version&quot;: 2,  // Iceberg table format version<br>  &quot;table-uuid&quot;: &quot;4a467d5b...&quot;,  // Unique ID for the table<br>  &quot;location&quot;: &quot;s3://warehouse/rideshare/rides&quot;,  // Table root location</pre><p>This tells us where the table lives and that it’s using format version 2 (the latest, with support for advanced features like deletes and branching).</p><pre>  &quot;schemas&quot;: [<br>    {<br>      &quot;fields&quot;: [<br>        { &quot;id&quot;: 1, &quot;name&quot;: &quot;ride_id&quot;, &quot;type&quot;: &quot;uuid&quot; },<br>        { &quot;id&quot;: 2, &quot;name&quot;: &quot;driver_id&quot;, &quot;type&quot;: &quot;string&quot; },<br>        ...<br>      ]<br>    }<br>  ],</pre><p>This section shows the <strong>full schema</strong> of the table — each field with its name, type, and field_id. This is how Iceberg tracks columns even if you rename or reorder them.</p><pre>  &quot;partition-specs&quot;: [<br>    {<br>      &quot;fields&quot;: [<br>        { &quot;source-id&quot;: 4, &quot;transform&quot;: &quot;day&quot;, &quot;name&quot;: &quot;pickup_day&quot; }<br>      ]<br>    }<br>  ],</pre><p>Here, Iceberg stores the <strong>partitioning config</strong> — we’re partitioning by pickup_time (field ID 4), using the day transform, and naming it pickup_day.</p><pre>  &quot;snapshots&quot;: [],</pre><p>No data yet — hence no snapshots. Once we insert data, this section will start tracking every write (with snapshot IDs and timestamps).</p><p>So even before writing a single row, the table is fully defined — schema, partitions, properties — and Iceberg is ready to track everything we do next.</p><p><strong>Now That We Have a Catalog…</strong></p><h3>Let’s Add Some Data</h3><h4>Writing data to Iceberg (the manual way)</h4><p>Now that we have a table defined in our Iceberg catalog, it’s time to write some data. But unlike traditional databases, writing to Iceberg manually — especially in Python — means understanding a bit more about what’s happening under the hood.</p><p>Right now, PyIceberg doesn’t offer a high-level .write_table() method like Delta Lake does. So we need to handle things more manually — first by creating the data, then registering it ourselves. Unlike systems like StarRocks, where adding a new field is handled automatically during insert, with Iceberg we need to explicitly manage schema updates and data registration.</p><ol><li>Generate some example data (e.g. with Pandas)</li></ol><pre>import pandas as pd<br>from uuid import uuid4<br>from datetime import datetime<br><br>df = pd.DataFrame([<br>    {<br>        &quot;ride_id&quot;: str(uuid4()),  # use string UUID<br>        &quot;driver_id&quot;: &quot;driver_001&quot;,<br>        &quot;customer_id&quot;: &quot;cust_001&quot;,<br>        &quot;pickup_time&quot;: datetime(2025, 2, 1, 8, 30).date(),<br>        &quot;dropoff_time&quot;: datetime(2025, 2, 1, 8, 50).date(),<br>        &quot;fare&quot;: 23.5,<br>        &quot;pickup_location&quot;: &quot;Downtown&quot;,<br>        &quot;dropoff_location&quot;: &quot;Airport&quot;,<br>        &quot;status&quot;: &quot;completed&quot;<br>    }<br>])<br><br>df[&quot;pickup_day&quot;] = df[&quot;pickup_time&quot;]</pre><p>2. Convert it to Arrow table.</p><p>We <strong>must</strong> convert to an Arrow table because PyIceberg doesn’t handle raw Pandas DataFrames or data serialization directly — it expects you to write properly formatted <strong>Parquet files</strong> yourself. PyArrow is the bridge: it lets us define the exact schema and write the data in a format Iceberg can track and manage reliably.</p><pre>import pyarrow as pa<br><br># Arrow schema<br>schema = pa.schema([<br>    pa.field(&quot;ride_id&quot;, pa.string(), nullable=False),<br>    (&quot;driver_id&quot;, pa.string()),<br>    (&quot;customer_id&quot;, pa.string()),<br>    (&quot;pickup_time&quot;, pa.date32()),<br>    (&quot;dropoff_time&quot;, pa.date32()),<br>    (&quot;fare&quot;, pa.float64()),<br>    (&quot;pickup_location&quot;, pa.string()),<br>    (&quot;dropoff_location&quot;, pa.string()),<br>    (&quot;status&quot;, pa.string()),<br>    (&quot;pickup_day&quot;, pa.date32())<br>])<br><br># Convert to Arrow Table<br>table = pa.Table.from_pandas(df, schema=schema, preserve_index=False)</pre><p>3. Register it with the Iceberg table</p><p>Now that we have our data in an Arrow table, we can register it with the Iceberg table using just one line: iceberg_table.append(table):</p><pre>table_identifier = &quot;rideshare.rides&quot;<br><br># Just load the table and append the data<br>iceberg_table = catalog.load_table(table_identifier)<br>iceberg_table.append(table)<br><br>print(f&quot;📌 Data appended to Iceberg table: {table_identifier}&quot;)</pre><p>This not only registers the data but also handles writing it as a properly partitioned Parquet file behind the scenes. Check your warehouse/rideshare/rides/data directory on mocked S3:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1018/1*DAVihSr1qFtP_GQ_OQ4o3Q.png" /></figure><p>This might sound like a lot, but it’s super educational — and you’ll actually see what Iceberg is doing behind the scenes.</p><h3>🔍 Querying Iceberg Tables with StarRocks</h3><p>Now that we’ve written a Parquet file and registered it with our Iceberg table (at least in theory), it’s time to validate everything from the SQL side. StarRocks connects directly to the Iceberg catalog and MinIO-backed data lake, letting us explore the table just like we would in a warehouse.</p><p>We have two ways to interact with StarRocks: you can either connect via the command line inside the FE container:</p><pre>docker exec -it starrocks-fe \<br>mysql -P 9030 -h 127.0.0.1 -u root --prompt=&quot;StarRocks &gt; &quot;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/582/1*Oj0NhrrZXiX3fZu66ueMfg.png" /></figure><p>or use a MySQL client directly from the Jupyter notebook. All commands will be included in the Jupyter notebook for reference, but some screenshots may reflect the MySQL CLI output instead of notebook cells.</p><p>To make it easier to run SQL queries from inside our Jupyter notebook, we’ll use a small helper function that opens a connection to the StarRocks Frontend node using the MySQL protocol. This starrocks_cursor context manager lets us execute queries with simple with blocks, so we can focus on the SQL and not the connection boilerplate:</p><pre>import mysql.connector<br>from contextlib import contextmanager<br><br>@contextmanager<br>def starrocks_cursor(host=&quot;starrocks-fe&quot;, port=9030, user=&quot;root&quot;, password=&quot;&quot;):<br>    conn = mysql.connector.connect(<br>        host=host,<br>        port=port,<br>        user=user,<br>        password=password<br>    )<br>    cursor = conn.cursor()<br>    try:<br>        yield cursor<br>        conn.commit()<br>    finally:<br>        cursor.close()<br>        conn.close()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/386/1*uYvYoXxb9aujzHSfVXRcfA.png" /></figure><p>From there, we can list tables, inspect schemas, and run actual queries to confirm that our Iceberg metadata and Parquet data are in sync.</p><h4>Let’s register our catalog</h4><p>This SQL statement creates a new <strong>external catalog</strong> in StarRocks named &quot;demo&quot; that connects to our Apache Iceberg tables stored in MinIO:</p><pre># Register catalog<br><br>sql_query = f&quot;&quot;&quot;<br>CREATE EXTERNAL CATALOG &#39;demo&#39;<br>COMMENT &quot;External catalog to Apache Iceberg on MinIO&quot;<br>PROPERTIES<br>(<br>  &quot;type&quot;=&quot;iceberg&quot;,  -- defines this as an iceberg catalog<br>  &quot;iceberg.catalog.type&quot;=&quot;rest&quot;,  -- uses the rest catalog backend<br>  &quot;iceberg.catalog.uri&quot;=&quot;http://iceberg-rest:8181&quot;,  -- rest catalog endpoint<br>  &quot;iceberg.catalog.warehouse&quot;=&quot;warehouse&quot;,  -- root path in s3/minio<br>  &quot;aws.s3.access_key&quot;=&quot;{os.environ[&quot;AWS_ACCESS_KEY_ID&quot;]}&quot;,  -- minio access key<br>  &quot;aws.s3.secret_key&quot;=&quot;{os.environ[&quot;AWS_SECRET_ACCESS_KEY&quot;]}&quot;,  -- minio secret key<br>  &quot;aws.s3.endpoint&quot;=&quot;http://minio:9000&quot;,  -- minio api endpoint<br>  &quot;aws.s3.enable_path_style_access&quot;=&quot;true&quot;,  -- required for minio compatibility<br>  &quot;client.factory&quot;=&quot;com.starrocks.connector.iceberg.IcebergAwsClientFactory&quot;  -- tells starrocks to use aws-style s3 client <br>);<br>&quot;&quot;&quot;<br><br>with starrocks_cursor() as cursor:<br>    cursor.execute(sql_query)<br>    print(&quot;✅ Iceberg catalog registered.&quot;)</pre><p>Check if we see it</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/595/1*AWsmAqXynIAHY74DXMP4gQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Pp_rw03VByyf_8Fcai3mdQ.png" /></figure><p>And query our table :)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bUydVzy7YfHmMWH1vXYpvw.png" /></figure><p>same data as we append via PyIceberg, right?</p><h4>Insert new record</h4><pre>with starrocks_cursor() as cursor:<br>    cursor.execute(&quot;&quot;&quot;<br>        INSERT INTO demo.rideshare.rides VALUES (<br>            &#39;00000000-0000-0000-0000-000000000999&#39;,<br>            &#39;driver_demo&#39;, &#39;customer_demo&#39;,<br>            &#39;2025-06-01 10:00:00&#39;, &#39;2025-06-01 10:15:00&#39;,<br>            12.50, &#39;Downtown&#39;, &#39;Uptown&#39;, &#39;completed&#39;,<br>                        &#39;2025-06-01&#39;  -- 👈 pickup_day<br>        );<br>    &quot;&quot;&quot;)<br>    print(&quot;✅ Row inserted.&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F5cce9qMe6pc1LnbFEOSJw.png" /></figure><h4>Time travel</h4><p>Data isn’t static — it changes constantly. A row might be deleted accidentally, a ride might get updated incorrectly, or a new schema might introduce bugs. In traditional data systems, recovering from this often means restoring backups or manually rebuilding state. Iceberg fixes this.</p><p>With <strong>time travel</strong>, we can query a table <strong>as it existed at any past point in time</strong> — either using a <strong>snapshot ID</strong> or a <strong>timestamp</strong>. This gives us a powerful way to debug, audit, or even compare historical changes without data duplication.</p><p>Apache Iceberg supports <strong>time travel</strong>, allowing you to query historical versions of your data using either snapshot IDs or timestamps. In StarRocks, this is made possible through built-in support for Iceberg metadata tables:</p><ul><li>$snapshots: shows all available snapshots</li><li>$history: shows the snapshot lineage</li><li>$manifests: lists manifest files tied to snapshots</li></ul><p>(<em>Full list: </em><a href="https://docs.starrocks.io/docs/data_source/catalog/iceberg/iceberg_meta_table/"><em>StarRocks metadata tables</em></a>)</p><p>You can query the available snapshots for a table like this:</p><pre>SELECT * FROM demo.rideshare.rides$snapshots;</pre><p>You’ll see metadata for each <strong>snapshot</strong> of your Iceberg table — each representing a version of the table created by a commit (e.g., an insert, delete, or schema change). The key columns in this table are:</p><ul><li>snapshot_id— a unique identifier for the snapshot (used for time travel)</li><li>committed_at — the timestamp when the snapshot was created</li><li>operation — the type of change made — typically append, overwrite, or delete</li><li>summary — a JSON string describing the commit (e.g., number of files added or deleted).manifest_listThe location of the manifest list file, which tracks the data files for the snapshot</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9XRueAcUqHz0O6S8-nsx0w.png" /></figure><p>So, let’s get the <strong>first </strong><strong>snapshot_id</strong>, which represents the <strong>original version of our table</strong> — right after the initial data was inserted:</p><pre>SELECT * FROM demo.rideshare.rides VERSION AS OF 2692934278614844860;</pre><p>Is it our original version, with one row?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VaEv4B0eoz2e622Ue1azzQ.png" /></figure><p>Alternatively, you can time travel using a timestamp:</p><pre>SELECT * FROM demo.rideshare.rides TIMESTAMP AS OF &#39;2025-07-01 09:45:59&#39;;</pre><p>StarRocks will automatically resolve the correct snapshot closest to that point in time.</p><p><em>For details, see the </em><a href="https://docs.starrocks.io/docs/data_source/catalog/iceberg/iceberg_timetravel/"><em>official StarRocks time travel docs</em></a><em>.</em></p><p>Boom — you just looked into the past. This is especially powerful when paired with schema evolution. You can see the <strong>structure and content of the table</strong> exactly as it was — with zero extra setup.</p><h4>Schema Evolution</h4><p>Real-world data changes over time — and Iceberg is built to handle that safely.</p><p>In our RideShare app, we might add a tip_amount column or track a driver_rating. Iceberg supports these changes without table rewrites or data loss.</p><p>Iceberg allows:</p><ul><li><strong>Add</strong>: New columns or fields (including nested)</li><li><strong>Drop</strong>: Remove existing columns</li><li><strong>Rename</strong>: Change column names safely</li><li><strong>Update</strong>: Widen types (e.g., INT → LONG)</li><li><strong>Reorder</strong>: Adjust column order without breaking things</li></ul><p>These are <strong>metadata-only operations</strong> — no rewriting of data files.</p><p>After making a schema change, like:</p><pre>ALTER TABLE demo.rideshare.rides <br>ADD COLUMN tip_amount DOUBLE;</pre><p>Insert new record</p><pre>INSERT INTO demo.rideshare.rides VALUES (<br>    &#39;00000000-0000-0000-0000-000000000999&#39;,<br>    &#39;driver_demo&#39;,<br>    &#39;customer_demo&#39;,<br>    &#39;2025-06-01 10:00:00&#39;,<br>    &#39;2025-06-01 10:15:00&#39;,<br>    12.50,<br>    &#39;Downtown&#39;,<br>    &#39;Uptown&#39;,<br>    &#39;completed&#39;,<br>    &#39;2025-06-01&#39;,   -- pickup_day<br>    2.00            -- tip_amount (new column)<br>);</pre><p>You can inspect how Iceberg recorded that change by querying the table’s metadata log:</p><pre>SELECT * FROM demo.rideshare.rides$metadata_log_entries;</pre><p><strong>Wait — why does </strong><strong>tip_amount show up in an old snapshot?</strong></p><pre>SELECT * FROM demo.rideshare.rides <br>VERSION AS OF 2692934278614844860;</pre><p>You’ll still see the <strong>latest schema</strong> by default — so newly added columns like tip_amount may appear as NULL in older records. This design allows you to keep queries consistent across time, without rewriting them for each schema version.</p><p>If you need to see the <strong>original schema of a snapshot</strong>, you can use tools like PyIceberg or the REST catalog to inspect the snapshot’s schema_id and load that version directly.</p><h4>Partition Evolution</h4><p>Partitioning improves query performance — but business logic and access patterns change over time.</p><p>Iceberg supports <strong>partition evolution</strong>, allowing you to change how data is partitioned <strong>without rewriting old files</strong>. For example, you might start with pickup_day and later switch to pickup_hour, which could look similar to this:</p><pre>ALTER TABLE demo.rideshare.rides <br>SET PARTITIONED BY (days(pickup_time), bucket(driver_id, 8));</pre><p>This tells Iceberg to start writing new data with a new partition layout — by day and bucketed by driver.</p><blockquote><strong>Note</strong>: As of now, PyIceberg and StarRocks do not support this operation natively. In Iceberg, you’ll need to use Spark or Java APIs, and in StarRocks, partition changes require creating a new table with the desired layout.</blockquote><h4>Deleting data</h4><p>Unlike traditional databases, Apache Iceberg is <strong>append-only</strong> under the hood. That means:</p><ul><li>It <strong>never overwrites files directly</strong></li><li>Deletes and updates are tracked as <strong>new metadata + new files</strong></li><li>The original data remains intact in previous snapshots</li></ul><p><strong>What Iceberg Actually Does</strong></p><ol><li>Creates a <strong>new snapshot</strong></li><li>Writes new <strong>position delete</strong> or <strong>rewrite files</strong>:</li></ol><ul><li>For DELETE, it records which row(s) to ignore in future reads</li><li>For UPDATE, it combines a delete + an insert (as Iceberg doesn’t modify rows in place)</li></ul><p>3. Keeps old files intact for time travel and audit</p><p>ATM, StarRocks does <strong>not support deletes</strong> on Iceberg tables via the REST catalog. For example:</p><pre>DELETE FROM demo.rideshare.rides <br>WHERE ride_id = &quot;2d43c9c5-5f75-40da-a3d8-8322f0621a89&quot;;</pre><p>Results in:</p><pre>ERROR 1064 (HY000): Table of iceberg catalog doesn&#39;t support [DELETE].</pre><p>Since deletes aren’t supported, the typical pattern is:</p><blockquote><strong><em>Insert a new record with the same primary key and deduplicate on read.</em></strong></blockquote><p>You simulate updates or corrections by writing new rows and filtering for the latest version during queries.</p><p>As you can see, not everything works out of the box just yet — especially when using <strong>PyIceberg</strong> or lightweight SQL engines like <strong>StarRocks</strong>, compared to something more fully featured like Spark.</p><p>That said, there’s no doubt that <strong>Apache Iceberg is one of the hottest topics in data engineering right now</strong>. It’s quickly becoming the go-to choice for working with large-scale datasets. With this article, I wanted to help you get Iceberg set up locally — but to really discover what it can do, the best next step is to explore it yourself and experiment with its features.</p><p>I know this article doesn’t cover everything, but I hope you at least have Iceberg running locally now — and you’re ready to dive in further on your own.</p><h4>What’s Really Stored: Exploring Iceberg Data Files</h4><p>Iceberg tracks all physical data files ever written to a table — including their schema, partition, and column-level statistics — in a special system table called $files.</p><pre>SELECT * FROM demo.rideshare.rides$files;</pre><p>Each row corresponds to one <strong>Parquet file</strong> written during a snapshot. Here’s what some of the key columns mean:</p><ul><li>file_pathThe full path to the Parquet file in your object storage (e.g. S3, MinIO)</li><li>contentType of file (e.g. 0 = data, 1 = position delete, 2 = equality delete)</li><li>file_formatFormat of the file (e.g. PARQUET)</li><li>record_countNumber of records in the file</li><li>file_size_in_bytesFile size on disk</li><li>column_sizesPer-column byte sizes</li><li>value_countsNumber of values per column</li><li>null_value_countsNumber of nulls per column</li><li>lower_bounds, upper_boundsMin/max values per column — useful for pruning</li><li>split_offsetsRow group offsets for optimized scans</li></ul><p><strong>Example: tip amount appears in new file only</strong></p><pre>11:&quot;2.0&quot;} </pre><p>Only one Parquet file (for pickup_day=2025-07-01) contains column tip_amount.</p><h4>How Iceberg Structures Data on Object Storage</h4><p>When you create an Iceberg table (like rideshare.rides), Iceberg organizes everything into a <strong>directory structure</strong> in your warehouse (e.g., S3, MinIO, GCS). It separates <strong>data</strong> and <strong>metadata</strong>, so the engine can manage and audit your data efficiently.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/663/1*GbP9m0511fkWCsjBNzeLRA.png" /></figure><h3>That’s all Folks!</h3><p>You’ve just gone end-to-end: from spinning up MinIO, REST Catalog, and PyIceberg — to creating your first Apache Iceberg table, writing data, querying it with StarRocks, and exploring powerful features like <strong>schema evolution</strong>, <strong>time travel</strong>, and <strong>partition changes</strong>.</p><p>We’ve seen how Iceberg handles data differently:</p><ul><li>All changes are tracked immutably via <strong>snapshots</strong></li><li>Deletions are not physical — they’re logical and reversible</li><li>Schema and partition evolution happen via <strong>metadata</strong>, not rewrites</li></ul><p>But we’ve also seen the limits:</p><ul><li><strong>PyIceberg</strong> is still maturing (no upserts or partition changes yet)</li><li>For full capabilities, you still need heavier engines like <strong>Spark </strong>or<strong> Flink</strong></li></ul><h4>So, What Now?</h4><p>Apache Iceberg is clearly a <strong>game-changer</strong> for data engineering — especially if you’re working with large-scale datasets, evolving schemas, and the need for reliable time-travel and audit capabilities.</p><p>With this guide, you now have:</p><ul><li>A fully working <strong>local Iceberg setup</strong> (with MinIO, REST, and SQL)</li><li>A deep understanding of how <strong>data and metadata</strong> interact under the hood</li><li>Enough tools to start building, experimenting, and learning on your own</li></ul><p>If you’re just exploring, Iceberg is a fascinating tool to tinker with.</p><p>If you’re building something serious, it’s one worth betting on.</p><p>Either way — you’ve got Iceberg running locally. Now it’s your turn to take it for a spin.</p><p>Thanks for reading, and happy Lakehousing!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7a2428c54e6e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Engineering: Understanding Slowly Changing Dimensions (SCD) with dbt]]></title>
            <link>https://ijaniszewski.medium.com/data-engineering-understanding-slowly-changing-dimensions-scd-with-dbt-98b37f3ddf03?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/98b37f3ddf03</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Sat, 10 May 2025 10:41:06 GMT</pubDate>
            <atom:updated>2025-05-10T10:41:06.658Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction</h3><p>In the world of data engineering, managing how data changes over time is a common — and crucial — challenge. This is where the concept of <strong>Slowly Changing Dimensions (SCD)</strong> comes in. SCD refers to different techniques for tracking and handling changes in dimensional data (like customer profiles, product details, or employee records) over time.</p><p>In this blog post, we’ll walk through the three basic types of SCD — <strong>Type 0</strong>, <strong>Type 1</strong>, and <strong>Type 2</strong> — and explain how each one works. We’ll also show <strong>real-world examples using dbt (data build tool)</strong> to demonstrate how you can implement each strategy in practice.</p><p>By the end, you’ll have a clear understanding of when to use each type, and how to start tracking changes in your own data projects.</p><h3>What is dbt?</h3><p><strong>dbt (data build tool)</strong> is an open-source tool that helps data teams transform raw data into clean, reliable datasets — all using simple SQL.<br>Instead of manually writing transformation pipelines, dbt allows you to build modular models, test your data quality, and document your transformations, all within a version-controlled environment.</p><p>Recently, my cousin — he’s in IT, but on the academic side — asked me:<br> <em>“Okay, cool that you have tools like dbt in the data world… but why not just use SQL scripts in Git and run them on a cron job?”</em></p><p>Long story short: <strong>because dbt makes things simpler, more powerful, and way easier to manage — especially once your project gets even a little complex.</strong><br>And let’s be honest: it <em>usually</em> gets complex pretty quickly when you have lots of data, sources, pipelines, and stakeholders in the mix.</p><h4>Imagine you have a 3-step data transformation pipeline:</h4><ol><li><strong>stage_customers.sql</strong> – Raw customer data staging</li><li><strong>transform_orders.sql</strong> – Join orders with staged customer data</li><li><strong>customer_ltv.sql</strong> – Calculate lifetime value (LTV) per customer</li></ol><p>With plain SQL scripts:</p><ul><li>You have to <strong>manually run them in the right order</strong>, or write a brittle shell script:</li></ul><pre>psql -f stage_customers.sql<br>psql -f transform_orders.sql<br>psql -f customer_ltv.sql</pre><ul><li>If stage_customers.sql fails, the rest may still run unless you add error handling.</li><li>If someone modifies stage_customers.sql, there’s <strong>no easy way to know what downstream files depend on it</strong>.</li><li>There’s no built-in testing — so LTV logic could break and you wouldn’t know.</li><li>No clear visibility into what depends on what.</li><li>No environment separation: you’d need to <strong>manually swap connection strings</strong> or hardcode database names in the SQL.</li></ul><h3>dbt — the solution</h3><p>In dbt, you’d define three models:</p><ul><li>models/source/source_customers.sql <em>(prepares and cleans raw customer data from a source table)</em></li></ul><pre>SELECT<br>    id AS customer_id,<br>    first_name,<br>    last_name,<br>    email,<br>    created_at,<br>    updated_at<br>FROM {{ source(&#39;raw&#39;, &#39;customers&#39;) }}<br>WHERE email IS NOT NULL</pre><ul><li>models/transform/transform_orders.sql <em>(joins order data with staged customers)</em></li></ul><pre>SELECT o.*, c.name<br>FROM {{ ref(&#39;source_customers&#39;) }} c<br>JOIN raw.orders o ON o.customer_id = c.id</pre><ul><li>models/metrics/customer_ltv.sql <em>(aggregates order totals to calculate customer lifetime value)</em></li></ul><pre>SELECT customer_id, SUM(order_total) as ltv<br>FROM {{ ref(&#39;transform_orders&#39;) }}<br>GROUP BY customer_id</pre><p>Then you simply run:</p><pre>dbt run</pre><p>dbt will:</p><ul><li>Build a <strong>DAG</strong> to figure out the right order.</li><li>Stop the pipeline gracefully on failure.</li><li>Track dependencies (you can <strong>visualize</strong> them!).</li><li>Allow you to add <strong>tests</strong> like:</li></ul><pre>- name: customer_ltv<br>  columns:<br>    - name: ltv<br>      tests:<br>        - not_null<br>        - accepted_values:<br>            values: [&quot;&gt;=0&quot;]</pre><p>dbt simplifies data workflows by turning raw SQL scripts into modular, dependency-aware models that run in the correct order automatically using ref(). It supports separate dev/prod environments, built-in testing, and auto-generated documentation — all without custom scripting. This makes pipelines easier to manage, scale, and debug compared to plain SQL.</p><p><strong>Basically, dbt adds structure, automation, and maintainability</strong> to SQL-based analytics workflows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TN5m5O3sLEXgGbDeEOFb2w.png" /><figcaption>Courtesy of ChatGPT</figcaption></figure><p>In this post, we’ll use dbt to demonstrate how Slowly Changing Dimensions (SCD) can be implemented easily and consistently inside a modern data stack.</p><h3>First, lets setup our environment!</h3><p>Here is <a href="https://github.com/ijaniszewski/dbt-slowly-changing-dimensions/tree/main">the full code</a> on Github.</p><p><strong>Prerequisites</strong></p><ul><li>Docker + Docker Compose installed</li><li>Basic familiarity with dbt and SQL (helpful but not required)</li></ul><h4>⚠️ Security Note</h4><blockquote>This project uses hardcoded credentials (e.g., demo_user, demo_pass) for illustrative purposes only. In real environments, <strong>avoid passing passwords directly via CLI</strong> and use secrets management instead.</blockquote><h4>Step-by-Step: Starting dbt environment</h4><p>Before we dive into Slowly Changing Dimensions, let’s get our project up and running. We’ll start by launching the environment and loading some initial data into our MySQL database.</p><p>1️⃣ <strong>Start the dbt + MySQL Environment</strong></p><p>We use Docker Compose to spin up two containers:</p><ul><li>A <strong>MySQL</strong> database preconfigured with a demo schema and some seed data</li><li>A <strong>dbt</strong> container where we’ll run our transformation models</li></ul><pre>docker compose up</pre><p>It will:</p><ul><li>Initialize the MySQL server with demo_db and a table called customers.</li><li>load an initial CSV (customers_initial.csv) into the customers table using a SQL script (01_load_data.sql).</li><li>Prepares the environment to be accessed by dbt.</li></ul><p>2️⃣ <strong>Test the dbt connection</strong></p><p>Let’s make sure dbt can connect to the database:</p><pre>docker exec -it dbt_scd dbt debug</pre><p>You should see output confirming that the connection is successful.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gPN8m5z0rOYbl-EUcLG-Xw.png" /></figure><p>The git [ERROR] in the dbt debug output means that git is not installed in our dbt container — but <strong>don’t worry</strong>, this does <strong>not block dbt from working</strong> unless you’re using certain dbt features that rely on Git (like dbt deps with Git-based packages). If you have similar output: <strong>your dbt connection is fine. </strong>So dbt is able to connect to MySQL database — you’re good to go for running models like dbt build.</p><p><strong>3️⃣ Check what data do we have</strong></p><pre>docker exec -it mysql_scd mysql -u demo_user -pdemo_pass demo_db<br><br>select * from customers;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NJmbbkEeTx-53WGInvWmWg.png" /></figure><p>Looks like initial load to MySQL worked.</p><p>4️⃣ F<strong>irst dbt build: load initial data into SCD tables</strong></p><p>Now, let’s run our dbt models for the first time:</p><pre>docker exec -it dbt_scd dbt build</pre><p>Here’s what happens during this build:</p><ul><li>dbt reads from the customers source table (loaded during setup).</li><li>It creates and populates three models:<br>customers_scd0 – Type 0 (no updates)<br>customers_scd1 – Type 1 (overwrite changes)<br>customers_scd2 – Type 2 (track historical changes)</li></ul><p><strong>4️⃣ Let’s verify the initial output</strong></p><pre>docker exec -it mysql_scd mysql -u demo_user -pdemo_pass demo_db<br><br>show tables;</pre><p>All three tables will initially contain the <strong>same data</strong> because we haven’t introduced any changes yet:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Tq0ClNXd34Ewc0a0MrQDfQ.png" /></figure><p>As you probably see, the <strong>structure</strong> of the SCD2 table includes <strong>extra metadata columns</strong>. Will explain it in a minute.</p><p><strong>5️⃣ Do the dbt magic!</strong></p><p>Now it’s time to simulate a real-world scenario: customer data has changed — maybe addresses were updated or names were corrected.</p><p>Let’s load these <strong>updated customers</strong> into the customers table in MySQL:</p><pre>docker exec -i mysql_scd mysql -u root -proot demo_db &lt; ./scripts/02_load_customers_updated.sql</pre><p>Then rerun dbt to apply transformations to our SCD models:</p><pre>docker exec -it dbt_scd dbt build</pre><p>This is where each SCD type starts behaving differently — even though they all read from the same updated source table, each one handles changes in its own way.</p><blockquote>We’ll now walk through what happens in each model — starting with <strong>SCD type 0</strong>.</blockquote><h4>SCD type 0: fixed attributes (no changes allowed)</h4><p><strong>Concept</strong><br>SCD type 0 retains original data and never updates existing records — even if the source data changes. This is typically used for attributes that should remain historically accurate and never change (e.g., birth date, customer signup date).</p><p><strong>dbt implementation:</strong></p><pre>{{ config(<br>    materialized=&#39;incremental&#39;,<br>    unique_key=&#39;customer_id&#39;<br>) }}</pre><p>materialized=&#39;incremental&#39;<br>this tells dbt: <em>don’t rebuild the whole table every time — only add or update records. </em>It’s more efficient and aligns perfectly with slowly changing dimensions, where we often want to <strong>append or update</strong> only certain rows.</p><p>unique_key=&#39;customer_id&#39;<br>dbt uses this key to determine uniqueness in the target table.</p><pre>select<br>  customer_id,<br>  first_name,<br>  last_name,<br>  email,<br>  address,<br>  updated_at<br>from {{ ref(&#39;stg_customers&#39;) }}</pre><p>This is the main SELECT — pulling data from the <strong>staging model</strong>, which reads from the raw customers table.<br>At this point, it&#39;s just a clean version of the latest data.</p><pre>{% if is_incremental() %}<br>  where customer_id not in (select customer_id from {{ this }})<br>{% endif %}</pre><p>This block only runs <strong>when dbt is doing an incremental load</strong> (i.e., not the first run). It says: <em>“Only insert customers that </em><strong><em>don’t already exist</em></strong><em> in the target table.” <br></em>This behavior makes sense for <strong>SCD Type 0</strong>:</p><ul><li>We never want to update existing records.</li><li>Only <strong>new customer IDs</strong> are inserted.</li></ul><p><strong>OK, so the whole model does:</strong></p><ul><li>On the initial run, all rows are loaded.</li><li>On subsequent runs, it <strong>only inserts new customers</strong> (not found in the existing table).</li><li>Existing records are <strong>never updated</strong>, even if data in the source has changed.</li></ul><p><strong>Check the results!</strong></p><pre>docker exec -it mysql_scd mysql -u demo_user -pdemo_pass demo_db <br><br>select * from customers_scd0;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*riXHo4_NYFGFypgtPmP_Aw.png" /></figure><ul><li>New customers from the update file are added.</li><li>Existing customer data remains unchanged, even if the email, address, or updated_at has changed in the source.</li></ul><p><strong>Why use SCD0?</strong></p><ul><li>When you want to <strong>preserve the original data</strong> as-is.</li><li>Use cases: legal records, contracts, audit trails.</li></ul><h4>SCD type 1: overwrite attributes (no history)</h4><p><strong>Concept<br></strong>SCD Type 1 <strong>overwrites existing records</strong> when changes are detected. It keeps only the most up-to-date version of each record, and <strong>does not preserve history</strong>. This is useful when changes are corrections or when historical tracking isn’t necessary — like fixing typos or updating email addresses.</p><p><strong>dbt implementation:</strong></p><pre>{{ config(<br>    materialized=&#39;table&#39;<br>) }}<br><br>with ranked_customers as (<br>    select<br>        customer_id,<br>        first_name,<br>        last_name,<br>        email,<br>        address,<br>        updated_at,<br>        row_number() over (partition by customer_id order by updated_at desc) as rn<br>    from {{ ref(&#39;stg_customers&#39;) }}<br>)<br><br>select<br>    customer_id,<br>    first_name,<br>    last_name,<br>    email,<br>    address,<br>    updated_at<br>from ranked_customers<br>where rn = 1</pre><p>materialized=&#39;table&#39;<br>We’re using a full table materialization (not incremental) to <strong>refresh the table entirely</strong> each time we run dbt. This makes sense for SCD1, where we always want the most recent version of each record — even if it means rewriting the table.</p><p>row_number() — window function. This part is key:</p><pre>row_number() over (partition by customer_id order by updated_at desc) as rn</pre><ul><li>For each customer_id, we sort all rows by updated_at in descending order.</li><li>The most recent version gets rn = 1.</li></ul><p>Then we just select the rows <strong>where </strong><strong>rn = 1</strong>, effectively filtering out any older duplicates.</p><p><strong>So What Does This Model Do?</strong></p><p><strong>Initial run</strong>:</p><ul><li>Loads all customer data.</li></ul><p><strong>Subsequent runs</strong>:</p><ul><li>Inserts new customers.</li><li><strong>Overwrites</strong> any customer whose ID already exists (with updated values).</li></ul><p>This is exactly what you want when the <strong>latest state is all that matters</strong>.</p><p><strong>Check the results!</strong></p><pre>docker exec -it mysql_scd mysql -u demo_user -pdemo_pass demo_db <br><br>SELECT * FROM customers_scd1;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hRNDnjpMHjoLbvxGS2-Aqw.png" /></figure><ul><li>New customers are added.</li><li>Existing customers are updated with the latest values from the source — changes in email, address, or updated_at are reflected.</li><li><strong>No historical version</strong> of the previous data is kept.</li></ul><p><strong>Why use SCD1?</strong></p><p>When you don’t care about history and just want the <strong>most accurate, current data</strong>.</p><p><strong>Use cases</strong>:</p><ul><li>Customer profiles where only the latest info matters</li><li>Fixing data quality issues (e.g., typos, invalid emails)</li><li>Operational dashboards</li></ul><p><em>aaaand here comes the final and most powerful one: </em><strong><em>SCD Type 2</em></strong><em>.</em></p><h4>SCD type 2: track history over time</h4><p><strong>Concept<br></strong>SCD Type 2 stores <strong>every historical version</strong> of a record. When something about a customer changes (like their address), we <strong>don’t overwrite</strong> the old row — instead, we mark it as historical and <strong>insert a new version</strong> with the updated info.</p><p>This gives you a <strong>complete history</strong> of changes over time.</p><p><strong>dbt implementation: Using Snapshots</strong></p><pre>{% snapshot customers_scd2 %}<br><br>{{<br>  config(<br>    target_schema=&#39;demo_db&#39;,<br>    unique_key=&#39;customer_id&#39;,<br>    strategy=&#39;timestamp&#39;,<br>    updated_at=&#39;updated_at&#39;<br>  )<br>}}<br><br>select<br>  customer_id,<br>  first_name,<br>  last_name,<br>  email,<br>  address,<br>  updated_at<br>from {{ ref(&#39;stg_customers&#39;) }}<br><br>{% endsnapshot %}</pre><p>target_schema=&#39;demo_db&#39;<br>Specifies where the snapshot will be stored — in the same schema as our other models.</p><p>unique_key=&#39;customer_id&#39;<br>Used to identify whether a record is the “same” customer across runs.</p><p>strategy=&#39;timestamp&#39;<br>Tells dbt to track changes based on a timestamp column.</p><p>updated_at=&#39;updated_at&#39;<br>The field that dbt uses to know if a record has changed since the last snapshot run.</p><p><strong>How It Works?</strong></p><p>On the <strong>first run:</strong></p><ul><li>all rows are inserted into the snapshot table.</li></ul><p>On later runs:</p><ul><li>dbt compares the current version of each row (based on customer_id) with the previously stored version.</li><li>If the updated_at timestamp is newer (and the data has changed), it:</li><li>Marks the old row as <strong>dbt_valid_to = current time</strong></li><li>Inserts the new version with <strong>dbt_valid_from = current time</strong></li><li>Leaves dbt_valid_to NULL (because it’s the current version)</li></ul><p>This gives you built-in time range tracking and history.</p><p><strong>Check the results!</strong></p><pre>docker exec -it mysql_scd mysql -u demo_user -pdemo_pass demo_db <br><br>SELECT * FROM customers_scd2;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tNGCou8sBwGEEQ2WJaCbWA.png" /></figure><p><strong>What This Tells Us</strong></p><p>1️⃣ <strong>Customer 1 (John Doe)</strong> has <strong>two rows</strong>:</p><ul><li>The first row shows the <strong>original version</strong>, with dbt_valid_to = 2025-01-01</li><li>The second row is the <strong>new version</strong>, with updated email and address, and dbt_valid_to = NULL (it’s current)</li></ul><p>2️⃣ <strong>Customer 2</strong> hasn’t changed — only one row, and it’s still current.</p><p>3️⃣ <strong>Customer 3</strong> is <strong>new</strong> — just one row, added in the second load.</p><p><strong>This Confirms That:</strong></p><ul><li>dbt detected the change for Customer 1 and <strong>created a historical version</strong>.</li><li>The old row was marked with an end date (dbt_valid_to) and a new one was inserted with updated values.</li><li>This is classic <strong>SCD Type 2 behavior</strong> — done automatically by dbt using the snapshot logic.</li></ul><p><strong>Bonus Columns</strong></p><p>You might also see:</p><ul><li>dbt_scd_id: a hash used by dbt to identify changes in column values.</li><li>dbt_updated_at: when dbt detected this change in the source data.</li></ul><p>These are managed internally by dbt to detect and record changes reliably.</p><p>If you want to see only the <strong>current version</strong> of each customer, then you need just a simple SQL query (i.e., where dbt_valid_to IS NULL).</p><p><strong>Why Use Snapshots for SCD2?</strong></p><ul><li>Cleaner and simpler than writing complex SQL yourself</li><li>Handles valid_from / valid_to logic for you</li><li>Ideal when your data already includes a reliable updated_at field</li></ul><p><strong>Use cases</strong>:</p><ul><li>Full audit history</li><li>Legal/compliance reporting</li><li>Any scenario where “what did we know and when?” matters</li></ul><h4>BONUS 1: SCD2 as dbt model (not to use snapshot)</h4><p>You can implement SCD Type 2 directly in a dbt <strong>incremental model</strong> without using snapshots.</p><p>This approach:</p><ul><li>Tracks changes manually by comparing incoming records with the current ones</li><li>Marks old rows as is_current = false and inserts new ones with valid_from = now()</li><li>Gives you full control over logic and customization</li></ul><p>Use it when you want historical tracking <strong>without relying on snapshots</strong>.</p><pre>{{ config(<br>    materialized=&#39;incremental&#39;,<br>    unique_key=&#39;customer_id&#39;<br>) }}<br><br>with incoming as (<br>    select<br>        customer_id,<br>        first_name,<br>        last_name,<br>        email,<br>        address,<br>        updated_at<br>    from {{ ref(&#39;stg_customers&#39;) }}<br>),<br><br>latest as (<br>    select *<br>    from {{ this }}<br>    where is_current = true<br>),<br><br>changes as (<br>    select<br>        i.customer_id,<br>        i.first_name,<br>        i.last_name,<br>        i.email,<br>        i.address,<br>        i.updated_at,<br>        current_timestamp as valid_from,<br>        null as valid_to,<br>        true as is_current<br>    from incoming i<br>    left join latest l<br>      on i.customer_id = l.customer_id<br>    where l.customer_id is null  -- new customer<br>       or i.email != l.email<br>       or i.address != l.address<br>       or i.first_name != l.first_name<br>       or i.last_name != l.last_name<br>)<br><br>select * from changes<br><br>{% if is_incremental() %}<br>union all<br><br>select<br>    customer_id,<br>    first_name,<br>    last_name,<br>    email,<br>    address,<br>    updated_at,<br>    valid_from,<br>    current_timestamp as valid_to,<br>    false as is_current<br>from {{ this }}<br>where is_current = true<br>and customer_id in (select customer_id from changes)<br>{% endif %}</pre><p>Check if that works and let me know, I prefer solutions out of the box! :)</p><h4>BONUS 2: deletions</h4><p>What happens when a record disappears from the source table?</p><p>Let’s simulate that by deleting a customer:</p><pre>docker exec -i mysql_scd mysql -u root -proot demo_db &lt; ./scripts/03_delete_customer.sql</pre><p>This runs:</p><pre>DELETE FROM customers WHERE customer_id = 1;</pre><p>Then we re-run dbt to see how each SCD type reacts.</p><pre>docker exec -it dbt_scd dbt build</pre><p>So, what do you think? Which of our SCD approaches actually handles deletions?</p><p><strong>SCD Type 0 (Fixed Attributes)</strong></p><ul><li>Nothing changes — the deleted record remains in customers_scd0.</li><li>This is expected: SCD0 only inserts new records and ignores all updates or deletions.</li></ul><p><strong>SCD Type 1 (Overwrite)</strong></p><ul><li>The deleted record is <strong>still present</strong>.</li><li>dbt has no logic to remove it — it always reflects the <strong>latest known version</strong>, not deletions.</li></ul><p><strong>SCD Type 2 (History Tracking)</strong></p><ul><li>The deleted record also remains — <strong>marked as current</strong> unless you implement explicit deletion handling.</li><li>dbt snapshots and most custom SCD2 models don’t detect deletions by default — you have to add logic for it.</li></ul><p>If your project requires handling deletions, then you need some extra work. To truly capture deletions in SCD1 or SCD2, you’d need to:</p><ul><li>Compare existing records in your target table ({{ this }}) with the current source (stg_customers)</li><li>Identify missing customer_id ‘s</li><li>Mark them as “soft deleted” or is_current = false, or store a deleted_at timestamp</li></ul><p>This can be done in your model logic — but by default, <strong>dbt won’t remove or flag deleted records</strong>.</p><pre>{{ config(<br>    materialized=&#39;table&#39;<br>) }}<br><br>with latest_source as (<br>    select<br>        customer_id,<br>        first_name,<br>        last_name,<br>        email,<br>        address,<br>        updated_at<br>    from {{ ref(&#39;stg_customers&#39;) }}<br>),<br><br>-- Detect &quot;soft deletions&quot;: records that exist in the target but not in the source<br>-- Only run this part if the table already exists<br>deleted_customers as (<br>    {% if is_incremental() %}<br>    select<br>        t.customer_id,<br>        t.first_name,<br>        t.last_name,<br>        t.email,<br>        t.address,<br>        t.updated_at<br>    from {{ this }} t<br>    left join latest_source s<br>        on t.customer_id = s.customer_id<br>    where s.customer_id is null<br>    {% else %}<br>    select null as customer_id, null as first_name, null as last_name,<br>           null as email, null as address, null as updated_at<br>    where false<br>    {% endif %}<br>),<br><br>-- Keep the latest from source<br>ranked_customers as (<br>    select<br>        customer_id,<br>        first_name,<br>        last_name,<br>        email,<br>        address,<br>        updated_at,<br>        row_number() over (partition by customer_id order by updated_at desc) as rn<br>    from latest_source<br>)<br><br>select<br>    customer_id,<br>    first_name,<br>    last_name,<br>    email,<br>    address,<br>    updated_at<br>from ranked_customers<br>where rn = 1<br><br>union all<br><br>-- Add soft-deleted customers<br>select * from deleted_customers</pre><h4>Summary &amp; Key Takeaways</h4><p>We explored how to handle Slowly Changing Dimensions (SCD) using <strong>dbt</strong> and <strong>MySQL</strong>, covering three common types — each with its own approach to managing data changes over time.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SY9AVpdATKU666zCuaF4cw.png" /><figcaption>Courtesy of ChatGPT</figcaption></figure><p><strong>Key Takeaways</strong></p><ul><li><strong>SCD0</strong> is ideal when original values must never change (e.g., legal records).</li><li><strong>SCD1</strong> gives you a clean, current view — best for operational reporting.</li><li><strong>SCD2</strong> provides a full change history — essential for audit, analytics, and trend tracking.</li><li><strong>Snapshots</strong> in dbt make SCD2 easy, but you can also build it manually using incremental models.</li><li><strong>Deletions</strong> require extra logic — dbt doesn’t detect them out of the box, but you can extend models (like we did with SCD1) to keep track.</li></ul><p><strong>Try It Yourself!</strong></p><p>All examples, data, and dbt project files are on <a href="https://github.com/ijaniszewski/dbt-slowly-changing-dimensions/tree/main">GitHub</a> — just run:</p><pre>docker compose up</pre><p>And step through the project’s README to see SCD logic in action!</p><p>Want to go deeper into soft deletes, row hashing, or dbt testing strategies next? Let me know — I’d love to expand this series!</p><p>Happy dbt-ing! 🚀</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=98b37f3ddf03" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Your first data engineering pipeline (Airflow + dbt)! Spotify Release Radar songs]]></title>
            <link>https://ijaniszewski.medium.com/your-first-data-engineering-pipeline-airflow-dbt-spotify-release-radar-songs-65fd36cd1670?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/65fd36cd1670</guid>
            <category><![CDATA[airflow]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[dbt]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Tue, 22 Apr 2025 07:05:54 GMT</pubDate>
            <atom:updated>2025-04-22T07:05:54.101Z</atom:updated>
            <content:encoded><![CDATA[<h3>Abstract:</h3><p>In this hands-on guide, we walk through building your very <strong>first data engineering pipeline</strong> using Apache Airflow and dbt — powered by data from Spotify’s Release Radar playlist. Originally inspired by a desire to preserve Discover Weekly songs. Now, based on Release Radar, this tutorial takes you through the complete process: from authenticating with Spotify’s API and extracting playlist data, to loading it into a datalake, transforming it with dbt, and orchestrating the whole flow with Airflow. Whether you’re just getting started with data engineering or looking for a practical, music-inspired project, this article offers a fun and comprehensive introduction to ELT pipelines, modern orchestration, and transformation frameworks.</p><p>Here is <a href="https://github.com/ijaniszewski/airflow-dbt-spotify-release-radar">the full code</a> on Github.</p><h4>This whole project started because I kept losing great songs from Discover Weekly.</h4><p>At first, I wanted to do it with the <strong>Discover Weekly</strong> playlist. My idea was to collect all the recommended songs and store them in my table, so I’d always have the opportunity to revisit them. Since the playlist updates every Monday, I often found that I missed some songs—either because I forgot to add them to my favourites or because I wasn’t connected to Spotify all week, missing the chance to listen. That’s why I wanted to save all the suggested songs and have the option to return to them whenever I wanted.</p><p>Unfortunately, for some unknown reason on November 27th, 2024, Spotify changed the API. As a good IT practice, you can add new features, but you shouldn’t remove them without even “advance notice”. Here’s Spotify’s <a href="https://developer.spotify.com/blog/2024-11-27-changes-to-the-web-api">post about the change</a> or <a href="https://community.spotify.com/t5/Spotify-for-Developers/Discover-Weekly-playlist-appears-as-null-when-using-Get-User/td-p/6551114">suprised user question</a>.</p><p>The thing is, you (the user) are no longer the owner of your Discover Weekly playlist, meaning you don’t have access to it through the API. Hopefully, Spotify will bring back the Discover Weekly functionality, as there’s a lively <a href="https://community.spotify.com/t5/Spotify-for-Developers/Changes-to-Web-API/td-p/6540414">discussion about this change</a>.</p><p>In a meantime, I needed to switch to Release Radar — even if it’s not as cool or helpful. But hey, at least we can practice our data engineering skills! According to Spotify, this playlist “catches all the latest music from the artists you follow, plus singles picked for you. Updates every Friday.”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*krt5rptNuEVd29RzSpt-lw.png" /></figure><h4>Let’s try it:)</h4><p>First, you will need Spotify Developer account and <a href="https://developer.spotify.com/dashboard">an app</a>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5sJ5TLnZR9ua6hhM14SjGw.png" /></figure><p>and Create app (important to add <a href="https://localhost:7777/callback"><em>https://localhost:7777/callback</em></a> in Redirect URIs, we will use it later):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7ch90qBockW7bZbSyOClUg.png" /></figure><p>Click save and you’ll have Client ID and a secret. Create <em>.env</em> file (as shown in .env.example):</p><pre>cp .env.example .env</pre><pre>SPOTIPY_CLIENT_ID=<br>SPOTIPY_CLIENT_SECRET=<br>SPOTIPY_REDIRECT_URI=https://127.0.0.1:7777/callback</pre><p>and update variables. Spotipy redirect URI should be the same as you added in your’s app spec.</p><p>Then, we need to get Release Radar ID. For this, you can go to Spotify Web App, find Release Radar (or any other playlist):</p><p>https://open.spotify.com/playlist/<strong>&lt;playlist_id&gt;</strong></p><p>or you can do by Python code, but need to install <a href="https://spotipy.readthedocs.io/en/2.25.1/">Spotipy</a> (<em>a lightweight Python library for the Spotify Web API</em>): and <a href="https://pypi.org/project/python-dotenv/">python-dotenv</a> (<em>to handle envs from .env file)</em></p><pre>pip install spotipy python-dotenv</pre><pre>from dotenv import load_dotenv<br><br>from spotipy import Spotify<br>from spotipy.oauth2 import SpotifyOAuth<br><br>load_dotenv()<br><br># Define scope to access user&#39;s playlists<br>scope = &quot;user-library-read&quot;<br><br># Auth manager handles login<br>sp = Spotify(auth_manager=SpotifyOAuth(scope=scope))<br><br># Fetch and search for Release Radar playlist<br>playlists = sp.current_user_playlists()<br>for playlist in playlists[&quot;items&quot;]:<br>    if playlist[&quot;name&quot;] == &quot;Release Radar&quot;:<br>        print(f&quot;Found Release Radar! ID: {playlist[&#39;id&#39;]}&quot;)<br>        break<br>else:<br>    print(&quot;Release Radar not found.&quot;)</pre><p>Spotify’s OAuth flow is designed to require user interaction, meaning a web browser must be involved in at least one part of the process. By default, the Spotipy library uses its OAuth manager to launch the web browser automatically so you can sign in and grant the necessary permissions.</p><p>New tab in your browser will be open (on localhost:7777/callback as we set it up), you need to copy the link and paste it in your shell:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1022/1*Rk9sox8_BH2KrMJYVH5Ycw.png" /></figure><p>Once you confirm permissions for your app (and <em>.cache</em> file with credentials will be saved), the ID of your playlist will be printed out:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/790/1*UjAedzP1z51Fb5lk-TveSQ.png" /></figure><p>Add the playlist ID to the .env file:</p><pre>SPOTIPY_PLAYLIST_ID=6ChN52JDDy7LzCXSrvlZor</pre><h4>OK! Let’s get down to business!</h4><p>You need to have <a href="https://docs.docker.com/compose/install/">docker-compose installed</a> and the <a href="https://docs.docker.com/engine/daemon/start/">Daemon (Docker Engine) running</a>.</p><p>Verify your Docker Compose version with:</p><pre>docker-compose version</pre><p>and can check if the Docker daemon is running:</p><pre>docker info</pre><p>If you encounter:</p><pre>Server:<br>ERROR: error during connect: this error may indicate that the docker daemon is not running:</pre><p>means Daemon is not running (you can for example manually run Docker Desktop ).</p><p>Once you have docker-compose, we can</p><ol><li>initialize Airflow environment by setting up the metadata database, creating user and essential configurations. This step is required only once to bootstrap the system</li></ol><pre>docker compose up airflow-init</pre><p>2. and start Airflow services (each service runs in its own container):</p><ul><li><strong>PostgreSQL</strong><br>a database to store metadata like DAG definitions, task instance states, scheduling information, logs, XComs (inter-task communication) or user data</li><li><strong>Redis</strong><br>needed only with the CeleryExecutor — which is designed for distributed task execution. Airflow runs tasks by distributing them across multiple worker processes. This requires a message broker to queue and send tasks to workers. Airflow scheduler sends tasks to Redis and worker picks them up from there and runs</li><li><strong>webserver<br></strong>provides the graphical user interface (GUI) that allows to visualize and manage DAGs, monitor task statuses or view logs</li><li><strong>scheduler<br></strong>continuously monitors DAGs and decides which tasks need to be executed at any given time. It schedules tasks by sending them to workers based on dependencies and timing settings</li><li><strong>worker<br></strong>executes the actual tasks dispatched by the scheduler. In a distributed setup, multiple workers can run tasks concurrently to improve processing speed</li><li><strong>airflow-triggerer<br></strong>handles asynchronous triggering of tasks that rely on external events. It monitors these events and wakes up tasks waiting on such triggers, thus optimizing resource usage</li></ul><pre>docker compose up</pre><h4>Ok, while compose is spinning up, let’s dive into some tech details.</h4><p>In docker-compose you can choose between using the official image directly or building a custom image for greater control:</p><ul><li><strong>Using the Official Image:</strong><br> you define the x-airflow-common anchor like this:</li></ul><pre>x-airflow-common:<br>  &amp;airflow-common<br>  image: apache/airflow:2.9.1-python3.10</pre><p>This configuration is shared by all Airflow-based services in your docker-compose file.</p><ul><li><strong>Using a Custom Build:</strong><br>for more control — such as adding extra dependencies like Spotipy — you can use the build option:</li></ul><pre>x-airflow-common:<br>  &amp;airflow-common<br>  build: .</pre><p>and point out to a dedicated Docker file:</p><pre>FROM apache/airflow:2.10.5<br>ADD requirements.txt .<br>RUN pip install apache-airflow==${AIRFLOW_VERSION} -r requirements.txt</pre><p>this lets you build a tailored image that all your Airflow services (webserver, scheduler, worker, triggerer, airflow-init) will use.</p><p>Also, we want to mount datalake directory, which will be used to save our raw data, before transformation will be done:</p><pre>  volumes:<br>    - ./dags:/opt/airflow/dags<br>    - ./logs:/opt/airflow/logs<br>    - ./plugins:/opt/airflow/plugins<br>    - ./datalake:/opt/airflow/datalake</pre><p>it means anything you save to /opt/airflow/datalake from the Airflow services will also be accessible from your local machine (same with dags, logs and plugns). We will use it to exchange files between DAGs (our mini datalake).</p><p>With Airflow running via Docker Compose, you can easily access the web interface by navigating to <a href="http://localhost:8080/">http://localhost:8080/</a> in your browser. In our Docker Compose setup, the airflow-webserver service includes a port mapping:</p><pre>airflow-webserver:<br>  ports:<br>    - &quot;8080:8080&quot;</pre><p>This configuration forwards port 8080 from the container to port 8080 on your host machine, allowing you to interact with the Airflow UI and manage your workflows conveniently. There, you can login with unbreakable credentials: admin admin (only for test purposes!) and see our two Spotify DAGs:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qkqvncbbFCHqIwVGS9LfZg.png" /></figure><p>First, there’s spotify_weekly_ingest, which <strong>E</strong>xtracts raw data from the Spotify API and <strong>L</strong>oads it into our “<em>datalake” </em>(as it’s just a mounted directory, but to understand the concept). Then there&#39;s spotify_transform, which processes (<strong>T</strong>ransforms) the data using dbt (data-build tool). It’s not a typical <strong>ETL</strong> pipeline—we load the data <em>as-is</em> first, exactly like it comes from the source. Then do the transformations. It’s more like a datalake setup: get everything into one place, keep the structure identical to the source, and transform it later. That way, we’ve got more flexibility if we ever need to go back to the original format. It’s called <strong>ELT data pipeline</strong>.</p><h4>Let’s break down the DAGs</h4><p>First dag, to ingest data, is scheduled weekly (as Release Radar playlist is refreshed every week) , hence the &lt;source&gt;_&lt;frequency&gt;_&lt;role&gt; name. It fetches data from API and stores it on datalake.</p><p>Key concepts:</p><ul><li>@dag, @task decorators: mark a Python function as an Airflow DAG or Task</li><li><a href="https://airflow.apache.org/docs/apache-airflow/stable/authoring-and-scheduling/datasets.html"><strong>dataset</strong></a>: other DAGs can be triggered once a task marked with a dataset completes successfully. Great for creating dependent workflows without strict time schedules.</li></ul><pre>    # Dataset is used to trigger the downstream DAG (transform)<br>    @task(outlets=[Dataset(&quot;spotify&quot;)])</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/868/1*vS1xYd47OlyU-w1ePbNDWw.png" /></figure><p>In other words, when spotify_weekly_ingest finishes, it triggers spotify_transform to take over — no need for cron scheduling or manual orchestration. Pretty cool thing!</p><p>Transform DAG basically takes raw data from datalake and <em>transforms</em> it. It gets JSON file, loads it to mySQL database and then run dbt .</p><ul><li><em>load_json_file<br></em>Looks for the most recent Spotify JSON file in the datalake directory, then loads and parses the file contents</li><li><em>transform_data<br></em>Extracts the timestamp from the filename for tracking and transforms the raw JSON structure into a clean list of songs, capturing the song name, artist, and URI</li><li><em>log_songs<br></em>Prints each song and artist to the logs — useful for quick inspection/debugging</li><li><em>store_in_mysql<br></em>Inserts the transformed song data into a MySQL table: source_spotify_tracks</li><li><em>run_dbt_transform <br></em>Executes a dbt build command to apply additional SQL-based transformations and model building.</li></ul><h4>Let’s take a moment to focus on the dbt (data-build tool)</h4><p>dbt is a modern data transformation framework for analytics engineering. It lets you <strong>transform raw data into clean, tested, and documented datasets</strong> using SQL, and it integrates directly into version control systems (like Git) and workflows (like Airflow).</p><p>dbt models are SQL files that get compiled into raw SQL queries, and executed on your target data warehouse or database — in our case, <strong>MySQL</strong>.</p><p><em>profiles.yml</em><br>Defines how dbt connects to your database.</p><pre>spotify_dbt:<br>  target: dev<br>  outputs:<br>    dev:<br>      type: mysql<br>      server: mysql<br>      user: airflow<br>      schema: spotify<br>      password: airflow<br>      port: 3306</pre><p>Uses the spotify schema. It’s required to run any dbt command.</p><p><em>dbt_project.yml</em><br>Configures your dbt project structure and settings.</p><pre>name: &#39;spotify_dbt&#39;<br>version: &#39;1.0&#39;<br>profile: &#39;spotify_dbt&#39;<br><br>config-version: 2<br><br>model-paths: [&quot;models&quot;]<br><br>models:<br>  spotify_dbt:<br>    materialized: table<br>    +pre-hook:<br>      - &quot;USE {{ target.schema }}&quot;<br></pre><p>Points to models folder and materializes models as tables.</p><p>Together, these files tell dbt:</p><ul><li>Where to connect</li><li>What to build</li><li>How to behave</li></ul><h4>Medallion Architecture (<strong>Bronze-Silver-Gold Convention)</strong></h4><p>This layered naming convention is borrowed from <strong>data lake best practices</strong> and helps structure your transformations by stage of maturity:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Gn_SZD0FvhNJN4VREIdGxQ.png" /><figcaption><a href="https://www.databricks.com/glossary/medallion-architecture">https://www.databricks.com/glossary/medallion-architecture</a></figcaption></figure><p>This structure makes pipelines easier to debug, extend, and maintain — and ensures separation between ingestion, transformation, and presentation logic.</p><p>Our <strong>bronze layer</strong> lceans up and standardizes raw ingestion data. Dedulicates songs and generates song_id, adds time-based features (week, year)</p><pre>{{ config(materialized=&#39;table&#39;) }}<br><br>SELECT<br>    -- {{ dbt_utils.generate_surrogate_key([&#39;artist&#39;, &#39;name&#39;]) }} AS song_id,<br>    MIN(id) AS raw_id,<br>    artist,<br>    name,<br>    MIN(_load_ts) AS _load_ts,<br>    WEEK(MIN(_load_ts), 3) AS week_number,  -- ISO 8601 week<br>    YEAR(MIN(_load_ts)) AS year            -- Extract year from timestamp<br>FROM {{ source(&#39;spotify&#39;, &#39;source_spotify_tracks&#39;) }}<br>GROUP BY artist, name</pre><p><strong>silver layer</strong> — teoretically here we should do some transformations</p><pre>{{ config(materialized=&#39;table&#39;) }}<br><br>SELECT<br>    song_id,<br>    artist,<br>    name,<br>    week_number,<br>    year<br>FROM {{ ref(&#39;bronze_spotify_tracks&#39;) }}</pre><p>Here, selects relevant fields for downstream analysis (removes raw metadata like raw_id and _load_ts).</p><p>The last part, <strong>gold layer </strong>should reflect the business logic:</p><pre>{{ config(materialized=&#39;table&#39;) }}<br><br>WITH ranked_weeks AS (<br>    SELECT<br>        year,<br>        week_number,<br>        RANK() OVER (ORDER BY year DESC, week_number DESC) AS rnk<br>    FROM {{ ref(&#39;silver_spotify_tracks&#39;) }}<br>),<br><br>latest_week AS (<br>    SELECT<br>        year,<br>        week_number<br>    FROM ranked_weeks<br>    WHERE rnk = 1<br>    LIMIT 1<br>)<br><br>SELECT<br>    artist,<br>    name<br>FROM {{ ref(&#39;silver_spotify_tracks&#39;) }} s<br>JOIN latest_week lw<br>  ON s.year = lw.year AND s.week_number = lw.week_number</pre><p>Produces the final dataset for analytics or dashboards. Here, filters for the most recent week of Spotify data and outputs a clean list of current top tracks. Such table, we should be able to expose to our stakeholder (end user).</p><p>Now, you can just turn the DAGs on and see the magic happens :)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sGi5Ttj3Yt2qfX3CG1MW6w.png" /></figure><p>To see if the table is populated properly, you can directly connect to it. Connect to mysql container:</p><pre>docker-compose exec mysql mysql -uairflow -p</pre><p>for password type airflow (as set up in compose YAML file MYSQL_PASSWORD: airflow ).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LZ1MCfVisWlvjEKtrvk8BA.png" /></figure><p>Then you can run SQL commands:</p><pre>USE spotify;<br>SHOW TABLES;<br>SELECT * FROM gold_spotify_tracks;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ttPFMwMhoDWLPyG1J5bEjg.png" /></figure><h4>To wrap it up</h4><p>The final gold layer mirrors the exact songs currently featured in your Release Radar playlist — cleaned, structured, and ready for analysis or reporting. Along the way, this project covered everything from data extraction and orchestration to transformation and data modeling, making it a solid foundation for anyone looking to dive into the world of ELT pipelines.</p><p>Hopefully, it inspires your own experiments with data, music, and engineering and helps you get started in the field of Data Engineering. Fingers crossed we’ll see you here soon!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=65fd36cd1670" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Airflow: How to model downstream for tasks dynamically mapped]]></title>
            <link>https://ijaniszewski.medium.com/airflow-how-to-model-downstream-tasks-for-tasks-dynamically-mapped-bc794c329430?source=rss-722a326ed2be------2</link>
            <guid isPermaLink="false">https://medium.com/p/bc794c329430</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[airflow]]></category>
            <dc:creator><![CDATA[Ignacy Janiszewski]]></dc:creator>
            <pubDate>Sat, 19 Apr 2025 18:21:13 GMT</pubDate>
            <atom:updated>2025-04-21T07:29:30.534Z</atom:updated>
            <content:encoded><![CDATA[<p>In modern data pipelines, dynamic task mapping in Apache Airflow is a powerful feature that allows for scalable and efficient DAG designs. However, a common point of confusion arises when trying to set up downstream or successor tasks for each dynamically mapped task individually.</p><h3>✅ Scenario 1<br>One Downstream Task After All Mapped Tasks</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*O397FyruvOM3C1V8" /></figure><h4><strong>Flow:</strong></h4><ol><li>Task A runs first</li><li>it maps into Task B with 3 mapped instances (e.g., B[0], B[1], B[2]).</li><li>Only <strong>after all B tasks succeed</strong>, Task C runs.</li><li>If <strong>any one of the B tasks fails</strong>, Task C does <strong>not</strong> run.</li></ol><h4>The code:</h4><pre>    @task<br>    def task_a():<br>        return [1, 2, 3]<br><br>    @task<br>    def task_b(x):<br>        if x == 2:<br>            raise Exception(&quot;Failing one of the tasks!&quot;)<br>        return x * 10<br><br>    @task<br>    def task_c(values):<br>        print(f&quot;Final values: {values}&quot;)<br><br>    task_c.expand(values=task_b.expand(x=task_a()))</pre><p>So, if any of B mapped task will fail, then task wont be run as upstream_failed :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WezTgIaCylv2sHoOUwlBYw.png" /></figure><h4>Real-life example:</h4><p>You want to publish a company-wide dashboard (Task C) <strong>only if</strong> all incoming data from multiple departments passes quality checks (Tasks B). The raw data is first collected (Task A).</p><p><strong>task A: </strong><strong>collect_data()</strong></p><p>Gathers data from multiple departments:</p><ul><li>sales_data.csv</li><li>marketing_data.csv</li><li>finance_data.csv</li></ul><p><strong>task B: </strong><strong>run_data_quality_check()</strong></p><p>This task runs separately for each dataset.</p><p>You map over each department’s data file.</p><p>If any quality check fails (e.g., missing fields, nulls, invalid formats), it raises an error.</p><p><strong>task C: </strong><strong>publish_dashboard()</strong></p><p>Only runs <strong>after all</strong> mapped quality checks are successful.</p><p>If even one department’s data fails validation, the dashboard is <strong>not updated</strong>.</p><pre>@task<br>def collect_data():<br>    return [&quot;sales_data.csv&quot;, &quot;marketing_data.csv&quot;, &quot;finance_data.csv&quot;]<br><br>@task<br>def run_data_quality_check(file_name):<br>    # Simulate validation<br>    if &quot;finance&quot; in file_name:<br>        raise ValueError(&quot;Finance data has missing rows&quot;)<br>    return f&quot;{file_name} passed&quot;<br><br>@task<br>def publish_dashboard(validated_files):<br>    print(f&quot;Publishing dashboard with: {validated_files}&quot;)<br><br># DAG setup<br>validated = run_data_quality_check.expand(file_name=collect_data())<br>publish_dashboard(validated_files=validated)</pre><p>Ok, but what if want to have each mapped task from Task B feed directly into its corresponding Task C instance — not wait for all Task B instances to finish before any Task C starts?</p><h3>✅ Scenario 2<br>Each Mapped Task Has Its Own Downstream Task</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LXnxd7JUgAMHHP2K" /></figure><p>An attempt like this might seem intuitive:</p><pre>taskB.expand_kwargs(v=taskA) &gt;&gt; taskC.expand(v=taskA)</pre><p>But this causes Airflow to wait for <strong>all</strong> mapped instances of Task B to complete before beginning <strong>any</strong> mapped instance of Task C. Worse still, trying to chain .expand() like a regular task ((Task B &gt;&gt; Task C).expand_kwargs(...)) doesn&#39;t even compile.</p><h4>The Right Way: Use Task Groups</h4><p>To create this one-to-one mapped relationship between tasks, you can use @task_group to group your mapped tasks and their successors. Here&#39;s a working example:</p><pre>import logging<br>from datetime import datetime<br><br>from airflow.decorators import task, task_group<br>from airflow.exceptions import AirflowException<br>from airflow.models.dag import DAG<br><br>with DAG(<br>    dag_id=&quot;dynamic_task_group&quot;,<br>    description=&quot;Task Group on top of Dynamic Mapped Tasks&quot;,<br>    start_date=datetime(2023, 1, 1),<br>    catchup=False,<br>    schedule_interval=None,<br>) as dag:<br><br>    @task<br>    def create_instructions(**kwargs):<br>        # kwargs if you need an input params<br>        # input = kwargs.get(&quot;params&quot;)<br>        # try:<br>        #     ingest_cfg = input[&quot;ingest&quot;]<br>        return [<br>            {&quot;pipeline_name&quot;: &quot;first&quot;, &quot;parameters&quot;: 1},<br>            {&quot;pipeline_name&quot;: &quot;second&quot;, &quot;parameters&quot;: 2},<br>        ]<br><br>    @task<br>    def print_values(pipeline_name, parameters):<br>        logging.info(f&quot;pipeline_name: {pipeline_name}&quot;)<br>        logging.info(f&quot;parameters: {parameters}&quot;)<br><br>    @task<br>    def print_values_again(pipeline_name, parameters):<br>        logging.info(f&quot;pipeline_name: {pipeline_name}&quot;)<br>        logging.info(f&quot;parameters: {parameters}&quot;)<br><br>    @task_group<br>    def my_group(pipeline_name, parameters):<br>        one = print_values(pipeline_name, parameters)<br>        two = print_values_again(pipeline_name, parameters)<br><br>        one &gt;&gt; two<br><br>    my_group.expand_kwargs(create_instructions())</pre><p>Which gives:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eu9FWkPR4_qeY7WCOnX3gw.png" /></figure><p>In this setup:</p><ul><li>create_instructions dynamically generates a list of mappings.</li><li>my_group runs per mapping, executing both the mapped print_values and its own successor (done).</li><li>Each group instance is isolated, maintaining a clean 1:1 mapping of the form:<br> Task B[i] -&gt; Task C[i]</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rNvj5OESwK0y8DgEu9FgVQ.png" /></figure><h4>Testing failed pipeline</h4><p>You can even simulate failures by raising exceptions. Let’s break one of the pipelines and see what happens:</p><pre>from airflow.exceptions import AirflowException<br><br>@task<br>def print_values(pipeline_name, parameters):<br>    if pipeline_name == &quot;second&quot;:<br>        raise AirflowException(&quot;Test error status&quot;)<br>    logging.info(f&quot;pipeline_name: {pipeline_name}&quot;)<br>    logging.info(f&quot;parameters: {parameters}&quot;)</pre><p>Then, second mapped task in print_values failed:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OJlA2rY0YnNkASMcKKYSXQ.png" /></figure><p>which gives print_values_again <strong>upstream_failed</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kjjIuj07ZIBDgMxbI44xYA.png" /></figure><p>First pipeline is finished successfully.</p><h4>Real-life example:</h4><p>Per-Department Data Load &amp; Archival</p><p>Imagine you’re on a <strong>data engineering team</strong> for a company that collects <strong>daily transaction data</strong> from different departments — say <strong>Retail</strong>, <strong>Online</strong>, and <strong>Wholesale</strong>.</p><p>You want to:</p><ul><li><strong>(Task A)</strong> Dynamically define each department’s data that needs to be processed.</li><li><strong>(Task B)</strong> Run <strong>quality checks</strong> for each department.</li><li><strong>(Task C)</strong> If a department passes validation, immediately <strong>load that department’s data into its own dedicated warehouse table</strong>, and <strong>archive the raw file</strong>.</li></ul><p><strong>Mapped DAG Design:</strong></p><p><strong>Task A</strong> → create_instructions: returns a list of departments + file info.</p><p><strong>Task B</strong> → validate_data: ensures data integrity for that department.</p><p><strong>Task C</strong> → load_and_archive_data: runs <strong>after validation</strong>, tailored per department.</p><p>Each <strong>Task B</strong> → <strong>Task C</strong> pair runs per department, all isolated — no shared final step.</p><pre>import logging<br>from datetime import datetime<br>from airflow.decorators import task, task_group<br>from airflow.models.dag import DAG<br><br>with DAG(<br>    dag_id=&quot;departmental_data_pipeline&quot;,<br>    description=&quot;Validate and load data for each department independently&quot;,<br>    start_date=datetime(2023, 1, 1),<br>    catchup=False,<br>    schedule_interval=None,<br>) as dag:<br><br>    @task<br>    def create_instructions():<br>        return [<br>            {&quot;department&quot;: &quot;Retail&quot;, &quot;file_path&quot;: &quot;/data/retail_202304.csv&quot;},<br>            {&quot;department&quot;: &quot;Online&quot;, &quot;file_path&quot;: &quot;/data/online_202304.csv&quot;},<br>            {&quot;department&quot;: &quot;Wholesale&quot;, &quot;file_path&quot;: &quot;/data/wholesale_202304.csv&quot;},<br>        ]<br><br>    @task<br>    def validate_data(department, file_path):<br>        logging.info(f&quot;Running quality checks for {department} - {file_path}&quot;)<br>        # Simulate failure for demonstration<br>        if department == &quot;Online&quot;:<br>            raise ValueError(f&quot;Validation failed for {department}&quot;)<br>        logging.info(f&quot;{department} passed validation.&quot;)<br><br>    @task<br>    def load_and_archive_data(department, file_path):<br>        logging.info(f&quot;Loading data into {department}_table&quot;)<br>        logging.info(f&quot;Archiving file: {file_path}&quot;)<br>        # Simulate DB load and S3 archive<br><br>    @task_group<br>    def process_department(department, file_path):<br>        validation = validate_data(department, file_path)<br>        load_and_archive = load_and_archive_data(department, file_path)<br>        validation &gt;&gt; load_and_archive<br><br>    process_department.expand_kwargs(create_instructions())</pre><h3>Summary</h3><p>Using task groups in combination with .expand_kwargs() is the key to modeling flows where each dynamically mapped task has its own path with successors. It&#39;s a clean, scalable solution that gives you granular control over your DAG logic.</p><h4>Pros:</h4><ul><li>Precise mapping from Task B[i] → Task C[i]</li><li>Clean separation of task instances</li><li>Full flexibility within each group</li></ul><p>If you’re building complex data pipelines and leveraging dynamic mapping, this structure will save you from DAG design headaches.</p><h3>Bonus!<br>Custom names for Dynamic Task Mapping</h3><p>As of <a href="https://airflow.apache.org/blog/airflow-2.9.0/#custom-names-for-dynamic-task-mapping">Airflow version 2.9.0</a> there is an option to name dynamic tasks:</p><pre>    @task<br>    def task_a():<br>        return [1, 2, 3]<br><br>    @task(map_index_template=&quot;name_{{ my_variable }}&quot;)<br>    def task_b(x):<br>        context = get_current_context()<br>        context[&quot;my_variable&quot;] = x * 3<br>        return x * 10<br><br>    @task<br>    def task_c(values):<br>        print(f&quot;Final values: {values}&quot;)<br><br>    task_c.expand(values=task_b.expand(x=task_a()))</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vp4xzK62ujuPkBL1mn0hbQ.png" /></figure><p>just set map_index_template . You can even customize the task name dynamically at mapping time, using context or variables.</p><blockquote><strong>Is it modified during runtime?</strong><br>Not exactly. The map_index_template is evaluated at <strong>task mapping time</strong>, but <strong>not</strong> during actual runtime (task execution). So while you can use variables and context to customize it dynamically at the time the task is being mapped, you cannot change it during the runtime of the task itself.</blockquote><p>Read more in the <a href="https://airflow.apache.org/docs/apache-airflow/stable/authoring-and-scheduling/dynamic-task-mapping.html#named-mapping">documentation</a>.</p><p>Using custom task names instead of numeric indexes (0, 1, 2…) makes it much easier to locate specific tasks, debug issues, and generally work with dynamic tasks. It greatly improves the readability and maintainability of your DAGs.</p><p>Happy DAGging :)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bc794c329430" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>