The Definitive Guide to Agentic Analytics

2. The PostgreSQL Illusion (OLTP vs OLAP)

The most catastrophic beginner mistake in data engineering is refusing to build a data warehouse at all. Throughout this book, we will frequently use PostgreSQL as our baseline. Postgres is undeniably the world's greatest operational (OLTP) database, making it the perfect mental model to *unlearn* when stepping into the world of Data Warehousing (OLAP).

3. The 2026 Ecosystem: OLAP vs OLTP vs Graph

Do not use a hammer to turn a screw. Before designing a migration pipeline, understand the exact workload the target database is engineered to solve.

4. Unlearning Normalization (OBT vs Kimball)

In Postgres, you are taught the golden rule of 3rd Normal Form (3NF): Do not repeat data. If a user changes their name, you should only have to update it in one single table. Storage on expensive SSDs must be conserved.

In a Data Warehouse, storage is effectively free (pennies per GB on S3), but compute—moving data across the network to join it—is aggressively expensive. In the DW, Joins are the enemy of scale.

5. Slowly Changing Dimensions (Type 2)

If we denormalize everything, what happens when data changes? In Postgres, if a user moves from New York to California, you execute a simple command: UPDATE users SET state = 'CA' WHERE id = 5;

In a Data Warehouse, destructive updates destroy analytical truth. If you overwrite the state to 'CA', all historical sales from last year made by that user will now falsely appear as California revenue in your geographic reports. To solve this, we use SCD Type 2.

-- Instead of UPDATE, we expire the old row and INSERT a new one.
-- Notice the surrogate key (sk_user_id), which is unique per version of the user.

SELECT 
    sk_user_id,
    user_id,
    name,
    state,
    effective_date,
    expiration_date,
    is_current
FROM dim_users 
WHERE user_id = 5
ORDER BY effective_date ASC;

/* Result:
sk_user_id | user_id | name  | state | effective_date | expiration_date | is_current
------------------------------------------------------------------------------------
1001       | 5       | Alice | NY    | 2024-01-01     | 2026-03-15      | FALSE
2045       | 5       | Alice | CA    | 2026-03-15     | 9999-12-31      | TRUE
*/

-- Now, when joining orders to users, we join on the timestamp to get the state they lived in AT THE TIME of the order.
SELECT sum(o.amount)
FROM fct_orders o
JOIN dim_users u 
  ON o.user_id = u.user_id
 AND o.order_date >= u.effective_date
 AND o.order_date < u.expiration_date;

7. Why ELT Defeats ETL

For a decade, the industry standard was Extract, Transform, Load (ETL). Data engineers wrote complex Python (Pandas) or Java (Spark) pipelines that extracted data from the source, transformed it entirely in server memory, and finally loaded it into the warehouse. In 2026, ETL is an anti-pattern. We now strictly use ELT (Extract, Load, Transform).

8. Change Data Capture (CDC)

How do we "Extract and Load" data from Postgres into the DW without killing the Postgres production server? Nightly batch queries (`SELECT * FROM users WHERE updated_at > yesterday`) are obsolete. They require heavy sequential scans that lock application tables.

Continuous replication via Change Data Capture (CDC) reads directly from the legacy database's transaction logs (e.g., Postgres WAL) with near-zero performance impact on the application.

9. The Staging Strategy (Raw String Tables)

To facilitate bulletproof ELT pipelines, land your data from CDC into "Bronze" tables where every single column is a STRING (VARCHAR).

If you try to cast a date during network transit, and the legacy Postgres database outputs `'0000-00-00'` (a common legacy corruption), the target DW will reject the entire batch, halting the pipeline. Copying string data will NEVER fail. Once it lands in the Bronze string table, you use SQL inside the DW to safely clean it into Silver tables.

-- 1. BRONZE LAYER: Land the raw CDC payload perfectly intact.
CREATE TABLE stg_legacy_users (
  id VARCHAR, 
  full_name VARCHAR, 
  birth_date VARCHAR,
  _cdc_timestamp VARCHAR
);

COPY INTO stg_legacy_users FROM @s3_migration_stage;

-- 2. SILVER LAYER: Transform safely inside the DW using TRY_CAST.
-- If TRY_CAST fails, it yields NULL instead of crashing the pipeline.
CREATE TABLE silver_users AS
SELECT
  CAST(id AS BIGINT) AS user_id,
  TRIM(full_name) AS clean_name,
  TRY_CAST(birth_date AS DATE) AS birth_date,
  CAST(_cdc_timestamp AS TIMESTAMP) AS sync_time
FROM stg_legacy_users;

10. Engine Physics: Data Skipping & Partitions

Because Data Warehouses do not use B-Tree indexes, performance is dictated by Data Skipping (or partition pruning).

A modern DW breaks your massive 100TB table into thousands of smaller, immutable files (micro-partitions), typically 100MB to 500MB in size. For each file, the DW maintains a metadata catalog recording the `MIN` and `MAX` values of the columns inside it.

11. Integrity & Dialect Traps

SQL is not universally standard. Moving from Oracle to Postgres to Snowflake introduces subtle dialect differences that will silently break executive dashboards and application logic if not handled carefully.

• 1. The Floating Point Trap

  Postgres handles `NUMERIC` types gracefully. Big data frameworks sometimes default to floating-point math if schema types are inferred. In floats, `0.1 + 0.2` might equal `0.30000000000000004`. When migrating financial data, always explicitly cast to strict DECIMAL(38, 9) types, or your accounting department will reject the entire multi-million dollar migration over a $0.01 rounding anomaly.

• 2. NULLS FIRST vs NULLS LAST

  In an `ORDER BY col DESC` statement, Postgres and Snowflake sort `NULL` values to the top. Oracle puts them at the bottom. If an API relies on pagination, this dialect difference will skip or duplicate rows. Always explicitly declare `ORDER BY col DESC NULLS LAST` in your warehouse views.

• 3. Enforcing UTC (The Silent Corrupter)

  Legacy systems often store dates as `TIMESTAMP WITHOUT TIME ZONE`, assuming local server time. The cloud operates globally in UTC. You must cast all legacy times to `TIMESTAMP WITH TIME ZONE` at offset during the ELT process. Failure to do so will result in daily aggregations shifting by 4-8 hours, misaligning daily revenue reports.

12. The Agentic AI Future

The true purpose of modernizing the data warehouse is to enable Agentic AI. Dashboards are static and backward-looking. In the future, business leaders won't look at dashboards; they will ask autonomous analysts questions, and the agents will write SQL, execute it, and chart the results instantly.

# data_contract.yaml
dataset: fct_orders
version: 1.2.0
owner: team-billing
quality_SLA: 99.9%
columns:
  - name: order_id
    type: string
    is_primary: true
    checks:
      - not_null
      - unique
  - name: tax_rate
    type: decimal(10,4)
    checks:
      - min: 0.0
      - max: 1.0
    # Agent blocks any upstream removal of this column