DEV Community

Bechir Bejaoui
Bechir Bejaoui

Posted on

Snowflake to PostgreSQL: the type traps, caught before they reach your tables

#snowflake #postgres #dataengineering #python

Moving a table out of Snowflake into PostgreSQL looks like a one-liner. Then the numbers come out slightly wrong, and nobody notices for weeks.

Here is the order ID I tested with: 9007199254740993. In Snowsight, the small chart above the results column shows it as 9007199254740992. One digit off. The cell below shows the right value; the chart doesn't, because somewhere along the way the number went through a floating-point value, and a float can't hold every integer above 2^53.

Any pipeline that lets a value pass through a float, a generic "number" type or a text conversion can do the same thing to your data, silently. So when I added a Snowflake source to Hydra ETL, the goal was simple: every value lands in PostgreSQL exactly as it was in Snowflake, or the run stops before writing anything.

The test

A real Snowflake trial account, a table of 50,000 orders:

  • ORDER_ID NUMBER(38,0), starting at 9007199254740993 (just above 2^53)
  • AMOUNT NUMBER(12,2)
  • ORDERED_AT TIMESTAMP_TZ, SHIPPED_ON DATE, LOADED_AT TIMESTAMP_NTZ
  • IS_GIFT BOOLEAN, DETAILS VARIANT (a JSON document)

The whole job is two YAML files:

# sources.yaml
sources:
  snowflake_orders:
    type: snowflake
    connection:
      account: "{{ env:SNOWFLAKE_ACCOUNT }}"
      user: "{{ env:SNOWFLAKE_USER }}"
      private_key_file: "{{ env:SNOWFLAKE_PRIVATE_KEY_FILE }}"   # key-pair authentication
      warehouse: COMPUTE_WH
      database: SALES
      schema: PUBLIC
      column_case: lower        # ORDER_ID -> order_id
    extract:
      table: ORDERS
Enter fullscreen mode Exit fullscreen mode 
# destinations.yaml
destinations:
  postgres_orders:
    type: postgresql
    connection:
      host: "{{ env:PG_HOST }}"
      user: "{{ env:PG_USER }}"
      password: "{{ env:PG_PASSWORD }}"
      database: "{{ env:PG_DATABASE }}"
    load:
      table: orders
      mode: upsert              # re-running updates rows, never duplicates them
      key: [order_id]
      atomic: true              # all or nothing
Enter fullscreen mode Exit fullscreen mode

Then hdrctl run .

What arrived in PostgreSQL

Same row count, same total, same smallest ID, on both sides:

  • Snowflake: 50,000 rows, total amount 24989859.06, smallest ID 9007199254740993
  • PostgreSQL: 50,000 rows, total amount 24989859.06, smallest ID 9007199254740993

And the table Hydra ETL created on the PostgreSQL side has the types you would have chosen by hand:

  • NUMBER(38,0) → bigint, the exact integer, never a float
  • NUMBER(12,2) → numeric, the exact decimal
  • TIMESTAMP_TZ → timestamp with time zone, the instant is kept
  • TIMESTAMP_NTZ → timestamp without time zone
  • DATE → date, not a timestamp at midnight
  • BOOLEAN → boolean
  • VARIANT → jsonb, queryable with -> and ->>
  • order_id → primary key, because the job loads in upsert on that key

The uppercase Snowflake column names became lowercase (column_case: lower), so nobody has to write "ORDER_ID" with quotes in every PostgreSQL query. If two columns only differ by case, the source refuses to start before reading a single row, instead of letting one overwrite the other.

Then I broke it on purpose

order_id is a bigint in PostgreSQL now. Snowflake's NUMBER(38,0) goes far beyond that. So, in Snowflake:

UPDATE SALES.PUBLIC.ORDERS SET AMOUNT = AMOUNT + 1;
INSERT INTO SALES.PUBLIC.ORDERS (ORDER_ID, CUSTOMER_ID, AMOUNT, CURRENCY)
VALUES (12345678901234567890123, 1, 10.00, 'EUR');
Enter fullscreen mode Exit fullscreen mode

50,000 updated amounts, plus one ID that cannot fit in a bigint. The run stopped on that row. And PostgreSQL?

  • Before the run: total amount 24989859.06
  • After the failed run: total amount 24989859.06

Not one of the 50,000 updates was applied. With atomic: true, the whole load is one transaction: either everything is published, or nothing is.

Hydra ETL 0.20 also checks integer ranges before writing a batch, so the error names the problem instead of PostgreSQL's bare bigint out of range:

the value 12345678901234567890123 (row … of the batch) does not fit in column 'order_id',
which is bigint in public.orders. Nothing of this batch was written. Widen the column first,
for example:
  ALTER TABLE public.orders ALTER COLUMN order_id TYPE NUMERIC(38,0);
Enter fullscreen mode Exit fullscreen mode

I deleted the oversized row in Snowflake and ran the job again: total amount 25039859.06, exactly 1.00 more on each of the 50,000 orders.

Two setup traps worth knowing

Not type traps, but they cost me time:

  • Key-pair authentication. Snowflake expects the public key in the BEGIN PUBLIC KEY format, which starts with MIIBIjANBgkqh. A key that starts with MIIBCgKCAQEA is valid RSA, but in another format, and Snowflake rejects it with "Invalid Public key". openssl pkey -in rsa_key.p8 -pubout gives the right one.
  • Account identifier. A wrong one fails at login with a 404 Not Found. Use ORGNAME-ACCOUNTNAME, or the locator with its region as in the Snowsight address (xy12345.eu-central-2.aws), without https://.

Try it 

pip install "hydra-etl[snowflake,postgres]"
Enter fullscreen mode Exit fullscreen mode

The complete example, with a PostgreSQL in Docker and a local Snowflake emulator if you don't have an account, is in the repository: examples/snowflake-to-postgres.

Top comments (0)