PostgreSQL (Source) -> TRUNCATE (old table) -> Load all rows → MS SQL (Target)

from airflow import DAG
from airflow.providers.postgres.operators.postgres import PostgresOperator
from airflow.providers.microsoft.mssql.operators.mssql import MsSqlOperator
from datetime import datetime

with DAG(dag_id="full_load_customers",
         start_date=datetime(2024, 1, 1),
         schedule_interval="@daily",
         catchup=False) as dag:

    truncate_target_table = MsSqlOperator(
        task_id='truncate_target_table',
        mssql_conn_id='mssql_target',
        sql="TRUNCATE TABLE customers;"
    )

    load_full_data = PostgresOperator(
        task_id='load_full_data',
        postgres_conn_id='postgres_source',
        sql="""
            INSERT INTO mssql_target.dbo.customers
            SELECT * FROM postgres_source.public.customers;
        """
    )

    truncate_target_table >> load_full_data

NoSQL so'zi qanday kelib chiqanini bilasizmi?

NoSQL birinchi Marta twitterdagi hashtag sifatida 2010 yilda foydalanilgan #NoSQL yani u paytda SQL databaza sifatida dominantlik qilib kelgan va SQLga yo'q deymiz degan manoda yozilgan . Ammo u keyinchali o'zgartirilib yani Not Only SQL deb kengaytirilgan va hozirda shu manoda qo'llanilib kelinadi.

Python da context managerni with kalit sozi bilan ishlatilishidan tanib olsa boladi.
U resurslarni ikkita metod yordamida boshqaradi: __enter__() — resurslarni ishga tayyorlash uchun, __exit__() esa ularni boshatish yoki yopish uchun ishlatiladi.

Context manager xatolik yuz berganda ham resurslar xavfsiz boshqarilishini taminlaydi va odatda fayllar, thread lar yoki malumotlar bazasi ulanishlarini boshqarishda qollaniladi

Even though the term big is used in big data, it’s not just about the size of
the data. It’s also about all the data, big or small, within your company and
all the data outside your company that would be helpful to you. The data
can be in any format and can be collected with any degree of regularity.
So the best way to define big data is to think of it as all data,
no matter its size (volume), speed (velocity), or type (variety). In addition to those criteria,
there are three more factors you can use to describe data: veracity,
variability, and value. Together, they’re commonly known as the “six Vs” of
big data

Data Maturity

Modern data warehouse

DuckDB 2.0

Async I/O: S3 dan o’qish 2–3 barobar tezlashdi
Oldin har bir thread navbatma-navbat ishlardi: avval ma’lumotni yuklab olardi, keyin decode qilardi. Natijada CPU tarmoqni kutib bo’sh turardi.

Endi alohida pool row group’larni oldindan tortib qo’yadi, worker’lar esa faqat decode bilan band. 2,2 GB Parquet ustidagi query: 18,8 s → 7,7 s. Default holatda yoqilgan, kodga tegish shart emas (read_ahead_depth). Lekin 1 MB lik minglab mayda fayllaringiz bo’lsa, bu yordam bermaydi, asos baribir to’g’ri qurilgan bo’lishi kerak.

Rekursiv CTE: chuqur ierarxiyalar endi uchib ishlaydi
Oldin rekursiyaning har bir qadamida jadval boshidan oxirigacha qayta o’qilardi. Endi u bir marta o’qiladi, keyin faqat kerakli joylardan lookup qilinadi.

20 000 ta commit’lik tarixni aylanib chiqish oldin 1,8–16 soniya olardi, endi 0,1 soniya. Git tarixi, lineage, thread’lar va BOM uchun ayni muddao. Oddiy, sayoz org-strukturada esa farqni deyarli sezmaysiz.

JSON o’rniga VARIANT
Strukturasi o’zgarmaydigan JSON field’lar avtomatik ravishda alohida kolonkalarga ajratiladi (shredding), qolgani esa binary ko’rinishda saqlanadi.

Natija: JSON string’ga qaraganda 2,7 barobar kam joy, filterlar esa ~6 barobar tez, deyarli oddiy typed kolonkalardek.

Bitta nyuans bor: list larni cast qilish hozircha sekin, bazida son, bazida string bo’lib keladigan field esa kolonkaga ajratilmaydi.