Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

Accio: Query Your Data Warehouse Like Exploring...

Accio: Query Your Data Warehouse Like Exploring One Big View

Avatar for brandboat

brandboat

July 30, 2023
Tweet

Other Decks in Programming

Transcript

  1. •This is an introduction of Accio •The inspiration behind Accio

    •An overview of the benefits of Accio •Some simple example of it in use •An overview of Accio architecture •ChatGPT Plugin About this presentation
  2. © 2023 Canner, Inc. Data development at enterprise scale is

    really hard... Dimension Table Fact Table ERF 3rd Party Data Fact Table Wide Dimension Table Superset Tableau BI Tools… Logging DB Exports Dimension Table Raw Data Core Data Tables Increasingly Wider, More Complex Derived Tables Downstream Tools Fact Table with Many Dimensions Fact Table with Even Many Dimensions Fact Table with Dimensions …
  3. © 2023 Canner, Inc. Data development at enterprise scale is

    really hard... The need for data development: ØSimplifying Complex Databases ØImproving Collaboration ØAdapting to Changes
  4. © 2023 Canner, Inc. What about using the concept of

    GraphQL in data development ? Dimension Table Fact Table ERF 3rd Party Data Fact Table Wide Dimension Table Superset Tableau BI Tools… Logging DB Exports Dimension Table Raw Data Core Data Tables Increasingly Wider, More Complex Derived Tables Downstream Tools Fact Table with Many Dimensions Fact Table with Even Many Dimensions Fact Table with Dimensions …
  5. © 2023 Canner, Inc. Accio • Model Definition Language •

    Model/Metric/Relation/View • Accio Query • Syntactic sugar • Close to standard SQL • Lambda/Array function
  6. © 2023 Canner, Inc. Query to-1 relation Accio Query Standard

    Query Lineitem Supplier Nation N:1 N:1
  7. © 2023 Canner, Inc. Query to-N relation with array/lambda function

    Accio Query Lineitem Supplier 1:N Find the total revenue from all suppliers after the ship date of 1995-04-01 Just like functional programming
  8. © 2023 Canner, Inc. Metric @preAgg: store in duckdb @model

    @measure @dim @time_grain Use roll_up to group by orderdate_grain by YEAR
  9. © 2023 Canner, Inc. Data Source Pre-Aggregation Accio Architecture Wire

    Protocol § pg message flow § bunch of pg tables Interface Layer SQL Rewrite Translate SQL to target dialect Check if all references is cached BI Tools… JDBC PSQL
  10. © 2023 Canner, Inc. Accio Interface Layer Wire Protocol §

    pg message flow § bunch of pg tables Interface Layer https://www.youtube.com/watch?v=zX4NpklUZjI
  11. •Structured Model Definition Language •On-Demand SQL Generation •Consistency and Reusability

    • Metric definition and model expression •Metric pre-aggregation in DuckDB •PG Wire Protocol Recap
  12. © 2023 Canner, Inc. References • https://help.tableau.com/current/online/en-us/datasource_datamodel.htm • https://www.linkedin.com/pulse/headless-bi-flexible-scalable-approach-business-jarred- armstrong/

    • https://docs.snowflake.com/en/user-guide/sample-data-tpch • https://medium.com/airbnb-engineering/how-airbnb-achieved-metric-consistency-at- scale-f23cc53dea70
  13. © 2023 Canner, Inc. What about using BI ? •

    Performance limitation: Pull all specified tables to establish semantic model • Direct query mode: Can't apply semantic model • What about data sharing ?